4.2: Can't send email, error 500, foreign key constraint fails

Product Support
1

Your software
My Mautic version is: 4.2
My PHP version is: 7.4.27
My Database type and version is: MariaDB 10.5.15

Your problem
My problem is: Error 500 on attempting to send an email.

These errors are showing in the log:

[2022-03-05 22:08:11] mautic.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\ForeignKeyConstraintViolationException: "An exception occurred while executing ‘INSERT INTO mauticemail_stats (email_address, date_sent, is_read, is_failed, viewed_in_browser, date_read, tracking_hash, retry_count, source, source_id, tokens, open_count, last_opened, open_details, email_id, lead_id, list_id, ip_id, copy_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)’ with params [ {{email content removed}} “, 0, null, “a:0:{}”, 25, “77”, 1, null, “4bfeaf7a3350744da59befbff9c56667”]:\n\nSQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (mautic.mauticemail_stats, CONSTRAINT FK_8EA2B849A8752772 FOREIGN KEY (copy_id) REFERENCES mauticemail_copies (id) ON DELETE SET NULL) at /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:68, Doctrine\DBAL\Driver\PDO\Exception(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (mautic.mauticemail_stats, CONSTRAINT FK_8EA2B849A8752772 FOREIGN KEY (copy_id) REFERENCES mauticemail_copies (id) ON DELETE SET NULL) at /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (mautic.mauticemail_stats, CONSTRAINT FK_8EA2B849A8752772 FOREIGN KEY (copy_id) REFERENCES mauticemail_copies (id) ON DELETE SET NULL) at /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:112)”}

Steps I have tried to fix the problem: reinstall files, ran php bin/console doctrine:migration:status and php bin/console doctrine:migration:migrate with no errors and nothing outstanding.

I have also gone into the MariaDB table for mauticemail_copies and deleted the most recent edition along with the corresponding row in mauticemails with no success.

2

Still unable to fix the problem. Deleting the email has no effect. Have also run optimize table on the affected tables listed in the query.

3

Still unable to get past this problem. In my shell script, these are the commands I run:

find /home/mauticmail -type f -size 0b -delete &
php /var/www/html/bin/console cache:clear
php /var/www/html/bin/console mautic:social:monitoring &
php /var/www/html/bin/console mautic:segments:update --force &
php /var/www/html/bin/console mautic:segments:rebuild &
php /var/www/html/bin/console mautic:messages:send &
php /var/www/html/bin/console mautic:import &
php /var/www/html/bin/console mautic:emails:send --force &
php /var/www/html/bin/console mautic:email:fetch &
php /var/www/html/bin/console mautic:campaigns:trigger --force &
php /var/www/html/bin/console mautic:campaigns:rebuild --force &
php /var/www/html/bin/console mautic:campaigns:messages &
php /var/www/html/bin/console mautic:campaigns:messagequeue &
php /var/www/html/bin/console mautic:broadcasts:send &
php /var/www/html/bin/console mautic:maintenance:cleanup --days-old=365 --no-interaction &
php /var/www/html/bin/console mautic:queue:process &

4

Can you post “SHOW CREATE TABLE” for mauticemail_stats, mauticemail_copies

maybe the types are not set correctly (eg: int(11) vs int(10) on the other table)

5

Run this command and copy-paste its output please:

bin/console doctrine:schema:update --dump-sql | grep email
1 Like
6

CREATE INDEX mauticunique_identifier_search ON mauticcompanies (companyemail, companyname);

7

| mauticemail_stats | CREATE TABLE mauticemail_stats (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
email_id int(10) unsigned DEFAULT NULL,
lead_id bigint(20) unsigned DEFAULT NULL,
list_id int(10) unsigned DEFAULT NULL,
ip_id int(10) 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(11) DEFAULT NULL,
source varchar(191) DEFAULT NULL,
source_id int(11) DEFAULT NULL,
tokens longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ‘(DC2Type:array)’,
open_count int(11) DEFAULT NULL,
last_opened datetime DEFAULT NULL,
open_details longtext DEFAULT NULL COMMENT ‘(DC2Type:array)’,
generated_sent_date date GENERATED ALWAYS AS (concat(year(date_sent),’-’,lpad(month(date_sent),2,‘0’),’-’,lpad(dayofmonth(date_sent),2,‘0’))) VIRTUAL COMMENT ‘(DC2Type:generated)’,
PRIMARY KEY (id),
KEY IDX_8EA2B849A832C1C9 (email_id),
KEY IDX_8EA2B84955458D (lead_id),
KEY IDX_8EA2B8493DAE168B (list_id),
KEY IDX_8EA2B849A03F5E9F (ip_id),
KEY IDX_8EA2B849A8752772 (copy_id),
KEY mauticstat_email_search (email_id,lead_id),
KEY mauticstat_email_search2 (lead_id,email_id),
KEY mauticstat_email_failed_search (is_failed),
KEY mauticis_read_date_sent (is_read,date_sent),
KEY mauticstat_email_hash_search (tracking_hash),
KEY mauticstat_email_source_search (source,source_id),
KEY mauticemail_date_sent (date_sent),
KEY mauticemail_date_read_lead (date_read,lead_id),
KEY mauticgenerated_sent_date_email_id (generated_sent_date,email_id),
CONSTRAINT FK_8EA2B8493DAE168B FOREIGN KEY (list_id) REFERENCES mauticlead_lists (id) ON DELETE SET NULL,
CONSTRAINT FK_8EA2B84955458D FOREIGN KEY (lead_id) REFERENCES mauticleads (id) ON DELETE SET NULL,
CONSTRAINT FK_8EA2B849A03F5E9F FOREIGN KEY (ip_id) REFERENCES mauticip_addresses (id),
CONSTRAINT FK_8EA2B849A832C1C9 FOREIGN KEY (email_id) REFERENCES mauticemails (id) ON DELETE SET NULL,
CONSTRAINT FK_8EA2B849A8752772 FOREIGN KEY (copy_id) REFERENCES mauticemail_copies (id) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=6454505 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC |

1 Like
8

| mauticemail_copies | CREATE TABLE mauticemail_copies (
id varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
date_created datetime NOT NULL,
body longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
subject longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC |

1 Like
9

the table column on mauticemail_stats is
copy_id varchar(32) DEFAULT NULL,

and on mauticemail_copies it’s defined as
id varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,

so you need to make sure both tables are encoded with the same collation.

You need to convert all the tables into the same (preferability utf8mb4_unicode_ci) on all tables, there’s some other posts on this forum and github related to this issue. One issue you may face after trying to convert the columns to utf8mb4_unicode_ci, is that the index is bigger than what’s supported on mysql/mariadb, so you need to reduce the column from 255 to 191 chars