MySQL Bugs: #9870: mysql client hangs after delimiter used

Bug #9870	mysql client hangs after delimiter used
Submitted:	13 Apr 2005 14:07	Modified:	31 May 2005 17:31
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.0.5-beta-debug	OS:	Linux (SUSE 9.2)
Assigned to:	Jani Tolonen	CPU Architecture:	Any

Description:
After some statement combinations, which appear to require the use of a "DELIMITER ..." statement on the client, a hang occurs. From another client I run SHOW PROCESSLIST and see that the hanging client's status is "Sleep".

How to repeat:
Here are two scripts. The first (LONG) one is from a comment that I made on BUG#7185; the second (SHORT) one is from Per-Erik Martin's reply, also a comment on BUG#7185. Probably only the second is significant.

(LONG)

drop database db11;
create database db11;
use db11;
create table avg (s1 int);
insert into avg values (5);
delimiter //
drop table avg//
create table avg (avg int)//
insert into avg (avg) values (5)//
create procedure avg1 (avg int) begin declare avg int; declare
     avg cursor for select
     avg from avg where avg >= 0 order by avg; open avg; fetch avg into avg;
     close avg; end;//
create table avg (s1 int);
     insert into avg values (5);
     delimiter //
drop table avg//
create table avg (avg int)//
insert into avg (avg) values (5)//
create procedure avg1 (avg int) begin declare avg int; declare
     avg cursor for select
     avg from avg where avg >= 0 order by avg; open avg; fetch avg into avg;
     close avg; end;//
drop procedure avg1//
drop table avg//
create table avg (s1 int);
     insert into avg values (5);
delimiter //

(SHORT)

mysql> delimiter //
mysql> create table x (s1 int); insert into x values (5); foobar //
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

(... now hanging...)

My 5.0 client doesn't hang but just ignores delimiter:
kostja@dragonfly:~/work/mysql-5.0-root/sql> .. 
kostja@dragonfly:~/work/mysql-5.0-root> client/mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 5.0.5-beta-valgrind-max-debug
<cut>
mysql> delimiter :;
mysql> show tables: 
    -> 
    -> 
    -> 
    -> 
    ->

This is not a bug, improving documentation into the client help.

delimiter command works like other mysql client specific commands,
it does not require ';' at the end.

delimiter command takes it's argument exactly as it appears, and it
can be a string. So issuing 'mysql> delmiter :;' makes the delimiter
":;" not ":".

The reason why we don't want to 'eat' the possibly last ';' character,
is because 1) It would make it a special character in this regard and
2) when setting a delimiter to for example ';;', one would then
have to run 'delimiter ;;;'.

';;' is good value for a delimiter when issuing multiple statements
and we use it in some examples.

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/24309

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Please read all the test cases, in particular this one:

mysql> delimiter //
mysql> create table x (s1 int); insert into x values (5); foobar //
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

(... now hanging...)

I still think this is a bug.

I was able to reproduce this with 5.0.7 from bk:

Query OK, 0 rows affected (0.00 sec)

mysql> drop table avg//
Query OK, 0 rows affected (0.00 sec)

mysql> create table avg (s1 int);
    ->      insert into avg values (5);
    -> delimiter //
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

---> Hangs here  <----

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

A duplicate of http://bugs.mysql.com/bug.php?id=9992