MySQL Bugs: #10760: mysql_stmt_attr_set fetch after rollback

Bug #10760	mysql_stmt_attr_set fetch after rollback
Submitted:	20 May 2005 10:19	Modified:	29 Jul 2005 18:15
Reporter:	Berto van de Kraats	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.6-beta-nightly-20050515-debug	OS:	Linux (linux/windows)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

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>