Hey, I’ve been struggling with this issue for a while now. I still have no idea what is actually going on, but feel like I’ve narrowed it down enough that hopefully someone can help me.
We have been using liquibase for about 2 years at work, about 1 year ago we renamed some of the tables (which then renames the “referenced table names” on some foreign keys.)
In production when this change was run originally everything worked fine and we have not had any issues with it. But now when creating the database from scratch locally, it seems like these foreign key changes are made in some way where they don’t get fully committed.
The foreign key shows up as having its name correctly updated if I query the database. But if I make an ALTER to the table (E.g just adding an unrelated column, even outside of liquibase), the foreign key table name change gets reverted. So basically because we alter the table after the table name has changed, the foreign keys break.
If I turn on and off the Mysql server a bunch of the foreign keys disappear as well and the referenced table name is reverted.
If I export the database (to a .sql file) and then import it again, the foreign keys keep their updated name, and they do not revert back if I update the table. So I have gotten it to work by commenting out the changelogs that update the table, and then export and importing the table, and then running the final changelogs, but obviously don’t want to have to do that.
If anyone has any suggestions on how to further troubleshoot this that would also be much appreciated because I feel completely lost here and not sure how to figure it out any further
Hey Costinmoraru. Thanks a lot for the reply, if its annoying to test and you have any specific suggestions on how I can help troubleshoot it I’m happy to try whatever.
MysqlVersion: Ver 14.14 Distrib 5.7.23, for osx10.14 (x86_64) using EditLine wrapper
OS Version: macOS Mojave (10.14.6 (18G6020))
Liquibase version: ‘org.liquibase:liquibase-core:3.5.1’ (have also tried 4.0.0 with same issue.)
SQL Connector: ‘mysql’, name: ‘mysql-connector-java’, version: ‘6.0.6’
Hi @Earl,
sorry for the delay response. I tried to replicate your situation, but i didn’t have any issue. Can you provide me with an example of an alter you did?
Foreign key constraint names that point to the renamed table are automatically updated unless there is a conflict, in which case the statement fails with an error. A conflict occurs if the renamed constraint name already exists. In such cases, you must drop and re-create the foreign keys for them to function properly.
Need a little more help in understanding this. It does not appear to be a liquibase problem, since it is reproducible outside of liquibase.
I would need to see the command:
liquibase updateSQL
run against a database that has not had the alter table ran yet. Let’s see the sequence of sql liquibase thinks it needs to run.
Hmmm…this is totally odd, I would also post that to mysql, that cannot be expected behavior. If you have control over the db and are turning it off and on a bunch, does this mean it is like docker container? Just curious to get the idea of your environment.
Hey, sorry for the late response as well but been working trying to figure this out. The environment is just on my local machine so I can do what I want.
As you diagnosed it does not seem to be a liquibase problem. I’ve made the problem space as simple as I can so just to summarise where I’m at.
CREATE TABLE insurance.User (user_id BIGINT NOT NULL, CONSTRAINT PK_USER PRIMARY KEY (user_id));
CREATE TABLE insurance.Contract (id BIGINT AUTO_INCREMENT NOT NULL, user_id BIGINT NULL, CONSTRAINT PK_CONTRACT PRIMARY KEY (id));
ALTER TABLE insurance.Contract ADD CONSTRAINT `test-name` FOREIGN KEY (user_id) REFERENCES insurance.User (user_id) ON UPDATE NO ACTION ON DELETE NO ACTION;
This creates the tables, then running: ALTER TABLE User rename to Derptable; Correctly updates the name of the table and the foreign key.
At this point if I either restart mysql, or make any update (e.g alter table Contract add column derp boolean; ), the name of the referenced Table on the foreign key will revert to User.
If I first Export the database then re-import it, the name change on the table and foreign key remain, and does not get lost after updating.
Since its not a liquibase problem I realise its not your problem, but would be good to just get a sanity check that this is not expected behaviour, and if you have any suggestion on what might be going on here or where to ask for further help that would be great.
I imagine as a first step I’ll just try re-installing my MySQL (which is 5.7 installed by homebrew.)
Hi @Earl,
change all table names to use only lower case characters. Like this:
CREATE SCHEMA insurance DEFAULT CHARACTER SET utf8 COLLATE utf8_bin ;
CREATE TABLE insurance.user (user_id BIGINT NOT NULL, CONSTRAINT PK_USER PRIMARY KEY (user_id));
CREATE TABLE insurance.contract (id BIGINT AUTO_INCREMENT NOT NULL, user_id BIGINT NULL, CONSTRAINT PK_CONTRACT PRIMARY KEY (id));
ALTER TABLE insurance.contract ADD CONSTRAINT test-name FOREIGN KEY (user_id) REFERENCES insurance.user (user_id) ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE insurance.user rename to insurance.derptable;
And after i restarted several times myql db, the keys persist.
Wow, thanks a million that seems to have solved it for me as well. What a completely stupid thing to waste dozens of man hours on. Again thank you so much
This leaves me with one question then which now is liquibase related. We have several databases, the biggest one being initially built by 3 pretty big changelogs on its own. And then from there we have over 100 different changelogs making additions and adjustments to the database (The table name change happens in the middle.)
What (if any) is the sensible way to get out of this? Production works fine and is tied to the current changelogs, but I can’t use the current changelogs to generate new databases because of this casing issue
I guess one way would be to just start a new changelog.xml which creates the entire database from scratch (compressing the 100+ changelogs into a few), and then adding new changes from there, but our current production and staging database is attached to the current changelog so not sure how to best execute that (if that is even the best way to go about this.)
Hi @Earl,
let me understand exact your situation, and maybe i can help you. for example i you want to fix your actual changelogs i can provide you a tool to do the job. i will be happy to help
I’ll try to explain, but with the caveat that I might have some key misunderstanding.
Currently in our production and staging environment, the databases are functional and are attached to these 2 year old changelogs with 100+ entries.
I would want a changelog that I can both run against the production databases currently (to add new changes) but also that I can run locally to create a version of the production schema locally.
If I just add a new changelog renaming the databases on our current changelogs, I don’t think that will work because the changelog breaks itself by renaming and then making updates to the tables (brekaing the foreign keys.)
If I create a completely new changelog that re-creates the entire production database from scratch (now with all lower case names), I wouldn’t be able to run that changelog against the database.
I imagine I need to do some version of the second option, but would prefer to do it in a way where there doesn’t become a separation between production changelogs and development changelogs, or at least where I don’t have to worry about accidentally running the new changelog I create on production because that would repeat a bunch of operations.
Hi @Earl,
i think the best way you can do it is using a script to rename all tables and references into your changelogs, export the data from your production db, do the same thing (rename all tables and references using a script like ‘sed’), drop and recreate the database and reimport data.
