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: | |
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
[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';