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:
None 
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
Triage: Triaged: D5 (Feature request)

[7 Aug 2009 8:46] Neven Jacmenovic
Description:
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
Neven

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
) ENGINE=MyISAM DEFAULT CHARSET=utf8' at line 5

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
Neven