Bug #13219 PreparedStatement with subselect crashes MySQL server

• Submitted: 15 Sep 2005 10:57
• Modified: 15 Sep 2005 15:56
• Reporter: Kaarle Kaila
• Status: Closed
• Category: MySQL Server: Prepared statements
• Severity: S2 (Serious)
• Version: MySQL 5.0.12/JDBC 3.1.10
• OS: Windows (W2K)
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[15 Sep 2005 10:57] Kaarle Kaila

Description:
A query or update using subselect in the where statement and when the sql command has been created using PreparedStatement crashes the MySQL server when executed. Same command if it has been created using Statement class functions ok. 

Sample command:

select location_id,name,language_code,country_code from location where location_id in (select parent_id from located_in where child_id = ? )

results in:

Caused by: com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.net.SocketException
MESSAGE: Connection reset

STACKTRACE:

java.net.SocketException: Connection reset

MySQL has to be restarted e.g. from SystemTray monitor

here are table and view definitions

create table location_t (
  location_id varchar(16) not null,
  language_code varchar(64) not null,
  country_code varchar(2) not null,
  name varchar(128) not null,
  is_state varchar(1),
  published_child_rs varchar(1),
  published_grandchild_rs  varchar(1),
  published_vra varchar(1),
  approved_child_rs varchar(1),
  approved_grandchild_rs  varchar(1),
  approved_vra varchar(1),
  imported_child_rs varchar(1),
  imported_grandchild_rs  varchar(1),
  imported_vra varchar(1),
  batch_no integer,  
  removed_batch integer,
  approved_batch integer,
  published_batch integer
);

create table located_in_t (
  parent_id varchar(16) not null,
  child_id varchar(16) not null,
  batch_no integer,  
  removed_batch integer,
  approved_batch integer,
  published_batch integer 
);

create view location as
  select location_id, language_code,country_code,name,is_state,
  imported_child_rs as has_child_rs,imported_grandchild_rs as has_grandchild_rs,
  imported_vra as has_vra from location_t
  where removed_batch is null;

create view located_in as
   select parent_id, child_id,batch_no from located_in_t
   where removed_batch is null;

How to repeat:
Crashes every time even if tables are (at least almost ) empty

[15 Sep 2005 15:56] 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: 5.0.12-beta-nt

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

mysql> PREPARE stmt1 FROM 'select location_id,name,language_code,country_code from location where
    '> location_id in (select parent_id from located_in where child_id = ? )';
Query OK, 0 rows affected (0.16 sec)
Statement prepared

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

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

c:\mysql\bin>mysqld-nt --standalone --console
050915 11:41:14  InnoDB: Started; log sequence number 0 688466
050915 11:41:14 [Note] mysqld-nt: ready for connections.
Version: '5.0.12-beta-nt'  socket: ''  port: 3306  Official MySQL binary

runtime error R6025
- pure virtual function call

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: 5.0.13-beta-nt

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

mysql> PREPARE stmt1 FROM 'select location_id,name,language_code,country_code from location where
    '> location_id in (select parent_id from located_in where child_id = ? )';
Query OK, 0 rows affected, 2 warnings (0.22 sec)
Statement prepared

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

mysql> EXECUTE stmt1 USING @a;
Empty set (0.03 sec)

mysql>