297187 - checksetup.pl: primary key for groups exists. ( bit ) conflict with id as primary key

Status: RESOLVED WONTFIX

(Bugzilla :: Installation & Upgrading, defect, P2)

Description

[Christian Volkmann]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; de-AT; rv:1.7.8) Gecko/20050511
Build Identifier: Mozilla/5.0 (X11; U; Linux i686; de-AT; rv:1.7.8) Gecko/20050511

Hi,
I did a migration of data from 2.14 to 2.19.3

checksetup.pl failed for the creation of the primary key "id".
The if ( ...) {  drop ... } did not work for me. I do not know why.

    if ($dbh->primary_key(undef, undef, 'groups')) {
        $dbh->do("ALTER TABLE groups DROP PRIMARY KEY");
    }

    $dbh->bz_add_column('groups', 'id',
        {TYPE => 'MEDIUMSERIAL', NOTNULL => 1, PRIMARYKEY => 1});

Best regards,

Christian

Reproducible: Always

Steps to Reproduce:
1.get database from 2.14
2../checksetup.pl


Actual Results:  
checksetup.pl stopped

Expected Results:  
continue...

:) Thangs for bugzilla.

Comment 1

[Colin Ogilvie [:cso]]

*** Bug 297188 has been marked as a duplicate of this bug. ***

Comment 2

[Max Kanat-Alexander]

Could you paste here the exact error message that checksetup outputs?

Have you customized your database at all?"

