Bug #118100 `You have an error in your SQL syntax` exception thrown after `mysql-connector-j` `9.2.0` -> `9.3.0` upgrade

• Submitted: 1 May 21:15
• Modified: 7 Jul 21:12
• Reporter: Jack Green
• Status: Closed
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 9.3.0
• OS: Any
• CPU Architecture: Any

[1 May 21:15] Jack Green

Description:
Having upgraded `mysql-connector-j` `9.2.0` -> `9.3.0`, a previously-working query fails with:
```
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HAVING TABLE_TYPE IN ('TABLE','VIEW',null,null,null) ORDER BY TABLE_TYPE, TABLE_' at line 1
```

How to repeat:
`Reproducer.java`:
```
package com;

import org.testcontainers.containers.*;
import java.sql.*;

/** Simple Java reproducer that connects to a mysql instance running in Docker and executes a query */
public class Reproducer {
    public static void main(String[] args) throws Exception {
        try (JdbcDatabaseContainer<?> container = new MySQLContainer<>("mysql:lts")
                .withUrlParam("user", "root")
                .withUrlParam("password", "test")) {
            container.start();

            try (ResultSet tables = DriverManager.getConnection(container.getJdbcUrl())
                    .getMetaData()
                    .getTables(null, null, null, new String[] {"TABLE", "VIEW"})) {
            }
        }
    }
}
```

`pom.xml`:
```
<project xmlns="http://maven.apache.org/POM/4.0.0" 
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
                             http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>org.me</groupId>
    <artifactId>mysql-reproducer</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <dependencies>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>mysql</artifactId>
            <version>1.21.0</version>
        </dependency>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>${mysql.connector.version}</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <version>3.5.0</version>
                <configuration>
                    <mainClass>com.Reproducer</mainClass>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>
```

Execute from CLI:
```
mvn compile exec:java -Dmysql.connector.version=9.3.0
```

You can use `9.2.0` as an argument to illustrate it's a regression.

[2 May 6:31] MySQL Verification Team

Hello Jack Green,

Thank you for the report and test case.

regards,
Umesh

[7 Jul 21:12] Daniel So

Posted by developer:
 
Added an entry to the Connector/J 9.4.0 changelog: 

"When using Connector/J 9.3.0, calling DatabaseMetaDataInformationSchema.getTables with the arguments catalog, schemaPattern and tableNamePattern set to null caused an SQL syntax exception."

[16 Jul 18:12] Daniel So

Posted by developer:
 
Updated the changelog entry to the following: 

"When using Connector/J 9.3.0 0 with useInformationSchema=true, calling DatabaseMetaDataInformationSchema.getTables with the arguments catalog, schemaPattern and tableNamePattern set to null caused an SQL syntax exception."

[18 Jul 20:33] Daniel So

Posted by developer:
 
Updated the changelog entry to the following: 

"When using Connector/J 9.3.0 0 with useInformationSchema=true, calling DatabaseMetaData.getTables with the arguments catalog, schemaPattern and tableNamePattern set to null caused an SQL
syntax exception."