We recently completed a migration from M1.9 to M2.1.9 however, there seems to be the odd missing picture. What I'm hoping to do is run a query to identify ALL images that are listed in Magento 1 DB with the expected file location and file name, and then compare that to a list of images that were successfully copied to our M2 installation, and identify those missing images.

Does anybody know the SQL queries I can run to identify all images in M1.9 database?



1 Answer 1


I don't think it's necessary to check catalog_product_entity_varchar first. Image "names" are stored in catalog_product_entity_media_gallery table, but the DB value already contains subfolder prefixes:

So for your product_image.png the entry is /p/r/product_image.png.

Raw SQL for filename ad location could look like:

  SUBSTRING(value, 6) as 'file',
  CONCAT('your_magento_url/media/catalog/product', value) as 'path'
FROM catalog_product_entity_media_gallery;

... or for php script ...

$path = Mage::getBaseUrl(Mage_Core_Model_Store::URL_TYPE_MEDIA) . 'catalog' . DS . 'product';

$resource = Mage::getSingleton('core/resource');
$read = $resource->getConnection('core_read');
$query = $read->select()
    ->from($resource->getTableName('catalog_product_entity_media_gallery'), array('file', 'path'))
    ->columns('SUBSTRING(value, 6) as file')
    ->columns('CONCAT("' . $path . '", value) as path');

$images = $read->fetchPairs($query);

This would give you an array like

   'product_image.png' => 'full/path/to/product_image.png',

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.