| 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: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.2-alpha, for pc-linux | OS: | Linux (Debian Linux (Testing)) |
| Assigned to: | Per-Erik Martin | CPU Architecture: | Any |
[10 Jan 2005 15:45]
MySQL Verification Team
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.
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;