Mautic Community Forums

Database upgrade failure

Hello,

I am new to Mautic and having a database problem. In attempting to upgrade from 2.5.1 to 2.6.1 the file upgrade worked OK but the database upgrade failed. I went back to 2.5.1 from backups and then attempted to upgrade/repair the database using http://mymauticsite.com/s/upgrade/schema but this also failed.

the log shows as below but i am unable to interpret the log to identify and fix the problem.

Perhaps someone can help?

Thanks



Log Entries:



[2017-03-06 19:18:15] mautic.NOTICE: DoctrineDBALExceptionDriverException: An exception occurred while executing ‘ALTER TABLE mauyu_campaign_events ADD channel VARCHAR(255) DEFAULT NULL, ADD channel_id INTEGER DEFAULT NULL, ADD INDEX mauyu_campaign_event_channel (channel, channel_id), DROP INDEX mauyu_campaign_event_type_search, ADD INDEX mauyu_campaign_event_search (type, event_type), DROP INDEX mauyu_event_type, ADD INDEX mauyu_campaign_event_type (event_type)’: Can’t DROP ‘mauyu_campaign_event_type_search’; check that column/key exists (uncaught exception) at /home/mysite/public_html/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 115 while running console command doctrine:migrations:migrate [] []

[2017-03-06 19:18:15] mautic.ERROR: [UPGRADE ERROR] Exit code 1; Mautic Migrations Migrating up to 20170127205928 from 20161124145649 ++ migrating 20161026202839 -> CREATE TABLE mauyu_message_channels ( id INT AUTO_INCREMENT NOT NULL, message_id INT NOT NULL, channel VARCHAR(255) NOT NULL, channel_id INT NULL, properties LONGTEXT NOT NULL COMMENT ‘(DC2Type:json_array)’, is_enabled TINYINT(1) NOT NULL, INDEX IDX_118ADA5E537A1329 (message_id), INDEX mauyu_channel_entity_index (channel, channel_id), INDEX mauyu_channel_enabled_index (channel, is_enabled), UNIQUE INDEX mauyu_channel_index (message_id, channel), PRIMARY KEY(id) ) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; -> CREATE TABLE mauyu_messages ( id INT AUTO_INCREMENT NOT NULL, category_id INT DEFAULT NULL, is_published TINYINT(1) NOT NULL, date_added DATETIME DEFAULT NULL COMMENT ‘(DC2Type:datetime)’, created_by INT DEFAULT NULL, created_by_user VARCHAR(255) DEFAULT NULL, date_modified DATETIME DEFAULT NULL COMMENT ‘(DC2Type:datetime)’, modified_by INT DEFAULT NULL, modified_by_user VARCHAR(255) DEFAULT NULL, checked_out DATETIME DEFAULT NULL COMMENT ‘(DC2Type:datetime)’, checked_out_by INT DEFAULT NULL, checked_out_by_user VARCHAR(255) DEFAULT NULL, name VARCHAR(255) NOT NULL, description LONGTEXT DEFAULT NULL, publish_up DATETIME DEFAULT NULL COMMENT ‘(DC2Type:datetime)’, publish_down DATETIME DEFAULT NULL COMMENT ‘(DC2Type:datetime)’, INDEX IDX_3D41E7812469DE2 (category_id), INDEX mauyu_date_message_added (date_added), PRIMARY KEY(id) ) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; -> ALTER TABLE mauyu_message_channels ADD CONSTRAINT FK_118ADA5E537A1329 FOREIGN KEY (message_id) REFERENCES mauyu_messages (id) ON DELETE CASCADE -> ALTER TABLE mauyu_messages ADD CONSTRAINT FK_3D41E7812469DE2 FOREIGN KEY (category_id) REFERENCES mauyu_categories (id) ON DELETE SET NULL ++ migrated (0.35s) ++ migrating 20161122215214 -> ALTER TABLE mauyu_push_notifications ADD button LONGTEXT NOT NULL ++ migrated (0.33s) ++ migrating 20161125002837 -> ALTER TABLE mauyu_lead_devices ADD device_fingerprint VARCHAR(255) DEFAULT NULL -> CREATE INDEX mauyu_device_fingerprint_search ON mauyu_lead_devices (device_fingerprint) ++ migrated (0.32s) ++ migrating 20161222183556 -> CREATE TABLE mauyu_saml_id_entry ( id VARCHAR(255) NOT NULL, entity_id VARCHAR(255) NOT NULL, expiryTimestamp INT NOT NULL, PRIMARY KEY(id, entity_id) ) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB ++ migrated (0.35s) ++ migrating 20170106102310 -> ALTER TABLE mauyu_campaign_events ADD channel VARCHAR(255) DEFAULT NULL, ADD channel_id INTEGER DEFAULT NULL, ADD INDEX mauyu_campaign_event_channel (channel, channel_id), DROP INDEX mauyu_campaign_event_type_search, ADD INDEX mauyu_campaign_event_search (type, event_type), DROP INDEX mauyu_event_type, ADD INDEX mauyu_campaign_event_type (event_type) Migration 20170106102310 failed during Execution. Error An exception occurred while executing ‘ALTER TABLE mauyu_campaign_events ADD channel VARCHAR(255) DEFAULT NULL, ADD channel_id INTEGER DEFAULT NULL, ADD INDEX mauyu_campaign_event_channel (channel, channel_id), DROP INDEX mauyu_campaign_event_type_search, ADD INDEX mauyu_campaign_event_search (type, event_type), DROP INDEX mauyu_event_type, ADD INDEX mauyu_campaign_event_type (event_type)’: Can’t DROP ‘mauyu_campaign_event_type_search’; check that column/key exists [DoctrineDBALExceptionDriverException] An exception occurred while executing ‘ALTER TABLE mauyu_campaign_events ADD channel VARCHAR(255) DEFAULT NULL, ADD channel_id INTEGER DEFAULT NULL, ADD INDEX mauyu_campaign_event_channel (channel, channel_id), DROP INDEX mauyu_campaign_event_type_search, ADD INDEX mauyu_campaign_event_search (type, event_type), DROP INDEX mauyu_event_type, ADD INDEX mauyu_campaign_event_type (event_type)’: Can’t DROP ‘mauyu_campaign_event_type_search’; check that column/key exists [DoctrineDBALDriverMysqliMysqliException] Can’t DROP ‘mauyu_campaign_event_type_search’; check that column/key exists doctrine:migrations:migrate ] ] [-h|–help] [-q|–quiet] [-v|vv|vvv|–verbose] [-V|–version] [-n|–no-interaction] [-s|–shell] [-e|–env ENV] [] [] []


