MySQL Bugs: #21166: Prepared statement causes signal 11 on second execution

Bug #21166	Prepared statement causes signal 11 on second execution
Submitted:	19 Jul 2006 21:47	Modified:	31 Aug 2006 19:32
Reporter:	Tyler Montbriand	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S1 (Critical)
Version:	5.0.22/5.0BK/5.1BK	OS:	Linux (Linux)
Assigned to:	Tomash Brechko	CPU Architecture:	Any

Description:
When PHP executes a particular prepared statement for the second time, the MySQL server crashes with signal 11.  I upgraded from 5.0.21 to see if that fixed the crashes, it did not.

The exact dataset I am using is here:
http://burningsmell.org/sqlbug/test.sql

The relevant error message from mysqld.err is here:
http://burningsmell.org/sqlbug/mysqld.err

A debugging trace is here:
http://burningsmell.org/sqlbug/mysqld.trace

I haven't been able to get a stack trace yet.

How to repeat:
The PHP code which produced this crash, stripped of HTML, is:

        $typeid_list=array(
                array(2,"SDRAM"),
                array(3,"SIMM 72")
                );

        $stmt=mysqli_prepare($_sql,<<<END
SELECT typevalue, notes FROM parts
        JOIN producttype ON(producttype.typeid=parts.typeid)
        LEFT JOIN productdata ON(
                (productdata.typeid,productdata.valueid)=
                (parts.typeid,parts.valueid))
        WHERE parts.typeid=?
END
        );

        mysqli_stmt_bind_result($stmt,$typevalue, $notes);
        mysqli_stmt_bind_param($stmt,'i',$typeid_in);
        mysqli_stmt_bind_result($stmt,$typevalue,$notes);

        for($n=0; $typeid_list[$n][0]; $n++)
        {
                $typeid_in=$typeid_list[$n][0];
                mysqli_stmt_execute($stmt);
                while(mysqli_stmt_fetch($stmt))
                {
                  echo "<p>$typevalue</p><p>$notes</p>";
                }
        }

This crash happens exactly the same way every time, the connection with the server breaking at the second execution when the server dies from signal 11.

Back trace 5.0BK on Suse 10 32-bit

Attachment: bt-21166.txt (text/plain), 26.05 KiB.

Back trace attached.

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create dbj
miguel@hegel:~/dbs/5.0> bin/mysql -uroot dbj
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.25-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> source /home/miguel/a/test.sql
Query OK, 0 rows affected, 1 warning (0.01 sec)

<cut>

Query OK, 1 row affected (0.00 sec)

mysql> PREPARE stmt1 FROM 'SELECT typevalue, notes FROM parts
    '> JOIN producttype ON(producttype.typeid=parts.typeid)
    '> LEFT JOIN productdata ON(
    '> (productdata.typeid,productdata.valueid)=
    '> (parts.typeid,parts.valueid))
    '> WHERE parts.typeid=?';
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> SET @a = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt1 using @a;
+-----------+----------------+
| typevalue | notes          |
+-----------+----------------+
| PC66      | Assorted Sizes | 
| PC100     | 128MB          | 
| PC133     | 128MB          | 
+-----------+----------------+
3 rows in set (0.00 sec)

mysql> execute stmt1 using @a;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

Thank you for the bug report. I was able to repeat on 5.0/5.1BK source.

miguel@hegel:~/dbs/5.1> bin/mysqladmin -uroot create dbj
miguel@hegel:~/dbs/5.1> bin/mysql -uroot dbj
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.12-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> source /home/miguel/a/test.sql
Query OK, 0 rows affected, 1 warning (0.02 sec)

Query OK, 1 row affected (0.00 sec)

mysql> PREPARE stmt1 FROM 'SELECT typevalue, notes FROM parts
    '> JOIN producttype ON(producttype.typeid=parts.typeid)
    '> LEFT JOIN productdata ON(
    '> (productdata.typeid,productdata.valueid)=
    '> (parts.typeid,parts.valueid))
    '> WHERE parts.typeid=?';
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> SET @a = 2;
Query OK, 0 rows affected (0.01 sec)

mysql> execute stmt1 using @a;
+-----------+----------------+
| typevalue | notes          |
+-----------+----------------+
| PC66      | Assorted Sizes | 
| PC100     | 128MB          | 
| PC133     | 128MB          | 
+-----------+----------------+
3 rows in set (0.01 sec)

mysql> execute stmt1 using @a;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

Anything else you need me to do to help solve this bug?

I have been attempting to narrow down the crash but this has been very difficult as it happens deep within obfuscated C++ object code.

It would be really, really nice to have someone look at this bug.  It was VERIFIED rapidly enough but has seen absolutely no activity at all since then.  Our business is waiting on a solution or workaround.

The crashes do not appear to happen when not using InnoDB tables.

I spoke too hastily.  The error still occurs.

Likely to be a 4.1 issue too.

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/commits/10557

ChangeSet@1.2235, 2006-08-16 20:58:03+04:00, kroki@moonlight.intranet +8 -0
  BUG#21166: Prepared statement causes signal 11 on second execution
  
  Changes in an item tree done by optimizer weren't properly
  registered and went unnoticed, which resulted in preliminary freeing
  of used memory.
  
  TO THE REVIEWER:
  The original bug was triggered in Item_row::transform().  I fixed
  all transform methods (hope this is correct), but test case covers only
  original case, because it's difficult to figure out how to trigger
  particular transform.

So far the patched MySQL 5.0.22 is operating perfectly.  Thank you.

Approved over email with a few comments.

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/commits/10823

ChangeSet@1.2243, 2006-08-24 15:49:12+04:00, kroki@moonlight.intranet +9 -0
  BUG#21166: Prepared statement causes signal 11 on second execution
  
  Changes in an item tree done by optimizer weren't properly
  registered and went unnoticed, which resulted in preliminary freeing
  of used memory.

Queued to 5.0-runtime.

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/commits/10865

ChangeSet@1.2245, 2006-08-25 11:34:13+04:00, kroki@moonlight.intranet +1 -0
  Comment cleanup after push of bug#21166.

Pushed into 5.0.25 and 5.1.12

Noted in 5.0.25, 5.1.12 changelogs.