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

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.