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

[19 Jul 2006 21:47] Tyler Montbriand
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.
[19 Jul 2006 23:19] MySQL Verification Team
Back trace 5.0BK on Suse 10 32-bit

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

[19 Jul 2006 23:20] MySQL Verification Team
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>
[19 Jul 2006 23:26] MySQL Verification Team
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>
[24 Jul 2006 21:21] Tyler Montbriand
Anything else you need me to do to help solve this bug?
[2 Aug 2006 15:09] Tyler Montbriand
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.
[2 Aug 2006 15:18] Tyler Montbriand
The crashes do not appear to happen when not using InnoDB tables.
[2 Aug 2006 15:21] Tyler Montbriand
I spoke too hastily.  The error still occurs.
[14 Aug 2006 22:17] Konstantin Osipov
Likely to be a 4.1 issue too.
[16 Aug 2006 16:58] 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/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.
[17 Aug 2006 0:41] Tyler Montbriand
So far the patched MySQL 5.0.22 is operating perfectly.  Thank you.
[23 Aug 2006 18:28] Konstantin Osipov
Approved over email with a few comments.
[24 Aug 2006 11:49] 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/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.
[24 Aug 2006 11:52] Tomash Brechko
Queued to 5.0-runtime.
[25 Aug 2006 7:34] 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/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.
[31 Aug 2006 10:21] Konstantin Osipov
Pushed into 5.0.25 and 5.1.12
[31 Aug 2006 19:32] Paul DuBois
Noted in 5.0.25, 5.1.12 changelogs.