MySQL Bugs: #59534: A too long DELIMITER renders client nonfunctional after 1st statement

Bug #59534	A too long DELIMITER renders client nonfunctional after 1st statement
Submitted:	16 Jan 2011 12:25	Modified:	29 Oct 2013 13:05
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.x	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
(I know the category is probably wrong, but don't find any appropriate)

With a comment between the DELIMITER keyword and the new delimiter defined I see two issues

1) client becomes inoperable. 

2) DELIMITER set includes the comment.

How to repeat:
Two test cases:

1)
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.1.54-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> mysql> delimiter /* anything here */ |
    -> mysql> select 1 |
    ->
Note: the "->" prompt continues forever

2)
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.1.54-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> delimiter /* anything here */ |
mysql> select 1 /* anything here */ |
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

    ->
    -> delimiter ;
    -> select 1;
    ->
Note: the "->" prompt continues forever

In neither case the 'mysql' promt never appears again and the client is non-operable. 

Suggested fix:
As an end user I expect case 1) to set the DELIMITER "|" (only). An in any case the client should return to the 'mysql' prompt after delivering the result "1".

Maybe that Paul (as per a discussion in another ticket a few days old will tell me (but I do not agree it should!) that the DELIMITER gets defined as "/* anything here */ |". But it does not work fully either - the 'mysql' prompt never appears again and the client instance is non-operable.

It is a matter if 'hierarchy of interpretation' in my understanding. I think comments should be 'highest of hierarchy'. But not sure how the client treats such comments (if the are (supposed to be) stripped or not) so this may involve client/server interaction as well.

The other discussion referred to is this one:
http://bugs.mysql.com/bug.php?id=59381

but this works:

mysql> delimiter /*|
mysql> select 1 /*|
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql>

(see 'mysql' prompt returning)

I think that any user should quote delimiter if it contains white spaces. Then it works more or less as expected:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.56-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> delimiter "/* 1 */ //"
mysql> select 1/* 1 */ //
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Not quite. Please see:

mysql> DELIMITER '/*anything-here*/|'
mysql> SELECT 1 /*anything-here*/|
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

    ->
    ->
    ->

1) No WHITESPACE in this DELIMITER. Quoting makes no difference.
2) 'mysql' prompt never appears again.  Client is non-operable (well .. you can press ENTER it you think that is funny - but that is also all you can do! :-) ).

I think the C-style comment and the DELIMITER conflict in the client (or client/server interface) somehow.

And I (probably naively) expected this to work:

DELIMITER /*anything-here*/ |
SELECT 1 |

I agree that your last example demonstrates the problem:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.56-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> delimiter '/*anythinghere*/|'
mysql> select 1/*anythinghere*/|
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

    -> /*anythinghere*/|
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/|' at line 1
    -> /*anythinghere*/|
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/|' at line 1
    -> Ctrl-C -- exit!
Aborted

Looks like one should not use comments in DELIMITER command. This (if intended) should be at least documented.

It looks like it is much more simple.  If DELIMITER is more than 15 characters the 'mysql' prompt does not return in the client. It seems to have nothing to do with /*comment*/

mysql> delimiter /*12345678901*/
mysql> select 1 /*12345678901*/
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> delimiter /*123456789012*/
mysql> select 1 /*123456789012*/
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

    ->

and 

mysql> delimiter 123456789012345
mysql> select 1 123456789012345
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> delimiter 1234567890123456
mysql> select 1 1234567890123456
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

    ->

Not that a 15-character limit is a problem. It is more than enough.  But the client should not malfunction (ie. not return to 'mysql' prompt).  The client should return an error (like it does when "\" is used) if it cannot handle it:

mysql> delimiter \
ERROR:
DELIMITER cannot contain a backslash character
mysql>

(and still is a comment between "DELIMITER" and delimiter valid or not?)

You are right:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.56-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> delimiter aaaaabbbbbccccc
mysql> select 1aaaaabbbbbccccc
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> delimiter aaaaabbbbbcccccd
mysql> select 1aaaaabbbbbcccccd
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

    -> aaaaabbbbbcccccd
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'd' at line 1
    -> Ctrl-C -- exit!
Aborted

Changed synopsis accordingly!

Recategorized to documentation, we just need to document that the maximum size of a delimiter is 15 characters. The delimiter command does not parse comments (or its argument at all), it just takes a string to use as delimiter.

I disagree that this is a documentation issue alone, because if you try to use a longer DELIMITER the client stops functioning.

Also # and -- style comments cannot be used on same line as DELIMITER is defined. 

mysql> delimiter | #testing
mysql> select 1 | #testing
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

It is not a big deal, but could require a note from the keyword DELIMITER to end of line comments are not possible as everything after "R" in "DELMITER" (except for WHITESPACE in first position(s) after "R") will simply be included in the delimiter. This is not obvious.

It doesn't stop working, it just silently truncates the delimiter to the maximum length. Also, again, DELIMITER just takes a string, it does not parse it in any way, it will take anything.

1) From user's point of view it stops working.

DELIMITER 1234567890123456 

-- should return an error what is *not* a documentation issue.  'Silent truncation' is a bug. It should not be 'silent' - but 'verbose'.

2) 
http://dev.mysql.com/doc/refman/5.1/en/comments.html

claims that "#" and "-- " and /* ... */ identify a comment what is *not* the case if the keyword DELIMITER occurs earlier on same line. This exception is *nowhere* listed. I know that this is server-level docs and DELIMITER is client-side implementation. 

I think client docs should make it clear that "you cannot use comments on same line after DELIMITER keyword as everything from the DELIMITER keyword to end of line (with the exception of WHITESPACE immediately following the DELIMITER keyword) will be included in the new DELIMITER.

1) Not if documented. But, sure, we can leave this bug here for eternity.

2) Is quite irrelevant. If we follow this line of thought, we will end up documenting everything that is not obvious for you. Again, DELIMITER takes a string as-is, this implies that there is no concept of comments, or anything else, it takes anything that forms a string.

@Davi I do not claim it is a big issue. People will not use such long delimiter.  But I do not find how I can make the client functional again after

1)
delimiter 1234567890123456
select 1 1234567890123456

2)
delimiter | -- some stupid comment here
select 1 | -- some stupid comment here

How do I get the 'mysql' prompt back in the client?

From the comments, this seems to be expected behavior.