SQL errors after FreePBX 2.11 upgrade

Hi,

I upgraded FreePBX from 2.10 to 2.11.0.24 over the weekend. Everything seems to be working fine but once a day, I’m getting a series of messages in the log complaining about a SQL error:

[2014-03-04 05:28:31] VERBOSE[1955] netsock2.c: == Using SIP RTP TOS bits 184
[2014-03-04 05:28:31] VERBOSE[1955] netsock2.c: == Using SIP RTP CoS mark 5
[2014-03-04 05:28:31] WARNING[1924] res_odbc.c: SetConnectAttr (Txn isolation) returned an error: HY000: [MySQL][ODBC 5.1 Driver]MySQL server has gone away
[2014-03-04 05:28:31] WARNING[1924] res_odbc.c: SQL Execute returned an error -1: 08S01: [MySQL][ODBC 5.1 Driver][mysqld-5.1.61]MySQL server has gone away (65)
[2014-03-04 05:28:31] WARNING[1924] res_odbc.c: SQL Execute error -1! Verifying connection to asteriskcdrdb [MySQL-asteriskcdrdb]…
[2014-03-04 05:28:31] WARNING[1924] res_odbc.c: Connection is down attempting to reconnect…
[2014-03-04 05:28:31] VERBOSE[31850] pbx.c: – Executing [[email protected]:1] NoOp(“SIP/losangeles2.voip.ms-00000010”, “Catch-All DID Match - Found 8885551212 - You probably want a DID for this.”) in new stack
[2014-03-04 05:28:31] DEBUG[1924] res_odbc.c: Disconnected 0 from asteriskcdrdb [MySQL-asteriskcdrdb]
[2014-03-04 05:28:31] DEBUG[1924] res_odbc.c: Database handle 0xb7402b28 deallocated
[2014-03-04 05:28:31] NOTICE[1924] res_odbc.c: Connecting asteriskcdrdb

I haven’t figured out yet what is even triggering this daily re-connection. Is this normal with FreePBX 2.11? Do I have a misconfiguration?

Yes it is new as of 2.11

Sounds like you are using a Distro that is not setting up ODBC and FreePBX is trying to use ODBC for the CEL logging portion of CDRs.

Tony,

Thanks very much for your reply.

Is ODBC a new requirement for 2.11? I didn’t have these errors on 2.10. I think the upgrade asked if it was okay to make a SQL DB change, but I don’t recall any issues reported.

Mark Berry

What should I do now? Is there a post-upgrade “to do” list somewhere?

What distro are you using?

PBX in a Flash 2.0.6.2. I posted in their forum first and was directed here.

http://pbxinaflash.com/community/index.php?threads/sql-errors-after-freepbx-2-11-upgrade.14568

I see. The problem is that you’d normally have to configure odbc.ini which is not something freepbx normally has control over. In the FreePBX distro we control that ini file through an rpm. The reason we have moved to odbc from mysql is because Digium has depreciated support of the mysql.so module, so eventually it just wont work at all

To fix the issue install odbc (and sorry I do not use PIAF so I do not know the processes they use)

Then you’ll want to add this to /etc/odbc.ini

[MySQL-asteriskcdrdb]
Description=MySQL connection to ‘asterisk’ database
driver=MySQL
server=localhost
database=asteriskcdrdb
Port=3306
Socket=/var/lib/mysql/mysql.sock
option=3

Making sure that the dsn in this file (below) /etc/asterisk/res_odbc_additional.conf matches the context (section) in the ini above

;--------------------------------------------------------------------------------;
; Do NOT edit this file as it is auto-generated by FreePBX. ;
;--------------------------------------------------------------------------------;
; For information on adding additional paramaters to this file, please visit the ;
; FreePBX.org wiki page, or ask on IRC. This file was created by the new FreePBX ;
; BMO - Big Module Object. Any similarity in naming with BMO from Adventure Time ;
; is totally deliberate. ;
;--------------------------------------------------------------------------------;
[asteriskcdrdb]
enabled=>yes
dsn=>MySQL-asteriskcdrdb
pooling=>no
limit=>1
pre-connect=>yes
username=>asteriskuser
password=>amp109

