Sign in to follow this  

[web] MySQL, AutoIncrement and the number zero

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

If you intended to correct an error in the post then please contact us.

Recommended Posts

We have a number of lookup tables, such as "jobs" or "locations". Each one has an autonumber "id" field and a text field that contains the description (so location 1 is "Norwich", job 3 is "Chef", that sort of thing). In places where we have incomplete data we've assigned id 0 to be "(Unknown)". This works fine, until we come to transferring the database. I'm doing this by using mysqldump to create a .sql file which is then being redirected into the mysql command line client. I have had a number of real problems with this. [sad] My batch file looks like this: @echo off cls pause echo DUMPING CURRENT DATA TO DISK... mysqldump -uroot -hlocalhost DATABASE --compatible=mysql323 --comments=FALSE --compact --add-drop-table > C:\datafile.sql echo UPLOADING DATA... mysql -uUSERNAME -pPASSWORD -hHOST DATABASE --compress --force < C:\datafile.sql echo DONE pause The dumping of the data is fine. However, the mysql command line client complains bitterly when it hits an item with an id of 0 that it already exists. This is no wonder, as it has added the element that should have an id of 0 as the value-of-the-last-inserted-entry-plus-one, hitting an error and not adding the rest of the table. Is there any way to get it to force using a zero index? Also, as an aside, any comments on tables or fields are exported by mysqldump. However, the mysql command line client doesn't seem to support them, saying that any "COMMENT" commands inside a field/table creation is invalid syntax. Anyway around that, too?

Share this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
Share on other sites
Quote:
Original post by abstractworlds
How 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 this post


Link to post
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 this post


Link to post
Share on other sites
Quote:
Original post by Sander
You 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 this post


Link to post
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.

Share this post


Link to post
Share on other sites

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

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this