Segment with filter "email open" not working

Product Support
1

Your software
My Mautic version is: 2.16.2
My PHP version is: 7.2

Your problem
I create a segment that should include all the contacts who have open a specific mail.

However, even if it should have 104 contacts (I can see this number in the statistic of the mail), it stays empty -> 0 contact.

When I manually enter “php -q console mautic:segments:update” in the console, I get the following error messages:

[Doctrine\DBAL\Exception\InvalidFieldNameException]
An exception occurred while executing 'SELECT count(leadIdPrimary) count, max(leadIdPrimary) maxId, min(leadIdPrimary) minId F
ROM (SELECT DISTINCT l.id as leadIdPrimary, iQbuzmtl.lead_id AS iQbuzmtl_lead_id FROM maleads l LEFT JOIN malead_lists_leads i
Qbuzmtl ON iQbuzmtl.lead_id = l.id and (iQbuzmtl.leadlist_id = 112) WHERE (EXISTS(SELECT NULL FROM maemail_stats PzuwTRFM WHER
E (PzuwTRFM.lead_id = l.id) AND (email_stats.is_read = 1) AND (PzuwTRFM.email_id IN (?)))) AND (iQbuzmtl.lead_id IS NULL)) sss
’ with params [88]:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘email_stats.is_read’ in ‘where clause’

[Doctrine\DBAL\Driver\PDOException]
SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘email_stats.is_read’ in ‘where clause’

[PDOException]
SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘email_stats.is_read’ in ‘where clause’

Any idea what happens? Everything else is working fine with this mautic installation.

Thanks for your support.

Claude

2

Maybe an update went wrong or something else broke your database.

First step is to check if the column exists in the table in the database.

3

Is there a way to re-create the database without loosing the current datas?

4

I have no idea: I’ve never tried…

Maybe you can try something like doctrine:schema:update --force…

But it is very risky and not guaranteed: if collected data are not complete, I don’t know which will be the behavior of Mautic…

Anyway, if you go this way, do a backup!

5

Could I somehow import all the data of the corrupted database into a new mautic installation?

6

Other than import, I would try to force a schema update: if you have partial data, also importing them in a fresh installation doesn’t solve the problem of incompleteness (if it exists at all).

Also, I think you can only export contacts…

Rebuilding segments, campaigns, emails, etc will be a very hard, long and tedious work.

Do a backup and try to force the update of the schema.

7

I forced the schema update… but there is still the same issue and same error messages when I update the segments!

8

Check the column exists.

And try to clear the cache.