By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,200 Members | 1,568 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,200 IT Pros & Developers. It's quick & easy.

"Duplicate column name" error when adding column

P: n/a
I have two identical databases running on two separate servers. I want
to add a column to the following table:

classified_cats { acid , name , parent }

Running
ALTER TABLE `classified_cats` ADD `is_active` TINYINT( 1 ) DEFAULT '0'
NOT NULL ;
on server A works fine, so I know the SQL is correct. Running the same
query on server B results in the following error:
Duplicate column name 'acid'.

The show create table SQL for server A is as follows:
CREATE TABLE `classified_cats` ( `acid` int(11) NOT NULL
auto_increment, `name` varchar(255) NOT NULL default '', `parent`
int(11) NOT NULL default '0', PRIMARY KEY (`acid`) ) ENGINE=MyISAM
DEFAULT CHARSET=latin1

The show create table SQL for server B is as follows:
CREATE TABLE `classified_cats` ( `acid` int(11) NOT NULL
auto_increment, `name` varchar(255) NOT NULL default '', `parent`
int(11) NOT NULL default '0', PRIMARY KEY (`acid`,`acid`) )
ENGINE=MyISAM DEFAULT CHARSET=latin1

Note the difference in PRIMARY KEY - I'm not sure what (`acid`,`acid`)
means.

Any ideas on how to overcome this?

TIA

Andy

Jan 12 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Found a solution:
1) Dump the table.
2) Recreate with the SQL corrected to ...PRIMARY KEY(`acid`)
3) Source the dump.

Then the ALTER TABLE statement works as expected.

Think it was a problem due to server B being upgraded to MySQL 5.0.

Andy

Jan 12 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.