Mysql – How does STRICT_ALL_TABLES work with multi-row statements and partial updates

mariadbmariadb-10.1MySQL

In the docs for STRICT_ALL_TABLES it states,

For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, because the earlier rows have been inserted or updated, the result is a partial update. To avoid this, use single-row statements, which can be aborted without changing the table.

What does this mean though?

CREATE TABLE foo ( a int NOT NULL );
INSERT INTO foo VALUES (NULL), (NULL);

This does what everyone would reasonably expect a totally insane database to do,

SELECT * FROM foo;
+---+
| a |
+---+
| 0 |
| 0 |
+---+
2 rows in set (0.00 sec)

But, I expected a multi-row INSERT to insert the non-violating rows that are seen first,

SET sql_mode='STRICT_ALL_TABLES';
INSERT INTO foo VALUES (5), (NULL);

To insert just one row with 5, as per the docs in STRICT_ALL_TABLES. Instead it does,

ERROR 1048 (23000): Column 'a' cannot be null

How do I interpret the docs to STRICT_ALL_TABLES when they say earlier rows .. partial update and "single-row statements". And why is MySQL doing something less horrible then documented? I'm pleasantly surprised.

Best Answer

I agree that the split between _ALL_ and _TRANS_ needs clarification.

Earlier on that page it says

With TRADITIONAL mode enabled, an INSERT or UPDATE aborts as soon as an error occurs. If you are using a nontransactional storage engine, this may not be what you want because data changes made prior to the error may not be rolled back, resulting in a “partially done” update.

I think the meat of the issue is InnoDB versus MyISAM.

MyISAM is covered by STRICT_ALL_TABLES, but not by STRICT_TRANS_TABLES. If an UPDATE or DELETE is aborted for any reason mid-stream, some rows will have been updated or deleted. You have no practical way to clean up the mess. (This is a major reason never to use MyISAM.)

InnoDB is covered by both settings, but STRICT_TRANS_TABLES is more relevant, and overrides the statement about "earlier rows". UPDATE and DELETE are atomic.

Also involved is the question of aborting with an error versus doing something so you can keep going. For example, bad dates and defaults without an explicit DEFAULT, etc. These are cases where MySQL (unlike some other engines) defines that a non-abort action will be taken.

If your Question is intended to point out poor phrasing in the documentation, go for it. But do it at bugs.mysql.com , where there is a chance of it getting fixed.

MySQL 8.0 moves toward more robust default settings by changing the defaults.

Related Question