Remove unneeded Woocommerce image meta

Question

A couple of versions ago WOOCommerce has started to generate additional image thumbnails, in my case 100x100 and 700x700 (700x700 is actually the default image size for my WordPress installation so I don't get the idea of making additional copy).

To prevent this I've added the following code to my functions.php:

function shapeSpace_customize_image_sizes($sizes) {
    unset($sizes['shop_catalog']);
    unset($sizes['shop_single']);
    unset($sizes['shop_thumbnail']);
    unset($sizes['woocommerce_gallery_thumbnail']);
    unset($sizes['woocommerce_single']);
    unset($sizes['woocommerce_thumbnail']);
}
add_filter('intermediate_image_sizes_advanced', 'shapeSpace_customize_image_sizes');
add_filter('max_srcset_image_width', create_function('', 'return 1;'));

and it successfully stops WOOCommerce to spam my server any longer.

So now I want to delete all those -100x100 and -700x700 images but I can't do that as they are referenced in _wp_attachment_metadata column of wp_postmeta table.

The reference is always placed between "sizes" and "image_meta", and looks like

"sizes";a:4:{s:18:"woocommerce_single";a:4:{s:4:"file";s:30:"IMAGE-700x700.jpg";s:5:"width";i:700;s:6:"height";i:700;s:9:"mime-type";s:10:"image/jpeg";}s:29:"woocommerce_gallery_thumbnail";a:4:{s:4:"file";s:30:"IMAGE-100x100.jpg";s:5:"width";i:100;s:6:"height";i:100;s:9:"mime-type";s:10:"image/jpeg";}s:11:"shop_single";a:4:{s:4:"file";s:30:"IMAGE-700x700.jpg";s:5:"width";i:700;s:6:"height";i:700;s:9:"mime-type";s:10:"image/jpeg";}s:14:"shop_thumbnail";a:4:{s:4:"file";s:30:"IMAGE-100x100.jpg";s:5:"width";i:100;s:6:"height";i:100;s:9:"mime-type";s:10:"image/jpeg";}}s:10:"image_meta"

Very large string, but there is no need to read it, as the actual template is just

"sizes";a:4:{SOME_TEXT}s:10:"image_meta"

where a:4 after "sizes" goes for the number of thumbnails.

---

I want to find and replace every

"sizes";a:4:{SOME_TEXT}s:10:"image_meta"

with

"sizes";a:0:{}s:10:"image_meta"

I can do that manually but don't think it is a good idea for ~1000 images.

Is it possible with MySQL query? If so what it would be?

Thank you in advance.

Answer 1

Try this:

1. Export your database with phpMyAdmin
2. Open it's copy (always make backup) in Notepad++
3. Press CTRL+H to open Replace dialogue
4. Put "sizes".*."image_meta" into "Find what: " field
5. Put "sizes";a:0:{}s:10:"image_meta" into "Replace with: " field
6. Tick "Regular expression", untick ". matches newline"
7. Click "Find Next" button and make sure only the desired part of a string is selected
8. If it is, click "Replace All" button
9. Import your updated database with phpMyAdmin