Comment by 190n

7 hours ago

I don't understand why this issue only appeared after the upgrade, not immediately after the migration to add the column?

3 comments

190n

Insimwytim 5 hours ago

Versions are different during the upgrade. That's how upgrade is done.

You spin up replica, upgrade it to the target engine version, ensure it's up to date and switch over. [1]

[1] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_...

fipar 5 hours ago

I think it happened right after the alter table, but it was discovered after the upgrade. It's normal to have more eyes on the system after a DB upgrade, and also common to blame the DB for post-upgrade problems.

Turns out this time the DB was to blame, but not because of the upgrade.

If the alter table in the blog post is not a simplified version of what was executed (barring changing column names, of course), that means the table had no primary key before the migration, which is a problem on its own.

To be honest, I don't even know if there's a safe way out of that situation in a replication setup, but one plan I would have tried to test in that situation is: - switch binlog_format to ROW (and never look back ...) - run a noop alter table to rebuild the table and hope that with ROW format, the rows get inserted in the same order (hope really hard please, with feeling) - run the alter table

Fortunately, recent versions of MySQL have ROW as the default binlog_format.

evanelias 3 hours 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...