#1499 MariaDB DatabaseMetaData.getTables threw an error, no table / view metadata

[skipperTux]

Hi.
SQuirreL SQL 4.3.0 does not fetch table / view metadata from MariaDB. From the logs it looks like the statement to fetch metadata is incorrect.

JDBC Driver CLASSNAME org.mariadb.jdbc.Driver
getURL jdbc:mariadb://10.1.2.3/?user=root&password=***
getDriverName MariaDB Connector/J
getDatabaseProductName MariaDB
getDatabaseProductVersion 10.3.32-MariaDB-1:10.3.32+maria~focal
getDriverVersion 3.0.3

Tested with Driver Version 3.0.1 and 3.0.3; see log below.
Any idea? Thanks.

2022-02-22 13:35:01,039 [Thread-2] INFO  net.sourceforge.squirrel_sql.client.session.SessionConnectionKeepAlive  - SessionConnectionKeepAlive (CHIRA Root) running SQL: SELECT Version()
2022-02-22 13:35:01,282 [Thread-3] INFO  net.sourceforge.squirrel_sql.plugins.sqlparam.SQLParamPlugin  - Initializing plugin
2022-02-22 13:35:01,519 [AWT-EventQueue-1] INFO  net.sourceforge.squirrel_sql.plugins.sqlparam.SQLParamPlugin  - Adding SQL execution listener.
2022-02-22 13:35:01,526 [AWT-EventQueue-1] INFO  net.sourceforge.squirrel_sql.plugins.sqlreplace.SQLReplacePlugin  - Adding SQL execution listener.
2022-02-22 13:35:21,606 [Thread-0] WARN  net.sourceforge.squirrel_sql.fw.sql.databasemetadata.SQLDatabaseMetaData  - DatabaseMetaData.getTables(...) threw an error when called with tableNamePattern = null. Trying tableNamePattern %. The error was: java.sql.SQLSyntaxErrorException: (conn=28) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND TABLE_TYPE IN ('BASE TABLE','SYSTEM VERSIONED','VIEW') ORDER BY TABLE_TYP...' at line 1
2022-02-22 13:35:21,607 [Thread-0] ERROR net.sourceforge.squirrel_sql.client.session.schemainfo.SchemaInfo  - failed to load table names
java.sql.SQLSyntaxErrorException: (conn=28) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND TABLE_TYPE IN ('BASE TABLE','SYSTEM VERSIONED','VIEW') ORDER BY TABLE_TYP...' at line 1
    at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:270)
    at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:358)
    at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:133)
    at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:807)
    at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:746)
    at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:665)
    at org.mariadb.jdbc.client.impl.StandardClient.execute(StandardClient.java:608)
    at org.mariadb.jdbc.Statement.executeInternal(Statement.java:906)
    at org.mariadb.jdbc.Statement.executeQuery(Statement.java:114)
    at org.mariadb.jdbc.DatabaseMetaData.executeQuery(DatabaseMetaData.java:470)
    at org.mariadb.jdbc.DatabaseMetaData.getTables(DatabaseMetaData.java:657)
    at net.sourceforge.squirrel_sql.fw.sql.databasemetadata.SQLDatabaseMetaData.getTables(SQLDatabaseMetaData.java:951)
    at net.sourceforge.squirrel_sql.client.session.schemainfo.SchemaInfo.privateLoadTables(SchemaInfo.java:1209)
    at net.sourceforge.squirrel_sql.client.session.schemainfo.SchemaInfo.loadTables(SchemaInfo.java:410)
    at net.sourceforge.squirrel_sql.client.session.schemainfo.SchemaInfo.privateLoadAll(SchemaInfo.java:301)
    at net.sourceforge.squirrel_sql.client.session.schemainfo.SchemaInfo.reloadAll(SchemaInfo.java:202)
    at net.sourceforge.squirrel_sql.client.session.schemainfo.SchemaInfo.reloadAll(SchemaInfo.java:192)
    at net.sourceforge.squirrel_sql.client.gui.session.CatalogsComboListener$1.run(CatalogsComboListener.java:51)
    at net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82)
    at java.base/java.lang.Thread.run(Thread.java:829)
