patneu

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;
      }
    }
  }
}

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}