5

I'm using Liquibase for versioning an existing database, so I'm using 

liquibase \
  --logLevel=debug \
  --driver=com.mysql.jdbc.Driver \
  --classpath=lib/mysql-connector-java-5.1.30.jar \
  --url="jdbc:mysql://127.0.0.1:3306/schema" \
  --username=user \
  --password=pass \
  --diffTypes="data" \
  --changeLogFile="./data.xml" generateChangeLog

for generating the changeset xml.

This works, but the problem is when I'm trying to run those generated changesets. I get

Cannot add or update a child row: a foreign key ...' because the exported order of changeset does not take into account the foreign keys.

My question is: Is there a command option or something that can generate the changeset in the right order or should I manually reorder the changesets to get the desired result?

Update:

Normally, the foreign keys should be and are created after the foreign key. But, in our systems, the core application creates the structure of the database and multiple client applications are populating the database with their own private data in the same database. And when we're generating the data, the data changesets are generated in the alphabetical order of the tables in the database, which may the foreign key constraints. We managed to manually arrange the changesets, but I would like to know if there is a nicer workaround for this particular situation.

DaJackal
  • 2,085
  • 4
  • 32
  • 48
  • I can't imagine that there is a switch/option to change this. I'd say it's just wrong. If it's really the case that just the order is wrong you should check liquibase' [jira](https://liquibase.jira.com) to see if there is already a ticket for this and otherwise open one for it. – Jens May 06 '14 at 12:49
  • What might also help: Edit your question and show the definition of the table (the ddl) and also the output of `generateChangeLog`. That should make the bug (if it really is a bug) more obvious. – Jens May 06 '14 at 12:51

2 Answers2

10

So the problem is not with the order of types of changes (tables, then data then FKs) but that you are using just the generated data insets with an existing table and FK structure? Liquibase does not even try to figure out how rows depend on each other as it gets nearly impossible in the general case.

The easiest solution is to disable FK checks before inserting and re-enable them after. How you do that depends on your database, but you can include something like:

<changeSet id="disable-keys" author="x" runAlways="true">
    <sql>SET FOREIGN_KEY_CHECKS=0;</sql>
</changeSet>

before your <insert> tags and

<changeSet id="enable-keys" author="x" runAlways="true">
    <sql>SET FOREIGN_KEY_CHECKS=1;</sql>
</changeSet>

after them.

Nathan Voxland
  • 15,453
  • 2
  • 48
  • 64
  • It would be nice to have a tag for disabling the FK checks (cross-db), but yeah, this is the answer I was expecting. One of the reasons we turned to XML changeset is to support various rdbmss, so we don't want to add SQLs in the changesets. THank you very much for you valuable input! – DaJackal May 22 '14 at 05:20
  • I added a PR to respect the FOREIGN KEY definitions when generating a changelog for an Oracle DB. https://github.com/liquibase/liquibase/pull/906/files – Daniel Gray Aug 19 '19 at 09:14
1

There is no way to control the order. However, it should be generating it with creation then data insert than foreign key creation. Are you running the newest 3.1.1 version?

You can always reorder the changelog as well. You should consider the output of generateChangeLog as saving you from typing in a changeLog for an existing database and there is nothing wrong with fixing up what was generated if it is not quite right. The main functionality of Liquibase is tracking executed chagneSets and so is limitations to the "snapshot" functionality including perhaps ordering of generated chagneSets.

Nathan Voxland
  • 15,453
  • 2
  • 48
  • 64
  • Normally, the foreign keys should be and are created after the foreign key. But, in our systems, the core application creates the structure of the database and multiple client applications are populating the database with their own private data. And when I'm generating the data, the data changesets are generated in the alphabetical order of the tables in the database. We managed to manually arrange the changesets, but it would be cool to have some kind of easier workaround for these cases. – DaJackal May 20 '14 at 17:19
  • I've updated the question body with the exact context. – DaJackal May 20 '14 at 17:26