Today I got a error in the Magento backend telling me, that I have requested an invalid website_id (Invalid website id requested.), when I tried to save a product.
The problem was, that I had deleted unused websites and stores over the Magento backend. Unfortunately Magento seemed to keep the relation between website and product, although I deleted the website.
A search got me to this article on stackexchange (https://magento.stackexchange.com/questions/55867/invalid-website-id-requested). I checked the mentioned tables in it. But these tables where already equipped with foreign keys to delete relations for deleted websites.
So I had to search for other missing foreign keys, that could have been responsible for the “Invalid website id requested” error in the Magento backend.
After searching all table I could determine the catalog_category_product table as the guilty one. When I selected the distinct website_id‘s in it, I got a list like that:
As you can see in the image the table catalog_category_product has no foreign key for the website_id. And that’s causing the “Invalid website id requested.” error in the backend. I thought these days of Magento where already over! 😉 But this bug still exists in v.18.104.22.168.
The query for getting all associated website id’s was:
SELECT DISTINCT website_id FROM catalog_product_website;
In this list I had expected only two entries (admin + one still existing website). But there where several other entries for deleted website ID’s.
It could also be seen if one do a little bit reverse engineering of this table (with DDL-generation by IntelliJ for example ):
create table catalog_product_website ( product_id int(10) unsigned not null comment 'Product ID', website_id smallint not null comment 'Website ID', primary key (product_id, website_id) ) comment 'Catalog Product To Website Linkage Table' ; create index IDX_CATALOG_PRODUCT_WEBSITE_WEBSITE_ID on catalog_product_website (website_id) ;
As you can see, there is no foreign key creation and also no cascade on delete at all.
With the following query, I got an listing of all tables containing an website_id field:
SELECT DISTINCT TABLE_NAME FROM information_schema.columns WHERE column_name LIKE 'website_id';
I wanted to make sure, that no other table could cause the “Invalid website id requested” error. So I checked all of this tables as well. And as far as I could determine, its true. Its only caused by catalog_category_product.
So I deleted all unnecessary entries of catalog_category_product with the following query:
DELETE FROM catalog_product_website WHERE website_id NOT IN (0, 29);
I thin 0 is not needed at all. But it would be admin. And 29 was the still existing website.
That solved the “Invalid website id requested” error in the Magento backend for me.