Wednesday 26 May 2010

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)

6 comments:

Stewart Smith said...

It is in Drizzle. In fact, we're strict by default and you can't turn it off.

Anonymous said...

Hi!

A small correction, there is no "strict by default". We just don't allow this operation, period. There are no modes.

Cheers,
-Brian

Unknown said...
This comment has been removed by a blog administrator.
Anonymous said...

Is that a MyISAM table, or a InnoDB table? Since InnoDB is transactional, it may handle things correctly.

But then again, at this point there is nearly no reason to use MyISAM.

Mark Robson said...

The only difference between MyISAM and Innodb in this case is that if a multi-row insert has a failure (which generates an error, e.g. unique key violation), InnoDB will roll back the partially done insert.

However, the "null is only an error on the first row" crapness happens with both. It's not part of the engine.

hafizan aziz said...

For a novice you cannot understand how to manipulate both side.It's the best .Why

$sql="update j where n=1"; // this is innodb
$sql="update log set sql=$sql"; // this is myisam
if error die
If you're in transaction logging error wouldn't insert to db.The point you can manipulate before edit value field before and after as audit column field and make the programmer more usefull to understand what sql error they make on response.