Description of problem: On bz-web1-test, admin user can't delete an existing product How reproducible: Steps to Reproduce: 1.Create a test product on "Other" -> "del_product_test" 2.Click "delete" link 3.In "Confirmation" section, click "Yes, delete" button Actual results: Software error: DBD::mysql::db do failed: Cannot delete or update a parent row: a foreign key constraint fails (`bugs34/versions`, CONSTRAINT `fk_versions_product_id_products_id` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON UPDATE CASCADE) [for Statement "DELETE FROM products WHERE id = ?"] at /var/www/html/bugzilla/Bugzilla/Product.pm line 399 Expected results: Product "del_product_test" should be deleted successfully Additional info:
The error is caused by the incorrect FK constraints in versions table, components table and milestones tables. For example, run following sql command to check FKs in versions table mysql> show create table versions\G *************************** 1. row *************************** Table: versions Create Table: CREATE TABLE `versions` ( `value` varchar(64) NOT NULL, `product_id` smallint(6) NOT NULL, `id` mediumint(9) NOT NULL auto_increment, PRIMARY KEY (`id`), UNIQUE KEY `versions_value_idx` (`value`,`product_id`), KEY `versions_product_id_idx` (`product_id`), CONSTRAINT `fk_versions_product_id_products_id` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON UPDATE CASCADE, CONSTRAINT `versions_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=887 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) There are two problems. First, the products.id->milestones.product_id constraint were defined twice (fk_versions_product_id_products_id and versions_ibfk_1) Second, the constraint is "ON UPDATE CASCADE" which should prevent user from deleting any existing product. Interestingly, I can delete product from bz-web2, which has exactly same definition of versions table and the fk constraints didn't work as it should do). Nevertheless, it should be "ON UPDATE CASCADE ON DELETE CASCADE". The Bugzilla/DB/Schema.pm has the right definitions. components, milestones table (and some other tables) have the same problem. Please see attached file for details. Possible solution: 1. Before running checksetup.pl, we can drop all problematic FKs from actual tables as well as bz_schema table 2. When running checksetup.pl, it will fetch correct FK information from Bugzilla/DB/Schema.pm and install them into actual tables. (we can set Mysql's system variable - set_foreign_key_checks to 0 to speed this process)
Created attachment 368338 [details] Incorrect FKs in bz 3.2 and beta bz3.4
The incorrect FKs are in production db and bugs34 on bz-db1-test
Now I have figured out why we can delete product on our production db and can not delete product on beta bz3.4 db. the Bugzilla/Product.pm of bz3.2 explicitly delete all related records in child tables (components, milestones etc) before deleting the product, while the new Product.pm seems to leave these tasks to Mysql's foreign key mechanism, which requires the fks constraints to be set properly. Unfortunately, our existing db has some problems on these fk constraints, like I mentioned in previous comments, therefore deleting an existing product doesn't work for our bz3.4 db on bz-web1.
Tony, could a script be written to remove the improper foreign keys and re-create them on the running database using the bz_* database functions? Could you look into that and test it on bugs34? Thanks Dave
Tony, is this not an issue anymore once the fk_fix.pl script is ran on the live database when we do the migration? If yes then we will close this bug as nextrelease. Dave
(In reply to comment #6) > Tony, is this not an issue anymore once the fk_fix.pl script is ran on the live > database when we do the migration? If yes then we will close this bug as > nextrelease. > > Dave Yes, running fk_fix.pl will fix it. Closing this bug.