- Detect valleys of a signal
- How do you interpret a pneumatic regulator symbol?
- Equation for water rower
- Is it possible to quantify & cost the ideological obstruction to climate change?
- I need a way for a NASA spaceship to be able to dock with the hatch of an alien ship
- Can an alien civilization develop without money?
- How to cause a surge in large carnivore diversity, roughly 5-7 million years hence?
- Taking control of the Human population with nanobots
- Except for baudhyana dharma sutras which other dharma sastras talk about loss of caste due to sea voyage?
- Upgraded to Craft 3 and lost my Commerce Gateways
- Falcon Circus, Chapter 2: The Wise
- Welcome to the Jungles of South America
- Zeebus Puzzle #1
- An Incredible Theory
- Homophone riddle
- Symlink or Rename files with spaces?
- Hierarchical Single-Link Clustering BGP Updates
- GAN isn't stable
- I need sources of interrogative, exclamatory, and imperative sentences
- Segmenting an Age Column
How does STRICT_ALL_TABLES work with multi-row statements and partial updates?
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,
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 an
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 dat2018-07-17 01:12:36