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

[20 May 2005 10:19] Berto van de Kraats
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
[20 May 2005 10:20] Berto van de Kraats
bug10.c

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

[21 May 2005 2:25] MySQL Verification Team
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()
[8 Jul 2005 8:08] Konstantin Osipov
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).
[8 Jul 2005 8:17] Berto van de Kraats
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
[8 Jul 2005 8:47] Konstantin Osipov
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.
[8 Jul 2005 8:56] Konstantin Osipov
Update: I was actually able to repeat the bug (I had been running the server with --skip-innodb option :))
[8 Jul 2005 9:01] Berto van de Kraats
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
[8 Jul 2005 9:09] Konstantin Osipov
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.
[10 Jul 2005 7:05] Konstantin Osipov
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).
[11 Jul 2005 5:52] Bugs System
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
[19 Jul 2005 18:21] Bugs System
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
[19 Jul 2005 19:08] Konstantin Osipov
Fixed in 5.0 tree, currently tagged 5.0.10
[29 Jul 2005 18:15] Mike Hillyer
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>