OS: Ubuntu-20.04 LTS

Yes, INSERT IGNORE is supposed to ‘fix’ errors by replacing garbage with some other garbage. It is working as designed when it replaces the NULL values with the implicit default value of the NOT NULL column, in this case the empty string.

Here is an mtr version of the test, replacing the INSERT IGNORE with an equivalent plain INSERT. By the way, MDEV-27025 or MDEV-27992 is not playing any role here:

--source include/have_innodb.inc

CREATE TABLE t(c1 BLOB NOT NULL, c2 TEXT) ENGINE=InnoDB;

INSERT INTO t VALUES ('', NULL), ('', 'aaa');

SELECT * FROM t;

BEGIN;

--error ER_TRUNCATED_WRONG_VALUE

UPDATE t SET c2='test' WHERE c1;

connect con2,localhost,root;

send UPDATE t SET c2='def';

connection default;

let $wait_condition=

    select count(*) = 1 from information_schema.processlist

    where state = "Updating" and info like "UPDATE t SET%";

--source include/wait_condition.inc

COMMIT;

connection con2;

reap;

disconnect con2;

connection default;

SELECT * FROM t;

DROP TABLE t;

The error message confused me at first. But, it turns out that WHERE apparently expects a DOUBLE expression here, or something that can ultimately be interpreted as Boolean. The error message is about converting the value '' of the column c1 to a numeric expression. I would guess that in a non-strict sql_mode, the empty string should be equivalent to 0, which in turn is equivalent to false. The MariaDB data type system does not include a Boolean data type.

I think that this is working as designed. The error during the UPDATE execution is not a fatal one that would force the transaction to be aborted. The transaction will continue to hold some locks. Examples of errors that cause a transaction abort would be a deadlock, and in some cases, a lock wait timeout.

If you think that something should be fixed, please describe it in more detail. I do not defend the current design. I think that we must avoid changes that could break compatibility with existing applications. I guess that it is the reason why MariaDB has retained the INSERT IGNORE syntax.

John Jove, I imagine that you could want an sql_mode where some errors would lead to transaction rollback. Is that what you want?

We already have a parameter innodb_rollback_on_timeout that is disabled by default. Its documentation implies that MySQL 4.0 or 4.1 rolled back transactions on timeout. I can imagine that some user might want their transaction to be able to go through even if a timeout occurs while executing some step. That timing-out statement could perhaps be resubmitted. But, perhaps we would not need that much granularity and could indeed have an sql_mode where any error (including lock wait timeouts) will lead to transaction rollback.

Thank you for youre explanation. I understand that it is a design.

I am curious that under READ COMMITTED and READ UNCOMMITTED, an UPDATE that reports error does not release the lock it holds. But an UPDATE where the WHERE clause is evaluated to FALSE releases th lock.

READ COMMITTED and READ UNCOMMITTED enable so-called "semi-consistent read", which I implemented in MySQL Bug #3300. Any non-matching rows would be unlocked in that mode. I suppose that when an error occurs while evaluating the WHERE condition, the virtual member function handler::unlock_row() will not be called. I do not remember and did not check if it also applies to DELETE, or only UPDATE.

The isolation levels READ COMMITTED and READ UNCOMMITTED also disable some gap locking, like the removed (MDEV-19544) parameter innodb_locks_unsafe_for_binlog used to do.

I think that this is best addressed in the documentation.