Auto Increment reset to 0, but cannot insert value with id=0. Does not happen for values >0

Question

I've just stumbled on a very weird behaviour:

Imagine we have a table customers:

MariaDB [connections]> describe customers;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| customerId   | int(11)     | NO   | PRI | NULL    | auto_increment |
| customerName | varchar(50) | NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Insert a couple of values:

insert into customers(customerName) values('Foo');
insert into customers(customerName) values('Bar');

Then delete everything and reset the auto increment:

DELETE FROM customers;
ALTER TABLE customers AUTO_INCREMENT = 0;

Now, insert a new value with customerId=0:

INSERT INTO customers(customerId,customerName) VALUES(0,'Site owner');

And see the result:

MariaDB [connections]> select * from customers;
+------------+--------------+
| customerId | customerName |
+------------+--------------+
|          1 | Site owner   |
+------------+--------------+
1 row in set (0.00 sec)

customerId is set to 1!!!!

Repeat the same procedure but reset to 5 and insert 5, everything is OK:

MariaDB [connections]> delete from customers;
Query OK, 1 row affected (0.00 sec)

MariaDB [connections]> ALTER TABLE customers AUTO_INCREMENT = 5;
Query OK, 0 rows affected (0.00 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [connections]> INSERT INTO customers(customerId,customerName) VALUES(5,'Site owner');
Query OK, 1 row affected (0.00 sec)

MariaDB [connections]> select * from customers;
+------------+--------------+
| customerId | customerName |
+------------+--------------+
|          5 | Site owner   |
+------------+--------------+
1 row in set (0.00 sec)

What is going on here? How can I insert the value '0' with insert values? (Yes, I can edit afterwards but for various reasons it is not practical for my case).

Thanks!

Gonick

I can immediately tell you one thing, you can believe me or just drop it, forget about it and remember what I told you when it comes to bite you in the ass (and it will). NEVER rely on auto_increment to provide numbers that are meaningful to you. Auto_increment isn't made so we can use it for sequential numbering. It's got one purpose and one only - to uniquely identify a row. That means mysql can discard some numbers (when inserts fail), it can offset them, it can use negative values etc. Just let it be, if you need some "special" numbers - add another column or something like that. – Sheba

Answer 1

I've referred the answer from the link

You can use:

SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO'

Which as described here, will prevent MySQL from interpreting an INSERT/UPDATE ID of 0 as being the next sequence ID. Such behaviour will be limited to NULL.

It is what I'd consider pretty bad behaviour from the application though. You'll have to be real careful that it's used consistently, especially if you choose to implement replication at a later date.

Mary

This does work from the command line but it does not using mysqli's $link->query(). Am I missing someting? – Gonick

Answer 2

After the INSERT INTO Take this last record and use UPDATE id = 0;

Battology

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Nay

Answer 3

This is impossible.

0 is reserved for auto_increment field. When you insert to auto increment row 0 or null then mysql insert record with current auto increment value.

Mannie