How 3rd Party Extensions Can Ruin Your Magento Upgrade

Image from customerparadigm.com

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 🙂

4 Kommentare zu «How 3rd Party Extensions Can Ruin Your Magento Upgrade»

  1. I had similar problems with ugift module when doing an update from 1.3 to 1.5.

    You could also disable the foreign keys and do your changes without drop/add foreign keys

    SET foreign_key_checks = 0;

    ALTER TABLE ….

    SET foreign_key_checks = 1;

    And additionally, Admin –> System –> Configuration –> Advanced –> Advanced doesn’t deactivate the modules, it disable the frontend display. To disable a module, you have to edit each app/etc/modules/MyModule.csv and replace true by false however as you explained, it won’t remove the tables and eventual mapping with other tables.

    Thank you for your share

  2. Hey Tim,

    Great post – also love the presentation you did recently on estimating Magento upgrades. I was wondering why, in your example, the initial change of the user_id column to INT(10) would fail silently due to the ugiftcert foreign key constraint?

    If you had DEVELOPER_MODE on, shouldn’t it fail loudly and at least make it easier to identify and resolve the issue faster?

    I’m actually beginning to think about ways to automatically detect and potentially resolve these types of conflicts with my upgrade tool. I’m leaning towards at least figuring out a way to try/catch upgrade-related exceptions and flag foreign-key-related ones.

    Thanks,
    Kalen

    1. Tim Bezhashvyly

      Hi Kalen,

      Thanks for your appreciation.

      I’m not sure if first type change failure will leave a footprint in exception.log but I don’t think it will help you much as once it already happend the upgrade script will not halt to wait.

      I like the idea of building a diagnosing tool, however it may take much time and energy which can be all in waste once Magento 2 is released.

      Regards,
      Tim.

Kommentar verfassen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert