I'm trying to use Java and Spring to read the table structure for a MySQL database. The code I've got (based on other answers on this site) is: If I call this code with the name of any of my database tables, it works perfectly. However, when I pass the name of any of my database views, I get the

How to read database view metadata

I’m trying to use Java and Spring to read the table structure for a MySQL database. The code I’ve got (based on other answers on this site) is:

private void dumpDefinition(JdbcTemplate jdbc, String table) throws SQLException {
    DatabaseMetaData metaData = jdbc.getDataSource()
        .getConnection()
        .getMetaData();

    try (
        ResultSet definition = 
           metaData.getColumns(null, null, table, null)     // Exception here
    ) {
        System.out.println(table);

        while (definition.next()) {
            String name = definition.getString("COLUMN_NAME");
            int type = definition.getInt("DATA_TYPE");

            System.out.println("   " + name + "    " + type);
        }
    } catch (SQLException | RuntimeException e) {
        e.printStackTrace();
    }
}

If I call this code with the name of any of my database tables, it works perfectly. However, when I pass the name of any of my database views, I get the following exception:

java.sql.SQLException: Index 8 out of bounds for length 7
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1206)
    at com.mysql.cj.jdbc.DatabaseMetaData$2.forEach(DatabaseMetaData.java:2162)
    at com.mysql.cj.jdbc.DatabaseMetaData$2.forEach(DatabaseMetaData.java:2091)
    at com.mysql.cj.jdbc.IterateBlock.doForAll(IterateBlock.java:56)
    at com.mysql.cj.jdbc.DatabaseMetaData.getColumns(DatabaseMetaData.java:2267)
    at my.package.MyClass.dumpDefinition(MyClass.java:69)
        --- omitted Spring Boot and Tomcat classes for clarity --- 

Caused by: java.lang.ArrayIndexOutOfBoundsException: Index 8 out of bounds for length 7
    at com.mysql.cj.protocol.a.NativePacketPayload.readInteger(NativePacketPayload.java:386)
    at com.mysql.cj.protocol.a.NativeServerSessionStateController$NativeServerSessionStateChanges.init(NativeServerSessionStateController.java:108)
    at com.mysql.cj.protocol.a.result.OkPacket.parse(OkPacket.java:64)
    at com.mysql.cj.protocol.a.NativeProtocol.readServerStatusForResultSets(NativeProtocol.java:1691)
    at com.mysql.cj.protocol.a.ResultsetRowReader.read(ResultsetRowReader.java:83)
    at com.mysql.cj.protocol.a.ResultsetRowReader.read(ResultsetRowReader.java:42)
    at com.mysql.cj.protocol.a.NativeProtocol.read(NativeProtocol.java:1587)
    at com.mysql.cj.protocol.a.TextResultsetReader.read(TextResultsetReader.java:87)
    at com.mysql.cj.protocol.a.TextResultsetReader.read(TextResultsetReader.java:48)
    at com.mysql.cj.protocol.a.NativeProtocol.read(NativeProtocol.java:1600)
    at com.mysql.cj.protocol.a.NativeProtocol.readAllResults(NativeProtocol.java:1654)
    at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:1000)
    at com.mysql.cj.protocol.a.NativeProtocol.sendQueryString(NativeProtocol.java:933)
    at com.mysql.cj.NativeSession.execSQL(NativeSession.java:664)
    at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1174)
    ... 72 more

I’ve tried bumping the SQL driver to the latest version (8.0.27) but that didn’t help.

As a work around I tried:

try (
        PreparedStatement statement = jdbc.getDataSource()
            .getConnection()
            .prepareStatement("SELECT * from mySchema." + table + " WHERE 1 = 0");
        ResultSet resultSet = statement.executeQuery()  // Exception here
    ) {
        ResultSetMetaData meta = resultSet.getMetaData();
                    etc

But again, I get the same SQLException – an array out of bounds exception.

Any ideas?

Additional

The following code dumps all the database table columns, but fails with the same exception as soon as a view is introduced into the schema

private void readDefinitions(JdbcTemplate jdbc) {
    try (
        ResultSet definition = jdbc.getDataSource()
            .getConnection()
            .getMetaData()
            .getColumns(null, "mySchema", null, null) // Exception here
    ) {
        while (definition.next()) {
            String name = definition.getString("COLUMN_NAME");
            int type = definition.getInt("DATA_TYPE");
            String table = definition.getString("TABLE_NAME");

            System.out.println(table + " - " + name + " => " + type);
        }
    } catch (SQLException | RuntimeException e) {
        e.printStackTrace();
    }
}

Am I looking at a JDBC bug?

Update

The JDBC Driver as given in my POM is:

 <dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
     <version>8.0.27</version>
 </dependency>

metaData.getDriverVersion() returns mysql-connector-java-8.0.27 (Revision: e920b979015ae7117d60d72bcc8f077a839cd791)

I’ve also tried versions 8.0.21 and 5.1.49, but they behave the same way.

This is all running on a Windows 11 PC with Java 17.0.1

In the code above I’ve highlighted the line that causes the exception – it’s the one that creates the results I need to process (before I attempt to read any of the columns)
WRT The stack trace – The exception I catch is a java.sql.SQLException – it’s not wrapped inside anything.
The Database I’m talking to is on a Raspberry Pi. The details are:

Version: 10.3.23-MariaDB-0+deb10u1 (Raspbian 10)
Compiled for: debian-linux-gnueabihf (armv7l)

Answer

The error seems to be a protocol issue (it’s expecting more bytes than it received), and given you’re connecting to MariaDB, not MySQL, you should be using the MariaDB Connector/J (version 2.7.3), and protocol prefix jdbc:mariadb:.

Although MariaDB started as a fork of MySQL, it has diverged since it was forked.

Advertisement

Answer