hi,

Hibernate version: 3.2.0
Name and version of the database: MySQL 5.0.27 InnoDB on windows

i'm doing schema validation with

<propertyname="dialect">
org.hibernate.dialect.MySQL5InnoDBDialect</property>
<property name="hbm2ddl.auto">validate</property>

against a database schema that was created in MySQL without the help of hibernate.

when i do schema validation against a table with a decimal/numeric column the hibernate schema validator always reports a datatype mismatch, saying that it expected "numeric".

my investigation resulted in these findings:

i observe that MySQL always reports numeric/decimal columns as decimal. i.e.,

CREATE TABLE `T` (
`d` decimal(10,0) NULL,
`n` numeric(10,0) NULL
)

will create a table where both columns d and n are of dataype decimal.

the hibernate MySQL5InnoDBDialect ultimately inherits from MySQLDialect and this registers the following datatype:

registerColumnType(2, "numeric($p,$s)");

indeed i find that if i change that to

registerColumnType(2, "decimal($p,$s)");

the aforementioned problems go away.

is this a genuine bug?

cheers,
gerald

Hibernate version: 3.2.2
Name and version of the database: MySQL 5.0.28 InnoDB on Linux Debian 4.0

I'm doing schema validation with:


Got the result:



Gerald's fix looks pretty good, since MySql does use "decimal" internally for numeric type. My question is: how do I call registerColumnType(2,"decimal($p,$s)")?

And there lacks sql-type equivalent in the annotation....otherwise I could also supress this problem by writing



Thanks in advance!

Ellen

hi ellen,



The only solution i saw was to simply create my own patched mysql dialects based on the mysql dialects that ship with hibernate:

- MySQLPatchedDialect is identical to MySQLDialect but in it's constructor calls registerColumnType(2, "decimal($p,$s)") (rather than registerColumnType(2, "numeric($p,$s)")).
- MySQL5PatchedDialect is identical to MySQL5Dialect but extends MySQLPatchedDialect (rather than MySQLDialect)
- MySQL5InnoDBPatchedDialect is identical to MySQL5InnoDBDialect but extends MySQL5PatchedDialect (rather than MySQL5Dialect)
- in my hibernate mapping file i then use MySQL5InnoDBPatchedDialect rather than MySQL5InnoDBDialect.

Not very elegant but safe ;-)

Please note that i've also created the JIRA issue HHH-2582 for this.

cheers,
gerald

Hi Gerald,

I looked at the HHH-2582, added a vote and added a comment. Thank you very much for this hint. :-) This type-mismatch problem has bugged me for the whole day, your solution made me productive again. :-)


Best Regards,
Ellen

I downloaded the 3.2.4 sp1 and saw that this problem is still not solved. It is causing a lot of problem for MySQL DB users and it is so easy to fix, could someone in the hibernate team please modify one line of code so that I do not have to patch again and again whenever there is a new release of hibernate?

In the package:
org.hibernate.dialect

In the file:
MySQLDialect.java

change line 39 from
registerColumnType( Types.NUMERIC, "numeric($p,$s)" );

to
registerColumnType( Types.NUMERIC, "decimal($p,$s)" );

And that's it.

I saw this file was last changed on 2006-12-08 by Mr. steve.ebersole@jboss.com. If Mr. Ebersole is around could you please have a look? thank you very much!


Ellen

I had the same issue with DB2400Dialect.

and
registerColumnType( Types.VARCHAR, "char($l)" );