PDA

View Full Version : Migration Hypersonic to Mysql



rokydaba
01-27-2006, 04:49 AM
Hello,

would you help us.

We want to use mysql in place to hypersonic. Here is how we process.

-> First we create the source of datas named (quartzmusql, sharkmysql, sampledatamysql, hibernatemysql).


-> Second we change the file of configuration

-> At the end we want to import (quartz, sharkl, sampledata, hibernate) data base into the first. For this work we do "import name_of_name_fic.script for each one. But I have error : the following error

"

Line no.:32
Error Code: 1064 - 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 'LONGVARCHAR,ACTHELP LONGVARCHAR,COMPXML LONGVARCHAR,LISTIDX INTEGER,PARENT_ID VA' at line 1

Query:
CREATE TABLE ACTNDEFN(ACTIONID VARCHAR(100) NOT NULL PRIMARY KEY,REVISION INTEGER NOT NULL,NAME VARCHAR(200) NOT NULL,LOGLEVEL INTEGER,AUDIT BOOLEAN,ACTTYPE VARCHAR(75),TITLE VARCHAR(254),COMPNAME VARCHAR(254),ACTDESCRIPT text,ACTHELP text,COMPXML text,LISTIDX INTEGER,PARENT_ID VARCHAR(100))
CREATE TABLE ACTNPARM(ACTPARMID VARCHAR(100) NOT NULL PRIMARY KEY,REVISION INTEGER NOT NULL,NAME VARCHAR(200) NOT NULL,PARMTYPE VARCHAR(200) NOT NULL,PARAMKEY VARCHAR(50),PMAPDEST INTEGER,PARENT_ID VARCHAR(100) NOT NULL,CONSTRAINT FKCC35800895389695 FOREIGN KEY(PARENT_ID) REFERENCES ACTNDEFN(ACTIONID))
CREATE TABLE ACTNPARMSOURCE(ACTPARMSRCID VARCHAR(100) NOT NULL PRIMARY KEY,REVISION INTEGER NOT NULL,SOURCENAME VARCHAR(200) NOT NULL,LISTIDX INTEGER,SOURCEVALUE VARCHAR(200) NOT NULL,PARENT_ID VARCHAR(100) NOT NULL,CONSTRAINT FK676AB623EA6BDDC7 FOREIGN KEY(PARENT_ID) REFERENCES ACTNPARM(ACTPARMID))
CREATE TABLE ACTNSEQS(ACTIONSEQID VARCHAR(100) NOT NULL PRIMARY KEY,REVISION INTEGER NOT NULL,NAME VARCHAR(200) NOT NULL,PATH VARCHAR(1024) NOT NULL,PARENT VARCHAR(100) NOT NULL,TITLE VARCHAR(254),AUTHOR VARCHAR(100),VERSION VARCHAR(30),IMGURL VARCHAR(254),ACTDESCRIPT text,ACTHELP text,CONSTRAINT SYS_CT_1 UNIQUE(PATH))
CREATE TABLE BDPARAMS(ITEMID VARCHAR(100) NOT NULL,PARAMVALUE VARCHAR(50),PARAMKEY VARCHAR(50) NOT NULL,CONSTRAINT SYS_PK_BDPARAMS PRIMARY KEY(ITEMID,PARAMKEY))
CREATE TABLE COMPPROPS(PARENT_ID VARCHAR(100) NOT NULL,PROPVALUE VARCHAR(254),PROPKEY VARCHAR(50) NOT NULL,CONSTRAINT SYS_PK_COMPPROPS PRIMARY KEY(PARENT_ID,PROPKEY),CONSTRAINT FK52B73BA195389695 FOREIGN KEY(PARENT_ID) REFERENCES ACTNDEFN(ACTIONID))
CREATE TABLE CONTENTITEM(CONTITEMID VARCHAR(100) NOT NULL PRIMARY KEY,REVISION INTEGER NOT NULL,NAME VARCHAR(200) NOT NULL,PARENT_ID VARCHAR(100),PATH VARCHAR(1024) NOT NULL,TITLE VARCHAR(200) NOT NULL,MIMETYPE VARCHAR(100) NOT NULL,URL VARCHAR(254),LATESTVERNUM INTEGER NOT NULL,EXTENSION VARCHAR(10) NOT NULL,WRITEMODE INTEGER NOT NULL,CONSTRAINT SYS_CT_3 UNIQUE(PATH))
CREATE TABLE CONTENTLOCATION(CONTENTID VARCHAR(100) NOT NULL PRIMARY KEY,REVISION INTEGER NOT NULL,NAME VARCHAR(200) NOT NULL,SOLNID VARCHAR(100) NOT NULL,DESCRIPTION VARCHAR(200) NOT NULL,DIRPATH VARCHAR(1024) NOT NULL,CONSTRAINT SYS_CT_5 UNIQUE(DIRPATH))
CREATE TABLE CONTITEMFILE(CONTIFILEID VARCHAR(100) NOT NULL PRIMARY KEY,REVISION INTEGER NOT NULL,OSFILENAME VARCHAR(200) NOT NULL,OSPATH VARCHAR(1024) NOT NULL,ACTNAME VARCHAR(100) NOT NULL,PARENT_ID VARCHAR(100),VERSNUM INTEGER,FILESIZE BIGINT,FILEDATETIME TIMESTAMP NOT NULL,ISINITIALIZED INTEGER,LATESTVERID INTEGER,CONSTRAINT FK7FC3F441D2A64CE FOREIGN KEY(PARENT_ID) REFERENCES CONTENTITEM(CONTITEMID))
CREATE TABLE CPLXPARAMS(ITEMID VARCHAR(100) NOT NULL,PARAMVALUE LONGVARBINARY,PARAMKEY VARCHAR(50) NOT NULL,CONSTRAINT SYS_PK_CPLXPARAMS PRIMARY KEY(ITEMID,PARAMKEY))
CREATE TABLE DTPARAMS(ITEMID VARCHAR(100) NOT NULL,PARAMVALUE TIMESTAMP,PARAMKEY VARCHAR(50) NOT NULL,CONSTRAINT SYS_PK_DTPARAMS PRIMARY KEY(ITEMID,PARAMKEY))
CREATE TABLE LNGPARAMS(ITEMID VARCHAR(100) NOT NULL,PARAMVALUE BIGINT,PARAMKEY VARCHAR(50) NOT NULL,CONSTRAINT SYS_PK_LNGPARAMS PRIMARY KEY(ITEMID,PARAMKEY))
CREATE TABLE LSPARAMS(ITEMID VARCHAR(100) NOT NULL,PARAMVALUE text,PARAMKEY VARCHAR(50) NOT NULL,CONSTRAINT SYS_PK_LSPARAMS PRIMARY KEY(ITEMID,PARAMKEY))
CREATE TABLE POSTEXECAUDLST(ITEM_ID VARCHAR(100) NOT NULL,AUDLIST VARCHAR(100) NOT NULL,POSIDX INTEGER NOT NULL,CONSTRAINT SYS_PK_POSTEXECAUDLST PRIMARY KEY(ITEM_ID,POSIDX),CONSTRAINT FKA45E678E97F49F8C FOREIGN KEY(ITEM_ID) REFERENCES ACTNDEFN(ACTIONID))
CREATE TABLE PREEXECAUDLST(ITEM_ID VARCHAR(100) NOT NULL,AUDLIST VARCHAR(100) NOT NULL,POSIDX INTEGER NOT NULL,CONSTRAINT SYS_PK_PREEXECAUDLST PRIMARY KEY(ITEM_ID,POSIDX),CONSTRAINT FK3C7A4DB197F49F8C FOREIGN KEY(ITEM_ID) REFERENCES ACTNDEFN(ACTIONID))
CREATE TABLE RTELEMENT(INSTANCEID VARCHAR(100) NOT NULL PRIMARY KEY,REVISION INTEGER NOT NULL,PARID VARCHAR(254),PARTYPE VARCHAR(50),SOLNID VARCHAR(254),READONLY BOOLEAN)
CREATE TABLE SOLNFOLDER(SOLUTIONID VARCHAR(100) NOT NULL PRIMARY KEY,REVISION INTEGER NOT NULL,PATH VARCHAR(1024) NOT NULL,NAME VARCHAR(200) NOT NULL,DESCRIPTION VARCHAR(200) NOT NULL,IMGURL VARCHAR(254),PARENT VARCHAR(100),CONSTRAINT SYS_CT_7 UNIQUE(PATH),CONSTRAINT FK65DD5ECC5E61788D FOREIGN KEY(PARENT) REFERENCES SOLNFOLDER(SOLUTIONID))
CREATE TABLE SOLRESOURCEDEF(RESOURCEID VARCHAR(100) NOT NULL PRIMARY KEY,REVISION INTEGER NOT NULL,MIMETYPE VARCHAR(200),ACCESSTYPE VARCHAR(200),RESNAME VARCHAR(200),LOCATION VARCHAR(254) NOT NULL,MAPKEY VARCHAR(50) NOT NULL,PARENT_ID VARCHAR(100) NOT NULL,CONSTRAINT FKE7FDA9A795389695 FOREIGN KEY(PARENT_ID) REFERENCES ACTNDEFN(ACTIONID))
CREATE TABLE SSPARAMS(ITEMID VARCHAR(100) NOT NULL,PARAMVALUE VARCHAR(254),PARAMKEY VARCHAR(50) NOT NULL,CONSTRAINT SYS_PK_SSPARAMS PRIMARY KEY(ITEMID,PARAMKEY),CONSTRAINT FK60E4AFE64EEDD021 FOREIGN KEY(ITEMID) REFERENCES RTELEMENT(INSTANCEID))
CREATE TABLE PARAMTYPESMAP(ITEMID VARCHAR(100) NOT NULL,PARAMVALUE VARCHAR(50),PARAMKEY VARCHAR(50) NOT NULL,CONSTRAINT SYS_PK_PARAMTYPESMAP PRIMARY KEY(ITEMID,PARAMKEY),CONSTRAINT FK60E4AFE64EEDD025 FOREIGN KEY(ITEMID) REFERENCES RTELEMENT(INSTANCEID))
CREATE TABLE VERSIONMAP(ITEMID VARCHAR(50) NOT NULL,VERSIONVALUE BIGINT,VERSIONKEY VARCHAR(50) NOT NULL,CONSTRAINT SYS_PK_VERSIONMAP PRIMARY KEY(ITEMID,VERSIONKEY))
CREATE TABLE VERSMGR(VERSIONKEY VARCHAR(50) NOT NULL PRIMARY KEY,REVISION INTEGER NOT NULL)
ALTER TABLE ACTNDEFN ADD CONSTRAINT FKCC30192579154D83 FOREIGN KEY(PARENT_ID) REFERENCES ACTNSEQS(ACTIONSEQID)
ALTER TABLE ACTNSEQS ADD CONSTRAINT FKCC36EC105E61788D FOREIGN KEY(PARENT) REFERENCES SOLNFOLDER(SOLUTIONID)
ALTER TABLE BDPARAMS ADD CONSTRAINT FK61733C484EEDD021 FOREIGN KEY(ITEMID) REFERENCES RTELEMENT(INSTANCEID)
ALTER TABLE CONTENTITEM ADD CONSTRAINT FK692B5EEC875C1BF0 FOREIGN KEY(PARENT_ID) REFERENCES CONTENTLOCATION(CONTENTID)
ALTER TABLE CPLXPARAMS ADD CONSTRAINT FKD6D6E97F4EEDD021 FOREIGN KEY(ITEMID) REFERENCES RTELEMENT(INSTANCEID)
ALTER TABLE DTPARAMS ADD CONSTRAINT FK7F994A164EEDD021 FOREIGN KEY(ITEMID) REFERENCES RTELEMENT(INSTANCEID)
ALTER TABLE LNGPARAMS ADD CONSTRAINT FKE304FCCB4EEDD021 FOREIGN KEY(ITEMID) REFERENCES RTELEMENT(INSTANCEID)
ALTER TABLE LSPARAMS ADD CONSTRAINT FK89BC75CD4EEDD021 FOREIGN KEY(ITEMID) REFERENCES RTELEMENT(INSTANCEID)
ALTER TABLE VERSIONMAP ADD CONSTRAINT FKF4F325841E8538EF FOREIGN KEY(ITEMID) REFERENCES VERSMGR(VERSIONKEY)


