Column not found: 1054 Unknown column '-' in 'generated column function' when upgrading from 3.0.2 to 3.1

Your software
My PHP version is : 7.2
My MySQL version is (delete as applicable): MySQL 8

Updating/Installing Errors
I am (delete as applicable): Updating
Upgrading/installing via (delete as applicable) : Web or Command Line

These errors are showing in the installer :

Constant page redirects

These errors are showing in the Mautic log :

[2020-08-30 06:10:25] console.ERROR: Error thrown while running command "doctrine:migrations:migrate --quiet --no-interaction". Message: "An exception occurred while executing 'ALTER TABLE 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 email_stats ADD INDEX `generated_sent_date_email_id`(generated_sent_date, email_id)':  SQLSTATE[42S22]: Column not found: 1054 Unknown column '-' in 'generated column function'" {"exception":"[object] (Doctrine\\DBAL\\Exception\\InvalidFieldNameException(code: 0): An exception occurred while executing 'ALTER TABLE 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)';\n            ALTER TABLE email_stats ADD INDEX `generated_sent_date_email_id`(generated_sent_date, email_id)':\n\nSQLSTATE[42S22]: Column not found: 1054 Unknown column '-' in 'generated column function' at /var/www/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:60, Doctrine\\DBAL\\Driver\\PDOException(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column '-' in 'generated column function' at /var/www/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:80, PDOException(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column '-' in 'generated column function' at /var/www/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:75)","command":"doctrine:migrations:migrate --quiet --no-interaction","message":"An exception occurred while executing 'ALTER TABLE 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)';\n            ALTER TABLE email_stats ADD INDEX `generated_sent_date_email_id`(generated_sent_date, email_id)':\n\nSQLSTATE[42S22]: Column not found: 1054 Unknown column '-' in 'generated column function'"} []

These errors are showing in the upgrade_log.txt file (located in the root of your Mautic instance when an upgrade has been attempted - ensure you remove or redact any sensitive data such as domain names in the file path) :

upgrade_log.txt does not exist.

Your problem
My problem is :

When running the upgrade from either the web interface or the command line, the upgrade fails during schema migration with the error above.

This results in the Mautic installation getting stuck in an infinite redirect loop in the browser and breaks the installation.

When running php bin/console mautic:update:apply, I am told to run it twice by the CLI, the second time with the --finish flag, and this is the point at which it fails.:

-bash-4.2$ /opt/remi/php72/root/bin/php bin/console mautic:update:find
Version 3.1.0 of Mautic is available for download. Please visit https://github.com/mautic/mautic/releases/tag/3.1.0 for more information.
To update, you can run 'php app/console mautic:update:apply' from the command line.
-bash-4.2$ /opt/remi/php72/root/bin/php bin/console mautic:update:apply
Are you sure you wish to update Mautic to the latest version? yes
Step    5 [----->----------------------] Clearing the cache

<warning>IMPORTANT: Run the same command again with --finish. For example 'php bin/console mautic:update:apply --finish'</warning>
-bash-4.2$ /opt/remi/php72/root/bin/php bin/console mautic:update:apply --finish
Step    2 [-->-------------------------] Migrating database schema...

An error occurred while updating the database. Check log for more details.

Steps I have tried to fix the problem :

Tried installing from both the CLI and the web UI, to no avail

1 Like

@MBConsultingUK is this issue related to the email builder issue?

Not as far as Iā€™m aware, this is just from doing an upgrade.

Sorry for this late reaction on this topic, but I just wanted to install Mautic 3.1.2 and got the same error.
What can I do to fix this?

@MBConsultingUK

the problem are here:

generated_sent_date DATE AS (CONCAT(YEAR(date_sent), "-", LPAD(MONTH(date_sent), 2, "0"), "-", LPAD(DAY(date_sent), 2, "0"))) COMMENT '(DC2Type:generated)', 

fix quotes, from double quotes (") to simple quotes (')

generated_sent_date DATE AS (CONCAT(YEAR(date_sent), '-', LPAD(MONTH(date_sent), 2, '0'), '-', LPAD(DAY(date_sent), 2, '0'))) COMMENT '(DC2Type:generated)', 

You can create table with this code:

CREATE TABLE email_stats (
id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
email_id INT UNSIGNED DEFAULT NULL,
lead_id BIGINT UNSIGNED DEFAULT NULL,
list_id INT UNSIGNED DEFAULT NULL,
ip_id INT UNSIGNED DEFAULT NULL,
copy_id VARCHAR(32) DEFAULT NULL,
email_address VARCHAR(191) NOT NULL,
date_sent DATETIME NOT NULL,
is_read TINYINT(1) NOT NULL,
is_failed TINYINT(1) NOT NULL,
viewed_in_browser TINYINT(1) NOT NULL,
date_read DATETIME DEFAULT NULL,
tracking_hash VARCHAR(191) DEFAULT NULL,
retry_count INT DEFAULT NULL,
source VARCHAR(191) DEFAULT NULL,
source_id INT DEFAULT NULL,
tokens LONGTEXT DEFAULT NULL COMMENT ā€˜(DC2Type:array)ā€™,
open_count INT DEFAULT NULL,
last_opened DATETIME DEFAULT NULL,
open_details LONGTEXT DEFAULT NULL COMMENT ā€˜(DC2Type:array)ā€™,
generated_sent_date DATE AS (
CONCAT(
YEAR(date_sent),
ā€˜-ā€™,
LPAD(MONTH(date_sent), 2, ā€˜0ā€™),
ā€˜-ā€™,
LPAD(DAY(date_sent), 2, ā€˜0ā€™)
)
) COMMENT ā€˜(DC2Type:generated)ā€™,
INDEX IDX_CA0A2625A832C1C9 (email_id), INDEX IDX_CA0A262555458D (lead_id), INDEX IDX_CA0A26253DAE168B (list_id),
INDEX IDX_CA0A2625A03F5E9F (ip_id), INDEX IDX_CA0A2625A8752772 (copy_id), INDEX stat_email_search (email_id, lead_id),
INDEX stat_email_search2 (lead_id, email_id), INDEX stat_email_failed_search (is_failed), INDEX is_read_date_sent (is_read, date_sent),
INDEX stat_email_hash_search (tracking_hash), INDEX stat_email_source_search (source, source_id), INDEX email_date_sent (date_sent),
INDEX email_date_read_lead (date_read, lead_id),
INDEX generated_sent_date_email_id (generated_sent_date, email_id),
PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC
;

or in file

Blockquote ./app/bundles/EmailBundle/EventListener/GeneratedColumnSubscriber.php

you can find this file and problem using this command at ssh

find ./ -type f -exec grep -H 'generated_sent_date' {} \;
1 Like

Linking to Github issue: