maxValue in table jidatatype on MySql 5.5

[pjtech]

Hi Guys,

I am having an issue where JasperServer is bombing out on MySql 5.5 both on the install and when I attempt to insert a Data Type. Table  jidatatype has a column name of maxValue which I beleve is a reserved word on 5.5.  Has anyone had the same issue or got a workaround?

Thanks Peter

 

[swood]

We haven't tested the repository against MySQL 5.5.

 

Try putting the column name in quotes, like `maxValue`.

 

 

Sherman

Jaspersoft

[pjtech]

Hi,

Thanks for the quick reply.  Yes I done that and you can load the schema.  But the problem is you still cant insert a datatype as the insert bombs out.  For now I reverted to MySql 5.1 and its working fine.

Again Thanks for yor help.

Peter

 

[ahagopian]

Code:

With MySQL 5.5 going GA yesterday (Dec 15) any plans on fixing this in jasperserver anytime soon?  This is unfortunately keeping us from being able to upgrade to MySQL 5.5 and take advantage of all its performance goodies.



Thanks

[planetzone]

I have the same problem and really need a fix for this. I already moved all my databases to MySQL 5.5 and it would cost me a lot of time to move back to 5.1.

When can the community edition user expect a patch?

[ernestoo]

Wondering if this is only applicable to Microsoft Windows MySQL 5.5 users?

The reserved word is MAXVALUE and the table name is maxValue so I think in Linux it should work just fine.

8.2.2. Identifier Case Sensitivity

In MySQL, databases correspond to directories within the data directory.

Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine).

Triggers also correspond to files. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. This means such names are not case sensitive in Windows, but are case sensitive in most varieties of Unix.

http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html

[ahagopian]

I can confirm this is an issue on linux too.

[cyberdemos]

Hi Swood,
Is there any news about this fix?

This should be very important or i have to pass back to Mysql 5.1...

Regards,
Cyb.

[mdahlman]

Cyb,

If you have support, you should contact technical support to see when an update for this is expected. I don't know when it's planned for.

In principle it shouldn't be so difficult to change the column to max_value and then update the hibernate mapping to understand that maxValue maps to the column max_value. Give it a try. It would be a great benefit to others if you can let us know how it works for you.

The actual change would be to jasperserver-repository-hibernate/src/main/resources/com/jaspersoft/jasperserver/api/metadata/common/service/impl/hibernate/persistent/RepoDataType.hbm.xml

Just change the name of the column to something MySQL 5.5 likes:

<hibernate-mapping>
    <joined-subclass 
           name="com.jaspersoft.jasperserver.api.metadata.common.service.impl.hibernate.persistent.RepoDataType"
           extends="com.jaspersoft.jasperserver.api.metadata.common.service.impl.hibernate.persistent.RepoResource"
           table="JIDataType">
        <key column="id"/>
        <property name="type"/>
        <property name="maxLength"/>
        <property name="decimals"/>
        <property name="regularExpr"/>
        <property name="minValue" type="serializable"/>
        <property name="maxValue" column="max_value" type="serializable"/>
        <property name="strictMin"/>
        <property name="strictMax"/>
    </joined-subclass>
</hibernate-mapping>

The js-create.ddl script (which is failing for you now) will be generated with the new column name when you rebuild.

Before any of this gets rolled into the real product we need to make sure it works (duh!) but we also need to figure out any upgrade issues. You may not care about that side of things, so you could try it more quickly.

Good luck,
Matt

P.S. Credit goes to Bob T for the code sample.

[ahagopian]

 FYI I opened an issue on this a while back.  I also made a similiar change but instead of changing the column name, simply had hibernate quote the column (the brackets are the platform independant way to tell hibernate to quote the column).

What's nice about this approach is no schema changes are necessary and hibernate knows how to quote a column in any database server it is connecting to.  

http://jasperforge.org/plugins/mantis/view.php?id=5008

Code:

Post Edited by ahagopian at 02/22/2011 19:54

[cyberdemos]

Hi mdahlman,

i tested your code this morning and all is ok!

I modified only xml file to see if it was right... i hope this fix will be in the next jasperserver release.

 

Thank you very much to you, Bob T and ahagopian !!!!

 

Regards,

 

Cyb.

[sjongenelen]

i can confirm this works on MySQl 5.5.10 32bit CE with MS Windows Server 2008 64 bit:

 <property name="maxValue" column="[maxValue]" type="serializable"/>