MySQL, what are you smoking?

There are a lot of weird things which MySQL does to handle its mix of transactional and non-transactional behaviour, but this one was new to me :)

create table t1 (ID INT NOT NULL PRIMARY KEY, V INT NOT NULL);

Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 (ID,V) VALUES (2,NULL);
ERROR 1048 (23000): Column 'V' cannot be null

mysql> insert into t1 (ID,V) VALUES (3,1),(4,1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

insert into t1 (ID,V) VALUES (5,1),(6,NULL);
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+---------------------------+
| Level   | Code | Message                   |
+---------+------+---------------------------+
| Warning | 1048 | Column 'V' cannot be null |
+---------+------+---------------------------+
1 row in set (0.00 sec)

select * from t1;

+----+---+
| ID | V |
+----+---+
|  3 | 1 |
|  4 | 1 |
|  5 | 1 |
|  6 | 0 |
+----+---+
4 rows in set (0.00 sec)

What's going on?
MySQL does not consider (some) errors to be errors, if they happen on the second or subsequent row of a multi-row insert.

On the other hand, if it happens on the first row, it's an error.

Why?

Because non-transactional engines can't rollback to a savepoint. This means that if it's inserted one or more rows already, to generate an error would leave some stuff in the database.

No, really why?

I don't know. This is not consistent with, for example, a unique index violation, which makes it stop half way through a multi-row insert anyway, and non-transactional engines can't rollback.

So if you insert a duplicate, THAT still generates an error on the second a subsequent row. It's not even consistent!

mysql> insert into t1 (ID,V) VALUES (10,1),(10,2);
ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'
mysql> select * from t1;
+----+---+
| ID | V |
+----+---+
|  3 | 1 |
|  4 | 1 |
|  5 | 1 |
|  6 | 0 |
| 10 | 1 |
+----+---+
5 rows in set (0.00 sec)

Of course if we use a transactional engine, it looks better:

mysql> ALTER TABLE t1 ENGINE=InnoDB;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into t1 (ID,V) VALUES (20,1),(20,2);
ERROR 1062 (23000): Duplicate entry '20' for key 'PRIMARY'
mysql> select * from t1 WHERE ID=20;
Empty set (0.00 sec)

But that won't change the behaviour of a multi-row insert with NULLs in invalid places.

This kind of stuff is nonsense and we need it to GO AWAY NOW.

How?

SET SQL_MODE='STRICT_ALL_TABLES'

And now every error is really an error. Yay! Why can't this be the default? (I know the answer; this is a rhetorical question)