Strange error on restore on mysql asteriskcdr not writing giving error acdrunichan

[faisalkhan]

Hi all,

I am having strange issue while my cdrs were working fine but now suddenly I receive this error and cdrs are not writing in to the pbx. I am using FreePBX 16.

[2024-11-19 12:07:35] WARNING[17364]: res_odbc.c:529 ast_odbc_print_errors: SQL Execute returned an error: 42S02: [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.65-MariaDB]Table 'asteriskcdrdb.acdrunichan' doesn't exist
[2024-11-19 12:07:35] WARNING[17364]: res_odbc.c:424 ast_odbc_prepare_and_execute: SQL Execute error -1!
[2024-11-19 12:07:35] WARNING[17364]: cdr_adaptive_odbc.c:765 odbc_log: 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, accountcode, uniqueid, recordingfile, cnum, cnam, outbound_cnum, linkedid, peeraccount, sequence) VALUES ({ ts '2024-11-19 12:06:28' }, '"" <123456789>', '123456789', '123456789', 'from-internal', 'PJSIP/501-0000000a', 'SIP/VoipInnovations-00000008', 'Dial', 'SIP/VoipInnovations/123456789,300,Ttb(func-apply-sipheaders^s^1,(2))', 32, 0, 'FAILED', 3, 'Outbound', '1732046788.240', 'out-123456789-501-20241119-120628-1732046788.240.wav', '501', 'Mark Ladd', '123456789', '1732046788.240', 'Outbound', 230)

[dicko]

lgaetzLorne Gaetz

Sep 2018

I’ve seen these errors before, and they’ve always been caused by some oddball Asternic Reports MySQL TRIGGER.

[faisalkhan]

So what do you suggest.

[faisalkhan]

Due to this cdrs are not writing in the database and can’t see them on the CDRs page.

[dicko]

I suggest you contact asternic.

[faisalkhan]

What about installing the asternic cdr stats pro?

[dicko]

I don’t have any comment on anything Asternic.

[faisalkhan]

What about if we can restore the system without these tables.

This was installed on our old server Asternic Stats pro.

And I am pretty sure that when we restored the backup from the old server, on new server there is no such table and I don’t know what change Asternic implemented in the code to write the CDRs which is affecting the CDRs.

There must be a way to at least backup the server without this or on the new server we can ignore in the cdr values and make Freepbx work with it’s default cdrs behavior.

[dicko]

You chose to add Asternic so you broke the “default” behavior , use mysql’s SHOW TRIGGERS and thus DROP TRIGGER . . . to delete any unneeded triggers, use ALTER TABLE DROP . . . command to drop those ‘acdr*’ columns from asteriskcdrdb if you don’t use Asternic anymore

[faisalkhan]

I have found what was the issue and where it’s coming from

https://www.asternic.net/cdrreports/docs/

I have used this approach to drop every single one of them but still I am getting the cdrs failed.

It’s still trying to for that table.

WARNING[8751]: res_odbc.c:529 ast_odbc_print_errors: SQL Execute returned an error: 42S02: [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.65-MariaDB]Table 'asteriskcdrdb.acdrunichan' doesn't exist
[2024-11-19 19:04:38] WARNING[8751]: res_odbc.c:424 ast_odbc_prepare_and_execute: SQL Execute error -1!

I did fwconsole restart and fwconsole chown but nothing worked.

[dicko]

You still have the acdrscedule table which probably still has a trigger

[faisalkhan]

ok removed.

What to do next?

MariaDB [asteriskcdrdb]> DROP TABLE acdrschedule;
Query OK, 0 rows affected (0.01 sec)

MariaDB [asteriskcdrdb]> show tables;
±------------------------+
| Tables_in_asteriskcdrdb |
±------------------------+
| cdr |
| cel |
| queuelog |
| transient_cdr |
±------------------------+
4 rows in set (0.00 sec)

MariaDB [asteriskcdrdb]>

[faisalkhan]

I fixed with the help of @dicko . thanks Dicko!.

Following has been done to fix this issue.

rm -rf /var/www/html/cdrstats

USE asteriskcdrdb;

DROP TABLE acdrschedule;
DROP TABLE acdrchannels;
DROP TABLE acdrcost;
DROP TABLE acdrnormalisationrules;
DROP TABLE acdrrates;
DROP TABLE acdrsetup;
DROP TABLE acdruserchannel;
DROP TABLE acdruserfilter;
DROP TABLE acdrusers;

SHOW TRIGGERS FROM asteriskcdrdb;


MariaDB [asteriskcdrdb]> SHOW TRIGGERS FROM asteriskcdrdb;
+--------------+--------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| Trigger      | Event  | Table | Statement                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | Timing | Created | sql_mode | Definer        | character_set_client | collation_connection | Database Collation |
+--------------+--------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| simp_channel | INSERT | cdr   | BEGIN

SET @orichn   = ''; SET @dstchn = '';

SELECT SUBSTRING(NEW.channel,1,LENGTH(NEW.channel)-LOCATE("-",REVERSE(NEW.channel))) INTO @orichn;
SELECT SUBSTRING(NEW.dstchannel,1,LENGTH(NEW.dstchannel)-LOCATE("-",REVERSE(NEW.dstchannel))) INTO @dstchn;

IF @dstchan LIKE 'Local/FM%' THEN

SELECT CONCAT('SIP/',SPLIT_STR(REPLACE(REPLACE(dstchannel,'#','-'),'@','-'),'-',2)) INTO @dstchn;
REPLACE INTO acdrunichan VALUES (NEW.uniqueid,@orichn,@dstchn);

ELSE

REPLACE INTO acdrunichan VALUES (NEW.uniqueid,@orichn,@dstchn);

END IF;
END | AFTER  | NULL    |          | root@localhost | utf8                 | utf8_general_ci      | utf8mb4_unicode_ci |
+--------------+--------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

DROP TRIGGER simp_channel;

SHOW TRIGGERS FROM asteriskcdrdb;

EXIT;

fwconsole restart

This is the whole activity to fix this issue.