867177 - Backport bug 745533to bmo/4.2 to add index to audit_log table

Status: RESOLVED FIXED

(bugzilla.mozilla.org :: General, defect)

Description

[David Lawrence [:dkl]]

+++ This bug was initially created as a clone of Bug #866188 +++

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.

Comment 1

[David Lawrence [:dkl]]

Attachment: Patch to add index audit_log_class_at_time_idx for audit_log (v1)

Comment 2

[:glob ✱]

Comment on attachment 743708 [details] [diff] [review]
Patch to add index audit_log_class_at_time_idx for audit_log (v1)

r=glob

Comment 3

[David Lawrence [:dkl]]

Committing to: bzr+ssh://[e-mail]/bmo/4.2             
modified Bugzilla/Install/DB.pm
modified Bugzilla/DB/Schema.pm                                                         
Committed revision 8767.