CallerID Lookup SQL statement truncates

FreePBX
1

I have a longer than usual (I guess) SQL statement that I must pass to MySQL in order to have the correct data returned. This statement utilises 2 tables and requires an IFNULL statement and 2 LEFT Joins. When I enter it via the FreePBX UI the statement gets cut off at character 255, understandable considering the length allowable in a HTML Input element. Can I enter the SQL statement directly into a CONF and bypass the UI? Or can anyone share a way in which I may reduce the length of my SQL statement without changing the database or its tables? The SQL statement is,
SELECT ifnull(tblDebtors.Pronounced,CONCAT(FirstName,’ ',LastName)) AS username
FROM tblComms
LEFT JOIN tblContacts ON tblComms.ContactID = tblContacts.ContactID
LEFT JOIN tblDebtors ON tblContacts.UserID = tblDebtors.DebtorID
WHERE tblComms.CircuitDetails LIKE '%[NUMBER]%'
GROUP BY username

2

Originally this column was “Text”

A TEXT column with a maximum length of 255 (28 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each TINYTEXT value is stored using a 1-byte length prefix that indicates the number of bytes in the value. A BLOB column with a maximum length of 65,535 (216 − 1) bytes.

So your assertion makes sense. In very recent versions of 13 we have started managing the schema with doctrine where we set this field as a “string” which defaults to varchar set to 255 chars. The best course of action would be to file a feature request at http://issues.freepbx.org

Note we are pushing feature requests to 14+ so this may not help for 13. You can manually expand the column

alter table cidlookup modify mysql_query VARCHAR(512);

but this won’t survive an update. When the table structure is reset it will likely truncate the field to 255

3

Thank you for your kind and quick explanation of the technicalities behind my problem, and I would like to expand further on modifying the table even if it requires further modification at a later date. I do have Webmin installed on my local Windows computer which I use to connect to my FreePBX system, this has proven very useful with Postfix issues I have been having, but Webmin did not install the MySQL module so I wasn’t sure how to actually connect to the database behind FreePBX in order to alter the cidlookup table.

Can anyone please help in explaining how I connect to the FreePBX database in order to alter the cidlookup table please.

4

Login as root and get MySQL with:

mysql

At the Asterisk MySQL CLI:

use asterisk;
alter table cidlookup modify mysql_query VARCHAR(512);
5

Thanks Lorne, the instructions were clear and extremely helpful, one small misunderstanding was when you said at the Asterisk CLI, I soon realised that you meant at the MySQL CLI ;-). I’ll have to wait for on of the Debtor’s to ring in now to see if it works as hoped.
Shame I can’t get the MySQL module in Webmin working, Webmin can’t find MySQL, I guess because it is not in it’s normal location, any clues please?

1 Like
6

OK, found out how what I have to do to manage my FreePBX MySQL database from inside Webmin.
First, the MySQL server module is available in the un-used modules section because Webmin can’t find the MySQL server as it is in a non-standard location. Change the System Configuration within the MySQL Server module so that the file locations for all the executables look in /usr/bin/
for example /usr/bin/mysqlshow, then change the following to,
command to Start MySQL - /usr/bin/mysql start
command to Stop MySQL - /usr/bin/mysql stop
path to mysqld command - /usr/bin
Presto, MySQL Server module works a treat. Login with root and no password.