DB Migration Error (Warning) after Update

Resolved patneu
(@patneu)

1 year, 1 month ago

Ater Updating the Plugin from Version 4.15 to 5.10 the plugin cannot activated anymore:

WP Version is 6.7.2 and PHP 8.2
Following warrnigs are displayed several times.

Warning: Undefined array key “key” in /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/lib/Migrations/Db/Migration_20230831_143755_Db.php on line 121

Warning: Undefined array key “key” in /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/lib/Migrations/Db/Migration_20230831_143755_Db.php on line 121

Can anybody help me?

The page I need help with: [log in to see the link]

Viewing 6 replies - 1 through 6 (of 6 total)

Plugin Support Ojoma a11n
(@geraltrivia)

1 year, 1 month ago
Hello there @patneu ,

Thanks for reaching out and sharing those warning messages — I can confirm this relates to a database migration step. We can help get things back on track.

To resolve this, you’ll need to do the following:
1. Deactivate the MailPoet plugin (if it’s not already deactivated)
2. Access your site’s database using a tool like phpMyAdmin
3. Locate the table called wp_mailpoet_migrations (the wp_ prefix may vary depending on your setup)
4. Find and delete the row where the name is: Migration_20230831_143755
5. Once deleted, go back and reactivate the MailPoet plugin. This will trigger the migration to run again properly.
Let us know if this is something you feel comfortable doing. If not, we recommend asking your hosting provider for help — they can usually assist with accessing and editing the database safely.

Once this is done, everything should work as expected, but we’re here if you need help at any step along the way!
Thread Starter patneu
(@patneu)

1 year, 1 month ago
Hello,
Thank you for your information.
I deleted that row. But after activating the error occurs again and the row was added again.
I even updated and reinstalled to 5.10.1 but the behavior is the same.
Best regards
Patrick
- This reply was modified 1 year, 1 month ago by patneu.
Thread Starter patneu
(@patneu)

1 year, 1 month ago

Following error is in the error field of the row:

