MySQL Forums
Forum List  »  Replication

alter table, and replication woes
Posted by: ian diddams
Date: August 21, 2020 08:49AM

We have a developer that is hell bent on multiple schema table updates on the master, which we have noticed breaks replication (master/slave setup)

more specifically he drops tables and recreates them, then we think (he wont confirm as yet) uses alter table to change the engine type for each table.

show slave status \G shows lines such as

Error 'Specified key was too long; max key length is 1000 bytes' on query. Default database: 'db1'. Query: 'alter table `table1` add primary key

whilst its painful we can then use alter table on the slave to update appropriately until replication SHOWS as OK in show slave status...

ie
Slave_IO_Running: Yes
Slave_SQL_Running: Yes


except it then takes some time to ACTUALLY replicate as expected. 50 minutes or so.

I can see that there is a known issue with the use of alter table
https://federico-razzoli.com/perils-of-alter-table-in-mysql-mariadb#comment-237


which at least explains why it takes some while after ive fixed all the broken replication errors for replication to actually start working again...

But more importantly what is it that may be breaking replciation in the first place? We think its the developer using alter table but we are still waiting for him to confirm.



???

cheers

Ian

Options: ReplyQuote


Subject
Views
Written By
Posted
alter table, and replication woes
183
August 21, 2020 08:49AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.