How 3rd Party Extensions Can Ruin Your Magento Upgrade

You may say «Nah, I’m not that stupid. I disabled all extensions before I messed up with this bloody thing called Magento upgrade. I’m not even that stupid to disable them in Admin –> System –> Configuration –> Advanced –> Advanced. I disabled them deadly by turning the `active` XML node of their bootstrap files to `false`». Smart you are (and was I) but then get ready to be overwhelmed with errors like «1005 Can’t create table ‘blah-blah-blah’ (errno: 150)».

The problem is that even if you disable an extension there, its database tables might still be present and quite possible they have their (here comes the word that will kick the smile off your faces) foreign keys. Apparently Magento is not taking those keys into consideration so in some cases it breaks the database update process.

In my case the problem was caused by a quite reliable extension by one of the best developers Magento ever knew – Boris Gurvich a.k.a. Unirgy. The commercial extension `uGiftCert` installs 2 new database tables and one of them (`ugiftcert_history`) has a foreign key referencing the `admin_user.user_id` field. That means that `admin_user.user_id` gets locked from changes. And then when Magneto tries to change it to `INT( 10 )` while upgrading to version 1.6.0.0 it returns a silent error.

From that point each extension which will try to create a foreign key assuming that `admin_user.user_id` is already `INT(10)` will get an error. And you will hear it loud and clear. In my case Oauth and Api2 (maybe something else) failed to create tables with keys referencing to `admin_user.user_id`.

So the solution is the following:

1. Find the table/field that got locked. I found it but analyzing the MySQL query that Magento choked up with. I copied `CREATE TABLE ..` code to the query window of phpMyAdmin and apparently it gave me an error. There were 3 foreign keys declarations and excluding each of them one-by-one and running a query I found that a foreign key referencing `admin_user.user_id` is causing problems. Here it is also important to note what type of field it is supposed to be. In my case it was `INT( 10 )`. Don’t forget to delete the newly created table afterwards as you might like it to be created by the generic Magento installer.
2. Find the table/field that locks it (may be more than one). This may be tricky. While it’s suggested to use `SHOW ENGINE INNODB STATUS` you (and in my case it was so) may not have enough MySQL permissions to run this. So what I did is I just created a MySQL dump of the database structure and a quick search for `admin_user (user_id)` gave me the only table involved and it was `ugiftcert_history`.
3. Drop foreign key. Change types of both fields. Re-create foreign key. Easy as that:

   ALTER TABLE  `ugiftcert_history` DROP FOREIGN KEY  `ugiftcert_history_ibfk_1`;
   ALTER TABLE `admin_user` CHANGE `user_id` `user_id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
   ALTER TABLE `ugiftcert_history` CHANGE `user_id` `user_id` INT( 10 ) UNSIGNED NULL DEFAULT NULL;
   ALTER TABLE `ugiftcert_history` ADD FOREIGN KEY (`user_id`) REFERENCES `admin_user` (`user_id`) ON DELETE SET NULL ON UPDATE SET NULL;

That’s it. After this your Magento upgrade process will continue to run. Until the next error happens 🙂