MySQL Bugs: #10760: mysql_stmt_attr_set fetch after rollback

Bug #10760	mysql_stmt_attr_set fetch after rollback
Submitted:	20 May 2005 12:19	Modified:	29 Jul 2005 20:15
Reporter:	Berto van de Kraats
Status:	Closed
Category:	Server	Severity:	S1 (Critical)
Version:	5.0.6-beta-nightly-20050515-debug	OS:	Linux (linux/windows)
Assigned to:	Konstantin Osipov	Target Version:

Description:
if cursors are used mysql crashes if after a rollback a fetch is done

How to repeat:
compile and run the simple c-program

bug10.c

Attachment: bug10.c (application/octet-stream, text), 3.72 KiB.

Thank you for the bug report.

lock0lock.c
--489--

/* NOTE that we call this function while holding the search
	system latch. To obey the latching order we must NOT reserve the
	kernel mutex here! */

	trx_id = row_get_rec_trx_id(rec, index, offsets);
	
	if (read_view_sees_trx_id(view, trx_id)) {
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

		return(TRUE);
	}^

CALL STACK ON WINDOWS:

 	mysqld-debug.exe!read_view_sees_trx_id(read_view_struct * view=0x00000000, dulint_struct
trx_id={...})  Line 53 + 0x3	C
>	mysqld-debug.exe!lock_clust_rec_cons_read_sees(unsigned char * rec=0x01e64093,
dict_index_struct * index=0x01d35da0, const unsigned long * offsets=0x03eef870,
read_view_struct * view=0x00000000)  Line 495 + 0x11	C
 	mysqld-debug.exe!row_search_for_mysql(unsigned char * buf=0x00f3fba0, unsigned long
mode=1, row_prebuilt_struct * prebuilt=0x01d354a0, unsigned long match_mode=0, unsigned
long direction=1)  Line 3736 + 0x31	C
 	mysqld-debug.exe!ha_innobase::general_fetch(unsigned char * buf=0x00f3fba0, unsigned int
direction=1, unsigned int match_mode=0)  Line 3885 + 0x17	C++
 	mysqld-debug.exe!ha_innobase::index_next(unsigned char * buf=0x00f3fba0)  Line 3923	C++
 	mysqld-debug.exe!join_read_next(st_read_record * info=0x02fe80f8)  Line 9815 + 0x18	C++
 	mysqld-debug.exe!sub_select(JOIN * join=0x02fe7048, st_join_table * join_tab=0x02fe80b8,
int end_of_records=0)  Line 9182 + 0xa	C++
 	mysqld-debug.exe!Cursor::fetch(unsigned long num_rows=1)  Line 1845 + 0x12	C++
 	mysqld-debug.exe!mysql_stmt_fetch(THD * thd=0x02ff0688, char * packet=0x02fe2fd9,
unsigned int packet_length=9)  Line 2234	C++
 	mysqld-debug.exe!dispatch_command(enum_server_command command=COM_FETCH, THD *
thd=0x02ff0688, char * packet=0x02fe2fd9, unsigned int packet_length=9)  Line 1621 +
0x11	C++
 	mysqld-debug.exe!do_command(THD * thd=0x02ff0688)  Line 1454 + 0x31	C++
 	mysqld-debug.exe!handle_one_connection(void * arg=0x02ff0688)  Line 1114 + 0x9	C++
 	mysqld-debug.exe!pthread_start(void * param=0x00e4bf68)  Line 63 + 0x7	C
 	mysqld-debug.exe!_threadstart(void * ptd=0x02ff55c8)  Line 173 + 0xd	C
 	kernel32.dll!7c80b50b() 	
 	kernel32.dll!7c8399f3()

The bug is no longer repeatable:
kostja@dragonfly:~> ./a.out
10
mysql_rollback(), succeeded
20
kostja@dragonfly:~> ./a.out
10
mysql_rollback(), succeeded
20

However, the problem is there, and I'm writing a patch for it.
For now mysql_rollback will simply close all cursors that use InnoDB tables (MyISAM
tables
should be fine, as they don't have per-transaction state).

Hi Konstantin,

Maybe this remark is a bit superfluous, but mysql_rollback closing all cursors is not the
behavior we expect. We expect that after a rollback all cursors remain open and we can
continue to fetch from them.

Kind regards, Berto

Understood. However, not all storage engines in MySQL have support for this.
For example, NDB (the cluster storage engine), although is transactional, has no support
for
consistent read views. So the safest approach for NDB is to close all cursors that use NDB
tables
at COMMIT or ROLLBACK.
For MyISAM there is no transaction-specific state, so COMMIT or ROLLBACK have no
influence
on cursors (but there is a nasty deadlock problem there).
For InnoDB a cursor must open a consistent read view when created, and once it's done, 
close the view. However, this functionality is not in place yet, so how exactly cursors 
currently work after COMMIT/ROLLBACK is a puzzle.
I should implement a proper support for InnoDB tables, maybe along with the main patch,
maybe a week or two later (but it's planned for 5.0). For now I have 2 options for the
main patch:
 - close cursors to InnoDB tables at COMMIT (the safe one)
 - leave everything as is and wait for a bug report for the problem (if there is such
problem).
Please let me know what you think.

Update: I was actually able to repeat the bug (I had been running the server with
--skip-innodb option :))

Hi Konstantin,

Actually, our applications require that all cursors remain open after a COMMIT and they
can continue to fetch from the cursors. So, I would opt for the second proposal: leave
everything as is and wait for a bug report for the problem (if there is such problem).

Berto

Well, as I have just re-verified this bug, there is such problem :(
I only can tell now that I'll try to fix it ASAP, but not until the next week, when most
of the InnoDB team is back from vacation.

Subject: bk commit - 5.0 tree (konstantin:1.1890) BUG#10760

ChangeSet
  1.1890 05/07/08 22:34:51 konstantin@mysql.com +28 -0
  A fix and a test case for Bug#10760 and complementary cleanups.
  (see comments to the changed files).

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/26831

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/27316

Fixed in 5.0 tree, currently tagged 5.0.10

Documented in 5.0.10 changelog:

<listitem><para>MySQL server would crash is a fetch was performed after a
<literal>ROLLBACK</literal> when cursors were involved. (Bug #10760)</para></listitem>