Liquibase (changeLogSync, update, changeLogSyncSQL) does not create tables or inserts data

Question

Ive been following this guys page to get Liquibase setup ( http://bytefilia.com/managing-database-schema-changes-liquibase-existing-schema/ ) with at least my base tables and data. I have successfully ran

./liquibase --driver=com.mysql.jdbc.Driver \
--classpath=lib/mysql-connector-java-5.1.34-bin.jar \
--changeLogFile="../data/database/boot.json" \
--url="jdbc:mysql://localhost/tester" \
--username=root \
--password=password \
--logLevel=debug \
generateChangeLog

I do this two times one for the table structure and one for the data.

./liquibase --driver=com.mysql.jdbc.Driver \
--classpath=lib/mysql-connector-java-5.1.34-bin.jar \
--changeLogFile="../data/database/base-data.json" \
--url="jdbc:mysql://localhost/tester" \
--username=root \
--password=password \
--logLevel=debug \
--diffTypes="data"  
generateChangeLog

It generates both files boot.json & base-data.json. The files seem to be good at least comparing them to other websites and the liquibase files. The only problem that I have found is with the data file the datetime stamps generate an error about the ":" , but if I wrap them in quotes liquibase runs and syas it did it successfully. Though when I checked the database I only have 2 tables.

DATABASECHANGELOG
DATABASECHANGELOGLOCK

If I look inside DATABASECHANGELOG it has all the commits from both files after running the sync command. But it does not have any of the tables I need and of course no data. I thought maybe it wouldnt display the tables being version control and all so I tried a regular select statement.

select * from ua_auth_user;
ERROR 1146 (42S02): Table 'tester.ua_auth_user' doesn't exist

Any help is awesome. What do I need to do to get liquibase generating my schemas from a fresh database with the data that I exported. If I have done something wrong in getting the data and table structure I'll change it. I was only following the instructions to get a working example up and running before I try major development.

---Update (Following sugestions from Nathan Voxland)

I removed all tables except one table to start with. I dropped the database and create a brand new one. Then I ran the command below.

boot.json

{ "databaseChangeLog": [
  {
    "changeSet": {
      "id": "1",
      "author": "lumberjacked",
      "changes": [
        {
          "createTable": {
            "columns": [
              {
                "column": {
                  "autoIncrement": true,
                  "constraints": {
                  "constraints": {
                      "primaryKey": true
                }
              },
              "name": "id",
              "type": "INT"
            }
          },
          {
            "column": {
              "name": "subject_category_id",
              "type": "INT"
            }
          },
          {
            "column": {
              "name": "subject",
              "type": "VARCHAR(50)"
            }
          },
          {
            "column": {
              "name": "label",
              "type": "VARCHAR(50)"
            }
          }]
        ,
        "tableName": "common__subjects"
      }
    }]

    }
  }

]}

Command

➜  liquibase-bin git:(test) ✗ ./liquibase
   --driver=com.mysql.jdbc.Driver 
   --classpath=lib/mysql-connector-java-5.1.34-bin.jar 
   --changeLogFile="../data/database/boot.json" 
   --url="jdbc:mysql://localhost/tester" 
   --username=root 
   --password=password 
   --logLevel=debug update

Error Message

Unexpected error running Liquibase:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Incorrect
table definition; there can be only one auto column and it must be
defined as a key

SEVERE 1/18/15 4:05 PM: liquibase:
../data/database/boot.json::1::lumberjacked:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Incorrect
table definition; there can be only one auto column and it must be
defined as a key

It is strange that changeSets would be marked as ran even though the tables are not there. Any exceptions in creating the tables should stop the databasechangelog insert from happening. Are there any errors from when you ran `liquibase update` ? — Nathan Voxland, Jan 16 '15 at 19:47
Im going to try your suggestions today and gather the output. — lumberjacked, Jan 18 '15 at 17:27

score 1 · Answer 1 · answered Jan 19 '15 at 14:27

You need to inspect the files that are being generated. For some reason liquibase generated your json incorrectly. After looking at your file here is what you need to do:

original

"column": {
    "autoIncrement": true,
    "constraints": {
        "constraints": {
            "primaryKey": true
        }
    }
}

corrected

"column": {
    "autoIncrement": true,
    "constraints": {
        "primaryKey": true
    }
}

score 0 · Answer 2 · answered Jan 19 '15 at 03:11

When I exported the data for some reason liquibase generated the json wrong but its not invalid json just wrong and wrapped constraints twice. I don't know if its suppose to be this way but after removing the second constraint it generated all the tables. My file of tables is around 4000 lines after striping out the unique and foreign constraints at the bottom. So I'm not completely sure there aren't more errors in the json.

generated wrong

"column": {
    "autoIncrement": true,
    "constraints": {
        "constraints": {
            "primaryKey": true
        }
    }

corrected

"column": {
    "autoIncrement": true,
    "constraints": {
        "primaryKey": true
    }
}

To give credit where credit is do, my buddy @gmaniac solved the problem his suggestion is the fix . I was just slow at looking at putting two and two together before going off and working it out. — lumberjacked, Jan 19 '15 at 13:07

Liquibase (changeLogSync, update, changeLogSyncSQL) does not create tables or inserts data

2 Answers2