Join the discussion
Write your take first — we'll ask for email only when you're ready to publish.
- Hacker News
- I don't understand why this issue only appeared after the upgrade, not immediately after the migration to add the column?by 190n
- 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.
by fipar - 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_...
by Insimwytim - Mixing binlog formats across replicas sounds like user error to me.by krautburglar
- Or the use of AUTO_INCREMENT, a feature which is already a footgun if implemented badly (as it clearly was)by rf15
- 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
by setr - Is there actually a way to 'be careful'?
The moral of this story seems that you'll step on landmines. And perhaps that 'managed' databases aren't that managed.
by mrkeen - I agree, couldn't have said it better.by el1s7
- That's kinda fun (not in a fun way).
Since the update is done semi-automatically by the means of AWS RDS, does AWS takes any responsibility for that? Unless this use case is warned against in migration notes, I would think they ought to.
by Insimwytim