Generated MySQL DDL fails to regenerate database

Question

I just used the MySQL mysqldump tool to generate DDL so I could create the sane database on another machine. When I try to run it on the target machine I get this error:

Script line: 28 Key column 'friendPrimaryKeyInYour Phone' doesn't exist in table

running this statement:

/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `friend` (
  `id` bigint(20) unsigned NOT NULL,
  `dependentId` bigint(20) unsigned NOT NULL,
  `friendPrimaryKeyInYourPhone` int(10) unsigned NOT NULL,
  `friendNameInYourPhone` varchar(45) NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`,`friendPrimaryKeyInYourPhone`) USING BTREE,
  UNIQUE KEY `idx_dependendentId_phonePk` (`dependentId`,`friendPrimaryKeyInYour
Phone`),
  KEY `idx_phonePk` (`friendPrimaryKeyInYourPhone`),
  KEY `idx_dependentId` (`dependentId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Any idea how MySQL could generate this DDL and seconds later consider it invalid?

Answer 1

There's a space in friendPrimaryKeyInYour Phone - both in the error message and your SQL. Must be a copy / paste (or some sort of line-wrap) error, remove it and try again.

Answer 2

The problem seems to be with the extra space...

I'm not sure MySQL is at fault here. Could it be the text editor or some other tool used that created this artifact?

Edit: in looking in more details, this split of the field name happens at the 79th character... many text presentation tools using 80 as a standard witdth, this seems to confirm the problem.

Suggestions:

• Look into the very code produced by MySQL's dump. Direct from the "horse's mouth", For observation, open [and never save] with either an hex editor, or various text editor.
• If indeed there is an extra space there, look into the MySQL schema for said database and see if somehow there's not some kind of odd character there....?
• Look in other parts of the script and see if long lines are similarly split

Answer 3

USING BTREE is causing the problem. When I remove that text, it works fine.