MySQL Bugs: #4192: Prepared query filtering a datetime field with BETWEEN crashes the server

Bug #4192	Prepared query filtering a datetime field with BETWEEN crashes the server
Submitted:	17 Jun 2004 17:36	Modified:	22 Jun 2004 4:34
Reporter:	Nathaniel Blanchard	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.2a	OS:	Windows (windows client, linux server)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
Using a prepared query where the query makes use of a BETWEEN where clause and you bind 2 string buffers, when you execute the prepared query the server will crash.

How to repeat:
Use a prepared query that filters by a datetime column using BETWEEN syntax, ie WHERE <datetimefield> BETWEEN :1 and :2.

Bind 2 string buffers buffers to the query (for :1 and :2)

In a loop (we cycle thru a couple date ranges):
copy a string version of the date you want to start and end on into the buffers.
execute the query

The mysql server will crash.

Could you please provide the test case ? This will help us for to
test quickly.

Thanks in advance.

Test Plan:

CREATE TABLE date_test (date datetime);

  MYSQL_STMT *stmt;
  MYSQL_BIND bind_array[3];
  MYSQL *mysql = NULL;
  MYSQL_TIME datetime;
  char szStart[ 100 ], szEnd[ 100 ];

  const char szFirstDateFormat[] = "%d-%02d-%02d %02d:00:00";
  const char szSecondDateFormat[] = "%d-%02d-%02d %02d:59:59";

  const char *stmt_text;
  int rc; /* to save return value of API calls */
  int i; /* loop counter */

  //setup_connection();
  mysql = mysql_init( NULL );
  mysql_real_connect( mysql, "nsb", "nate", "nate", "nate", 0, NULL, 0 );

  stmt= mysql_stmt_init(mysql);

  stmt_text= "INSERT INTO date_test ( date ) VALUES (?)";

  mysql_stmt_prepare(stmt, stmt_text, strlen(stmt_text));

  memset(bind_array, 0, sizeof(bind_array));

  bind_array[0].buffer_type= MYSQL_TYPE_DATETIME;
  bind_array[0].buffer= (char*) &datetime;

  mysql_stmt_bind_param(stmt, bind_array);

  datetime.year = 2004;
  datetime.month = 1;

  for (id= 1; id <= 3; ++id)
  {
    for ( int hour = 1; hour <= 24; hour++ )
	{
		datetime.day = id;
		datetime.hour = hour;
		mysql_stmt_execute(stmt);
	}
  }

  stmt_text= "DELETE FROM date_test WHERE date BETWEEN ? AND ?";
  mysql_stmt_prepare(stmt, stmt_text, strlen(stmt_text));

  memset(bind_array, 0, sizeof(bind_array));

  bind_array[0].buffer_type= MYSQL_TYPE_VAR_STRING;
  bind_array[0].buffer= szStart;

  bind_array[1].buffer_type= MYSQL_TYPE_VAR_STRING;
  bind_array[1].buffer= szEnd;

  mysql_stmt_bind_param(stmt, bind_array);

  for (id= 1; id <= 3; ++id)
  {
    sprintf( szStart, szFirstDateFormat, 2004, 1, id, 0 );
    sprintf( szEnd, szSecondDateFormat, 2004, 1, id, 0 );
    mysql_stmt_execute(stmt);
  }

That code will cause the database server to crash.

Thank you for the bug report and its test case; I was able to repeat:

The full backtrace it is attached in the file tab.

/home/miguel/dbs/4.1/libexec/mysqld: ready for connections.
Version: '4.1.3-beta-debug-log'  socket: '/home/miguel/dbs/4.1/mysql41.sock'  port: 3306
[New Thread 147466 (LWP 3440)]
[New Thread 163851 (LWP 16662)]
pure virtual method called

Program received signal SIGABRT, Aborted.
[Switching to Thread 163851 (LWP 16662)]
0x40205b71 in kill () from /lib/i686/libc.so.6
(gdb) backtrace full

