WordPress Post ID Huge Jumps

I am moving from my previous linux web host to dotNet host which can handle ASP.NET pages. I am surprised the migration is relatively easy and wordpress already support IIS rewrite to maintain permalink.

I was tried to check everything out and found my several latest post get HUGE ID, like this one 4.162.708.458. I never post 4 millions article to my website even combine with my revisions. I don’t remember what the exact error but will cause error as like “You don’t have permission to edit” or some kind like that when trying to edit content. In Linux based machine this won’t lead to any problem anyway.

So, I drill down the data and found out interesting auto increment jump from 210 suddenly jump to that number. I don’t want my next post will have ID in 4.162.708.459. Problematic post ID around 14 posts. So, I need a solution to make my next post will be 225. I am using store procedure to achieve my purpose, I know I can use PHP but mysql SP doesn’t need mysql_connect or mysqli_connect. Let’s see the code.

DELIMITER $$

CREATE PROCEDURE `wp_fix`(IN start_id BIGINT)
BEGIN
DECLARE done INT DEFAULT FALSE;
    DECLARE post_id BIGINT;
    DECLARE fix BIGINT;
    DECLARE posts CURSOR FOR SELECT id FROM wp_posts WHERE id > start_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SET fix = start_id;

    OPEN posts;

    post_loop: LOOP
        FETCH posts INTO post_id;
        IF done THEN
            LEAVE post_loop;
        END IF;
        SET fix = fix + 1;
        UPDATE wp_posts SET ID = fix WHERE ID = post_id;
        UPDATE wp_comments SET comment_post_ID = fix WHERE comment_post_ID = post_id;
        UPDATE wp_postmeta SET post_id = fix  WHERE post_id = post_id;
        UPDATE wp_term_relationships SET object_id = fix WHERE object_id = post_id;
    END LOOP;

    CLOSE posts;
    set fix = fix + 1;    

    set @qry = concat('ALTER TABLE wp_posts AUTO_INCREMENT = ',fix);
    PREPARE stmt from @qry;
    EXECUTE stmt;
END$$

Delimiter used to ignore semicolon from being parsed by mysql server, maybe it’s like CDATA in XML.

wp_fix procedure have one parameter, start_id, which is the last “good and proper” id. The procedure will read each post id and updating related tables to reflect the changes. So, any comments, meta, and category will still preserved.

The last thing, the procedure will set the auto_increment for wp_posts table to plus one of the last generated id. For example, if the generated id ends with 410 then the auto_increment will be set to 411.

To use this stored procedure is easy. Just open your phpmyadmin or mysql workbench and type

CALL wp_fix(210);

In this example, start_id parameter filled with 210. Hopes this will help you to regenerate post_id for wordpress post table.