Database problem with adding autoincrement field




6 years ago
5 years ago


(Reporter: Simon Green, Unassigned)





6 years ago
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 (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


6 years ago
See Also: → bug 764457, bug 776972

Comment 1

6 years ago
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.
Severity: major → minor
Depends on: 764457
See Also: bug 764457, bug 776972
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

6 years ago
(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

5 years ago
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.
Flags: needinfo?(LpSolit)

Comment 5

5 years ago
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.
Flags: needinfo?(LpSolit)


5 years ago
Last Resolved: 5 years ago
Resolution: --- → INVALID
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.
Why is this marked resolved-invalid? Is the autoinc field not being put in after all?

Comment 8

5 years ago
(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.
You need to log in before you can comment on or make changes to this bug.