Bug #46598 MySQL 5.1 resulted in dump files that could not be loaded in MySQL 5.0
Submitted: 7 Aug 2009 8:46 Modified: 11 Aug 2009 15:36
Reporter: Neven Jacmenovic Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.1.37, 5.1.38-bzr OS:Other (Windows x86 and x64 versions - without installer)
Assigned to: CPU Architecture:Any
Tags: mysql windows btree mysqldump

[7 Aug 2009 8:46] Neven Jacmenovic
This bug fix is not applied to latest Windows MySQL versions - distribution without installer - http://bugs.mysql.com/bug.php?id=25162

Jared Sullivan confirmed that this bug is fixed in 5.1.37+installer (latest) version, and I can confirm that is not included in without installer version for both x86 and x64 systems.

Also, why is there no  --compatible=50 switch in mysqldump shipped with MySQL version 5.1?

Thanks in advance!
Best regards

How to repeat:
Install latest 5.1.x windows version (without installer). mysqldump database and try to import it on 5.0.x mysql. You will get error similar to this one:

ERROR 1064 (42000) at line 417: 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 'USING BTREE

The problem was in incompatible syntax for key definition in CREATE TABLE. MySQL 5.0 supports only this syntax:
{INDEX|KEY} [index_name] [index_type] (index_col_name,...)
while MySQL 5.1 preferrs this one :
{INDEX|KEY} [index_name] (index_col_name,...) [index_type]
..which lead to incompatible dump.
[7 Aug 2009 11:45] Valeriy Kravchuk
Recent 5.0.x (starting with 5.0.60) should support both versions of syntax. For older 5.0.x using  --compatible=mysql40 may help in some cases. 

But still, I'd say that  support for --compatible=mysql50 option (and, maybe, mysql50 SQL mode at server level!) is a reasonable feature request for mysqldump/server.
[11 Aug 2009 15:36] Neven Jacmenovic
Hi Valeriy,

Do you know when could bugfix (http://bugs.mysql.com/bug.php?id=25162) be implemented in windows non-installer version of MySQL? This bugfix is implemented in installer version, but not in non-installer.

Best regards