You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@drill.apache.org by Rameshwar Mane <mr...@gmail.com> on 2019/11/28 10:22:46 UTC

not able read few tables form mysql due to date type format error

Hi,
I was trying to query my mysql table registered in apache drill. I am facing the following error while trying to execute query.
the error in the terminal was:

Error: DATA_READ ERROR: Failure while attempting to read from database.

sql SELECT *
FROM `openisjb_demo`.`ASSOC`
plugin mysql
Fragment 0:0

[Error Id: 12adbd85-48b4-4b1d-a29b-0e8ab0d34d54 on xxxxxxxxxxxx:31010] (state=,code=0)

the logs in sqlline.log file are :

2019-11-28 10:15:13,679 [Client-1] INFO o.a.d.j.i.DrillCursor$ResultsListener - [#6] Query failed:

org.apache.drill.common.exceptions.UserRemoteException: DATA_READ ERROR: Failure while attempting to read from database.

sql SELECT *
FROM `openisjb_demo`.`ASSOC`
plugin mysql
Fragment 0:0

[Error Id: 67b0e4e1-18d3-4155-8537-021806c88858 on xxxxxxxxxxxxxxxxxxxx:31010]
at org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(QueryResultHandler.java:123) [drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.rpc.user.UserClient.handle(UserClient.java:422) [drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.rpc.user.UserClient.handle(UserClient.java:96) [drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:273) [drill-rpc-1.16.0.jar:1.16.0]
at org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:243) [drill-rpc-1.16.0.jar:1.16.0]
at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:88) [netty-codec-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:287) [netty-handler-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:102) [netty-codec-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:312) [netty-codec-4.0.48.Final.jar:4.0.48.Final]
at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:286) [netty-codec-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:86) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1294) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:911) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:131) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:645) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:580) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:497) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:459) [netty-transport-4.0.48.Final.jar:4.0.48.Final]
at io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:131) [netty-common-4.0.48.Final.jar:4.0.48.Final]
at java.lang.Thread.run(Thread.java:748) [na:1.8.0_151]
java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) ~[mysql-connector-java.jar:5.1.48]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) ~[mysql-connector-java.jar:5.1.48]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) ~[mysql-connector-java.jar:5.1.48]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861) ~[mysql-connector-java.jar:5.1.48]
at com.mysql.jdbc.ResultSetRow.getDateFast(ResultSetRow.java:146) ~[mysql-connector-java.jar:5.1.48]
at com.mysql.jdbc.ByteArrayRow.getDateFast(ByteArrayRow.java:244) ~[mysql-connector-java.jar:5.1.48]
at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2011) ~[mysql-connector-java.jar:5.1.48]
at org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488) ~[commons-dbcp2-2.5.0.jar:2.5.0]
at org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488) ~[commons-dbcp2-2.5.0.jar:2.5.0]
at org.apache.drill.exec.store.jdbc.JdbcRecordReader$DateCopier.copy(JdbcRecordReader.java:440) ~[drill-jdbc-storage-1.16.0.jar:1.16.0]
at org.apache.drill.exec.store.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:271) ~[drill-jdbc-storage-1.16.0.jar:1.16.0]
at org.apache.drill.exec.physical.impl.ScanBatch.internalNext(ScanBatch.java:223) ~[drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.physical.impl.ScanBatch.next(ScanBatch.java:271) ~[drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:126) ~[drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:116) ~[drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.record.AbstractUnaryRecordBatch.innerNext(AbstractUnaryRecordBatch.java:63) ~[drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:141) ~[drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:186) ~[drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:104) ~[drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext(ScreenCreator.java:83) ~[drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:94) ~[drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:296) ~[drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:283) ~[drill-java-exec-1.16.0.jar:1.16.0]
at .......(:0) ~[na:na]
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1746) ~[hadoop-common-2.7.4.jar:na]
at org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:283) ~[drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.common.SelfCleaningRunnable.run(SelfCleaningRunnable.java:38) ~[drill-common-1.16.0.jar:1.16.0]
at .......(:0) ~[na:na]

the logs in drillbit.log file are :

2019-11-28 10:01:03,217 [22206520-3db3-ddd0-72c1-5ab0990a73a4:foreman] INFO o.a.drill.exec.work.foreman.Foreman - Query text for query with id 22206520-3db3-ddd0-72c1-5ab0990a73a4 issued by drill: select * from ASSOC
2019-11-28 10:01:04,111 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0] INFO o.a.d.e.w.fragment.FragmentExecutor - 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State change requested AWAITING_ALLOCATION --> RUNNING
2019-11-28 10:01:04,111 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0] INFO o.a.d.e.w.f.FragmentStatusReporter - 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State to report: RUNNING
2019-11-28 10:01:04,132 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0] INFO o.a.d.e.store.jdbc.JdbcRecordReader - User Error Occurred: Failure while attempting to read from database. (Value '0000-00-00' can not be represented as java.sql.Date)
org.apache.drill.common.exceptions.UserException: DATA_READ ERROR: Failure while attempting to read from database.

sql SELECT *
FROM `openisjb_demo`.`ASSOC`
plugin mysql

[Error Id: 12adbd85-48b4-4b1d-a29b-0e8ab0d34d54 ]
at org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:630) ~[drill-common-1.16.0.jar:1.16.0]
at org.apache.drill.exec.store.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:281) [drill-jdbc-storage-1.16.0.jar:1.16.0]
at org.apache.drill.exec.physical.impl.ScanBatch.internalNext(ScanBatch.java:223) [drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.physical.impl.ScanBatch.next(ScanBatch.java:271) [drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:126) [drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:116) [drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.record.AbstractUnaryRecordBatch.innerNext(AbstractUnaryRecordBatch.java:63) [drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:141) [drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:186) [drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:104) [drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext(ScreenCreator.java:83) [drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:94) [drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:296) [drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:283) [drill-java-exec-1.16.0.jar:1.16.0]
at java.security.AccessController.doPrivileged(Native Method) [na:1.8.0_151]
at javax.security.auth.Subject.doAs(Subject.java:422) [na:1.8.0_151]
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1746) [hadoop-common-2.7.4.jar:na]
at org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:283) [drill-java-exec-1.16.0.jar:1.16.0]
at org.apache.drill.common.SelfCleaningRunnable.run(SelfCleaningRunnable.java:38) [drill-common-1.16.0.jar:1.16.0]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_151]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_151]
at java.lang.Thread.run(Thread.java:748) [na:1.8.0_151]
Caused by: java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) ~[mysql-connector-java.jar:5.1.48]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) ~[mysql-connector-java.jar:5.1.48]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) ~[mysql-connector-java.jar:5.1.48]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861) ~[mysql-connector-java.jar:5.1.48]
at com.mysql.jdbc.ResultSetRow.getDateFast(ResultSetRow.java:146) ~[mysql-connector-java.jar:5.1.48]
at com.mysql.jdbc.ByteArrayRow.getDateFast(ByteArrayRow.java:244) ~[mysql-connector-java.jar:5.1.48]
at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2011) ~[mysql-connector-java.jar:5.1.48]
at org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488) ~[commons-dbcp2-2.5.0.jar:2.5.0]
at org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488) ~[commons-dbcp2-2.5.0.jar:2.5.0]
at org.apache.drill.exec.store.jdbc.JdbcRecordReader$DateCopier.copy(JdbcRecordReader.java:440) ~[drill-jdbc-storage-1.16.0.jar:1.16.0]
at org.apache.drill.exec.store.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:271) [drill-jdbc-storage-1.16.0.jar:1.16.0]
... 20 common frames omitted
2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0] INFO o.a.d.e.w.fragment.FragmentExecutor - 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State change requested RUNNING --> FAILED
2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0] ERROR o.a.d.e.physical.impl.BaseRootExec - Batch dump started: dumping last 2 failed batches
2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0] ERROR o.a.d.e.p.i.p.ProjectRecordBatch - ProjectRecordBatch[projector=null, hasRemainder=false, remainderIndex=0, recordCount=0, container=org.apache.drill.exec.record.VectorContainer@70742027[recordCount = 0, schemaChanged = true, schema = null, wrappers = [], ...]]
2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0] ERROR o.a.d.exec.physical.impl.ScanBatch - ScanBatch[container=org.apache.drill.exec.record.VectorContainer@76871541[recordCount = 0, schemaChanged = true, schema = null, wrappers = [org.apache.drill.exec.vector.NullableIntVector@ad6aa7f[field = [`ASSOC_MST_ID` (INT(12, 0):OPTIONAL)], ...], org.apache.drill.exec.vector.NullableVarCharVector@1292045f[field = [`ASSOC_MST_ROLE_CD` (VARCHAR(1, 0):OPTIONAL)], ...], org.apache.drill.exec.vector.NullableVarCharVector@43367b32[field = [`ASSOC_MST_LNAME` (VARCHAR(25, 0):OPTIONAL)], ...], org.apache.drill.exec.vector.NullableVarCharVector@d554308[field = [`ASSOC_MST_FNAME` (VARCHAR(25, 0):OPTIONAL)], ...], org.apache.drill.exec.vector.NullableVarCharVector@32ef958f[field = [`ASSOC_MST_MID_INIT` (VARCHAR(1, 0):OPTIONAL)], ...], org.apache.drill.exec.vector.NullableVarCharVector@305ddf71[field = [`ASSOC_MST_P_ADDR` (VARCHAR(90, 0):OPTIONAL)], ...], org.apache.drill.exec.vect
or.NullableVarCharVector@4042dda3[field = [`ASSOC_MST_E_ADDR` (VARCHAR(50, 0):OPTIONAL)], ...], org.apache.drill.exec.vector.NullableIntVector@67a88ba0[field = [`ASSOC_MST_PHONE` (INT(10, 0):OPTIONAL)], ...], org.apache.drill.exec.vector.NullableDateVector@29a52937[field = [`ASSOC_MST_ST_DT` (DATE(10, 0):OPTIONAL)], ...], org.apache.drill.exec.vector.NullableIntVector@77dddd25[field = [`ASSOC_MST_PAY_RT` (INT(11, 0):OPTIONAL)], ...], org.apache.drill.exec.vector.NullableVarCharVector@21a4a9b8[field = [`ASSOC_MST_SSN` (VARCHAR(9, 0):OPTIONAL)], ...], org.apache.drill.exec.vector.NullableIntVector@272d3a7[field = [`ASSOC_MST_ORG_GRP_ID` (INT(10, 0):OPTIONAL)], ...], org.apache.drill.exec.vector.NullableIntVector@29eae4f[field = [`id` (INT(12, 0):OPTIONAL)], ...]], ...], currentReader=JdbcRecordReader[sql=SELECT *
FROM `openisjb_demo`.`ASSOC`, Plugin=mysql], schema=null]
2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0] ERROR o.a.d.e.physical.impl.BaseRootExec - Batch dump completed.
2019-11-28 10:01:04,134 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0] INFO o.a.d.e.w.fragment.FragmentExecutor - 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State change requested FAILED --> FINISHED
2019-11-28 10:01:04,139 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0] WARN o.a.d.exec.rpc.control.WorkEventBus - Fragment 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0 manager is not found in the work bus.
2019-11-28 10:01:04,148 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0] WARN o.a.d.e.w.f.QueryStateProcessor - Dropping request to move to COMPLETED state as query is already at FAILED state (which is terminal).

