377432 - checksetup.pl tries to duplicate foreign keys when the bz_schema table has been truncated

Status: NEW

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

Description

[Frédéric Buclin]

While working on a patch, I had to truncate the bz_schema table to force checksetup.pl to recreate it with the real schema of my DB. But doing so makes checksetup.pl to fail the next time you run it:

Adding foreign key: attachments.submitter_id -> profiles.userid...
DBD::mysql::db do failed: Can't create table './bugs_cvs/#sql-10b2_76f.frm' (errno: 121) at Bugzilla/DB.pm line 497
        Bugzilla::DB::bz_add_fk('Bugzilla::DB::Mysql=HASH(0x97f4ad4)', 'attachments', 'submitter_id', 'HASH(0x9861398)') called at Bugzilla/DB.pm line 444
        Bugzilla::DB::bz_setup_foreign_keys('Bugzilla::DB::Mysql=HASH(0x97f4ad4)') called at Bugzilla/Install/DB.pm line 515
        Bugzilla::Install::DB::update_table_definitions() called at ./checksetup.pl line 196


From the MySQL command shell, SHOW INNODB STATUS displays the following information:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
070413 22:04:29 Error in foreign key constraint creation for table `bugs_cvs/#sql-10b2_76f`.
A foreign key constraint of name `bugs_cvs/fk_attachments_submitter_id_profiles_userid`
already exists. (Note that internally InnoDB adds 'databasename/'
in front of the user-defined constraint name).
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.


So either bz_schema doesn't contain information about foreign keys and so checksetup.pl tries to duplicate them, or checksetup.pl forgets to read this information. In both cases, it fails before it completes the installation.

Comment 1

[Max Kanat-Alexander]

Rebuilding the schema from the disk after upgrading from 2.18 is an emergency measure, not something that's officially supported. The disk-rebuild code only understands a 2.18 schema.

Thus, this is not actually a critical bug, and it also explains why you're running into that problem.

Generally, people should not expect Bugzilla to work if they destroy some part of their database.

Comment 2

[Max Kanat-Alexander]

Bugzilla 3.2 is now frozen. Only enhancements blocking 3.2 or specifically approved for 3.2 may be checked in to the 3.2 branch. If you would like to nominate your enhancement for Bugzilla 3.2, set the "blocking3.2" flag to "?". Then, either the target milestone will be changed back, or the blocking3.2 flag will be granted, if we will accept this enhancement for Bugzilla 3.2.

This particular bug has not been touched in over eight months, and thus is being retargeted to "---" instead of "Bugzilla 4.0". If you believe this is a mistake, feel free to retarget it to Bugzilla 4.0.

Comment 3

[David Marshall]

(In reply to comment #1)

> Generally, people should not expect Bugzilla to work if they destroy some part
> of their database.

Well, OK, but what if it happens anyway?

The fix would be to have a schema rebuild that knows how to figure out REFERENCES.  In practice, all the FKs need to be dropped so that the schema matches the contents of bz_schema.

I once had a script that would generate all the ALTER TABLE statements to drop all the foreign keys, but I can't find it!  Alternatively (as I'm doing for a test database that got corrupted), the database can be dumped, the dumpfile edited to remove the foreign keys, then reloaded.

Comment 4

[Max Kanat-Alexander]

> The fix would be to have a schema rebuild that knows how to figure out
> REFERENCES. 

  That's true, that would be the fix. But the "schema building" code is designed *only* to figure out the schema of a Bugzilla 2.18 database so that it can be updated to Bugzilla 2.20 and above.

> I once had a script that would generate all the ALTER TABLE statements to drop
> all the foreign keys, but I can't find it!

  That's okay, we wouldn't need that in and of itself--we have a method call that could do it, although not without a valid bz_schema table, I suppose. Also, remember that we support three (soon to be four or five) database backends, and MySQL is the only one that could *ever* build a schema from the database.

Comment 5

[Max Kanat-Alexander]

(In reply to comment #3)
> Well, OK, but what if it happens anyway?

  Oh and to answer this, in the absolute worst case, they would have to either:

  (a) restore that table's contents from a backup
  (b) have Bugzilla re-create an exact duplicate database schema in another database and copy the bz_schema contents from that database.

  That would be the only reliable solution for building an accurate schema map anyhow, even if the existing schema-building code could figure out FKs (because as I mentioned, the schema builder is only really accurate for a 2.18-or-below database).

Comment 7

[andershol]

Attachment: Fix "Building Schema object from database" to handle existing foreign keys

(Adding patch from bug 825584 as suggested, although this bug looks quite dead)

Bugzilla may try to build the schema object from the database (e.g. if you delete the row from the bz_schema-table). But when it does, it does not check for any foreign keys, so when it next tries to upgrade the schema it will try to create the foreign keys that already exist and this will fail.

This patch adds logic to get foreign key definitions from the database (Bugzilla/DB/Mysql.pm, Bugzilla/DB/Schema.pm), handles missing columns (Bugzilla/DB.pm), and fix the error handling to check for missing column before attempting dclone-ing it (Bugzilla/DB/Schema.pm).

Comment 8

[Frédéric Buclin]

(In reply to Max Kanat-Alexander from comment #4)
> and MySQL is the only one that could *ever* build a schema from
> the database.

Why MySQL only? What's wrong with Pg, Oracle or SQLite?

Comment 9

[Simon Green]

Comment on attachment 697046 [details] [diff] [review]
Fix "Building Schema object from database" to handle existing foreign keys

Setting a reviewer for this patch.

Comment 10

[Dave Miller [:justdave]]

Amazingly, this patch still applies pretty cleanly. :-)

Comment 11

[Dave Miller [:justdave]]

Attachment: GIthub pull request

Moving the patch to github as a pull request