Comment by fipar | Hacker News Reader

Comment by fipar | Hacker News Reader

Parent Full thread

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.

Author here. The table did actually have a primary key, during the migration it was changed to unique, and then the new auto-incremental primary key was added.

I've updated the article to make that part clear.

I agree that having the binlog_format to ROW is the only option that make sense, which thankfully seems to be the default now.

That makes sense then.

If the table had a primary key, in case you ever face such a setup again (hopefully not!) then I think pt-online-schema-change to add the auto increment primary key while using ROW would probably be a better choice than the steps I mentioned in my first reply. It will rebuild the table anyway but at least it won’t block it while that happens.

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...

Yeah, 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! :)

Huh, 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.

Reply on news.ycombinator.com