Campaigns Trigger Causes SQLSTATE[42000] Error

Product Support
1

Hi Guys,



I usually don’t post but… well - this is a particularly painful issue for me. It looks like enough people might be going through this challenge - so I’m hoping this issue can get cleared out. I’ll try to give as much detail as possible - about my environment, the exact errors (and how I see them), and how the existing documented solutions don’t seem to address my issue.



Environment

I’m using a Bitnami AWS instance, PHP Version 5.6.25, and run Mautic 2.7.1.



Campaign

This is a Segment based Campaign. It’s a pretty straight forward 4 week ‘drip’ of email based on Contacts in a specific segment.





Errors

When running mautic:campaigns:trigger, I get the following::

Quote:
[DoctrineDBALExceptionSyntaxErrorException]
An exception occurred while executing 'SELECT c.*, l.lead_id, l.is_primary FROM ma_companies c INNER JOIN ma_companies_leads l ON l.company_id = c.id WHERE (l
.manually_removed = 0) AND (l.lead_id IN ()) ORDER BY l.date_added, l.company_id DESC':
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version f
or the right syntax to use near ')) ORDER BY l.date_added, l.company_id DESC' at line 1

[DoctrineDBALDriverPDOException]
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version f
or the right syntax to use near ')) ORDER BY l.date_added, l.company_id DESC' at line 1

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version f
or the right syntax to use near ')) ORDER BY l.date_added, l.company_id DESC' at line 1


Solution 1
https://www.mautic.org/community/index.php/1466-database-error

Solution 1 suggests that I update based on GitHub Pull Request 1286 (https://github.com/mautic/mautic/pull/1286). There are several problems or concerns with this solution.

- This dates back to 2015 (before Companies were introduced).
- This is for Campaigns based on forms only
- This fix seems to break the Mautic queue and forces you to change to immediate delivery (as reported by @felipe )
- This fix seems to work on preventing the SQL exception but doesn't actually fix the SQL query.
- I`ve tested the PR and it DID NOT fix my error

Solution 2
https://www.mautic.org/community/index.php/434-campaign-update-console-command-causes-sql-error

Solution 2 suggests that I update based on GitHub Pull Request 533 (https://github.com/mautic/mautic/pull/533). Again, there are several problems or concerns with this solution.

- This dates back to 2015 (before Companies were introduced).
- This is for Campaigns based on forms only
- This fix seems to work on preventing the SQL exception but doesn't actually fix the SQL query.
- I`ve tested the PR and it DID NOT fix my error

btw -- YES. I cleared the cache and my browser cache when testing the two PR's above.


Solution 3
https://www.mautic.org/community/index.php/980-mautic-campaigns-trigger-dbalexception

Solution 3 suggests that I update based on GitHub Pull Request 906 (https://github.com/mautic/mautic/pull/906). For obvious reasons (this is from 2015 and is focused on form field based Campaigns), it doesn't make sense for me to test this. I just want to demonstrate that I've been looking up and down for a solution.


SO -- I'll continue to comb the forums. Has anyone fixed this issue?

Ray
2

Hi Guys,

I usually don’t post but… well - this is a particularly painful issue for me. It looks like enough people might be going through this challenge - so I’m hoping this issue can get cleared out. I’ll try to give as much detail as possible - about my environment, the exact errors (and how I see them), and how the existing documented solutions don’t seem to address my issue.

Environment
I’m using a Bitnami AWS instance, PHP Version 5.6.25, and run Mautic 2.7.1.

Campaign
This is a Segment based Campaign. It’s a pretty straight forward 4 week ‘drip’ of email based on Contacts in a specific segment.

Errors
When running mautic:campaigns:trigger, I get the following::

[quote][DoctrineDBALExceptionSyntaxErrorException]
An exception occurred while executing ‘SELECT c.*, l.lead_id, l.is_primary FROM ma_companies c INNER JOIN ma_companies_leads l ON l.company_id = c.id WHERE (l
.manually_removed = 0) AND (l.lead_id IN ()) ORDER BY l.date_added, l.company_id DESC’:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version f
or the right syntax to use near ‘)) ORDER BY l.date_added, l.company_id DESC’ at line 1

[DoctrineDBALDriverPDOException]
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version f
or the right syntax to use near ‘)) ORDER BY l.date_added, l.company_id DESC’ at line 1

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version f
or the right syntax to use near ‘)) ORDER BY l.date_added, l.company_id DESC’ at line 1 [/quote]

