You have an error in your SQL syntax … USING BTREE

Author: seven August 4, 2009

If you see error similar to this one on your MySQL 5.0 while trying to import mysql dump exported on MySQL 5.1 (with mysqldump):
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

...you should upgrade to latest mysql. Bummer!

This is old and confirmed bug #25162. 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.

We have this situation - workstations run MySQL v5.1.33 and our development and production servers are still on 5.0.51a-3ubuntu5.4 (Ubuntu LTS) version. We can't upgrade MySQL on servers that easily so I first tried updating my workstation to latest windows version 5.1.37. mysqldump which comes with it is v10.13 Distrib 5.1.37, and it produced exactly the same incompatible output. I even tried x86 and x64 versions - with same incompatible results. I submited a bug report to MySQL because it seems that this bug was fixed in Windows installer version but not in without-installer version which I used to upgrade my local installation (bug #46598).

This was going nowhere so I downgraded my workstation to MySQL 5.0.84. I was then able to do proper export from my workstation and import on our development server. I have no idea why mysqldump in 5.1 version didn't get --compatible=50 switch option. If somebody knows a workaround for this, do share!

If you cant' upgrade, try exporting from 5.1 with --compatible=mysql40 switch but doing so will heavily downgrade your dump so beware.

Author
seven
CEO/CTO at Nivas®
Neven Jacmenović has been passionately involved with computers since late 80s, the age of Atari and Commodore Amiga. As one of internet industry pioneers in Croatia, since 90s, he has been involved in making of many award winning, innovative and successful online project. He is experienced full stack web developer, analyst and system engineer. In his spare time, Neven is transforming retro-futuristic passion into various golang, Adobe Flash and JavaScript/WebGL projects.

    Leave a Reply

    Your email address will not be published. Required fields are marked *