MySQL server version for the right syntax to use near ‘USING BTREE
星期二, 7月 6th, 2010mysqldump 经常会遇到版本不一致引起的导入到处问题， 本文给出了参考解决方法。
http://www.magento-vietnam.com/magento-tips-tricks/magento-database-import-error/
Magento Database Import Error
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.
