Flyway Azure Pipeline Issue - Flyway CLI is creating a schema $(schemas) in deployment targets

Obiero

I'm using the Azure templated YAML for deployment pipelines in Azure DevOps, following the End-to-End Proof-of-Concept video. I noticed Flyway creates the flyway_schema_history table in ${(schemas)}. Shouldn't it be in dbo by default?

Additionally, my deployment fails with an "object already exists" error. It seems Flyway might be trying to apply all previous migrations, not just the new one. What could be causing this issue?

Here is output from the failed pipeline:

Caused by: Script B001_20240714150799__baseline.sql failed

-----------------------------------------------

SQL State : S0001

Error Code : 2714

Message : There is already an object named 'orders' in the database.

DanC

Hi @Obiero

I've come across this behaviour myself, and it's down to the value you're putting for schemas in your vars yaml

I made an updated guide for creating a pipeline with SQL server with YAML which addresses these new changes - https://redgatesupport.zendesk.com/knowledge/editor/01HSZSGR4HQD2D8WTF8G973BD9/en-us?brand_id=3280206

Obiero

Thank you @DanC

Quick question: Is the databaseName in this variable group supposed to be the one for Production or for Checking? And isn't the target_database_JDBC supposed to be the full JDBC connection string and not just the database name?

DanC

Hi @Obiero

Thank you for pointing out that error in one of my screenshots, I'll update that now!

databaseName should be the name of your production database!

Here's what it should look like: 

Obiero

Hi @DanC

I am still running into issues with the Drift and Change Report. Why does it try to execute the baseline script, yet it was already been applied? What could I be doing wrong yet I followed your guide step by step?

2024-07-21T09:30:49.8083842Z Dry Run: Database will NOT be modified. The following messages only represent what would happen in a normal Flyway migration run.
2024-07-21T09:30:49.8088821Z =================================================================================================================================
2024-07-21T09:30:49.8236095Z Successfully validated 3 migrations (execution time 00:00.003s)
2024-07-21T09:30:49.8484953Z Current version of schema [dbo]: 002.20240719123640
2024-07-21T09:30:49.8485715Z WARNING: outOfOrder mode is active. Migration of schema [dbo] may not be reproducible.
2024-07-21T09:30:49.8486551Z Schema [dbo] is up to date. No migration necessary.
2024-07-21T09:30:49.9867036Z ERROR: Script B001_20240719130702__baseline.sql failed
2024-07-21T09:30:49.9911726Z A Flyway report has been generated here: C:\agent\_work\11\a\flyway_prd01-158304-Drift-And-Change-Report.html
2024-07-21T09:30:49.9975601Z -----------------------------------------------
2024-07-21T09:30:49.9991081Z SQL State  : S0001
2024-07-21T09:30:49.9992585Z Error Code : 2714
2024-07-21T09:30:50.0001870Z Message    : There is already an object named 'orders' in the database.
2024-07-21T09:30:50.0007484Z Location   : C:\agent\_work\11\s\flyway-01\migrations\B001_20240719130702__baseline.sql (C:\agent\_work\11\s\flyway-01\migrations\B001_20240719130702__baseline.sql)
2024-07-21T09:30:50.0014214Z Line       : 17
2024-07-21T09:30:50.0014787Z Statement  : Run Flyway with -X option to see the actual statement causing the problem

Obiero

Hi @DanC

I think I am beginning to understand the problem. flyway clean command only deletes the flyway_schema_history table leaving other objects untouched hence the object already exists error...

DanC

Hi @Obiero

That's odd, to isolate the issue, could you try creating a local flyway project with the database you're trying to clean as the target and then do a flyway clean command and see if you get an error?