Clean Up wp_attachment_metadata for Deleted Images

This cleans up any WP attachment metadata for images that no longer exist in the uploads directory. It checks all rows in the WordPress postmeta table for any images that are referenced in attachment metadata. Then, any _wp_attachment_metadata that references images that do not exist in the uploads folder (e.g. deleted images), will be updated the correct way, not simply manually changing the values in the database, but rather letting WordPress update the serialized values.

It goes through all images listed in all _wp_attachment_metadata values, and it checks your ‘wp-content/uploads’ folder to see if that image exists. If it doesn’t exist, the metadata in the WordPress database will be updated to remove that image data in a correct way using WordPress functions.

You must edit line 12 to use your own WordPress database details.

function isa_cleanup_attachment_metadata(){
    if ( get_option( 'my_run_only_once_1' ) != 'completed' ) {
  
        $upload_dir   = wp_upload_dir();
        $upload_basedir = $upload_dir['basedir'];
        $attachment_post_ids = array();
 
        // get all attachment ids for any _wp_attachment_metadata that exists in the wp_postmeta table
         
        // @todo edit database details: 
 
        $mysqli = new mysqli('DB_HOST', 'DB_USER', 'DB_USER_PASSWORD', 'DATABASE_NAME');
 
        if( ! $res = $mysqli->query( "SELECT * FROM wp_postmeta WHERE meta_key = '_wp_attachment_metadata'" ) ) {
            error_log($mysqli->error);
        } else {
            while ($row = $res->fetch_assoc()) {
                $attachment_post_ids[] = $row['post_id'];
            }
            $res->close();
        }
        $mysqli->close();
 
        foreach( $attachment_post_ids as $attachment_id ) {
  
            $data = wp_get_attachment_metadata( $attachment_id );
 
			$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( 'my_run_only_once_1', 'completed' );
     
 
    }// end run only once
 
}
 
add_action('admin_init', 'isa_cleanup_attachment_metadata');