MySQL Bugs: #12456: Cursor shows incorrect data - DML does not affect, probably caching

Bug #12456	Cursor shows incorrect data - DML does not affect, probably caching
Submitted:	9 Aug 2005 12:45	Modified:	5 Jul 2006 17:10
Reporter:	Andrey Hristov
Status:	To be fixed later
Category:	Server: InnoDB	Severity:	S1 (Critical)
Version:	5.0.12-20050809	OS:	Linux (SLES 9)
Assigned to:	Heikki Tuuri	Target Version:

Description:
A cursor (PS) shows a consistent view of the transaction after it has started. DML does
not affect this view, though it should.
Scenario:
- commit
- create table
- dump the table with a cursor
- insert into the table
- dump the table with a cursor

During the second dump the data inserted will not be shown. In fact no data will be
fetched. The dump every time creates a new cursor (new PS) and do not reuse an old one. 

How to repeat:
See the scenario above and use the attached repro case.

a repro case

Attachment: bug12456.c (text/x-csrc), 1.38 KiB.

The example has a bug, during the second dump 1 row is expected, not 2

Hi!

a quick fix is to make the cursor to see the changes of THIS transaction.

A more complete fix is to implement a 'high-granularity semi-consistent read view'. 

Regards,

Heikki

The quick fix should be in place August 24, 2005.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28433

Jan,

ok to push your patch.

Regards,

Heikki

Hi Jan, Heikki,
I have applied the patch and the test case still fails, any clue?

Additionaly I get this:

InnoDB: Error: MySQL is trying to perform a SELECT
InnoDB: but it has not locked any tables in ::external_lock()!
TRANSACTION 0 2347110, ACTIVE 0 sec, process no 9040, OS thread id 1093663072
1 lock struct(s), heap size 368, undo log entries 2
MySQL thread id 381, query id 183124 10.18.202.4 root
SELECT * FROM bad_cursor_read

InnoDB: Error: MySQL is trying to perform a SELECT
InnoDB: but it has not locked any tables in ::external_lock()!
TRANSACTION 0 2347110, ACTIVE 0 sec, process no 9040, OS thread id 1093663072
2 lock struct(s), heap size 368, undo log entries 3
MySQL thread id 381, query id 183129 10.18.202.4 root
SELECT * FROM bad_cursor_read

Ok, with clean recompile of patched tree the patch works!
Thanks for the help!!!

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28511

Andrey,

I have one minute ago pushed a patch that also removes the error print about the wrong
lock count. We must return the error print when we have also a lock count of cursors used
by the thd. Then we should have:

trx->n_mysql_tables_in_use + 'the number of table locks by open cursors of this thd' > 0

if MySQL is trying to SELECT from a table.

We have now done a quick fix of this bug. I am leaving the bug report to the state 'In
Progress', because we must implement a 'high-granularity read view' to get the behavior
Konstantin wants. And we must fix the lock counting of open cursors.

Regards,

Heikki

Partially fixed in 5.0.12.

I have been working on this issue and now I have my first version to fix this problem. To
make sure that my fix works as expected I need to run several test cases. Test case
presented here is it correct. In the first dump you expect to get 2 rows, right, but in
the second run you should get 1 row not 2 as it is in the test ?

Test case for primary key search and secondary search

Attachment: high_granularity_view.c (text/x-csrc), 2.22 KiB.

Hi Jan,
----
[11 Aug 11:41] Andrey Hristov

The example has a bug, during the second dump 1 row is expected, not 2
----

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29150

Note that this patch implements high-granularity consistent read view for cursors. We need
to still fix lock counting on cursors. Thus, I leave this bug to in progress status.

Regards,
    JanL

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29163

This bug will be fixed on 5.1.x.

'Patch pending' describes this bug report more accurately, since Konstantin must
coordinate this.