Line no.:32
Error Code: 1064 - 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 'CREATE TABLE ACTNPARM(ACTPARMID VARCHAR(100) NOT NULL PRIMARY KEY,REVISION INTE' at line 2
"


would you tell us why ?

Do you know an migration item between Hypersonic and mysql ?

Think you ?

mbatchelor
01-27-2006, 08:48 AM
Hi there.

The problem you're having is that mysql and hypersonic don't share the same dialect for SQL. SQL stands for Structured Query Language, not Standard Query Language (unfortunately). That means that, aside from some generally accepted standards, engine makers can roll-their-own on column types, column length limits and all sorts of things.

However, this is not the first time we've encountered the question. I'll refer you to several forum posts that should cover the bulk of your questions:

Hibernate
http://forums.pentaho.org/showthread.php?t=25870

Quartz
http://www.pentaho.org/index.php?option=com_simpleboard&Itemid=6&func=view&id=753&catid=32

If you're using mysql 5, we've created a mysql 5 dialect that you'll probably want to use since mysql increased the maximum varchar width from 255 to 1024 in version 5.

Other people have reported that SQL Squirrel has helped them migrate all their data from Hypersonic to mysql.

I hope this helps,

Marc

rokydaba
01-27-2006, 09:57 AM
think you mac,

I dowload the sql source of quartz but I can't for the hibernate's one. The Hibernate's link

