OK, been asked to create a summary of my findings to help clarify things as there’s a bit going on in my posts:
Our original implementation was running via java the Liquibase version 3.5.3 via powershell
– This saved all of the SQL files being run into our DATABASECHANGELOG table in the format ‘…/Migrations/Tables/my-script.sql’ (notice the …/ at the beginning)
My next try was using the official Liquibase docker implementation version 4.11.
–I started from a fresh database and ran the scripts, which saved the filenames in the format ‘Migrations/Tables/my-script.sql’ (the …/ has now been removed)
–When I then tried running this docker implementation against an existing database, it tried running the scripts again because the filenames were now different (…/ vs without that)
–If I removed the prepended …/ and then ran docker implementation against the existing database, it ran successfully, because it found the previous scripts and “matched” and did not try to run them again.
I then tried running older docker versions to see if there was a point that it had changed within versions, but from 4.6 to 4.10 there was a different issue, it seemed, as it was now trying to prepend './changelog/…/Migrations/Tables` in front of everything. It also ran the scripts twice for some reason. These issues both stopped in version 4.11 (the one I tried in step 2 above) so I apparently got lucky in the version I was using
So my final question would be: Is it ok, to remove the ‘…/’ that has been prepended to the filenames stored in existing DATABASECHANGELOG tables, as long as we continue to use the docker implementation? I would imagine that switching back to the jar version that we have would break it as it would be looking for the prepended ‘…/’ and would not find it and then would try running the scripts again.
--------- Original Post ----------
I have a question about running my migration scripts against an existing database.
Our DBA team created a bacpac file of our dev database. In my testing, I take that file and import it to create a database locally that matched our dev dB. This dev dB has already had our migration scripts run against it. I was hoping that I could run liquibase against that database, and it would only run scripts that had not been run already (as it does when running from scratch). Is there a way to update the database so that liquibase knows that those scripts have already been run?
And…answering my own question. I didn’t notice at first but the filename that was stored in the databasechangelog table had a slightly different path. So I guess the md5 hash generated was slightly different. Will have to look into how I can keep it the same.
the path in my changelog.xml had <includeAll path="..\Migrations\Tables" relativeToChangelogFile="true"/>
In the DATABASECHANGELOG table, the files were all saved with a prepended ‘…/Migrations/Tables…’
However, when I run the same migration now, with the same changelog.xml file, the …/ is no longer in the ‘filename’ column in the dB. Is this due to some change in the way liquibase stores the filename? (the original table was generated using a very old 3.5.3 version), but I’m using the 4.11 version.
I’ll update the column to match the expectation if that’s the way it is, but I wouldn’t want to change it if I’m doing something unusual in my file references.
I have done some more testing with this, and there does seem to have been some sort of change from 4.10 → 4.11. Using multiple versions of docker (started with 4.6, progressing to 4.11), 4.6 - 4.10 all saved to the log, starting with an empty dB, like this ./changelog/../Migrations/Tables/20200616.01-DocumentTable.sql Then it also tried to run the scripts a second time and the blew up stating that the table already exists (which it would as it had already created it on a previous run).
This appeared to be fixed in 4.11 (as it was not doing either the double run, or adding the ./changelog/.. in front of everything, but I was just hoping for some confirmation in case I’m doing something wrong.
The command is running with these params url=jdbc:sqlserver://${SQL_SERVER_URL};database=${DATABASE_NAME};username=${DB_LOGIN};password=${DB_PASSWORD};${EXTENDED_CONNECTION_PROPERTIES} --changelogFile=./changelog/changelog.xml update
And the location of everything is just
.\liquibase:/liquibase/changelog
.\Migrations:/liquibase/Migrations`
So nothing crazy there either, I don’t think. I tried adjusting the location of the changelog file to see if that made a difference, but it did not seem to.
I just verified in 4.12 and that mimics the functionality in 4.11.
Thought I would push this back to the top to see if anyone @Liquibase can answer my above questions. Been a while now since I posted last, but am still hoping to know what the issue was/is so I can either use the docker version updating the db logs as I go, then continuing without having to do that each time.
OK, been asked to create a summary of my findings to help clarify things as there’s a bit going on in my posts:
Our original implementation was running via java the Liquibase version 3.5.3 via powershell
– This saved all of the SQL files being run into our DATABASECHANGELOG table in the format ‘…/Migrations/Tables/my-script.sql’ (notice the …/ at the beginning)
My next try was using the official Liquibase docker implementation version 4.11.
–I started from a fresh database and ran the scripts, which saved the filenames in the format ‘Migrations/Tables/my-script.sql’ (the …/ has now been removed)
–When I then tried running this docker implementation against an existing database, it tried running the scripts again because the filenames were now different (…/ vs without that)
–If I removed the prepended …/ and then ran docker implementation against the existing database, it ran successfully, because it found the previous scripts and “matched” and did not try to run them again.
I then tried running older docker versions to see if there was a point that it had changed within versions, but from 4.6 to 4.10 there was a different issue, it seemed, as it was now trying to prepend './changelog/…/Migrations/Tables` in front of everything. It also ran the scripts twice for some reason. These issues both stopped in version 4.11 (the one I tried in step 2 above) so I apparently got lucky in the version I was using
So my final question would be: Is it ok, to remove the ‘…/’ that has been prepended to the filenames stored in existing DATABASECHANGELOG tables, as long as we continue to use the docker implementation? I would imagine that switching back to the jar version that we have would break it as it would be looking for the prepended ‘…/’ and would not find it and then would try running the scripts again.
As you noticed, liquibase uses the file path as a part of the changeset unique identifier. We do that so that you don’t have to worry about keeping id’s unique across files, just within each file. But, that means you have to be accessing your files similarly.
We do have logic that attempts to smooth out differences that may creep into those filenames which should not count as different. For example, we treat / and \ separators as the same. We collapse down /../ references, etc. and when we fix bugs in what gets stored as the value from time to time we make sure the old versions should work. So even if there are some differences in the strings we have some ability to understand what’s going on. But, if they are actually different, that ends up being a problem. And leading ../ paths are not something we can smooth out.
NOTE: The MD5sum does not take the filepath into account, so that doesn’t have to play into it.
A couple solutions for you: First: The overall pattern we have for handling “paths are different in different environments” is the search path setting. You can see more info at How Liquibase Finds Changelog Files: Liquibase Search Path | Liquibase Docs but the short version is that you always have your changelog file references defined as the path within the search-path, then on each machine you configure where it’s search-path locations are.
Liquibase always tracks the files in the changelog table based on how it was looked up in the search path. Liquibase has always worked this way, but older versions tried to be a bit more flexible on it which could lead to problems like you’re having. So we’ve been trying to tighten it up over the last few releases.
Second: changelog files can have a “logicalFilePath” attribute set, which hard-codes the path liquibase compares with what is in the databasechangelog table. If you use that, it doesn’t matter where you move your file or how you reference it – it’s always seen as the same file.
To get back to your specific question: it is perfectly fine to fix up your databasechangelog table if it ended up somehow out of sync with the paths liquibase is now using. The paths really shouldn’t be starting with ../ so I would suggest removing those from the stored entries. I think you were hitting a bug in older versions that would do that for some setups. Fixing them to match the correct way without that leading …/ will make things easier for you.
Hi Nathan,
Thanks for the detailed reply! Sounds like everything should work correctly for me with simply fixing the paths. I’ll give that a go. The docker container that you folk wrote made it amazingly simple so I was hoping that would be an option.
Gareth
