create table t1 (ID INT NOT NULL PRIMARY KEY, V INT NOT NULL);What's going on?
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)
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);Of course if we use a transactional engine, it looks better:
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)
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:
It is in Drizzle. In fact, we're strict by default and you can't turn it off.
Hi!
A small correction, there is no "strict by default". We just don't allow this operation, period. There are no modes.
Cheers,
-Brian
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.
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.
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.
Post a Comment