MySQL Database Woes - CDR and CEL

mbielech5 · April 4, 2022, 9:42pm

This all started when I was trying to export the Call Data Records (CDR) and Call Event Log (CEL) databases from the currently running FreePBX v14 system in the basement, to a new FreePBX v16 virtual machine I’ve been using for testing. I followed the instructions here:

Moving CDR and CEL from old to new

After a couple of MySQL permission issues (could not log without a password) I was able to export the two databases as root on the old system.

I may have run the older commands below instead of the newer ones from the post initially:

mysqldump --skip-add-drop-table --no-create-db --no-create-info --complete-insert asteriskcdrdb cdr > cdr.sql 
mysqldump --skip-add-drop-table --no-create-db --no-create-info --complete-insert asteriskcdrdb cel > cel.sql

I should have run this on the currently running/production system (and eventually did):

mysqldump  asteriskcdrdb cel > cel.sql
mysqldump  asteriskcdrdb cdr > cdr.sql

And then I ran this in the test VM:

mysql asteriskcdrdb < cel.sql
fwconsole ma install cel
mysql asteriskcdrdb < cdr.sql
fwconsole ma install cdr

Afterwards, I cannot see any call data records on either system, but yet I can see call event logs on both systems. Unfortunately this did erase the existing entries I already had in the test VM. I believe the export was successful though, I opened up the cel.sql in Notepad++ and can see entries dating back to 2018 (when I first built the system), but when I open the cdr.sql file, I do not see any “INSERT INTO `cdr` VALUES” entries.

I have a million questions:

Aside from trying to do this manually, where did I go wrong?
Where did the existing entries go in the CDR database on the new system? Overwritten to null?
During this export/import process, is it possible to retain/keep the existing entries on the destination system somehow?
Why would the CEL database populate but not the CDR?
Is there any way to generate the missing CDR data from the CEL database? Is there any way I can fix both systems?

Lastly, I did notice in my /var/log/asterisk/full with a grep -i CDR full I noticed these errors:

[2022-04-04 12:34:01] ERROR[14870] loader.c: cdr_csv declined to load.
[2022-04-04 12:34:01] ERROR[14870] loader.c: cdr_manager declined to load.
[2022-04-04 12:34:01] ERROR[14870] loader.c: cdr_odbc declined to load.
[2022-04-04 12:48:07] ERROR[16983] config_options.c: Unable to load config file 'cdr.conf'

Sure enough, the cdr.conf file does not exist in /etc/asterisk in a fresh install of FreePBX v16, but one of my other test v16 VMs seems to work fine without this file. Below is the full output of the grep command:

