SOLVED CDR Reporting - Not Reporting...

[dicko]

FreePBX relies on two databases, the 'asterisk' one is particular to the GUI, 'asteriskcdrdb' particular for CDR's and CEL. mysql might not start if either has crashed however, check cdr's with

mysql asteriskcdrdb (-u user -ppassword) -e 'check table cdr'
mysql asteriskcdrdb (-u user -ppassword) -e 'check table cel'

repairing a database is rarely a huge problem though.

 

[lcsneil]

Thanks all.

So the system is Debian running on a Raspberry Pi and originally installed via the Nerd Vittles link as above. Not sure where Ubuntu comes into it was I ant see it in any of the posts. Anyway, at the time I was replacing a 10 year old version and so did not transfer any settings or any data across from my own system. So have managed to hose this one from the GUI, That said Ward's suggestion has finally shown a fault.

root@PIAF2027:~ $ systemctl status mysql ? mariadb.service - MariaDB 10.5.15 database server Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled) Active: active (running) since Wed 2023-08-16 06:59:31 BST; 11s ago Docs: man:mariadbd(8) https://mariadb.com/kb/en/library/systemd/ Process: 4268 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS) Process: 4269 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS) Process: 4271 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCE> Process: 4328 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS) Process: 4337 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS) Main PID: 4318 (mariadbd) Status: "Taking your SQL requests now..." Tasks: 17 (limit: 1596) CPU: 998ms CGroup: /system.slice/mariadb.service ??4318 /usr/sbin/mariadbd Aug 16 06:59:31 PIAF2027 /etc/mysql/debian-start[4339]: Upgrading MySQL tables if necessary. Aug 16 06:59:31 PIAF2027 mariadbd[4318]: 2023-08-16 6:59:31 0 [Note] InnoDB: Buffer pool(s) load completed at 230816 6:59:31 Aug 16 06:59:31 PIAF2027 /etc/mysql/debian-start[4342]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored Aug 16 06:59:31 PIAF2027 /etc/mysql/debian-start[4342]: Looking for 'mysql' as: /usr/bin/mysql Aug 16 06:59:31 PIAF2027 /etc/mysql/debian-start[4342]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck Aug 16 06:59:31 PIAF2027 /etc/mysql/debian-start[4342]: This installation of MariaDB is already upgraded to 10.5.15-MariaDB. Aug 16 06:59:31 PIAF2027 /etc/mysql/debian-start[4342]: There is no need to run mysql_upgrade again for 10.5.15-MariaDB. Aug 16 06:59:31 PIAF2027 /etc/mysql/debian-start[4342]: You can use --force if you still want to run mysql_upgrade Aug 16 06:59:31 PIAF2027 /etc/mysql/debian-start[4350]: Checking for insecure root accounts. Aug 16 06:59:31 PIAF2027 debian-start[4353]: ERROR 1356 (HY000) at line 1: View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

So looks like there is an issued with access rights to the dB.

@dicko
I checked your suggestion and both have come back ok
sql asteriskcdrdb -e 'check table cdr' +-------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------+-------+----------+----------+ | asteriskcdrdb.cdr | check | status | OK | +-------------------+-------+----------+----------+ root@PIAF2027:~ $ mysql asteriskcdrdb -e 'check table cel' +-------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------+-------+----------+----------+ | asteriskcdrdb.cel | check | status | OK | +-------------------+-------+----------+----------+


I agree that blowing this away and starting again is most probably most expedient route.

That said I would like to back up a number of things if at all possible namely:_

• extensions
• inbound routes
• outbound routes
• time conditions

Is that a realistic expectation or is there a high danger of that corrupting things and best left alone.

If so then is it a case of backing up the "modules" in the admin panel Backup/Restore and if so which specific modules as I was struggling to see which modules would be needed for those 4 items.

TIA

lcsneil

 

[kenn10]

I have another issue with my IncrediblePBX 2027-U install.

The first sentence of your first post refers to the Ubuntu version of IncrediblePBX so that led me to believe you had mixed versions.

If you go to the Bulk Handler module under Admin > Bulk Handler, you will see what you can manually export to a spreadsheet for later re-import. Time conditions and outbound routes are not included. If you do not see the Bulk Handler on the menu, you can install it from the Admin > Module Admin.

Anything else, you will need to reconfigure on a new system.

I do not recommend the FreePBX backup from Admin > Backup/Restore or the Incrediblebackup as it will copy any database corruption or other problems that exist.

 

[lcsneil]

Ah thanks Andrew - hadn't spotted the U subtlety. My error.

OK thanks for the pointers on csv export. I think that will be the quickest option in the long run.

Ta
lcsneil

 

[lcsneil]

OK - finally got around to rebuilding this. How do you export the SIP trunks out of the old system? I have tried bulk handler trunks but the file is empty (they are SIP trunks rather than PJSIP trunks).

lcsneil

 

[kenn10]

You need to convert your trunks to PJSIP since SIP is deprecated and will not be supported in the next release of Asterisk. You'll need to study up on PJSIP since the setup is different from chan_sip. There are many PJSIP setup discussions in this forum.

Otherwise, you need to cut and paste from old to new and then concentrate on conversion to PJSIP.

Typically, a FreePBX backup on the old system and a restore to the new system will bring trunks across but you have had so much trouble with you new setup that I don't recommend it.