Magento Database Import Error: USING BTREE, KEY `FK_ATTRIBUTE_VARCHAR_ENTITY` (`entity_id`), KEY `FK_CATALO’ at line 9

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:

<p style="text-align: left; ">UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`,`store_id`) USING BTREE,

and replaced it with this:

<p style="text-align: left; ">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.

View Comments

ceddddd says:

December 4th, 2009 at 8:50 am

Very good information thank you its work

sorry for my english

Michi says:

December 16th, 2009 at 10:24 am

Thank you very much. I thought i’m going crazy on this problem. You saved me a lot of time.

Moe says:

December 24th, 2009 at 5:29 pm

THANKS!! You’re a life saver =)

ahmed says:

February 2nd, 2010 at 11:59 pm

I can’t tell you how much time this saved me! Thanks for the post.

magento verplaatsen van lokaal naar live server says:

February 9th, 2010 at 4:54 am

[...] niet om deze database te uploaden. Na wat googlen op de foutmelding kwam ik op de volgende site Elias Interactive , hier werd uitgelegd wat ik fout deed en vervolgens heb ik dit aangepast en voila de database [...]

nico says:

February 9th, 2010 at 11:37 am

you’re my savior dude..

Rob says:

February 23rd, 2010 at 5:37 am

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…

Lee Taylor says:

February 23rd, 2010 at 2:47 pm

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!

Stéphane says:

March 1st, 2010 at 12:22 pm

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.

Lee Taylor says:

March 1st, 2010 at 4:05 pm

Right on @Stéphane! Thanks for sharing.

Bravehartk2 says:

March 24th, 2010 at 9:24 am

THX! You saved me a lot of time!!!!

diego montero says:

April 10th, 2010 at 1:04 pm

YOU DID IT! Thanks a lot. You saved my life!

choussamaster says:

April 13th, 2010 at 7:31 am

thanks very much this work !

Magento Ecommerce: An Overview and Some Useful Hints « Rosslyn Digital says:

May 10th, 2010 at 12:48 pm

[...] BTREE errors http://www.eliasinteractive.com/blog/magento-database-import-error-using-btree-key-fk_attribute_varc... [...]

Kentaur says:

June 24th, 2010 at 1:58 pm

Ty vm, u saved me!

Fernando says:

June 27th, 2010 at 1:57 am

Many thanks Lee, save-me a lot

Cheers

Margots says:

July 12th, 2010 at 6:43 pm

Another saved soul

Jay says:

July 27th, 2010 at 12:56 pm

You, sir, are a scholar & gentlemen.

This has probably saved me a ton of hassle.

Azhar says:

August 27th, 2010 at 1:26 am

thanks

Leave a comment