Column already exists 1060 Duplicate column name

Column already exists 1060 Duplicate column name

Update failure: Column already exists: 1060 Duplicate column name.

A typical error would look like:

Updating to version 1.3.6.1 failed with:
CDbCommand failed to execute the SQL statement:
SQLSTATE[42S21]: Column already exists: 1060
Duplicate column name 'must_confirm_delivery'

This happens because you have ran an update previously and it failed for whatever reason, most likely if ran it from the web interface and the database connection timed out.
In order to fix this and continue with the update, we should open the file: apps/common/data/update-sql/1.3.6.1.sql from the server and look for the sql query that shows the 'must_confirm_delivery' column, remove the entire sql statement, save the file and try to run the update command once again.
In this example, the sql file looks like:

--
-- Update sql for AppMail EMA from version 1.3.6.0 to 1.3.6.1
--

--
-- Table delivery_server
--
ALTER TABLE `delivery_server` ADD `must_confirm_delivery` ENUM('yes','no') NOT NULL DEFAULT 'no' AFTER `force_reply_to`;

--
-- Table campaign_delivery_log
--
ALTER TABLE `campaign_delivery_log` ADD `delivery_confirmed` ENUM('yes','no') NOT NULL DEFAULT 'yes' AFTER `email_message_id`;
ALTER TABLE `campaign_delivery_log` ADD `server_id` INT(11) NULL DEFAULT NULL AFTER `subscriber_id`;
ALTER TABLE `campaign_delivery_log` ADD INDEX `fk_campaign_delivery_log_delivery_server1_idx` (`server_id`);
ALTER TABLE `campaign_delivery_log` ADD CONSTRAINT `fk_campaign_delivery_log_delivery_server1` FOREIGN KEY (`server_id`) REFERENCES `delivery_server` (`server_id`) ON DELETE SET NULL ON UPDATE NO ACTION;

--
-- Table campaign_delivery_log_archive
--
ALTER TABLE `campaign_delivery_log_archive` ADD `delivery_confirmed` ENUM('yes','no') NOT NULL DEFAULT 'yes' AFTER `email_message_id`;
ALTER TABLE `campaign_delivery_log_archive` ADD `server_id` INT(11) NULL DEFAULT NULL AFTER `subscriber_id`;
ALTER TABLE `campaign_delivery_log_archive` ADD INDEX `fk_campaign_delivery_log_archive_delivery_server1_idx` (`server_id`);
ALTER TABLE `campaign_delivery_log_archive` ADD CONSTRAINT `fk_campaign_delivery_log_archive_delivery_server1` FOREIGN KEY (`server_id`) REFERENCES `delivery_server` (`server_id`) ON DELETE SET NULL ON UPDATE NO ACTION;

So we look for the sql statement that contains the `must_confirm_delivery` wording and we remove it, so now the update file will look like:

--
-- Update sql for AppMail EMA from version 1.3.6.0 to 1.3.6.1
--

--
-- Table campaign_delivery_log
--
ALTER TABLE `campaign_delivery_log` ADD `delivery_confirmed` ENUM('yes','no') NOT NULL DEFAULT 'yes' AFTER `email_message_id`;
ALTER TABLE `campaign_delivery_log` ADD `server_id` INT(11) NULL DEFAULT NULL AFTER `subscriber_id`;
ALTER TABLE `campaign_delivery_log` ADD INDEX `fk_campaign_delivery_log_delivery_server1_idx` (`server_id`);
ALTER TABLE `campaign_delivery_log` ADD CONSTRAINT `fk_campaign_delivery_log_delivery_server1` FOREIGN KEY (`server_id`) REFERENCES `delivery_server` (`server_id`) ON DELETE SET NULL ON UPDATE NO ACTION;

--
-- Table campaign_delivery_log_archive
--
ALTER TABLE `campaign_delivery_log_archive` ADD `delivery_confirmed` ENUM('yes','no') NOT NULL DEFAULT 'yes' AFTER  `email_message_id`;
ALTER TABLE `campaign_delivery_log_archive` ADD `server_id` INT(11) NULL DEFAULT NULL AFTER `subscriber_id`;
ALTER TABLE `campaign_delivery_log_archive` ADD INDEX `fk_campaign_delivery_log_archive_delivery_server1_idx` (`server_id`);
ALTER TABLE `campaign_delivery_log_archive` ADD CONSTRAINT `fk_campaign_delivery_log_archive_delivery_server1` FOREIGN KEY (`server_id`) REFERENCES `delivery_server` (`server_id`) ON DELETE SET NULL ON UPDATE NO ACTION;

We save the file and run the update command once again.
We follow all the above steps each time the application complaints about a column already existing in the database.
Once it stops complaining, the update process will continue and finish without errors.

    • Related Articles

    • This license is already registered to another AppMail instance

      This article is about the following error message that you get in your AppMail dashboard: {"errors":{"license_key":"This license is already registered to another AppMail instance"},"message":"General error","status":"error"} Why do I get this error? ...

    • Correct CSV file format for import

      When importing subscribers into AppMail, you can do it from text files, csv files or external SQL database. If you decide to do it from csv files, your csv file has to contain proper header columns and one of the columns must be named email. If you ...

    • Update failure - Missing database table or columns

      You can get an error like: The table '{{table_name}}' for active record class 'ModelName' cannot be found in the database. Which means a database table is missing from your app, or you can get an error where the app complaints about a missing ...

    • Where is my subscription page?

      In AppMail, each email list has its own pages that you can edit and customize according to what your list represent. You can view your email list by logging into AppMail as a customer and clicking the Lists link from the left side menu. Once you ...

    • Enable the use of temporary queue tables when sending campaigns

      The main issue we have with huge AppMail apps is that the campaign_delivery_log table becomes huge (I’ve seen installs with hundred GB’s of data in that table alone) and we use that table to join against when we send the campaigns so that we can see ...