Solution 1
https://www.mautic.org/community/index.php/1466-database-error

Solution 1 suggests that I update based on GitHub Pull Request 1286 (https://github.com/mautic/mautic/pull/1286). There are several problems or concerns with this solution.

  • This dates back to 2015 (before Companies were introduced).
  • This is for Campaigns based on forms only
  • This fix seems to break the Mautic queue and forces you to change to immediate delivery (as reported by @felipe )
  • This fix seems to work on preventing the SQL exception but doesn’t actually fix the SQL query.
  • I`ve tested the PR and it DID NOT fix my error

Solution 2
https://www.mautic.org/community/index.php/434-campaign-update-console-command-causes-sql-error

Solution 2 suggests that I update based on GitHub Pull Request 533 (https://github.com/mautic/mautic/pull/533). Again, there are several problems or concerns with this solution.

  • This dates back to 2015 (before Companies were introduced).
  • This is for Campaigns based on forms only
  • This fix seems to work on preventing the SQL exception but doesn’t actually fix the SQL query.
  • I`ve tested the PR and it DID NOT fix my error

btw – YES. I cleared the cache and my browser cache when testing the two PR’s above.

Solution 3
https://www.mautic.org/community/index.php/980-mautic-campaigns-trigger-dbalexception

Solution 3 suggests that I update based on GitHub Pull Request 906 (https://github.com/mautic/mautic/pull/906). For obvious reasons (this is from 2015 and is focused on form field based Campaigns), it doesn’t make sense for me to test this. I just want to demonstrate that I’ve been looking up and down for a solution.

SO – I’ll continue to comb the forums. Has anyone fixed this issue?

Ray

3

Yes – HOWEVER – I had these same problems & errors before the upgrade.

4

SO – consider these errors valid for both 2.5.1 AND 2.7.1

5

I hate to reply emotionally but… A lot of support responses assume that we haven’t spent hours on the forums reading through a gazillion unanswered threads in desperation to save our Mautic installation and preserve our faith in something that we’ve put our hopes in. We are the silent community members that wish that we could actually affect Mautic in a positive way without getting the tough love support that thinks we’re idiots…

ok - please ignore any attitude you feel there. How can I say that I have spent exponentially more time looking for a solution in the forums and DOING solutions suggested in the forums (AS YOU CAN SEE IN MY INITIALPOST)… I spent more time trying to fix things than I have done marketing this week.

YES. I followed the instructions for the 2.7.1 upgrade TO THE T…

Once again, THIS DID NOT WORK BEFORE THE UPGRADE. I want to make sure that everyone understands that the issue that I’m reporting should be considered as a fact for 2.5.1 (for bitnami users with similar builds at least).

IF there a weird reason that I should try to fix a 2.7.1 broken feature that was already not functioning for 2.5.1, there was an occasion to try the option that you referenced. (And I did it — not what I want to do on a production system)

Sorry to be Snarky. You might be aware from other threads but – I have a client that updated Mautic from 2.5.1 to 2.7.1, hosed their system, completely lost faith, etc… YES - I agree that their junior sys admin shouldn’t have upgraded without consulting me. BUT end of the day, Mautic versioning should be more stable and shouldn’t have put me & my client in this position. Long story short, Campaigns have not worked since 2.5.1, there are SQL issues, and I need them fixed. I’ll prove that I’ve done everything possible without hosing my system if you need.

I have done Steps 1 - 4.


From what I read, Step 5 would completely blow out my system including my database (pretty much the same as deleting my Bitnami instance, creating a new one, and updating that to the latest version).

Can you tell me more about Step 5? Apologies but, from the level of support that I see from the forums, I don’t trust that the suggestion has any respect or consideration that this is a production system with valuable data. I’m ok with moving forward with Step 5 if you advise but… What’s going to happen with my data?

No disrespect but… if you want to stay engaged, I can show you the specific SQL Errors related to ‘INDEX’ or Foreign Key references around schema upgrades. I think this could be a variable. to consider.

  • Can you confirm that those errors are unrelated?
  • Can you tell me why an update that affects database schema can proceed without a successful schema update?
  • I manually looked at all the schema changes (based on the update) and manually ran all the SQL updates possible. ALL of the failures were related to INDEX and Foreign Keys. Have you even seen these errors?

IF we can’t slowly step through issues in this way, then…


I’m concerned about 2 production systems. Is there a way of backing up existing data, blowing out the system (with a clean database and app) then restoring data with that new db schema?

Respectfully,

Ray

6

Good question. I’m not sure of the original installation version.

This seems painful but I need this fixed so let’s jump into the details.

Do you have suggestions / questions about the schema and the specific db errors?

7

Hey Guys — What are my next steps? Can I provide more detail so to better clarify the issue? Is this a known issue? Is this a new bug? Is there a clear path to resolution?

Thanks in advance.

Ray

8

@JoPitts — have you seen this SQLState 42000 error? This is the specific Syntax Error ::

[DoctrineDBALExceptionSyntaxErrorException]
An exception occurred while executing ‘SELECT c.*, l.lead_id, l.is_primary FROM ma_companies c INNER JOIN ma_companies_leads l ON l.company_id = c.id WHERE (l
.manually_removed = 0) AND (l.lead_id IN ()) ORDER BY l.date_added, l.company_id DESC’:

Do you know what file has this SQL query? If there’s no documented solution, I’d love to jump in the code and see if I can manually fix the SQL syntax. Can you point me to the correct file?

Thanks,

Ray

9

@gigcity , mate - I’ve got no idea.
can you try adding a lead so that the l.lead_in IN () actually has a value. I doubt that is where the problem lies, but you never know.

10

@JoPitts – I’m pretty sure the query would work if there was a list of lead ID’s there. I’m guessing that the system isn’t adding id’s to that list.

Question – What’s an easy way to manually back up all existing data? Can I then blow out this installation with a clean one and restore that old data?

11

Another question — Are campaigns working on fresh 2.7.1 installations? I see so many complaints about campaigns. I would love to understand if this is a reasonable expectation.

12

When running this command from SSH:
php app/console doctrine:migration:migrate

I get the following errors:

Migration 20160606000000 failed during Execution. Error An exception occurred while executing ‘CREATE INDEX ma_campaign_leads ON ma_campaign_leads (campaign_id, manually_removed, date_added, lead_id)’:

SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name ‘ma_campaign_leads’

@redneckbob @JoPitts - it seems that I have 60 or so doctrine:migration things to process. These apparently have not run via the normal upgrade process. In that I’m running a Bitnami instance and have done simple updates using the web UI, I wouldn’t be surprised if many of the other campaign related issues being reported are related to this.

@LilyAF – can you try running the same command? I’m curious if you get the same result.

thoughts?

13

btw – more specifically, the files here: /opt/bitnami/apps/mautic/htdocs/app/migrations/ are the ones that seem to throw SQL errors when running the migration command.

Here’s another example error:

Migration 20170108012944 failed during Execution. Error An exception occurred while executing 'ALTER TABLE ma_dynamic_content_lead_data DROP FOREIGN KEY ':

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 1

From what I’m seeing now, the issues that I’m experiencing are actually described on the top page of Mautic’s github ---- "If the source and/or database schema gets out of sync with Mautic’s releases, the release updater may not work and will require manual updates. "

Once again, I haven’t touched github and have just run updates from the UI. Same thing with my client that has a hosed box.

Apologies for the ALL CAPS but

IS SOMEONE THAT WORKS FOR MAUTIC GOING TO MAGICALLY APPEAR AND PRONOUNCE THIS A BUG ANYTIME SOON?

I really appreciate the community contributions to the thread but… I’m wasting my time reporting errors unless someone that understands this weighs in – Or even points me to the documentation so I can teach myself how to fix it.

Respectfully,

Ray

14

@gigcity - You said “can you try running the same command? I’m curious if you get the same result.”

I am sorry, as I said, I am a normal person when it comes to this. I do not understand what you are saying. Where and how do I run this command, and what does it mean if I get the same error message, and how is that going to help me? I need somebody to explain this like you’re walking your grandpa through it.

15

Hi @LilyAF – if you have SSH (terminal) access to your Mautic installation, you can run that command there. Of course, I’m assuming that you have root access to your machine. No worries if that’s not the case.

16

When running “sudo php app/console doctrine:migration:migrate”, I was getting errors. You can see a few unrun migration documents here: app/migrations/* You’ll see files like this: Version20170108012944.php

From this behavior, you can start to see database / schema updates which have been failing from upgrade to upgrade. I figured out a few and I manually ran the following:

ALTER TABLE `ma_campaign_events`
ADD `channel` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
ADD `channel_id` int(11) DEFAULT NULL,
ADD PRIMARY KEY (`id`),
ADD KEY `IDX_8EC42EE7F639F774` (`campaign_id`),
ADD KEY `IDX_8EC42EE7727ACA70` (`parent_id`),
ADD KEY `campaign_event_type_search` (`type`,`event_type`),
ADD KEY `event_type` (`event_type`),
ADD KEY `ma_campaign_event_channel` (`channel`,`channel_id`);


ALTER TABLE `ma_campaign_leads` 
ADD `date_last_exited` datetime DEFAULT NULL COMMENT '(DC2Type:datetime)',
ADD `rotation` int(11) NOT NULL;


ALTER TABLE `ma_campaign_lead_event_log`
ADD `id` int(11) NOT NULL AUTO_INCREMENT,
ADD `rotation` int(11) NOT NULL,
ADD UNIQUE KEY `id` (`id`),
ADD UNIQUE KEY `ma_campaign_rotation` (`event_id`,`lead_id`,`rotation`),
ADD KEY `ma_campaign_event_upcoming_search` (`is_scheduled`,`lead_id`),
ADD KEY `ma_campaign_leads` (`lead_id`,`campaign_id`,`rotation`),
ADD KEY `ma_campaign_log_channel` (`channel`,`channel_id`,`lead_id`),
ADD KEY `IDX_AD8B90DA71F7E88B` (`event_id`),
ADD KEY `IDX_AD8B90DA55458D` (`lead_id`),
ADD KEY `IDX_AD8B90DAF639F774` (`campaign_id`),
ADD KEY `IDX_AD8B90DAA03F5E9F` (`ip_id`);

Obviously, I put this together specific to my system. I would recommend someone running this sql without seeing specific errors about missing database columns related to what I’m adding above.

With what I’m describing above, I can smoothly upgrade from 2.5.1 to 2.7.1. As a test, I ran through this successfully twice this evening.

I’m still getting SQLState 42000 errors when attempting to manually trigger a campaign trigger – but from what I’m seeing above, I’m positive that there’s a schema issue.

17

And here’s the validation that the database schema is not in sync:

When I run this:

sudo php app/console doctrine:schema:validate

I get the following:

[Mapping]  FAIL - The entity-class 'MauticDynamicContentBundleEntityDynamicContentLeadData' mapping is invalid:
* The association MauticDynamicContentBundleEntityDynamicContentLeadData#dynamicContent refers to the inverse side field MauticDynamicContentBundleEntityDynamicContent#id which is not defined as association.
* The association MauticDynamicContentBundleEntityDynamicContentLeadData#dynamicContent refers to the inverse side field MauticDynamicContentBundleEntityDynamicContent#id which does not exist.

[Mapping]  FAIL - The entity-class 'BazingaOAuthServerBundleModelAccessToken' mapping is invalid:
* The field 'BazingaOAuthServerBundleModelAccessToken#expiresAt' uses a non-existant type 'int'.

[Mapping]  FAIL - The entity-class 'BazingaOAuthServerBundleModelRequestToken' mapping is invalid:
* The field 'BazingaOAuthServerBundleModelRequestToken#expiresAt' uses a non-existant type 'int'.

[Database] FAIL - The database schema is not in sync with the current mapping file.

@redneckbob – How do I bump this to an administrator and get a developer’s help on this?

18

To be honest, I just started using Mautic last week and still stumbling around the “mautic way”, so I can only speak to a small slice of the overall functionality.

I have setup a couple different Contacts, Segments, Campaigns, and delivered through a couple Email segments. Works fine for what I’m doing, which is far from complex. Works fine for text messaging via Twillio too.

The one irritant is having to wait on chron to update the segment, campaign, etc. I wonder what happens if you go create the campaign off a segment that has yet to update by chron.

Also, I relay mail out through SendGrid.

19

@redneckbob

Here’s a little time saver - if you have SSH access, you can run these instead of waiting around:

php -q app/console mautic:segments:update
php -q app/console mautic:campaigns:rebuild
php -q app/console mautic:campaigns:trigger
php -q app/console mautic:emails:send --do-not-clear
php -q app/console mautic:webhooks:process
php -q app/console mautic:email:fetch --env=prod
php -q app/console mautic:social:monitoring --env=prod

Those are pretty much what your cron jobs are calling every 15 minutes or so.

20

btw - I’m very curious about campaigns on a fresh install. I also would like to know an easy way to backup contacts in way that I could import my existing data into a clean installation (without hosing the clean installation’s database schema).

Should there be someone on this forum that can answer these questions?