There’s a quirk with mysql 5.0 where a “drop database” command will result in a foreign-key constraint error (see http://bugs.mysql.com/bug.php?id=18942 ). Unfortunately, this same bug I believe prevents liquibase from dropping all when instructed to do so.
When I use the mysql command-line client, I’m able to workaround this problem by executing “SET FOREIGN_KEY_CHECKS = 0” prior to my drop & create statements. Is there some method to have liquibase do the same?
Thank you!
~ kc
Actually, since not all databases support a nice “drop database” syntax, the dropAll command gets all the foreign keys and tables and drops them individually. We could override the method for mysql to do a drop database but have not yet.
What error are you getting? Are you able to reproduce it and/or send us a test case?
Nathan
The error I receive is:
Error executing SQL ALTER TABLE name] DROP FOREIGN KEY [foreign key name]
Error on rename of ‘./[schema name]/
Subsequent executions of liquibase generate different errors, but all have to do with dropping a foreign key constraint. I believe with each successive execution, mysql is able to drop the errant key that caused a problem with the previous pass.
While liquibase doesn’t use “drop database”, I think the same underlying mysql bug plays a part in this (though this is based solely on my hunch).
In any case, this is solely because of something with mysql and not within liquibase.
I don’t want to switch mysql to use “drop database” because a user may not have that permission, plus there isn’t a way for liquibase to call “create database” because it needs the databasechangelog table to be created in the database before any changes can be ran.
I haven’t been able to duplicate the problem yet. DropAll on mysql has been working fine for me so far. I’ll keep trying to replicate it, otherwise if you can provide a test case or try to debug it locally that would be great too.
Nathan
I don't want to switch mysql to use "drop database" because a user may not have that permission, plus there isn't a way for liquibase to call "create database" because it needs the databasechangelog table to be created in the database before any changes can be ran.
Ah yes, that latter reason certainly being important! I’ve been doing the work necessary for liquibase’s initial introduction to our development cycle – including reverse engineering our entire schema, identifying system-data, etc. During this effort, dropping and creating the schema was convenient, albeit far removed from how we’d use it moving forward.
I feel bad you’ve spent any cycles trying to duplicate the issue. Once time avails, let me see if I can’t get the minimal number of tables to replicate the error. If successful, I’ll provide my result.
Thank you for your help! I look forward to using liquibase within our environment.
~ hf
The dropAll command is definitely designed more for a development environment, including test setup/teardown. It is not a normal part of the liquibase process, like you said.
If it helps, there is a generateChangeLog command that you can use to take a snapshot of a database schema (including data if need be) and create a starting changelog file. It will usually need some modification but it may help you in your reverse engineering.
Nathan
I’m seeing something very similar; however, I’m using Firebird 2.1/Jaybird 2.1.1 with Liquibase 1.9.5. The first time I run dropAll, I get the “Error executing SQL ALTER TABLE name] DROP FOREIGN KEY [foreign key name]” message for FK_1; the next time I get it for FK_2; then FK_3; and so on.
When I use the older 1.9.4, dropAll seems to work fine. Any idea what could have changed in this area between the two releases?
Also, 1.9.5 did not seem to like when something else was already connected to the DB (i.e. my FlameRobin SQL client); however, 1.9.4 does not seem to mind.
Thanks,
Jamie
They do sound similar indeed. However, with mysql I experience the same problem with liquibase 1.9.4 (well, the maven plugin 1.9.4.0 which I assume uses the corresponding liquibase version).
Just thought this could be another clue. For the time being, I’m manually dropping all of my schema’s tables, except the DATABASECHANGELOG tables. Is there something else I should do, perhaps changing data in DATABASECHANGELOG, to replicate the dropFirst liquibase routine?
~hf
If you drop all your tables, you will probably want to do a “delete from databasechangelog” so all changeSets are re-run the next time. You will probably also want to keep DATABASECHANGELOGLOCK
Nathan