is there any way to configure the data types to be used in apache drill or any alternate solutions instead of changing schema of the table in datasources.
Thanks and regards
Rameshwar Mane
Big Data Engineer

Re: not able read few tables form mysql due to date type format error

Posted by Rameshwar Mane <mr...@gmail.com>.

Hi Igor,
Thanks for quick reply.
The solution provided in the stackoverflow question helped me resolve the problem.
I added the
jdbc:mysql://localhost:3306/master_schema?zeroDateTimeBehavior=convertToNull;

string at the end of my connection string. And i am able to query tables without any issues.
Thanks and Regards
Rameshwar Mane
Big Data Engineer

Nov 28 2019, at 8:12 pm, Igor Guzenko <ihor.hu
zenko.igs@gmail.com> wrote:
> Hello Rameshwar,
>
> It seems like you have issue with date conversion for one of columns:
> Caused by: java.sql.SQLException: Value '0000-00-00' can not be represented
> as java.sql.Date.
>
> I believe you could try options suggested here:
> https://stackoverflow.com/questions/17195343/value-0000-00-00-can-not-be-represented-as-java-sql-date/17196386
>
> Thanks,
> Igor
>
> On Thu, Nov 28, 2019 at 2:07 PM Rameshwar Mane <mr...@gmail.com> wrote:
> > Hi,
> > I was trying to query my mysql table registered in apache drill. I am
> > facing the following error while trying to execute query.
> > the error in the terminal was:
> >
> > Error: DATA_READ ERROR: Failure while attempting to read from database.
> > sql SELECT *
> > FROM `openisjb_demo`.`ASSOC`
> > plugin mysql
> > Fragment 0:0
> >
> > [Error Id: 12adbd85-48b4-4b1d-a29b-0e8ab0d34d54 on xxxxxxxxxxxx:31010]
> > (state=,code=0)
> >
> > the logs in sqlline.log file are :
> > 2019-11-28 10:15:13,679 [Client-1] INFO
> > o.a.d.j.i.DrillCursor$ResultsListener - [#6] Query failed:
> >
> > org.apache.drill.common.exceptions.UserRemoteException: DATA_READ ERROR:
> > Failure while attempting to read from database.
> >
> > sql SELECT *
> > FROM `openisjb_demo`.`ASSOC`
> > plugin mysql
> > Fragment 0:0
> >
> > [Error Id: 67b0e4e1-18d3-4155-8537-021806c88858 on
> > xxxxxxxxxxxxxxxxxxxx:31010]
> > at
> > org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(QueryResultHandler.java:123)
> > [drill-java-exec-1.16.0.jar:1.16.0]
> > at org.apache.drill.exec.rpc.user.UserClient.handle(UserClient.java:422)
> > [drill-java-exec-1.16.0.jar:1.16.0]
> > at org.apache.drill.exec.rpc.user.UserClient.handle(UserClient.java:96)
> > [drill-java-exec-1.16.0.jar:1.16.0]
> > at org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:273)
> > [drill-rpc-1.16.0.jar:1.16.0]
> > at org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:243)
> > [drill-rpc-1.16.0.jar:1.16.0]
> > at
> > io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:88)
> > [netty-codec-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:287)
> > [netty-handler-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:102)
> > [netty-codec-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:312)
> > [netty-codec-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:286)
> > [netty-codec-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:86)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1294)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:911)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:131)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:645)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:580)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:497)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:459)
> > [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> > at
> > io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:131)
> > [netty-common-4.0.48.Final.jar:4.0.48.Final]
> > at java.lang.Thread.run(Thread.java:748) [na:1.8.0_151]
> > java.sql.SQLException: Value '0000-00-00' can not be represented as
> > java.sql.Date
> > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
> > ~[mysql-connector-java.jar:5.1.48]
> > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
> > ~[mysql-connector-java.jar:5.1.48]
> > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
> > ~[mysql-connector-java.jar:5.1.48]
> > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
> > ~[mysql-connector-java.jar:5.1.48]
> > at com.mysql.jdbc.ResultSetRow.getDateFast(ResultSetRow.java:146)
> > ~[mysql-connector-java.jar:5.1.48]
> > at com.mysql.jdbc.ByteArrayRow.getDateFast(ByteArrayRow.java:244)
> > ~[mysql-connector-java.jar:5.1.48]
> > at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2011)
> > ~[mysql-connector-java.jar:5.1.48]
> > at
> > org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488)
> > ~[commons-dbcp2-2.5.0.jar:2.5.0]
> > at
> > org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488)
> > ~[commons-dbcp2-2.5.0.jar:2.5.0]
> > at
> > org.apache.drill.exec.store.jdbc.JdbcRecordReader$DateCopier.copy(JdbcRecordReader.java:440)
> > ~[drill-jdbc-storage-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.store.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:271)
> > ~[drill-jdbc-storage-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.physical.impl.ScanBatch.internalNext(ScanBatch.java:223)
> > ~[drill-java-exec-1.16.0.jar:1.16.0]
> > at org.apache.drill.exec.physical.impl.ScanBatch.next(ScanBatch.java:271)
> > ~[drill-java-exec-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:126)
> > ~[drill-java-exec-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:116)
> > ~[drill-java-exec-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.record.AbstractUnaryRecordBatch.innerNext(AbstractUnaryRecordBatch.java:63)
> > ~[drill-java-exec-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:141)
> > ~[drill-java-exec-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:186)
> > ~[drill-java-exec-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:104)
> > ~[drill-java-exec-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext(ScreenCreator.java:83)
> > ~[drill-java-exec-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:94)
> > ~[drill-java-exec-1.16.0.jar:1.16.0]
> > at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:296)
> > ~[drill-java-exec-1.16.0.jar:1.16.0]
> > at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:283)
> > ~[drill-java-exec-1.16.0.jar:1.16.0]
> > at .......(:0) ~[na:na]
> > at
> > org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1746)
> > ~[hadoop-common-2.7.4.jar:na]
> > at org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:283)
> > ~[drill-java-exec-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.common.SelfCleaningRunnable.run(SelfCleaningRunnable.java:38)
> > ~[drill-common-1.16.0.jar:1.16.0]
> > at .......(:0) ~[na:na]
> >
> > the logs in drillbit.log file are :
> > 2019-11-28 10:01:03,217 [22206520-3db3-ddd0-72c1-5ab0990a73a4:foreman]
> > INFO o.a.drill.exec.work.foreman.Foreman - Query text for query with id
> > 22206520-3db3-ddd0-72c1-5ab0990a73a4 issued by drill: select * from ASSOC
> > 2019-11-28 10:01:04,111 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> > INFO o.a.d.e.w.fragment.FragmentExecutor -
> > 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State change requested
> > AWAITING_ALLOCATION --> RUNNING
> > 2019-11-28 10:01:04,111 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> > INFO o.a.d.e.w.f.FragmentStatusReporter -
> > 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State to report: RUNNING
> > 2019-11-28 10:01:04,132 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> > INFO o.a.d.e.store.jdbc.JdbcRecordReader - User Error Occurred: Failure
> > while attempting to read from database. (Value '0000-00-00' can not be
> > represented as java.sql.Date)
> > org.apache.drill.common.exceptions.UserException: DATA_READ ERROR: Failure
> > while attempting to read from database.
> >
> > sql SELECT *
> > FROM `openisjb_demo`.`ASSOC`
> > plugin mysql
> >
> > [Error Id: 12adbd85-48b4-4b1d-a29b-0e8ab0d34d54 ]
> > at
> > org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:630)
> > ~[drill-common-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.store.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:281)
> > [drill-jdbc-storage-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.physical.impl.ScanBatch.internalNext(ScanBatch.java:223)
> > [drill-java-exec-1.16.0.jar:1.16.0]
> > at org.apache.drill.exec.physical.impl.ScanBatch.next(ScanBatch.java:271)
> > [drill-java-exec-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:126)
> > [drill-java-exec-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:116)
> > [drill-java-exec-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.record.AbstractUnaryRecordBatch.innerNext(AbstractUnaryRecordBatch.java:63)
> > [drill-java-exec-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:141)
> > [drill-java-exec-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:186)
> > [drill-java-exec-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:104)
> > [drill-java-exec-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext(ScreenCreator.java:83)
> > [drill-java-exec-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:94)
> > [drill-java-exec-1.16.0.jar:1.16.0]
> > at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:296)
> > [drill-java-exec-1.16.0.jar:1.16.0]
> > at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:283)
> > [drill-java-exec-1.16.0.jar:1.16.0]
> > at java.security.AccessController.doPrivileged(Native Method)
> > [na:1.8.0_151]
> > at javax.security.auth.Subject.doAs(Subject.java:422) [na:1.8.0_151]
> > at
> > org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1746)
> > [hadoop-common-2.7.4.jar:na]
> > at org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:283)
> > [drill-java-exec-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.common.SelfCleaningRunnable.run(SelfCleaningRunnable.java:38)
> > [drill-common-1.16.0.jar:1.16.0]
> > at
> > java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> > [na:1.8.0_151]
> > at
> > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> > [na:1.8.0_151]
> > at java.lang.Thread.run(Thread.java:748) [na:1.8.0_151]
> > Caused by: java.sql.SQLException: Value '0000-00-00' can not be
> > represented as java.sql.Date
> > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
> > ~[mysql-connector-java.jar:5.1.48]
> > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
> > ~[mysql-connector-java.jar:5.1.48]
> > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
> > ~[mysql-connector-java.jar:5.1.48]
> > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
> > ~[mysql-connector-java.jar:5.1.48]
> > at com.mysql.jdbc.ResultSetRow.getDateFast(ResultSetRow.java:146)
> > ~[mysql-connector-java.jar:5.1.48]
> > at com.mysql.jdbc.ByteArrayRow.getDateFast(ByteArrayRow.java:244)
> > ~[mysql-connector-java.jar:5.1.48]
> > at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2011)
> > ~[mysql-connector-java.jar:5.1.48]
> > at
> > org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488)
> > ~[commons-dbcp2-2.5.0.jar:2.5.0]
> > at
> > org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488)
> > ~[commons-dbcp2-2.5.0.jar:2.5.0]
> > at
> > org.apache.drill.exec.store.jdbc.JdbcRecordReader$DateCopier.copy(JdbcRecordReader.java:440)
> > ~[drill-jdbc-storage-1.16.0.jar:1.16.0]
> > at
> > org.apache.drill.exec.store.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:271)
> > [drill-jdbc-storage-1.16.0.jar:1.16.0]
> > ... 20 common frames omitted
> > 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> > INFO o.a.d.e.w.fragment.FragmentExecutor -
> > 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State change requested RUNNING
> > --> FAILED
> > 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> > ERROR o.a.d.e.physical.impl.BaseRootExec - Batch dump started: dumping last
> > 2 failed batches
> > 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> > ERROR o.a.d.e.p.i.p.ProjectRecordBatch - ProjectRecordBatch[projector=null,
> > hasRemainder=false, remainderIndex=0, recordCount=0,
> > container=org.apache.drill.exec.record.VectorContainer@70742027[recordCount
> > = 0, schemaChanged = true, schema = null, wrappers = [], ...]]
> > 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> > ERROR o.a.d.exec.physical.impl.ScanBatch -
> > ScanBatch[container=org.apache.drill.exec.record.VectorContainer@76871541[recordCount
> > = 0, schemaChanged = true, schema = null, wrappers =
> > [org.apache.drill.exec.vector.NullableIntVector@ad6aa7f[field =
> > [`ASSOC_MST_ID` (INT(12, 0):OPTIONAL)], ...],
> > org.apache.drill.exec.vector.NullableVarCharVector@1292045f[field =
> > [`ASSOC_MST_ROLE_CD` (VARCHAR(1, 0):OPTIONAL)], ...],
> > org.apache.drill.exec.vector.NullableVarCharVector@43367b32[field =
> > [`ASSOC_MST_LNAME` (VARCHAR(25, 0):OPTIONAL)], ...],
> > org.apache.drill.exec.vector.NullableVarCharVector@d554308[field =
> > [`ASSOC_MST_FNAME` (VARCHAR(25, 0):OPTIONAL)], ...],
> > org.apache.drill.exec.vector.NullableVarCharVector@32ef958f[field =
> > [`ASSOC_MST_MID_INIT` (VARCHAR(1, 0):OPTIONAL)], ...],
> > org.apache.drill.exec.vector.NullableVarCharVector@305ddf71[field =
> > [`ASSOC_MST_P_ADDR` (VARCHAR(90, 0):OPTIONAL)], ...],
> > org.apache.drill.exec.vect
> > or.NullableVarCharVector@4042dda3[field = [`ASSOC_MST_E_ADDR`
> > (VARCHAR(50, 0):OPTIONAL)], ...],
> > org.apache.drill.exec.vector.NullableIntVector@67a88ba0[field =
> > [`ASSOC_MST_PHONE` (INT(10, 0):OPTIONAL)], ...],
> > org.apache.drill.exec.vector.NullableDateVector@29a52937[field =
> > [`ASSOC_MST_ST_DT` (DATE(10, 0):OPTIONAL)], ...],
> > org.apache.drill.exec.vector.NullableIntVector@77dddd25[field =
> > [`ASSOC_MST_PAY_RT` (INT(11, 0):OPTIONAL)], ...],
> > org.apache.drill.exec.vector.NullableVarCharVector@21a4a9b8[field =
> > [`ASSOC_MST_SSN` (VARCHAR(9, 0):OPTIONAL)], ...],
> > org.apache.drill.exec.vector.NullableIntVector@272d3a7[field =
> > [`ASSOC_MST_ORG_GRP_ID` (INT(10, 0):OPTIONAL)], ...],
> > org.apache.drill.exec.vector.NullableIntVector@29eae4f[field = [`id`
> > (INT(12, 0):OPTIONAL)], ...]], ...],
> > currentReader=JdbcRecordReader[sql=SELECT *
> > FROM `openisjb_demo`.`ASSOC`, Plugin=mysql], schema=null]
> > 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> > ERROR o.a.d.e.physical.impl.BaseRootExec - Batch dump completed.
> > 2019-11-28 10:01:04,134 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> > INFO o.a.d.e.w.fragment.FragmentExecutor -
> > 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State change requested FAILED -->
> > FINISHED
> > 2019-11-28 10:01:04,139 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> > WARN o.a.d.exec.rpc.control.WorkEventBus - Fragment
> > 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0 manager is not found in the work
> > bus.
> > 2019-11-28 10:01:04,148 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> > WARN o.a.d.e.w.f.QueryStateProcessor - Dropping request to move to
> > COMPLETED state as query is already at FAILED state (which is terminal).
> >
> > is there any way to configure the data types to be used in apache drill or
> > any alternate solutions instead of changing schema of the table in
> > datasources.
> > Thanks and regards
> > Rameshwar Mane
> > Big Data Engineer
>
>

