Headsup - FIXED Entity from Database problem with Netbeans 7.0 and MySQL 5.5.11 on OSX

[mattski]

Hi Everyone,

I've recently installed Netbeans and MySQL on OS-X to do some J2EE development and have run into a problem with creating entity classes from my database due to primary keys not being identified by the wizard.

Before describing the problem and solution, here are the versions of the software I've installed:-

Product Version: NetBeans IDE 7.0 (Build 201104080000)
Java: 1.6.0_24; Java HotSpot(TM) 64-Bit Server VM 19.1-b02-334
System: Mac OS X version 10.6.7 running on x86_64; MacRoman; en_US (nb)
Userdir: /Users/matt/.netbeans/7.0
/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.5.11, for osx10.6 (i386) using readline 5.1
JDBC driver mysql-connector-java-5.1.13-bin.jar

I've also tried pointing Netbeans at MySQL on a CentOS 5 platform and interesting that seems to work, the version of MySQL is:-
mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1

So, the problem appears to be something to do with MySQL on OS-X. I've created my schema and here's an abridged fragment of a couple of the tables, note the mixed-case table names:-

CREATE TABLE elementType
(
elementTypeID INTEGER NOT NULL,
name VARCHAR(20) NOT NULL,

CONSTRAINT elementType_pk PRIMARY KEY (elementTypeID)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;

CREATE TABLE managedElement
(
instanceID INTEGER NOT NULL AUTO_INCREMENT,
caption VARCHAR(64) NOT NULL,
elementTypeID INTEGER NOT NULL,

CONSTRAINT managedElement_pk PRIMARY KEY (instanceID),

CONSTRAINT managedElement_elementType_fk FOREIGN KEY (elementTypeID)
REFERENCES elementType(elementTypeID) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;

If you look at what the OS-X MySQL's created in the data directory and in show tables, it's not taken the mixed-case name into account for managedElement but has for elementType; seems a bit random.

The fix to this problem is to set the lower_case_table_names property to 1 in your my.cnf file; once that was done Netbeans correctly listed all of the tables in the wizard. NOTE: MySQL on OS-X does not by-default have a my.cnf file and uses defaults, to get this take a look at the examples in /usr/local/mysql/support-files; I used the large one.