In a Magento project, I bulk deleted all images to start again. I thought I accomplished this by deleting all images from /media/catalog/product, and all records from tables catalog_product_entity_media_gallery and catalog_product_entity_media_gallery_value.

However there was one step I missed, and that led to phantom images that appeared on the frontend (in the category view and product view), while in the backend, no image was showing in the image tab for that product.

I finally found that the culprit was the table catalog_product_entity_varchar. You can use this SQL on your database to view all image links with SKU.

SELECT `sku` , cpev.value
FROM `catalog_product_entity` AS cpe
INNER JOIN `eav_attribute` AS attribute ON attribute.attribute_code = 'image'
AND attribute.frontend_input = 'media_image'
LEFT JOIN `catalog_product_entity_varchar` AS cpev ON cpe.entity_id = cpev.entity_id
AND cpev.attribute_id = attribute.attribute_id
WHERE cpe.type_id = 'configurable'
AND cpev.value IS NOT NULL AND cpev.value != 'no_selection'

A similar query produces a list of products with no images.

SELECT `sku`
FROM `catalog_product_entity` AS cpe
INNER JOIN `eav_attribute` AS attribute ON attribute.attribute_code = 'image'
AND attribute.frontend_input = 'media_image'
LEFT JOIN `catalog_product_entity_varchar` AS cpev ON cpe.entity_id = cpev.entity_id
AND cpev.attribute_id = attribute.attribute_id
WHERE cpe.type_id = 'configurable'
AND cpev.value IS NULL OR cpev.value = 'no_selection' OR cpev.value = ''

NB: I have put in the product type as configurable because all my products viewing on the front end are configurable and I wanted to leave out simple products.

The take home lesson is that if you intend to bulk delete images, you also need to null or ‘no_selection’ all references to images in catalog_product_entity_varchar.value