Segment:Update Cron Failing Since Update

Your software 4.4.2
My PHP version is : 7.4.30
My MariaDB version is (delete as applicable): 10.5.10-MariaDB-1:10.5.10+maria~xenial-log

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

These errors are showing in the installer :

These errors are showing in the Mautic log :

Your problem
My problem is : When running mautic:segments:update cron job

An exception occurred while executing ‘SELECT count(leadIdPrimary) count, m
ax(leadIdPrimary) maxId, min(leadIdPrimary) minId FROM (SELECT DISTINCT l.i
d as leadIdPrimary FROM leads l WHERE ((l.state IS NULL) OR (l.state NOT IN
())) AND (l.id IN (SELECT par2.lead_id FROM lead_tags_xref par2 WHERE par2
.tag_id IN ())) AND (l.id IN (SELECT par4.lead_id FROM lead_tags_xref par4
WHERE par4.tag_id IN ())) AND (NOT EXISTS(SELECT NULL FROM page_hits par7 W
HERE (par7.lead_id = l.id) AND ((par7.url = ?) OR (par7.url IS NULL)))) AND
(l.id NOT IN (SELECT par8.lead_id FROM lead_lists_leads par8 WHERE par8.le
adlist_id = ?))) sss’ with params [“https://mydomain.com/beneficiary-form-c
omplete/”, 5]:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error i
n your SQL syntax; check the manual that corresponds to your MariaDB server
version for the right syntax to use near ‘))) AND (l.id IN (SELECT par2.le
ad_id FROM lead_tags_xref par2 WHERE par2.tag…’ at line 1

In Exception.php line 18:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error i
n your SQL syntax; check the manual that corresponds to your MariaDB server
version for the right syntax to use near ‘))) AND (l.id IN (SELECT par2.le
ad_id FROM lead_tags_xref par2 WHERE par2.tag…’ at line 1

In PDOStatement.php line 117:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error i
n your SQL syntax; check the manual that corresponds to your MariaDB server
version for the right syntax to use near ‘))) AND (l.id IN (SELECT par2.le
ad_id FROM lead_tags_xref par2 WHERE par2.tag…’ at line 1

Steps I have tried to fix the problem :
schema status check
schema migration migrate
schema migration update --force
( and I get: SQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP INDEX IDX_1AE3441319EB6921; check that it exists)

Hi,

I might be wrong here, but looking at the query it looks like it generates an empty IN statement.

This usually indicates issue with segment filter config.

What happens, if you create a segment with only one simple filter? Does it fire/update OK?

Just got this figured out, in the upgrade, all the conditions to create the segment except for one were wiped out.

Replacing the values in the statement solved the problem.

1 Like

This topic was automatically closed 36 hours after the last reply. New replies are no longer allowed.