Per the article, the replicas are configured the same. MIXED is just dynamically selecting the format automatically, and it seems to be the case that AUTOINCREMENT has fundamentally broken semantics under replication.
And instead of erroring out when replicating, it instead chooses ROW format and plays a game of complete nonsense.
The user error is in not sufficiently reading the docs, but it seems to me MySQL is going out of its way to wrap the noose
No, the binlog format is actually irrelevant here; the post author was just incorrect about that part. ALTER TABLE always gets replicated as a statement, regardless of the binlog format.
Auto_increment only has broken replication semantics in the very specific situation the author encountered: a table has data, but no primary key (and also no alternative unique index which could serve as the clustered index key) and then an attempt is made to alter the table to add an auto_increment primary key.
Basically that form of ALTER TABLE statement is telling the database to add a sequential ID to each existing row, but without providing any deterministic way that those numbers should be assigned. So each replica may choose a different numbering, causing the problem experienced by the author.
It's a foot gun, but not a common one in production at any real scale where you'd have a replica in the first place. InnoDB tables really should always have primary keys, and there are various ways to ensure that happens (sql_require_primary_key option, generated invisible primary key option, external linters, etc).
Agreed, and SQL is as important to fully understand, as when, for example, writing C. An inept usage of it, can lead to complete and total disaster.
You wouldn't want a junior to write (unreviewed) an internet connected daemon. Or to try to meet a complex RFC spec. Yet SQL? Why not?!
I think one of the greatest disservices people have done, is to abstract away SQL in frameworks. It certainly lets juniors more safely work with databases, but it really has reduced the general SQL knowledge out there. I see many senior programmers, with almost no exposure. Never touched it.
Per the article, the replicas are configured the same. MIXED is just dynamically selecting the format automatically, and it seems to be the case that AUTOINCREMENT has fundamentally broken semantics under replication.
And instead of erroring out when replicating, it instead chooses ROW format and plays a game of complete nonsense.
The user error is in not sufficiently reading the docs, but it seems to me MySQL is going out of its way to wrap the noose
No, the binlog format is actually irrelevant here; the post author was just incorrect about that part. ALTER TABLE always gets replicated as a statement, regardless of the binlog format.
Auto_increment only has broken replication semantics in the very specific situation the author encountered: a table has data, but no primary key (and also no alternative unique index which could serve as the clustered index key) and then an attempt is made to alter the table to add an auto_increment primary key.
Basically that form of ALTER TABLE statement is telling the database to add a sequential ID to each existing row, but without providing any deterministic way that those numbers should be assigned. So each replica may choose a different numbering, causing the problem experienced by the author.
It's a foot gun, but not a common one in production at any real scale where you'd have a replica in the first place. InnoDB tables really should always have primary keys, and there are various ways to ensure that happens (sql_require_primary_key option, generated invisible primary key option, external linters, etc).
Or the use of AUTO_INCREMENT, a feature which is already a footgun if implemented badly (as it clearly was)
Agreed, and SQL is as important to fully understand, as when, for example, writing C. An inept usage of it, can lead to complete and total disaster.
You wouldn't want a junior to write (unreviewed) an internet connected daemon. Or to try to meet a complex RFC spec. Yet SQL? Why not?!
I think one of the greatest disservices people have done, is to abstract away SQL in frameworks. It certainly lets juniors more safely work with databases, but it really has reduced the general SQL knowledge out there. I see many senior programmers, with almost no exposure. Never touched it.