Bug 780745 (SOA-3198)

Summary: MySQL 5.1 - Type=InnoDB changed to ENGINE=InnoDB
Product: [JBoss] JBoss Enterprise SOA Platform 5 Reporter: Len DiMaggio <ldimaggi>
Component: Tooling, Build Process, Distribution, EDSAssignee: Van Halbert <vhalbert>
Status: CLOSED NEXTRELEASE QA Contact:
Severity: urgent Docs Contact:
Priority: urgent    
Version: 5.2.0 ER1CC: vhalbert
Target Milestone: ---   
Target Release: 5.2.0 GA, 5.2.0.ER2   
Hardware: Unspecified   
OS: Unspecified   
URL: http://jira.jboss.org/jira/browse/SOA-3198
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
RHEL 6.1, MySQL 5.1 - as supplied by RHEL6 SOA-P 5.2 ER1
Last Closed: 2011-09-30 19:23:09 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Attachments:
Description Flags
server.log none

Description Len DiMaggio 2011-07-20 17:01:28 UTC 
Workaround Description: Use ModeShape's DDL generation tool/utility to generate the DDL for Hibernate's MySQL InnoDB dialect, manually change the name of the "UTC_TIMESTAMP" column, and manually apply the DDL schema to the database. Then be sure to set the "autoGenerateSchema" property in the JpaSource (within the ModeShape configuration) to "disable".
project_key: SOA

This issue was originally logged as a feature request for MySQL 5.5 in: SOA-3170

But - the same error is being seen with RHEL6 and MySQL 5.1 - as installed by RHEL6

2011-07-20 12:51:48,892 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (modeshape-start-repo-2-thread-1) Unsuccessful: create table DNA_CHANGELOG (ID bigint not null auto_increment, CHANGES longblob not null, CHANGE_COUNT integer not null, UTC_TIMESTAMP bigint not null, USERNAME varchar(64) not null, primary key (ID)) ENGINE=InnoDB
2011-07-20 12:51:48,892 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (modeshape-start-repo-2-thread-1) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UTC_TIMESTAMP bigint not null, USERNAME varchar(64) not null, primary key (ID)) ' at line 1
2011-07-20 12:51:49,702 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (modeshape-start-repo-2-thread-1) Unsuccessful: create index NS_CHANGE_TS_INX on DNA_CHANGELOG (UTC_TIMESTAMP)
2011-07-20 12:51:49,702 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (modeshape-start-repo-2-thread-1) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UTC_TIMESTAMP)' at line 1
2011-07-20 12:51:52,941 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (modeshape-start-repo-2-thread-1) Unsuccessful: create table DNA_CHANGELOG (ID bigint not null auto_increment, CHANGES longblob not null, CHANGE_COUNT integer not null, UTC_TIMESTAMP bigint not null, USERNAME varchar(64) not null, primary key (ID)) ENGINE=InnoDB
2011-07-20 12:51:52,941 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (modeshape-start-repo-2-thread-1) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UTC_TIMESTAMP bigint not null, USERNAME varchar(64) not null, primary key (ID)) ' at line 1
2011-07-20 12:51:52,941 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (modeshape-start-repo-2-thread-1) Unsuccessful: create index NS_CHANGE_TS_INX on DNA_CHANGELOG (UTC_TIMESTAMP)
2011-07-20 12:51:52,941 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (modeshape-start-repo-2-thread-1) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UTC_TIMESTAMP)' at line 1
Comment 1 Len DiMaggio 2011-07-20 17:02:18 UTC 
Attachment: Added: server.log
Comment 2 Van Halbert 2011-07-21 15:49:18 UTC 
Link: Added: This issue Cloned to SOA-3204
Comment 3 Van Halbert 2011-07-21 18:35:47 UTC 
Can duplicate problem by trying to run the source create table statement using SQuirrel jdbc tool. However, when `UTC_TIMESTAMP`, has quotes added, the create now works. See http://dev.mysql.com/doc/refman/5.0/en/timestamp.html regarding this issue.
Comment 4 Van Halbert 2011-07-21 20:47:18 UTC 
Workaround Description: Added: Use ModeShape's DDL generation tool/utility to generate the DDL for Hibernate's MySQL InnoDB dialect, manually change the name of the "UTC_TIMESTAMP" column, and manually apply the DDL schema to the database. Then be sure to set the "autoGenerateSchema" property in the JpaSource (within the ModeShape configuration) to "disable".
Comment 6 David Le Sage 2011-08-26 05:20:24 UTC 
Release Notes Docs Status: Added: Not Required
Writer: Added: dlesage
Comment 7 Len DiMaggio 2011-09-30 19:23:09 UTC 
Verified fixed in ER4.