Anup Looking at the logs, I see two relevant errors -
- (User Error Occurred: Schema [[mysql, test is not valid with respect to either root schema or current default schema. AND - Failure connecting to the MySQL server. Both seem to point to some change in the MySQL setup/ your network environment - so I wouldn’t recommend downgrading to 1.11 just based on these errors. It might helpful to check the MySQL server logs at the same timestamps to see if there was an intermittent issue there. Pritesh 2018-03-16 06:56:46,453 [2554e2a9-63cd-5b3b-a8f7-5b2133fa725f:foreman] INFO o.a.d.e.planner.sql.SchemaUtilites - User Error Occurred: Schema [[mysql, test]] is not valid with respect to either root schema or current default schema. org.apache.drill.common.exceptions.UserException: VALIDATION ERROR: Schema [[mysql, test]] is not valid with respect to either root schema or current default schema. Current default schema: dfs.tmp Query : create table dfs.tmp.table_info as select * from mysql.test.table_info; Error : WARN o.a.d.e.store.jdbc.JdbcStoragePlugin - Failure while attempting to load JDBC schema.com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 49,949,177 milliseconds ago. The last packet sent successfully to the server was 49,949,196 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem. On Mar 16, 2018, at 10:58 PM, Saurabh Mahapatra <[email protected]<mailto:[email protected]>> wrote: Anyone have any suggestion on this? Makes me wonder if anything changed? On Fri, Mar 16, 2018 at 1:15 AM, Anup Tiwari <[email protected]<mailto:[email protected]>> wrote: Hi All, We checked our MySQL max number of connections which is set to 200 and i think this might be due to exceeding max number of connections only as right now i can see 89 connections to MySQL. I want to know community's thoughts on this whether i am heading in right direction or not. On Fri, Mar 16, 2018 1:03 PM, Anup Tiwari [email protected]<mailto:[email protected]> wrote: Hi All, We are getting a lot of different type of issues/error post upgrading from Drill 1.10.0 to 1.12.0 which i am asking on forum as well so just wanted to know whether downgrading to Drill 1.11.0 will help or not? This time we got exception related to mysql connection storage and please note that this issue is not consistent i.e. if i execute this query after some time then it works. Please find below query are error logs. Query : create table dfs.tmp.table_info as select * from mysql.test.table_info; Error : WARN o.a.d.e.store.jdbc.JdbcStoragePlugin - Failure while attempting to load JDBC schema.com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 49,949,177 milliseconds ago. The last packet sent successfully to the server was 49,949,196 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem. at sun.reflect.NativeConstructorAccessorImpl. newInstance0(Native Method) ~[na:1.8.0_72] at sun.reflect.NativeConstructorAccessorImpl.newInstance( NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_72] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance( DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_72] at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_72] at com.mysql.jdbc.Util.handleNewInstance(Util.java:389) ~[mysql-connector-java-5.1.35-bin.jar:5.1.35] at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1038) ~[mysql-connector-java-5.1.35-bin.jar:5.1.35] at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3609) ~[mysql-connector-java-5.1.35-bin.jar:5.1.35] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2417) ~[mysql-connector-java-5.1.35-bin.jar:5.1.35] at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) ~[mysql-connector-java-5.1.35-bin.jar:5.1.35] at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2531) ~[mysql-connector-java-5.1.35-bin.jar:5.1.35] at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2489) ~[mysql-connector-java-5.1.35-bin.jar:5.1.35] at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1446) ~[mysql-connector-java-5.1.35-bin.jar:5.1.35] at com.mysql.jdbc.DatabaseMetaData.getCatalogs(DatabaseMetaData.java:2025) ~[mysql-connector-java-5.1.35-bin.jar:5.1.35] at org.apache.commons.dbcp.DelegatingDatabaseMetaData.getCatalogs( DelegatingDatabaseMetaData.java:190) ~[commons-dbcp-1.4.jar:1.4] at org.apache.drill.exec.store.jdbc.JdbcStoragePlugin$ JdbcCatalogSchema.<init>(JdbcStoragePlugin.java:309) ~[drill-jdbc-storage-1.12.0.jar:1.12.0] at org.apache.drill.exec.store.jdbc.JdbcStoragePlugin.registerSchemas( JdbcStoragePlugin.java:430) [drill-jdbc-storage-1.12.0.jar:1.12.0] at org.apache.drill.exec.planner.sql.DynamicRootSchema.loadSchemaFactory( DynamicRootSchema.java:94) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.drill.exec.planner.sql.DynamicRootSchema.getSubSchema( DynamicRootSchema.java:74) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.calcite.prepare.CalciteCatalogReader.getSchema( CalciteCatalogReader.java:160) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.prepare.CalciteCatalogReader.getTableFrom( CalciteCatalogReader.java:114) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.prepare.CalciteCatalogReader.getTable( CalciteCatalogReader.java:108) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.drill.exec.planner.sql.SqlConverter$DrillCalciteCatalogReader. getTable(SqlConverter.java:493) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.drill.exec.planner.sql.SqlConverter$DrillCalciteCatalogReader. getTable(SqlConverter.java:434) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.calcite.sql.validate.EmptyScope.getTableNamespace(EmptyScope. java:75) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.DelegatingScope.getTableNamespace( DelegatingScope.java:124) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl( IdentifierNamespace.java:104) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.AbstractNamespace. validate(AbstractNamespace.java:86) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace( SqlValidatorImpl.java:886) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery( SqlValidatorImpl.java:872) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SqlValidatorImpl. validateFrom(SqlValidatorImpl.java:2817) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SqlValidatorImpl. validateFrom(SqlValidatorImpl.java:2802) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect( SqlValidatorImpl.java:3025) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SelectNamespace. validateImpl(SelectNamespace.java:60) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.AbstractNamespace. validate(AbstractNamespace.java:86) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace( SqlValidatorImpl.java:886) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery( SqlValidatorImpl.java:872) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:210) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression( SqlValidatorImpl.java:846) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SqlValidatorImpl. validate(SqlValidatorImpl.java:560) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.drill.exec.planner.sql.SqlConverter.validate( SqlConverter.java:172) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode( DefaultSqlHandler.java:617) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler. validateAndConvert(DefaultSqlHandler.java:192) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.drill.exec.planner.sql.handlers.CreateTableHandler.getPlan( CreateTableHandler.java:77) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.drill.exec.planner.sql.DrillSqlWorker. getQueryPlan(DrillSqlWorker.java:131) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan( DrillSqlWorker.java:79) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:1017) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:289) [drill-java-exec-1.12.0.jar:1.12.0] at java.util.concurrent.ThreadPoolExecutor.runWorker( ThreadPoolExecutor.java:1142) [na:1.8.0_72] at java.util.concurrent.ThreadPoolExecutor$Worker.run( ThreadPoolExecutor.java:617) [na:1.8.0_72] at java.lang.Thread.run(Thread.java:745) [na:1.8.0_72] Caused by: java.net.SocketException: Broken pipe at java.net.SocketOutputStream.socketWrite0(Native Method) ~[na:1.8.0_72] at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109) ~[na:1.8.0_72] at java.net.SocketOutputStream.write(SocketOutputStream.java:153) ~[na:1.8.0_72] at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) ~[na:1.8.0_72] at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140) ~[na:1.8.0_72] at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3591) ~[mysql-connector-java-5.1.35-bin.jar:5.1.35] ... 43 common frames omitted 2018-03-16 06:56:46,453 [2554e2a9-63cd-5b3b-a8f7-5b2133fa725f:foreman] INFO o.a.d.e.planner.sql.SchemaUtilites - User Error Occurred: Schema [[mysql, test]] is not valid with respect to either root schema or current default schema. org.apache.drill.common.exceptions.UserException: VALIDATION ERROR: Schema [[mysql, test]] is not valid with respect to either root schema or current default schema. Current default schema: dfs.tmp [Error Id: 83ef0cbb-f289-4201-8668-15d5ad3d7b01 ] at org.apache.drill.common.exceptions.UserException$ Builder.build(UserException.java:586) ~[drill-common-1.12.0.jar:1.12.0] at org.apache.drill.exec.planner.sql.SchemaUtilites. throwSchemaNotFoundException(SchemaUtilites.java:177) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.drill.exec.planner.sql.SqlConverter$DrillCalciteCatalogReader. isValidSchema(SqlConverter.java:523) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.drill.exec.planner.sql.SqlConverter$DrillCalciteCatalogReader. getTable(SqlConverter.java:497) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.drill.exec.planner.sql.SqlConverter$DrillCalciteCatalogReader. getTable(SqlConverter.java:434) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.calcite.sql.validate.EmptyScope.getTableNamespace(EmptyScope. java:75) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.DelegatingScope.getTableNamespace( DelegatingScope.java:124) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl( IdentifierNamespace.java:104) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.AbstractNamespace. validate(AbstractNamespace.java:86) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace( SqlValidatorImpl.java:886) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery( SqlValidatorImpl.java:872) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SqlValidatorImpl. validateFrom(SqlValidatorImpl.java:2817) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SqlValidatorImpl. validateFrom(SqlValidatorImpl.java:2802) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect( SqlValidatorImpl.java:3025) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SelectNamespace. validateImpl(SelectNamespace.java:60) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.AbstractNamespace. validate(AbstractNamespace.java:86) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace( SqlValidatorImpl.java:886) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery( SqlValidatorImpl.java:872) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:210) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression( SqlValidatorImpl.java:846) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.calcite.sql.validate.SqlValidatorImpl. validate(SqlValidatorImpl.java:560) [calcite-core-1.4.0-drill-r23.jar:1.4.0-drill-r23] at org.apache.drill.exec.planner.sql.SqlConverter.validate( SqlConverter.java:172) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode( DefaultSqlHandler.java:617) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler. validateAndConvert(DefaultSqlHandler.java:192) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.drill.exec.planner.sql.handlers.CreateTableHandler.getPlan( CreateTableHandler.java:77) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.drill.exec.planner.sql.DrillSqlWorker. getQueryPlan(DrillSqlWorker.java:131) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan( DrillSqlWorker.java:79) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:1017) [drill-java-exec-1.12.0.jar:1.12.0] at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:289) [drill-java-exec-1.12.0.jar:1.12.0] at java.util.concurrent.ThreadPoolExecutor.runWorker( ThreadPoolExecutor.java:1142) [na:1.8.0_72] at java.util.concurrent.ThreadPoolExecutor$Worker.run( ThreadPoolExecutor.java:617) [na:1.8.0_72] at java.lang.Thread.run(Thread.java:745) [na:1.8.0_72] Let me know what to do here. Regards, Anup Tiwari Regards, Anup Tiwari
