Skip to content

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.


----- cut -----

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)
----- cut -----


So whats going on here?


Since 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, so 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.

  • Twitter
  • Bookmark Pitfalls of missing transactions when handling multiple inserts in Mysql at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Pitfalls of missing transactions when handling multiple inserts in Mysql
  • Bloglines Pitfalls of missing transactions when handling multiple inserts in Mysql
  • Technorati Pitfalls of missing transactions when handling multiple inserts in Mysql
  • Fark this: Pitfalls of missing transactions when handling multiple inserts in Mysql
  • Bookmark Pitfalls of missing transactions when handling multiple inserts in Mysql at YahooMyWeb
  • Bookmark Pitfalls of missing transactions when handling multiple inserts in Mysql at Furl.net
  • Bookmark Pitfalls of missing transactions when handling multiple inserts in Mysql at reddit.com
  • Bookmark Pitfalls of missing transactions when handling multiple inserts in Mysql at blinklist.com
  • Bookmark Pitfalls of missing transactions when handling multiple inserts in Mysql at Spurl.net
  • Bookmark Pitfalls of missing transactions when handling multiple inserts in Mysql at Simpy.com
  • Bookmark Pitfalls of missing transactions when handling multiple inserts in Mysql at blogmarks
  • Bookmark Pitfalls of missing transactions when handling multiple inserts in Mysql with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Trackbacks

No Trackbacks

Comments

Display comments as Linear | Threaded

LenZ on :

I am not actually sure if this is really related to transactions in any way, but I agree this behaviour is a bit inconsistent. Note that you received two warnings during the multi-column insert that you could have evaluated: lenz@localhost:test > INSERT INTO notnulltest (id, test) VALUES (1, NULL), (2, NULL); Query OK, 2 rows affected, 2 warnings (0.02 sec) Records: 2 Duplicates: 0 Warnings: 2 lenz@localhost:test > SHOW WARNINGS; +---------+------+-------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------------+ | Warning | 1263 | Column was set to data type implicit default; NULL supplied for NOT NULL column 'test' at row 1 | | Warning | 1263 | Column was set to data type implicit default; NULL supplied for NOT NULL column 'test' at row 2 | +---------+------+-------------------------------------------------------------------------------------------------+ In MySQL 5.0, you can actually configure the server to throw an error instead - this is documented here: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html By enabling it, the insert is not performed: lenz@localhost:test > SET sql_mode = 'TRADITIONAL'; Query OK, 0 rows affected (0.09 sec) lenz@localhost:test > INSERT INTO notnulltest (id, test) VALUES (1, NULL), (2, NULL); ERROR 1263 (22004): Column was set to data type implicit default; NULL supplied for NOT NULL column 'test' at row 1 You can configure the server to use this mode by default, see the documentation. But as it could potentially break existing applications, it's not enabled in the default configuration.
Comments ()

Andreas Scherbaum on :

Hello Lenz, thanks for this update. I know, this behavior is in the documentation, but most people will miss this, because the "CREATE TABLE" site is very long and had a lot of additional infos. This one is just a small note somewhere in the middle. What me disturbs is the fact, that this is still the default case, only people who know about this can work around. And to be true: i don't know an application that will evaluate warnings. The common way is to check for errors, warnings will be ignored.
Comments ()

LenZ on :

Oh, one more thing: the behaviour you observed is documented in the manual as a known "gotcha": http://dev.mysql.com/doc/refman/5.0/en/constraint-invalid-data.html If you try to store NULL into a column that doesn't take NULL values, an error occurs for single-row INSERT statements. For multiple-row INSERT statements or for INSERT INTO ... SELECT statements, MySQL Server stores the implicit default value for the column data type. In general, this is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types.
Comments ()

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.
Form options