"Duplicate entry '0' for key PRIMARY" usually means your database lost AUTO_INCREMENT

Installing a plugin fails with:

Duplicate entry '0' for key 'PRIMARY'

The obvious reading is that the plugin tried to insert a row with id 0. The actual reading is usually the opposite: the plugin inserted a row without naming the id column, relied on AUTO_INCREMENT to supply one, and got 0 because the column no longer has AUTO_INCREMENT on it. The second such insert then collides with the first.

This is not a plugin bug, and chasing it as one wastes a lot of time. Here is how it looks, why it spreads, and how to repair it without setting a second trap.

The tell: it moves

We first saw this installing a module on a PrestaShop shop. The failure walked through the tables in order:

1. ps_configuration — first setting the module writes
2. then ps_log — the "starting module install" row
3. then ps_module — the row that registers the module itself

Fix one table, run the install again, fail on the next. That whack-a-mole is the diagnostic. A plugin bug does not migrate to core tables. If your failure changes table each time you fix one, the corruption is database-wide.

Later the same shop failed in the storefront: adding anything to the cart threw the same error on INSERT INTO ps_cart. Same cause, another table nobody had touched yet. If it had got as far as checkout it would have been ps_orders next.

Where it comes from

A bad import or restore. mysqldump, phpMyAdmin exports and various migration tools can produce a schema that recreates the primary key without the AUTO_INCREMENT attribute — and such a restore often leaves the internal counters stale as well, with rows stranded at id 0.

On the shop we repaired, every AUTO_INCREMENT in the database was gone: products, combinations, modules, configuration, employees, shops, languages, and around fifty-five module tables.

The trap when you fix it

The natural fix is:

ALTER TABLE `ps_log` MODIFY `id_log` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

which fails with:

#1062 - ALTER TABLE causes auto_increment resequencing,
        resulting in duplicate entry 'N' for key 'PRIMARY'

Adding AUTO_INCREMENT makes MySQL renumber the existing id = 0 row, and the stale counter hands it an id that is already in use.

Prevent the renumber:

SET SESSION sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
ALTER TABLE `ps_log`           MODIFY `id_log`           INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `ps_configuration` MODIFY `id_configuration` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `ps_module`        MODIFY `id_module`        INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

NO_AUTO_VALUE_ON_ZERO tells MySQL to treat a literal 0 as a real value rather than a request for the next id, so the existing row keeps its id and nothing is renumbered.

Run the SET and the ALTERs in the same submission. SET SESSION lasts for one connection; phpMyAdmin may hand you a different connection for a separate query, and then the ALTER fails exactly as before while you are certain you just set the mode.

Finding every affected table

Do not do this by hand. Ask the schema:

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
  AND COLUMN_KEY = 'PRI'
  AND EXTRA NOT LIKE '%auto_increment%'
  AND DATA_TYPE IN ('int','bigint','smallint','mediumint','tinyint')
ORDER BY TABLE_NAME;

Hardcode the schema name. A generator built on DATABASE() returns nothing when phpMyAdmin is sitting in the information_schema context, which it often is if you arrived through the schema browser. The query runs, returns zero rows, and you conclude the database is fine.

That query lists candidates. It does not list fixes — and the difference matters, because some of those primary keys are legitimately not auto-increment.

What to exclude

Blindly ALTERing everything the query returns will damage your database. Exclude:

• Every composite primary key. AUTO_INCREMENT applies to a single column that is the leftmost part of a key. Multi-column PKs in that list are joins and pivots and must be left alone.
• Single-column integer PKs that are by design not auto-increment — a foreign key doubling as the primary key. In PrestaShop those include ps_address_format (id_country), ps_product_sale (id_product), ps_ip2location (ip_to), the ps_layered_indexable_* tables, ps_pscheckout_address / ps_pscheckout_customer (id_customer), ps_psshipping_address, and the ps_eventbus_* tables. Your schema will have its own equivalents: the test is whether the value is assigned by another table or generated here.

Two special cases worth knowing, because they look like exclusions and are not: ps_customization.id_customization and ps_cms_role.id_cms_role sit in composite primary keys but are legitimately AUTO_INCREMENT, as the leftmost column.

The workable process: generate the ALTER statements with a query that CONCATs them, read the output, delete the rows that belong to the exclusion list, then run what is left. Two failure modes we hit doing exactly this:

• Running the generator and believing that fixed something. It only prints statements. You have to run its output.
• Deleting the id = 0 rows instead of running the ALTERs. That clears the immediate collision and leaves the schema still broken, so it comes back on the next insert.

One more operational detail: if you paste a large batch into phpMyAdmin and the newlines are lost, a -- comment swallows the statement that follows it. Ship comment-free SQL for bulk paste.

What to do instead, if you can

Repairing in place works — we did it across about 130 tables and verified the storefront afterwards — but think about what it means. A restore that dropped AUTO_INCREMENT from every primary key was not selective. It may also have dropped foreign keys, defaults, or column attributes you have not noticed yet, and no amount of ALTERing primary keys tells you about those.

If a clean dump from before the bad import exists, restore that instead. The in-place repair is what you do when it does not.

The five-second version

• Plugin install fails on a core table with Duplicate entry '0' → suspect the schema, not the plugin.
• Failure moves to a different table each time you fix one → confirmed, it is database-wide.
• SET SESSION sql_mode = 'NO_AUTO_VALUE_ON_ZERO' in the same submission as the ALTERs.
• Drive it from information_schema, hardcode the schema name, and curate the list before running it.
• Prefer restoring a good dump.

---

From maintaining around sixty PrestaShop modules at MEG Venture. We lost a fortnight to this one in two separate incidents before recognising the shape.