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.
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.
Severity: critical → normal
Priority: -- → P3
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.
Target Milestone: Bugzilla 3.2 → ---
(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.
> 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.
(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).
Created attachment 697046 [details] [diff] [review] 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).
(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?
Attachment #697046 - Flags: review?(LpSolit) → review?
Comment on attachment 697046 [details] [diff] [review] Fix "Building Schema object from database" to handle existing foreign keys Setting a reviewer for this patch.
Attachment #697046 - Flags: review? → review?(glob)
Attachment #697046 - Flags: review?(glob) → review?(justdave)
Amazingly, this patch still applies pretty cleanly. :-)
Created attachment 8945331 [details] [review] GIthub pull request Moving the patch to github as a pull request
You need to log in before you can comment on or make changes to this bug.