root@freepbx asterisk]# grep -i CDR full
[2022-04-04 12:32:52] NOTICE[13372] res_odbc.c: Registered ODBC class 'asteriskcdrdb' dsn->[MySQL-asteriskcdrdb]
[2022-04-04 12:32:52] VERBOSE[13372] loader.c: Reloading module 'cdr' (CDR Engine)
[2022-04-04 12:32:52] ERROR[13372] config_options.c: Unable to load config file 'cdr.conf'
[2022-04-04 12:32:52] VERBOSE[13372] loader.c: Reloading module 'cdr_adaptive_odbc.so' (Adaptive ODBC CDR backend)
[2022-04-04 12:32:52] VERBOSE[13372] cdr_adaptive_odbc.c: Found adaptive CDR table cdr@asteriskcdrdb.
[2022-04-04 12:32:52] VERBOSE[13372] cdr_adaptive_odbc.c: Found alias start for column calldate in cdr@asteriskcdrdb
[2022-04-04 12:32:52] VERBOSE[13372] cel_odbc.c: Found CEL table cel@asteriskcdrdb.
[2022-04-04 12:33:42] VERBOSE[13996] loader.c: Unloading app_forkcdr.so
[2022-04-04 12:33:42] VERBOSE[13996] pbx_app.c: Unregistered application 'ForkCDR'
[2022-04-04 12:33:42] VERBOSE[13996] loader.c: Unloading func_cdr.so
[2022-04-04 12:33:42] VERBOSE[13996] pbx_functions.c: Unregistered custom function CDR
[2022-04-04 12:33:42] VERBOSE[13996] pbx_functions.c: Unregistered custom function CDR_PROP
[2022-04-04 12:33:42] VERBOSE[13996] loader.c: Unloading app_cdr.so
[2022-04-04 12:33:42] VERBOSE[13996] pbx_app.c: Unregistered application 'NoCDR'
[2022-04-04 12:33:42] VERBOSE[13996] pbx_app.c: Unregistered application 'ResetCDR'
[2022-04-04 12:33:42] VERBOSE[13996] pbx.c: Remove macro-pinsets/cdr/12, registrar=pbx_config; con=<nil>((nil)); con->root=(nil)
[2022-04-04 12:33:42] VERBOSE[13996] pbx.c: Remove macro-pinsets/cdr/11, registrar=pbx_config; con=<nil>((nil)); con->root=(nil)
[2022-04-04 12:33:42] VERBOSE[13996] pbx.c: Remove macro-pinsets/cdr/10, registrar=pbx_config; con=<nil>((nil)); con->root=(nil)
[2022-04-04 12:33:42] VERBOSE[13996] pbx.c: Remove macro-pinsets/cdr/9, registrar=pbx_config; con=<nil>((nil)); con->root=(nil)
[2022-04-04 12:33:42] VERBOSE[13996] pbx.c: Remove macro-pinsets/cdr/8, registrar=pbx_config; con=<nil>((nil)); con->root=(nil)
[2022-04-04 12:33:42] VERBOSE[13996] pbx.c: Remove macro-pinsets/cdr/7, registrar=pbx_config; con=<nil>((nil)); con->root=(nil)
[2022-04-04 12:33:42] VERBOSE[13996] pbx.c: Remove macro-pinsets/cdr/6, registrar=pbx_config; con=<nil>((nil)); con->root=(nil)
[2022-04-04 12:33:42] VERBOSE[13996] pbx.c: Remove macro-pinsets/cdr/5, registrar=pbx_config; con=<nil>((nil)); con->root=(nil)
[2022-04-04 12:33:42] VERBOSE[13996] pbx.c: Remove macro-pinsets/cdr/4, registrar=pbx_config; con=<nil>((nil)); con->root=(nil)
[2022-04-04 12:33:42] VERBOSE[13996] pbx.c: Remove macro-pinsets/cdr/3, registrar=pbx_config; con=<nil>((nil)); con->root=(nil)
[2022-04-04 12:33:42] VERBOSE[13996] pbx.c: Remove macro-pinsets/cdr/2, registrar=pbx_config; con=<nil>((nil)); con->root=(nil)
[2022-04-04 12:33:42] VERBOSE[13996] pbx.c: Remove macro-pinsets/cdr/1, registrar=pbx_config; con=<nil>((nil)); con->root=(nil)
[2022-04-04 12:33:59] NOTICE[14870] res_odbc.c: Registered ODBC class 'asteriskcdrdb' dsn->[MySQL-asteriskcdrdb]
[2022-04-04 12:33:59] VERBOSE[14870] loader.c: Loading cdr.
[2022-04-04 12:33:59] ERROR[14870] config_options.c: Unable to load config file 'cdr.conf'
[2022-04-04 12:33:59] NOTICE[14870] cdr.c: Failed to process CDR configuration; using defaults
[2022-04-04 12:33:59] NOTICE[14870] cdr.c: CDR simple logging enabled.
[2022-04-04 12:33:59] VERBOSE[14870] loader.c: cdr => (CDR Engine)
[2022-04-04 12:33:59] VERBOSE[14870] loader.c: Loading cdr_adaptive_odbc.so.
[2022-04-04 12:33:59] VERBOSE[14870] cdr_adaptive_odbc.c: Found adaptive CDR table cdr@asteriskcdrdb.
[2022-04-04 12:33:59] VERBOSE[14870] cdr_adaptive_odbc.c: Found alias start for column calldate in cdr@asteriskcdrdb
[2022-04-04 12:33:59] VERBOSE[14870] loader.c: cdr_adaptive_odbc.so => (Adaptive ODBC CDR backend)
[2022-04-04 12:34:00] VERBOSE[14870] loader.c: Loading cdr_csv.so.
[2022-04-04 12:34:00] WARNING[14870] cdr_csv.c: unable to load config: cdr.conf
[2022-04-04 12:34:00] VERBOSE[14870] loader.c: Loading cdr_manager.so.
[2022-04-04 12:34:00] WARNING[14870] cdr_manager.c: Failed to load configuration file. Module not activated.
[2022-04-04 12:34:00] VERBOSE[14870] cdr.c: Unregistered 'cdr_manager' CDR backend
[2022-04-04 12:34:00] VERBOSE[14870] loader.c: Loading cdr_odbc.so.
[2022-04-04 12:34:00] WARNING[14870] cdr_odbc.c: cdr_odbc: Unable to load config for ODBC CDR's: cdr_odbc.conf
[2022-04-04 12:34:00] VERBOSE[14870] cel_odbc.c: Found CEL table cel@asteriskcdrdb.
[2022-04-04 12:34:00] VERBOSE[14870] loader.c: Loading app_cdr.so.
[2022-04-04 12:34:00] VERBOSE[14870] pbx_app.c: Registered application 'NoCDR'
[2022-04-04 12:34:00] VERBOSE[14870] pbx_app.c: Registered application 'ResetCDR'
[2022-04-04 12:34:00] VERBOSE[14870] loader.c: app_cdr.so => (Tell Asterisk to not maintain a CDR for the current call)
[2022-04-04 12:34:00] VERBOSE[14870] loader.c: Loading func_cdr.so.
[2022-04-04 12:34:00] VERBOSE[14870] pbx_functions.c: Registered custom function 'CDR'
[2022-04-04 12:34:00] VERBOSE[14870] pbx_functions.c: Registered custom function 'CDR_PROP'
[2022-04-04 12:34:00] VERBOSE[14870] loader.c: func_cdr.so => (Call Detail Record (CDR) dialplan functions)
[2022-04-04 12:34:00] VERBOSE[14870] loader.c: Loading app_forkcdr.so.
[2022-04-04 12:34:00] VERBOSE[14870] pbx_app.c: Registered application 'ForkCDR'
[2022-04-04 12:34:00] VERBOSE[14870] loader.c: app_forkcdr.so => (Fork The CDR into 2 separate entities)
[2022-04-04 12:34:01] ERROR[14870] loader.c: cdr_csv declined to load.
[2022-04-04 12:34:01] ERROR[14870] loader.c: cdr_manager declined to load.
[2022-04-04 12:34:01] ERROR[14870] loader.c: cdr_odbc declined to load.
[2022-04-04 12:48:07] NOTICE[16983] res_odbc.c: Registered ODBC class 'asteriskcdrdb' dsn->[MySQL-asteriskcdrdb]
[2022-04-04 12:48:07] VERBOSE[16983] loader.c: Reloading module 'cdr' (CDR Engine)
[2022-04-04 12:48:07] ERROR[16983] config_options.c: Unable to load config file 'cdr.conf'
[2022-04-04 12:48:07] VERBOSE[16983] loader.c: Reloading module 'cdr_adaptive_odbc.so' (Adaptive ODBC CDR backend)
[2022-04-04 12:48:07] VERBOSE[16983] cdr_adaptive_odbc.c: Found adaptive CDR table cdr@asteriskcdrdb.
[2022-04-04 12:48:07] VERBOSE[16983] cdr_adaptive_odbc.c: Found alias start for column calldate in cdr@asteriskcdrdb
[2022-04-04 12:48:07] VERBOSE[16983] cel_odbc.c: Found CEL table cel@asteriskcdrdb.
[2022-04-04 13:02:13] NOTICE[18173] res_odbc.c: Registered ODBC class 'asteriskcdrdb' dsn->[MySQL-asteriskcdrdb]
[2022-04-04 13:02:13] VERBOSE[18173] loader.c: Reloading module 'cdr' (CDR Engine)
[2022-04-04 13:02:13] NOTICE[18173] cdr.c: CDR simple logging enabled.
[2022-04-04 13:02:13] VERBOSE[18173] loader.c: Reloading module 'cdr_adaptive_odbc.so' (Adaptive ODBC CDR backend)
[2022-04-04 13:02:13] VERBOSE[18173] cdr_adaptive_odbc.c: Found adaptive CDR table cdr@asteriskcdrdb.
[2022-04-04 13:02:13] VERBOSE[18173] cdr_adaptive_odbc.c: Found alias start for column calldate in cdr@asteriskcdrdb
[2022-04-04 13:02:14] VERBOSE[18173] cel_odbc.c: Found CEL table cel@asteriskcdrdb.

