Mysql: USING BTREE – Error when dumping from 5.1 and importing to 5.0

I was dumping a database today to put onto a different server. I am currently running 5.1.45 locally, and the server I am moving to has 5.0.58. When trying to import the database to the new server, I was getting the following error:


ERROR 1064 (42000) at line 576: 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,
KEY `FK_ATTRIBUTE_VARCHAR_ENTITY` (`entity_id`),
KEY `FK_CATALO' at line 9

After doing some checking around, I found that the problem was the ‘USING BTREE’. Mysql 5.0 and lower doesn’t seem to understand what that is about and will fail. The solution: Just remove ‘USING BTREE’, and you’ll be good to go.

It sounds like versions after mine (5.1.45) have fixed this and omit the ‘USING BTREE’ from the dumps.

This entry was posted in MySQL. Bookmark the permalink.

21 Responses to Mysql: USING BTREE – Error when dumping from 5.1 and importing to 5.0

  1. Pingback: Magento auf einen neuen Server umziehen - Typische Fehler - codesprint GmbH

  2. JabeN says:

    Thx a lot…
    Work for me.

  3. Skipper says:

    Thanx a bunch! Solved my problem.

  4. Rakesh says:

    Hello all,

    Can anybody explain the method to edit the query.

    • Gustavo says:

      Open the .sql file with a text editor (Notepad++ could be usefull) and edit it.
      It’s just a plain text file.

  5. ken says:

    thanks! you must be jesus!

  6. jcleroi says:

    Not really need to remove USING BTREE:

    Expl: KEY `idx_usergroup_nested_set_lookup` (`lft`,`rgt`) USING BTREE

    becomes: KEY `idx_usergroup_nested_set_lookup` USING BTREE (`lft`,`rgt`)

  7. Theo says:

    Thanks mate, that was easy!

  8. Tom says:

    Thanks, really.

    • Rakesh says:

      hi Tom

      Can u please let me know the steps as i a newbie in sql field. I’m very grateful to you.

      Regards

  9. Jeff07 says:

    Worked for me.
    thx!

  10. mirko says:

    worked for me (transfering joomla from 5.0.45 to 5.1.44).
    thx!

  11. Thanks, I was really stuck up with this. Trying to import again and again and knowing not whats wrong in my SQL.. Very useful and saved my day..

  12. phingko says:

    Hi josh!
    I just want to know where did u edit the file?
    I seems can’t find to edit the SQL.
    I’m using JOOMLA btw

  13. Pingback: Magento auf einen neuen Server umziehen – Typische Fehler » CodersX Blog

  14. vivy says:

    10x, it worked!!!!
    Bugs cud be buggy, u know?

  15. erosage says:

    Thanks a lot, i encountered the same exact problem with you and i almost lost my sanity finding out what the hell is truly happening

  16. YkudzA says:

    Great thx !

  17. Pingback: Mysql: USING BTREE – Error when dumping from 5.1 and importing to … | Error Resolve | Errors | Software | Hardware | MSSQL | MySQL | .NET | PHP | Asp.NET