Mautic Community Forums

SQL syntax errors Mautic 3.1.1

Your software
My Mautic version is:3.1.1
My PHP version is:7.3.23

Your problem
My problem is: When I sent my first email to list, Mautic got a 6 hour hang. It became non-responsive.

Now when I try to log, it give Error 500.
I can only go in to the Mautic by https://My_URL/s/update/schema
Despite going inside Mautic, many things do not work including dashboard

These errors are showing in the log: It is as below
## Current Error Log

[2020-10-13 09:40:58] mautic.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\InvalidFieldNameException: “An exception occurred while executing ‘SELECT t.generated_sent_date AS date, COUNT( ) AS count FROM mau86_email_stats t WHERE t.date_sent BETWEEN ? AND ? GROUP BY t.generated_sent_date ORDER BY t.generated_sent_date ASC LIMIT 31’ with params [“2020-09-13 00:00:00”, “2020-10-13 09:40:58”]: Unknown column ‘t.generated_sent_date’ in ‘field list’" at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 60 {“exception”:"[object] (Doctrine\DBAL\Exception\InvalidFieldNameException(code: 0): An exception occurred while executing 'SELECT t.generated_sent_date AS date, COUNT( ) AS count FROM mau86_email_stats t WHERE t.date_sent BETWEEN ? AND ? GROUP BY t.generated_sent_date ORDER BY t.generated_sent_date ASC LIMIT 31’ with params [“2020-09-13 00:00:00”, “2020-10-13 09:40:58”]:\n\nUnknown column ‘t.generated_sent_date’ in ‘field list’ at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:60, Doctrine\DBAL\Driver\Mysqli\MysqliException(code: 0): Unknown column ‘t.generated_sent_date’ in ‘field list’ at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/Mysqli/MysqliStatement.php:86)”}
[2020-10-13 09:41:18] mautic.NOTICE: Doctrine\DBAL\Exception\SyntaxErrorException: An exception occurred while executing ‘ALTER TABLE mau86_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 mau86_email_stats ADD INDEX mau86_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 mau86_email_stats ADD INDEX mau86_generated_sent_date_email_id (gen’ at line 2 (uncaught exception) at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 79 while running console command doctrine:migrations:migrate
[2020-10-13 09:41:18] mautic.WARNING: Command doctrine:migrations:migrate exited with status code 1
[2020-10-13 09:41:18] mautic.ERROR: [UPGRADE ERROR] Exit code 1; ++ adding generated column generated_sent_date -> ALTER TABLE mau86_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 mau86_email_stats ADD INDEX mau86_generated_sent_date_email_id (generated_sent_date, email_id) \ In AbstractMySQLDriver.php line 79: \ An exception occurred while executing ‘ALTER TABLE mau86_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 mau86_email_stats ADD INDEX mau86_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 86_email_stats ADD INDEX mau86_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 86_email_stats ADD INDEX mau86_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] [–] \
[2020-10-13 09:41:34] mautic.NOTICE: Doctrine\DBAL\Exception\SyntaxErrorException: An exception occurred while executing ‘ALTER TABLE mau86_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 mau86_email_stats ADD INDEX mau86_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 mau86_email_stats ADD INDEX mau86_generated_sent_date_email_id (gen’ at line 2 (uncaught exception) at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 79 while running console command doctrine:migrations:migrate
[2020-10-13 09:41:34] mautic.WARNING: Command doctrine:migrations:migrate exited with status code 1
[2020-10-13 09:41:34] mautic.ERROR: [UPGRADE ERROR] Exit code 1; ++ adding generated column generated_sent_date -> ALTER TABLE mau86_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 mau86_email_stats ADD INDEX mau86_generated_sent_date_email_id (generated_sent_date, email_id) \ In AbstractMySQLDriver.php line 79: \ An exception occurred while executing ‘ALTER TABLE mau86_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 mau86_email_stats ADD INDEX mau86_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 86_email_stats ADD INDEX mau86_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 86_email_stats ADD INDEX mau86_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] [–] \
[2020-10-13 09:59:30] mautic.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\InvalidFieldNameException: “An exception occurred while executing ‘SELECT t.generated_sent_date AS date, COUNT( ) AS count FROM mau86_email_stats t WHERE (t.email_id IN (9)) AND (t.date_sent BETWEEN ? AND ?) GROUP BY t.generated_sent_date ORDER BY t.generated_sent_date ASC LIMIT 31’ with params [“2020-09-13 00:00:00”, “2020-10-13 09:59:30”]: Unknown column ‘t.generated_sent_date’ in ‘field list’" at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 60 {“exception”:"[object] (Doctrine\DBAL\Exception\InvalidFieldNameException(code: 0): An exception occurred while executing 'SELECT t.generated_sent_date AS date, COUNT( ) AS count FROM mau86_email_stats t WHERE (t.email_id IN (9)) AND (t.date_sent BETWEEN ? AND ?) GROUP BY t.generated_sent_date ORDER BY t.generated_sent_date ASC LIMIT 31’ with params [“2020-09-13 00:00:00”, “2020-10-13 09:59:30”]:\n\nUnknown column ‘t.generated_sent_date’ in ‘field list’ at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:60, Doctrine\DBAL\Driver\Mysqli\MysqliException(code: 0): Unknown column ‘t.generated_sent_date’ in ‘field list’ at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/Mysqli/MysqliStatement.php:86)”}
[2020-10-13 04:40:55] mautic.CRITICAL: Uncaught PHP Exception Mautic\PluginBundle\Exception\ApiErrorException: “Property values were not valid Array ( [0] => Array ( [isValid] => [message] => Active was not one of the allowed options: [label: “Not Eligible” value: “Inactive” display_order: 0 double_data: 0.0 hidden: false description: “” read_only: false , label: “Eligible” value: “Eligible” display_order: 1 double_data: 0.0 hidden: false description: “” read_only: false ] [error] => INVALID_OPTION [name] => marketing_status ) ) " at /home4/joylocxl/connect.joyloan.in/plugins/MauticCrmBundle/Api/HubspotApi.php line 28 {“exception”:”[object] (Mautic\PluginBundle\Exception\ApiErrorException(code: 0): Property values were not valid \n Array\n(\n [0] => Array\n (\n [isValid] => \n [message] => Active was not one of the allowed options: [label: “Not Eligible”\nvalue: “Inactive”\ndisplay_order: 0\ndouble_data: 0.0\nhidden: false\ndescription: “”\nread_only: false\n, label: “Eligible”\nvalue: “Eligible”\ndisplay_order: 1\ndouble_data: 0.0\nhidden: false\ndescription: “”\nread_only: false\n]\n [error] => INVALID_OPTION\n [name] => marketing_status\n )\n\n)\n at /home4/joylocxl/connect.joyloan.in/plugins/MauticCrmBundle/Api/HubspotApi.php:28)"}
[2020-10-13 10:14:48] mautic.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\InvalidFieldNameException: “An exception occurred while executing ‘SELECT t.generated_sent_date AS date, COUNT( ) AS count FROM mau86_email_stats t WHERE (t.email_id IN (11)) AND (t.date_sent BETWEEN ? AND ?) GROUP BY t.generated_sent_date ORDER BY t.generated_sent_date ASC LIMIT 31’ with params [“2020-09-13 00:00:00”, “2020-10-13 10:14:48”]: Unknown column ‘t.generated_sent_date’ in ‘field list’" at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 60 {“exception”:"[object] (Doctrine\DBAL\Exception\InvalidFieldNameException(code: 0): An exception occurred while executing 'SELECT t.generated_sent_date AS date, COUNT( ) AS count FROM mau86_email_stats t WHERE (t.email_id IN (11)) AND (t.date_sent BETWEEN ? AND ?) GROUP BY t.generated_sent_date ORDER BY t.generated_sent_date ASC LIMIT 31’ with params [“2020-09-13 00:00:00”, “2020-10-13 10:14:48”]:\n\nUnknown column ‘t.generated_sent_date’ in ‘field list’ at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:60, Doctrine\DBAL\Driver\Mysqli\MysqliException(code: 0): Unknown column ‘t.generated_sent_date’ in ‘field list’ at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/Mysqli/MysqliStatement.php:86)”}
[2020-10-13 10:56:34] mautic.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\InvalidFieldNameException: “An exception occurred while executing ‘SELECT t.generated_sent_date AS date, COUNT( ) AS count FROM mau86_email_stats t WHERE t.date_sent BETWEEN ? AND ? GROUP BY t.generated_sent_date ORDER BY t.generated_sent_date ASC LIMIT 31’ with params [“2020-09-13 00:00:00”, “2020-10-13 10:56:34”]: Unknown column ‘t.generated_sent_date’ in ‘field list’" at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 60 {“exception”:"[object] (Doctrine\DBAL\Exception\InvalidFieldNameException(code: 0): An exception occurred while executing 'SELECT t.generated_sent_date AS date, COUNT( ) AS count FROM mau86_email_stats t WHERE t.date_sent BETWEEN ? AND ? GROUP BY t.generated_sent_date ORDER BY t.generated_sent_date ASC LIMIT 31’ with params [“2020-09-13 00:00:00”, “2020-10-13 10:56:34”]:\n\nUnknown column ‘t.generated_sent_date’ in ‘field list’ at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:60, Doctrine\DBAL\Driver\Mysqli\MysqliException(code: 0): Unknown column ‘t.generated_sent_date’ in ‘field list’ at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/Mysqli/MysqliStatement.php:86)”}
[2020-10-13 10:56:39] mautic.NOTICE: Doctrine\DBAL\Exception\SyntaxErrorException: An exception occurred while executing ‘ALTER TABLE mau86_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 mau86_email_stats ADD INDEX mau86_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 mau86_email_stats ADD INDEX mau86_generated_sent_date_email_id (gen’ at line 2 (uncaught exception) at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 79 while running console command doctrine:migrations:migrate
[2020-10-13 10:56:39] mautic.WARNING: Command doctrine:migrations:migrate exited with status code 1
[2020-10-13 10:56:39] mautic.ERROR: [UPGRADE ERROR] Exit code 1; ++ adding generated column generated_sent_date -> ALTER TABLE mau86_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 mau86_email_stats ADD INDEX mau86_generated_sent_date_email_id (generated_sent_date, email_id) \ In AbstractMySQLDriver.php line 79: \ An exception occurred while executing ‘ALTER TABLE mau86_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 mau86_email_stats ADD INDEX mau86_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 86_email_stats ADD INDEX mau86_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 86_email_stats ADD INDEX mau86_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] [–] \
[2020-10-13 11:16:17] mautic.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\InvalidFieldNameException: “An exception occurred while executing ‘SELECT t.generated_sent_date AS date, COUNT( ) AS count FROM mau86_email_stats t WHERE t.date_sent BETWEEN ? AND ? GROUP BY t.generated_sent_date ORDER BY t.generated_sent_date ASC LIMIT 31’ with params [“2020-09-13 00:00:00”, “2020-10-13 11:16:17”]: Unknown column ‘t.generated_sent_date’ in ‘field list’" at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 60 {“exception”:"[object] (Doctrine\DBAL\Exception\InvalidFieldNameException(code: 0): An exception occurred while executing 'SELECT t.generated_sent_date AS date, COUNT( ) AS count FROM mau86_email_stats t WHERE t.date_sent BETWEEN ? AND ? GROUP BY t.generated_sent_date ORDER BY t.generated_sent_date ASC LIMIT 31’ with params [“2020-09-13 00:00:00”, “2020-10-13 11:16:17”]:\n\nUnknown column ‘t.generated_sent_date’ in ‘field list’ at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:60, Doctrine\DBAL\Driver\Mysqli\MysqliException(code: 0): Unknown column ‘t.generated_sent_date’ in ‘field list’ at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/Mysqli/MysqliStatement.php:86)”}
[2020-10-13 11:16:24] mautic.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\InvalidFieldNameException: “An exception occurred while executing ‘SELECT t.generated_sent_date AS date, COUNT( ) AS count FROM mau86_email_stats t WHERE t.date_sent BETWEEN ? AND ? GROUP BY t.generated_sent_date ORDER BY t.generated_sent_date ASC LIMIT 31’ with params [“2020-09-13 00:00:00”, “2020-10-13 11:16:24”]: Unknown column ‘t.generated_sent_date’ in ‘field list’" at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 60 {“exception”:"[object] (Doctrine\DBAL\Exception\InvalidFieldNameException(code: 0): An exception occurred while executing 'SELECT t.generated_sent_date AS date, COUNT( ) AS count FROM mau86_email_stats t WHERE t.date_sent BETWEEN ? AND ? GROUP BY t.generated_sent_date ORDER BY t.generated_sent_date ASC LIMIT 31’ with params [“2020-09-13 00:00:00”, “2020-10-13 11:16:24”]:\n\nUnknown column ‘t.generated_sent_date’ in ‘field list’ at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:60, Doctrine\DBAL\Driver\Mysqli\MysqliException(code: 0): Unknown column ‘t.generated_sent_date’ in ‘field list’ at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/Mysqli/MysqliStatement.php:86)”}
[2020-10-13 11:23:12] mautic.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\InvalidFieldNameException: “An exception occurred while executing ‘SELECT t.generated_sent_date AS date, COUNT( ) AS count FROM mau86_email_stats t WHERE (t.email_id IN (13)) AND (t.date_sent BETWEEN ? AND ?) GROUP BY t.generated_sent_date ORDER BY t.generated_sent_date ASC LIMIT 31’ with params [“2020-09-13 00:00:00”, “2020-10-13 11:23:12”]: Unknown column ‘t.generated_sent_date’ in ‘field list’" at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 60 {“exception”:"[object] (Doctrine\DBAL\Exception\InvalidFieldNameException(code: 0): An exception occurred while executing 'SELECT t.generated_sent_date AS date, COUNT( ) AS count FROM mau86_email_stats t WHERE (t.email_id IN (13)) AND (t.date_sent BETWEEN ? AND ?) GROUP BY t.generated_sent_date ORDER BY t.generated_sent_date ASC LIMIT 31’ with params [“2020-09-13 00:00:00”, “2020-10-13 11:23:12”]:\n\nUnknown column ‘t.generated_sent_date’ in ‘field list’ at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:60, Doctrine\DBAL\Driver\Mysqli\MysqliException(code: 0): Unknown column ‘t.generated_sent_date’ in ‘field list’ at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/Mysqli/MysqliStatement.php:86)”}
[2020-10-13 18:19:38] mautic.NOTICE: Doctrine\DBAL\Exception\SyntaxErrorException: An exception occurred while executing ‘ALTER TABLE mau86_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 mau86_email_stats ADD INDEX mau86_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 mau86_email_stats ADD INDEX mau86_generated_sent_date_email_id (gen’ at line 2 (uncaught exception) at /home4/joylocxl/connect.joyloan.in/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 79 while running console command doctrine:migrations:migrate
[2020-10-13 18:19:38] mautic.WARNING: Command doctrine:migrations:migrate exited with status code 1
[2020-10-13 18:19:38] mautic.ERROR: [UPGRADE ERROR] Exit code 1; ++ adding generated column generated_sent_date -> ALTER TABLE mau86_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 mau86_email_stats ADD INDEX mau86_generated_sent_date_email_id (generated_sent_date, email_id) \ In AbstractMySQLDriver.php line 79: \ An exception occurred while executing ‘ALTER TABLE mau86_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 mau86_email_stats ADD INDEX mau86_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 86_email_stats ADD INDEX mau86_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 86_email_stats ADD INDEX mau86_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] [–] \

Steps I have tried to fix the problem:

Is this a shared hosting ?

What version of Mysql or Mariadb are you using? Minimum MySQL version is 5.7 or MariaDB 10.2 .

All those errors look like a failed/failing upgrade.
Run the doctrine:schema:validate command via ssh and post results.

Yes. It is shared hosting…

SQL is showing 5.7.23-23

SSH is showing irrelevant option. It asks me to either Generate or Import Key.

There is another option to run some software command named Terminal: When I type " doctrine:schema:validate", it gives message command not found

I tried sudo -u joylocxl php /home4/joylocxl/connect.joyloan.in/bin/console doctrine:schema:validate, (as I do not know, what exact command to enter). There it gives error.
sudo: effective uid is not 0, is /bin/sudo in file system with nosuid option set or NFS file system without root privileges?

I tried with diffrent method to enter the command, but there is error in Terminal every time. Please provide format of the command to enter in the terminal?

I recommend MobaXTerm for ssh. I use it every day and wont use anything else.
The generate or import key it to securely login to your host. In my opinion your provider probably has blocked non secure connections as it is best practice or you were logging in for the first time from a new machine or with a new ssh client or both.
On line 3 of your local.php file does it say ‘db_driver’ => ‘pdo_mysql’, if not copy paste ‘db_driver’ => ‘pdo_mysql’, to line three, save it and then try.

Its in your app/config folder.
Can you do a dump of your current database and create a new one? Import the old database dump into the new one and change the appropriate setting your local file to have mautic connect to new database and see if the same errors occur?

Does your host provide you with Cpanel or Plesk?
Have you looked at file permissions?

Have you looked in your var/spool folder to see if its full of unsent messages?
Are you over your inode limit for your hosting package?

I know its alot of questions but the more we know the better we can help you troubleshoot these issues.

Third lines reads: ‘db_driver’ => ‘mysqli’

Shall I replace it with db_driver’ => ‘pdo_mysql?

Yes indeed. I want to try to get you logged in first. We can address the ssh commands after.

I change the code on third line. Now when I try to go to dashboard, it gives message:

Ready to Install!

Great! Your environment is ready for Mautic.

But I think, it is asking me to make install afresh. Then all my work within mautic will be lost

Post a screenshot of it. It may be trying to complete an incomplete upgrade. You can also try to run your ssh commands and see if the output differs as well.

On Mautic Installation - Database Setup it is showing database and database user name prefilled.

And option of back-up existing database is there

I have created RSA key in SSH and received the confirmation as below.

But I do not know how and where to run the command you suggested ( doctrine:schema:validate)

Line 121 in local.php says debug. set to true. save it.

ok. you have some choices here. You can try to validate your current schema , update your current schema via ssh.
You can do the update process over via ssh and see if it will complete with the updated driver.

Third is backup existing mautic database, click install, and see if you can connect to the old database by changing config in local.php

You should delete everything in this post between the first line and the last line. Like this:

Its a widely accepted practice not to post your encryption key info

Start with this and adjust based on the accepted format for your system.

Mine are
/opt/plesk/php/7.3/bin/php /var/www/vhosts/MAUTICINSTALL/bin/console mautic:segments:rebuild

(centos7 with plesk panel)
for example

Thank you so much for your guidance. Without you, it was not possible for me to correct this. :pray:

Method 3 worked for me :heart: (continue with install). Now it is working fine.

Can you please tell me, what had gone wrong? I just triggered my first email send on Monday.

What I can do to avoid it in future?

I can’t speculate beyond the steps we took to fix it. I’m glad to be of some assistance.