Any help would greatly be appreciated, I’ve been pulling my hair out for two months now testing and trying to migrate from the older FreePBX to a newer FreePBX version running inside a virtual machine.

mbielech5 · April 6, 2022, 6:30pm

UPDATE:

I was able to fix CDR on the production system, but all of the existing entries are gone (and that’s IF they were there to begin with). I was also able to fix the cdr database on the test VM by creating/importing the table from a fresh install of FreePBX from another test VM I had. It seems that during the export, the cdr database was not getting populated (and still isn’t) from the get-go in FreePBX v14. I checked the odbc.ini file and the DSN is correct, so I don’t know what is going on there:

nano /etc/odbc.ini
-----------------------
[MySQL-asteriskcdrdb]
Description=MySQL connection to 'asteriskcdrdb' database
driver=MySQL
server=localhost
database=asteriskcdrdb
Port=3306
Socket=/var/lib/mysql/mysql.sock
option=3
Charset=utf8

I also ran a odbc show all and the this database hasn’t been connected to since 1969:

freepbx*CLI> odbc show all

ODBC DSN Settings
-----------------

  Name:   asteriskcdrdb
  DSN:    MySQL-asteriskcdrdb
    Last connection attempt: 1969-12-31 19:00:00
    Number of active connections: 1 (out of 5)

I tried running a mysqlcheck --repair --all-databases but I get a:

asteriskcdrdb.cdr
note     : The storage engine for the table doesn't support repair
asteriskcdrdb.cel
note     : The storage engine for the table doesn't support repair

Anyways, when I originally went to export the database from the production system, the mysqldump asteriskcdrdb cdr > cdr.sql creates a DROP statement that deletes the existing table and REPLACES any existing data in the table. And since there was no data in the cdr table to begin with, Garbage In = Garbage Out. So, to retain just the data and not the whole database and table structure, I can run this:

mysqldump --user root --host localhost --skip-add-drop-table --no-create-info asteriskcdrdb cel  > cel.sql
mysqldump --user root --host localhost --skip-add-drop-table --no-create-info asteriskcdrdb cdr  > cdr.sql

OR, for password-based login/authentication:

mysqldump --user root --password --host localhost --skip-add-drop-table --no-create-info asteriskcdrdb cel > cel.sql
mysqldump --user root --password --host localhost --skip-add-drop-table --no-create-info asteriskcdrdb cdr > cdr.sql

If you’re only looking to transfer the database from one system to another, and overwrite any existing entries in the table, you can still run this:

mysqldump --user root --host localhost asteriskcdrdb cel  > cel.sql
mysqldump --user root --host localhost asteriskcdrdb cdr  > cdr.sql

OR, to transfer the database from one system to another, and overwrite any existing entries in the table with password-based login/authentication, you can run this:

mysqldump --user root --password --host localhost asteriskcdrdb cel > cel.sql
mysqldump --user root --password --host localhost asteriskcdrdb cdr > cdr.sql

I would still like to populate the cdr table using the cel table as an input on the production system if it is possible, but that is beyond my technical abilities for the moment. I would also like to fix the production system to write to the cdr table and troubleshoot if I can, but I am chalking that one up to a loss as well. This system will soon be replaced with a fresh install of v16 in a virtual machine once I get all the kinks ironed out and migrate to the new system. And the new system has no issues writing to either the cdr or cel tables.

Is there anyone able to help with this?

christrati · April 7, 2022, 9:25pm

I recently went through a PBXact 14 to a PBXact 16 migration. And dealing with the CDR was one of those questions. Just to aggregate it for everyone else, I’m assuming you referenced the following posts when trying to move the CDR around:
Remote CDR Database Installation Guide - PBX GUI - Documentation (freepbx.org)

Periodically Delete CDRs Older than N Months - General Help - FreePBX Community Forums

How to remove old CDRs - General Help - FreePBX Community Forums

