Repair foreign keys

Article Version

5.5.0 or newer

Table of contents

In certain circumstances, and only with some hosting providers, it may occur that the foreign keys required by Shopware are lost. The model generation of attribute models requires these keys, so that the attributes can be created correctly.

This can lead to error messages like the following:


[Semantical Error] line 0, col 114 near 'billing WHERE': Error: Class Shopware\Models\Attribute\CustomerBilling has no association named customerBilling in Doctrine/ORM/Query/QueryException.php on line 47
Stack trace: ...

Proceed as follows:

1. Create a backup of the database.

2. Download the file under "Available downloads" on this page.

3. Unzip the downloaded file into your local system.

4. In "PHPMyAdmin", select your Shopware database. On the right side you will find the tab "Import". With this function you can select a "File to Import". Select the downloaded file from your local computer and click to confirm.

5. Delete the cache via FTP with removing the "production_xxxx" (x = date of Shopware build) directory fom "var/cache".

6. Check the writing permissions of the file. They need to have full reading and writing access.

7. Call the frontend in your browser again.

Done!

Troubleshooting

...CONSTRAINT FAILED... FOREIGN KEY (`basketID`)...

If you receive this error message while running the SQL file, there are corrupt files in the database:

MySQL meldet:
#1452 - Cannot add or update a child row: a foreign key constraint fails (`result 2 when explaining filename '#CONSTRAINT `*******cc_ibfk_1` FOREIGN KEY (`basketID`) REFERENCES `s_order_basket` (`id`) ON DELETE CASCADE ON UPDATE )

You can delete these with the following statement. Run the statement in PHPMyAdmin.


 
DELETE FROM s_order_basket_attributes  WHERE basketID NOT IN (
    SELECT id FROM s_order_basket
);
 

Go back to step 4 and run the SQL file again.

...CONSTRAINT FAILED... FOREIGN KEY (`categoryID`)...

If you receive this error message while running the SQL file, there are corrupt files in the database:

MySql meldet:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`****`.`#sql-ac5_2d164`, CONSTRAINT `#sql-ac5_2d164_ibfk_1` FOREIGN KEY (`categoryID`) REFERENCES `s_categories` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION) 

You can delete these with the following statement. Run the statement in PHPMyAdmin.


DELETE ca -- SELECT ca.*
FROM `s_categories_attributes` ca 
LEFT JOIN s_categories c
ON c.id = ca.categoryID 
WHERE c.id IS NULL

If you receive the messages from another table, you must adjust the query.

...CONSTRAINED FAILED... FOREIGN KEY ('articleID')...

mySql reports:
 
#1452 - Cannot add or update a child row:
a foreign key constraint fails
(`xxxxxx`.`xxxxxxxxxx`,
CONSTRAINT `#xxxxxxxxxxxx`
FOREIGN KEY (`articleID`)
REFERENCES `s_articles` (`id`)
ON DELETE CASCADE ON UPDATE NO ACTION) 

Solution:


DELETE ca -- SELECT ca.*
FROM `s_articles_attributes` ca 
LEFT JOIN s_articles c
ON c.id = ca.articleID 
WHERE c.id IS NULL   

Available Downloads

Download Foreign Key Repair
Download Download Foreign Keys 5.2.13