Reset auto-increment for option_id in wp_options table

reset-option-id.sql

-- Step 1: Create a table backup so that you won't have to restore the whole DB if something goes goofy
CREATE TABLE wp_options_bak LIKE wp_options;
INSERT INTO wp_options_bak SELECT * FROM wp_options;

-- Step 2: Export data in the existing order, ordered by option_id
CREATE TABLE wp_options_tmp AS 
SELECT option_name, option_value, autoload 
FROM wp_options 
ORDER BY option_id;

-- Step 3: Drop the original table
DROP TABLE wp_options;

-- Step 4: Recreate the original table structure
CREATE TABLE wp_options LIKE wp_options_bak;

-- Step 5: Insert data in the same order
INSERT INTO wp_options (option_name, option_value, autoload)
SELECT option_name, option_value, autoload FROM wp_options_tmp;

-- Step 6: Reset AUTO_INCREMENT (MySQL will set it to highest ID + 1)
ALTER TABLE wp_options AUTO_INCREMENT = 1;

-- Step 7: Drop the temporary table
DROP TABLE wp_options_tmp;

-- Step 8: Drop the backup table after confirming that everything is good to go
DROP TABLE wp_options_bak;