Based on my testing, I will say if you run the command to drop the table, everything will be lost. And with my testing, what I found worked the best was to run a backup with just the CDR and then the built in restore (in this case, PBXAct16 using the fwconsole backup --restore --restorelegacycdr

It took quite a while (about 1.5 hours, since I have data back to 2018), but it did complete.

So, hopefully that helps you with migrating.

And hopefully I can get a response back on a way to archive out the CDR records. Essentially, it would be good to run an export of those old records, but have a way to restore or use a 3rd party program to search them, in case of audits, etc.

Chris

christrati · April 8, 2022, 7:14pm

Just to follow up, since you referenced @lgaetz post in the Moving CDR and CEL from old to new.

From what I got back from Sangoma support, they were recommending the following:

#!/bin/bash
mysql -D asteriskcdrdb -e "DELETE FROM cdr WHERE calldate < DATE_SUB(NOW(), INTERVAL 90 DAY);"
mysql -D asteriskcdrdb -e "DELETE FROM cel WHERE eventtime < DATE_SUB(NOW(), INTERVAL 90 DAY);"
mysqlcheck --auto-repair --check --optimize --all-databases'

Now, I’m going to modify this and pick a specific date.

For restoring, it was the same as what you had above:

mysql asteriskcdrdb < cdr.sql
mysql asteriskcdrdb < cel.sql

In regards to what @lgaetz stating about the fwconsole ma install, I’m wondering if it is similar to what I ran into with queue log reporting. I had to essentially had to uninstall the module, run the restore and then install the module again. So makes me wonder if you would do the following if this would work:

mysql asteriskcdrdb < cel.sql
fwconsole ma install cel
fwconsole ma uninstall cdr
mysql asteriskcdrdb < cdr.sql
fwconsole ma install cdr

See if that helps.

Chris

mbielech5 · April 8, 2022, 8:17pm

I know in the business world it is good to not have a large database full of call records, and also be able to backup old records and purge from the running database, but I was actually looking to migrate all the CDR and CEL data we’ve collected on several instances of FreePBX that I have set up over the years on several pbx servers here at our home. The DELETE FROM statements I was not trying to use.

But now looking at the Asterisk logs on the currently running production system, I do see a few SQL/CDR errors in the log:

[2022-04-08 13:39:53] NOTICE[24420]: cdr.c:4445 cdr_toggle_runtime_options: CDR simple logging enabled.
[2022-04-08 13:39:53] WARNING[24420]: res_odbc.c:958 odbc_obj_connect: res_odbc: Error SQLConnect=-1 errno=0 [unixODBC][Driver Manager]Can't open lib '/usr/lib64/libmyodbc5.so' : file not found
[2022-04-08 13:39:53] NOTICE[24420]: res_odbc.c:617 load_odbc_config: Registered ODBC class 'asteriskcdrdb' dsn->[MySQL-asteriskcdrdb]
[2022-04-08 13:39:53] WARNING[24420]: res_odbc.c:958 odbc_obj_connect: res_odbc: Error SQLConnect=-1 errno=0 [unixODBC][Driver Manager]Can't open lib '/usr/lib64/libmyodbc5.so' : file not found
[2022-04-08 13:39:53] WARNING[24420]: cdr_adaptive_odbc.c:135 load_config: No such connection 'asteriskcdrdb' in the 'asteriskcdrdb' section of cdr_adaptive_odbc.conf.  Check res_odbc.conf.

and

[2022-04-08 14:10:58] WARNING[2900] res_odbc.c: SQL Execute returned an error: 42S02: [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.60-MariaDB]Table 'asteriskcdrdb.replicate_cdr' doesn't exist
[2022-04-08 14:10:58] WARNING[2900] res_odbc.c: SQL Execute error -1!
[2022-04-08 14:10:58] WARNING[2900] cdr_adaptive_odbc.c: cdr_adaptive_odbc: Insert failed on 'asteriskcdrdb:cdr'.  CDR failed: INSERT INTO cdr (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,uniqueid,recordingfile,cnum,cnam,linkedid,sequence) VALUES ({ ts '2022-04-08 14:10:23' },'"Mark Bielech" <711>','711','808','followme-check','SIP/708-000000fe','SIP/808-000000ff','Dial','SIP/808,27,HhTtrIM(auto-blkvm)b(func-apply-sipheaders^s^1),',34,33,'ANSWERED',3,'1649441423.4653','internal-808-708-20220408-141023-1649441423.4653.wav','711','Mark Bielech','1649441423.4653',1079)

I tried reinstalling the mariadb odbc connector by running yum install mariadb-connector-odbc, but all it did was freeze-up the console. Should I try a yum remove first? Will reinstalling blow any FreePBX custom configurations away?

Later I did copy the libmyodbc5.so that was missing on the production system from a v16 virtual machine I’ve been using (I don’t know if there is any incompatibilities with this file between v14 in v16), but it still does not want to write to the cdr table.

Any suggestions to look at to see why FreePBX isn’t writing to the database?

christrati · April 12, 2022, 9:15pm

This is still moving from v14 to v16 correct?

And the v16 is the one that isn’t working?

Did you try making a backup of just the CDR using the builtin backup and then use the restorelegacycdr switch on the v16?

When I tested, it would essentially drop the CDR and CEL tables and restore the new ones to that table. During my test, I had a similar issue, because I dropped the CDR and CEL tables out, hence it couldn’t write. When I ran the legacycdrrestore switch, it then recreated them.

Chris

mbielech5 · April 14, 2022, 3:25pm

Yes, the currently running system is v14 and the new one I am setting up is v16. The only differences I’ve seen between the old a new database is that the new database has three new columns added to the very end:

linkedid
peeraccount
sequence

I just added these missing fields to the old database and I was able to import the old data.

Also I did not use the built-in backup module to restore the database for fear that I did not want to overwrite any of the existing data in the new v16 database already populated; but that is good to know that it is available and that I can use the restorelegacycdr option if needed.

When you restored your CDR and CEL tables from the previous FreePBX version with the built-in module, did it overwrite any data on the new system? Or, did you only want the data from the old system and nothing from the new system?

christrati · April 18, 2022, 2:49pm

My goal was to essentially sync the servers together and then swap them around. I had a v14 running as production, and then had v16 as a spare. I then scheduled a downtime and backed up the CDR and restored it over to v16. I used the restorelegacycdr option and it would only wipe out any calls on the v16 only that happened prior to the restore. Ie, for me, I didn’t have much of any history on v16, but when I swapped over we had a few calls. Those were then lost, due to the table being dropped and then restored.

My database is pretty large, since I have data all the way back to 2018. That is something I’m working on shrinking, and trying to find a way to archive the data. I reached out to Sangoma, as I figured this would be a feature that would be built into the system. It is not, so you have to essentially run Mysql commands to trim the tables.

Sometime, I’m going to see about posting my notes on the migration. I would say overall, it was successful; however, there were some gotchas. For me the big issues after the migration were:

Misc Destinations no longer passed on CallerID - it would only use the trunk’s caller ID. v14, it would pass along caller ID. Workaround was to create Virtual Extensions with FollowMe to pass the callerID
Multicast paging was missing. I had to manually restore those multicast IPs in order for that to work
Momentum SIP trunk was showing everything coming in on the same trunk, rather than the multiple trunks we have setup. Fix was to enable (Set to Use) User = Phone in PJSIP Advanced settings.

After that, the rest were minor, but I do have the following outstanding issues:

TLS registered phones will not register when Verify Client is enabled.
Sangoma and Yealink phones have to change their Certificate settings to allow all certificates (ie, disable the Only allow trusted certificates). v14 this was not an issue, but v16 it is required. @sorvani mentioned this in some other posts as well with Yealink phones. It also happens with my Sangoma phones as well.
Notification on the Dashboard stating it "Failed to send security notification email. Unable to clear the message. And do get email alerts and update notifications
Notification on Dashboard that “Calendar using COUNT”, but haven’t found any of the calendars showing a reocurring appointment with them ending after so many occurrences.

Overall, for anyone wanting to migrate using the Backup Restore method from v14 to v16 would want to use another system, as I had to tweak the backup script to gather more info, to make sure everything would restore correctly.

Anyways, I’ll likely have to start a new post on my v14 → v16 migration experience. Just so anyone else moving that way would have some troubleshooting steps.

Chris

system · April 25, 2022, 2:49pm

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.