MYSQL to HSQLDB migration issue

1
votes

I have an issue with HSQLDB, I have a MySql database that I'm dumping to an in memory HSQLDB i get the following error when I run the script: Error: unexpected token: ( which is on a create table script and the offending line is TINYINT(3) if I remove the brackets and the number it works fine, this is a valid declaration on MYSQL and I have tried turning MYSQL compatibility on by changing my url to: jdbc:hsqldb:mem:dataSource;sql.syntax_mys=true but this still doesn't work. :(

just as additional info I'm using a Spring hibernate connection and using Liquibase to do the dumping from MySQL to HSQLDB and I'm running HSQLDB v2.3.2

2
mysqlspringhsqldbliquibase
Switch to HSQLDB 2.3.3 for its more extended MySQL compatibility mode. The TINYINT(3) is just a verbose form of TINYINT. - fredt
thanks, will upgrade now and let you know - ThaSaleni
hi @fredt upgrading my HSQLDB version doesn't solve this issue, I still get the same error, is there a way to set HSQLDB to igonore the braces after a variable if it doesn't need them? - ThaSaleni
You should remove the braces. There are other improvements in the upgrade. - fredt
removing the braces is not the option for me cause this is an automated process on build, but thanks for your help - ThaSaleni

2 Answers

2
votes

SQL Syntax especially DDL is not very well portable between different databases. You will have to learn the correct syntax for create table in HSQLDB witch is somewhat different from MySQL.

You can not just export table definition from one flavor of database and import into another. Would be great if this would be the case but SQL Standard is quite loose...

0
votes

I assume you have a DDL-Script, you can add SET DATABASE SQL SYNTAX MYS TRUE; to the top of it, see also here (Table 13.29. MySQL Style Syntax).

You may use this only for tests though; if you want to fully migrate to HSQLDB, changing the scripts themselves is sure the long term solution.