Hibernate
http://forums.pentaho.org/showthread.php?t=25870

and the quartz's link

Quartz
http://www.pentaho.org/index.php?option=com_simpleboard&Itemid=6&func=view&id=753&catid=32

are the same one.

Post edited by: rokydaba, at: 01/27/2006 14:30

mbatchelor
01-27-2006, 10:56 AM
Actually, they differ in that one is ID 752 and the other is ID 753. The Hibernate discussion simply tells you how to have the platform create the tables for you in mysql.

You may run into some issues with field lengths and indexes in mysql because they have different limits than other databases.

As I recall, some of the .hbm.xml files have lines that look like this:

<column name="PATH" length="1024" not-null="true" unique-key="ICONTITMPATH" />

Note here that we're saying to the database to create a string field of 1024 characters and create a unique index on it.

Well, in MySQL5, the 1024 would need to be reduced to 767 because of issues they have with indexes that are larger than 767 bytes.

In MySQL4.x, the 1024 would need to become 254! That's one of the reasons we initially went with HSQL was because of these limits.

Now that MySQL5 is out, we're looking into creating a full MySQL deployment that takes these things into account.

Meanwhile, if you do a search in all the *.hbm.xml files under the source tree, and look for any indexed string fields where the size of the field is greater than 767 (MySQL5) or 255 (MySQL 4.x) and adjust the files accordingly. Note that in the MySQL 4.x case, you'll be severely limited to the depth of the path where your solutions are located.

Take care,

Marc