Re: not able read few tables form mysql due to date type format error

Posted by Igor Guzenko <ih...@gmail.com>.

Hello Charles,

Always glad to help. Please post, no problems.
One minor note that this specific issue more related to mysql & jdbc rather
than Drill.

Thanks,
Igor

On Thu, Nov 28, 2019 at 5:29 PM Charles Givre <cg...@gmail.com> wrote:

> @Igor,
> Thanks for posting this.  Can we post this solution to the Drill
> documentation as I would imagine it would benefit others who run into the
> same issue?
> Thanks,
> -- C
>
>
>
> > On Nov 28, 2019, at 9:42 AM, Igor Guzenko <ih...@gmail.com>
> wrote:
> >
> > Hello Rameshwar,
> >
> > It seems like you have issue with date conversion for one of columns:
> >
> > Caused by: java.sql.SQLException: Value '0000-00-00' can not be
> represented
> > as java.sql.Date.
> >
> > I believe you could try options suggested here:
> >
> https://stackoverflow.com/questions/17195343/value-0000-00-00-can-not-be-represented-as-java-sql-date/17196386
> >
> > Thanks,
> > Igor
> >
> > On Thu, Nov 28, 2019 at 2:07 PM Rameshwar Mane <mr...@gmail.com>
> wrote:
> >
> >> Hi,
> >> I was trying to query my mysql table registered in apache drill. I am
> >> facing the following error while trying to execute query.
> >> the error in the terminal was:
> >>
> >> Error: DATA_READ ERROR: Failure while attempting to read from database.
> >>
> >> sql SELECT *
> >> FROM `openisjb_demo`.`ASSOC`
> >> plugin mysql
> >> Fragment 0:0
> >>
> >> [Error Id: 12adbd85-48b4-4b1d-a29b-0e8ab0d34d54 on xxxxxxxxxxxx:31010]
> >> (state=,code=0)
> >>
> >> the logs in sqlline.log file are :
> >>
> >> 2019-11-28 10:15:13,679 [Client-1] INFO
> >> o.a.d.j.i.DrillCursor$ResultsListener - [#6] Query failed:
> >>
> >> org.apache.drill.common.exceptions.UserRemoteException: DATA_READ ERROR:
> >> Failure while attempting to read from database.
> >>
> >> sql SELECT *
> >> FROM `openisjb_demo`.`ASSOC`
> >> plugin mysql
> >> Fragment 0:0
> >>
> >> [Error Id: 67b0e4e1-18d3-4155-8537-021806c88858 on
> >> xxxxxxxxxxxxxxxxxxxx:31010]
> >> at
> >>
> org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(QueryResultHandler.java:123)
> >> [drill-java-exec-1.16.0.jar:1.16.0]
> >> at org.apache.drill.exec.rpc.user.UserClient.handle(UserClient.java:422)
> >> [drill-java-exec-1.16.0.jar:1.16.0]
> >> at org.apache.drill.exec.rpc.user.UserClient.handle(UserClient.java:96)
> >> [drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:273)
> >> [drill-rpc-1.16.0.jar:1.16.0]
> >> at
> org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:243)
> >> [drill-rpc-1.16.0.jar:1.16.0]
> >> at
> >>
> io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:88)
> >> [netty-codec-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:287)
> >> [netty-handler-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:102)
> >> [netty-codec-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:312)
> >> [netty-codec-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:286)
> >> [netty-codec-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:86)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1294)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:911)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:131)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:645)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:580)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:497)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:459)
> >> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> >> at
> >>
> io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:131)
> >> [netty-common-4.0.48.Final.jar:4.0.48.Final]
> >> at java.lang.Thread.run(Thread.java:748) [na:1.8.0_151]
> >> java.sql.SQLException: Value '0000-00-00' can not be represented as
> >> java.sql.Date
> >> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
> >> ~[mysql-connector-java.jar:5.1.48]
> >> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
> >> ~[mysql-connector-java.jar:5.1.48]
> >> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
> >> ~[mysql-connector-java.jar:5.1.48]
> >> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
> >> ~[mysql-connector-java.jar:5.1.48]
> >> at com.mysql.jdbc.ResultSetRow.getDateFast(ResultSetRow.java:146)
> >> ~[mysql-connector-java.jar:5.1.48]
> >> at com.mysql.jdbc.ByteArrayRow.getDateFast(ByteArrayRow.java:244)
> >> ~[mysql-connector-java.jar:5.1.48]
> >> at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2011)
> >> ~[mysql-connector-java.jar:5.1.48]
> >> at
> >>
> org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488)
> >> ~[commons-dbcp2-2.5.0.jar:2.5.0]
> >> at
> >>
> org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488)
> >> ~[commons-dbcp2-2.5.0.jar:2.5.0]
> >> at
> >>
> org.apache.drill.exec.store.jdbc.JdbcRecordReader$DateCopier.copy(JdbcRecordReader.java:440)
> >> ~[drill-jdbc-storage-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.store.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:271)
> >> ~[drill-jdbc-storage-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.ScanBatch.internalNext(ScanBatch.java:223)
> >> ~[drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> org.apache.drill.exec.physical.impl.ScanBatch.next(ScanBatch.java:271)
> >> ~[drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:126)
> >> ~[drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:116)
> >> ~[drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractUnaryRecordBatch.innerNext(AbstractUnaryRecordBatch.java:63)
> >> ~[drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:141)
> >> ~[drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:186)
> >> ~[drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:104)
> >> ~[drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext(ScreenCreator.java:83)
> >> ~[drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:94)
> >> ~[drill-java-exec-1.16.0.jar:1.16.0]
> >> at org.apache.drill.exec.work
> .fragment.FragmentExecutor$1.run(FragmentExecutor.java:296)
> >> ~[drill-java-exec-1.16.0.jar:1.16.0]
> >> at org.apache.drill.exec.work
> .fragment.FragmentExecutor$1.run(FragmentExecutor.java:283)
> >> ~[drill-java-exec-1.16.0.jar:1.16.0]
> >> at .......(:0) ~[na:na]
> >> at
> >>
> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1746)
> >> ~[hadoop-common-2.7.4.jar:na]
> >> at org.apache.drill.exec.work
> .fragment.FragmentExecutor.run(FragmentExecutor.java:283)
> >> ~[drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.common.SelfCleaningRunnable.run(SelfCleaningRunnable.java:38)
> >> ~[drill-common-1.16.0.jar:1.16.0]
> >> at .......(:0) ~[na:na]
> >>
> >> the logs in drillbit.log file are :
> >>
> >> 2019-11-28 10:01:03,217 [22206520-3db3-ddd0-72c1-5ab0990a73a4:foreman]
> >> INFO o.a.drill.exec.work.foreman.Foreman - Query text for query with id
> >> 22206520-3db3-ddd0-72c1-5ab0990a73a4 issued by drill: select * from
> ASSOC
> >> 2019-11-28 10:01:04,111 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> >> INFO o.a.d.e.w.fragment.FragmentExecutor -
> >> 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State change requested
> >> AWAITING_ALLOCATION --> RUNNING
> >> 2019-11-28 10:01:04,111 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> >> INFO o.a.d.e.w.f.FragmentStatusReporter -
> >> 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State to report: RUNNING
> >> 2019-11-28 10:01:04,132 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> >> INFO o.a.d.e.store.jdbc.JdbcRecordReader - User Error Occurred: Failure
> >> while attempting to read from database. (Value '0000-00-00' can not be
> >> represented as java.sql.Date)
> >> org.apache.drill.common.exceptions.UserException: DATA_READ ERROR:
> Failure
> >> while attempting to read from database.
> >>
> >> sql SELECT *
> >> FROM `openisjb_demo`.`ASSOC`
> >> plugin mysql
> >>
> >> [Error Id: 12adbd85-48b4-4b1d-a29b-0e8ab0d34d54 ]
> >> at
> >>
> org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:630)
> >> ~[drill-common-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.store.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:281)
> >> [drill-jdbc-storage-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.ScanBatch.internalNext(ScanBatch.java:223)
> >> [drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> org.apache.drill.exec.physical.impl.ScanBatch.next(ScanBatch.java:271)
> >> [drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:126)
> >> [drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:116)
> >> [drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractUnaryRecordBatch.innerNext(AbstractUnaryRecordBatch.java:63)
> >> [drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:141)
> >> [drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:186)
> >> [drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:104)
> >> [drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext(ScreenCreator.java:83)
> >> [drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:94)
> >> [drill-java-exec-1.16.0.jar:1.16.0]
> >> at org.apache.drill.exec.work
> .fragment.FragmentExecutor$1.run(FragmentExecutor.java:296)
> >> [drill-java-exec-1.16.0.jar:1.16.0]
> >> at org.apache.drill.exec.work
> .fragment.FragmentExecutor$1.run(FragmentExecutor.java:283)
> >> [drill-java-exec-1.16.0.jar:1.16.0]
> >> at java.security.AccessController.doPrivileged(Native Method)
> >> [na:1.8.0_151]
> >> at javax.security.auth.Subject.doAs(Subject.java:422) [na:1.8.0_151]
> >> at
> >>
> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1746)
> >> [hadoop-common-2.7.4.jar:na]
> >> at org.apache.drill.exec.work
> .fragment.FragmentExecutor.run(FragmentExecutor.java:283)
> >> [drill-java-exec-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.common.SelfCleaningRunnable.run(SelfCleaningRunnable.java:38)
> >> [drill-common-1.16.0.jar:1.16.0]
> >> at
> >>
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> >> [na:1.8.0_151]
> >> at
> >>
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> >> [na:1.8.0_151]
> >> at java.lang.Thread.run(Thread.java:748) [na:1.8.0_151]
> >> Caused by: java.sql.SQLException: Value '0000-00-00' can not be
> >> represented as java.sql.Date
> >> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
> >> ~[mysql-connector-java.jar:5.1.48]
> >> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
> >> ~[mysql-connector-java.jar:5.1.48]
> >> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
> >> ~[mysql-connector-java.jar:5.1.48]
> >> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
> >> ~[mysql-connector-java.jar:5.1.48]
> >> at com.mysql.jdbc.ResultSetRow.getDateFast(ResultSetRow.java:146)
> >> ~[mysql-connector-java.jar:5.1.48]
> >> at com.mysql.jdbc.ByteArrayRow.getDateFast(ByteArrayRow.java:244)
> >> ~[mysql-connector-java.jar:5.1.48]
> >> at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2011)
> >> ~[mysql-connector-java.jar:5.1.48]
> >> at
> >>
> org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488)
> >> ~[commons-dbcp2-2.5.0.jar:2.5.0]
> >> at
> >>
> org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488)
> >> ~[commons-dbcp2-2.5.0.jar:2.5.0]
> >> at
> >>
> org.apache.drill.exec.store.jdbc.JdbcRecordReader$DateCopier.copy(JdbcRecordReader.java:440)
> >> ~[drill-jdbc-storage-1.16.0.jar:1.16.0]
> >> at
> >>
> org.apache.drill.exec.store.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:271)
> >> [drill-jdbc-storage-1.16.0.jar:1.16.0]
> >> ... 20 common frames omitted
> >> 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> >> INFO o.a.d.e.w.fragment.FragmentExecutor -
> >> 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State change requested RUNNING
> >> --> FAILED
> >> 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> >> ERROR o.a.d.e.physical.impl.BaseRootExec - Batch dump started: dumping
> last
> >> 2 failed batches
> >> 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> >> ERROR o.a.d.e.p.i.p.ProjectRecordBatch -
> ProjectRecordBatch[projector=null,
> >> hasRemainder=false, remainderIndex=0, recordCount=0,
> >> container=org.apache.drill.exec.record.VectorContainer@70742027
> [recordCount
> >> = 0, schemaChanged = true, schema = null, wrappers = [], ...]]
> >> 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> >> ERROR o.a.d.exec.physical.impl.ScanBatch -
> >>
> ScanBatch[container=org.apache.drill.exec.record.VectorContainer@76871541
> [recordCount
> >> = 0, schemaChanged = true, schema = null, wrappers =
> >> [org.apache.drill.exec.vector.NullableIntVector@ad6aa7f[field =
> >> [`ASSOC_MST_ID` (INT(12, 0):OPTIONAL)], ...],
> >> org.apache.drill.exec.vector.NullableVarCharVector@1292045f[field =
> >> [`ASSOC_MST_ROLE_CD` (VARCHAR(1, 0):OPTIONAL)], ...],
> >> org.apache.drill.exec.vector.NullableVarCharVector@43367b32[field =
> >> [`ASSOC_MST_LNAME` (VARCHAR(25, 0):OPTIONAL)], ...],
> >> org.apache.drill.exec.vector.NullableVarCharVector@d554308[field =
> >> [`ASSOC_MST_FNAME` (VARCHAR(25, 0):OPTIONAL)], ...],
> >> org.apache.drill.exec.vector.NullableVarCharVector@32ef958f[field =
> >> [`ASSOC_MST_MID_INIT` (VARCHAR(1, 0):OPTIONAL)], ...],
> >> org.apache.drill.exec.vector.NullableVarCharVector@305ddf71[field =
> >> [`ASSOC_MST_P_ADDR` (VARCHAR(90, 0):OPTIONAL)], ...],
> >> org.apache.drill.exec.vect
> >> or.NullableVarCharVector@4042dda3[field = [`ASSOC_MST_E_ADDR`
> >> (VARCHAR(50, 0):OPTIONAL)], ...],
> >> org.apache.drill.exec.vector.NullableIntVector@67a88ba0[field =
> >> [`ASSOC_MST_PHONE` (INT(10, 0):OPTIONAL)], ...],
> >> org.apache.drill.exec.vector.NullableDateVector@29a52937[field =
> >> [`ASSOC_MST_ST_DT` (DATE(10, 0):OPTIONAL)], ...],
> >> org.apache.drill.exec.vector.NullableIntVector@77dddd25[field =
> >> [`ASSOC_MST_PAY_RT` (INT(11, 0):OPTIONAL)], ...],
> >> org.apache.drill.exec.vector.NullableVarCharVector@21a4a9b8[field =
> >> [`ASSOC_MST_SSN` (VARCHAR(9, 0):OPTIONAL)], ...],
> >> org.apache.drill.exec.vector.NullableIntVector@272d3a7[field =
> >> [`ASSOC_MST_ORG_GRP_ID` (INT(10, 0):OPTIONAL)], ...],
> >> org.apache.drill.exec.vector.NullableIntVector@29eae4f[field = [`id`
> >> (INT(12, 0):OPTIONAL)], ...]], ...],
> >> currentReader=JdbcRecordReader[sql=SELECT *
> >> FROM `openisjb_demo`.`ASSOC`, Plugin=mysql], schema=null]
> >> 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> >> ERROR o.a.d.e.physical.impl.BaseRootExec - Batch dump completed.
> >> 2019-11-28 10:01:04,134 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> >> INFO o.a.d.e.w.fragment.FragmentExecutor -
> >> 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State change requested FAILED
> -->
> >> FINISHED
> >> 2019-11-28 10:01:04,139 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> >> WARN o.a.d.exec.rpc.control.WorkEventBus - Fragment
> >> 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0 manager is not found in the
> work
> >> bus.
> >> 2019-11-28 10:01:04,148 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> >> WARN o.a.d.e.w.f.QueryStateProcessor - Dropping request to move to
> >> COMPLETED state as query is already at FAILED state (which is terminal).
> >>
> >> is there any way to configure the data types to be used in apache drill
> or
> >> any alternate solutions instead of changing schema of the table in
> >> datasources.
> >> Thanks and regards
> >> Rameshwar Mane
> >> Big Data Engineer
> >>
>
>

Re: not able read few tables form mysql due to date type format error

Posted by Charles Givre <cg...@gmail.com>.

@Igor, 
Thanks for posting this.  Can we post this solution to the Drill documentation as I would imagine it would benefit others who run into the same issue?
Thanks,
-- C



> On Nov 28, 2019, at 9:42 AM, Igor Guzenko <ih...@gmail.com> wrote:
> 
> Hello Rameshwar,
> 
> It seems like you have issue with date conversion for one of columns:
> 
> Caused by: java.sql.SQLException: Value '0000-00-00' can not be represented
> as java.sql.Date.
> 
> I believe you could try options suggested here:
> https://stackoverflow.com/questions/17195343/value-0000-00-00-can-not-be-represented-as-java-sql-date/17196386
> 
> Thanks,
> Igor
> 
> On Thu, Nov 28, 2019 at 2:07 PM Rameshwar Mane <mr...@gmail.com> wrote:
> 
>> Hi,
>> I was trying to query my mysql table registered in apache drill. I am
>> facing the following error while trying to execute query.
>> the error in the terminal was:
>> 
>> Error: DATA_READ ERROR: Failure while attempting to read from database.
>> 
>> sql SELECT *
>> FROM `openisjb_demo`.`ASSOC`
>> plugin mysql
>> Fragment 0:0
>> 
>> [Error Id: 12adbd85-48b4-4b1d-a29b-0e8ab0d34d54 on xxxxxxxxxxxx:31010]
>> (state=,code=0)
>> 
>> the logs in sqlline.log file are :
>> 
>> 2019-11-28 10:15:13,679 [Client-1] INFO
>> o.a.d.j.i.DrillCursor$ResultsListener - [#6] Query failed:
>> 
>> org.apache.drill.common.exceptions.UserRemoteException: DATA_READ ERROR:
>> Failure while attempting to read from database.
>> 
>> sql SELECT *
>> FROM `openisjb_demo`.`ASSOC`
>> plugin mysql
>> Fragment 0:0
>> 
>> [Error Id: 67b0e4e1-18d3-4155-8537-021806c88858 on
>> xxxxxxxxxxxxxxxxxxxx:31010]
>> at
>> org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(QueryResultHandler.java:123)
>> [drill-java-exec-1.16.0.jar:1.16.0]
>> at org.apache.drill.exec.rpc.user.UserClient.handle(UserClient.java:422)
>> [drill-java-exec-1.16.0.jar:1.16.0]
>> at org.apache.drill.exec.rpc.user.UserClient.handle(UserClient.java:96)
>> [drill-java-exec-1.16.0.jar:1.16.0]
>> at org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:273)
>> [drill-rpc-1.16.0.jar:1.16.0]
>> at org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:243)
>> [drill-rpc-1.16.0.jar:1.16.0]
>> at
>> io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:88)
>> [netty-codec-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:287)
>> [netty-handler-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:102)
>> [netty-codec-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:312)
>> [netty-codec-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:286)
>> [netty-codec-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:86)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1294)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:911)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:131)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:645)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:580)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:497)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:459)
>> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
>> at
>> io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:131)
>> [netty-common-4.0.48.Final.jar:4.0.48.Final]
>> at java.lang.Thread.run(Thread.java:748) [na:1.8.0_151]
>> java.sql.SQLException: Value '0000-00-00' can not be represented as
>> java.sql.Date
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
>> ~[mysql-connector-java.jar:5.1.48]
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
>> ~[mysql-connector-java.jar:5.1.48]
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
>> ~[mysql-connector-java.jar:5.1.48]
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
>> ~[mysql-connector-java.jar:5.1.48]
>> at com.mysql.jdbc.ResultSetRow.getDateFast(ResultSetRow.java:146)
>> ~[mysql-connector-java.jar:5.1.48]
>> at com.mysql.jdbc.ByteArrayRow.getDateFast(ByteArrayRow.java:244)
>> ~[mysql-connector-java.jar:5.1.48]
>> at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2011)
>> ~[mysql-connector-java.jar:5.1.48]
>> at
>> org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488)
>> ~[commons-dbcp2-2.5.0.jar:2.5.0]
>> at
>> org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488)
>> ~[commons-dbcp2-2.5.0.jar:2.5.0]
>> at
>> org.apache.drill.exec.store.jdbc.JdbcRecordReader$DateCopier.copy(JdbcRecordReader.java:440)
>> ~[drill-jdbc-storage-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.store.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:271)
>> ~[drill-jdbc-storage-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.physical.impl.ScanBatch.internalNext(ScanBatch.java:223)
>> ~[drill-java-exec-1.16.0.jar:1.16.0]
>> at org.apache.drill.exec.physical.impl.ScanBatch.next(ScanBatch.java:271)
>> ~[drill-java-exec-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:126)
>> ~[drill-java-exec-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:116)
>> ~[drill-java-exec-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.record.AbstractUnaryRecordBatch.innerNext(AbstractUnaryRecordBatch.java:63)
>> ~[drill-java-exec-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:141)
>> ~[drill-java-exec-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:186)
>> ~[drill-java-exec-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:104)
>> ~[drill-java-exec-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext(ScreenCreator.java:83)
>> ~[drill-java-exec-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:94)
>> ~[drill-java-exec-1.16.0.jar:1.16.0]
>> at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:296)
>> ~[drill-java-exec-1.16.0.jar:1.16.0]
>> at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:283)
>> ~[drill-java-exec-1.16.0.jar:1.16.0]
>> at .......(:0) ~[na:na]
>> at
>> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1746)
>> ~[hadoop-common-2.7.4.jar:na]
>> at org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:283)
>> ~[drill-java-exec-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.common.SelfCleaningRunnable.run(SelfCleaningRunnable.java:38)
>> ~[drill-common-1.16.0.jar:1.16.0]
>> at .......(:0) ~[na:na]
>> 
>> the logs in drillbit.log file are :
>> 
>> 2019-11-28 10:01:03,217 [22206520-3db3-ddd0-72c1-5ab0990a73a4:foreman]
>> INFO o.a.drill.exec.work.foreman.Foreman - Query text for query with id
>> 22206520-3db3-ddd0-72c1-5ab0990a73a4 issued by drill: select * from ASSOC
>> 2019-11-28 10:01:04,111 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
>> INFO o.a.d.e.w.fragment.FragmentExecutor -
>> 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State change requested
>> AWAITING_ALLOCATION --> RUNNING
>> 2019-11-28 10:01:04,111 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
>> INFO o.a.d.e.w.f.FragmentStatusReporter -
>> 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State to report: RUNNING
>> 2019-11-28 10:01:04,132 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
>> INFO o.a.d.e.store.jdbc.JdbcRecordReader - User Error Occurred: Failure
>> while attempting to read from database. (Value '0000-00-00' can not be
>> represented as java.sql.Date)
>> org.apache.drill.common.exceptions.UserException: DATA_READ ERROR: Failure
>> while attempting to read from database.
>> 
>> sql SELECT *
>> FROM `openisjb_demo`.`ASSOC`
>> plugin mysql
>> 
>> [Error Id: 12adbd85-48b4-4b1d-a29b-0e8ab0d34d54 ]
>> at
>> org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:630)
>> ~[drill-common-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.store.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:281)
>> [drill-jdbc-storage-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.physical.impl.ScanBatch.internalNext(ScanBatch.java:223)
>> [drill-java-exec-1.16.0.jar:1.16.0]
>> at org.apache.drill.exec.physical.impl.ScanBatch.next(ScanBatch.java:271)
>> [drill-java-exec-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:126)
>> [drill-java-exec-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:116)
>> [drill-java-exec-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.record.AbstractUnaryRecordBatch.innerNext(AbstractUnaryRecordBatch.java:63)
>> [drill-java-exec-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:141)
>> [drill-java-exec-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:186)
>> [drill-java-exec-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:104)
>> [drill-java-exec-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext(ScreenCreator.java:83)
>> [drill-java-exec-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:94)
>> [drill-java-exec-1.16.0.jar:1.16.0]
>> at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:296)
>> [drill-java-exec-1.16.0.jar:1.16.0]
>> at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:283)
>> [drill-java-exec-1.16.0.jar:1.16.0]
>> at java.security.AccessController.doPrivileged(Native Method)
>> [na:1.8.0_151]
>> at javax.security.auth.Subject.doAs(Subject.java:422) [na:1.8.0_151]
>> at
>> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1746)
>> [hadoop-common-2.7.4.jar:na]
>> at org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:283)
>> [drill-java-exec-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.common.SelfCleaningRunnable.run(SelfCleaningRunnable.java:38)
>> [drill-common-1.16.0.jar:1.16.0]
>> at
>> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
>> [na:1.8.0_151]
>> at
>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
>> [na:1.8.0_151]
>> at java.lang.Thread.run(Thread.java:748) [na:1.8.0_151]
>> Caused by: java.sql.SQLException: Value '0000-00-00' can not be
>> represented as java.sql.Date
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
>> ~[mysql-connector-java.jar:5.1.48]
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
>> ~[mysql-connector-java.jar:5.1.48]
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
>> ~[mysql-connector-java.jar:5.1.48]
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
>> ~[mysql-connector-java.jar:5.1.48]
>> at com.mysql.jdbc.ResultSetRow.getDateFast(ResultSetRow.java:146)
>> ~[mysql-connector-java.jar:5.1.48]
>> at com.mysql.jdbc.ByteArrayRow.getDateFast(ByteArrayRow.java:244)
>> ~[mysql-connector-java.jar:5.1.48]
>> at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2011)
>> ~[mysql-connector-java.jar:5.1.48]
>> at
>> org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488)
>> ~[commons-dbcp2-2.5.0.jar:2.5.0]
>> at
>> org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488)
>> ~[commons-dbcp2-2.5.0.jar:2.5.0]
>> at
>> org.apache.drill.exec.store.jdbc.JdbcRecordReader$DateCopier.copy(JdbcRecordReader.java:440)
>> ~[drill-jdbc-storage-1.16.0.jar:1.16.0]
>> at
>> org.apache.drill.exec.store.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:271)
>> [drill-jdbc-storage-1.16.0.jar:1.16.0]
>> ... 20 common frames omitted
>> 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
>> INFO o.a.d.e.w.fragment.FragmentExecutor -
>> 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State change requested RUNNING
>> --> FAILED
>> 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
>> ERROR o.a.d.e.physical.impl.BaseRootExec - Batch dump started: dumping last
>> 2 failed batches
>> 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
>> ERROR o.a.d.e.p.i.p.ProjectRecordBatch - ProjectRecordBatch[projector=null,
>> hasRemainder=false, remainderIndex=0, recordCount=0,
>> container=org.apache.drill.exec.record.VectorContainer@70742027[recordCount
>> = 0, schemaChanged = true, schema = null, wrappers = [], ...]]
>> 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
>> ERROR o.a.d.exec.physical.impl.ScanBatch -
>> ScanBatch[container=org.apache.drill.exec.record.VectorContainer@76871541[recordCount
>> = 0, schemaChanged = true, schema = null, wrappers =
>> [org.apache.drill.exec.vector.NullableIntVector@ad6aa7f[field =
>> [`ASSOC_MST_ID` (INT(12, 0):OPTIONAL)], ...],
>> org.apache.drill.exec.vector.NullableVarCharVector@1292045f[field =
>> [`ASSOC_MST_ROLE_CD` (VARCHAR(1, 0):OPTIONAL)], ...],
>> org.apache.drill.exec.vector.NullableVarCharVector@43367b32[field =
>> [`ASSOC_MST_LNAME` (VARCHAR(25, 0):OPTIONAL)], ...],
>> org.apache.drill.exec.vector.NullableVarCharVector@d554308[field =
>> [`ASSOC_MST_FNAME` (VARCHAR(25, 0):OPTIONAL)], ...],
>> org.apache.drill.exec.vector.NullableVarCharVector@32ef958f[field =
>> [`ASSOC_MST_MID_INIT` (VARCHAR(1, 0):OPTIONAL)], ...],
>> org.apache.drill.exec.vector.NullableVarCharVector@305ddf71[field =
>> [`ASSOC_MST_P_ADDR` (VARCHAR(90, 0):OPTIONAL)], ...],
>> org.apache.drill.exec.vect
>> or.NullableVarCharVector@4042dda3[field = [`ASSOC_MST_E_ADDR`
>> (VARCHAR(50, 0):OPTIONAL)], ...],
>> org.apache.drill.exec.vector.NullableIntVector@67a88ba0[field =
>> [`ASSOC_MST_PHONE` (INT(10, 0):OPTIONAL)], ...],
>> org.apache.drill.exec.vector.NullableDateVector@29a52937[field =
>> [`ASSOC_MST_ST_DT` (DATE(10, 0):OPTIONAL)], ...],
>> org.apache.drill.exec.vector.NullableIntVector@77dddd25[field =
>> [`ASSOC_MST_PAY_RT` (INT(11, 0):OPTIONAL)], ...],
>> org.apache.drill.exec.vector.NullableVarCharVector@21a4a9b8[field =
>> [`ASSOC_MST_SSN` (VARCHAR(9, 0):OPTIONAL)], ...],
>> org.apache.drill.exec.vector.NullableIntVector@272d3a7[field =
>> [`ASSOC_MST_ORG_GRP_ID` (INT(10, 0):OPTIONAL)], ...],
>> org.apache.drill.exec.vector.NullableIntVector@29eae4f[field = [`id`
>> (INT(12, 0):OPTIONAL)], ...]], ...],
>> currentReader=JdbcRecordReader[sql=SELECT *
>> FROM `openisjb_demo`.`ASSOC`, Plugin=mysql], schema=null]
>> 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
>> ERROR o.a.d.e.physical.impl.BaseRootExec - Batch dump completed.
>> 2019-11-28 10:01:04,134 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
>> INFO o.a.d.e.w.fragment.FragmentExecutor -
>> 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State change requested FAILED -->
>> FINISHED
>> 2019-11-28 10:01:04,139 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
>> WARN o.a.d.exec.rpc.control.WorkEventBus - Fragment
>> 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0 manager is not found in the work
>> bus.
>> 2019-11-28 10:01:04,148 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
>> WARN o.a.d.e.w.f.QueryStateProcessor - Dropping request to move to
>> COMPLETED state as query is already at FAILED state (which is terminal).
>> 
>> is there any way to configure the data types to be used in apache drill or
>> any alternate solutions instead of changing schema of the table in
>> datasources.
>> Thanks and regards
>> Rameshwar Mane
>> Big Data Engineer
>>

