301901 - Index rename failure when renaming an index called PRIMARY

Status: RESOLVED FIXED

(Bugzilla :: Installation & Upgrading, defect)

Description

[Dave Miller [:justdave]]

Renaming index PRIMARY to cc_bug_id_idx...
DBD::mysql::db do failed: You have an error in your SQL syntax near 'PRIMARY' at
line 1 at Bugzilla/DB/Mysql.pm line 393
Bugzilla::DB::Mysql::bz_setup_database('Bugzilla::DB::Mysql=HASH(0xada0eb8)')
called at ./checksetup.pl line 1591

Comment 1

[Max Kanat-Alexander]

Is it possible for me to get a copy of that DB's Schema?

Comment 2

[Dave Miller [:justdave]]

Attachment: schema dump from "bad" database

Comment 3

[Dave Miller [:justdave]]

sucks being so behind on bugmail.  sorry for the delay on getting this to you. 
I actually still haven't seen the bugmail, I just hunted this bug down to see if
it was fixed yet because the Bugzilla in question has been out of commission
since that attempted upgrade (and because it's a small Bugzilla with only 3 or 4
people using it I was lazy and didn't make a backup first).

Comment 4

[Max Kanat-Alexander]

Attachment: v1

This should fix it. Basically, when I re-wrote the index-renaming code to be
faster, I forgot to get back in the escaping of reserved words.

In order to get the index-renaming code to run again on your DB, you'll need to
do:

DROP INDEX bugs_assigned_to_idx ON bugs;
CREATE INDEX ON bugs(assigned_to);

I'm assuming that you didn't run checksetup again *after* this error, but if
you did, you may also want to try:

TRUNCATE TABLE bz_schema;

Just to be safe.

Comment 5

[Max Kanat-Alexander]

Note that this will only happen if we're upgrading from something that was
backed-up and restored with a buggy mysqldump that renamed UNIQUE indexes to
"PRIMARY".

Comment 6

[Dave Miller [:justdave]]

(In reply to comment #5)
> Note that this will only happen if we're upgrading from something that was
> backed-up and restored with a buggy mysqldump that renamed UNIQUE indexes to
> "PRIMARY".

Yeah, that's probably the case...  this bugzilla install has been moved between
machines before.

Comment 7

[Dave Miller [:justdave]]

Comment on attachment 191149 [details] [diff] [review]
v1

this worked on the problem database :)

Comment 8

[Max Kanat-Alexander]

I think this is safe to check-in during the QA-test phase of 2.20rc2, because it
only touches checksetup code (which has extremely heavy automated testing) and
it fixes a regression in database-upgrade code which is very difficult to fix
afterward without a database backup.

Tip:

Checking in Bugzilla/DB/Schema/Mysql.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Schema/Mysql.pm,v  <--  Mysql.pm
new revision: 1.11; previous revision: 1.10
done

2.20:

Checking in Bugzilla/DB/Schema/Mysql.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Schema/Mysql.pm,v  <--  Mysql.pm
new revision: 1.10.2.1; previous revision: 1.10
done