compare images from catalog_product_entity_media_gallery with actual presence on server

Question

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:

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

Answer 1

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.