MySQL Bugs: #49649: mysqldump throws error Couldn't execute '/*!40100 SET @@SQL

Bug #49649	mysqldump throws error Couldn't execute '/*!40100 SET @@SQL_MODE=''
Submitted:	13 Dec 2009 20:31	Modified:	10 Jan 2010 12:51
Reporter:	Bernhard Blasen	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	version 5.1.40	OS:	Linux (kernel 2.4.22, gcc)
Assigned to:		CPU Architecture:	Any
Tags:	40100, error, mysqldump

Description:
installed mysql version 5.1.41 from source. everything is ok except mysqldump throwing error:

# mysqldump -uroot -p dbname > dbname.sql
Enter password: 
mysqldump: Couldn't execute '/*!40100 SET @@SQL_MODE='' */': 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 '!40100 SET @@SQL_MODE='' */' at line 1 (1064)

# mysqldump -V
mysqldump  Ver 10.13 Distrib 5.1.41, for pc-linux-gnu (i686)

#mysql -V
mysql  Ver 14.14 Distrib 5.1.41, for pc-linux-gnu (i686) using  EditLine wrapper
# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 212
Server version: 5.1.41-log Source distribution

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

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.41-log          |
| version_comment         | Source distribution |
| version_compile_machine | i686                |
| version_compile_os      | pc-linux-gnu        |
+-------------------------+---------------------+
5 rows in set (0,00 sec)

mysql>

How to repeat:
installing from source

Compiler version:
# g++ -v
Reading specs from /usr/lib/gcc-lib/i586-mandrake-linux-gnu/3.3.2/specs
Configured with: ../configure --prefix=/usr --libdir=/usr/lib --with-slibdir=/lib --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --enable-long-long --enable-__cxa_atexit --enable-clocale=gnu --enable-languages=c,c++,ada,f77,objc,java,pascal --host=i586-mandrake-linux-gnu --with-system-zlib
Thread model: posix
gcc version 3.3.2 (Mandrake Linux 10.0 3.3.2-6mdk)

I am getting curious about what happened to mysqldump!

I would try all the 4 statements
* SET @@SQL_MODE=''
* SET SQL_MODE=''
* SET SESSION SQL_MODE=''
* SET @@SESSION.SQL_MODE=''
(they all work fine on my Windows environment with MySQL 5.1.41)

.. from command line or any client. 

http://dev.mysql.com/doc/refman/5.1/en/set-option.html says

"To indicate explicitly that a variable is a session variable, precede its name by SESSION, @@session., or @@. Setting a session variable requires no special privilege, but a client can change only its own session variables, not those of any other client."

and

"If no modifier is present, SET changes the session variable"

and 

"The @@var_name syntax for system variables is supported for compatibility with some other database systems."

hmmm .. if this syntax is only "supported for compatibility with some other database systems" I do not think 'mysqldump' should use it.  But no idea if the @@var_name syntax could possible be non-functional on your self-built environment (and why not use a generic RPM?).

I believe that all mysqldumps I have seen (I have never used mysqldump with MySQL 5.1.41) use SET @@SESSION.SQL_MODE= syntax (but they do no set empty mode for the session).

Peter
(not a MySQL person)

this one: 
http://wiki.mandriva.com/en/Releases/Mandrake/10.0 ?

Thank you for the report.

Please indicate which configure options and flags did you use when compile MySQL server.

configure statement was

./configure --prefix=/usr/local/mysql --with-collation=utf8_general_ci --with-charset=utf8

very curious:

# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 270
Server version: 5.1.41-log Source distribution

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

mysql> SET @@SQL_MODE='' ;
Query OK, 0 rows affected (0,01 sec)

mysql> 

to make sure that there's no different mysqldump program somewhere:

# which mysqldump
/usr/local/bin/mysqldump
# ls -l /usr/local/bin/mysqldump
lrwxrwxrwx    1 root     root           30 Dez 13 20:19 /usr/local/bin/mysqldump -> /usr/local/mysql/bin/mysqldump

Thank you for the feedback.

I still can not repeat described behavior.

Where did you download MySQL sources? From http://dev.mysql.com/downloads?

Please also provide output of SELECT @@global.sql_mode;

Download url was 
http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.41.tar.gz/from/http://ftp.gwdg.de/p...

# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 312
Server version: 5.1.41-log Source distribution

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

mysql> SELECT @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0,00 sec)

Thank you for the feedback.

We still can not repeat described behavior.

Please try mysqldump from one of our binary packages located at http://dev.mysql.com/downloads and if problem still exists check with help of MySQL Proxy and general query log what mysqldump really sends.

With general log:

1. Turn general query log to ON, choose output to FILE
2. Run mysqldump
3. Send us content of mysq general log file
4. Restore general query log settings

With MySQL Proxy:

1. Create file read_query.lua:

$cat read_query.lua 
function read_query( packet )
        if packet:byte() == proxy.COM_QUERY then
                print("we got a normal query:__" .. packet:sub(2) .. "__")
        end
end

2. Start MySQL Proxy with option: mysql-proxy --proxy-lua-script=/path/to/read_query.lua You can redirect output to some file
3. Run mysqldump command using proxy (mysqldump -h127.0.0.1 -P4040 ...)
4. Send us MySQL Proxy output.

Probably would be good if you use 2 options to be sure MySQL server gets same query as MySQL Proxy (==query is not corrupted in the way to server)

sorry for the delay - was on holiday some days.

Tried binary package - mysqldump is ok!

Don't know anything about mysql-proxy. Cannot find a program named mysql-proxy in the tree generated by make install.

So I don't know how to make your second proposal.....

Bernhard

using binary installation I faced a different problem:

# mysql -uroot -p
mysql: error while loading shared libraries: libncursesw.so.5: cannot open shared object file: No such file or directory

Bernhard

Thank you for the feedback.

Good to know mysqldump works for you. Closing report as "Can't repeat" because of this reason.

MySQL Proxy is separate product, but it is not needed as problem is solved.

Regarding to error 'mysql: error while loading shared libraries: libncursesw.so.5: cannot open shared object file: No such file or directory" check your Curses installation.