Bug #8807 Select crash server
Submitted: 25 Feb 2005 14:42 Modified: 20 Apr 2005 1:37
Reporter: Francois Guillemette Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.10 + 5.0.4 OS:Any (any)
Assigned to: Magnus Blåudd CPU Architecture:Any

[25 Feb 2005 14:42] Francois Guillemette
Description:
The following code make MySQL server crash (v4.1.10-nt-max). I suspect the JConnector since I can do the query inside  MySQL query browser without problem. 

Also, the code work well if the database table engine is MyIsam, it crash with InnoDB (no problem using MySQL query browser).

How to repeat:
  public void test() throws Exception {
    PreparedStatement preparedStatement = null;
    String sql = "drop table if exists testSelect1";
    preparedStatement = connection.prepareStatement(sql);
    preparedStatement.execute();
    System.out.println("Table testSelect1 dropped");

    sql = "drop table if exists testSelect2";
    preparedStatement = connection.prepareStatement(sql);
    preparedStatement.execute();
    System.out.println("Table testSelect2 dropped");

    sql = "create table testSelect1 (\n" + 
          "  id varchar(23) not null,\n" +
          "  idx smallint(6) not null,\n" +
          "  datum date not null,\n" +
          "  text varchar(255), \n" +
          "  primary key (id, idx, datum)\n" +
          ");";
    preparedStatement = connection.prepareStatement(sql);
    preparedStatement.execute();
    System.out.println("Table testSelect1 created");

    sql = "create table testSelect2 (\n" + 
          "  id varchar(23) not null,\n" +
          "  idx smallint(6) not null,\n" +
          "  datum date not null,\n" +
          "  text varchar(255), \n" +
          "  primary key (id, idx, datum)\n" +
          ");";
    preparedStatement = connection.prepareStatement(sql);
    preparedStatement.execute();
    System.out.println("Table testSelect2 created");
    
    sql = "SELECT datum,text\n" +
          "FROM testSelect1\n" +
          "WHERE datum =(\n" + 
          "  SELECT MAX(datum)\n" + 
          "  FROM testSelect2\n" +
          "  WHERE id = ? AND idx = ?)\n" + 
          "AND id = ?\n" + 
          "AND idx = ?\n";
    preparedStatement = connection.prepareStatement(sql);  // <<-- Crash here
    preparedStatement.setString(1, "some id");
    preparedStatement.setInt(2, 2);
    preparedStatement.setString(3, "some other id");
    preparedStatement.setInt(4, 2);
    preparedStatement.executeQuery();
    System.out.println("Select done");
}

Suggested fix:
Use MyIsam table engine, or change the select into two selects.
[26 Feb 2005 1:56] Miguel Solorzano
Could you please verify in your host_name.err or mysql.err file
if exists an error message from InnoDB when the crash happens
or start the server as standalone i.e.:

mysqld-nt --standalone --console

and when the crash happens verify if exists error message from
InnoDB and show them here.

Thanks in advance.
[28 Feb 2005 14:08] Francois Guillemette
Generated by mysqld-debug.exe --standalone --console -- debug

Attachment: mysqld.trace (application/octet-stream, text), 176.64 KiB.

[28 Feb 2005 14:09] Francois Guillemette
Hi Miguel, I have started the server using the command :

mysqld-nt.exe --standalone --console

but there is no error file created, the server crash, and the only thing I got is in application event viewer : 

Application failure mysqld-nt.exe, version 0.0.0.0, module failure mysqld-nt.exe, version 0.0.0.0, at offset 0x000a0a25.

I restarteds the server with the following command :

mysqld-debug.exe --standalone --console --debug

I have sent the trace that was generated.
[7 Mar 2005 17:00] Francois Guillemette
The JDK I use is 1.4.1_06
[9 Mar 2005 6:17] Miguel Solorzano
This sounds as server bug not Connector/J bug. I did a test using C API:

1- Start the server as below:

