What is wrong with this mysql create table query?

Question

I have a database in my production server and it works fine... What i did is took a dump of that DB and executed in my local system.. All the other tables are created except one table... So i manually inserted it,

CREATE TABLE `contact` (
  `contactId` int(11) NOT NULL AUTO_INCREMENT,
  `cRefId` int(20) DEFAULT '0',
  `contactFirstName` varchar(100) DEFAULT NULL,
  `contactLastName` varchar(100) DEFAULT NULL,
  `contactPhone` varchar(35) DEFAULT NULL,
  `contactEmail` varchar(150) DEFAULT NULL,
  `organizationid` int(11) NOT NULL,
  `mobileNo` varchar(35) DEFAULT NULL,
  `dor` datetime DEFAULT NULL,
  `doe` datetime DEFAULT NULL,
  `dod` datetime DEFAULT NULL,
  `designation` varchar(50) DEFAULT NULL,
  `income` double DEFAULT NULL,
  `homeloan` tinyint(1) DEFAULT NULL,
  `companyName` varchar(200) DEFAULT NULL,
  `isDeleted` tinyint(1) DEFAULT '0',
  `addressId` int(11) DEFAULT NULL,
  `accgroupid` int(11) DEFAULT NULL,
  `createdBy` int(11) DEFAULT NULL,
  `editedBy` int(11) DEFAULT NULL,
  `deletedBy` int(11) DEFAULT NULL,
  `assignedto` int(11) DEFAULT NULL,
  `industryid` int(11) DEFAULT NULL,
  `note` varchar(150) DEFAULT NULL,
  `twirrerId` varchar(150) DEFAULT NULL,
  `linkedinId` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`contactId`),
  KEY `aa` (`organizationid`),
  KEY `add_id` (`addressId`),
  KEY `idx_contactid` (`contactId`),
  KEY `FK_contact` (`industryid`),
  KEY `fk_contacteditedby_user` (`editedBy`),
  KEY `fk_contactaccount_account` (`accgroupid`,`contactId`),
  KEY `contact_First_Name` (`contactFirstName`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

But when i execute this i get the following error,

Error Code : 1064
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 AUTO_INCREMENT=2 DEFAULT CHARSET=latin1' at line 35

Answer 1

Are you sure you are using a version of MySQL where 'USING BTREE' is supported? If I recall correctly, that's a MySQL 5.1 feature.

You could also have hit http://bugs.mysql.com/bug.php?id=25162

Edit: Actually, it's just a MySQL 5 feature.

Answer 2

I'm pretty certain you have to specify the using before the index column that you're using:

... KEY `contact_First_Name` USING BTREE (`contactFirstName`)

The doco referenced in that link states (MySQL 5.1):

{INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ...

and [index_type] is the using btree bit.

Failing that, check that you have compatible versions on the source and destination server. There was a patch which made the interpretation of that command a little more robust in terms of what order they should be in.

Or you could remove the using btree altogether and use the server default method though you should understand the ramifications of that before choosing it as an option.

Answer 3

You should use the same version of mysql in your development and production systems (except when testing a mysql upgrade, of course).

Using a different version invalidates testing. Don't do it. Your development system should run the exact same server build as you use in production, the only things which should be different are parameters which need to be in order for it to work (say you have 32G of ram in production, but only 4G on your test server, you need to make the buffers smaller there)