Thank you for those instructions.

Both files already exist on this machine.

/etc/asterisk/res_odbc_additional.conf is identical to your example.

/etc/odbc.ini is pretty close:

[MySQL-asteriskcdrdb]
Description = asteriskcdrdb MySQL ODBC
Driver = MySQL
Socket = /var/lib/mysql/mysql.sock
Server = localhost
User = root
Password = [removed here]
Database = asteriskcdrdb
Option = 3

The password in odbc.ini is not amp109 but I wouldn’t think that would matter if you supply the password at connection.

I also see the main “asterisk” database defined in odbci.ini. Is ODBC also used to access the “asterisk” database? Would the system even function if ODBC were not working?

Here are the results of a manual connection test to the CDR DB:

[email protected]:~ $ isql -v MySQL-asteriskcdrdb asteriskuser amp109
±--------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
±--------------------------------------+
SQL>

The password in odbc.ini IS the connection. The password HAS to be amp109 if thats what you use to connect to the database!

odbc is only used for AsteriskCDR it is not used for anything else. You must configure odbc correctly.

Andrew, since /etc/odbc.ini is beyond FreePBX control, can FreePBX not test for proper access and throw a notice/warning on the status page that odbc is not configured properly?

I can change the password in odbc.ini. Still trying to understand if these connection warnings are really an issue… My CDR reports contain current data. I can even see in my log that the ODBC connection to asteriskcdrdb succeeds after the initial failure. Is it falling back to try a default password? Would it be best to just omit the password from odbc.ini if the default amp109 is used?

Yesterday’s log:

[2014-03-10 09:04:21] VERBOSE[1955] netsock2.c: == Using SIP RTP TOS bits 184
[2014-03-10 09:04:21] VERBOSE[1955] netsock2.c: == Using SIP RTP CoS mark 5
[2014-03-10 09:04:21] WARNING[1924] res_odbc.c: SetConnectAttr (Txn isolation) returned an error: HY000: [MySQL][ODBC 5.1 Driver]MySQL server has gone away
[2014-03-10 09:04:21] WARNING[1924] res_odbc.c: SQL Execute returned an error -1: 08S01: [MySQL][ODBC 5.1 Driver][mysqld-5.1.61]MySQL server has gone away (65)
[2014-03-10 09:04:21] WARNING[1924] res_odbc.c: SQL Execute error -1! Verifying connection to asteriskcdrdb [MySQL-asteriskcdrdb]…
[2014-03-10 09:04:21] WARNING[1924] res_odbc.c: Connection is down attempting to reconnect…
[2014-03-10 09:04:21] DEBUG[1924] res_odbc.c: Disconnected 0 from asteriskcdrdb [MySQL-asteriskcdrdb]
[2014-03-10 09:04:21] DEBUG[1924] res_odbc.c: Database handle 0xb74147d8 deallocated
[2014-03-10 09:04:21] NOTICE[1924] res_odbc.c: Connecting asteriskcdrdb
[2014-03-10 09:04:21] VERBOSE[25682] pbx.c: – Executing [*[email protected]:1] Answer(“SIP/101-00000066”, “”) in new stack
[2014-03-10 09:04:21] NOTICE[1924] res_odbc.c: res_odbc: Connected to asteriskcdrdb [MySQL-asteriskcdrdb]

As a side question, am I correct in deducing that the ODBC connection happens the first time a user accesses the system each day (in this case, checking voicemail *97)?

Oops I just realized that my odbc.ini is configured to use user “root”, and that the password is valid for “root”.

So my odbc.ini actually has a working asteriskcdrdb section, which makes me wonder whether the “MySQL server has gone away” warnings have anything to do with the ODBC configuration. Maybe it’s normal for it to “lose” the connection after a period of inactivity, then have to re-connect?

Thats the plan. There is a ticket on it. However it doesn’t write to it. Yet.

It looks like it’s working fine “res_odbc.c: res_odbc: Connected to asteriskcdrdb [MySQL-asteriskcdrdb]”