500 error when selecting Channels->Text Messages

Product Support
1

Your software
My Mautic version is: 3.2.4
My PHP version is: 7.2
My Database type and version is: MySQL 5.7.31

Wondering if someone can help. Twilio installed, when I select CHANNELS -> TEXT MESSAGES I briefly see the list of one test message I was working on setting up and then it bounces immediately to a 500 error.

I believe it’s a table name error, this should probably be mautic_sms… and not mautic.sms… but I don’t know where to fix (or maybe my assumption is incorrect):
Base table or view not found: 1146 Table ‘mautic.sms_message_list_xref’ doesn’t exist

Full error message:
[2021-01-14 12:53:46] 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/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 = 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/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)"}

2

Hey were you able to get this fixed?

3

Nope, have kind of given up on it after trying so many different things to get it to work. No one seems to be able to answer the question.

4

have you considered updating to 3.3.1?

5

Yes, we’re completely current on Mautic versions. This problem has persisted through 3 updates of Mautic 3.x.x, as well as an update to php 7.4

6

/bin/console doctrine:schema:update --force I would try updating schema with this command. Check file permissions, and make sure line 3 of your app/config/local.php file line 3 is ‘db_driver’ => ‘pdo_mysql’,

7

Thank you, already have done the doctrine update, file permissions have been set multiple times and local.php has that setting.

I’ve even reinstalled completely, keeping only local.php

8

Honestly I thought the doctrine update would fix it. The error is in the query string format,
Mautic_
instead of
Mautic.

I can’t find anywhere why this one table reference isn’t formatted correctly

9

Is this happening when you use link tracking in SMS? (If the setting is set as YES in your config / sms settings)

10

No, that setting doesn’t make any difference.

It’s an error in the query build, a table name reference is incorrect, an underscore instead of a period.

It’s mautic_sms_message_list_xref instead of mautic.sms_message_list_xref

11

Oh okay :slight_smile:
Can you create a github issue?

12

Can you send a link to the correct repository for this?

Thanks

13
14

I created the bug report, thank you for your help.

15

Hi everyone, is there any updates on this? I am facing the same issue too.

16

Yes, this is a bug and it came back when I upgraded to 3.3.3. It’s a simple fix so I’m not sure why it hasn’t been resolved.

Link to fix: 500 error on SMS listing: /s/sms - bad query string · Issue #9814 · mautic/mautic · GitHub

Fix:

The tablename prefix was missing in the query builder. you just need to add. just need to add that at these 2 places:

line# 149 - app/bundles/SmsBundle/Entity/Sms.php
->setJoinTable(MAUTIC_TABLE_PREFIX.'sms_message_list_xref')

and on

line# 75 - app/bundles/SmsBundle/Entity/SmsRepository.php
$q->from(MAUTIC_TABLE_PREFIX.'sms_message_list_xref', 'sml')
2 Likes
17

Awesome. Thanks alot mate.