Bug #7743 'Lost connection to MySQL server during query' on Stored Procedure
Submitted: 8 Jan 2005 12:47 Modified: 21 Feb 2005 8:32
Reporter: Holger S. Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.2-alpha, for pc-linux OS:Linux (Debian Linux (Testing))
Assigned to: Bugs System CPU Architecture:Any

[8 Jan 2005 12:47] Holger S.
Description:
Hello,

I wanted to create a Stored Procedure including a Select with case sensitive binary search through a CHAR column.

When I now call this Procedure with the mysql-shell-client (mysql  Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686)) I get errormessages like "Lost connection to MySQL server during query" and "Illegal mix of collations".

Maybe it is a better way to use collations for CS searches, but surely a Client should never be disconnected for "incorrect" SQL statements ;)
I dont have any experiences in handling those new collations...

How to repeat:

-- just paste this into the mysql shell client...

delimiter //
use test

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  a mediumint(8) unsigned NOT NULL auto_increment,
  b smallint(5) unsigned NOT NULL,
  c char(32) NOT NULL,
  PRIMARY KEY  (a)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES (1, 2, 'OneWord');
INSERT INTO t1 VALUES (2, 2, 'AnotherWord');
        

DROP PROCEDURE IF EXISTS myproc
\g
CREATE PROCEDURE myproc ( searchstring CHAR(28) )
BEGIN
	DECLARE var MEDIUMINT(8) UNSIGNED;
	-- read uid
	SELECT a INTO var FROM t1 WHERE b = 2 AND c = BINARY searchstring LIMIT 1;
	
	--the following SELECT would NOT lead to those errors but is not CS as well
	--SELECT a INTO var FROM t1 WHERE b = 2 AND c = searchstring LIMIT 1;
	--SELECT a INTO var FROM t1 WHERE c = BINARY searchstring LIMIT 1;
	
	--the following SELECT with subquery should be a propper workaround
	--SELECT a INTO var FROM (SELECT * FROM t1 WHERE b = 2) AS tmpResult WHERE c = BINARY searchstring LIMIT 1;
	SELECT var;
END
\g

call myproc("oneword") \g -- should output NULL
call myproc("OneWord") \g -- should output 1 INT
call myproc("anotherword") \g  -- should output NULL
call myproc("AnotherWord") \g -- should output 2 INT

Suggested fix:
Strangely a single-conditional BINARY search works without problems, so a subquery might be a proper workaround:

SELECT a INTO var FROM (SELECT * FROM t1 WHERE b = 2) AS tmpResult WHERE c = BINARY searchstring LIMIT 1;
[10 Jan 2005 15:45] Victoria Reznichenko
Verified with 5.0.3-alpha-debug-log

(gdb) bt
#0  0xb7ceea21 in kill () from /lib/libc.so.6
#1  0xb7e49051 in pthread_kill () from /lib/libpthread.so.0
#2  0xb7e4942b in raise () from /lib/libpthread.so.0
#3  0xb7cee7b4 in raise () from /lib/libc.so.6
#4  0xb7cefc7d in abort () from /lib/libc.so.6
#5  0xb7ce7def in __assert_fail () from /lib/libc.so.6
#6  0x08190939 in mysql_parse (thd=0x8d6e370, inBuf=0x8d5aeb8 "call myproc(\"oneword\")",
    length=22) at sql_parse.cc:4757
#7  0x081891bf in dispatch_command (command=COM_QUERY, thd=0x8d6e370, packet=0x8d56e81 "",
    packet_length=23) at sql_parse.cc:1532
#8  0x08188b78 in do_command (thd=0x8d6e370) at sql_parse.cc:1340
#9  0x0818800a in handle_one_connection (arg=0x8d6e370) at sql_parse.cc:1059
#10 0xb7e4614b in pthread_start_thread () from /lib/libpthread.so.0
#11 0xb7e461df in pthread_start_thread_event () from /lib/libpthread.so.0
#12 0xb7d7950a in clone () from /lib/libc.so.6
[21 Feb 2005 8:32] Joerg Bruehe
Also see new bug#8638.