<cut>

     log_file_name = 0x42866334 "dc\206B¶¤3\bl7R\b\v\200\002",
      port = 1074099852, connect_retry = 139605868,
      pos = 4793627928371921495, server_id = 1074069871,
      ssl = 139605884, ssl_key = 0x0,
      ssl_cert = 0x42866364 "\214c\206B\220c\206Bl7R\b\201ý=\bW\002",
      ssl_ca = 0x833a4b6 "\211Ø\215eô[^_]ÃU\211åWVS\203ì\030\213]\f\213}\024SèC{Ùÿ\203Ä\020\203{8", ssl_capath = 0x852376c "",
      ssl_cipher = 0x2800b <Address 0x2800b out of bounds>,
      relay_log_name = 0x42866354 "¤c\206B³\2173\bX\002",
      relay_log_pos = 139479524}, mqh = {questions = 139605856,
      updates = 10, connections = 1116103588, bits = 137596851},
    thread_id = 600, type = 1116103560, sql_command = 1116103564

<cut>

#20 0x08154de1 in handle_one_connection (arg=0x0) at sql_parse.cc:1016
        error = 1074099852
---Type <return> to continue, or q <return> to quit---
        net = (NET *) 0x850d35c
        thd = (class THD *) 0x850ce30
        launch_time = 0
        set = {__val = {0 <repeats 32 times>}}
#21 0x4004ef60 in pthread_start_thread ()
   from /lib/i686/libpthread.so.0
No symbol table info available.
#22 0x4004f0fe in pthread_start_thread_event ()
   from /lib/i686/libpthread.so.0
No symbol table info available.
#23 0x402b8327 in clone () from /lib/i686/libc.so.6
No symbol table info available.
(gdb)

Full backtrace

Attachment: bug4192.txt (text/plain), 19.72 KiB.

I wasn't able to repeat this bug on the latest MySQL 4.1 tree (Linux).
I was fixing several other bugs in prepared statements which can influence this issue, and
if hide the bug if not fix it.
Could you check again against the latest BK tree?
I've asked Miguel (my colleague) to do that as well.

I am closing this bug because I confirmed the Konstantin's test.
With the latest BK 4.1 source tree the server don't crashes anymore.

Thank you for the bug report.

Any idea when the dev tree will compile again without errors?  I'd really like to be able to test his.  I get a ton of errors dealing with the new timezone functionality that was added a couple days ago.

It does compile on Linux, we're working on making it compile on Sun Solaris and Windows.

--cut:
This is what I get when I try and compile it on linux (keep in mind that I have
+been successfully compiling the dev tree for the past 3 months now):

mkdir .libs
g++ -O3 -DDBUG_OFF -fno-implicit-templates -fno-exceptions -fno-rtti -rdynamic
+-o mysql_tzinfo_to_sql tztime.o  ../myisam/libmyisam.a
+../myisammrg/libmyisammrg.a ../heap/libheap.a ../vio/libvio.a
+../mysys/libmysys.a ../dbug/libdbug.a ../regex/libregex.a
+../strings/libmystrings.a -lpthread -lz -lcrypt -lnsl -lm -lpthread
/usr/lib/gcc-lib/i386-redhat-linux/2.96/../../../crt1.o: In function `_start':
/usr/lib/gcc-lib/i386-redhat-linux/2.96/../../../crt1.o(.text+0x18): undefined
+reference to `main'
tztime.o: In function `my_tz_init(THD *, char const *, char)':
tztime.o(.text+0xacc): undefined reference to `THD::THD(void)'
tztime.o(.text+0xae8): undefined reference to `THD::store_globals(void)'
tztime.o(.text+0xb1a): undefined reference to `sql_print_error(char const
+*,...)'
tztime.o(.text+0xb56): undefined reference to `sql_print_error(char const
+*,...)'
tztime.o(.text+0xc8f): undefined reference to `open_tables(THD *, st_table_list
+*, unsigned int *)'
tztime.o(.text+0xcaa): undefined reference to `sql_print_error(char const
+*,...)'
--end cut

Hi.
You should not write me personally; please use Bugs DB instead.

BugDB will ensure that your issue is not lost, will make it easy to involve other developers as well as will benefit other users who will be able to find use info
in this thread.
Regarding your compile problem, the reason of the error is old autotools you're using.
Original timezones patch worked only with late versions of autotools (i.e.
automake 1.7.6 and autoconf 2.57).
We soon will fix it to work with automake 1.5.