SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Feb 2003
    Location
    durango
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL error #1060 - Duplicate column name

    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

    If I only had a brain.

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Code:
    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`;


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •