Fixing Auto Increment MySQL Errors (for WordPress)

The problem:

  • You can’t update your settings in WordPress.
  • You’re unable to add new posts or upload media.

Likely cause:

AUTO_INCREMENT is disabled in the MySQL table structure.

What is AUTO_INCREMENT and why is it important?

WordPress relies on numbers to save and retrieve settings from tables stored in the database. These are called option_id in the options table structure, and ID in the posts table structure. Without AUTO_INCREMENT, WordPress will not be able to determine the id number for new items sequentially.

How did AUTO_INCREMENT get disabled in the first place?

I have encountered this problem when changing hosting providers, and more specifically, when I export a database to another MySQL server. It also seems to happen when the domain name changes.

So what’s the fix?

You could try enabling auto increment in phpMyAdmin. Make sure you backup your database before implementing this solution.

Step 1:

Go to the options table of your WordPress install and click on the structure tab. Notice that AUTO_INCREMENT is not shown in the Extra column for the option_id row. Find “option_id” and click “Change.”

Tick the A_I checkbox and click “Save.” There’s nothing more you need to do if you did not see any errors. Otherwise, move on to step 2 if your screen looks like this:

Step 2:

Select all items listed in the table and click “Export.”

Leave the file format as SQL and click “Go.” Your web browser will download the SQL file to computer.

Step 3:

Open the SQL file in a plain text editor. Make sure you have something similar to the following code in the beginning of your script:

--
-- Database: `wordpress`
--

-- --------------------------------------------------------

--
-- Table structure for table `wp_options`
--

CREATE TABLE `wp_options` (
  `option_id` bigint(20) UNSIGNED NOT NULL,
  `option_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `option_value` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `autoload` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'yes'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

This code tells MySQL to create a table called “wp_options” in a database called “wordpress” at import. Chances are your database name and WordPress prefix are different from this example, so I do not recommend copying and pasting this as is.

Step 4:

Scroll to the very bottom of the script. You should see something similar to this:

(475, 'fv_player_popups', 'a:1:{i:1;a:4:{s:4:\"name\";s:0:\"\";s:4:\"html\";s:0:\"\";s:3:\"css\";s:0:\"\";s:8:\"disabled\";s:1:\"0\";}}', 'yes');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `wp_options`
--
ALTER TABLE `wp_options`
  ADD PRIMARY KEY (`option_id`),
  ADD UNIQUE KEY `option_name` (`option_name`),
  ADD KEY `autoload` (`autoload`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Notice the number in the first line. That number corresponds to the last option_id on the list. Your number will be different depending on the number of settings you have saved from your WordPress installation. Make note of it.

Step 5:

Copy and paste the following code at the absolute bottom of the SQL file:

--
-- AUTO_INCREMENT for table `wp_options`
--
ALTER TABLE `wp_options`
  MODIFY `option_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=480;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Including this code will tell MySQL to enable auto increment for the table called “wp_options.” Change the prefix so it matches with what’s already there.

Example from a different database repair.

Step 6:

Find…

AUTO_INCREMENT=480;

Remember the option_id number from the last item on your options table? Mine was 475, so I went with 480 to avoid problems and because it’s a nice round number. Pick a number that’s greater than the last option_id number on your list. Save and close.

Step 7:

Login to phpMyAdmin and drop the options table from your database.

Import the SQL file that you’ve just modified. You’ll know the import was a success if phpMyAdmin populates your screen with green confirmation boxes.

Step 8:

Check the options table to see if AUTO_INCREMENT has been enabled.

You should now be able to make changes to your WordPress installation. Happy blogging.