innodb cluster split

[pavel rekun]

Hi Guys,

Got myself into a strange scenario - we have 4 nodes innodb cluster
db1 R/W Primary
db2 R Secondary
db3 R Secondary
db4 R Secondary

I needed to run an optimize query on a huge 1.2tb (would save 300GB when completed) so i wanted to run it on each node without writing to binary - but i did a mistake in the query and forgot to add the LOCAL switch to skip logging.
so i ran a command on db4 by switching the read_only=0.
only after db4 completed the optimize (about 8 hours) i noticed my mistake - at this point all other servers db1/db2/db3 started to run the query. the problem is that the Primary R/W instance failed to complete the query with this error "...required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again", while all other servers completed susscefully.
not sure what will happen now if i switch the master to a different server - might break the cluster - or will it fail db1 saying it's missing GTID.
the error says to retry again - but how can i do this? not sure... maybe it's possible to skip it and then i'll run it locally only on db1 - again not sure how can it be done.

so currently the status

db1 R/W Primary OPTIMIZE FAILED
db2 R Secondary OPTIMIZE COMPLETED
db3 R Secondary OPTIMIZE COMPLETED
db4 R Secondary OPTIMIZE COMPLETED

Replication currently works and shows no errors but i see the optimize query still being shown in the process list (altogth it failed - my guess is after a restart of mysql on db1 i will enounter replication fail/error due to this.

My best guess would be that once i restart the db1 instance - master will failover to db2 - when db1 comes up again it will retry running the optimize query - can someone confirm this?

any help would be appreciated.



Edited 1 time(s). Last edit at 05/13/2024 04:59AM by pavel rekun.