While playing around with MySql recently, I discovered a strange little quirk. An automatically incremented field cannot be reset - that is, it cannot be reset to a lower value than it currently is. For example, say that you insert a lot of records into a table, or that you manually add a key that’s very high:
MySql.Data.MySqlClient.MySqlException: ‘Duplicate entry ‘2147483647’ for key ‘test_table.PRIMARY”
In my case, I was simply playing around with some settings - if you have a table with more records than that then you may have other issues than this. However, my initial idea was to reset the count:
ALTER TABLE myschema.test\_table AUTO\_INCREMENT = 1
It turns out that, whilst you can do this, you can only do it for numbers higher than it currently is. Presumably this is to prevent conflicts or whatever. Anyway, the way around it for me was to simply insert a new record, but to override the setting of the auto-increment:
INSERT INTO \`myschema\`.\`test\_table\`
(\`firstname\`,
\`surname\`,
\`key\`)
VALUES
('',
'',
1);
You would then need to remove this record. Not ideal, but the only way I could get this to work.