Exception when call " show databases ", " show tables " and " !tables " #340

aloyszhang · 2019-08-13T08:57:56Z

Hi,
I met some exception when use sqlline to connect a mysql database.
All the operation and exception are like follows:

sqlline> !connect jdbc:mysql://localhost:3306 root password
Tue Aug 13 16:45:45 CST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Connected to: MySQL (version 5.7.25)
Driver: MySQL Connector Java (version mysql-connector-java-5.1.47 ( Revision: fe1903b1ecb4a96a917f7ed3190d80c049b1de29 ))
0: jdbc:mysql://localhost:3306> show databases;
Warning: Incorrect database name '' (state=42000,code=1102)
java.lang.reflect.UndeclaredThrowableException
	at com.sun.proxy.$Proxy1.getPrimaryKeys(Unknown Source)
	at sqlline.Rows.loadAndCachePrimaryKeysForTable(Rows.java:351)
	at sqlline.Rows.getTablePrimaryKeys(Rows.java:389)
	at sqlline.Rows.isPrimaryKey(Rows.java:142)
	at sqlline.TableOutputFormat.getOutputString(TableOutputFormat.java:129)
	at sqlline.TableOutputFormat.getOutputString(TableOutputFormat.java:110)
	at sqlline.TableOutputFormat.print(TableOutputFormat.java:46)
	at sqlline.SqlLine.print(SqlLine.java:1717)
	at sqlline.Commands.executeSingleQuery(Commands.java:1046)
	at sqlline.Commands.execute(Commands.java:986)
	at sqlline.Commands.sql(Commands.java:950)
	at sqlline.SqlLine.dispatch(SqlLine.java:731)
	at sqlline.SqlLine.begin(SqlLine.java:538)
	at sqlline.SqlLine.start(SqlLine.java:267)
	at sqlline.SqlLine.main(SqlLine.java:206)
Caused by: java.lang.reflect.InvocationTargetException
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at sqlline.DatabaseMetaDataHandler.invoke(DatabaseMetaDataHandler.java:37)
	... 15 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: **Incorrect database name ''**
	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:944)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2491)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2449)
	at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1381)
	at com.mysql.jdbc.DatabaseMetaData$7.forEach(DatabaseMetaData.java:3737)
	at com.mysql.jdbc.DatabaseMetaData$7.forEach(DatabaseMetaData.java:3727)
	at com.mysql.jdbc.IterateBlock.doForAll(IterateBlock.java:50)
	at com.mysql.jdbc.DatabaseMetaData.getPrimaryKeys(DatabaseMetaData.java:3725)
	... 20 more
0: jdbc:mysql://localhost:3306> !connect jdbc:mysql://localhost:3306/test root password
Tue Aug 13 16:56:11 CST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Connected to: MySQL (version 5.7.25)
Driver: MySQL Connector Java (version mysql-connector-java-5.1.47 ( Revision: fe1903b1ecb4a96a917f7ed3190d80c049b1de29 ))
1: jdbc:mysql://localhost:3306/test> show tables;
Warning: Table 'test.table_names' doesn't exist (state=42S02,code=1146)
java.lang.reflect.UndeclaredThrowableException
	at com.sun.proxy.$Proxy1.getPrimaryKeys(Unknown Source)
	at sqlline.Rows.loadAndCachePrimaryKeysForTable(Rows.java:351)
	at sqlline.Rows.getTablePrimaryKeys(Rows.java:389)
	at sqlline.Rows.isPrimaryKey(Rows.java:142)
	at sqlline.TableOutputFormat.getOutputString(TableOutputFormat.java:129)
	at sqlline.TableOutputFormat.getOutputString(TableOutputFormat.java:110)
	at sqlline.TableOutputFormat.print(TableOutputFormat.java:46)
	at sqlline.SqlLine.print(SqlLine.java:1717)
	at sqlline.Commands.executeSingleQuery(Commands.java:1046)
	at sqlline.Commands.execute(Commands.java:986)
	at sqlline.Commands.sql(Commands.java:950)
	at sqlline.SqlLine.dispatch(SqlLine.java:731)
	at sqlline.SqlLine.begin(SqlLine.java:538)
	at sqlline.SqlLine.start(SqlLine.java:267)
	at sqlline.SqlLine.main(SqlLine.java:206)
