MySQL Bugs: #103891: Two parallel threads trigger error code '1032 Can't find record in 'table'

Bug #103891	Two parallel threads trigger error code '1032 Can't find record in 'table'
Submitted:	2 Jun 2021 14:13	Modified:	4 Jul 2021 7:42
Reporter:	Fei Li	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	MySQL5.6 and MySQL5.7.33	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
# The script can reproduce
# the `can’t find record` problem in MySQL5.6 and MySQL5.7.33

import pymysql
import _thread

# You should create this database before executing the script
db = 'testdb'

# host, port, user and password
host = 'localhost'
port = 13306
user = 'root'
password = 'root'

def connect() -> pymysql.Connection:
    return pymysql.connect(host=host,
                           port=port,
                           user=user,
                           password=password,
                           db=db)

# recreate database ${db}
# then create schema for test
def initdb():
    conn = connect()
    cur = conn.cursor()
    cur.execute(f'drop table if exists t;')
    cur.execute('create table t(pkAttr0 integer, coAttr1 varchar(100), \
                coAttr2 varchar(100), coAttr3 varchar(100), coAttr4 integer, \
                coAttr5 varchar(100), coAttr6 varchar(100), primary key(pkAttr0));')

def select():
    conn = connect()
    cur = conn.cursor()
    cur.execute('set session transaction isolation level read uncommitted')
    conn.autocommit(False)
    while True:
        #Sometimes, this select triggers error：pymysql.err.0perationalError: (1032,"Can't find record in 't'")
        cur.execute('select pkAttr0, coAttr1, coAttr2, coAttr3, coAttr4, coAttr5, coAttr6 \
            from t order by pkAttr0, coAttr1, coAttr2, coAttr3, coAttr4, coAttr5, coAttr6;')
        print(cur.fetchall())
        conn.commit()

def change():
    conn = connect()
    cur = conn.cursor()
    cur.execute('set session transaction isolation level read uncommitted')
    conn.autocommit(False)

    while True:
        cur.execute('delete from t where pkAttr0 = 1;')
        cur.execute('insert into t(pkAttr0, coAttr1, coAttr2, coAttr3, coAttr4, coAttr5, coAttr6) \
            values("1", "varchar1", "varchar2", "varchar3", "2021", "varchar5", "varchar6");')
        # The problem can be reproduced whether or not to commit the transaction
        # conn.commit()

initdb()

_thread.start_new_thread(select, ())
_thread.start_new_thread(change, ())

while True:
    pass

How to repeat:
Run the above Python script to reproduce the bug.

Suggested fix:
The expected result should not throw '1032' error.

Hi Mr. Li,

Thank you for your bug report.

However, we can not accept a test case in this form, since we are not testing Python , but only our own software.

Hence, we need structure and contents of all the tables involved in these two transactions. Next, we require a set of SQL queries in each of the transactions , that would lead to the error code that you report. We shall run them in parallel, in our own environment.

We are waiting on your feedback.

Hi,

We must also point out that what you describe is absolutely expected behaviour when you use isolation level of READ_UNCOMMITTED. As our Manual says , it can lead to the unexpected results, so let us also know whether you also get this error with any other isolation level.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Verified with a psuedorandom testcase on 5.7.36.

drop table if exists t;
create table t(pkattr0 integer, coattr1 varchar(100), coattr2 varchar(100), coattr3 varchar(100), coattr4 integer, coattr5 varchar(100), coattr6 varchar(100), primary key(pkattr0));

drop procedure if exists p;
delimiter $
create procedure p()
begin
 
  declare done int default false;
  declare v_pkattr0 integer;
  declare v_coattr1 varchar(100);
  declare v_coattr2 varchar(100);
  declare v_coattr3 varchar(100);
  declare v_coattr4 varchar(100);
  declare v_coattr5 varchar(100);
  declare v_coattr6 varchar(100);
  declare cur1 cursor for select pkattr0, coattr1, coattr2, coattr3, coattr4, coattr5, coattr6 from t order by pkattr0, coattr1, coattr2, coattr3, coattr4, coattr5, coattr6;
  -- declare continue handler for sqlexception begin end;
  declare continue handler for not found set done = true;
  repeat
     set session transaction isolation level read uncommitted;
     if rand()<0.5 then start transaction; end if;
     open cur1;
      read_loop: loop
        fetch cur1 into v_pkattr0,v_coattr1,v_coattr2,v_coattr3,v_coattr4,v_coattr5,v_coattr6;
        if done then
          leave read_loop;
        end if;
      end loop;
     close cur1;
     if rand()<0.5 then commit; end if;
     if rand()<0.5 then start transaction; end if;
     if rand()<0.5 then delete from t where pkattr0 = 1; end if;
     if rand()<0.5 then commit; end if;
     set session transaction isolation level read uncommitted;
     if rand()<0.5 then start transaction; end if;
     if rand()<0.5 then replace into t(pkattr0, coattr1, coattr2, coattr3, coattr4, coattr5, coattr6) values("1", "varchar1", "varchar2", "varchar3", "2021", "varchar5", "varchar6"); end if;
     if rand()<0.5 then commit; end if;
     
  until 1=2 end repeat;
  

end $

delimiter ;

call p();   -- in multiple threads

On launching second call p(),  first one quickly dies with:

mysql> call p();   -- in multiple threads
ERROR 1032 (HY000): Can't find record in 't'

Current 8.0 was not affected.