Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

On MySQL engine parameter is hard-coded to InnoDB, this brakes replication to a diffrent storage engine #3641

Open
kantselovich opened this issue Mar 24, 2023 · 4 comments · May be fixed by #3642
Open

On MySQL engine parameter is hard-coded to InnoDB, this brakes replication to a diffrent storage engine #3641

kantselovich opened this issue Mar 24, 2023 · 4 comments · May be fixed by #3642
Labels
s: needs investigation

Comments

@kantselovich
Copy link

kantselovich commented Mar 24, 2023 •
edited

Which version and edition of Flyway are you using?

8.0.0

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, I can

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Command line

Which database are you using? (Type & version)

MySQL 5.6, 5.7

Which operating system are you using?

Ubuntu

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)

Applying flyway migration to a new schema, to a database server that has a replica running a different storage engine.
Ref https://dev.mysql.com/doc/refman/5.7/en/replication-solutions-diffengines.html

What did you expect to see?

Flyway migration applied successfully.
Database changes from migration applied successfully to the replica server, running a different storage engine.

What did you see instead?

Flyway migration applied successfully.
Replication stops with the error "cannot create table 'migrations'"

Additional info:

On MySQL engine parameter is hard-coded to InnoDB, this brakes replication to a diffrent engine, when flyway runs against a schema for the first time.

flyway/flyway-mysql/src/main/java/org/flywaydb/database/mysql/MySQLDatabase.java

Lines 160 to 174 in 75c3b90

return "CREATE TABLE " + table + " (\n" +
" `installed_rank` INT NOT NULL,\n" +
" `version` VARCHAR(50),\n" +
" `description` VARCHAR(200) NOT NULL,\n" +
" `type` VARCHAR(20) NOT NULL,\n" +
" `script` VARCHAR(1000) NOT NULL,\n" +
" `checksum` INT,\n" +
" `installed_by` VARCHAR(100) NOT NULL,\n" +
" `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n" +
" `execution_time` INT NOT NULL,\n" +
" `success` BOOL NOT NULL,\n" +
" CONSTRAINT " + getConstraintName(table.getName()) + " PRIMARY KEY (`installed_rank`)\n" +
")" + tablespace + " ENGINE=InnoDB" +
baselineMarker +
";\n" +

@kantselovich kantselovich linked a pull request Mar 24, 2023 that will close this issue
Open
@kantselovich
Copy link
Author

Any updates here? Let me know if you need any details ?

@PabloThiele
Copy link

PabloThiele commented Jul 10, 2023 •
edited

@kantselovich This unfortunately is about the drop of support to MySQL 5.7 since Flyway 8 (Open Source version). That change happened here: 4622cee

So to have the support now do you need an enterprise version.

More details about it: https://stackoverflow.com/questions/70438180/what-is-the-last-version-of-flyway-community-edition-that-supported-mysql-5-7

@kantselovich
Copy link
Author

kantselovich commented Jul 10, 2023 •
edited

@PabloThiele thank you for the reminder about support timeline for MySQL 5.7

However, the problem raised in this issue is not specific to MySQL 5.7 and it is not really addressed by your comment.
It has to do that in flyway storage engine definition is hard-coded to be InnoDB.

MySQL supports various storage engines , and there are cases when setting replication with different storage engines on master & replicas are beneficial. That 'cross storage engine' replication will be broken by flyway creating migrations history table with ENGINE=InnoDB -- create table statement with ENGINE=InnoDB will be pushed to binlog on the primary (master) database and then this statement can cause failure in SQL Slave applying binlog changes on the replica if InnoDB storage engine is not available.

I think this problem can simply be solved removing specific storage engine from table definition and using the default. I do not see any benefits of hard-coding storage engine to be InnoDB. Or possibly storage engine specification can be made as configurable option, but that will add complications and more code to maintain, so I would consider this option if there are benefits to it.

I would like to get some feedback on #3642

Thanks you,
-Konstantin

@kantselovich
Copy link
Author

UP.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
s: needs investigation
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Issue#3614 Use default database storage engine for flyway migration table kantselovich/flyway
3 participants
@kantselovich @PabloThiele @Barry-RG