| Bug #12763 | Update statement causes server crash with InnoDB Table | ||
|---|---|---|---|
| Submitted: | 23 Aug 2005 21:44 | Modified: | 23 Aug 2005 22:03 |
| Reporter: | Dwain Ehler | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 4.1.13 | OS: | Solaris (Solaris 8 & Windows) |
| Assigned to: | CPU Architecture: | Any | |
[23 Aug 2005 22:03]
MySQL Verification Team
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:
c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.13-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create temporary table IF NOT EXISTS A (
->
-> `A1` bigint(20) NOT NULL default '0',
-> `A2` bigint(20) default '0'
->
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> create temporary table IF NOT EXISTS B
-> ( `B1` bigint(20) NOT NULL default '0' ,
-> `B2` bigint(20) NOT NULL default '0',
-> UNIQUE KEY b_idx (B1, B2)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> insert into A (A1, A2 ) values( 188,1 );
Query OK, 1 row affected (0.02 sec)
mysql> insert into B(B1, B2 ) values( 1,2 );
Query OK, 1 row affected (0.03 sec)
mysql>
mysql> UPDATE A
-> SET A2 = ( Select B2
-> from B
-> where A2 = B1 )
-> where A1 = 188;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.15-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create temporary table IF NOT EXISTS A (
->
-> `A1` bigint(20) NOT NULL default '0',
-> `A2` bigint(20) default '0'
->
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> create temporary table IF NOT EXISTS B
-> ( `B1` bigint(20) NOT NULL default '0' ,
-> `B2` bigint(20) NOT NULL default '0',
-> UNIQUE KEY b_idx (B1, B2)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> insert into A (A1, A2 ) values( 188,1 );
Query OK, 1 row affected (0.03 sec)
mysql> insert into B(B1, B2 ) values( 1,2 );
Query OK, 1 row affected (0.05 sec)
mysql>
mysql> UPDATE A
-> SET A2 = ( Select B2
-> from B
-> where A2 = B1 )
-> where A1 = 188;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
Description: I have an update query that will consistently crash the Mysql Server in both Windows XP and Solaris 8. In solaris it gets a signal 11 and restarts, in windows it crashes completely. I've reproduced this in 4.1.12 and 4.1.13. It did not cause a crash in 4.1.10 so it seems newly introduced. I didn't test in 4.1.11. This will happen with temporary or regular tables. It is easy to reproduce with the SQL below. How to repeat: I connect to the empty test DB as root to run these queries; create temporary table IF NOT EXISTS A ( `A1` bigint(20) NOT NULL default '0', `A2` bigint(20) default '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; create temporary table IF NOT EXISTS B ( `B1` bigint(20) NOT NULL default '0' , `B2` bigint(20) NOT NULL default '0', UNIQUE KEY b_idx (B1, B2)) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into A (A1, A2 ) values( 188,1 ); insert into B(B1, B2 ) values( 1,2 ); UPDATE A SET A2 = ( Select B2 from B where A2 = B1 ) where A1 = 188; Suggested fix: To work around; if I do any of the following to the above it will not crash: - Make Table B MyIsam - Remove the unique key b_idx - Make A2 not null