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 [8885551212@from-trunk: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?
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.
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
/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:
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 [*97@from-internal: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?