C:\mysql\bin>mysqld-debug --standalone --console --default-storage-engine=innodb
050309  3:00:16  InnoDB: Started; log sequence number 0 444629
mysqld-debug: ready for connections.
Version: '4.1.10-debug'  socket: ''  port: 3306  Source distribution

2- Create the tables.

C:\temp>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.10-debug

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

mysql> create table testSelect1 (
    -> id varchar(23) not null,
    -> idx smallint(6) not null,
    -> datum date not null,
    -> text varchar(255),
    -> primary key (id, idx, datum));
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> create table testSelect2 (
    -> id varchar(23) not null,
    -> idx smallint(6) not null,
    -> datum date not null,
    -> text varchar(255),
    -> primary key (id, idx, datum));
Query OK, 0 rows affected (0.11 sec)

mysql>

3- Run a small application as below:

#include <stdio.h>
#include <string.h>
#include <my_global.h>
#include <m_ctype.h>
#include <m_string.h>
#include <mysql.h>

#define STRING_SIZE 50

#define SELECT_COMMAND "SELECT datum,text FROM testSelect1\
                        WHERE datum = (SELECT MAX(datum)\
                        FROM testSelect2 WHERE id = ? AND idx = ?)\
                        AND id = ? AND idx = ?"

void main()
{
  MYSQL mysql;
  MYSQL_STMT    *stmt;
   
  if ( mysql_init(&mysql) == NULL )
  {
    printf( "mysql_init failed\n" );
    return;
  }

  if (mysql_real_connect(&mysql,"localhost",
                         "root","","test",0,NULL,0) ==NULL)
  {
    printf( "mysql_real_connect failed\n" );
    return;
  }

  
  printf( "Connected to MySQL Server: %s\n", mysql_get_server_info(&mysql));

  stmt = mysql_stmt_init(&mysql);
  if (!stmt)
  {
    fprintf(stderr, " mysql_stmt_init(), out of memory\n");
    return;
  }

  if (mysql_stmt_prepare(stmt, SELECT_COMMAND, strlen(SELECT_COMMAND)))
  {
    fprintf(stderr, " mysql_stmt_prepare(), SELECT failed\n");
    fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
    exit(0);
  }
  
  fprintf(stdout, " prepare, SELECT successful\n");

  if (mysql_stmt_close(stmt))
  {
    fprintf(stderr, " failed while closing the statement\n");
    fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
    return;
  }
}

