Realigning the IDs of multiple mySQL columns _without_ Foreign Keys

Question

I've inherited a project, which because of a weird quirk of WP's tumblr importer, has got a lot of posts whose IDs are > 2147483647, and are not causing grief on the new host that I'm migrating to. Their 32-bit architecture won't play nice with those big integers.

To that end, I've been trying to adapt the query I found over here,

SET @count = 0;
UPDATE `users` SET `users`.`id` = @count:= @count + 1;

to something more like:

SET @count = 0;
UPDATE wp_posts
    left join wp_postmeta on wp_postmeta.post_id = wp_posts.id
    SET wp_postmeta.post_id = @count = wp_posts.id = @count, @count := @count + 1;

Presumably, once I had that logic down, I could expand it to also join and update the wp_comments.comment_post_ID field, among others.

But, alas it throws a syntax error. Is there any way to update all those ids to the same @count variable, and keep it incrementing with every row?

Answer 1

My ultimate way out of this mess was to create a small script that would just go query-crazy, but ultimately keep those relationships intact while realigning all the ids starting at 0.

I'm sure it could be optimized, but it hits the spot for this one-time solution.

It also spits out a map of old ids to new ids, since my client posts without titles quite frequently, and WP defaults to those ids as a slug. So I'll import that bad-boy for redirects.

$id_map = array();
$update = array();

$posts = $db->query("SELECT * from wp_posts");

for ($count = 1; $count <= $posts->num_rows; $count++) {
    $row = $posts->fetch_assoc();
    $id_map[$row['ID']] = $count;

    if ($row['post_type'] == 'post' && $row['post_title'] == '')
        $update[$row['ID']] = $count;

    $currentID = $row['ID'];

    // Update post IDs sequentially
    $db->query("UPDATE wp_posts set id = " . $count . " WHERE id = " . $currentID);

    // Update their post_metas reference, and restore thumbnails
    $db->query("UPDATE wp_postmeta set post_id = " . $id_map[$currentID] . " WHERE post_id = " . $currentID);
    $db->query("UPDATE wp_postmeta set meta_value = " . $id_map[$currentID] . " WHERE meta_value = " . $currentID . " AND meta_key = '_thumbnail_id'");

    // Update the row's children.
    if ( $db->query("SELECT post_parent from wp_posts where post_parent = $currentID")->fetch_assoc() )
        $db->query("UPDATE wp_posts set post_parent = " . $id_map[$currentID] . " WHERE post_parent = " . $currentID);

    // If it has comments
    if ( $row['comment_count'] != 0 )
        $db->query("UPDATE wp_comments set comment_post_ID = " . $id_map[$currentID] . " WHERE comment_post_ID = " . $currentID);

    // If it has buddyPress activity
    if ( $bp = $db->query("SELECT * from wp_bp_activity where item_id = $currentID")->fetch_assoc() ) {
        $db->query("UPDATE wp_bp_activity 
            set item_id = " . $id_map[$currentID] . 
            ", primary_link = '" . preg_replace('/' . $currentID . '$/', $id_map[$currentID], $bp['primary_link']) . "'" .
            " WHERE item_id = $currentID");
    }

    // If it's secondary buddyPress activity
    if ( $db->query("SELECT id from wp_bp_activity where secondary_item_id = $currentID")->fetch_assoc() )
        $db->query("UPDATE wp_bp_activity set secondary_item_id = " . $id_map[$currentID] . " WHERE secondary_item_id = $currentID");

    // If there's a buddyPress Group with this forum
    if ( 'forum' === $row['post_type'] )
        if ( $bp = $db->query("SELECT * from wp_bp_groups_groupmeta WHERE meta_value LIKE '%$currentID%'")->fetch_assoc() ) {
            $db->query("UPDATE wp_bp_groups_groupmeta 
                set meta_value = '" . preg_replace("/$currentID;}/", $id_map[$currentID] . ";} ", $bp['meta_value']) . "'" .
                " WHERE meta_value LIKE '%$currentID%'");
            $db->query("UPDATE wp_bp_groups_groupmeta 
                set meta_key = '" . preg_replace("/$currentID/", $id_map[$currentID] . ";} ", $bp['meta_key']) . "'" .
                " WHERE meta_key LIKE '%$currentID%'");

        }

    if (in_array($row['post_type'], array('topic', 'reply', 'forum')))
        $db->query("UPDATE wp_postmeta 
            set meta_value = " . $id_map[$currentID] . " 
            WHERE meta_value = $currentID");
}

echo "<pre>" . json_encode($update) . "</pre>";