Remove Primary Key on the liquibasechangelog table
Description
Attachments
Activity
Mark Goodrich March 12, 2025 at 11:05 PM
So build passed for 2.7.x and master after my merge (after needing to fix an integration test). I also confirmed on 3 of our staging servers that the primary key has been removed. Not sure if there’s more testing I should do? Closing this out…
Mark Goodrich March 12, 2025 at 6:22 PM
Applied to 2.7.x here:
And to master here:
And to confirm what I tested upgrading from 2.7.x to 2.8.x and the changeset does run twice (but it isn’t an issue because of the precondition):
Mike Seaton March 12, 2025 at 2:00 PM
Yes, we have been caught out by this in the past - basically, if you rename your liquibase.xml file, or move it to a different location on the classpath or filesystem which results in the “filename” changing in the liquibasechangelog table, then liquibase will no longer consider any of the changesets to have already been run, and will try to re-execute them. This is why preconditions are so important.
Perhaps we have something in place in OpenMRS to prevent this from happening, but in the event that we don’t, we need to be careful.
Mark Goodrich March 12, 2025 at 1:52 PM
Sounds good …. I will note that I think the changeset will re-run because the filename, not just the changeset ID is part of the primary key to determine if a changeset has been run (correct )… but in this case the precondition should be able to handle it smoothly.
I guess to fully test I should try upgrading a 2.7 instance to 2.8.
Ian Bacher March 12, 2025 at 12:31 PM
I think you’ve described things accurately, but like you said, the preconditions also help us prevent re-execution. In any case, this only arises when we’re backporting actual liquibase changesets, which is something we do very rarely since, under normal circumstances, data model changes are not backportable.
Details
Assignee
Mark GoodrichMark GoodrichReporter
Mark GoodrichMark GoodrichFix versions
Priority
Low
Details
Details
Assignee
Mark GoodrichReporter
Mark GoodrichFix versions
Priority
Low
Currently, the liquibasechangelog table has a composite primary key on ID, AUTHOR, and FILENAME.
However, I don’t think this is something that is set up by liquibase, but rather something that we do manually. By default, the liquibasechangelog table does not have any primary keys:
https://docs.liquibase.com/concepts/tracking-tables/databasechangelog-table.html
Assuemdly, we add a primary key because MySQL requires one.
The above documentation does say “The composite of id, author, and filename is unique across all rows of the table” but this information appears to be outdated per this issue here:
(I have not found documentation about what the primary key should be outside of the above issue)
We should update the primary key on the liquibasechangelog table to include “ORDEREXECUTED” and “EXECTYPE”, as we have run into an issue similar to the one above, namely:
Many of the Bed Management change sets are marked an “runOnChange”, which means they run a changeset again when they change
To protect against being run twice, these changesets have a precondition test
We ran into a problem when updating an existing server as follows:
One of the changesets in Bed Management on the server had previously run and so had an entry in the liquibasechangelog table with EXECTYPE “EXECUTED”
On updating the server, there was a new version of Bed Management where that changeset had been changed, so liquibase attempted to run the changeset again
It (correctly) determined that the changeset did not need to run because the precondition fails, BUT
it attempts to instead a new row into the database with the name ID, AUTHOR and FILENAME, but with EXECTYPE=MARK_RAN instead of EXECUTED and fails with a constraint violation