MySQL error #1060 - Duplicate column name

Databases
1

Hi,

I am having the same problem another user had with trying to update a table. I am trying to run the following command from myPHPadmin:

ALTER TABLE users CHANGE users_key users_key BIGINT UNSIGNED NOT NULL AUTO_INCREMENT

However, I get the following error:

#1060 - Duplicate column name ‘users_key’

I don’t see a duplicate column in the admin or in the structure. Here is the table structure:

CREATE TABLE users (
users_key smallint(6) NOT NULL auto_increment,
users_ID varchar(32) NOT NULL default ‘’,
users_scol_ID varchar(32) NOT NULL default ‘’,
users_first_name tinytext NOT NULL,
users_last_name tinytext NOT NULL,
users_username varchar(12) NOT NULL default ‘’,
users_password varchar(10) NOT NULL default ‘reading’,
users_sec_level tinyint(4) NOT NULL default ‘0’,
users_login timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (users_key,users_key),
UNIQUE KEY users_username (users_username),
UNIQUE KEY users_ID (users_ID),
UNIQUE KEY users_username_2 (users_username),
FULLTEXT KEY users_password_2 (users_password),
FULLTEXT KEY users_password_3 (users_password),
FULLTEXT KEY users_password_4 (users_password),
FULLTEXT KEY users_password_5 (users_password),
FULLTEXT KEY users_password (users_password)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1188 ;

can anyone help me?

Thanks,

SK

2

I think the problem is in assinging your primary key:
PRIMARY KEY (users_key,users_key),
try
PRIMARY KEY users_key(users_key),

if that doesn’t work, drop the primary key, change your column type and re-add the primary key.

3

you’re indexes are a mess! run these commands one at a time to fix it up. note: make sure you have a backup of your table and that you know how to restore it. in your case, restoring a simple table dump won’t work because you have an invalid index. but you will be able to restore it if you edit that index.

lock table `users` write;
alter table `users` change `users_key` smallint(6) NOT NULL;
alter table `users` drop primary key;
alter table `users` add primary key `users_key`;
alter table `users` change `users_key` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
alter table drop index `users_username_2`, drop index `users_username_3`, drop index `users_username_4`, drop index `users_username_5`;
analyze table `users`;
unlock table `users`;