4- the call of  mysql_stmt_prepare(stmt, SELECT_COMMAND,....
    crash the server  like the Java test case:

/sql/sql_select.cpp
--2583--

      {
	TABLE *tmp_table=join->all_tables[tablenr];
	keyuse->ref_table_rows= max(tmp_table->file->records, 100);
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ CRASH HERE

5- Call stack

mysqld-debug.exe!optimize_keyuse(JOIN * join=0x02f194d8, st_dynamic_array * keyuse_array=0x02f19e1c)  Line 2585 + 0x3	C++
mysqld-debug.exe!make_join_statistics(JOIN * join=0x02f194d8, st_table_list * tables=0x00000000, Item * conds=0x02f1b510, st_dynamic_array * keyuse_array=0x02f19e1c)  Line 1968 + 0xd	C++
mysqld-debug.exe!JOIN::optimize()  Line 552 + 0x27	C++
mysqld-debug.exe!subselect_single_select_engine::exec()  Line 1270 + 0xb	C++
mysqld-debug.exe!Item_subselect::exec()  Line 204 + 0x11	C++
mysqld-debug.exe!Item_singlerow_subselect::val_str(String * str=0x02f1b5ac)  Line 493 + 0xe	C++
mysqld-debug.exe!Item::save_in_field(Field * field=0x02f10bc8, int no_conversions=1)  Line 1785 + 0x1b	C++
mysqld-debug.exe!convert_constant_item(THD * thd=0x02f13460, Field * field=0x02f10bc8, Item * * item=0x02f1b6a8)  Line 186 + 0x15	C++
mysqld-debug.exe!Item_bool_func2::fix_length_and_dec()  Line 229 + 0x17	C++
mysqld-debug.exe!Item_func::fix_fields(THD * thd=0x02f13460, st_table_list * tables=0x02f1ac68, Item * * ref=0x02f1bbe4)  Line 334	C++
mysqld-debug.exe!Item_cond::fix_fields(THD * thd=0x02f13460, st_table_list * tables=0x02f1ac68, Item * * ref=0x02f1949c)  Line 1940 + 0x27	C++
mysqld-debug.exe!setup_conds(THD * thd=0x02f13460, st_table_list * tables=0x02f1ac68, Item * * conds=0x02f1949c)  Line 2635 + 0x28	C++
mysqld-debug.exe!setup_without_group(THD * thd=0x02f13460, Item * * ref_pointer_array=0x02f1bc10, st_table_list * tables=0x02f1ac68, List<Item> & fields={...}, List<Item> & all_fields={...}, Item * * conds=0x02f1949c, st_order * order=0x00000000, st_order * group=0x00000000, int * hidden_group_fields=0x02f19414)  Line 229 + 0x11	C++
mysqld-debug.exe!JOIN::prepare(Item * * * rref_pointer_array=0x00e6e180, st_table_list * tables_init=0x02f1ac68, unsigned int wild_num=0, Item * conds_init=0x02f1bb58, unsigned int og_num=0, st_order * order_init=0x00000000, st_order * group_init=0x00000000, Item * having_init=0x00000000, st_order * proc_param_init=0x00000000, st_select_lex * select_lex_arg=0x00e6e094, st_select_lex_unit * unit_arg=0x00e6df94)  Line 278 + 0x112	C++
mysqld-debug.exe!st_select_lex_unit::prepare(THD * thd_arg=0x02f13460, select_result * sel_result=0x00000000, unsigned long additional_options=0)  Line 235 + 0xc7	C++
mysqld-debug.exe!mysql_test_select(Prepared_statement * stmt=0x00e6df58, st_table_list * tables=0x02f18a60, int text_protocol=0)  Line 1082 + 0x10	C++
mysqld-debug.exe!send_prepare_results(Prepared_statement * stmt=0x00e6df58, int text_protocol=0)  Line 1415 + 0x11	C++
mysqld-debug.exe!mysql_stmt_prepare(THD * thd=0x02f13460, char * packet=0x00e5eb11, unsigned int packet_length=203, st_lex_string * name=0x00000000)  Line 1621 + 0x13	C++
mysqld-debug.exe!dispatch_command(enum_server_command command=COM_PREPARE, THD * thd=0x02f13460, char * packet=0x00e5eb11, unsigned int packet_length=203)  Line 1458 + 0x13	C++
mysqld-debug.exe!do_command(THD * thd=0x02f13460)  Line 1291 + 0x31	C++
mysqld-debug.exe!handle_one_connection(void * arg=0x02f13460)  Line 1023 + 0x9	C++
mysqld-debug.exe!pthread_start(void * param=0x00e6f4b0)  Line 63 + 0x7	C
mysqld-debug.exe!_threadstart(void * ptd=0x02f14820)  Line 173 + 0xd	C
kernel32.dll!7c80b50b() 	
kernel32.dll!7c8399f3()
[15 Mar 2005 21:10] Francois Guillemette
Changed the version to MySQL 4.1.10, since it is not a problem with J/Connector.
[16 Mar 2005 10:11] Magnus Blåudd
Reproduced on Fedora Core 3 with latest mysql-4.1 tree
[24 Mar 2005 18:14] 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/internals/23364
[30 Mar 2005 9:53] 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/internals/23466
[30 Mar 2005 10:14] 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/internals/23467
[12 Apr 2005 10:14] Magnus Blåudd
Added patch that prevents prepared statements with subselects containing parameters from being treated as const during prepare phase. The crash occured when the subselect was executed during prepare statement.
Pushed to 4.1.10 and 5.0.4
[20 Apr 2005 1:37] Paul Dubois
Noted in 4.1.11, 5.0.4 changelogs.
[13 Jul 2005 13:27] 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/internals/26996
[13 Jul 2005 13:40] 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/internals/26997