cum grano salis — nobody likes a clever bastard

Being TopLink’s mutant flipper baby, it’s no surprise that EclipseLink’s (2.0.0) support of MySQL is substandard. If you have been using the two of them together, you may have noticed that LOBs don’t really work quite right. Some of the blame is probably for MySQL, with its multiple length-specific LOB column types, but it at least handles specified lengths and does the work for you. So, if you have been getting exceptions when you’re trying to insert a bunch of data, you’re using EL’s DDL generation, and you have been lazy about fixing it yourself, read on…

64000. Yep, that’s it. (Insert “64k ought to be enough” joke.) That’s all you get if you use a LOB type with EL and MySQL. It’s hard coded and it ignores the @Column length. I don’t know how important it is to abide by the JPA spec here. It doesn’t seem to be specified anywhere obvious that @Lob should obey @Column’s length, but Hibernate does that and it’s kind of useless on MySQL without it. Being that MySQL is basically a de facto standard in the OSS world, you would expect it to be usable on MySQL. (Yeah, I know that there are others. Big deal. I have never felt the need to reach for an alternative RDBMS, more often opting for distributed object stores or whatever.) Regardless, the JPA spec seems optional for EL, since it doesn’t really seem to follow it. (Hey, why should it work as expected when it can be faster!) Anyway, 64000…

The first suspect that’s broken in EclipseLink is o.e.p.platform.database.MySQLPlatform, which essentially blows at describing the underlying database’s capabilities and SQL quirks. It basically locks the column types for CLOB and BLOB to TEXT and BLOB, all with a fixed 64000 length. MySQL basically ignores that length and turns them into plain BLOB and TEXT columns. This doesn’t do us any good if we need MEDIUMBLOB, LONGTEXT, etc. This enables setting of the size on these field definitions so @Column’s length actually ends up somewhere.

diff -ur f/org/eclipse/persistence/platform/database/MySQLPlatform.java src/org/eclipse/persistence/platform/database/MySQLPlatform.java
--- f/org/eclipse/persistence/platform/database/MySQLPlatform.java	2009-11-04 16:00:56.000000000 -0600
+++ src/org/eclipse/persistence/platform/database/MySQLPlatform.java	2010-01-22 03:19:41.000000000 -0600
@@ -129,12 +129,18 @@
         fieldTypeMapping.put(String.class, new FieldTypeDefinition("VARCHAR", 255));
         fieldTypeMapping.put(Character.class, new FieldTypeDefinition("CHAR", 1));

-        fieldTypeMapping.put(Byte[].class, new FieldTypeDefinition("BLOB", 64000));
-        fieldTypeMapping.put(Character[].class, new FieldTypeDefinition("TEXT", 64000));
-        fieldTypeMapping.put(byte[].class, new FieldTypeDefinition("BLOB", 64000));
-        fieldTypeMapping.put(char[].class, new FieldTypeDefinition("TEXT", 64000));
-        fieldTypeMapping.put(java.sql.Blob.class, new FieldTypeDefinition("BLOB", 64000));
-        fieldTypeMapping.put(java.sql.Clob.class, new FieldTypeDefinition("TEXT", 64000));
+        final FieldTypeDefinition blobFieldTypeDefinition = new FieldTypeDefinition("BLOB", 64000);
+        blobFieldTypeDefinition.setIsSizeAllowed(true);
+
+        final FieldTypeDefinition clobFieldTypeDefinition = new FieldTypeDefinition("TEXT", 64000);
+        clobFieldTypeDefinition.setIsSizeAllowed(true);
+
+        fieldTypeMapping.put(Byte[].class, blobFieldTypeDefinition);
+        fieldTypeMapping.put(Character[].class, clobFieldTypeDefinition);
+        fieldTypeMapping.put(byte[].class, blobFieldTypeDefinition);
+        fieldTypeMapping.put(char[].class, clobFieldTypeDefinition);
+        fieldTypeMapping.put(java.sql.Blob.class, blobFieldTypeDefinition);
+        fieldTypeMapping.put(java.sql.Clob.class, clobFieldTypeDefinition);

         fieldTypeMapping.put(java.sql.Date.class, new FieldTypeDefinition("DATE", false));
         fieldTypeMapping.put(java.sql.Time.class, new FieldTypeDefinition("TIME", false));

The second is the pukerrific(!) o.e.p.tools.schemaframework.DefaultTableGenerator, which generates the DDL. Originally, it only obeyed the @Column length for, essentially, basic String values. Even if you set a length, it would silently ignore it. You would never know without running into it after deployment or verifying the schema by hand. If you’re going to have to do that, you may as well throw out your computers and use pencil and paper. Someone must not know what fail-fast means.

diff -ur f/org/eclipse/persistence/tools/schemaframework/DefaultTableGenerator.java src/org/eclipse/persistence/tools/schemaframework/DefaultTableGenerator.java
--- f/org/eclipse/persistence/tools/schemaframework/DefaultTableGenerator.java	2009-11-24 18:02:34.000000000 -0600
+++ src/org/eclipse/persistence/tools/schemaframework/DefaultTableGenerator.java	2010-01-22 03:48:10.000000000 -0600
@@ -683,7 +683,9 @@
                 if ((fieldType != null)) {
                     if (fieldType.equals(ClassConstants.STRING) ||
                        fieldType.equals(ClassConstants.APCHAR)  ||
-                       fieldType.equals(ClassConstants.ACHAR)) {
+                       fieldType.equals(ClassConstants.ACHAR) ||
+                       fieldType.equals(ClassConstants.BLOB) ||
+                       fieldType.equals(ClassConstants.CLOB)) {
                         // The field size is defaulted to "255" or use the user supplied length
                         fieldDef.setSize(dbField.getLength());
                     } else {

After these minor changes, maybe everything explodes for other database systems. I don’t really care. It works for me. Countdown to infinity, as we wait for EclipseLink developers to find this sitting here and then include it in EclipseLink, starts now!

EclipseLink Hates MySQL… Probably by cum grano salis — nobody likes a clever bastard, unless otherwise expressly stated, is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 United States License.