[web] MySQL, AutoIncrement and the number zero

This topic is 4706 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

Recommended Posts

Share on other sites
"However, the mysql command line client complains bitterly when it hits an item with an id of 0 that it already exists."

Did you make sure that the column isn't set as a primary key or unique key?

Share on other sites
Yes, the column is a unique key.
Let's say I have this table:
+----+------+| id | data |+----+------+ |  1 | Fish ||  2 | Meat ||  0 | Eggs ||  3 | Spam ||  4 | Bean |+----+------+

(Shown in the order I inserted them). mysqldump will create a .sql file that looks like:

INSERT INTO tablename SET id='1', data='Fish';
INSERT INTO tablename SET id='2', data='Meat';
INSERT INTO tablename SET id='0', data='Eggs';
INSERT INTO tablename SET id='3', data='Spam';
INSERT INTO tablename SET id='4', data='Bean';

The problem is that it will actually insert them as:
+----+------+| id | data |+----+------+ |  1 | Fish ||  2 | Meat ||  3 | Eggs | --- It sets Eggs as #3 when it SHOULD be 0.|  3 | Spam | --- Tries to set Spam as #3 as well, which causes an error.|  4 | Bean | --- Because of error, this never gets added.+----+------+

Share on other sites
How about temporarily switching auto_increment functionality off, doing the inserts, then switch it back on afterwards, e.g.:

alter table tablename modify id int not null;

insert...

alter table tablename modify id int auto_increment;

Share on other sites
Also, shouldn't:

INSERT INTO tablename SET id='1', ...

really be:

INSERT INTO tablename SET id=1, ...

Share on other sites
Quote:
 Original post by abstractworldsHow about temporarily switching auto_increment functionality off, doing the inserts, then switch it back on afterwards;
I'm doing this through a batch routine, so manually editing the .sql file that mysqldump produces is a bit of a pain.

Share on other sites
They way I handled this in a database of mine is to use 1 as the unknown value and just not using 0 at all. If you do that, don't forget to update your tables to have a default value of 1 on columns that can de initialized to unknown!

Another good way would be to use the mysqlimport command instead of the mysql command. Use the --ignore command to ignore data with duplicate keys, or use the --replace command to replace them.

The problem is that rows with an ID of 0 are simply impossible in the table structure you made. You probabely manually updated one of the rows to force it to be zero. I suggest you fix that so the automated tools work properly.

Share on other sites
Quote:
 Original post by SanderYou probabely manually updated one of the rows to force it to be zero.
Yes, I did. However, there's already a lot of data referencing id 1 as a valid location/job/whatever, so it's a bit of a pain to swap to a 1-based index. Ah well. My batch file currently writes a small "patch.sql" that fixes/adds the dodgy data (which is far from ideal) but it does work.

Share on other sites
Well, fixing it should be easy though. Just a few SQL queries and you're all done. Remove whatever object there is at index 1, add that object to the end of the table, update references to that object (UPDATE SET ref=X WHERE ref=1), force a new object on index one (1 insert, 1 update to force the index to 1), update references to that default object.

All in all: 6 queries (per fix) and it's fixed for good. It might be worth your trouble, depending on the ammount of tables that you have to fix and the ammount of other columns that reference those tables.

1. 1
2. 2
Rutin
24
3. 3
4. 4
JoeJ
16
5. 5

• 14
• 29
• 11
• 11
• 9
• Forum Statistics

• Total Topics
631774
• Total Posts
3002274
×