2 ms·
I agree the root cause here is the lack of a primary key to begin with. But as far as I know, DDL is always replicated as just a statement, regardless of sessio
by evanelias 1mo ago
I agree the root cause here is the lack of a primary key to begin with. But as far as I know, DDL is always replicated as just a statement, regardless of session binlog_format. So I believe the only real fix here is the general approach suggested in the manual [1], i.e. create a new empty table that has the auto_increment PK added and then populate it from the old table.
[1] https://dev.mysql.com/doc/refman/9.7/en/replication-features-auto-increment.html https://dev.mysql.com/doc/refman/9.7/en/replication-features...
- fipar 1mo agoYeah, looking back at my first reply I did get the order wrong: first the alter, then the empty noop. But once you run the alter and have a primary key (though the author clarified the table did have a PK already), I’d just use pt-osc to run the empty noop as that would basically do what the manual says. Nice seeing you Evan! :)
- evanelias 1mo agoHuh, if the table effectively already had a clustered index key (from the old PK) then I'm surprised that the auto_inc values would be assigned non-deterministically. I would have naively assumed that the values would be assigned in clustered index key order. Maybe this is one of those cases where too much logic is handled outside the storage engine? Anyway yes nice to see you here too Fernando! Good call on the noop pt-osc, I always forget about all the cool tricks that tool can do when applied in non-obvious ways.