Search code examples
mysqlhibernatehsqldbflyway

MySql to Hsqldb migration with engine=InnoDb in sql


I am creating a spring profile for dynamic environments in gitlab and don't want to create a separate mysql db for each instance, so what I try is to use my existing liquibase migrations with hsqldb in that speciffic profile, which seems to work besides the engine=InnoDb part in the sql.
I already added sql.syntax_mys=true to the datasource url, which supported the datatypes, not the engine part tho.

Since I want to avoid writing different sql migrations for the dynamic environments and already have a prod instance changing the migration or adding separate migrations is not really an option for me.

Is there a way to tell hsql to just ignore that part, or define it as some function which does nothing?

An example sql would be:

create table if not exists xy(
    field1             varchar(255) not null,
    field2  ....
) engine=InnoDB;


Solution

  • MySQL supports comments, including a special format for conditional execution: https://dev.mysql.com/doc/refman/8.0/en/comments.html

    If you add a version number after the ! character, the syntax within the comment is executed only if the MySQL version is greater than or equal to the specified version number. The KEY_BLOCK_SIZE keyword in the following comment is executed only by servers from MySQL 5.1.10 or higher:

    CREATE TABLE t1(a INT, KEY (a)) /*!50110 KEY_BLOCK_SIZE=1024 */;
    

    HSQLDB also supports comment syntax in SQL statements: http://www.hsqldb.org/doc/1.8/guide/ch09.html#N124ED

    All these types of comments are ignored by the database.

    Based on this, you could put the ENGINE=InnoDB into a comment so that HSQLDB will ignore it, but MySQL will run it:

    create table if not exists xy(
        field1             varchar(255) not null,
        field2  ....
    ) /*!10000 engine=InnoDB; */