SQL: Repair Auto Increment of a Table

Occasionally it happens that overlaps occur in a large table and the message Duplicate entry '0' for key 'PRIMARY' for query then gets the upper hand in the error logs. This error can be fixed quite easily with a short line of SQL.

Fix duplicate entry

To fix the error, a simple line of SQL is enough. This line usually fixes the error immediately, you only need the name of the table and the last value of your primary key which is set to Auto Increment.

ALTER TABLE `table_name` AUTO_INCREMENT = value;

Replace in the command table_name with the name of your table and the value with the last value of your primary key + 1 or 10. In any case something larger than the current value.

The command makes the auto increment continue at the new value and there is no duplicate entry anymore. So at least you have your table fixed.

The error within WordPress is in most cases like this:

WordPress database error Duplicate entry '0' for key 'PRIMARY' for query INSERT INTO 'table_name'.

However, to avoid the error in the future, you still need to fix the script that may be used too quickly, without pauses, to add entries to the table. This varies depending on the script, so there is no tip from me here.

If you have executed the above command, an OPTIMIZE TABLE and REPAIR TABLE may also help to avoid possible errors in the future.

REPAIR TABLE `table_name`
OPTIMIZE TABLE `table_name`

The first command tries to fix the table if it is broken and the second command optimizes the table to make it possibly a bit faster. The fastest way to make your table faster is to use the right types for each column. For example, you should avoid using text or longtext everywhere. Also a full text search can make your table faster.

If you have access to e.g. phpMyAdmin, you can select the table there and open it. Then click on SQL and execute your SQL there.

SQL: Repair Auto Increment of a Table

Comments (1)

More from this blog

Remove defined HTML tags including content from a string with PHP

Create and log in a WordPress administrator with FTP

WordPress: Useful functions for working with arrays and objects

Make MySQL searches faster with full text index

Fix duplicate entry

Command Palette

Comments (1)

More from this blog

Fix duplicate entry