How to enable milliseconds precision

From VoIPmonitor.org
Jump to navigation Jump to search

By default voipmonitor sniffer service stores CDR data with seconds precision. If you need to get calldate stored with ms precision:

Database is empty

  • If database is empty, add into sniffer's config (voipmonitor.conf) option time_precision_in_ms=yes and restart sniffer service (it will create or modify columns needed for storing CDRs in milliseconds pecission)

Database is not empty

  • If database contains data you need to additionally to above:

With the GUI

Login to GUI and in tools->check mysql schema, mark all tables with description 'missing support for time accuracy in milliseconds' and click on start upgrade/run sql. (beware that action can take hours/days and the GUI may be unusable until done.

Without the GUI

If you have no GUI you need to ALTER the tables manually:

ALTER TABLE cdr modify column calldate datetime(3) not null, modify column callend datetime(3) not null, modify column duration decimal(9,3) unsigned default null, modify column connect_duration decimal(9,3) unsigned default null, modify column progress_time decimal(9,3) unsigned default null, modify column first_rtp_time decimal(9,3) unsigned default null, modify column a_last_rtp_from_end decimal(9,3) signed default null, modify column b_last_rtp_from_end decimal(9,3) signed default null;
ALTER TABLE cdr_next modify column calldate datetime(3) not null;
ALTER TABLE cdr_proxy modify column calldate datetime(3) not null;
ALTER TABLE cdr_rtp modify column calldate datetime(3) not null;
ALTER TABLE cdr_dtmf modify column calldate datetime(3) not null;
ALTER TABLE cdr_sipresp modify column calldate datetime(3) not null;
ALTER TABLE cdr_siphistory modify column calldate datetime(3) not null;
ALTER TABLE cdr_tar_part modify column calldate datetime(3) not null;
ALTER TABLE cdr_country_code modify column calldate datetime(3) not null;
ALTER TABLE cdr_sdp modify column calldate datetime(3) not null;
ALTER TABLE cdr_txt modify column calldate datetime(3) not null;
ALTER TABLE cdr_flags modify column calldate datetime(3) not null;
ALTER TABLE message modify column calldate datetime(3) not null;
ALTER TABLE message_proxy modify column calldate datetime(3) not null;
ALTER TABLE message_country_code modify column calldate datetime(3) not null;
ALTER TABLE message_flags modify column calldate datetime(3) not null;
ALTER TABLE register modify column calldate datetime(3) not null;
ALTER TABLE register_state modify column created_at datetime(3) not null;
ALTER TABLE register_failed modify column created_at datetime(3) not null;
ALTER TABLE sip_msg modify column time datetime(3) not null, modify column request_time datetime(3) not null, modify column response_time datetime(3) not null;

Beware, that if you use cdr/messages/subs_notify custom_headers you need to alter also datetime column in tables

cdr_next_1, cdr_next_2 ...,cdr_next_N (column calldate)
messages_next_1,messages_next_2 ...,messages_next_N (column calldate)
sip_msg_next_1,sip_msg_next_2, ...,sip_msg_next_N, (column time)

For example:

ALTER TABLE cdr_next_1 modify column calldate datetime(3) not null;

After db tables altered

After above changes to tables done, you need to restart sniffer service for getting the call data stored with ms precission, and you need to logout/login into GUI to get CDR view in the GUI in ms precision.

Experienced issues during ALTERS

Warning code 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope.

We experienced on a version 5.5.60 of the mariadb this error when innodb_file_format not set to barracuda was Antelope. The solution was to disable strict_mode and enable the Barracuda as the file format for innodb as suggested in our scale section for [mysql]

sql_mode=NO_ENGINE_SUBSTITUTION
innodb_file_format = barracuda