Setting hibernate.hbm2ddl.auto=validate causes problems on mySQL with numeric fields

Description

Hello,

We got a Problem when using the following setup:
1) JBoss 4.0.4A with Hibernate 3.2.0 / mySQl 4.x AND 5.x
2) Set hibernate.hbm2ddl.auto=validate
3) Using a usertype which uses the SQL type: Types.NUMERIC

When deploying our application I got the following error:

12:43:21,985 INFO [TableMetadata] columns: [paymenttype, amount, oid, remarks, currency, ae_date]
12:43:21,985 WARN [ServiceController] Problem starting service persistence.units:jar=com.riege.pogo_0.1.4.jar,unitName=PogoEntityManager
org.hibernate.HibernateException: Wrong column type: amount, expected: numeric(19,2)
at org.hibernate.mapping.Table.validateColumns(Table.java:251)
at org.hibernate.cfg.Configuration.validateSchema(Configuration.java:1002)

The Problem is:
1) When hibernate creates (e.g. hibernate.hbm2ddl.auto=update) through the columns in the mySQL database it creates it as "numeric(x,y)" field.
2) mySQL always converts "numeric(x,y)" to "decimal(x,y)". The metadata returns "decimal(x,y)"
3) When deploying with "hibernate.hbm2ddl.auto=validate" to error above is thrown. The problem is that hibernate expects a "numeric(x,y)" type but mysql returns a "decimal(x,y)"

A workaround I'm now using is a fixed mySQL dialect which correctly handles Types.DECIMAL / "decimal(x,y)":

public class FixedMySQLDialect extends org.hibernate.dialect.MySQLDialect {

public FixedMySQLDialect() {
super();
registerColumnType(Types.NUMERIC, "decimal(19, $l)");
registerColumnType(Types.DECIMAL, "decimal(19, $l)");
}
}

Environment

None

Activity

Show:
ChristianC
January 17, 2007, 12:50 PM

There is a similar problem when declaring String fields on mySQL with lenght less than 3 chars.

Hibernate creates these columns as type "VARCHAR(3)". mySQL automatically converts these into "CHAR(3)"
The deploy with "hibernate.hbm2ddl.auto=validate" fails, because hibernate expects "VARCHAR" but only gets "CHAR".
I know it is stupid of mySQL to change the given datatype, but thats the way it is and at the moment noone can really use the "validate" option on mySQL.

Diego Plentz
September 29, 2007, 10:46 PM

"In MySQL, NUMERIC is implemented as DECIMAL."
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Steve Ebersole
March 21, 2011, 7:05 PM

Bulk closing stale resolved issues

Assignee

Diego Plentz

Reporter

ChristianC

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure