MySQL Bugs: #11309: mysql_stmt_attr_set CURSOR_TYPE_READ

Bug #11309	mysql_stmt_attr_set CURSOR_TYPE_READ_ONLY select aggregrate server crash
Submitted:	14 Jun 2005 10:10	Modified:	10 Aug 2005 16:42
Reporter:	Berto van de Kraats	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.11-200500730	OS:	Linux (linux)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
If cursors are enabled, a query with an aggregate in the select clause causes a crash of the server after the data has been fetched.

How to repeat:
Compile and run the attached program (which is an automatically generated trace
of our mysql database driver). The problem reproduces if the program silently
runs and has exit status 0.
The trace contains a call to mysql_rollback that returns 1 because the
server crashed (search for pattern "ERROR:" in attached program).

Cpp trace file

Attachment: bug11309.cpp (text/plain), 24.60 KiB.

tested on 5.0.6 and also got sigsegv

stackdump is:

0x8099672 handle_segfault + 430
0x83409c8 pthread_sighandler + 184
0x812bd10 check_trx_exists__FP3THD + 256
0x81323d6 external_lock__11ha_innobaseP3THDi + 30
0x8095e3a unlock_external__FP3THDPP8st_tableUi + 70
0x8095ab8 mysql_unlock_tables__FP3THDP13st_mysql_lock + 56
0x80ca3f1 close_thread_tables__FP3THDbT1P8st_table + 225
0x80d7440 close__6Cursor + 164
0x80d74b8 _._6Cursor + 32
0x80f46fa _._18Prepared_statement + 38
0x8092503 delete_statement_as_hash_key + 27
0x8310875 hash_free + 49
0x808f20f _._3THD + 535
0x8099280 end_thread__FP3THDb + 64
0x80a7bf8 handle_one_connection + 1056
0x833e17c pthread_start_thread + 220
0x8367b8a thread_start + 4

I also attached into FILES .err file, it contains a lot of assertion failed in innodb engine

.err file with error description

Attachment: hnt02951.err (application/octet-stream, text), 8.43 KiB.

Hi!

Looks like MySQL first frees a connection object thd, and only AFTER that calls ::external_lock() to release locks on it. InnoDB asserts because the trx object is already freed.

This is probably a bug in MySQL query processing, not in InnoDB. Please run a debug version of mysqld inside gdb, and break the execution in trx_free. Print bt full, so that we know where MySQL decides to close the connection.

Regards,

Heikki

ha_innodb.cc:

/*********************************************************************
Frees a possible InnoDB trx object associated with the current THD. */

static int
innobase_close_connection(
/*======================*/
                        /* out: 0 or error number */
        THD*    thd)    /* in: handle to the MySQL thread of the user
                        whose resources should be free'd */
{
        trx_free_for_mysql((trx_t*)thd->ha_data[innobase_hton.slot]);
        return(0);
}

trx0trx.c:

/************************************************************************
Frees a transaction object. */

void
trx_free(
/*=====*/
        trx_t*  trx)    /* in, own: trx object */
{
#ifdef UNIV_SYNC_DEBUG
        ut_ad(mutex_own(&kernel_mutex));
#endif /* UNIV_SYNC_DEBUG */

        if (trx->declared_to_be_inside_innodb) {
                ut_print_timestamp(stderr);
                fputs(
"  InnoDB: Error: Freeing a trx which is declared to be processing\n"
"InnoDB: inside InnoDB.\n", stderr);
                trx_print(stderr, trx);
                putc('\n', stderr);
        }

        if (trx->n_mysql_tables_in_use != 0
            || trx->mysql_n_tables_locked != 0) {

                ut_print_timestamp(stderr);
                fprintf(stderr,
"  InnoDB: Error: MySQL is freeing a thd\n"
"InnoDB: though trx->n_mysql_tables_in_use is %lu\n"
"InnoDB: and trx->mysql_n_tables_locked is %lu.\n",
                        (ulong)trx->n_mysql_tables_in_use,
                        (ulong)trx->mysql_n_tables_locked);

                trx_print(stderr, trx);

                ut_print_buf(stderr, (byte*)trx, sizeof(trx_t));
        }

ha_innodb.cc:

trx_t*
check_trx_exists(
/*=============*/
                        /* out: InnoDB transaction handle */
        THD*    thd)    /* in: user thread handle */
{
        trx_t*  trx;

        ut_ad(thd == current_thd);

        trx = (trx_t*) thd->ha_data[innobase_hton.slot];

        if (trx == NULL) {
                DBUG_ASSERT(thd != NULL);
                trx = trx_allocate_for_mysql();

                trx->mysql_thd = thd;
                trx->mysql_query_str = &(thd->query);
                trx->active_trans = 0;

                /* Update the info whether we should skip XA steps that eat
                CPU time */
                trx->support_xa = (ibool)(thd->variables.innodb_support_xa);

                thd->ha_data[innobase_hton.slot] = trx;
        } else {
                if (trx->magic_n != TRX_MAGIC_N) {
                        mem_analyze_corruption((byte*)trx);

                        ut_a(0);
                }
        }

Peter,
I can't repeat the bug any more. But there is another problem mentioned in this bug report:
mysql_rollback() and mysql_commit() issued between fetches from an open cursor.
Currently there is a bug in MySQL that the above statements (as well as modification of 
the tables used in the cursor) can lead to a crash.
I'm working on it, however, there is no single bug report identifying it.
You are very welcome to come up with one (bug reports usually help development and influence priorities).

kostja@dragonfly:~> g++ -I /home/kostja/work/mysql-5.0-11172-new/include -L/home/kostja/work/mysql-5.0-11172-new/libmysql/.libs -lmysqlclient -lz -lnsl -lcrypto  bug11309.cpp  -lstdc++
kostja@dragonfly:~> ./a.out 
kostja@dragonfly:~> ./a.out
kostja@dragonfly:~> ./a.out

Hi Konstantin,

There is a bug about a crash if a fetch after a rollback is done. It was reported by us and the number is 10760.

Berto (I kind of took over the honors of Peter)

Just downloaded the 5.0.11-beta snapshot and the problem still seems to be there...
See new attachment bug11309b.cpp.

Cpp trace

Attachment: bug11309b.cpp (text/plain), 24.88 KiB.

Verified here. The stacktrace is the same as in bug #12243 if the latter (this) is resolved probably the other will be resolved too.

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1090910560 (LWP 15189)]
0x0000000000769420 in read_view_sees_trx_id (view=0x0, trx_id={high = 0, low = 1802757}) at read0read.ic:53
53              if (ut_dulint_cmp(trx_id, view->up_limit_id) < 0) {
(gdb) bt
#0  0x0000000000769420 in read_view_sees_trx_id (view=0x0, trx_id={high = 0, low = 1802757}) at read0read.ic:53
#1  0x000000000076376b in lock_clust_rec_cons_read_sees (rec=0x2a97bf8157 "\200", index=0x2a970a80b8, offsets=0x4105dad0, view=0x0) at lock0lock.c:522
#2  0x00000000006faeb1 in row_search_for_mysql (buf=0x373eaa8 "\002", mode=1, prebuilt=0x2a970a04b8, match_mode=0, direction=1) at row0sel.c:3760
#3  0x000000000064fe9d in ha_innobase::general_fetch (this=0x3706260, buf=0x373eaa8 "\002", direction=1, match_mode=0) at ha_innodb.cc:3970
#4  0x000000000065027b in ha_innobase::rnd_next (this=0x3706260, buf=0x373eaa8 "\002") at ha_innodb.cc:4161
#5  0x0000000000639df5 in rr_sequential (info=0x3730c00) at records.cc:193
#6  0x00000000005cf8b2 in sub_select (join=0x372f318, join_tab=0x3730b88, end_of_records=false) at sql_select.cc:9380
#7  0x00000000005bc0d7 in Cursor::fetch (this=0x373b5b0, num_rows=1) at sql_select.cc:1871
#8  0x00000000005e878f in mysql_stmt_fetch (thd=0x37232b0, packet=0x3727271 "\001", packet_length=9) at sql_prepare.cc:2256
#9  0x000000000057b2d2 in dispatch_command (command=COM_STMT_FETCH, thd=0x37232b0, packet=0x3727271 "\001", packet_length=9) at sql_parse.cc:1634
#10 0x000000000057acad in do_command (thd=0x37232b0) at sql_parse.cc:1467
#11 0x0000000000579e67 in handle_one_connection (arg=0x37232b0) at sql_parse.cc:1116
#12 0x0000002a95992919 in start_thread () from /lib64/tls/libpthread.so.0
#13 0x0000002a95eff8a3 in thread_start () from /lib64/tls/libc.so.6

Subject: bk commit - 5.0 tree (konstantin:1.1975) BUG#11832                    

ChangeSet
  1.1975 05/08/10 18:36:13 konstantin@mysql.com +5 -0
  A fix and a test case for Bug#12243 "MySQL Server crashes with 2
  cursors (+ commit)" and Bug#11832 "Server crash with InnoDB + Cursors"
  See comments to the changed files.

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

Approved by Heikki Tuuri via email.

Fixed in 5.0 tree, currently tagged 5.0.12

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 5.0.12 changelog.