Hello,
I am new to Mautic and having a database problem. In attempting to upgrade from 2.5.1 to 2.6.1 the file upgrade worked OK but the database upgrade failed. I went back to 2.5.1 from backups and then attempted to upgrade/repair the database using http://mymauticsite.com/s/upgrade/schema but this also failed.
the log shows as below but i am unable to interpret the log to identify and fix the problem.
Perhaps someone can help?
Thanks

Log Entries:

[2017-03-06 19:18:15] mautic.NOTICE: DoctrineDBALExceptionDriverException: An exception occurred while executing ‘ALTER TABLE mauyu_campaign_events ADD channel VARCHAR(255) DEFAULT NULL, ADD channel_id INTEGER DEFAULT NULL, ADD INDEX mauyu_campaign_event_channel (channel, channel_id), DROP INDEX mauyu_campaign_event_type_search, ADD INDEX mauyu_campaign_event_search (type, event_type), DROP INDEX mauyu_event_type, ADD INDEX mauyu_campaign_event_type (event_type)’: Can’t DROP ‘mauyu_campaign_event_type_search’; check that column/key exists (uncaught exception) at /home/mysite/public_html/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 115 while running console command doctrine:migrations:migrate [] []
[2017-03-06 19:18:15] mautic.ERROR: [UPGRADE ERROR] Exit code 1; Mautic Migrations Migrating up to 20170127205928 from 20161124145649 ++ migrating 20161026202839 -> CREATE TABLE mauyu_message_channels ( id INT AUTO_INCREMENT NOT NULL, message_id INT NOT NULL, channel VARCHAR(255) NOT NULL, channel_id INT NULL, properties LONGTEXT NOT NULL COMMENT ‘(DC2Type:json_array)’, is_enabled TINYINT(1) NOT NULL, INDEX IDX_118ADA5E537A1329 (message_id), INDEX mauyu_channel_entity_index (channel, channel_id), INDEX mauyu_channel_enabled_index (channel, is_enabled), UNIQUE INDEX mauyu_channel_index (message_id, channel), PRIMARY KEY(id) ) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; -> CREATE TABLE mauyu_messages ( id INT AUTO_INCREMENT NOT NULL, category_id INT DEFAULT NULL, is_published TINYINT(1) NOT NULL, date_added DATETIME DEFAULT NULL COMMENT ‘(DC2Type:datetime)’, created_by INT DEFAULT NULL, created_by_user VARCHAR(255) DEFAULT NULL, date_modified DATETIME DEFAULT NULL COMMENT ‘(DC2Type:datetime)’, modified_by INT DEFAULT NULL, modified_by_user VARCHAR(255) DEFAULT NULL, checked_out DATETIME DEFAULT NULL COMMENT ‘(DC2Type:datetime)’, checked_out_by INT DEFAULT NULL, checked_out_by_user VARCHAR(255) DEFAULT NULL, name VARCHAR(255) NOT NULL, description LONGTEXT DEFAULT NULL, publish_up DATETIME DEFAULT NULL COMMENT ‘(DC2Type:datetime)’, publish_down DATETIME DEFAULT NULL COMMENT ‘(DC2Type:datetime)’, INDEX IDX_3D41E7812469DE2 (category_id), INDEX mauyu_date_message_added (date_added), PRIMARY KEY(id) ) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; -> ALTER TABLE mauyu_message_channels ADD CONSTRAINT FK_118ADA5E537A1329 FOREIGN KEY (message_id) REFERENCES mauyu_messages (id) ON DELETE CASCADE -> ALTER TABLE mauyu_messages ADD CONSTRAINT FK_3D41E7812469DE2 FOREIGN KEY (category_id) REFERENCES mauyu_categories (id) ON DELETE SET NULL ++ migrated (0.35s) ++ migrating 20161122215214 -> ALTER TABLE mauyu_push_notifications ADD button LONGTEXT NOT NULL ++ migrated (0.33s) ++ migrating 20161125002837 -> ALTER TABLE mauyu_lead_devices ADD device_fingerprint VARCHAR(255) DEFAULT NULL -> CREATE INDEX mauyu_device_fingerprint_search ON mauyu_lead_devices (device_fingerprint) ++ migrated (0.32s) ++ migrating 20161222183556 -> CREATE TABLE mauyu_saml_id_entry ( id VARCHAR(255) NOT NULL, entity_id VARCHAR(255) NOT NULL, expiryTimestamp INT NOT NULL, PRIMARY KEY(id, entity_id) ) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB ++ migrated (0.35s) ++ migrating 20170106102310 -> ALTER TABLE mauyu_campaign_events ADD channel VARCHAR(255) DEFAULT NULL, ADD channel_id INTEGER DEFAULT NULL, ADD INDEX mauyu_campaign_event_channel (channel, channel_id), DROP INDEX mauyu_campaign_event_type_search, ADD INDEX mauyu_campaign_event_search (type, event_type), DROP INDEX mauyu_event_type, ADD INDEX mauyu_campaign_event_type (event_type) Migration 20170106102310 failed during Execution. Error An exception occurred while executing ‘ALTER TABLE mauyu_campaign_events ADD channel VARCHAR(255) DEFAULT NULL, ADD channel_id INTEGER DEFAULT NULL, ADD INDEX mauyu_campaign_event_channel (channel, channel_id), DROP INDEX mauyu_campaign_event_type_search, ADD INDEX mauyu_campaign_event_search (type, event_type), DROP INDEX mauyu_event_type, ADD INDEX mauyu_campaign_event_type (event_type)’: Can’t DROP ‘mauyu_campaign_event_type_search’; check that column/key exists [DoctrineDBALExceptionDriverException] An exception occurred while executing ‘ALTER TABLE mauyu_campaign_events ADD channel VARCHAR(255) DEFAULT NULL, ADD channel_id INTEGER DEFAULT NULL, ADD INDEX mauyu_campaign_event_channel (channel, channel_id), DROP INDEX mauyu_campaign_event_type_search, ADD INDEX mauyu_campaign_event_search (type, event_type), DROP INDEX mauyu_event_type, ADD INDEX mauyu_campaign_event_type (event_type)’: Can’t DROP ‘mauyu_campaign_event_type_search’; check that column/key exists [DoctrineDBALDriverMysqliMysqliException] Can’t DROP ‘mauyu_campaign_event_type_search’; check that column/key exists doctrine:migrations:migrate [–write-sql] [–dry-run] [–query-time] [–allow-no-migration] [–configuration [CONFIGURATION]] [–db-configuration [DB-CONFIGURATION]] [–db DB] [–em EM] [–shard SHARD] [-h|–help] [-q|–quiet] [-v|vv|vvv|–verbose] [-V|–version] [–ansi] [–no-ansi] [-n|–no-interaction] [-s|–shell] [–process-isolation] [-e|–env ENV] [–no-debug] [–] [] [] []

