Bug #64135 Delimiter strips the next new line in a SQL statement
Submitted: 26 Jan 2012 15:09 Modified: 13 Dec 2012 16:06
Reporter: Simon Martin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.5.15, 5.6.5 OS:Any
Assigned to: CPU Architecture:Any
Tags: client, DELIMITER, new line

[26 Jan 2012 15:09] Simon Martin
Description:
If you use "delimiter" at the beginning of a new line within the quotes of a SQL statement the client will remove the next new line:

mysql> CREATE TABLE test (t VARCHAR(255));
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO test VALUES ('A
    '> delimiter
    '> B');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM test;
+--------------+
| t            |
+--------------+
| A
delimiterB |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT 'A
    '> delimiter Some Other Text
    '> B' AS bug;
+------------------------------+
| bug                          |
+------------------------------+
| A
delimiter Some Other TextB |
+------------------------------+
1 row in set (0.00 sec)

Exists in all 5.5 and 5.1 distributed clients on linux and windows which I have tested on.

It also exists if you try to load a SQL file through the client, for example:
$ mysql < my_file.sql

How to repeat:
CREATE TABLE test (t VARCHAR(255));

INSERT INTO test VALUES ('A
delimiter
B');

SELECT * FROM test;

SELECT 'A
delimiter Some Other Text
B' AS bug;
[26 Jan 2012 16:39] Valeriy Kravchuk
Thank you for the problem report. Verified with mysql-trunk on Mac OS X also:

macbook-pro:trunk 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.6.5-m8-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> drop table test;
ERROR 1051 (42S02): Unknown table 'test.test'
mysql> CREATE TABLE test (t VARCHAR(255));
Query OK, 0 rows affected (0.40 sec)

mysql> 
mysql> INSERT INTO test VALUES ('A
    '> delimiter
    '> B');
Query OK, 1 row affected (0.10 sec)

mysql> 
mysql> SELECT * FROM test;
+--------------+
| t            |
+--------------+
| A
delimiterB |
+--------------+
1 row in set (0.01 sec)

mysql> SELECT 'A
    '> delimiter Some Other Text
    '> B' AS bug;
+------------------------------+
| bug                          |
+------------------------------+
| A
delimiter Some Other TextB |
+------------------------------+
1 row in set (0.01 sec)

mysql> exit
macbook-pro:trunk openxs$ bin/mysql --version
bin/mysql  Ver 14.14 Distrib 5.6.5-m8, for osx10.5 (i386) using readline 5.1
[13 Dec 2012 16:06] Paul DuBois
Noted in 5.5.30, 5.6.10, 5.7.1 changelogs.

The mysql client could mishandle the delimiter command if it occurred
on a line during which mysql was looking for the end of a quoted
string.