788392 - Database problem with adding autoincrement field

Status: RESOLVED INVALID

(Bugzilla :: Database, defect)

Description

[Simon Green]

Bug 764457 and bug 776972 added an auto increment column (id) to the bugs_activity table.

brc runs with MySQL 5.0 master and multiple slave servers. As part of running this update, we ran into the MySQL issue "Adding an AUTO_INCREMENT column to a table with ALTER TABLE might not produce the same ordering of the rows on the slave and the master" which is detailed at http://dev.mysql.com/doc/refman/5.0/en/replication-features-auto-increment.html (it also affects MySQL 5.1, 5.5 and 5.6)

I'm wondering if it is practical to work around the issue, or at least provide an advisory on this in the Bugzilla 4.4 release notes. I'm not sure if Postgres or Oracle is affected by the same issue.

  -- simon

Comment 1

[Frédéric Buclin]

Why is it a problem? If a slave has a different ordering for existing rows than the master, the ids are different but queries made against the slave will still work, unless you mix data from the master and the slave, which we never do in practice.

Comment 2

[Sheeri Cabral [:sheeri]]

There's a workaround on that page, and there are other workarounds as well. 

The problem is that if the master and slave have different autoincrement numbers and someone does a consistency check, it will show up as inconsistent.

I think it's a good idea to have it, but also provide ways to ensure the master and slave have the same data. The workaround on the MySQL Manual page is fine, why not just have the update do that? Instead of just doing an ALTER TABLE ADD PRIMARY KEY, the update could:
- create a new table, bugs_activity_new, with the primary key
- INSERT INTO bugs_activity_new SELECT * from bugs_activity order by .....
- rename bugs_activity to bugs_activity_old
- rename bugs_activity_new to bugs_activity

If the bugs_activity table is large the update will need some downtime anyway, because ALTER TABLE to add a PRIMARY KEY is locking.

Also, just to be clear, our particular instance of Bugzilla is now on MySQL 5.1. Doesn't make a difference with this issue, but in case you didn't know :D

Comment 3

[Simon Green]

(In reply to Frédéric Buclin from comment #1)
> Why is it a problem? 

brc backported the feature to 4.2 to fix a security issue. As part of that fix, we did some "UPDATE bugs_activity ... WHERE id = X" statements which effectively corrupted our slaves.

So while it is correct that there is no issue of the slave having different values by itself, if extensions or other code relies on the id value matching on all databases, it will become a problem.

Comment 4

[Simon Green]

Should this be at least relnoted for 4.4, given that rc1 is due for release next week? I'd be happy if that was the outcome of this issue.

Comment 5

[Frédéric Buclin]

There is no need to relnote this for 4.4 specifically. It's not the first time we add an auto-incremented column to an existing table. We do this for several years (since at least 2006), see e.g. bug 69000, bug 339384 or bug 339385, all targetted Bugzilla 3.0. 7 major releases later, it would be confusing to suddenly relnote a problem which is at least 6 years old.

Comment 6

[:glob ✱]

just because we didn't do it in the past shouldn't be an automatic reason for not doing it now.  i don't see any anyone would be "confused".

this change to bugzilla may cause problems with large sites, and is still worth relnoting imho.

Comment 7

[Sheeri Cabral [:sheeri]]

Why is this marked resolved-invalid? Is the autoinc field not being put in after all?

Comment 8

[Simon Green]

(In reply to Sheeri Cabral [:sheeri] from comment #7)
> Why is this marked resolved-invalid?

Frédéric says that there is no need to relnote this. Therefore there is no point keeping the bug open.

> Is the autoinc field not being put in
> after all?

Yes, it is being added. This bug is dealing with the fact that some databases don't do the 'right thing' when using slave replication.