MySql changed ID value

matjaz

i have a table with couple of fields. one of them is ID, set to autonumber, works well... almost.

i wanted to keep ID==0 for anonymous user, ok, after some regular users signed up, i went and modified the table, set ID to 0 for my anonymous user.

but later the same day i noticed that the ID has changed! suddenly anonymous user had ID=304?!?? i changed it back to 0... but after some new users came.... that 0 changed to 313... and so on.

my question is: is there a way to "force" mysql not to touch this 0 user? why does mysql keep changing that value if it is set to 0? i dont think it is smart to modify ids just like that...

Sxooter

Are you sure it was 0 to start with? This may be some issue with the fact that MySQL has some pretty brain dead code handling autoincrements. Have you tried using innodb tables for this? Heikki seems to be using a completely different algo for his autonumbered fields that may not be susceptable to this.

matjaz

i want it to be 0.... i want my own default user to have ID=0, thats why i modified the value to 0. and after some time (when new users join the program), that 0 is changes to 300 or 345 or 304,...

and i change it back to 0 again.... then it changes again...

i would like it to be 0 forever! but i dont know how to do it.

Sxooter

Then why are you using an autoincrement? Why no just insert them as 0, and when they go live, do an update to the table to set that field to max(field)+1?

matjaz

autoincrement is easy and works well.... except that 0. i guess i will have to leave anonymous user to have 216 or something and stop bothering with it.

it is just weird why mysql hates "0" so much...

Sxooter

MySQL uses 0 to set the autoincrement to go off. I.e.:

create table test (id int auto_increment primary key,info text);
insert into test values (0,'abc');
insert into test values (0,'abc');
select * from test;

gives:

id |info
1 | abc
2 | abc

and so does NULL.

Now, if you insert with 0, then update you can get it to change to another number.

Or you could use Postgresql, which does this the right way, with sequences and the DEFAULT keyword

matjaz

thank you, sir! 😉