MySQL Bugs: #64104: Command line client fails valid query; works with php client

Bug #64104	Command line client fails valid query; works with php client
Submitted:	23 Jan 2012 2:11	Modified:	7 Feb 2013 14:55
Reporter:	Radu Dan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.6.4-m7	OS:	Windows (win7sp1-x64, XP x64)
Assigned to:		CPU Architecture:	Any
Tags:	client, multi-line

Description:
The client fails with a strange error message when sending multi-line queries generated by "show create table":

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 'm ENG
INE=InnoDB DEFAULT CHARSET=utf8' at line 12

Considering there is no 'm' before ENGINE in the query, I'm guessing it's a string handling issue.
Same query ran fine via php's mysql_query() function.
For what it's worth:

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 1.2.4                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.4-m7                     |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86                          |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+

How to repeat:
CREATE TABLE `errors` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `type` int(11) NOT NULL DEFAULT '0',
  `message` text,
  `file` text,
  `line` int(10) unsigned NOT NULL DEFAULT '0',
  `context` mediumblob,
  `stack` text,
  `time` int(11) NOT NULL DEFAULT '0',
  `query` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Probably something Windows-specific. Work on Mac for me:

macbook-pro:trunk openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
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> CREATE TABLE `errors` (
    ->   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->   `type` int(11) NOT NULL DEFAULT '0',
    ->   `message` text,
    ->   `file` text,
    ->   `line` int(10) unsigned NOT NULL DEFAULT '0',
    ->   `context` mediumblob,
    ->   `stack` text,
    ->   `time` int(11) NOT NULL DEFAULT '0',
    ->   `query` text,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.12 sec)

Please, send the output of

show variables like 'char%';

As requested:

mysql> show variables like 'char%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                    |
| character_set_connection | utf8                                                    |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                    |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.6\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)

I tried to copy / paste the query as displayed on this bug tracker, and it worked, so I now believe that this issue is related to newlines. As a matter of fact, the query came from the linux version of the mysql client, piped to putty via ssh. Maybe the windows client complains about the missing \r character, or maybe this is not at all related to mysql, but to the way windows handles pasting unix newlines into the terminal. In any case, I will upload a text file containing the query, as generated by the linux cli.

unix newline version of the query

Attachment: query.txt (text/plain), 377 bytes.

I can confirm the issue.  The query fails on 5.6 command line clients (both the 'ANSI' version and the 'unicode' version from start menu) on Win 7/64.  

On 5.5 there is no such error.

The query also works fine with an application compiled with a late 5.0x C-API and MySQL server 5.6x.

I have no clue if the error would arise with a C application compiled with the API source code shipped with 5.6 (or statically linking libmysql.dll ver. 5.6x).
But it looks like a *pure* command-line client issue.

This is more serious than 'S3' IMHO.

I can not repeat this on 64-bit Windows XP. Please, explain all steps to do with query.txt uploaded to get the problem.

Uh, download the file & open with notepad (or just open with firefox) and copy / paste the query into the mysql console window.
You should get the error. I just tried source file.txt and that worked, so I believe the error is STRICTLY related to copying and pasting unix line delimiters to the terminal.

I had verified this by copying content of the file uploaded from notepad.exe into the Unicode version of client. Pasting from IE works properly.

Noted in 5.6.5 changelog.

On Windows, pasting multiple-line input including a CRLF terminator
on the last line into the mysql client resulted in the first
character of the last line being changed, resulting in erroneous
statements. Handling of newlines in pasted input was also incorrect.