Closed Bug 764457 Opened 9 years ago Closed 9 years ago

Add a primary key to the bugs_activity table

Categories

(Bugzilla :: Database, enhancement)

enhancement
Not set
normal

Tracking

()

RESOLVED FIXED
Bugzilla 4.4

People

(Reporter: dkl, Assigned: dkl)

References

(Blocks 1 open bug)

Details

Attachments

(1 file)

Due to recent database issues and also being good practice to have a primary key on tables due to replication, I will be attaching a patch to add a primary key to the bugs_activity table.

dkl
I don't think we need a PK for everything. An index marked as UNIQUE will do it.
Severity: normal → enhancement
Summary: Add new autoinc primary key for the bugs_activity table → Add a primary key to the bugs_activity table
If you don't specify a primary key for InnoDB, InnoDB will make one internally, that we as end users cannot access. If the table isn't InnoDB, a UNIQUE index will do it.

The reason InnoDB makes this hidden primary key is that InnoDB stores a primary key with every secondary index (unique or regular index). So there is also motivation not to make a very large PRIMARY key (e.g., a primary key of (bug_id, bug_when, who, what) would be pretty "big").

My advice is for an auto-increment primary key. How does this affect non-MySQL databases?
Comment on attachment 632749 [details] [diff] [review]
Patch to add incrementing primary key to bugs_activity table (v1)

r=glob
Attachment #632749 - Flags: review?(glob) → review+
Flags: approval?
Flags: approval? → approval+
Target Milestone: --- → Bugzilla 4.4
Hum no, wait. Do we have any DB column within Bugzilla which isn't used at all? I don't think that's the case. Is there no code which could use it? Else there is no problem leaving InnoDB tables create their own internal PK.
Flags: approval+ → approval?
Well, we need a way to identify rows in case of data duplication, which actually did happen, and is why this ticket was created. so there *is* a use case for a primary key......if the unique index is very large, it makes more sense to use a primary key.
I found a place where this new primary key will be useful: in contrib/recode.pl. On checkin, add a comment in SPECIAL_KEYS right above

  bugs_activity     => 'bug_id,bug_when,fieldid'

that the PK only exists since 4.4 (else someone is going to accidentally remove this entry).
Flags: approval? → approval+
Keywords: relnote
Committing to: bzr+ssh://dlawrence%40mozilla.com@bzr.mozilla.org/bugzilla/trunk
modified contrib/recode.pl
modified Bugzilla/Install/DB.pm
modified Bugzilla/DB/Schema.pm
Committed revision 8290.
Status: ASSIGNED → RESOLVED
Closed: 9 years ago
Resolution: --- → FIXED
12:57 <%reed> simon: can you comment in the bug?
13:06 <%reed> simon: reopen it, too

It is my suggestion that the column type should be changed from a mediumserial to an intserial. The limit for a signed medium serial value is 8388607 (source: http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html )

At Red Hat, we have 8033780 rows in this table (and over 8.38 million on our dev server). Even if bmo isn't close to the 8.38 million limit, other installations (I'm looking at Yahoo! here) may easily be over that limit.
Status: RESOLVED → REOPENED
Resolution: FIXED → ---
on our 4.2 staging environment we have 8652904 rows in bugs_activity.

Adding new column 'id' to the 'bugs_activity' table...
DBD::mysql::db do failed: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '8388607' for key 'PRIMARY' [for Statement "ALTER TABLE bugs_activity ADD COLUMN id mediumint auto_increment PRIMARY KEY NOT NULL"] at Bugzilla/DB.pm line 606.


nice catch simon!
For comparison, how many rows do you have in the longdescs table? Its comment_id column is also of type MEDIUMSERIAL.

I'm a bit surprised that this problem hasn't been caught by dkl and glob before being checked in as they wanted it for bmo specifically. I thought this patch was already applied to bmo?
(In reply to Frédéric Buclin from comment #11)
> For comparison, how many rows do you have in the longdescs table? Its
> comment_id column is also of type MEDIUMSERIAL

mysql> SELECT COUNT(*) FROM longdescs;
+----------+
| COUNT(*) |
+----------+
|  4733147 |
+----------+

For brc, we are only half way to hitting the 8.3 million rows issue.
(In reply to Frédéric Buclin from comment #11)
> For comparison, how many rows do you have in the longdescs table? Its
> comment_id column is also of type MEDIUMSERIAL

6063162 in the sanitized dump (prod will have more of course).

> I'm a bit surprised that this problem hasn't been caught by dkl and glob
> before being checked in as they wanted it for bmo specifically.

i didn't hit the limit in my dev env, and there's a bug in our update script on our 4.2 env which was masking this :(

> I thought this patch was already applied to bmo?

it is not.
(In reply to Byron Jones ‹:glob› from comment #13)
> > For comparison, how many rows do you have in the longdescs table? Its
> > comment_id column is also of type MEDIUMSERIAL
> 
> 6063162 in the sanitized dump (prod will have more of course).

This means that bmo is very close to the 8.38M limit and should reach it in the coming years. Maybe both columns should be changed to INTSERIAL.
(In reply to Frédéric Buclin from comment #14)
> This means that bmo is very close to the 8.38M limit and should reach it in
> the coming years. Maybe both columns should be changed to INTSERIAL.

that sounds sane.
Blocks: 776972
Closing this bug again as bug 776972 has been filed about this problem.
Status: REOPENED → RESOLVED
Closed: 9 years ago9 years ago
Resolution: --- → FIXED
Blocks: 788392
Keywords: relnote
You need to log in before you can comment on or make changes to this bug.