Got error 123 when reading table with innodb_snapshot_isolation=ON

I'm not assigning it to InnoDB yet, because we already discussed the appearance of a related error "Record has changed since last read in table" in the log, and it was said to be expected, in the same way as ER_LOCK_DEADLOCK etc are written. So I guess the verdict about this one will be the same.

I don't think it's necessarily a great idea to have all these errors in the log under the default log level, but since users don't complain about ER_LOCK_DEADLOCK and ER_LOCK_WAIT_TIMEOUT, maybe ER_CHECKREAD won't bother them either. It remains to be seen, since in 11.6 innodb_snapshot_isolation has become default and the errors will show up in logs more.

In the case below however, it's not even "Record has changed since last read in table" which is at least understandable and correlates with what is returned in the client. Instead, it is

[ERROR] Got error 123 when reading table './test/t'

It is cryptic, and when is deciphered by perror, it says

MariaDB error code 123: Someone has changed the row since it was read (even though the table was locked to prevent it)

That is, not only is it circumstantial as in case of ER_CHECKREAD, but also it claims that it shouldn't be happening ("even though..."), so it looks like a real problem.

If it's really a locking flaw, it should probably be fixed; otherwise, maybe it makes sense to re-phrase the part in the brackets.

--source include/have_innodb.inc

SET innodb_snapshot_isolation= ON;

CREATE TABLE t (a INT, b INT, KEY (b)) ENGINE=InnoDB;

INSERT INTO t VALUES (1,10),(2,20),(3,30);

--connection default

START TRANSACTION;

SELECT * FROM t;

--connect (con12_0,localhost,root,,)

START TRANSACTION;

UPDATE t SET b = 2;

--connection default

--error ER_CHECKREAD

INSERT INTO t (a) SELECT 1 FROM t;

SHOW WARNINGS;

--connection con12_0

COMMIT;

--error 1

--exec grep "Got error 123" $MYSQLTEST_VARDIR/log/mysqld.1.err

# Cleanup

--connection default

DROP TABLE t;

2024-11-22 18:00:44 4 [ERROR] Got error 123 when reading table './test/t'

Note that I had to add grep in the test case, because MTR suppresses all "Got error xxx when reading table" globally in include/mtr_warnings.sql. The test fails when it finds the error in the log, and doesn't fail otherwise.