FIXED - mautic.ERROR: CAMPAIGN: An exception occurred while executing ‘REPLACE INTO

Product Support
1

FIXED

I will leave this in case it helps others…

  1. The field generated_sent_date was missing from the email_stats. Manually added it in mysql
  2. Had to change the mysql driver to “pdo_mysql” in local.php

Your software
My Mautic version is: 3.1.0
My PHP version is: 7.3
MY SQL version is: 5.7.31

Your problem
My problem is: Campaigns Are Not Restarting

These errors are showing in the log: [2020-08-28 19:42:02] mautic.ERROR: CAMPAIGN: An exception occurred while executing ‘REPLACE INTO mau4s_campaign_lead_event_failed_log( log_id, date_added, reason) SELECT id, :dateAdded as date_added, :message as reason from mau4s_campaign_lead_event_log WHERE is_scheduled = 1 AND lead_id = :contactId AND campaign_id = :campaignId AND rotation = :rotation’: 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 ':dateAdded as date_added, :message as reason from mau4s_campaign_lead_event_log ’ at line 2

Steps I have tried to fix the problem: When i run s/update/schema i get the following error, so something is wrong with my database. This was a new install of 3.0.2 that was immediately upgraded to 3.1.0.

[2020-08-28 19:47:20] mautic.NOTICE: Doctrine\DBAL\Exception\SyntaxErrorException: An exception occurred while executing ‘ALTER TABLE mau4s_email_stats ADD generated_sent_date DATE AS (CONCAT(YEAR(date_sent), “-”, LPAD(MONTH(date_sent), 2, “0”), “-”, LPAD(DAY(date_sent), 2, “0”))) COMMENT ‘(DC2Type:generated)’; ALTER TABLE mau4s_email_stats ADD INDEX mau4s_generated_sent_date_email_id(generated_sent_date, email_id)’: 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 ‘ALTER TABLE mau4s_email_stats ADD INDEX mau4s_generated_sent_date_email_id(gen’ at line 2 (uncaught exception) at /home/entice/ds.entice.cloud/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 79 while running console command doctrine:migrations:migrate
[2020-08-28 19:47:20] mautic.WARNING: Command doctrine:migrations:migrate exited with status code 1
[2020-08-28 19:47:20] mautic.ERROR: [UPGRADE ERROR] Exit code 1; ++ adding generated column generated_sent_date -> ALTER TABLE mau4s_email_stats ADD generated_sent_date DATE AS (CONCAT(YEAR(date_sent), “-”, LPAD(MONTH(date_sent), 2, “0”), “-”, LPAD(DAY(date_sent), 2, “0”))) COMMENT ‘(DC2Type:generated)’; ALTER TABLE mau4s_email_stats ADD INDEX mau4s_generated_sent_date_email_id(generated_sent_date, email_id) \ In AbstractMySQLDriver.php line 79: \ An exception occurred while executing ‘ALTER TABLE mau4s_email_stats ADD ge nerated_sent_date DATE AS (CONCAT(YEAR(date_sent), “-”, LPAD(MONTH(date_sen t), 2, “0”), “-”, LPAD(DAY(date_sent), 2, “0”))) COMMENT ‘(DC2Type:generate d)’; ALTER TABLE mau4s_email_stats ADD INDEX mau4s_generated_sent_d ate_email_id(generated_sent_date, email_id)’: \ 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 ‘ALTER TABLE mau 4s_email_stats ADD INDEX mau4s_generated_sent_date_email_id(gen’ at line 2 \ In MysqliStatement.php line 86: \ 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 ‘ALTER TABLE mau 4s_email_stats ADD INDEX mau4s_generated_sent_date_email_id(gen’ at line 2 \ doctrine:migrations:migrate [–write-sql [WRITE-SQL]] [–dry-run] [–query-time] [–allow-no-migration] [–all-or-nothing [ALL-OR-NOTHING]] [–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] [-e|–env ENV] [–no-debug] [–] \

I’ve also tried changing the value of property ‘db_driver’ from ‘mysqli’ to ‘db_driver’ => ‘pdo_mysql’, in app/config/local.php, but ran into the same problem when updating the schema

I should note that this feature worked in 3.0.0

Hi,

I have a campaign that is using the "Allow contacts to restart the campaign " option turned on. This is causing the error above and the campaigns are not restarting. Any help is appreciated

2

Gary, did you set the added field generated_sent_date as datetime? Also, do you know if this was taken care of in the last release of Mautic?

Thank you for your time and solution. Rich