SQL: Repair Auto Increment of a Table
Speaks many languages, but currently only uses PHP, JavaScript and WordPress (I consider it its own language, since it's a big sandbox).
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.
