[Bug report] jdbc-mysql catalogs failed on mysql 8.x driver

[kalencaya]

Version

main branch

Describe what's wrong

jdbc-mysql catalogs failed on mysql 8.x driver, but 5.x works well

Error message and/or stacktrace

How to reproduce

mount mysql 8.x driver to gravitino docker container on /root/gravitino/catalogs/jdbc-mysql/libs/mysql.jar and create jdbc-mysql catalog on gravitino ui

Additional context

No response

[jerryshao]

@FANNG1 @yuqi1129 would you please help to check this?

[yuqi1129]

@FANNG1 @yuqi1129 would you please help to check this?

Got it, I will verify it today.

[yuqi1129]

@kalencaya
Can you provide more information about it? We have used mysql-connector-java-8.0.27.jar for some time and it seems that nothing goes wrong, what is the version of JDBC-driver you use?

[kalencaya]

docker-compose.yml and jdbc properties

driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://mysql:3306/test
username: root
password: 123456

version: "3.8"

services:

  mysql:
    image: bitnami/mysql:8.0
    container_name: mysql
    environment:
      - TZ=Asia/Shanghai
      - MYSQL_ROOT_USER=root
      - MYSQL_ROOT_PASSWORD=123456
      - MYSQL_AUTHENTICATION_PLUGIN=mysql_native_password
      - MYSQL_DATABASE=test
    ports:
      - 3306:3306
    networks:
      - test

  gravitino:
    image: datastrato/gravitino:0.5.0
    container_name: gravitino
    ports:
      - 8090:8090
    restart: unless-stopped
    volumes:
      - ./mysql-connector-java-8.0.11.jar:/root/gravitino/catalogs/jdbc-mysql/libs/mysql.jar
    networks:
      - test

networks:
  test:
    driver: bridge

[kalencaya]

@yuqi1129 as you can see, I'm using mysql-connector-java-8.0.11.jar

[kalencaya]

[yuqi1129]

@yuqi1129 as you can see, I'm using mysql-connector-java-8.0.11.jar

OK, let me give it a try.

[yuqi1129]

@kalencaya
It did have a problem, we will fix it quickly. If you are interested, you can work on it too.

[yuqi1129]

It seems that the JDBC metadata is not very mature and stable. I'm afraid this is not the only issue that exists. We have addressed several issues with JDBC metadata such as mixed schema and catalog, wildcard issues, and so on.
@jerryshao @FANNG1

[kalencaya]

@kalencaya It did have a problem, we will fix it quickly. If you are interested, you can work on it too.

I'm willing to do this and pull requests

[kalencaya]

This should be caused by JdbcTableOperations#getTables(Connection) method:

  protected ResultSet getTables(Connection connection) throws SQLException {
    final DatabaseMetaData metaData = connection.getMetaData();
    String databaseName = connection.getSchema();
    return metaData.getTables(databaseName, databaseName, null, JdbcConnectorUtils.getTableTypes());
  }

I'll pull requests to change it as follow, which works well on mysql and postgresql:

  protected ResultSet getTables(Connection connection) throws SQLException {
    final DatabaseMetaData metaData = connection.getMetaData();
    String catalogName = connection.getCatalog();
    String schemaName = connection.getSchema();
    return metaData.getTables(catalogName, schemaName, null, JdbcConnectorUtils.getTableTypes());
  }

[yuqi1129]

@kalencaya
Thanks for your catch, please help to verify if the changes you mentioned conflict with #3229 or whether is it a better solution than that in #3229.

[kalencaya]

@kalencaya Thanks for your catch, please help to verify if the changes you mentioned conflict with #3229 or whether is it a better solution than that in #3229.

yes

[kalencaya]

@kalencaya Thanks for your catch, please help to verify if the changes you mentioned conflict with #3229 or whether is it a better solution than that in #3229.

#3229 is a better solution

[yuqi1129]

@kalencaya Thanks for your catch, please help to verify if the changes you mentioned conflict with #3229 or whether is it a better solution than that in #3229.

#3229 is a better solution

Do you mean that #3229 can resolve this issue? @kalencaya

[FANNG1]

  protected ResultSet getTables(Connection connection) throws SQLException {
    final DatabaseMetaData metaData = connection.getMetaData();
    String catalogName = connection.getCatalog();
    String schemaName = connection.getSchema();
    return metaData.getTables(catalogName, schemaName, null, JdbcConnectorUtils.getTableTypes());
  }

I prefer this solution than #3229 , because it's more general

[kalencaya]

  protected ResultSet getTables(Connection connection) throws SQLException {
    final DatabaseMetaData metaData = connection.getMetaData();
    String catalogName = connection.getCatalog();
    String schemaName = connection.getSchema();
    return metaData.getTables(catalogName, schemaName, null, JdbcConnectorUtils.getTableTypes());
  }

I prefer this solution than #3229 , because it's more general

this relies on jdbc driver api then appears more general.

but different jdbc driver implementations have some difference, they may have a nonstandard jdbc driver implementation.

actually, some dms (database management system) 、sql editor server or orm frameworks prefer to retrieve jdbc metadata through database internal metadata store tables not jdbc driver. more complicated than jdbc driver api way

I'm affected by that and prefers to not rely too much on jdbc driver api.

we may can't solve all jdbc metadata through jdbc driver api and have to query database internal metadata tables

just a little opinion

[yuqi1129]

this relies on jdbc driver api then appears more general.

but different jdbc driver implementations have some difference, they may have a nonstandard jdbc driver implementation.

actually, some dms (database management system) 、sql editor server or orm frameworks prefer to retrieve jdbc metadata through database internal metadata store tables not jdbc driver. more complicated than jdbc driver api way

I'm affected by that and prefers to not rely too much on jdbc driver api.

we may can't solve all jdbc metadata through jdbc driver api and have to query database internal metadata tables

just a little opinion

Please go ahead if it looks more elegant than #3229. #3229 will be modified based on your changes after your PR is merged.

[kalencaya]

this relies on jdbc driver api then appears more general.
but different jdbc driver implementations have some difference, they may have a nonstandard jdbc driver implementation.
actually, some dms (database management system) 、sql editor server or orm frameworks prefer to retrieve jdbc metadata through database internal metadata store tables not jdbc driver. more complicated than jdbc driver api way
I'm affected by that and prefers to not rely too much on jdbc driver api.
we may can't solve all jdbc metadata through jdbc driver api and have to query database internal metadata tables
just a little opinion

Please go ahead if it looks more elegant than #3229. #3229 will be modified based on your changes after your PR is merged.

I'll pull requests recently and not block your subsequent works