Bug #1574 Can't find record in 'tablename' on select (4.0.14+)
Submitted: 16 Oct 2003 8:54 Modified: 16 Mar 2013 19:52
Reporter: Stewart Witchalls Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:4.0.14+ OS:Windows (WinNT)
Assigned to: Heikki Tuuri CPU Architecture:Any

[16 Oct 2003 8:54] Stewart Witchalls
Description:
Randomly getting:

  General error,  message from server: "Can't find record in '<table>'"

Table is being inserted into and deleted from while select takes place (all on different transactions).

Statement:
  SELECT tid, tdate, tblob
  FROM t
  WHERE tid = 2
  AND tdate <= {ts '2003-10-16 16:45:17.890'}
  ORDER BY tdate ASC
  LIMIT 100

Isolation level = read-uncommitted (although read-committed still gives errors)
innodb_lock_wait_timeout = 0 (although 1 still gives errors)
Using InnoDB tables

How to repeat:
Run select every 5 seconds or more while inserting new rows and deleting current ones every 1 second.
[16 Oct 2003 9:10] Stewart Witchalls
Forgot to mention -

Table simulates an event Q. It is initially created with some 250 entries (single transaction to kick off test). As each entry is processed, others will be added and existing ones deleted.

After an error, the enclosing transaction is rolled back and the select run again after a short (< 1 sec) delay.

Although the table contains BLOBS, these are not retrieved in the select that is failing. I put them in the bug report to indicate their existence. Sorry for misleading you.

12 random errors in first 4 mins. No errors in 30 next mins.
[16 Oct 2003 9:27] Heikki Tuuri
Matt,

are you sure you are getting the errors also with the READ COMMITTED isolation level?

On the READ UNCOMMITTED level the behavior is expected, though must be seen as a bug: while MySQL is sorting the result set in the ORDER BY, a rollback can remove a row in the result set, and when MySQL after sorting retrieves the row using the primary key, it cannot find it. I have to consider suppressing the error in this case.

Regards,

Heikki
[17 Oct 2003 0:57] Stewart Witchalls
Yep. First thing I did was to turn off the read-uncomitted. The error was less frequent, but still occurred.
[17 Oct 2003 3:00] Stewart Witchalls
The problem is the ORDER BY. Take it off and no errors
[10 Nov 2003 11:06] Julio Benitez
i have the same error, the error is reported by jdbc driver

java.sql.SQLException: null: Can't find record in 'personajes'
	at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:718)
	at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:272)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:978)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:1809)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1458)
	at com.xlives.horitzo.TurnoHrz.procesarPersonajes(TurnoHrz.java:478)

the line 478 of my code is 

rs = stm.executeQuery();

the operation is atomic since view point of user code.

repeat the same operation can repeat the exception indefinitely in high concurency.

in my opinion it should be considered an error.  

this is my query.

select personaje_id from personajes
        where baja = 'N';

result is a set of 12,000 records
personaje_id is the unique key.

by definition READ_UNCOMMITED can include in the results uncommited rows but i'm not sure if the sort may report an error when the readed rows disapears by deletes and rollbacks because in my opinion "read sorted" is an atomic operation.

of course in the database are two diferenciated operations.

thank you
[26 Aug 2004 8:22] MySQL Verification Team
The currently release is 4.0.20 are you tested this bug with latter releases ?

Thanks in advance.
[26 Aug 2004 9:02] Stewart Witchalls
Nope. Only tested against 4.0.14. Awaiting you guys to say the problem has been fixed before I try testing again.
[11 Mar 2005 21:10] Heikki Tuuri
Stewart,

it may be that this bug has been fixed. MySQL used to release table locks in ::external_lock too early in some complex queries. That would cause InnoDB to look at the rows using a NEW snapshot after the sort, and can explain the bug in READ COMMITTED. 

Can you repeat the problem with 4.1.10?

Best regards,

Heikki
[13 Apr 2005 23:00] Bugs System
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".
[3 Jun 2012 6:35] Yaniv Cohen
I am using MySql 5.5 and I am still getting this bug 

http://stackoverflow.com/questions/10833742/mysql-throws-exception-when-update-statement-d...
[16 Mar 2013 19:52] MySQL Verification Team
this is a 9 years old bug report, it's safe to say the cause is not the same. if you can provide full details (description, table, queries, testcase?) in a new bug when the error happens, that would be great.
[11 Sep 2015 2:52] Marcos Albe
I can still repeat in 5.5:

mysql> SET GLOBAL tx_isolation='READ-UNCOMMITTED';
mysql> CREATE TABLE t (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lastupdateon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB 

Then ran the following loops in separate linux sessions:

# fill in table; I had 2 of these
shell> while true; do { i=`/usr/bin/shuf -i1-100000 -n1`; mysql test -e "replace into t (id) VALUES ($i)"; } done;

# run some of these delete threads; I had 3 of these concurrent
shell> while true; do { i=`/usr/bin/shuf -i1-100000 -n1`; mysql test -e "delete from t where id=$i"; } done;

# after a short while this will fail
shell> while true; do { mysql test -BNe "select id,lastupdateon from (SELECT * FROM t ORDER BY lastupdateon) as _t ORDER BY lastupdateon LIMIT 1"; sleep 0.01; } done;

# running this will make the issue disappear instantly.
mysql> SET GLOBAL tx_isolation='READ-COMMITTED';