Pitfalls of missing transactions when handling multiple inserts in MySQL

Imagine, you have a table and a colum is defined NOT NULL. You (and I) expect, that NULL arguments will not go into the table, but instead get rejected.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.41-log Source distribution

mysql> CREATE TABLE notnulltest (id INT, test TEXT NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO notnulltest (id, test) VALUES (1, NULL);
ERROR 1048 (23000): Column 'test' cannot be null
mysql> INSERT INTO notnulltest (id, test) VALUES (1, NULL), (2, NULL);
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 2

mysql> SELECT id, test FROM notnulltest;
+------+------+
| id   | test |
+------+------+
| 1    |      |
| 2    |      |
+------+------+
2 rows in set (0.00 sec)

mysql> SELECT id, test FROM notnulltest WHERE test IS NULL;
Empty set (0.00 sec)

So whats going on here?

Because MySQL had no transactions (in the past), it tries to avoid any error in multi-column inserts. Without transactions, the insert cannot be rolled back, therefore an error would leave an unknown number of inserted rows around and the application had to deal with the problem. For MySQL it seems a far better solution to modify your data so it fit’s the requirements.

Worth mentioning, this is the default behaviour in a recent (5.0.41) MySQL version, installed from source, with no config modifications.

Categories: [Other-Dbs]

Tags: [Mysql] [Null] [Stupid] [Transaction] [Transaktion]

Friends