0

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.

Improve this question
6
  • Over 100K tables -- Ouch! Sounds like a poorly designed schema. Are there tables with identical columns, but just the table name differing? Meanwhile, show us one of the failing ALTERs, plus the SHOW CREATE TABLE for the table in question. Plus the replication configuration.
    – Rick James
    Jul 22, 2021 at 18:45
  • And provide SHOW SLAVE STATUS;
    – Rick James
    Jul 22, 2021 at 18:46
  • MariaDB 10.3 does not have transactional ALTERs, so "commit" (implicit or explicit) is not relevant.
    – Rick James
    Jul 22, 2021 at 18:47
  • @RickJames multi-client, single tenant system. It's not great, but not as bad as it sounds! The replicant is caught up, 0 sec behind. I can see the replication occur from the DataGrip option, but not from the other two.
    – TheGremlyn
    Jul 22, 2021 at 18:48
  • "can see" -- meaning the table was successfully ALTERed on the Replica? My requests were to see if something subtle was going on that is not obvious. (I have not thought of how it could work with one client but not from other clients.)
    – Rick James
    Jul 22, 2021 at 18:57

1 Answer 1

Reset to default
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.