คู่มือการแก้ปัญหา Lock Wait Timeout และปรับปรุงข้อมูล WordPress ขนาดใหญ่ด้วย PHP Script แบบปลอดภัย

<?php
// Disable output compression and buffering for real-time terminal feedback
if (function_exists('apache_setenv')) {
    apache_setenv('no-gzip', '1');
}
@ini_set('zlib.output_compression', 'Off');
@ini_set('output_buffering', 'Off');
@ini_set('implicit_flush', 'true');
for ($i = 0; $i < ob_get_level(); $i++) {
    ob_end_flush();
}
ob_implicit_flush(true);

echo "[INFO] Loading WordPress environment...\n";
flush();

// Disable caching plugins to prevent lock wait timeouts and query interception
define( 'W3TC_DISABLE_CACHING', true );
define( 'WP_USE_THEMES', false );

require_once( __DIR__ . '/wp-load.php' );

if ( function_exists( 'w3tc_pgcache_flush' ) ) {
    @w3tc_pgcache_flush();
}

global $wpdb;
// Extend session-level lock wait timeout to handle large data sets safely
$wpdb->query( "SET SESSION innodb_lock_wait_timeout = 300" );

$table = $wpdb->posts;
$batch = 2000; // Number of records processed per batch
$last_id = 0;  // Tracks the last processed ID for cursor-based pagination
$total_updated = 0;

echo "[INFO] Starting cursor-based batch updates...\n";
flush();

for ($i = 1; $i <= 300; $i++) {
    // Fetch records strictly greater than the last processed ID to prevent gaps or overlaps
    $rows = $wpdb->get_results($wpdb->prepare("
        SELECT p1.ID AS id1, p1.post_name
        FROM {$table} p1
        WHERE p1.ID > %d 
          AND p1.post_content = '' 
          AND p1.post_name REGEXP '^[0-9]+$'
        ORDER BY p1.ID ASC
        LIMIT %d
    ", $last_id, $batch));

    if (empty($rows)) {
        echo "\n[SUCCESS] All records have been successfully updated!\n";
        break;
    }

    $count = 0;
    foreach ($rows as $row) {
        // Update the pointer to the current row ID
        $last_id = $row->id1;

        // Find the preceding post with an empty content to swap data with
        $id2 = $wpdb->get_var($wpdb->prepare(
            "SELECT ID FROM {$table} WHERE ID < %d AND post_content = '' ORDER BY ID DESC LIMIT 1",
            $row->id1
        ));

        if (!$id2) continue;

        $target = $wpdb->get_row($wpdb->prepare(
            "SELECT post_title, post_name FROM {$table} WHERE ID = %d", 
            $id2
        ));

        if ($target) {
            // Direct query update bypassing cache hooks
            $wpdb->query($wpdb->prepare(
                "UPDATE {$table} SET post_title = %s, post_name = %s WHERE ID = %d",
                $target->post_title,
                $target->post_name,
                $row->id1
            ));
            $count++;
        }
    }

    $total_updated += $count;
    echo "-> Batch {$i} | Reached ID: {$last_id} | Updated in this batch: {$count} | Total cumulative: {$total_updated}\n";
    flush();

    // Terminate loop early if the fetched dataset is smaller than the batch size (end of table reached)
    if (count($rows) < $batch) {
        echo "\n[SUCCESS] Reached the end of the table records!\n";
        break;
    }
}

// Automatically sync the auto_increment value to the highest current post ID
$max_id = $wpdb->get_var("SELECT MAX(ID) FROM {$table}");
$next_ai = intval($max_id) + 1;
$wpdb->query("ALTER TABLE {$table} AUTO_INCREMENT = {$next_ai}");
echo "[INFO] Table auto_increment successfully reset to: {$next_ai}\n";