#10 Can not transfer AUTO_INCREMENT columns from MySql

open-later
nobody
None
5
2004-02-28
2003-09-12
Anonymous
No

Table in MySql defined as:
DROP TABLE IF EXISTS `accesslog`;
CREATE TABLE `accesslog` (
`ID` bigint(20) unsigned NOT NULL auto_increment,
PRIMARY KEY (`ID`))

I try dump from MySql and restore to hsqldb.
HSQL Database Manager create script with a statement:

CREATE TABLE accesslog(ID SERIAL Default
CONSTRAINT PRIMARY PRIMARY KEY (ID) );

When i try to restore from this script i receive an error:
"Wrong datatype SERIAL...".

Discussion

  • Fred Toussi
    Fred Toussi
    2003-09-12

    Logged In: YES
    user_id=150940

    Transfer from / to MySql has not been tested by us. You need
    to manually correct the generated DDL. For the example
    above:

    CREATE TABLE accesslog(ID INTEGER IDENTITY,
    CONSTRAINT PRIMARY PRIMARY KEY (ID) );

     
  • Fred Toussi
    Fred Toussi
    2003-09-12

    • assigned_to: nobody --> hsqldb
    • status: open --> open-later
     
  • Logged In: NO

    in addition HSQL Database Manager creates wrong code in
    another cases.

    For example, code generated:

    CREATE TABLE accesslog(ID SERIAL Default ,UPD_DATE
    TIMESTAMP Default NOT NULL , OWN_ID INTEGER Default
    NOT NULL , CONSTRAINT PRIMARY PRIMARY KEY (ID) )

    Syntax errors:
    1) "SERIAL" instead of BIGINT
    2) "Default" with numeric and TIMESTAMP columns without
    default value supplied
    3) "CONSTRAINT PRIMARY PRIMARY KEY (ID)"
    instead off "PRIMARY KEY (ID)"

     
  • Logged In: NO

    In addition. I have corrected DDL - OK. But, a data can not
    be loaded correctly: if VARCHAR field contains "\n" this row
    and all subsequent rows can not be loaded.

     
  • Fred Toussi
    Fred Toussi
    2003-09-13

    Logged In: YES
    user_id=150940

    We do not currently test or develop Transfer Tool (which is
    called from Datababase Manger) with different database
    engines. If you have some time, you can develop a module
    called MySQLTransferHelper.java along the lines of the
    existing PostgresTransferHelper.java and others to perform
    the translation correctly.

    For the "\n" issue I will have a look and see if it can easily be
    fixed.

     
  • Logged In: YES
    user_id=199381

    Can we bump the priority down on this if it's not going to be
    actively worked on any time soon?

     
  • Fred Toussi
    Fred Toussi
    2004-02-28

    • labels: 325100 -->
    • milestone: 116101 -->
    • assigned_to: hsqldb --> nobody