Caused by: java.lang.reflect.InvocationTargetException
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at sqlline.DatabaseMetaDataHandler.invoke(DatabaseMetaDataHandler.java:37)
	... 15 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'test.table_names' doesn't exist
	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:944)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2491)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2449)
	at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1381)
	at com.mysql.jdbc.DatabaseMetaData$7.forEach(DatabaseMetaData.java:3737)
	at com.mysql.jdbc.DatabaseMetaData$7.forEach(DatabaseMetaData.java:3727)
	at com.mysql.jdbc.IterateBlock.doForAll(IterateBlock.java:50)
	at com.mysql.jdbc.DatabaseMetaData.getPrimaryKeys(DatabaseMetaData.java:3725)
	... 20 more
1: jdbc:mysql://localhost:3306/test> !tables;
Unknown command: tables;

The text was updated successfully, but these errors were encountered:

snuyanzin · 2019-08-13T09:11:53Z

Hello @aloyszhang
thanks for finding

I didn't try with Driver: MySQL Connector Java (version mysql-connector-java-5.1.47 ( Revision: fe1903b1ecb4a96a917f7ed3190d80c049b1de29 ))
Based on exception it looks like this driver does not allow to retrieve primary keys for some cases.
I tried with MariaDB org.mariadb.jdbc.Driver 2.4.* and it works.
Could you try it please with a different driver?

about the last lines

1: jdbc:mysql://localhost:3306/test> !tables;
Unknown command: tables;

there is definitely no commands tables; but there is a command tables (without semicolon (by the way you can use <tab> for autocompletion of sqlline commands to avoid such issues)).

so this should work

1: jdbc:mysql://localhost:3306/test> !tables

UPD: I didn't find from the traces. What version of sqlline do you use?

aloyszhang · 2019-08-13T11:58:45Z

@snuyanzin Thanks for your reply.
I run sqlline on the master branch code in IDEA.
The resson that these logs
Driver: MySQL Connector Java (version mysql-connector-java-5.1.47 ( Revision: fe1903b1ecb4a96a917f7ed3190d80c049b1de29 ))
appears is that I modify the code in the DatabaseConnection#connect() to see the driver information.
I modify the code from