Any possibility of help with this? I have tried numerous times to upgrade to both 2.6.1 and 2.7 Each tine the database upgrade fails.
I have also tried ‘mysite.com/mautic/s/update/schema’ which also fails?

@waverider
Data base updatation is really a very complicated task in mautic …for that have to do so many things from start …

Better you add me on sk…pe…kishankumawat at live dot com

Thanks bizcrony - I have emailed you.

I can’t understand why when trying to update I get the the database error:
[b]
Can’t

DROP 'mauyu_campaign_event_type_search'; 

check that column/key exists[/b]

When I dump the sql for the database I get:



– Table structure for table mauyu_campaign_events

CREATE TABLE IF NOT EXISTS `mauyu_campaign_events` ( `id` int(11) NOT NULL AUTO_INCREMENT, `campaign_id` int(11) NOT NULL, `parent_id` int(11) DEFAULT NULL, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `description` longtext COLLATE utf8_unicode_ci, `type` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `event_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `event_order` int(11) NOT NULL, `properties` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:array)', `trigger_date` datetime DEFAULT NULL, `trigger_interval` int(11) DEFAULT NULL, `trigger_interval_unit` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL, `trigger_mode` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL, `decision_path` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `temp_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`), KEY `IDX_25BBE925F639F774` (`campaign_id`), KEY `IDX_25BBE925727ACA70` (`parent_id`), KEY `mauyu_campaign_event_type_search` (`type`,`event_type`), KEY `mauyu_event_type` (`event_type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;


so the key appears to exist but no column?

Not sure how to proceed

Any help appreciated.