Bug #30226 JDBC driver not returning table meta data
Submitted: 3 Aug 2007 10:40 Modified: 13 Aug 2007 18:36
Reporter: Sadeepa Palliyaguru Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.0.7 OS:Windows
Assigned to: CPU Architecture:Any
Tags: connector, getColumns(), metadata

[3 Aug 2007 10:40] Sadeepa Palliyaguru
Description:
While accessing MySQL Enterprise Edition (Version : 5.0.36sp1-enterprise-gpl)   using the JDBC driver (mysql-connector-java-5.0.7 driver), we got the following problem.

One of the tables in our database contains 5 fields with date, time, datetime, timestamp and year Field Types.

We could retrieve the table field information using getColumns() method, on DatabaseMetadata with mysql-connector-java-3.0.11 driver. 

We wanted the same thing to happen with mysql-connector-java-5.0.7 driver.

However when we executed the same code with mysql-connector-java-5.0.7 driver, it returned null for field size. 
 
The steps followed and code segments used are as follows:

1. A database connection (conn) is obtained 
 
2. Get the database metadata and field sizes using:

    DatabaseMetaData databaseMetaData;

    databaseMetaData = conn.getMetaData();

    ResultSet rs = databaseMetaData.getColumns(null,null,tableName,"%");

    while (rs.next()) {

        String fieldSize=rs.getString(7);
    }

We could obtain the metadata of other types (varchar / char / etc)

We also used JBuilder's "Database Pilot" tool to check this. It returnd 0 as the size for the above fields when "mysql-connector-java-5.0.7 driver" is used and the correct size when "mysql-connector-java-3.0.11" is used.

How to repeat:
1. Create a table with date, time, datetime, timestamp and year Field Types. On MySQL 5.0.36sp1-enterprise-gpl

2. Create a database connection to the MySql Enterprise Edition DB and mysql-connector-java-5.0.7  
 
3. Try to get the database metadata and field sizes using:
    DatabaseMetaData databaseMetaData;
    databaseMetaData = conn.getMetaData();
    ResultSet rs = databaseMetaData.getColumns(null,null,tableName,"%");
    while (rs.next()) {
        String fieldSize=rs.getString(7);
    }
4. You will receive null for the field size instead of the expected value.
[3 Aug 2007 10:49] Sadeepa Palliyaguru
When we connected to the same database using mysql-connector-odbc-3.51.16-win32 we were able to receive the expected results.
[6 Aug 2007 8:12] Tonci Grgin
Hi Sadeepa and thanks for your report. I'll repeat my e-mail here:

I don't see the problem here, at least until I have your code. According to manual and as of c/J 3.1.4 we have this behavior:

• Datetimes with all-zero components (0000-00-00 ...) — These values can not be represented reliably in Java. Connector/J 3.0.x always converted them to NULL when being read from a Result-Set.
Connector/J 3.1 throws an exception by default when these values are encountered as this is the most correct behavior according to the JDBC and SQL standards. This behavior can be modified using the zeroDateTimeBehavior configuration property. The allowable values are:
• exception (the default), which throws an SQLException with an SQLState of S1009.
• convertToNull, which returns NULL instead of the date.
• round, which rounds the date to the nearest closest value which is 0001-01-01.
Starting with Connector/J 3.1.7, ResultSet.getString() can be decoupled from this behavior via noDatetimeStringSync=true (the default value is false) so that you can get retrieve the unaltered all-zero value as a String. It should be noted that this also precludes using any time zone conversions, therefore the driver will not allow you to enable noDatetimeStringSync and useTimezone at the same time.

As the exception is raised, according to JDBC specs, you'll never get what you expect. In turn, as ODBC is completely different specification, it's no wonder you get what you expect using it.

I can tell more when I see your test case, so please attach one.
[6 Aug 2007 17:37] Mark Matthews
This isn't really a bug. It's so that DatabaseMetaData.getColumns() when using "SHOW" commands is equivalent to the data returned by the mysql server when we use INFORMATION_SCHEMA queries. The _server_ reports NULL lengths for DATETIME columns.

We could consider adding a "compatibility" flag, but for now it's expected behavior. This behavior change is due to the fix for Bug#23304.
[8 Aug 2007 8:53] Sadeepa Palliyaguru
Thank you everyone for contributing . We will consider it to be the expected behavior.