Repair foreign keys

In certain constellations and also only with some hosters, it can happen that the foreign keys that shopware needs in the database are lost. The model generation of the attribute models needs these keys so that the attributes can be created correctly.

This can lead to error messages similar to this one:


[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: ...

Please do the following steps:

1. Make a backup of the database.

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

3. Unzip the downloaded file on your local system

4. Select your shopware database in PHPMyAdmin 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. Confirm this process with (Okay) on the page.

5. Delete the cache via FTP by selecting the folder "production_xxxx". (x = date of the used build) in the "var/cache" folder.

6. Check the file rights of the folders. These need full read and write access

7. Call up the front end in the browser again.


Done
 

Error handling

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

If you receive an error message similar to this one when executing the SQL file, there is corrupt data in the database:

MySQL reports:

#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 remove them with the following statement. Execute the statement in phpMyAdmin:


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

Now restart at step 4 and execute the SQL file again.

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

If you receive an error message similar to this one when executing the SQL file, there is corrupt data in the database:

MySql reports:

#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 remove them with the following statement. Execute 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 get the messages with another table, you have to adjust the query.

Example:


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

...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

A backup of the database should be created before the repair.
The current file is compatible with Shopware 5.6.2 and higher. Make sure that you also use the older Foreign Key repair for an older version. 

Current Foreign Keys repair

Download Foreign Keys from Shopware 5.6.2

Foreign Key repair for older Shopware versions

Download Foreign Keys till 5.2.13
Download Download Foreign Keys from 5.2.13
Download Foreign Keys from Shopware 5.5

Was this article helpful?