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!

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.