Description:
In our production system we automatically alter tables and add columns that are required with newer versions of the software. For doing this, we retrieve the metadata from the connection and iterate through all the columns returned via DatabaseMetaData.getColumns(null, null, "thetable", null). Up to JDBC connector version 5.1.47, this call returns only the columns of the table from the database that we are connected to. Later versions (confirmed with 6.0.3 and 8.0.16), this call now returns the columns from ALL the databases (aka catalogs) that the database user has access to (in our case, multiple databases with the same layout on this server). This resulted in our code thinking that all columns were present, as we just collected the column names in a HashSet<String> when iterating through the ResultSet (only one of the databases had the newer column already added), but when adding data via INSERT statements, these queries failed due to the missing column.
In the release notes of the 6.0 version of the JDBC driver, nothing was mentioned that the behavior of "DatabaseMetaData.getColumns(...)" had changed:
https://web.archive.org/web/20160803153832/http://dev.mysql.com/doc/connector-j/6.0/en/con...
This change has the potential to break a fair amount of legacy code. We had to revert a rollout of a new version of our software, as we couldn't figure out at the time why all the INSERT statements were failing.
How to repeat:
Create two test databases ("test1" and "test2"), each with one table called "table1". In the first database, this table has one column, in the second, two columns. Create a user called "jdbctest" and give it access to both of the databases:
CREATE DATABASE test1;
CREATE TABLE test1.table1 (
col1 INTEGER NOT NULL
);
CREATE DATABASE test2;
CREATE TABLE test2.table1 (
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL
);
CREATE USER 'jdbctest'@'localhost' IDENTIFIED BY 'jdbctest';
GRANT ALL PRIVILEGES ON test1.* TO 'jdbcuser'@'localhost';
GRANT ALL PRIVILEGES ON test2.* TO 'jdbcuser'@'localhost';
FLUSH PRIVILEGES;
The following Java class "JdbcTest" connects to database "test1", retrieves the columns for table "table1" and outputs the catalog and column name in the terminal:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.Statement;
public class JdbcTest {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test1", "jdbctest", "jdbctest");
DatabaseMetaData meta = conn.getMetaData();
// 6.0.3 requires columnNamePattern to be neither null nor empty,
// 8.0.16 is fine with null again, like 5.1.47
ResultSet rs = meta.getColumns(null, null, "table1", "%");
while (rs.next()) {
System.out.println(rs.getString("TABLE_CAT") + " - " + rs.getString("COLUMN_NAME"));
}
rs.close();
conn.close();
}
}
Using version 5.1.47, you get the following (expected) output:
test1 - col1
Using 6.0.3 or 8.0.16, you get the columns from both databases:
test1 - col1
test2 - col1
test2 - col2
Suggested fix:
Only output from the database that you are connected to should be returned (just like in 5.1.47).
When being in control of the code, you can create a workaround by specifying the catalog:
ResultSet rs = meta.getColumns(conn.getCatalog(), null, "table1", "%");
However, this approach won't work if you have third-party dependencies outside your control, that still use the old approach of using "null" for the catalog.
