MySQL won't map String PK properly

[pmalone]

Apologies in advance if I am doing something silly....
I have been using HyperSQL as a means of CMP persistence in JBoss 3.0.4. I have an EJB with a PK called 'id' of type String and everything has worked fine.

Now I would prefer to use mySQL, but when I initially deployed my bean I got an SQLException when trying to create the table:

java.sql.SQLException: Invalid argument value, message from server: "Too big column length for column 'id' (max = 255). Use BLOB instead"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1628)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:886)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:945)
at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:917)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1806)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1740)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1309)
at org.jboss.resource.adapter.jdbc.local.LocalStatement.executeUpdate(LocalStatement.java:231)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createTable(JDBCStartCommand.java:166)

I have set the default mapping to :
java:/DefaultDS
<type-mapping>mySQL</type-mapping>

in both standardjaws.xml and standardcmp-jdbc

I have changed the mySQL mapping for String to BLOB in both of these files

<java-type>java.lang.String</java-type>
<jdbc-type>BLOB</jdbc-type>
<sql-type>BLOB</sql-type>

in the jaws file
and the cmp file....

I have also renamed the mySQL DS JNDIName in the mysql-service.xml to
DefaultDS
and removed the hsqldb-service.xml completely.

Still i get the same error.... It is clearly not using the mapping that I have set :-(

Any ideas of where I have gone wrong or how can i switch on debug so I can see what DS / mapping is actually being used?

Any help would be really appreciated.

[jcordes]

Hi !

I think that the maximum length for "strings" in MySQL is indeed 250. As the exception states you have to use a MySQL blob-type instead. Now comes the tricky part: the jdbc counterpart is "CLOB". BTW: you can override the mapping on a per field basis like this in jbosscmp-jdbc.xml:

<cmp-field>
<field-name>myField</field-name>
<column-name>my_field</column-name>
<jdbc-type>CLOB</jdbc-type>
<sql-type>BLOB</sql-type>
</cmp-field>

HTH,

Jochen.

[fbiaggi]

HI,
fyi
>in MySQL is indeed 250
255.

Ciao