CDR reports not working FREEPBX 14

Romo

So the CDR reports stopped working after Feb 4, those are the last entries that are properly shown. That same day a client asked us to install Asternic CDR Reports to give the trial install a test. The install went without any apparent errors and it was left for the client to register for the trial.

Something must have gone wrong do to the asteriskcdrdb:cdr, since now that I am checking the logs do to the client reporting no entries show up either on asternic or the regular cdr reports I get this:

[2019-03-05 10:23:38] WARNING[5079]: res_odbc.c:459 ast_odbc_print_errors: SQL Execute returned an error: 42S02: [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.56-MariaDB]Table 'asteriskcdrdb.acdrunichan' doesn't exist
[2019-03-05 10:23:38] WARNING[5079]: res_odbc.c:403 ast_odbc_prepare_and_execute: SQL Execute error -1!
[2019-03-05 10:23:38] WARNING[5079]: cdr_adaptive_odbc.c:749 odbc_log: cdr_adaptive_odbc: Insert failed on 'asteriskcdrdb:cdr'.

Client reports Asternic Reports never worked for them, so I have gone a head and removed it, but still get the same errors.

Any ideas how to the the cdr logging to the db working again?

Romo

Apparently during the install of Asternic CDR reports a trigger was set that was pointing to a table that never got properly created. After I dropped the triggered from the asteriskcdrdb logging could start again.

Here is the trigger:

MariaDB [asteriskcdrdb]> show triggers;
+--------------+--------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| 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;
INSERT INTO acdrunichan VALUES (NEW.uniqueid,@orichn,@dstchn);

ELSE

INSERT 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.01 sec)

Deleted it with:

DROP Trigger if exists asteriskcdrdb.simp_channel;

Solved CDR reports not working FREEPBX 14