I have database with InnoDB tables in a production server and development. Several tables have auto_increment columns and there are few Foreign Keys also included.
Problem is that after taking a dump (schema and data) from production to development server, suddenly NO new insert from PHP increases any auto_increment key and they result having ai_key 0 in the table. I thought this should even not be possible without touching sql_mode setting on server. After this 0-key, all procedures just updates the 0-key row on database.
Insert goes like this (just for example):
IF vCol = 0 AND (SELECT COUNT(*) FROM aTable WHERE `aId` = vId) = 0
THEN
INSERT INTO aTable (
`aId`,
`bRandomCol`
)
VALUES
(
vCol,
vRandomCol
)
Using mysqli and prepared statements in PHP.
Calling prosedure outside PHP also fails. Making pure insert clause including auto_increment field into it and giving value 0 for it, that succeeds.
Inspecting table with SHOW TABLE STATUS FROM X LIKE 'Y' results the correct key in order.
I can repair the problem with ALTER TABLE X AUTO_INCREMENT = Z.
Have anyone seen problem like this before or know why this happens or is there any workaround?
