[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2582) foncusion of DECIMAL and NUMERIC column types in MySQL5

[Ellen Zhao (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2582?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_26807 ] 

Ellen Zhao commented on HHH-2582:
---------------------------------

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:
@Column(name = "CHO_Factor", scale=2, precision=4, nullable=true)
  public BigDecimal getChoFactor() {
    return choFactor;
  }


Got the result:

javax.persistence.PersistenceException: org.hibernate.HibernateException: Wrong column type: CHO_Factor, expected: numeric(4,2)


Gerald's fix looks pretty good, since MySql does use "decimal" internally for numeric type. 

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

@Column(name = "CHO_Factor", sqltype="decimal(4,2)", nullable=true)
  public BigDecimal getChoFactor() {
    return choFactor;
  }

I downloaded the hibernate-core source code and changed this line:

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

to this:

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


compiled this file and swapped the old MySQLDialect.class with my new MySQLDialect.class. The problem was gone.



Best Regards,
Ellen


> foncusion of DECIMAL and NUMERIC column types in MySQL5
> -------------------------------------------------------
>
>                 Key: HHH-2582
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2582
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.2.0.ga
>         Environment: MySQL 5.0.27 InnoDB on windows
>            Reporter: Gerald Loeffler
>
> when doing schema validation with
>   <property name="dialect">
>     org.hibernate.dialect.MySQL5InnoDBDialect</property>
>   <property name="hbm2ddl.auto">validate</property>
> against a table with a DECIMAL/NUMERIC columns, the hibernate schema validator always reports a column type mismatch, saying that it expected "NUMERIC".
> this can be explained as follows:
> 1. MySQL always reports columns created as NUMERIC or DECIMAL 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.
> 2. the hibernate MySQL5InnoDBDialect ultimately inherits from MySQLDialect and this registers the following column type:
>   registerColumnType(2, "numeric($p,$s)");
> which means that when doing schema validation against a column of type DECIMAL hibernate will report a type mismatch.
> indeed, if one creates a new hibernate dialect which is identical to MySQL5InnoDBDialect/MySQLDialect  in all respects apart from registering the following column typ
>   registerColumnType(2, "decimal($p,$s)");
> then the aforementioned schema validation error is not reported, i.e., schema validation succeeds as expected.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira