Experienced an error #1060 - Duplicate of Column 'forum_enabled'

Question

What is wrong with this?

ALTER TABLE `groups`
ADD COLUMN `forum_enabled`  enum('0','1') CHARACTER SET latin1 COLLATE 
latin1_swedish_ci NOT NULL DEFAULT '0' AFTER `admindeco`;

ALTER TABLE `user_badges`
ADD UNIQUE INDEX `user_id, badge_id`;

ALTER TABLE `furniture`
ADD COLUMN`behaviour_data`  int(11) NOT NULL DEFAULT 0 AFTER 
`interaction_type`;

ALTER TABLE `users`
MODIFY COLUMN `rank_vip`  int(1) NULL DEFAULT 1 AFTER `rank`;

ALTER TABLE `server_settings`
CHANGE COLUMN `variable` `key`  varchar(255) CHARACTER SET latin1 COLLATE 
latin1_swedish_ci NOT NULL DEFAULT 'server.variable' FIRST ;

    ALTER TABLE `catalog_deals`
    DROP COLUMN `page_id`,
    DROP COLUMN `cost_credits`,
    DROP COLUMN `cost_pixels`,
    MODIFY COLUMN `items`  text CHARACTER SET latin1 COLLATE 
    latin1_swedish_ci 
    NOT NULL AFTER `id`,
    ADD COLUMN `room_id`  int(11) NOT NULL AFTER `name`;

    SET FOREIGN_KEY_CHECKS=0;

I am getting following error:

SQL query: ALTER TABLE groups ADD COLUMN forum_enabled ENUM( '0', '1' ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '0' AFTER admindeco ; MySQL said: Documentation #1060 - Duplicate column name 'forum_enabled'

Are you saying you don't have a column forum_enabled in groups before you issue the alter statement? — P.Salmon, Dec 15 '18 at 14:54
Is that the exact error message? And can you add the output of show create table groups — P.Salmon, Dec 15 '18 at 15:02
This is the exact message: SQL query: ALTER TABLE `groups` ADD COLUMN `forum_enabled` ENUM( '0', '1' ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '0' AFTER `admindeco` ; MySQL said: Documentation #1060 - Duplicate column name 'forum_enabled' — Livetolearn, Dec 15 '18 at 15:15
@Livetolearn it seems there'e already a column called `forum_enabled` in `groups` table — Barbaros Özhan, Dec 15 '18 at 15:20
@P.Salmon Apologies, are you able to advise how I can go about outputting the table groups? — Livetolearn, Dec 15 '18 at 15:24
@BarbarosÖzhan Yes it seems to be the error here yet I can't find the duplicate despite running through the entire SQL... — Livetolearn, Dec 15 '18 at 15:25

GMB · Answer 1 · 2018-12-16T09:46:50.117

From the error message, it seems like the column you are trying to add in the first statement already exists in the table.

A simple solution is to DROP the column before you add it, like :

ALTER TABLE `groups` 
DROP COLUMN `forum_enabled`;

This can be combined with the ADD command in a single statement, like :

ALTER TABLE `groups`
    DROP COLUMN `forum_enabled`,
    ADD COLUMN `forum_enabled`  enum('0','1') ...
;

It is also possible to dynamically check if a column exists, then drop it if neede, like :

IF EXISTS (
    SELECT 1 
    FROM information_schema.columns
    WHERE 
        table_name = 'groups' 
        AND column_name = 'forum_enabled'
        AND table_schema = DATABASE()
) THEN
    ALTER TABLE `groups` 
    DROP COLUMN `forum_enabled`;
END IF;

Experienced an error #1060 - Duplicate of Column 'forum_enabled'

1 Answers1