Presto: Error occured while connecting to memsql using prestosql 320

[kranthikiran01]

Hi, I am seeing an error while connecting to memsql using prestosql 320. The error says, java.sql.SQLException: Unknown system variable 'performance_schema'.

What I tried

• I went through the release notes and added the mysql.jdbc.use-information-schema config to my catalog. This didnt work
• I tried reverting back to 319. This didnt work
• I tried reverting back to 318. This worked.

This is the stack trace for the above issue. Thanks to @ebyhr

2019-10-14T15:50:15.698+0900    ERROR   remote-task-callback-0  io.prestosql.execution.StageStateMachine    Stage 20191014_065009_00000_qf53i.2 failed
io.prestosql.spi.PrestoException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
    at io.prestosql.plugin.jdbc.BaseJdbcClient.getSchemaNames(BaseJdbcClient.java:165)
    at io.prestosql.plugin.jdbc.jmx.StatisticsAwareJdbcClient.lambda$getSchemaNames$1(StatisticsAwareJdbcClient.java:81)
    at io.prestosql.plugin.jdbc.jmx.JdbcApiStats.wrap(JdbcApiStats.java:35)
    at io.prestosql.plugin.jdbc.jmx.StatisticsAwareJdbcClient.getSchemaNames(StatisticsAwareJdbcClient.java:81)
    at io.prestosql.plugin.jdbc.ForwardingJdbcClient.getSchemaNames(ForwardingJdbcClient.java:47)
    at io.prestosql.plugin.jdbc.ForwardingJdbcClient.getSchemaNames(ForwardingJdbcClient.java:47)
    at io.prestosql.plugin.jdbc.JdbcMetadata.listSchemaNames(JdbcMetadata.java:75)
    at io.prestosql.metadata.MetadataManager.listSchemaNames(MetadataManager.java:260)
    at io.prestosql.metadata.MetadataListing.listSchemas(MetadataListing.java:58)
    at io.prestosql.connector.informationschema.InformationSchemaPageSource.addSchemataRecords(InformationSchemaPageSource.java:300)
    at io.prestosql.connector.informationschema.InformationSchemaPageSource.buildPages(InformationSchemaPageSource.java:214)
    at io.prestosql.connector.informationschema.InformationSchemaPageSource.getNextPage(InformationSchemaPageSource.java:171)
    at io.prestosql.operator.TableScanOperator.getOutput(TableScanOperator.java:286)
    at io.prestosql.operator.Driver.processInternal(Driver.java:379)
    at io.prestosql.operator.Driver.lambda$processFor$8(Driver.java:283)
    at io.prestosql.operator.Driver.tryWithLock(Driver.java:675)
    at io.prestosql.operator.Driver.processFor(Driver.java:276)
    at io.prestosql.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:1075)
    at io.prestosql.execution.executor.PrioritizedSplitRunner.process(PrioritizedSplitRunner.java:163)
    at io.prestosql.execution.executor.TaskExecutor$TaskRunner.run(TaskExecutor.java:484)
    at io.prestosql.$gen.Presto_null__testversion____20191014_064851_1.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
    at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2095)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2020)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:768)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:385)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:323)
    at io.prestosql.plugin.jdbc.DriverConnectionFactory.openConnection(DriverConnectionFactory.java:57)
    at io.prestosql.plugin.jdbc.jmx.StatisticsAwareConnectionFactory.lambda$openConnection$0(StatisticsAwareConnectionFactory.java:42)
    at io.prestosql.plugin.jdbc.jmx.JdbcApiStats.wrap(JdbcApiStats.java:35)
    at io.prestosql.plugin.jdbc.jmx.StatisticsAwareConnectionFactory.openConnection(StatisticsAwareConnectionFactory.java:42)
    at io.prestosql.plugin.jdbc.BaseJdbcClient.getSchemaNames(BaseJdbcClient.java:159)
    ... 23 more
Caused by: java.sql.SQLException: Unknown system variable 'performance_schema'
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3933)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2675)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2439)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1365)
    at com.mysql.jdbc.ConnectionImpl.loadServerVariables(ConnectionImpl.java:3767)
    at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3196)
    at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2058)
    ... 38 more

• The mysql driver version upgrade during 318 to 319 could be the reason. Downgrading the driver version back to 5.1.47 from 5.1.48 seems to have fixed the issue.

Refer to #1436 PR for understanding the reason behind the driver upgrade.

[findepi]

Per offline discussion, the solution should be to create separate memsql connector.
redshift connector can be used as an example to follow.

[ebyhr]

Agreed to create new connector. As we already know, MySQL(=MemSQL) JDBC metadata is little different from other drivers (eg listSchemas). We need to care about it.