Migration Error on CREATE TABLE when multiple column primary key (mysql)

General Discussion
1

Hi,
I am trying to migrate the structure generated with the generateChangeLog option but there is a table structure which makes liquibase generate an invalid SQL code for mysql during the migrate phase. Here is the table structure :

    CREATE TABLE  `tableName` (   `id` int(10) unsigned NOT NULL auto_increment,   `ts` datetime NOT NULL,   `weather` int(11) default NULL,   PRIMARY KEY  (`id`,`ts`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

As you can see, the primary key contains two columns with one being AUTO_INCREMENT. Here is the xml created by liquibase generateChangeLog option :

    <?xml version="1.0" encoding="UTF-8"?> http://www.liquibase.org/xml/ns/dbchangelog" ...>                                                                                                                                    

Because the primary key has multiple column, the primary key creation is done after the create table and the SQL code for the table creation is the following :

    CREATE TABLE `tableName` (   `id` INT UNSIGNED AUTO_INCREMENT  NOT NULL,   `ts` DATETIME NOT NULL,   `weather` INT )

When this query is executed, the mysql connector throw an Exception with this message : Incorrect table definition; there can be only one auto column and it must be defined as a key.

If the auto_increment primary key is the only column for the pk, the following xml is generated and the generated CREATE TABLE SQL works fine :

    <?xml version="1.0" encoding="UTF-8"?> http://www.liquibase.org/xml/ns/dbchangelog" ...>                                                                                                                    

    CREATE TABLE tableName (
      id INT UNSIGNED AUTO_INCREMENT  NOT NULL,
      ts DATETIME NOT NULL,
      weather INT,
      PRIMARY KEY  (id)
    )

Any idea on how to resolve this issue without changing the table structure ?
Thanks.
Pascal-Eric

2

An immediate workaround is just to do a raw SQL dump of the schema instead of using generateChangeLog.  That way you’re sure to know that the database structure is described completely and accurately.  I do this with Postgres since generateChangeLog doesn’t know about things like triggers and rules (at least in 1.9.5, maybe that’s changing).

3

I forgot to mention that I use RC2 for my tests (it seems it does not support triggers yet but it does support views)

As an immediate workaround, this is a great idea to use SQL dump. Of course, the xml version, in theory, should be more portable but the sql version should do the work.

Thank you.

4

You are right about SQL vs XML  The SQL is more exact, but less portable.  The diff support isn’t going to include triggers, rules, etc.  It is designed to be a sanity check/last resort tool, not a part of your standard development process.

I created an issue for the problem (http://liquibase.jira.com/browse/CORE-548) but I will not have time to really look into until the 2.1 release. 

I’m not sure what you are doing with the generated changelog or how it fits into your workflow, but you can treat the XML as a starting point and add any needed changeSets or modify the generated ones as necessary. 

Does the following XML give you the correct format:

    http://www.liquibase.org/xml/ns/dbchangelog" ...>                                                                                                                    

where both columns are defined as PK, but only one is auto increment and the compond PK and auto increment are set at table creation time?

Nathan

5

In fact, the created changelog was generated in order to have a snapshot of the database structure at the beginning of a version. The goal is to be able to create a new instance of a database structure from one of those snapshots and all the intermediates changelogs between that version and the next release. So it is imperative that the migrate option restore the database exactly like it was when the snapshot was taken.

About the XML snapshot you wrote, it does exactly was I expected : this is great !  ;D Now the next question is : when does the 2.1 version is planned to be released ?

Pascal-Eric

6

I need to get 2.0 out first :) 

The diff support will be a big focus of 2.1, but like I said, it is not something to rely on.  Since we are working with support for many different database, we will always support just a sub-set of the full functionality so while it should be an exact dump of the current database, there may be features and metadata we do not support in the extract.  For example, if you have some tables in mysql set as innodb and some myisam, we will probably never pull difference that over. 

I’ve generally taken the approach of always running the full database changelog from blank database to current on all updates.  Checking for ran changesets is very efficient and then you know that the database is being created consistently.  Depending on your changes, you may want to roll up the script from time to time, but it probably doesn’t have to be after every release if you don’t want the work of checking that the database snapshot contains everything exactly right.

Nathan