Update failure - Missing database table or columns

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 database column in a table, etc.
You get this error mainly because you have a large database and while running the import from the web interface, the web server timed out and you end up with an incomplete upgrade. Best is to always run the command line updater, it’s safer than the one from the web browser as it doe snot time out. Fixing errors like the above is not hard, but just time consuming.

Let’s assume you were upgrading from 1.4.0. to 1.4.3 when you got the above error.
So now, what we have to do, is to go via FTP at /apps/common/data/update-sql and open the files 1.4.0.sql / 1.4.1.sql / 1.4.2.sql and 1.4.3.sql.
These are the files that MailWizz tried to run in order to upgrade your database, but it failed to do so for one of them. We can see that the files 1.4.0.sql and 1.4.1.sql are empty, but 1.4.2.sql contains this:

--
-- campaign option
--
ALTER TABLE `campaign_option` ADD `autoresponder_include_current` ENUM('yes','no') NOT NULL DEFAULT 'no' AFTER `autoresponder_include_imported`;

--
-- create campaign open action subscriber table
--
CREATE TABLE IF NOT EXISTS `campaign_sent_action_subscriber` (
  `action_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `campaign_id` int(11) NOT NULL,
  `list_id` int(11) NOT NULL,
  `action` char(5) NOT NULL DEFAULT 'copy',
  `date_added` datetime NOT NULL,
  `last_updated` datetime NOT NULL,
  PRIMARY KEY (`action_id`),
  KEY `fk_campaign_sent_action_subscriber_campaign1_idx` (`campaign_id`),
  KEY `fk_campaign_sent_action_subscriber_list1_idx` (`list_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- add the fk to the campaign_sent_action_subscriber table
--
ALTER TABLE `campaign_sent_action_subscriber`
  ADD CONSTRAINT `fk_campaign_sent_action_subscriber_campaign1` FOREIGN KEY (`campaign_id`) REFERENCES `campaign` (`campaign_id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_campaign_sent_action_subscriber_list1` FOREIGN KEY (`list_id`) REFERENCES `list` (`list_id`) ON DELETE CASCADE ON UPDATE NO ACTION;

So there’s a bunch of sql queries in there, and we must run them, one by one against our database server in order to bring the database up to date.
You can use phpmyadmin to run the above queries against your database, or the command line, for those that have more experience.
Now, in SQL, lines that start with two dashes (–) means they are comments, so we can omit them.
So this means, from the above file, the first sql query that we have to run, is:

ALTER TABLE `campaign_option` ADD `autoresponder_include_current` ENUM('yes','no') NOT NULL DEFAULT 'no' AFTER `autoresponder_include_imported`;

However, running it like so, will most likely end up with an error, because our tables contain a prefix which we have added when we installed mailwizz. Most likely this table prefix is set to `mw_`, but you might choose something else when you installed the app.
Either way, in this case, our database query becomes:

ALTER TABLE `mw_campaign_option` ADD `autoresponder_include_current` ENUM('yes','no') NOT NULL DEFAULT 'no' AFTER `autoresponder_include_imported`;

and we can run it in phpmyadmin against our database, and we will see it runs okay.
If you get any error, it means this query has ran successfully during the upgrade process so you can simply go to the next query.
Now that we know the above, we can transform the rest of the queries from our file and add the right table prefix, like so:

--
-- create campaign open action subscriber table
--
CREATE TABLE IF NOT EXISTS `mw_campaign_sent_action_subscriber` (
  `action_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `campaign_id` int(11) NOT NULL,
  `list_id` int(11) NOT NULL,
  `action` char(5) NOT NULL DEFAULT 'copy',
  `date_added` datetime NOT NULL,
  `last_updated` datetime NOT NULL,
  PRIMARY KEY (`action_id`),
  KEY `fk_campaign_sent_action_subscriber_campaign1_idx` (`campaign_id`),
  KEY `fk_campaign_sent_action_subscriber_list1_idx` (`list_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- add the fk to the campaign_sent_action_subscriber table
--
ALTER TABLE `campaign_sent_action_subscriber`
  ADD CONSTRAINT `fk_campaign_sent_action_subscriber_campaign1` FOREIGN KEY (`campaign_id`) REFERENCES `mw_campaign` (`campaign_id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_campaign_sent_action_subscriber_list1` FOREIGN KEY (`list_id`) REFERENCES `mw_list` (`list_id`) ON DELETE CASCADE ON UPDATE NO ACTION;

And we can run them in phpmyadmin against our database.
We should do the same for all the .sql files left to complete the upgrade.

This is a bit difficult and you should avoid getting in such situation by simply using the command line upgrade tool instead of upgrading from the browser.

Please don’t forget to clear the app cache after you do all these changes. Simply rename the folder apps/common/runtime/cache to something else will force the cache to regenerate.