Geek Answers Handbook

Magento Upgrade 1.9.2.3 from 1.4.0.1: updated installer installation error: cannot add foreign key constraint

Try updating your magento system from 1.4.0.1 to lastest 1.9.2.3 .

Getting an error in the Mage_Customer module in the mysql4-upgrade-1.4.0.0.7-1.4.0.0.8.php update installer file

Throw 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 solve this problem .
  • What causes the error ;
+6
source share
3 answers

Cause:

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

Actually, this means that the script could not create a foreign key constraint, since it is actually a mistake, but the reason for the failure is actually simple, mysql will not allow you to create an FK if the two fields you are trying to bind are not of the same type and lengths.

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

 $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 attribute_id field is created as the smallest, but without the length.

Since this link is to the eav_attribute table eav_attribute now you can try to compare the field type and the smallint field with the dummy table you created.

 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

Resolution:

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

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

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

Please make sure that when exporting the source database you set "Check external key constraints" to "Off."

When importing this database, the problem is no longer needed.

+1
source

This may be a problem with the database engine. I also came across the same question a few months ago, so I compared my updated magento database tables engine with the database table engine 1.9 and noticed that all the tables have MyISAM as the database engine, so I changed it like in database 1.9 and my problem is resolved. .

+1
source

More articles:


All Articles