CDR reports not working FREEPBX 14



  • 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?



  • 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    |          | [email protected] | utf8                 | utf8_general_ci      | utf8mb4_unicode_ci |
    +--------------+--------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
    1 row in set (0.01 sec)
    
    

    Deleted it with:

    DROP Trigger if exists asteriskcdrdb.simp_channel;
    


  • 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    |          | [email protected] | utf8                 | utf8_general_ci      | utf8mb4_unicode_ci |
    +--------------+--------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
    1 row in set (0.01 sec)
    
    

    Deleted it with:

    DROP Trigger if exists asteriskcdrdb.simp_channel;
    

Log in to reply