Rare fatal error caused by ActionScheduler not installing correctly

[fpcorso]

Describe the bug

A few admins have reported an error occurring when the plugin is updated or first installed that appears to be coming from within Action Scheduler. Will update here as we have more info.

Site information

Popup Maker version: 1.11.0

Errors

One Admin reported this error in the forums here: https://wordpress.org/support/topic/version-1-11-breaks-my-site/

The error message (I replaced private information with xxxx):
Uncaught RuntimeException: Error saving action: Error saving action: Table ‘xxxxxxxxx.xxx_actionscheduler_actions’ doesn’t exist in /home/xxxxxxxx/xxxx/wp-content/plugins/popup-maker/packages/action-scheduler/classes/migration/ActionScheduler_DBStoreMigrator.php:44

Another admin reported this stacktrace in the forums here: https://wordpress.org/support/topic/critical-error-after-update-4/#post-13124120

Fatal error: Uncaught RuntimeException: Error saving action: Error saving action: Table ‘ccw_wp.wpccw_actionscheduler_actions’ doesn’t exist in /var/www/html/wp-content/plugins/popup-maker/packages/action-scheduler/classes/migration/ActionScheduler_DBStoreMigrator.php:44 
Stack trace: #0 /var/www/html/wp-content/plugins/popup-maker/packages/action-scheduler/classes/data-stores/ActionScheduler_HybridStore.php(242): ActionScheduler_DBStoreMigrator->save_action(Object(ActionScheduler_Action), NULL) #1 
/var/www/html/wp-content/plugins/popup-maker/packages/action-scheduler/classes/ActionScheduler_ActionFactory.php(177): ActionScheduler_HybridStore->save_action(Object(ActionScheduler_Action)) #2 
/var/www/html/wp-content/plugins/popup-maker/packages/action-scheduler/classes/ActionScheduler_ActionFactory.php(84): ActionScheduler_ActionFactory->store(Object(ActionScheduler_Action)) #3 
/var/www/html/wp-content/plugins/popup-maker/packages/action-scheduler/functions.php(36): ActionScheduler_ActionFactory->single(‘action_schedule…’, in 
/var/www/html/wp-content/plugins/popup-maker/packages/action-scheduler/classes/migration/ActionScheduler_DBStoreMigrator.php on line 44

• https://secure.helpscout.net/conversation/1252325886/17432/

[danieliser]

@fpcorso So I have been tracking this one for a week or so trying to figure out what is going on. Seems its not related to us at all, but rather the current release of Action Scheduler. It is affecting WooCommerce as well:

• v.3.1.5 woocommerce/action-scheduler#540
• Updated woocommerce => "There has been a critical error on your website." woocommerce/action-scheduler#577
• MySQL NO_ZERO_DATE prevents table creation woocommerce/action-scheduler#587

I haven't been able to make a determination yet but I believe its related to the create table query having something not quite right, at least for the version of MySQL these users are using.

Specifically I noted this comment in one of those issues:
ERROR 1067 (42000): Invalid default value for 'scheduled_date_gmt'

CREATE TABLE wp_actionscheduler_actions (
	action_id bigint(20) unsigned NOT NULL auto_increment,
	hook varchar(191) NOT NULL,
	status varchar(20) NOT NULL,
	scheduled_date_gmt datetime NOT NULL default '0000-00-00 00:00:00',
	scheduled_date_local datetime NOT NULL default '0000-00-00 00:00:00',
	args varchar(191),
	schedule longtext,
	group_id bigint(20) unsigned NOT NULL default '0',
	attempts int(11) NOT NULL default '0',
	last_attempt_gmt datetime NOT NULL default '0000-00-00 00:00:00',
	last_attempt_local datetime NOT NULL default '0000-00-00 00:00:00',
	claim_id bigint(20) unsigned NOT NULL default '0',
	extended_args varchar(8000) DEFAULT NULL,
	PRIMARY KEY  (action_id),
	KEY hook (hook(191)),
	KEY status (status),
	KEY scheduled_date_gmt (scheduled_date_gmt),
	KEY args (args(191)),
	KEY group_id (group_id),
	KEY last_attempt_gmt (last_attempt_gmt),
	KEY claim_id (claim_id)
) ${charset_collate}

Strangely I have encountered that when working on the new analytics tables on MySQL 5.7.

But this leads me to think that woocommerce/action-scheduler#587 is the true root of this. So I need to review my commits and see if I can work out what might be the cause here as well.

[danieliser]

Ahh yes I remember: https://stackoverflow.com/questions/36882149/error-1067-42000-invalid-default-value-for-created-at

Its an sql_mode that causes this issue.

I think I ended up switching to timestamp, but they seem to have worked it out yesterday so we hopefully can update that package shortly with a fix: woocommerce/action-scheduler#587 (comment)

[fpcorso]

@danieliser Seems the Action Scheduler package hasn't resolved this issue yet. If I get the other issues in 1.11.1 fixed, we may need to punt this one to the next hotfix release.

[danieliser]

@fpcorso - one option is to manually make the tweaks after gulp release but before svn ci

That way our users can avoid the errors in the mean time.

[danieliser]

Added notes about potential changes to resolve this here: woocommerce/action-scheduler#587 (comment)

[danieliser]

@fpcorso There seems to be some hope, other plugins have found workarounds for their users, and this one shows the workaround but also says they fixed it, haven't read the full details or looked at the code, but good place to start on solving it maybe: https://wordpress.org/support/topic/plugin-is-not-working-after-upgrade-please-fix-asap/

[fpcorso]

@danieliser I reviewed that support topic thread (which is two pages) but some users continued to experience the issue even after updating. However, they did say if the user went to WPForms > Tools > Scheduled Actions it would generate the tables. That may be a useful solution too since we are adding Scheduled Actions in our 1.12 update. Not sure if that tab automatically does that or if we'd have to add some code.

[danieliser]

@fpcorso WooCommerce suggested the same thing, somehow visiting that page causes it to recheck tables and install them again.

But if the plugin fatals on install, it doesn't much matter haha.

That said I do wonder if the users experiencing this have WP_DEBUG enabled.

[danieliser]

@tonyfelice Appreciate the feedback. So it could be related to table prefixes you think?

[tonyfelice]

Whatever the issue, it’s with the scheduler, not Popup Maker. After I had emailed, I tried to install Automatik’s standalone scheduler, and I’ve never seen Wordpress puke harder ;-) (tf)

…

On Feb 1, 2021, at 11:57 PM, Daniel L. Iser ***@***.***> wrote:  @tonyfelice Appreciate the feedback. So it could be related to table prefixes you think? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[danieliser]

@tonyfelice We may end up just removing our usage of it and finding an alternative. It seemed like it should be a super reliable choice early on since its maintained by WooCommerce, but sigh.