Your software
My Mautic version is: 3.1.2
My PHP version is: 7.2.24
My Database type and version is: AWS/RDS MySQL 8.0.20
Your problem
My problem is:
Configuration Page & SMS Channel cannot be accessed, everything else works fine.
The only change seems to have been with SMS.
Twilio was enabled and everything has been fine for the past week.
Today we created a new segment SMS message as a test, now when we go to the SMS channel we get a 500 “oops” error.
Also, now the configuration page just times out.
These errors are showing in the log:
No errors in any logfile except for the following timeout -
2020/12/18 01:59:02 [error] 4045#4045: *14 upstream timed out (110: Connection timed out) while reading response header from upstream, client: 68.3.179.64, server: www.131events.net, request: “GET /s/config HTTP/2.0”, upstream: “fastcgi://unix:/run/php/php7.2-fpm.sock”, host: “www.131events.net”
Steps I have tried to fix the problem:
-restored past local.php
-disabled Twilio
-Increase php memory limit
-cleared cache, restarted nginx & php…
-tried reviewing all the other similar issues here
It seems this is definitely connected to an SMS problem. I’ve recreated the initial error on SMS. Any ideas here?
[2020-12-18 10:15:30] mautic.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\TableNotFoundException: “An exception occurred while executing ‘SELECT COUNT(DISTINCT l.id) FROM sms_message_list_xref sml INNER JOIN mautic_lead_lists ll ON ll.id = sml.leadlist_id and ll.is_published = 1 INNER JOIN mautic_lead_lists_leads lll ON lll.leadlist_id = sml.leadlist_id and lll.manually_removed = 0 INNER JOIN mautic_leads l ON lll.lead_id = l.id WHERE (sml.sms_id = ?) AND (((l.mobile IS NOT NULL) OR (l.mobile <> ‘’)) OR ((l.phone IS NOT NULL) OR (l.phone <> ‘’))) AND (NOT EXISTS (SELECT null FROM mautic_sms_message_stats stat WHERE (stat.lead_id = l.id) AND (stat.sms_id = 1))) AND (NOT EXISTS (SELECT null FROM mautic_lead_donotcontact dnc WHERE (dnc.lead_id = l.id) AND (dnc.channel = ‘sms’))) AND (NOT EXISTS (SELECT null FROM mautic_message_queue mq WHERE (mq.lead_id = l.id) AND (mq.status <> ‘sent’) AND (mq.channel = ‘sms’))) ORDER BY lll.lead_id ASC’ with params [1]: SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘mautic.sms_message_list_xref’ doesn’t exist” at /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 42 {“exception”:"[object] (Doctrine\DBAL\Exception\TableNotFoundException(code: 0): An exception occurred while executing ‘SELECT COUNT(DISTINCT l.id) FROM sms_message_list_xref sml INNER JOIN mautic_lead_lists ll ON ll.id = sml.leadlist_id and ll.is_published = 1 INNER JOIN mautic_lead_lists_leads lll ON lll.leadlist_id = sml.leadlist_id and lll.manually_removed = 0 INNER JOIN mautic_leads l ON lll.lead_id = l.id WHERE (sml.sms_id = ?) AND (((l.mobile IS NOT NULL) OR (l.mobile <> ‘’)) OR ((l.phone IS NOT NULL) OR (l.phone <> ‘’))) AND (NOT EXISTS (SELECT null FROM mautic_sms_message_stats stat WHERE (stat.lead_id = l.id) AND (stat.sms_id = 1))) AND (NOT EXISTS (SELECT null FROM mautic_lead_donotcontact dnc WHERE (dnc.lead_id = l.id) AND (dnc.channel = ‘sms’))) AND (NOT EXISTS (SELECT null FROM mautic_message_queue mq WHERE (mq.lead_id = l.id) AND (mq.status <> ‘sent’) AND (mq.channel = ‘sms’))) ORDER BY lll.lead_id ASC’ with params [1]:\n\nSQLSTATE[42S02]: Base table or view not found: 1146 Table ‘mautic.sms_message_list_xref’ doesn’t exist at /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:42, Doctrine\DBAL\Driver\PDOException(code: 42S02): SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘mautic.sms_message_list_xref’ doesn’t exist at /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:123, PDOException(code: 42S02): SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘mautic.sms_message_list_xref’ doesn’t exist at /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:121)"}
I wondered if that was what I should do, thank you for confirming.
The configuration access issue took a while to resolve. It turned out that it was the gmail settings for monitored inbox. The setup had been working for months and then all of a sudden Gmail must have changed something so it was hanging, likely trying to validate those entries.
Really something needs to be changed in error handling for something like that. It was nearly impossible to diagnose, removing one component at a time from local.php to try and figure it out.
The SMS has still been a problem but I don’t use it much so I haven’t been back to that issue.
No luck with this. Same error message, base table not found.
[2021-02-06 14:53:50] mautic.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\TableNotFoundException: “An exception occurred while executing ‘SELECT COUNT(DISTINCT l.id) FROM sms_message_list_xref sml INNER JOIN mautic_lead_lists ll ON ll.id = sml.leadlist_id and ll.is_published = 1 INNER JOIN mautic_lead_lists_leads lll ON lll.leadlist_id = sml.leadlist_id and lll.manually_removed = 0 INNER JOIN mautic_leads l ON lll.lead_id = l.id WHERE (sml.sms_id = ?) AND (((l.mobile IS NOT NULL) OR (l.mobile <> ‘’)) OR ((l.phone IS NOT NULL) OR (l.phone <> ‘’))) AND (NOT EXISTS (SELECT null FROM mautic_sms_message_stats stat WHERE (stat.lead_id = l.id) AND (stat.sms_id = 2))) AND (NOT EXISTS (SELECT null FROM mautic_lead_donotcontact dnc WHERE (dnc.lead_id = l.id) AND (dnc.channel = ‘sms’))) AND (NOT EXISTS (SELECT null FROM mautic_message_queue mq WHERE (mq.lead_id = l.id) AND (mq.status <> ‘sent’) AND (mq.channel = ‘sms’))) ORDER BY lll.lead_id ASC’ with params [2]: SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘mautic.sms_message_list_xref’ doesn’t exist” at /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 42 {“exception”:"[object] (Doctrine\DBAL\Exception\TableNotFoundException(code: 0): An exception occurred while executing ‘SELECT COUNT(DISTINCT l.id) FROM sms_message_list_xref sml INNER JOIN mautic_lead_lists ll ON ll.id = sml.leadlist_id and ll.is_published = 1 INNER JOIN mautic_lead_lists_leads lll ON lll.leadlist_id = sml.leadlist_id and lll.manually_removed = 0 INNER JOIN mautic_leads l ON lll.lead_id = l.id WHERE (sml.sms_id = ?) AND (((l.mobile IS NOT NULL) OR (l.mobile <> ‘’)) OR ((l.phone IS NOT NULL) OR (l.phone <> ‘’))) AND (NOT EXISTS (SELECT null FROM mautic_sms_message_stats stat WHERE (stat.lead_id = l.id) AND (stat.sms_id = 2))) AND (NOT EXISTS (SELECT null FROM mautic_lead_donotcontact dnc WHERE (dnc.lead_id = l.id) AND (dnc.channel = ‘sms’))) AND (NOT EXISTS (SELECT null FROM mautic_message_queue mq WHERE (mq.lead_id = l.id) AND (mq.status <> ‘sent’) AND (mq.channel = ‘sms’))) ORDER BY lll.lead_id ASC’ with params [2]:\n\nSQLSTATE[42S02]: Base table or view not found: 1146 Table ‘mautic.sms_message_list_xref’ doesn’t exist at /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:42, Doctrine\DBAL\Driver\PDOException(code: 42S02): SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘mautic.sms_message_list_xref’ doesn’t exist at /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:123, PDOException(code: 42S02): SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘mautic.sms_message_list_xref’ doesn’t exist at /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:121)"}
What was the output when you ran these two commands via SSH?
Have you considered changing PHP version from 7.2.24 to 7.3x ?
Any relevant logs with RDS instance?
I just want to concur that I had what seems like the exact same situation, using Mautic v3.3.1. Having a wrong host entered into a configuration field completely hung the entire configuration part of Mautic.
Thanks to this post from EJL, I realized what it was. I had mistyped my host address for my monitored email. It was never able to connect, which I guess Mautic tries to do each time before it displays the configuration menu. I had to fix the error directly in the app/config/local.php. Very strange I could bring down a section of a web site with a seemingly passive typo. I hope this helps someone else!
After a long time scratching my head finally got this bug fixed. The reason is that the table prefix is not appeded in the query which is resulting table “sms_message_list_xref” not found. The fix is - to append the MAUTIC_TABLE_PREFIX in these 2 files -
Line#149 in file - app/bundles/SmsBundle/Entity/Sms.php ->setJoinTable(MAUTIC_TABLE_PREFIX.'sms_message_list_xref')
and in Line#75 in file app/bundles/SmsBundle/Entity/SmsRepository.php $q->from(MAUTIC_TABLE_PREFIX.'sms_message_list_xref', 'sml')
its kinda funny how the dev team never checked this.
I got this fixed. it is a silly mistake released on prod. The MAUTIC_TABLE_PREFIX is not appended with the table - “sms_message_list_xref”. Just append that it will be fixed