Could you do "mysqldump --opt --no-data bugs" on the bugs database (you might
have to specify -u <username> also, and replace the word "bugs" in that command
with the name of your Bugzilla database if it's not named "bugs.") and attach
the results here? (That will show me the tables of your database.)

Comment 3

[Christian Volkmann]

Attachment: mysqldump of the customized database. Dump of the production

Before the test migration I did:

ALTER TABLE versions DROP patch;
ALTER TABLE bugs DROP patch;

I will attach the exact error message soon.

Comment 4

[Christian Volkmann]

Attachment: Output of ./checksetup.pl

Final error-message:

Adding new column id to table groups ...
DBD::mysql::db do failed: Multiple primary key defined at Bugzilla/DB.pm line
345
	Bugzilla::DB::bz_add_column('Bugzilla::DB::Mysql=HASH(0x989fd00)',
'groups', 'id', 'HASH(0x9a411fc)') called at ./checksetup.pl line 2910

The complete output of ./checksetup.pl is attached.

Comment 5

[Max Kanat-Alexander]

OK, yep, this is a bug. Well, technically, the problem is that at some point you
restored your database using a slighly buggy mysqldump that renames any UNIQUE
index to PRIMARY.

Could you also get me a similar mysqldump of what the tables look like after the
error is printed? I need that to be able to fix the bug in a more final way.

After that, as a workaround, you can probably do "ALTER TABLE groups DROP
PRIMARY KEY" -before- you run your upgrade, and hopefully that should fix it.
However, I'd need that other mysqldump to be certain.

Comment 6

[Max Kanat-Alexander]

Note to myself: The tricky part here is that at some point, groups *did* have a
valid PRIMARY KEY, so we can't just rename the PRIMARY index always. We have to
check if the "bit" field still exists, and only rename it in that case.

Comment 7

[Christian Volkmann]

Attachment: mysqldump of the database after the error occured.

The definition of groups looks like this after the error.
See the attached file for the complete output.

--
-- Table structure for table `groups`
--

DROP TABLE IF EXISTS `groups`;
CREATE TABLE `groups` (
  `bit` bigint(20) NOT NULL default '0',
  `name` varchar(255) NOT NULL default '',
  `description` text NOT NULL,
  `isbuggroup` tinyint(4) NOT NULL default '0',
  `userregexp` tinytext NOT NULL,
  `isactive` tinyint(4) NOT NULL default '1',
  `last_changed` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`bit`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


=================
ALTER TABLE groups DROP PRIMARY KEY; _before_ the migation works fine.

It might be a complete solution to drop the primary key and recreate it,
always.

=================
I will be happy to approve the final fix if you like.

Haveaniceday,

Christian

Comment 8

[Max Kanat-Alexander]

See, here's what's weird. We *do* drop the Primary Key if DBI says there's a
primary key on that table!

It's this code:

    if ($dbh->primary_key(undef, undef, 'groups')) {
        $dbh->do("ALTER TABLE groups DROP PRIMARY KEY");
    }

I'm assuming that it's just not working right, at the moment. I'll have to try
to reproduce your schema and see what happens.

Comment 9

[Christian Volkmann]

Just "non perl programmers" idea:

./Bugzilla/DB/Mysql.pm
./Bugzilla/DB.pm

=> does not provide the function $dbh->primary_key(undef, undef, 'groups')
related perl function is not provided / mapped to be available.

Best regards,

Christian

Comment 10

[Max Kanat-Alexander]

(In reply to comment #9)
> => does not provide the function $dbh->primary_key(undef, undef, 'groups')
> related perl function is not provided / mapped to be available.

  Yeah, that's because Bugzilla::DB inherits from perl's DBI, and primary_key is
a standard DBI function. Any time we call a function on $dbh that doesn't start
with bz_ or sql_, it's a standard DBI function.

Comment 11

[Nick Barnes]

Just hit this bug when running unit tests on the P4DTI.  One of the tests sets
up a Bugzilla from this database:

http://www.ravenbrook.com/project/p4dti/version/2.2/test/job000352-mysqldump

and fails somewhere later on.  Investigating by hand, I get this result partway
through checksetup.pl:

Adding new index 'components_name_idx' to the components table ...
Adding new column last_changed to table groups ...
Removing index 'groups_bit_idx' from the groups table...
Adding new column id to table groups ...
DBD::mysql::db do failed: Multiple primary key defined at Bugzilla/DB.pm line 362
        Bugzilla::DB::bz_add_column('Bugzilla::DB::Mysql=HASH(0x8f6380c)',
'groups', 'id', 'HASH(0x95cc628)') called at checksetup.pl line 2923

Comment 12

[Nick Barnes]

By the way, I get this when testing against Bugzilla 2.20, but not for 2.18.*.

Comment 13

[Nick Barnes]

Workaround: upgrade to 2.18.* first.  This fixes it for me (because the relevant
part of upgrading the database schema is done by the older code in the 2.18
checksetup.pl).

Comment 14

[Christian Volkmann]

I suggest as quick work around:

-   if ($dbh->primary_key(undef, undef, 'groups')) {
        $dbh->do("ALTER TABLE groups DROP PRIMARY KEY");
-   }

+ Ignore error message of "$dbh->do("ALTER TABLE groups DROP PRIMARY KEY");"
 
That will work for sure and avoid user intervention before the update.

Best regards,

Christian

Comment 15

[Max Kanat-Alexander]

OK! I've reproduced the bug locally. :-) The problem is that groups can have an index named "PRIMARY" if you upgraded from an older MySQL and an older Bugzilla. The index is on the "name" field.

We miss this in the index renaming, so the real fix is to fix the index renaming.

Comment 16

[Max Kanat-Alexander]

Attachment: v1 (2.21 and 2.20)

This fixes the problem on the machine that I encountered it on.

Comment 17

[Frédéric Buclin]

Comment on attachment 205324 [details] [diff] [review]
v1 (2.21 and 2.20)

r=LpSolit assuming you have tested it.

Comment 18

[Max Kanat-Alexander]

I have indeed tested it.

Comment 19

[Max Kanat-Alexander]

tip:

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

2.20:

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

Comment 20

[Max Kanat-Alexander]

Arrgh. Unfortunately, this causes test-checksetup to fail its schema tests. Basically, the problem is this:

Versions of the "groups" table between 2.17.1 and 2.19.1 have a *valid* primary key that should *not* be renamed, but will be renamed by this patch.

Patch backed out.

Comment 21

[Frédéric Buclin]

what's the status of this bug? wontfix?

Comment 22

[Frédéric Buclin]

Not a security bug.

Comment 23

[Frédéric Buclin]

The Bugzilla 3.0 branch is now locked to security bugs and dataloss fixes only. This bug doesn't fit into one of these two categories and is retargetted to 3.2 as part of a mass-change. To catch bugmails related to this mass-change, use lts081207 in your email client filter.

Comment 24

[Frédéric Buclin]

Bugzilla 3.2 is restricted to security bugs only. Moreover, this bug is either assigned to nobody or got no traction for several months now. Rather than retargetting it at each new release, I'm clearing the target milestone and the bug will be retargetted to some sensible release when someone starts fixing this bug for real (Bugzilla 3.8 more likely).

Comment 25

[Gervase Markham [:gerv]]

No-one will ever be fixing bugs related to upgrading between such old versions of Bugzilla.

Gerv