Re: not able read few tables form mysql due to date type format error

Posted by Igor Guzenko <ih...@gmail.com>.

Hello Rameshwar,

It seems like you have issue with date conversion for one of columns:

Caused by: java.sql.SQLException: Value '0000-00-00' can not be represented
as java.sql.Date.

I believe you could try options suggested here:
https://stackoverflow.com/questions/17195343/value-0000-00-00-can-not-be-represented-as-java-sql-date/17196386

Thanks,
Igor

On Thu, Nov 28, 2019 at 2:07 PM Rameshwar Mane <mr...@gmail.com> wrote:

> Hi,
> I was trying to query my mysql table registered in apache drill. I am
> facing the following error while trying to execute query.
> the error in the terminal was:
>
> Error: DATA_READ ERROR: Failure while attempting to read from database.
>
> sql SELECT *
> FROM `openisjb_demo`.`ASSOC`
> plugin mysql
> Fragment 0:0
>
> [Error Id: 12adbd85-48b4-4b1d-a29b-0e8ab0d34d54 on xxxxxxxxxxxx:31010]
> (state=,code=0)
>
> the logs in sqlline.log file are :
>
> 2019-11-28 10:15:13,679 [Client-1] INFO
> o.a.d.j.i.DrillCursor$ResultsListener - [#6] Query failed:
>
> org.apache.drill.common.exceptions.UserRemoteException: DATA_READ ERROR:
> Failure while attempting to read from database.
>
> sql SELECT *
> FROM `openisjb_demo`.`ASSOC`
> plugin mysql
> Fragment 0:0
>
> [Error Id: 67b0e4e1-18d3-4155-8537-021806c88858 on
> xxxxxxxxxxxxxxxxxxxx:31010]
> at
> org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(QueryResultHandler.java:123)
> [drill-java-exec-1.16.0.jar:1.16.0]
> at org.apache.drill.exec.rpc.user.UserClient.handle(UserClient.java:422)
> [drill-java-exec-1.16.0.jar:1.16.0]
> at org.apache.drill.exec.rpc.user.UserClient.handle(UserClient.java:96)
> [drill-java-exec-1.16.0.jar:1.16.0]
> at org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:273)
> [drill-rpc-1.16.0.jar:1.16.0]
> at org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:243)
> [drill-rpc-1.16.0.jar:1.16.0]
> at
> io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:88)
> [netty-codec-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:287)
> [netty-handler-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:102)
> [netty-codec-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:312)
> [netty-codec-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:286)
> [netty-codec-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:86)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1294)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:911)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:131)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:645)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:580)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:497)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:459)
> [netty-transport-4.0.48.Final.jar:4.0.48.Final]
> at
> io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:131)
> [netty-common-4.0.48.Final.jar:4.0.48.Final]
> at java.lang.Thread.run(Thread.java:748) [na:1.8.0_151]
> java.sql.SQLException: Value '0000-00-00' can not be represented as
> java.sql.Date
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
> ~[mysql-connector-java.jar:5.1.48]
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
> ~[mysql-connector-java.jar:5.1.48]
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
> ~[mysql-connector-java.jar:5.1.48]
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
> ~[mysql-connector-java.jar:5.1.48]
> at com.mysql.jdbc.ResultSetRow.getDateFast(ResultSetRow.java:146)
> ~[mysql-connector-java.jar:5.1.48]
> at com.mysql.jdbc.ByteArrayRow.getDateFast(ByteArrayRow.java:244)
> ~[mysql-connector-java.jar:5.1.48]
> at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2011)
> ~[mysql-connector-java.jar:5.1.48]
> at
> org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488)
> ~[commons-dbcp2-2.5.0.jar:2.5.0]
> at
> org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488)
> ~[commons-dbcp2-2.5.0.jar:2.5.0]
> at
> org.apache.drill.exec.store.jdbc.JdbcRecordReader$DateCopier.copy(JdbcRecordReader.java:440)
> ~[drill-jdbc-storage-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.store.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:271)
> ~[drill-jdbc-storage-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.physical.impl.ScanBatch.internalNext(ScanBatch.java:223)
> ~[drill-java-exec-1.16.0.jar:1.16.0]
> at org.apache.drill.exec.physical.impl.ScanBatch.next(ScanBatch.java:271)
> ~[drill-java-exec-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:126)
> ~[drill-java-exec-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:116)
> ~[drill-java-exec-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.record.AbstractUnaryRecordBatch.innerNext(AbstractUnaryRecordBatch.java:63)
> ~[drill-java-exec-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:141)
> ~[drill-java-exec-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:186)
> ~[drill-java-exec-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:104)
> ~[drill-java-exec-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext(ScreenCreator.java:83)
> ~[drill-java-exec-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:94)
> ~[drill-java-exec-1.16.0.jar:1.16.0]
> at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:296)
> ~[drill-java-exec-1.16.0.jar:1.16.0]
> at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:283)
> ~[drill-java-exec-1.16.0.jar:1.16.0]
> at .......(:0) ~[na:na]
> at
> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1746)
> ~[hadoop-common-2.7.4.jar:na]
> at org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:283)
> ~[drill-java-exec-1.16.0.jar:1.16.0]
> at
> org.apache.drill.common.SelfCleaningRunnable.run(SelfCleaningRunnable.java:38)
> ~[drill-common-1.16.0.jar:1.16.0]
> at .......(:0) ~[na:na]
>
> the logs in drillbit.log file are :
>
> 2019-11-28 10:01:03,217 [22206520-3db3-ddd0-72c1-5ab0990a73a4:foreman]
> INFO o.a.drill.exec.work.foreman.Foreman - Query text for query with id
> 22206520-3db3-ddd0-72c1-5ab0990a73a4 issued by drill: select * from ASSOC
> 2019-11-28 10:01:04,111 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> INFO o.a.d.e.w.fragment.FragmentExecutor -
> 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State change requested
> AWAITING_ALLOCATION --> RUNNING
> 2019-11-28 10:01:04,111 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> INFO o.a.d.e.w.f.FragmentStatusReporter -
> 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State to report: RUNNING
> 2019-11-28 10:01:04,132 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> INFO o.a.d.e.store.jdbc.JdbcRecordReader - User Error Occurred: Failure
> while attempting to read from database. (Value '0000-00-00' can not be
> represented as java.sql.Date)
> org.apache.drill.common.exceptions.UserException: DATA_READ ERROR: Failure
> while attempting to read from database.
>
> sql SELECT *
> FROM `openisjb_demo`.`ASSOC`
> plugin mysql
>
> [Error Id: 12adbd85-48b4-4b1d-a29b-0e8ab0d34d54 ]
> at
> org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:630)
> ~[drill-common-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.store.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:281)
> [drill-jdbc-storage-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.physical.impl.ScanBatch.internalNext(ScanBatch.java:223)
> [drill-java-exec-1.16.0.jar:1.16.0]
> at org.apache.drill.exec.physical.impl.ScanBatch.next(ScanBatch.java:271)
> [drill-java-exec-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:126)
> [drill-java-exec-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:116)
> [drill-java-exec-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.record.AbstractUnaryRecordBatch.innerNext(AbstractUnaryRecordBatch.java:63)
> [drill-java-exec-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:141)
> [drill-java-exec-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:186)
> [drill-java-exec-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:104)
> [drill-java-exec-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext(ScreenCreator.java:83)
> [drill-java-exec-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:94)
> [drill-java-exec-1.16.0.jar:1.16.0]
> at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:296)
> [drill-java-exec-1.16.0.jar:1.16.0]
> at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:283)
> [drill-java-exec-1.16.0.jar:1.16.0]
> at java.security.AccessController.doPrivileged(Native Method)
> [na:1.8.0_151]
> at javax.security.auth.Subject.doAs(Subject.java:422) [na:1.8.0_151]
> at
> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1746)
> [hadoop-common-2.7.4.jar:na]
> at org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:283)
> [drill-java-exec-1.16.0.jar:1.16.0]
> at
> org.apache.drill.common.SelfCleaningRunnable.run(SelfCleaningRunnable.java:38)
> [drill-common-1.16.0.jar:1.16.0]
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> [na:1.8.0_151]
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> [na:1.8.0_151]
> at java.lang.Thread.run(Thread.java:748) [na:1.8.0_151]
> Caused by: java.sql.SQLException: Value '0000-00-00' can not be
> represented as java.sql.Date
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
> ~[mysql-connector-java.jar:5.1.48]
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
> ~[mysql-connector-java.jar:5.1.48]
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
> ~[mysql-connector-java.jar:5.1.48]
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
> ~[mysql-connector-java.jar:5.1.48]
> at com.mysql.jdbc.ResultSetRow.getDateFast(ResultSetRow.java:146)
> ~[mysql-connector-java.jar:5.1.48]
> at com.mysql.jdbc.ByteArrayRow.getDateFast(ByteArrayRow.java:244)
> ~[mysql-connector-java.jar:5.1.48]
> at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2011)
> ~[mysql-connector-java.jar:5.1.48]
> at
> org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488)
> ~[commons-dbcp2-2.5.0.jar:2.5.0]
> at
> org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:488)
> ~[commons-dbcp2-2.5.0.jar:2.5.0]
> at
> org.apache.drill.exec.store.jdbc.JdbcRecordReader$DateCopier.copy(JdbcRecordReader.java:440)
> ~[drill-jdbc-storage-1.16.0.jar:1.16.0]
> at
> org.apache.drill.exec.store.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:271)
> [drill-jdbc-storage-1.16.0.jar:1.16.0]
> ... 20 common frames omitted
> 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> INFO o.a.d.e.w.fragment.FragmentExecutor -
> 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State change requested RUNNING
> --> FAILED
> 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> ERROR o.a.d.e.physical.impl.BaseRootExec - Batch dump started: dumping last
> 2 failed batches
> 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> ERROR o.a.d.e.p.i.p.ProjectRecordBatch - ProjectRecordBatch[projector=null,
> hasRemainder=false, remainderIndex=0, recordCount=0,
> container=org.apache.drill.exec.record.VectorContainer@70742027[recordCount
> = 0, schemaChanged = true, schema = null, wrappers = [], ...]]
> 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> ERROR o.a.d.exec.physical.impl.ScanBatch -
> ScanBatch[container=org.apache.drill.exec.record.VectorContainer@76871541[recordCount
> = 0, schemaChanged = true, schema = null, wrappers =
> [org.apache.drill.exec.vector.NullableIntVector@ad6aa7f[field =
> [`ASSOC_MST_ID` (INT(12, 0):OPTIONAL)], ...],
> org.apache.drill.exec.vector.NullableVarCharVector@1292045f[field =
> [`ASSOC_MST_ROLE_CD` (VARCHAR(1, 0):OPTIONAL)], ...],
> org.apache.drill.exec.vector.NullableVarCharVector@43367b32[field =
> [`ASSOC_MST_LNAME` (VARCHAR(25, 0):OPTIONAL)], ...],
> org.apache.drill.exec.vector.NullableVarCharVector@d554308[field =
> [`ASSOC_MST_FNAME` (VARCHAR(25, 0):OPTIONAL)], ...],
> org.apache.drill.exec.vector.NullableVarCharVector@32ef958f[field =
> [`ASSOC_MST_MID_INIT` (VARCHAR(1, 0):OPTIONAL)], ...],
> org.apache.drill.exec.vector.NullableVarCharVector@305ddf71[field =
> [`ASSOC_MST_P_ADDR` (VARCHAR(90, 0):OPTIONAL)], ...],
> org.apache.drill.exec.vect
> or.NullableVarCharVector@4042dda3[field = [`ASSOC_MST_E_ADDR`
> (VARCHAR(50, 0):OPTIONAL)], ...],
> org.apache.drill.exec.vector.NullableIntVector@67a88ba0[field =
> [`ASSOC_MST_PHONE` (INT(10, 0):OPTIONAL)], ...],
> org.apache.drill.exec.vector.NullableDateVector@29a52937[field =
> [`ASSOC_MST_ST_DT` (DATE(10, 0):OPTIONAL)], ...],
> org.apache.drill.exec.vector.NullableIntVector@77dddd25[field =
> [`ASSOC_MST_PAY_RT` (INT(11, 0):OPTIONAL)], ...],
> org.apache.drill.exec.vector.NullableVarCharVector@21a4a9b8[field =
> [`ASSOC_MST_SSN` (VARCHAR(9, 0):OPTIONAL)], ...],
> org.apache.drill.exec.vector.NullableIntVector@272d3a7[field =
> [`ASSOC_MST_ORG_GRP_ID` (INT(10, 0):OPTIONAL)], ...],
> org.apache.drill.exec.vector.NullableIntVector@29eae4f[field = [`id`
> (INT(12, 0):OPTIONAL)], ...]], ...],
> currentReader=JdbcRecordReader[sql=SELECT *
> FROM `openisjb_demo`.`ASSOC`, Plugin=mysql], schema=null]
> 2019-11-28 10:01:04,133 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> ERROR o.a.d.e.physical.impl.BaseRootExec - Batch dump completed.
> 2019-11-28 10:01:04,134 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> INFO o.a.d.e.w.fragment.FragmentExecutor -
> 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0: State change requested FAILED -->
> FINISHED
> 2019-11-28 10:01:04,139 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> WARN o.a.d.exec.rpc.control.WorkEventBus - Fragment
> 22206520-3db3-ddd0-72c1-5ab0990a73a4:0:0 manager is not found in the work
> bus.
> 2019-11-28 10:01:04,148 [22206520-3db3-ddd0-72c1-5ab0990a73a4:frag:0:0]
> WARN o.a.d.e.w.f.QueryStateProcessor - Dropping request to move to
> COMPLETED state as query is already at FAILED state (which is terminal).
>
> is there any way to configure the data types to be used in apache drill or
> any alternate solutions instead of changing schema of the table in
> datasources.
> Thanks and regards
> Rameshwar Mane
> Big Data Engineer
>