replication – ALTER TABLE statements not replicating in all tested scenarios – MariaDB

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.