CODEWITHSUNDEEP
mysqlmariadb
Michael 'Maik' Ardan
Michael 'Maik' Ardan

Reputation: 4393

MySQL DEFAULT vs. MariaDB DEFAULT

I have table_a with and auto_increment column named id and string column named name.

Running the statement: INSERT INTO table_a(id, name)VALUES(DEFAULT, 'test');

Results to (MySQL):

+----+------+
| id | name |
+----+------|
| 1  | test |
+----+------+

Running the similar statement in MariaDB results to:

+----+------+
| id | name |
+----+------|
| 0  | test |
+----+------+

Other scenario:

I tried editing the AUTO_INCREMENT value of the table to 30. MySQL inserts 30 while MariaDB inserts 0.

What is the difference of DEFAULT value in INSERT statement of MySQL and MariaDB? Is this a bug in MariaDB or it is working as intended?

Upvotes: 1

Views: 1154

Answers (1)

elenst
elenst

Reputation: 3987

This behavior is controlled by SQL_MODE='NO_AUTO_VALUE_ON_ZERO', both in MySQL and MariaDB. If you observe the difference, it's most likely because you have different sql_mode on the instances.

MariaDB [test]> CREATE TABLE t (id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.20 sec)

MariaDB [test]> SET SQL_MODE='';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO t (id) VALUES (DEFAULT);
Query OK, 1 row affected (0.05 sec)

MariaDB [test]> SELECT * FROM t;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

MariaDB [test]> DROP TABLE t;
Query OK, 0 rows affected (0.14 sec)

MariaDB [test]> CREATE TABLE t (id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.30 sec)

MariaDB [test]> SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO t (id) VALUES (DEFAULT);
Query OK, 1 row affected (0.03 sec)

MariaDB [test]> SELECT * FROM t;
+----+
| id |
+----+
|  0 |
+----+
1 row in set (0.00 sec)

Upvotes: 2

Related Questions