Open monktastic opened 1 month ago
Hi @monktastic ,
I tried this scenario locally but unable to replicate.
url
was set to a MySQL jdbc url pointing to a schema sakila
defaultSchema = "schemaDefault"
migrate
. From the result, we can see the flyway_history_table
was created in the schemaDefault
schema.
@JasonLuo-Redgate The issue isn't that the history table is created in the wrong schema; it's that the "default schema" is set incorrectly on the connection. This is a subtle but important difference. Consider the following two sets of statements:
USE DB foo;
CREATE TABLE bar.history ...;
USE DB bar;
CREATE TABLE bar.history ...;
In both cases, history
will be created in the correct schema (bar
). But when MySQL writes those CREATE
statements to its binlog, it will record that the default db in use at the time was different in the two cases, which directs the replication engine to do slightly different things depending on which filters are in use. (For example, if you set replicate-do-db=bar
, you're not telling MySQL "replicate everything in the bar
db" like you would think; you're telling it "replicate all statements where bar
was set as default db at the time" (at least for DDL statements like this).)
The defaultSchema
method on Flyway
explicitly says:
This schema will be the default for the database connection (provided the database supports this concept).
But it's not. It issues the correct USE DB
statements on the connection for my migrations, but not on the connection where it creates the history. This results in incorrect replication behavior in MySQL.
Hi @monktastic , can you workaround this by removing the foo
schema from your MySQL JDBC url since you're setting another defaultSchema
?
Yes, I ended up having to do that. Given the structure of my codebase, it turned out to be really hard, but that does seem to be the workaround.
Which version and edition of Flyway are you using?
8.0.2, Community ed.
If this is not the latest version, can you reproduce the issue with the latest one as well? (Many bugs are fixed in newer releases and upgrading will often resolve the issue)
Yes, reproduced with 10.15.2.
Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)
Java API
Which database are you using? (Type & version)
MySql 8.4
Which operating system are you using?
Linux
What did you do? (Please include the content causing the issue, any relevant configuration settings, the SQL statement(s) that failed (if any), and the command you ran)
Followed by a migrate() call.
What did you expect to see? / What did you see instead?
There should be a
USE DB bar
in the MySQL log prior to the creation ofbar.flyway_history_table
. Instead there is aUSE DB foo
. So the history table is created in the right place, but thanks to the quirks of MySQL replication (which filters DDL statements based on the current default DB), my replication breaks.Note that
USE DB bar
is correctly invoked prior to the invocation of my migrations.Offending code seems to be this: https://github.com/flyway/flyway/blob/main/flyway-core/src/main/java/org/flywaydb/core/internal/schemahistory/JdbcTableSchemaHistory.java#L108
The defaultSchema isn't being used anywhere.