[hibernate-issues] [Hibernate-JIRA] Commented: (HBX-851) MySQL primary keys are not completely detected

[Zeljko Trogrlic (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HBX-851?page=comments#action_25797 ] 

Zeljko Trogrlic commented on HBX-851:
-------------------------------------

I took a table with varchar primary key:

mysql> desc api5_ldap_base;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| ID         | varchar(5)  | NO   | PRI |         |       |
| PARAMETERS | text        | NO   |     |         |       |
| ROOTDN     | varchar(10) | NO   |     |         |       |
| ROOTPW     | varchar(10) | NO   |     |         |       |
| SUFFIX     | varchar(10) | NO   |     |         |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.03 sec)

If you try to get primary keys from JDBC driver, it receives empty result set.

On the other hand, with this table:

mysql> desc nots_notification;
+-----------------------------+-----------------------+------+-----+-------------------+----------------+
| Field                       | Type                  | Null | Key | Default           | Extra          |
+-----------------------------+-----------------------+------+-----+-------------------+----------------+
| id                          | int(10) unsigned      | NO   | PRI | NULL              | auto_increment |
| hostname                    | varchar(85)           | NO   |     |                   |                |
<SNIP>
| accounting_off_notification | enum('Y','N')         | NO   |     | N                 |                |
+-----------------------------+-----------------------+------+-----+-------------------+----------------+
16 rows in set (0.16 sec)

it returns

----- keys-----
TABLE_CAT=configuration
TABLE_SCHEM=null
TABLE_NAME=nots_notification
COLUMN_NAME=id
KEY_SEQ=1
PK_NAME=PRIMARY
\\\\\\keys/////

It was tested with both old and latest drivers:

getDriverVersion: mysql-connector-java-3.0.15-ga ( $Date: 2004/08/09 22:15:11 $, $Revision: 1.27.2.43 $ )
getDriverVersion: mysql-connector-java-5.0.4 ( $Date: 2006-10-19 17:47:48 +0200 (Thu, 19 Oct 2006) $, $Revision: 5908 $ )

Code used for testing:

        DatabaseMetaData dmd = connection.getMetaData();
        System.out.println("getDriverName: " + dmd.getDriverName());
        System.out.println("getDriverVersion: " + dmd.getDriverVersion());
        System.out.println("get major+minor: " + dmd.getDriverMajorVersion() + "." + dmd.getDriverMinorVersion());
        ResultSet keys = dmd.getPrimaryKeys("configuration", null, "api5_ldap_base");
        printResultSet("keys", keys);
        keys.close();

    private static void printResultSet(String title, ResultSet tables) throws SQLException {
        System.out.println("----- " + title + "-----");
        tables.next();
        ResultSetMetaData rsmd2 = tables.getMetaData();
        for (int i = 1; i <= rsmd2.getColumnCount(); ++i) {
            System.out.println(rsmd2.getColumnName(i) + "=" + tables.getString(i));
        }
        System.out.println("\\\\\\\\\\\\" + title + "/////");
    }

> MySQL primary keys are not completely detected
> ----------------------------------------------
>
>          Key: HBX-851
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HBX-851
>      Project: Hibernate Tools
>         Type: Bug

>   Components: reverse-engineer
>     Versions: 3.2beta10
>  Environment: JBoss Seam 1.1.0.GA with MySQL 5
>     Reporter: Zeljko Trogrlic
>  Attachments: MySqlMetaDataDialect.java
>
>
> Due to bug in MySQL JDBC driver, tools detects only int primary keys and not other types, like varchar.
> This can be fixed with MySQL specific MetaDataDialect - fix attached.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira