JDBC: can not query gitbase

[bzz]

In order to query gitbase though JDBC driver for MySQL

import java.sql.*;

class JdbcQueryGitbase {
  public static void main(String[] args) throws Exception {
    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection connect = DriverManager
      .getConnection("jdbc:mysql://localhost:3306?user=root");
    
    Statement statement = connect.createStatement();
    ResultSet resultSet = statement
        .executeQuery("select * from repositories;");
    while (resultSet.next()) {
      System.out.println(resultSet.toString());
    }
  }
}

here is what I did

wget http://central.maven.org/maven2/mysql/mysql-connector-java/6.0.6/mysql-connector-java-6.0.6.jar
javac JdbcQueryGitbase.java
java -cp ".:mysql-connector-java-6.0.6.jar" JdbcQueryGitbase

it seems to have connected successfully but resulted in exception

Exception in thread "main" java.sql.SQLException: unknown error: table not found: dual
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:545)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
	at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983)
	at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1936)
	at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)
	at com.mysql.cj.jdbc.ConnectionImpl.loadServerVariables(ConnectionImpl.java:2831)
	at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:2381)
	at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:1739)
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1596)
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:633)
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:347)
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:219)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:270)
	at JdbcQueryGitbase.main(JdbcQueryGitbase.java:10)

the only thing in gitbase logs is

INFO[0017] NewConnection: client 1
INFO[0017] ConnectionClosed: client 1

Could you help me understand why does this happen and what is the best way to fix/avoid it?
Quick search landed me to https://en.wikipedia.org/wiki/DUAL_table but that was not super helpful.

Thanks in advance!

[ajnavarro]

We don't have the dual table, so queries like SELECT 1 will not work. I guess the driver is using the query SELECT 1 to check if the connection is open and working. I think it can be changed on configuration.

[bzz]

Thank you for explanation, will look more into the driver configuration and keep this issue posted.

As this was standard MySQL JDBC driver behavior with a default configuration, this also might mean that none of the GUI tools for working with RDBMs would be able to use gitbase without extra configuration steps.

[smola]

Implementing the dual table by default on go-mysql-server will be useful for this kind of thing. I have opened an issue for that: src-d/go-mysql-server#153

[bzz]

After a very quick glance - could not find a configuration to disable this type of connection verification from the client side.

+1 on gauging priority of compatibility \w RDMS tools that would allow people to explore gitbase easier.

[erizocosmico]

PR for this in go-mysql-server ready at src-d/go-mysql-server#155
As soon as it's merged I'll update the dependency and we can close this 😄