Magento Upgrade 1.9.2.3 from 1.4.0.1: Upgrade installer thrown error: Cannot add foreign key constraint

Question

Try to upgrade a magento system from 1.4.0.1 to lastest 1.9.2.3.

Getting error to Mage_Customer module's at upgrade installer file mysql4-upgrade-1.4.0.0.7-1.4.0.0.8.php

Throwing error:

a:5:{i:0;s:199:"Error in file: "app/code/core/Mage/Customer/sql/
customer_setup/mysql4-upgrade-1.4.0.0.7-1.4.0.0.8.php" - SQLSTATE[HY000]:
General error: 1215 Cannot add foreign key constraint";i:1;s:970:"
#0 app/code/core/Mage/Core/Model/Resource/Setup.php(644): Mage::exception('Mage_Core', 'Error in file: ...')

How to resolve this.
What is reason for error;

Something related to the installer/upgrade file https://github.com/nexcess/magento/blob/master/app/code/core/Mage/Customer/sql/customer_setup/mysql4-upgrade-1.4.0.0.7-1.4.0.0.8.php, i saw this result when i did a googling, but i am not sure this can help you. — Naveenbos, Feb 01 '16 at 12:07
Could you maybe try to pinpoint what is exactly not working in that file ? if you do a ```die('ok');``` before the create table of customer/form_attribute, does it work ? If you do it right after the create table, does it work still ? Or do you get the error ? — β.εηοιτ.βε, Feb 03 '16 at 12:35

score 3 · Answer 1 · answered Feb 02 '16 at 16:42

The reason :

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

Actually means that the script failed to create a foreign key constraint, as the error actually stands it, but the reason for those to fail is actually kind of simple, mysql will not allow you to create a FK if the two fields you are trying to link don't have the exact same type and length.

Now from the line 31 of this update script you can indeed find a creation of a table with the addition of a foreign key constraint on the field attribute_id

$installer->run("
CREATE TABLE `{$installer->getTable('customer/form_attribute')}` (
  `form_code` char(32) NOT NULL,
  `attribute_id` smallint UNSIGNED NOT NULL,
  PRIMARY KEY(`form_code`, `attribute_id`),
  KEY `IDX_CUSTOMER_FORM_ATTRIBUTE_ATTRIBUTE` (`attribute_id`),
  CONSTRAINT `FK_CUSTOMER_FORM_ATTRIBUTE_ATTRIBUTE` FOREIGN KEY (`attribute_id`) REFERENCES `{$installer->getTable('eav_attribute')}` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Customer attributes/forms relations';
");

But you can also see that the field attribute_id is created as a smallest but with no length.

Since this link to the table eav_attribute you can now try to compare the field type and a field of type smallint from a dummy table that you would create.

CREATE TABLE `dummy_table` (`attribute_id` smallint UNSIGNED NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# you should now have the table named "dummy_table"

show fields from dummy_table where field = 'attribute_id';
# the Type should be "smallint(5) unsigned" and Null should be "No", if not, that is why the foreign key creation fail.
show fields from eav_attribute where field = 'attribute_id';
# the Type should be "smallint(5) unsigned" and Null should be "No"

drop table `dummy_table`;
# clean up of our testing table

The resolution :

So now, if the field eav_attribute.attribute_id is not smallint(5) unsigned not null then you can safely edit eav_attribute.attribute_id to make it smallint(5) unsigned not null.

If the field you created in your dummy table is not smallint(5) unsigned not null then just edit the line 34 of the file mysql4-upgrade-1.4.0.0.7-1.4.0.0.8.php so the field is created correctly.

  // `attribute_id` smallint UNSIGNED NOT NULL, -- old line
  `attribute_id` smallint(5) UNSIGNED NOT NULL, // new line, so the field have the right type

Change to `attribute_id smallint(5) UNSIGNED NOT NULL` but still getting same error — Amit Bera, Feb 02 '16 at 18:40
what does ```show fields from eav_attribute where field = 'attribute_id'``` shows ? — β.εηοιτ.βε, Feb 02 '16 at 19:11
and does the table ```customer_form_attribute``` exists already on your database ? — β.εηοιτ.βε, Feb 03 '16 at 12:33
If the table is created, then it should be something else after the create table statement that would fail, right ? Can you pinpoint what line is doing it ? Adding a simple ```die('ok')``` and moving it from php line to php line would actually tell us what instruction is actually making that fail. — β.εηοιτ.βε, Feb 03 '16 at 16:24

score 1 · Answer 2 · answered Feb 01 '16 at 10:35

1

Please make sure that when exporting the original database you set Check Foreign Key Constraints to Off.

When importing this database the problem should not be there anymore.

answered Feb 01 '16 at 10:35

Ed de Tollenaer

81
1
5

score 1 · Answer 3 · edited Feb 05 '16 at 12:05

1

This might be the issue of database engine. I also faced same kind of issue few months back so i compared my updated magento database tables engine with the 1.9 database table engine and observed that all tables have MyISAM as database engine, So I changed it as in 1.9 database and my issue got resolved.

edited Feb 05 '16 at 12:05

Amit Bera

7,581
7
31
57

answered Feb 05 '16 at 11:55

VishalPandita

700
2
10
25

hi Vishal,i willcheck – Amit Bera Feb 05 '16 at 12:05

Magento Upgrade 1.9.2.3 from 1.4.0.1: Upgrade installer thrown error: Cannot add foreign key constraint

3 Answers3

The reason :

The resolution :