Bug #8071 Mysqldump fails and tries to execute the dump!!
Submitted: 21 Jan 2005 15:42 Modified: 21 Jan 2005 17:46
Reporter: Ben Dugdale Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S1 (Critical)
Version:4.1.8 OS:Linux (Gentoo GNU/Linux)
Assigned to: CPU Architecture:Any

[21 Jan 2005 15:42] Ben Dugdale
Description:
Mysqldump started failing after upgrading to 4.1.8 from 4.0.22.  The server stayed the same, and is 4.0.18-nt

It also seems that mysqldump is trying to execute part of the dump!

root@dugdale ~
 <>< mysql --version
mysql  Ver 14.7 Distrib 4.1.8, for pc-linux-gnu (i686)

root@dugdale ~
 ><> mysqldump -Q --opt -B cerberus -h obfuscated.domain -u cerberus -p > cerberus.sql
Enter password:
mysqldump: mysqldump: Couldn't execute 'SHOW CREATE DATABASE IF NOT EXISTS `cerberus`': 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 'DATABASE IF NOT EXISTS `cerberus`' at line 1 (1064)

root@dugdale ~
 <>< which mysqldump
/usr/bin/mysqldump

root@dugdale ~
 ><> qpkg -f /usr/bin/mysqldump
dev-db/mysql *

root@dugdale ~
 ><> uname -a
Linux dugdale 2.6.10-gentoo-r4y #2 SMP Tue Jan 11 14:50:41 MST 2005 i686 Intel(R) Pentium(R) 4 CPU 2.60GHz GenuineIntel GNU/Linux
root@dugdale /var/log/mysql
 
><> mysql -h obfuscated.domain -u cerberus -p cerberus
Enter password:
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 2740 to server version: 4.0.18-nt

How to repeat:
root@dugdale ~
 <>< mysql --version
mysql  Ver 14.7 Distrib 4.1.8, for pc-linux-gnu (i686)

root@dugdale ~
 <>< which mysqldump
/usr/bin/mysqldump

root@dugdale ~
 ><> qpkg -f /usr/bin/mysqldump
dev-db/mysql *

root@dugdale ~
 ><> uname -a
Linux dugdale 2.6.10-gentoo-r4y #2 SMP Tue Jan 11 14:50:41 MST 2005 i686 Intel(R) Pentium(R) 4 CPU 2.60GHz GenuineIntel GNU/Linux
root@dugdale /var/log/mysql
 
><> mysql -h obfuscated.domain -u cerberus -p cerberus
Enter password:
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 2740 to server version: 4.0.18-nt
<snip>
mysql> quit
Bye

root@dugdale ~
 ><> mysqldump -Q --opt -B cerberus -h obfuscated.domain -u cerberus -p > cerberus.sql
Enter password:
mysqldump: mysqldump: Couldn't execute 'SHOW CREATE DATABASE IF NOT EXISTS `cerberus`': 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 'DATABASE IF NOT EXISTS `cerberus`' at line 1 (1064)
[21 Jan 2005 15:56] Aleksey Kishkin
Could you try --compability= option for mysqldump?

http://dev.mysql.com/doc/mysql/en/mysqldump.html
[21 Jan 2005 16:38] Ben Dugdale
From --help:
  --compatible=name   Change the dump to be compatible with a given mode. By
                      default tables are dumped in a format optimized for
                      MySQL. Legal modes are: ansi, mysql323, mysql40,
                      postgresql, oracle, mssql, db2, maxdb, no_key_options,
                      no_table_options, no_field_options. One can use several
                      modes separated by commas. Note: Requires MySQL server
                      version 4.1.0 or higher. This option is ignored with
                      earlier server versions.

The server version is 4.0.18-nt

I treid it anyway like: 

<>< mysqldump -Q --opt --compatible=mysql40 -B cerberus -h obfuscated.domain -u cerberus -p > cerberus.sql
Enter password:
mysqldump: mysqldump: Couldn't execute 'SHOW CREATE DATABASE IF NOT EXISTS `cerberus`': 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 'DATABASE IF NOT EXISTS `cerberus`' at line 1 (1064)

Same results.
[21 Jan 2005 17:46] Aleksey Kishkin
Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

run server 4.0 in one window, run mysqldump from distribution 4.1 in another window. No error happened. If you have another ideas how to reproduce this error, please let us know.

/usr/local/mysql41/bin/mysqldump -Q --opt -B test
-- MySQL dump 10.9
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       4.0.24-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

etc etc