ALTER TABLE statements not replicating in all tested scenarios – MariaDB

All we need is an easy explanation of the problem, so here it is.

Summary

Tech: mariadb 10.3

Issue: some attempts to ALTER TABLE to change engine is not replicating.

Scenarios tested:

  • command line PHP script running ALTER statements using PDO – did not replicate
  • mysql direct command line ALTER statement – did not replicate
  • DataGrip remote connection – did replicate

The above were all run using the same user, so it’s not a permissions thing.

The Long version

I am undertaking a change to our replicated databases to convert all tables from MyISAM to InnoDB. It is A LOT of tables (over 100k, across a couple hundred DBs), so scripting it is necessary. We’ve tested the change in non-replicated environments, and all seems to be good with it in general.

I went to run a test to verify that the replication would not present a problem. When running my conversion script against a replicated test database, the primary tables all updated, but the replicants did not. I then attempted to run the query manually in mysql CLI directly. Same result – primary succeeds, replicants do nothing.

Last test I ran was to connect my IDE’s SQL editor (DataGrip) to the test DB and try modifying the schema from there. This worked!

So clearly DataGrip is doing something my script and direct CLI statement are not. I researched into commits, but everything I read says ALTER TABLE statements have an implicit commit included. I can’t find anything to enable a more verbose output from DataGrip to see what it might be doing differently.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I’m not exactly sure of the reason for this, but after a while I noticed that my IDE would run a USE database; statement before each set of ALTERs was executed. I tried this on the command line and it worked.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply