Healing / recreating relationships

  • Resolved manugarrido

    (@manugarrido)


    10 years, 6 months ago

    Hi WordPressers,

    Background:

    A WordPress imported lots of posts using RSS agregator (this is not an issue of that plugin). After that, due to lack of maintenance, most of the posts had to be deleted in a bad way (because the server timed out when trying to), querying the database and getting rid of lots of drafts (over 60000).

    As part of a scheduled migration, the hosting was changed and none of these files were copied.

    Now:

    My problem is that awful deletion left behind tons (over 250000) media records that were attached to these drafts. Technically they are still attached because the database migration took the orphaned relationships.

    Those media records cannot be quickly deleted because they are not listed within the ‘Unattached’ link.

    They are listed as ‘Uploaded to: (Unattached)’.

    Problem:

    Ideally I’d query an awesome order to mysql to let it do all the hardwork (as it should be!) but I cannot even guess a way to select all the attachments whose related post doesn’t exists. I don’t really understand how the tables relate to themselves.

    Why:

    I have to clean that because there still are around 900 posts with images. Checking for 404s and taking them one by one from the old hosting is not feasible.

    The task is going to be massively easier once all the orphaned images are taken care of.

    After that, a simple file_exists() could do the filtering and some cleverly placed str_replace() can retrieve the img’s src from the old hosting (so just drag’n’drop!…)

    Sorry for the massive chunk of text! Thanks!

Viewing 3 replies - 1 through 3 (of 3 total)
  • Thread Starter manugarrido

    (@manugarrido)

    Actually, the restoration of the needed images can be done without healing the relationships.

    I made a quick script to match old and new (missing) images by eye, so the drag and dropping can start:

    <table>
				<tr>
					<td>#</td>
					<td>POST ID</td>
					<td>OLD SITE</td>
					<td>NEW SITE</td>
					<td>MISSING IMAGE PATH</td>
				</tr>
		<?php

		global $post;
		$args = array(
			'posts_per_page' => -1,
			'offset'=> 0,
			'post_status' => array('publish')
		);
		$cnt = 0;
		$myposts = get_posts( $args );
		foreach ( $myposts as $post ) :
			setup_postdata( $post );
			$tn_id = get_post_thumbnail_id( $post->ID );
			$img = wp_get_attachment_image_src( $tn_id, 'full' );
			//if ( has_post_thumbnail() ) {
				if ( !file_exists( $img[0] ) ) {
					echo '
						<tr>
							<td>'.++$cnt.'</td>
							<td>'.get_the_ID().'</td>
							<td><center><img src="'.str_replace('https://','https://staging.',$img[0]).'" style="width:auto;height:50px;" /></center></td>
							<td><center><img src="'.$img[0].'" style="width:auto;height:50px;"/></center></td>
							<td><a href="'.$img[0].'" target="_blank">'.$img[0].'</a></td>
						</tr>
					';
				}
			//}
		endforeach;
		wp_reset_postdata(); ?>

			</table>

    However the database has to be healed.

    Thread Starter manugarrido

    (@manugarrido)

    I think that I have the solution.

    SELECT a.ID, b.meta_key, b.meta_value
FROM wp_posts a
LEFT JOIN wp_postmeta b ON ( a.ID = b.post_id )
WHERE a.post_parent NOT IN ( SELECT ID FROM wp_posts )
AND a.post_type = 'attachment'
LIMIT 999999;

    As far as I understand, this select retrieves the attachments whose related posts don’t exist, along with the related records of wp_postmeta. Am I correct?

    In case I do, converting that order in a DELETE would be safe enough to delete all orphaned attachments?

    Thread Starter manugarrido

    (@manugarrido)

    This worked for me

    DELETE FROM wp_posts
WHERE post_parent NOT IN (
	SELECT ID FROM (
		SELECT ID FROM wp_posts WHERE post_type <> 'attachment'
	) as sub_query
)
AND post_type = 'attachment'
;

DELETE FROM wp_postmeta
WHERE post_id NOT IN ( SELECT ID FROM wp_posts )
;
Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Healing / recreating relationships’ is closed to new replies.