565320 - Missing named key in users_permissions causes site failure when file cache expires

Status: VERIFIED FIXED

(support.mozilla.org :: Knowledge Base Software, task, P1)

Description

[James Socol [:jsocol, :james]]

[From bug 565289.] In attachment 443825 [details] [diff] [review] we drop three indexes from users_permissions but only add two. The one we forgot was used in webroot/lib/userslib.php:1715-1739 in a FORCE INDEX clause.

The key we dropped was
UNIQUE KEY `permName` ( `permName` )

We either need to add the UNIQUE KEY back (though since this column is also the PRIMARY KEY it seems redundant) or we need to update line 1757 of webroot/lib/userslib.php to do

FORCE INDEX (PRIMARY)

The latter is technically correct, I'm just not sure if there would be other fallout from it and would rather not poke the bear.

Paul: can you please audit the conversion script and check that no other indices were left behind?

Comment 1

[Paul Craciunoiu [:paulc]]

Attachment: fixes differences in indexes before vs after

Checked this thoroughly, using:
> mysql -e "SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME FROM information_schema.statistics WHERE TABLE_SCHEMA = 'dbname' order by TABLE_NAME, COLUMN_NAME, INDEX_TYPE;
to generate the list of indexes, and then doing a diff of the two.

Comment 2

[James Socol [:jsocol, :james]]

Comment on attachment 445008 [details] [diff] [review]
fixes differences in indexes before vs after

r+. I tested this by wiping out the file cache, which, as predicted, caused everything to break, then I applied this SQL, and magically, everything worked again.

Comment 4

[Stephen Donner [:stephend] Not actively reading bugmail]

Can we land this to unblock testing (so Rebecca/Michael can sign up for accounts here?)

Comment 5

[Paul Craciunoiu [:paulc]]

(In reply to comment #4)
> Can we land this to unblock testing (so Rebecca/Michael can sign up for
> accounts here?)
Bug 565701 is blocking this.

Comment 6

[Paul Craciunoiu [:paulc]]

SQL was run in bug 565701. Looks like the symptoms in bug 565606 are also fixed on stage-new. Resolving this so QA can verify.

Comment 7

[Stephen Donner [:stephend] Not actively reading bugmail]

Verified; this was the bug causing random (or, not-so-random) Tiki errors, iirc.