sqlLine.debug(
          sqlLine.loc("driver",
              meta.getDriverName(),
              meta.getDriverVersion()));
    } catch (Exception e) {

to

sqlLine.output(
          sqlLine.loc("driver",
              meta.getDriverName(),
              meta.getDriverVersion()));
    } catch (Exception e) {

aloyszhang · 2019-08-13T12:37:24Z

I find the example in manual !connect jdbc:oracle:thin:@localhost:1521:mydb scott tiger oracle.jdbc.driver.OracleDriver which specifies the database name mydb. And in my test !connect jdbc:mysql://localhost:3306 root password , I did not specify database name. Would be this cause the exception?
furthermore, our database in production environment was mysql, so I have to use mysql driver.

I also tried in production environment , which has the same problem :

sqlline version 1.9.0-SNAPSHOT
sqlline> !connect jdbc:mysql://localhost:3309 test test
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
0: jdbc:mysql://10.213.1.162:3309> show databases;
Warning: Incorrect database name '' (state=42000,code=1102)
java.lang.reflect.UndeclaredThrowableException
        at com.sun.proxy.$Proxy2.getPrimaryKeys(Unknown Source)
        at sqlline.Rows.loadAndCachePrimaryKeysForTable(Rows.java:351)
        at sqlline.Rows.getTablePrimaryKeys(Rows.java:389)
        at sqlline.Rows.isPrimaryKey(Rows.java:142)
        at sqlline.TableOutputFormat.getOutputString(TableOutputFormat.java:129)
        at sqlline.TableOutputFormat.getOutputString(TableOutputFormat.java:110)
        at sqlline.TableOutputFormat.print(TableOutputFormat.java:46)
        at sqlline.SqlLine.print(SqlLine.java:1717)
        at sqlline.Commands.executeSingleQuery(Commands.java:1046)
        at sqlline.Commands.execute(Commands.java:986)
        at sqlline.Commands.sql(Commands.java:950)
        at sqlline.SqlLine.dispatch(SqlLine.java:731)
        at sqlline.SqlLine.begin(SqlLine.java:538)
        at sqlline.SqlLine.start(SqlLine.java:267)
        at sqlline.SqlLine.main(SqlLine.java:206)
Caused by: java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at sqlline.DatabaseMetaDataHandler.invoke(DatabaseMetaDataHandler.java:37)
        ... 15 more
Caused by: java.sql.SQLSyntaxErrorException: Incorrect database name ''
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1218)
        at com.mysql.cj.jdbc.DatabaseMetaData$7.forEach(DatabaseMetaData.java:2950)
        at com.mysql.cj.jdbc.DatabaseMetaData$7.forEach(DatabaseMetaData.java:2938)
        at com.mysql.cj.jdbc.IterateBlock.doForAll(IterateBlock.java:56)
        at com.mysql.cj.jdbc.DatabaseMetaData.getPrimaryKeys(DatabaseMetaData.java:2991)
        ... 20 more

snuyanzin · 2019-08-13T15:13:48Z

I was able to reproduce it and prepare a junit test.
From one side it looks like an issue at sqlline's side which will be fixed at #341 .
From the other there is a strange message Caused by: java.sql.SQLSyntaxErrorException: Incorrect database name '' I have no idea if such message from mysql driver is correct or not.
I tried both mysql and mariadb drivers (the last could connect to both mysql and mariadb) and only mysql connector tells about it.

Also this error will not appear with json, csv output formats as they do not use DatabaseMetaData#getPrimaryKeys. So json, csv output formats could be used as workaround

snuyanzin · 2019-08-18T10:53:08Z

Merged as 83301ba

snuyanzin mentioned this issue Aug 13, 2019

[SQLLINE-340] Rethrow SQLException from DatabaseMetaDataHandler if it is the cause #341

Closed

snuyanzin closed this as completed Aug 18, 2019

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Exception when call " show databases ", " show tables " and " !tables " #340

Exception when call " show databases ", " show tables " and " !tables " #340

aloyszhang commented Aug 13, 2019 •

edited

Loading

snuyanzin commented Aug 13, 2019 •

edited

Loading

aloyszhang commented Aug 13, 2019 •

edited

Loading

aloyszhang commented Aug 13, 2019 •

edited

Loading

snuyanzin commented Aug 13, 2019 •

edited

Loading

snuyanzin commented Aug 18, 2019

Exception when call " show databases ", " show tables " and " !tables " #340

Exception when call " show databases ", " show tables " and " !tables " #340

Comments

aloyszhang commented Aug 13, 2019 • edited Loading

snuyanzin commented Aug 13, 2019 • edited Loading

aloyszhang commented Aug 13, 2019 • edited Loading

aloyszhang commented Aug 13, 2019 • edited Loading

snuyanzin commented Aug 13, 2019 • edited Loading

snuyanzin commented Aug 18, 2019

aloyszhang commented Aug 13, 2019 •

edited

Loading

snuyanzin commented Aug 13, 2019 •

edited

Loading

aloyszhang commented Aug 13, 2019 •

edited

Loading

aloyszhang commented Aug 13, 2019 •

edited

Loading

snuyanzin commented Aug 13, 2019 •

edited

Loading