images from catalog_product_entity_media_gallery comparing with presence on server

[emile_rdam]

So, In our catalog_product_entity_media_gallery table in the Magento 2.3.1. database are around 25.000 lines. Like:

18963,90,/_/_/__1_3.jpg,image,0
18965,90,/_/_/__1_4.jpg,image,0
10815,90,/_/_/__1.jpg,image,0
31586,90,/_/_/__12_1_1.jpg,image,0

Now some images mentioned in the table are not actually any longer on the server:

public_html/pub/media/catalog/product/_/_/__1_3.jpg,image,0

So when you run command  php bin/magento catalog:image:resize

It will fail. We need to reupload image or delete from table. The run command again.

This is extremely timeconsuming.
What I want.

Compare contents of catalog_product_entity_media_gallery with what is actually on our server.
So we see at once what is missing. Then we can upload those at once and run command again.

All help how to do this comparison is welcome.

[Damian Culotta]

Hi @emile_rdam,

Maybe you can check what happens with this module? https://github.com/magento-hackathon/EAVCleaner

[emile_rdam]

Thank you for this.

Looks promising. Will use it. But this solves removing images that are not used.

What I'm looking for as a automated way to find out which images are missing that according to the catalog_product_entity_media_gallery table should be there.

[emile_rdam · Accepted solution]

Found out how to do this. Bit timeconsuming but possible. Will mention in case you encounter same problem.

I use a windows pc.

1 - Download backup from public_html to you computer. Unzip if necessary

2 - Delete on your pc the contents of  pub/media/catalog/cache

3 - Open commandprompt. There you will export the filestructure of your pub/media/catalog/product with something like this command type dir /a /s > structure.txt 

4 - open the structure.txt. Now you have list of all images on server.
Make sure you search and replace (put it in excel) so you only keep lines like: /0/0/productname.jpeg

5 - open excel. Put all values from images on your server in first column

6 - open phpmyadmin - go to your mysql

7 - find catalog_product_entity_media_gallery -> choose export -> export .csv

8 - punt contents of export.csv in excel

9 - that list should look exactly like list which made from your pc

10 - use vlookup to compare: =VLOOKUP(E1;A:A;1;0) if you put values from your backup pc in column A and the mysql data in column E 

Et voila. Now you now what is missing.