Tonight I was setting up a staging environment for a client of ours so they would have the ability to “visually” interact with the recent data migration our team has performed (OSCommerce to Magento, in case anyone was wondering).
Here’s the error I was receiving:
ERROR 1064 (42000) at line 382: 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
The reason behind this is that there is an inconsistency in the way the export syntax was being handled as I imported into the staging environment (which evidently has a different MySQL version running). Thus, the staging site was expecting different syntax for the following:
-- -- Table structure for table `catalog_category_entity_varchar` -- DROP TABLE IF EXISTS `catalog_category_entity_varchar`; CREATE TABLE `catalog_category_entity_varchar` ( `value_id` int(11) NOT NULL AUTO_INCREMENT, `entity_type_id` smallint(5) unsigned NOT NULL DEFAULT '0', `attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0', `store_id` smallint(5) unsigned NOT NULL DEFAULT '0', `entity_id` int(10) unsigned NOT NULL DEFAULT '0', `value` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`value_id`), UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`,`store_id`) USING BTREE, KEY `FK_ATTRIBUTE_VARCHAR_ENTITY` (`entity_id`), KEY `FK_CATALOG_CATEGORY_ENTITY_VARCHAR_ATTRIBUTE` (`attribute_id`), KEY `FK_CATALOG_CATEGORY_ENTITY_VARCHAR_STORE` (`store_id`) ) ENGINE=InnoDB AUTO_INCREMENT=697 DEFAULT CHARSET=utf8;
As a rule of thumb, one can either
- (1) head over to the MySQL Developer Documentation for syntax reference
- (2) or simply check out a recent Magento export from the server they are trying to import into and determine how the “USING BTREE” statement was handled. Likewise, I simply removed the following:
UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`,`store_id`) USING BTREE,
and replaced it with this:
UNIQUE KEY `IDX_BASE` USING BTREE (`entity_type_id`,`entity_id`,`attribute_id`,`store_id`),
Hope this helps someone who is experiencing the same error and spending way too much time trying to understand the incompatible syntax.
Very good information thank you its work
sorry for my english 🙂
Thank you very much. I thought i’m going crazy on this problem. You saved me a lot of time.
THANKS!! You’re a life saver =)
I can’t tell you how much time this saved me! Thanks for the post.
you’re my savior dude..
Wow, this really saved me some time! I ran into the exact same thing with my Magento store. Thanks a lot for writing this, I would have spent a load of time trying to figure this out…
Great to hear @Rob! Very glad it helped. Feel free to share any findings with us that you deem to be useful for others as well.
Cheers!
Just in case you would like more information about this issue, here is the MySQL bug report: http://bugs.mysql.com/bug.php?id=25162. If you have MySQL 5.0.60 and greater (or 5.1.24 and greater), everything should be fine.
Right on @Stéphane! Thanks for sharing.
THX! You saved me a lot of time!!!!
YOU DID IT! Thanks a lot. You saved my life!
thanks very much this work !
Ty vm, u saved me!
Many thanks Lee, save-me a lot
Cheers
Another saved soul
You, sir, are a scholar & gentlemen.
This has probably saved me a ton of hassle.
thanks
thanks
great job man!!
Thanks a lot! Just saved me a lot of time!
Did you ever think about putting flattr on your site? I would definitely flattr this post.
Thanx a lot man, saved me some nice debugging 🙂 kudo’s
Sir, you are an absolute hero. Thank the heavens for google, and yourself. This is why the world is awesome 🙂
Thank you! worked like a charm. FYI i was moving my db from mysql version 5.1 to 5.0. I haven’t had that problem moving from version 5.0 to 5.1
Thank you so much!
You turned a nightmare of a day in to a good one, very much appreciated
Brilliant. The feeling when I got the error was washed away by your simple and easy to implement solution. Life saver
Awesome, thank you very much for this tip.
Thanks
You are a genius! Worked on this for ages!
Top drawer, sir. Many tanks.
Very helpful tip! Thanks a lot, Lee! I was very confusing when I couldn’t restore Joomla database from our testing env (Debian Squeeze with MySQL 5.1.49-3) to production env (Debian Lenny with MySQL 5.0.51a-24+lenny4).
Thank youuuuuuuuuuuuuu 🙂