2022-02-22 13:35:22,478 [Thread-3] WARN  net.sourceforge.squirrel_sql.fw.sql.databasemetadata.SQLDatabaseMetaData  - DatabaseMetaData.getTables(...) threw an error when called with tableNamePattern = null. Trying tableNamePattern %. The error was: java.sql.SQLSyntaxErrorException: (conn=28) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND TABLE_TYPE IN ('BASE TABLE','SYSTEM VERSIONED','VIEW') ORDER BY TABLE_TYP...' at line 1
2022-02-22 13:35:22,479 [Thread-3] ERROR net.sourceforge.squirrel_sql.client.session.schemainfo.SchemaInfo  - failed to load table names
java.sql.SQLSyntaxErrorException: (conn=28) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND TABLE_TYPE IN ('BASE TABLE','SYSTEM VERSIONED','VIEW') ORDER BY TABLE_TYP...' at line 1
    at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:270)
    at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:358)
    at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:133)
    at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:807)
    at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:746)
    at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:665)
    at org.mariadb.jdbc.client.impl.StandardClient.execute(StandardClient.java:608)
    at org.mariadb.jdbc.Statement.executeInternal(Statement.java:906)
    at org.mariadb.jdbc.Statement.executeQuery(Statement.java:114)
    at org.mariadb.jdbc.DatabaseMetaData.executeQuery(DatabaseMetaData.java:470)
    at org.mariadb.jdbc.DatabaseMetaData.getTables(DatabaseMetaData.java:657)
    at net.sourceforge.squirrel_sql.fw.sql.databasemetadata.SQLDatabaseMetaData.getTables(SQLDatabaseMetaData.java:951)
    at net.sourceforge.squirrel_sql.client.session.schemainfo.SchemaInfo.privateLoadTables(SchemaInfo.java:1209)
    at net.sourceforge.squirrel_sql.client.session.schemainfo.SchemaInfo.loadTables(SchemaInfo.java:410)
    at net.sourceforge.squirrel_sql.client.session.schemainfo.SchemaInfo.privateLoadAll(SchemaInfo.java:301)
    at net.sourceforge.squirrel_sql.client.session.schemainfo.SchemaInfo.reloadAll(SchemaInfo.java:202)
    at net.sourceforge.squirrel_sql.client.session.schemainfo.SchemaInfo.reloadAll(SchemaInfo.java:192)
    at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.ObjectTree$3.run(ObjectTree.java:305)
    at net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82)
    at java.base/java.lang.Thread.run(Thread.java:829)

[Gerd Wagner]

From the error message it is clear that it is a problem of your JDBC driver. The driver breaks on calling the standard JDBC method java.sql.DatabaseMetaData.getTables(...).

It may also be a version conflict between you DB and JDBC driver.

Here's a small Java program that should allow you to reproduce the problem outside SQuirreL:

package pack;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Bug1499Reproduction
{
   public static void main(String[] args) throws Exception
   {
      Class.forName("org.mariadb.jdbc.Driver");

      Connection con = DriverManager.getConnection(
            " jdbc:mariadb://10.1.2.3/?user=root&password=***", 
            "<yourUser>", 
            "<yourPassword>");

      con.getMetaData().getTables(null, null, null, null );
   }
}

[skipperTux]

Thanks for your fast reply. I found the MariaDB Connector/J upstream bug-report DatabaseMetadata#getTables with null value for tableNamePattern throws Syntax error. No error with the old stable driver version 2.7.5 and the regression shall be fixed in 3.0.4.
Sorry for raising the issue here. Case closed.

[Gerd Wagner]

Closed as requested.

[Markus Ueberall]

For the record, I'm facing the same problem using both SQuirreL SQL 4.3.0 and snapshot-20220227_2203 against MariaDB 10.5.15; the old stable driver version 2.7.5 of the Connector/J does not work for me.
However, the 3.0.4-SNAPSHOT (see here and here how to obtain it using, e.g., maven) works.