- How do I pass a file as an argument in bash scripting?
- Tmux binding error `returned 127` in cygwin
- RHEL 7: Add PATH variables globally
- My Debian install gives me black screen on start up - it's a quick fix but it happens every time
- How do I compare an argument's size to an arbitrary number of megabytes?
- is this an allusion and a metaphor?
- What is the origin of “sink a drink”?
- What sort of phrases cannot be restrictive or non-restrictive?
- Unvoiced /dʒ/ and /ʒ/ in word final position
- Word for opposite of *prerequisite*? Something that is possible because of another thing?
- A word to describe someone who is bad at handling their emotions?
- Is it he and [name], or am I still wrong?
- What does the word “health” mean in this context?
- REITs - Traditional Account or Roth
- Value of a call option spread
- Can I convert spread option into regular call or put?
- Can the bid price for selling the same call option in calendar spread be different from that in covered call?
- Clarification on options jargon regarding spreads
- How was the media able to identify that certain VIX put and calls were made by the same entity?
- hide image with animate package
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