Segment with filter "email open" not working

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:

An exception occurred while executing 'SELECT count(leadIdPrimary) count, max(leadIdPrimary) maxId, min(leadIdPrimary) minId F
ROM (SELECT DISTINCT as leadIdPrimary, iQbuzmtl.lead_id AS iQbuzmtl_lead_id FROM maleads l LEFT JOIN malead_lists_leads i
Qbuzmtl ON iQbuzmtl.lead_id = and (iQbuzmtl.leadlist_id = 112) WHERE (EXISTS(SELECT NULL FROM maemail_stats PzuwTRFM WHER
E (PzuwTRFM.lead_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’

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

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.


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.

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

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!

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

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.

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

Check the column exists.

And try to clear the cache.