469,271 Members | 1,466 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

"Duplicate column name" error when adding column

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
1 8862
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.

Similar topics

reply views Thread by UT-BadBoy | last post: by
5 posts views Thread by Chris Botha | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.