In process of optimizing wordpress database, cleaning up unused postmeta and metadata is a requried work. Howerver, you should have good knowledge of wordpress generally. Don’t forget: “Backup before do anything“.
You might also like
PHP function cleans up metadata for images that is no longer exist in wordpress upload_basedir
Paste this code to your theme functions.php
ADVERTISEMENT
function hitet_cleanup_attachment_metadata(){
if ( get_option( 'hitet_clean_up_wp_attachments' ) != 'completed' ) {
global $wpdb;
$upload_dir = wp_upload_dir();
$upload_basedir = $upload_dir['basedir'];
//use $wpdb to get all attachments in the wp_postmeta table
$all_attachments = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}postmeta WHERE meta_key = '_wp_attachment_metadata'"
);
//var_dump($all_attachments);
foreach( $all_attachments as $attachment ) {
$data = unserialize($attachment->meta_value);
$original_file_name = $data['file'] ?? '';
$new_data = $data;
// get all filenames from the data
$all_sizes_filenames = wp_list_pluck( $data['sizes'], 'file' );
foreach( $all_sizes_filenames as $size => $filename ) {
$delete_flag = true;
$update_flag = false;
// check that each one doesn't exist, if it does, then don't delete the whole attachment, just update it
// get month/year for filename
$p = strrpos($original_file_name, '/');
$path = ($p !== false) ? substr($original_file_name, 0, $p+1) : '';
if ( file_exists( $upload_basedir . '/' . $path . $filename ) ){
$delete_flag = false;
} else {
error_log($path . $filename .' DOES NOT EXIST');
unset( $new_data['sizes'][ $size ] );// remove that size from the array
$update_flag = true;
}
}
// if there's no original file, it is not an image, so don't delete the attachment, just update it to remove the sizes
if ( $original_file_name && $delete_flag ) {
// none of the sized images exist, now check if original file exists
if ( ! file_exists( $upload_basedir . '/' . $original_file_name ) ) {
error_log('DELETING attachment meta id ' . $attachment_id . ' :');
error_log(print_r($data, true));
wp_delete_attachment( $attachment_id );
}
}
if ($update_flag) {
if ( count( $new_data ) === 1 && empty( $new_data['sizes'] ) ) {
$new_data = array();// to make sure empty _wp_attachment_metadata is deleted
}
error_log('UPDATING attachment meta id ' . $attachment_id . ' :');
error_log(print_r($new_data, true));
wp_update_attachment_metadata( $attachment_id, $new_data );
}
}// ends foreach attachment
update_option( 'hitet_clean_up_wp_attachments', 'completed' );
}// end run only once
}
add_action('admin_init', 'hitet_cleanup_attachment_metadata');
The code will run once when you load your wordpress admin dashboard. It will set “hitet_clean_up_wp_attachments” option to “complete” on finish. To run the function again you need to delete or modify “hitet_clean_up_wp_attachments” in your database.
