Closed Bug 866188 Opened 11 years ago Closed 11 years ago

bugs.audit_log table needs a better index

Categories

(Bugzilla :: Database, defect)

Product:
Bugzilla
Component:
Database
Platform:
x86
macOS
Type:
defect
Priority:
Not set
Severity:
normal

Tracking

()

Status:
RESOLVED DUPLICATE of bug 745533

People

(Reporter: scabral, Unassigned)

References

Details

There's only one index on the audit_log table:

MariaDB [(none)]> show create table bugs.audit_log\G
*************************** 1. row ***************************
       Table: audit_log
Create Table: CREATE TABLE `audit_log` (
  `user_id` mediumint(9) DEFAULT NULL,
  `class` varchar(255) NOT NULL,
  `object_id` int(11) NOT NULL,
  `field` varchar(64) NOT NULL,
  `removed` mediumtext,
  `added` mediumtext,
  `at_time` datetime NOT NULL,
  KEY `fk_audit_log_user_id_profiles_userid` (`user_id`),
  CONSTRAINT `fk_audit_log_user_id_profiles_userid` FOREIGN KEY (`user_id`) REFERENCES `profiles` (`userid`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

When we checksum, we get errors because the user_id index isn't good enough (pt-table-checksum doesn't use it):

04-26T06:15:16 Skipping chunk 1 of bugs.audit_log because MySQL chose no index  instead of the fk_audit_log_user_id_profiles_useridindex.
04-26T06:15:16 Error checksumming table bugs.audit_log: Use of uninitialized value in string ne at /usr/bin/pt-table-checksum line 6228.

This is also a table that could benefit from a primary key.
as changes to the bugzilla schema need to happen via the bugzilla code, do you have any issues with me moving this bug to the bugzilla product and making it public?
No issues with that.
Assignee: server-ops-database → database
Group: infra
Component: Server Operations: Database → Database
Product: mozilla.org → Bugzilla
QA Contact: cshields → default-qa
Version: other → 4.2
An index has been added in Bugzilla 4.4, see bug 745533. Is that good enough?
Depends on: 867177
I think we should try it. It's hard to say if a non-primary key index will work without actually trying it.
Production bugzilla does in fact have that key:

  KEY `audit_log_class_idx` (`class`,`at_time`),

I ran a checksum on that table alone, and we don't get the error any more:

[root@bugzilla1.db.scl3 bin]# ./mysql-checksum-table.sh 
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
07-18T14:40:02      0      0    12547       4       0   0.464 bugs.audit_log
Status: NEW → RESOLVED
Closed: 11 years ago
Resolution: --- → FIXED
Resolution: FIXED → DUPLICATE
You need to log in before you can comment on or make changes to this bug.