Error 1060 on backup restore

[philmcdonnell]

Hello,

I am getting the following error when I restore a clients backup from one DA server to a new one.

Unable to restore database database.sql to database : ERROR 1060 (42S21) at line 12 in file: '/home/user/backups/backup/database.sql': Duplicate column name 'ID'

Any Ideas?

Phil

 

[webquarry]

I know that this has something to do with going from mysql 4.0 to 4.1 or newer but I haven't yet figured out how to solve it.

Anyone else?

 

[sde]

it sounds like there is data in the database already. column 'ID' is probably a unique field and that ID probably already exists.

is the table dropped or empty before the sql file is being loaded?

if the table is definately empty, maybe look at line 12 and see if any lines before it have the same ID.

 

[webquarry]

Yeah that is going into an empty table.

The very same dump file WILL import into mysql 4.0 just fine but generates the above mentioned error when imported into 4.1 or later.

Any other ideas?

 

[webquarry]

Here is a little more data for you:

mysqldump from version 4.0 is describing some tables like this one:

--
-- Table structure for table `mappar`
--

DROP TABLE IF EXISTS `mappar`;
CREATE TABLE `mappar` (
  `Fid` int(11) NOT NULL auto_increment,
  `Knamn` varchar(15) NOT NULL default '',
  `Sokvag` varchar(75) NOT NULL default '',
  `Tillhor` int(11) default NULL,
  `Kom` text NOT NULL,
  `Datum` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`Fid`,`Fid`,`Fid`,`Fid`)
) TYPE=MyISAM COMMENT='Mappar';

Note the "PRIMARY KEY" line. Changing that line to:

PRIMARY KEY  (`Fid`)

will allow the import to proceed without error in 4.1 and 5.0. The dumpfile will import into 4.0 no matter how the PRIMARY KEY line looks.

Anyone have any idea what is causing this behavior?

 

[sde]

a guess is that the later versions are just a bit more strict. why would a primary key be defined 4 times?

 

[webquarry]

why would a primary key be defined 4 times?

I have no idea. Perhaps a bug in earlier versions of mysqldump? I can think of no reason to define it like that.

The odd thing is that the dumpfile can be imported into version 4.0 without any problem but you have to remove the multiple key definitions to get it to import into 4.1 or later. If you DESCRIBE the table in any version, the description is reported identically.