MailPoet\Doctrine\WPDB\Exceptions\QueryException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘INSERT INTO wp_mailpoet_automation_run_logs (automation_run_id, step_id, step_ty’ at line 2 in /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/lib/Doctrine/WPDB/Connection.php:132
Stack trace: 0 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/lib/Doctrine/WPDB/Connection.php(123): MailPoet\Doctrine\WPDB\Connection->handleQueryError() 1 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/lib/Doctrine/WPDB/Connection.php(42): MailPoet\Doctrine\WPDB\Connection->runQuery(‘UPDATE wp_mailp…’) 2 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/vendor-prefixed/doctrine/dbal/src/Connection.php(473): MailPoet\Doctrine\WPDB\Connection->exec(‘UPDATE wp_mailp…’) 3 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/lib/Migrations/Db/Migration_20230831_143755_Db.php(138): MailPoetVendor\Doctrine\DBAL\Connection->executeStatement(‘UPDATE wp_mailp…’) 4 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/lib/Migrations/Db/Migration_20230831_143755_Db.php(62): MailPoet\Migrations\Db\Migration_20230831_143755_Db->backfillStepKeysAndTriggers() 5 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/lib/Migrator/Runner.php(35): MailPoet\Migrations\Db\Migration_20230831_143755_Db->run() 6 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/lib/Migrator/Migrator.php(53): MailPoet\Migrator\Runner->runMigration(‘Migration_20230…’, ‘db’) 7 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/lib/Config/Activator.php(78): MailPoet\Migrator\Migrator->run() 8 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/lib/Config/Activator.php(60): MailPoet\Config\Activator->processActivate() 9 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/lib/Config/Initializer.php(423): MailPoet\Config\Activator->activate() 10 /home/.sites/439/site1878/web/wordpress/wp-includes/class-wp-hook.php(324): MailPoet\Config\Initializer->maybeRunActivator(”) 11 /home/.sites/439/site1878/web/wordpress/wp-includes/class-wp-hook.php(348): WP_Hook->apply_filters(”, Array) 12 /home/.sites/439/site1878/web/wordpress/wp-includes/plugin.php(517): WP_Hook->do_action(Array) 13 /home/.sites/439/site1878/web/wordpress/wp-settings.php(704): do_action(‘init’) 14 /home/.sites/439/site1878/web/wordpress/wp-config.php(115): require_once(‘/home/.sites/43…’) 15 /home/.sites/439/site1878/web/wordpress/wp-load.php(50): require_once(‘/home/.sites/43…’) 16 /home/.sites/439/site1878/web/wordpress/wp-cron.php(46): require_once(‘/home/.sites/43…’) 17 {main}

Next MailPoetVendor\Doctrine\DBAL\Exception\SyntaxErrorException: An exception occurred while executing a query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘INSERT INTO wp_mailpoet_automation_run_logs (automation_run_id, step_id, step_ty’ at line 2 in /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/vendor-prefixed/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php:69
Stack trace: 0 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/vendor-prefixed/doctrine/dbal/src/Connection.php(821): MailPoetVendor\Doctrine\DBAL\Driver\API\MySQL\ExceptionConverter->convert(Object(MailPoet\Doctrine\WPDB\Exceptions\QueryException), Object(MailPoetVendor\Doctrine\DBAL\Query)) 1 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/vendor-prefixed/doctrine/dbal/src/Connection.php(793): MailPoetVendor\Doctrine\DBAL\Connection->handleDriverException(Object(MailPoet\Doctrine\WPDB\Exceptions\QueryException), Object(MailPoetVendor\Doctrine\DBAL\Query)) 2 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/vendor-prefixed/doctrine/dbal/src/Connection.php(475): MailPoetVendor\Doctrine\DBAL\Connection->convertExceptionDuringQuery(Object(MailPoet\Doctrine\WPDB\Exceptions\QueryException), ‘UPDATE wp_mailp…’, Array, Array) 3 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/lib/Migrations/Db/Migration_20230831_143755_Db.php(138): MailPoetVendor\Doctrine\DBAL\Connection->executeStatement(‘UPDATE wp_mailp…’) 4 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/lib/Migrations/Db/Migration_20230831_143755_Db.php(62): MailPoet\Migrations\Db\Migration_20230831_143755_Db->backfillStepKeysAndTriggers() 5 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/lib/Migrator/Runner.php(35): MailPoet\Migrations\Db\Migration_20230831_143755_Db->run() 6 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/lib/Migrator/Migrator.php(53): MailPoet\Migrator\Runner->runMigration(‘Migration_20230…’, ‘db’) 7 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/lib/Config/Activator.php(78): MailPoet\Migrator\Migrator->run() 8 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/lib/Config/Activator.php(60): MailPoet\Config\Activator->processActivate() 9 /home/.sites/439/site1878/web/wordpress/wp-content/plugins/mailpoet/lib/Config/Initializer.php(423): MailPoet\Config\Activator->activate() 10 /home/.sites/439/site1878/web/wordpress/wp-includes/class-wp-hook.php(324): MailPoet\Config\Initializer->maybeRunActivator(”) 11 /home/.sites/439/site1878/web/wordpress/wp-includes/class-wp-hook.php(348): WP_Hook->apply_filters(”, Array) 12 /home/.sites/439/site1878/web/wordpress/wp-includes/plugin.php(517): WP_Hook->do_action(Array) 13 /home/.sites/439/site1878/web/wordpress/wp-settings.php(704): do_action(‘init’) 14 /home/.sites/439/site1878/web/wordpress/wp-config.php(115): require_once(‘/home/.sites/43…’) 15 /home/.sites/439/site1878/web/wordpress/wp-load.php(50): require_once(‘/home/.sites/43…’) 16 /home/.sites/439/site1878/web/wordpress/wp-cron.php(46): require_once(‘/home/.sites/43…’) 17 {main}

Thread Starter patneu
(@patneu)

1 year, 1 month ago

Mabye this is helpfull:

DESCRIBE wp_mailpoet_automation_run_logs;

id int(11) unsigned NO PRI NULL auto_increment
automation_run_id int(11) unsigned NO MUL NULL
step_id varchar(191) NO MUL NULL
step_type varchar(255) NO NULL
step_key varchar(255) NO NULL
status varchar(191) NO MUL NULL
started_at timestamp YES NULL
updated_at timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
run_number int(11) NO NULL
completed_at timestamp YES NULL
error longtext YES NULL
data longtext YES NULL
Thread Starter patneu
(@patneu)

1 year, 1 month ago
The issue was in file: Migration_20230831_143755_Db.php of the plugin.

I fixed following function:

Update and Insert statement are splited up.
```
private function backfillStepKeysAndTriggers(): void {
  global $wpdb;
  $logsTable = $wpdb->prefix . 'mailpoet_automation_run_logs';
  $runsTable = $wpdb->prefix . 'mailpoet_automation_runs';
  $versionsTable = $wpdb->prefix . 'mailpoet_automation_versions';

  $triggerAddedMap = [];

  while (true) {
    $data = $this->connection->executeQuery("
      SELECT rl.id, rl.automation_run_id, rl.step_id, rl.started_at, v.steps
      FROM {$logsTable} rl
      JOIN {$runsTable} r ON r.id = rl.automation_run_id
      JOIN {$versionsTable} v ON v.id = r.version_id
      WHERE rl.step_key = ''
      ORDER BY rl.id ASC
      LIMIT 50
    ")->fetchAllAssociative();

    if (count($data) === 0) {
      break;
    }

    /** @var array<int, array{id:int, automation_run_id:int, step_id:string, started_at:string|null, steps:string}> $data */
    foreach ($data as $item) {
      $steps = json_decode(strval($item['steps']), true);
      $id = intval($item['id']);
      $stepId = strval($item['step_id']);
      $stepKey = addslashes(strval($steps[$stepId]['key'] ?? 'unknown'));
      $triggerId = addslashes($steps['root']['next_steps'][0]['id'] ?? 'unknown');
      $triggerKey = addslashes($steps['root']['next_steps'][0]['key'] ?? 'unknown');

      // UPDATE step_key
      $updateQuery = "UPDATE {$logsTable} SET step_key = '{$stepKey}' WHERE id = {$id}";
      $this->connection->executeStatement($updateQuery);

      // BACKFILL trigger row
      $runId = intval($item['automation_run_id']);
      if (!isset($triggerAddedMap[$runId])) {
        $startedAt = !empty($item['started_at']) ? $item['started_at'] : date('Y-m-d H:i:s');
        $dateExpression = "DATE_SUB('$startedAt', INTERVAL 1 SECOND)";

        $insertQuery = "
          INSERT INTO {$logsTable} (
            automation_run_id, step_id, step_type, step_key, status,
            started_at, updated_at, run_number, data
          )
          VALUES (
            $runId, '$triggerId', 'trigger', '$triggerKey', 'complete',
            $dateExpression, $dateExpression, 1, '{}'
          )
        ";
        $this->connection->executeStatement($insertQuery);
        $triggerAddedMap[$runId] = true;
      }
    }
  }
}
```
Plugin Support Saravanan S, a11n
(@simplysaru)

1 year, 1 month ago

Hi @patneu ,

Thanks for your persistence working on this and sharing this fix. I have shared this with our developers to have a look. Hopefully, this can help users in a similar situation.