Images Disappear from Media Library

Hey everyone,

I just solved a problem I couldn’t find a solution for: my images remained on my server, but all the entries (with post_type as “attachment”) in my wp_post table (or $wpdb->posts for those that have changed your prefix) were gone! Their metadata entries remained in my wp_postmeta table. Their absence caused my Media Library to be empty–not to mention ‘featured image’ functionality to be gone.

(Their continued presence in wp_postmeta caused problems as well.)

Anyway, “Add From Server” is a plugin that’ll let you get your gallery back to sorts. Like I said, all my images existed on the server, but were missing from my Media Library. It makes new entries for the images in wp_posts. But they’re orphans.

All my posts are image related. They still displayed the correct image because the urls in the posts aren’t relative. So it was easy to find the parents of the image attachments with a little php.

function catch_that_image() {
  global $post, $posts;
  $first_img = '';
  ob_start();
  ob_end_clean();
  $output = preg_match_all('/<img.+src=[\'"]([^\'"]+)[\'"].*>/i', $post->post_content, $matches);
  $first_img = $matches[1][0];

  if(empty($first_img)) {
    $first_img = "/path/to/default.png";
  }
  return $first_img;
}

$matches = Array();

$imgquery = new WP_Query( 'cat=-1&posts_per_page=250' );
while($imgquery->have_posts()) {
	$imgquery->the_post();
	array_push($matches, Array(
		'id'=>get_the_ID(),
		'image'=>catch_that_image()));
}

foreach ($matches as $match) {
	$wpdb->update(
		'wp_posts',
		array('post_parent'=>$match['id']),
		array(
			'guid'=>$match['image'],
			'post_type'=>'attachment',
			'post_parent'=>0
		),
		array('%d'),
		array(
			'%s',
			'%s',
			'%d'
		)
	);
}

Then I ran this query from http://wordpressapi.com/set-post-image-featured-image-automatically/

INSERT INTO wp_postmeta (meta_value, meta_key, post_id) SELECT DISTINCT(ID), post_type , post_parent FROM wp_posts WHERE post_type= 'attachment' AND post_parent !=0 AND post_status='inherit';

UPDATE wp_postmeta SET meta_key = '_thumbnail_id' WHERE meta_key='attachment'

It makes new wp_postmeta entries that tell everyone about your featured images.

BUT those former wp_postmeta entries that point to wp_posts entries that don’t exist mess with the_post_thumbnail() function. It just gets the first entry that tells where the thumbnail image is, so if it finds the empty one first it won’t work!

Here, http://wordpress.org/support/topic/sql-query-to-delete-orphans-wp_postmeta
you’ll find the SQL query to eliminate orphaned posts.

I used:

DELETE FROM wp_postmeta
WHERE wp_postmeta.meta_key='_thumbnail_id'
        AND NOT EXISTS (
	SELECT * FROM wp_posts
	WHERE wp_postmeta.meta_value = wp_posts.ID
)

But you can check to see what will be deleted with:

SELECT * FROM wp_postmeta
WHERE wp_postmeta.meta_key='_thumbnail_id'
        AND NOT EXISTS (
	SELECT * FROM wp_posts
	WHERE wp_postmeta.meta_value = wp_posts.ID
)

And that’s it!

(I’m at http://oobites.com to see everything working.)