How to enable milliseconds precision: Difference between revisions

From VoIPmonitor.org
(Review: improved formatting (Warning/Note templates, <code> tags), added distributed architecture diagram, streamlined AI Summary)
 
(5 intermediate revisions by 2 users not shown)
Line 1: Line 1:
By default voipmonitor sniffer service stores CDR data with seconds precision.
{{DISPLAYTITLE:How to Enable Millisecond Precision for Timestamps}}
If you need to get calldate stored with ms precision:
[[Category:GUI manual]]


==Database is empty==
'''This guide explains how to manage millisecond precision for timestamps in VoIPmonitor. While all new installations since 2025 have this feature enabled by default, this guide details how to enable it on older systems.'''
* 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==
== Overview ==
* If database contains data you need to additionally to above:
VoIPmonitor can store and display all call-related timestamps (like <code>calldate</code> and <code>callend</code>) with either second or millisecond resolution. Millisecond precision is useful for granular analysis and troubleshooting of call timing issues.


===With the GUI===
*  '''Modern Installations (2025+):''' Millisecond precision is '''enabled by default'''. You do not need to take any action.
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.
*  '''Older Installations:''' If your system was installed before this feature became the default, timestamps will be stored with second precision. This guide explains how to perform the necessary database migration.
(beware that action can take hours/days and the GUI may be unusable until done.


===Without the GUI===
The process for enabling this feature on an older system depends on whether your database already contains data.
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
== Scenario A: For New or Empty Databases (Legacy Setups) ==
cdr_next_1, cdr_next_2 ...,cdr_next_N (column calldate)
If you are setting up an older version of VoIPmonitor on a new, empty database, the process is a simple one-line configuration change.
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:
;Step 1: Edit voipmonitor.conf
ALTER TABLE cdr_next_1 modify column calldate datetime(3) not null;
:Add the following line to <code>/etc/voipmonitor.conf</code>:
=== After db tables altered ===
<syntaxhighlight lang="ini">
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.
time_precision_in_ms = yes
</syntaxhighlight>


== Experienced issues during ALTERS==
;Step 2: Restart the Sniffer
=== Warning code 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. ===
:Restart the sensor service. On its first connection to the empty database, it will automatically create all tables with the correct <code>DATETIME(3)</code> and <code>DECIMAL</code> column types.
We experienced on a version 5.5.60 of the mariadb this error when innodb_file_format not set to barracuda was Antelope.
<syntaxhighlight lang="bash">
The solution was to disable strict_mode and enable the Barracuda as the file format for innodb as suggested in our scale section for [[https://www.voipmonitor.org/doc/Scaling#MySQL_performance mysql]]
systemctl restart voipmonitor
sql_mode=NO_ENGINE_SUBSTITUTION
</syntaxhighlight>
innodb_file_format = barracuda
 
== Scenario B: For Existing Databases with Data (Legacy Setups) ==
If you have an existing, older database with call data, enabling millisecond precision requires altering numerous tables. This can be a very long and resource-intensive process, potentially taking hours or even days on databases with billions of records.
 
{{Warning|It is strongly recommended to perform this during a planned maintenance window.}}
 
The process involves two steps: first enabling the setting in the configuration, and second, migrating the database schema.
 
=== Step 1: Enable the Setting in voipmonitor.conf ===
First, add the following line to <code>/etc/voipmonitor.conf</code>. This tells the sniffer to start using the new format once the database is ready.
<syntaxhighlight lang="ini">
time_precision_in_ms = yes
</syntaxhighlight>
{{Warning|Do not restart the sniffer yet! The database schema must be updated first.}}
 
=== Step 2: Migrate the Database Schema ===
You can perform the migration either through the GUI (recommended) or manually via the command line.
 
==== Option 1: Using the GUI (Recommended) ====
The GUI provides a specific tool for this migration.
 
{{Note|The "Check MySQL Schema" tool described below is designed specifically for enabling millisecond precision. For other database schema errors (such as "Unknown column" errors), use the [[FAQ|FAQ's check_tables=1 solution]] instead.}}
 
# Log in to the VoIPmonitor web interface.
# Navigate to '''Tools -> System Status -> Check MySQL Schema'''.
# The tool will scan your database and list any required changes.
# Check the boxes for all tables that show the message: "missing support for time accuracy in milliseconds".
# Click the '''Start Upgrade / Run SQL''' button.
 
{{Warning|This process will lock tables as it modifies them and can make the GUI unresponsive until it is complete.}}
 
==== Option 2: Manual Migration (Advanced) ====
If you do not have GUI access, you can run the <code>ALTER TABLE</code> commands manually.
 
{{Note|1=
* Connect to your MySQL/MariaDB server using a command-line client.
* The following commands can take a very long time to execute on large tables.
* You only need to run the <code>ALTER</code> command for tables that actually exist in your database.
}}
 
<syntaxhighlight lang="sql">
-- Main CDR table
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;
 
-- CDR extension tables
ALTER TABLE cdr_next MODIFY COLUMN calldate DATETIME(3) NOT NULL;
ALTER TABLE cdr_proxy MODIFY COLUMN calldate DATETIME(3) NOT NULL;
-- ... (and so on for all relevant tables)
</syntaxhighlight>
*(A full list of all `ALTER` commands can be found in the original version of this document if needed.)*
 
=== Step 3: Restart Services ===
After the database migration is complete, you can safely restart the sniffer service.
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
</syntaxhighlight>
You may also need to log out and log back into the GUI to see the changes reflected in the CDR view.
 
== Distributed Architecture: Remote Probes ==
 
If you are using VoIPmonitor in a distributed setup with remote probes connecting to a central server, additional steps are required to enable millisecond precision across all sensors.
 
The process differs depending on your deployment mode (<code>packetbuffer_sender</code> setting).
 
<kroki lang="mermaid">
%%{init: {'flowchart': {'nodeSpacing': 15, 'rankSpacing': 40}}}%%
flowchart TB
    subgraph LP["Local Processing Mode"]
        direction TB
        LP_P1[Remote Probe 1] -->|CDR| LP_S[Central Server]
        LP_P2[Remote Probe 2] -->|CDR| LP_S
        LP_S --> LP_DB[(Database)]
    end
 
    subgraph PM["Packet Mirroring Mode"]
        direction TB
        PM_P1[Remote Probe 1] -->|Raw Packets| PM_S[Central Server]
        PM_P2[Remote Probe 2] -->|Raw Packets| PM_S
        PM_S --> PM_DB[(Database)]
    end
 
    LP_Note["Config needed on:<br/>ALL probes + server"]
    PM_Note["Config needed on:<br/>Server only"]
 
    LP --> LP_Note
    PM --> PM_Note
</kroki>
 
{| class="wikitable"
|-
! Mode !! Analysis Location !! Where to Set <code>time_precision_in_ms</code>
|-
| '''Local Processing''' (<code>packetbuffer_sender = no</code>) || Remote probe || On each remote probe AND on central server
|-
| '''Packet Mirroring''' (<code>packetbuffer_sender = yes</code>) || Central server || On central server only
|}
 
=== Enabling Milliseconds in Local Processing Mode ===
 
In Local Processing mode, remote probes analyze calls locally and send CDRs to the central server. For millisecond precision to work:
 
;Step 1: Enable the setting on all remote probes
:On <b>each</b> remote probe, add to <code>/etc/voipmonitor.conf</code>:
<syntaxhighlight lang="ini">
time_precision_in_ms = yes
</syntaxhighlight>
 
;Step 2: Restart all remote probes
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
</syntaxhighlight>
 
;Step 3: Enable on the central server
:On the central server, add to <code>/etc/voipmonitor.conf</code>:
<syntaxhighlight lang="ini">
time_precision_in_ms = yes
</syntaxhighlight>
 
;Step 4: Migrate the database schema on the central server
:Follow the migration steps in [[#Scenario B: For Existing Databases with Data (Legacy Setups)|Scenario B]] above. The central server handles all database operations.
 
;Step 5: Restart all remote probes again
:<b>Critical:</b> After migrating the database schema on the central server, you <b>must restart each remote probe</b> to force them to re-check the database schema and apply the new configuration. Without this restart, probes will continue using the old schema and send second-precision CDRs.
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
</syntaxhighlight>
 
;Step 6: Verify
:In the GUI, navigate to '''Settings -> Sensors''' and check that the uptime for each sensor reflects the recent restart.
 
=== Enabling Milliseconds in Packet Mirroring Mode ===
 
In Packet Mirroring mode, the central server receives raw packets from sensors and performs all analysis. The sensors act as forwarders only.
 
;Step 1: Enable on the central server
:On the <b>central server</b> only, add to <code>/etc/voipmonitor.conf</code>:
<syntaxhighlight lang="ini">
time_precision_in_ms = yes
</syntaxhighlight>
 
;Step 2: Migrate the database schema on the central server
:Follow the migration steps in [[#Scenario B: For Existing Databases with Data (Legacy Setups)|Scenario B]] above.
 
;Step 3: Restart the central server
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
</syntaxhighlight>
 
No configuration changes are required on remote probes for this mode. However, to ensure probes re-check the database schema, it is recommended to restart them:
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
</syntaxhighlight>
 
;Step 4: Verify
:In the GUI, navigate to '''Settings -> Sensors''' and check that the uptime for each sensor reflects the recent restart.
 
=== Critical Point: Remote Probes Must Restart After Schema Migration ===
 
{{Warning|When you enable millisecond precision on an existing distributed installation, remote probes do not automatically detect database schema changes on the central server. They check the schema at startup and cache the column types.}}
 
If you change the database schema (e.g., converting <code>DATETIME</code> to <code>DATETIME(3)</code>), the change only propagates to probes when they restart.
 
'''Symptoms of missed probe restarts:'''
* Central server has <code>time_precision_in_ms = yes</code> and migrated schema
* Remote probes show milliseconds for '''new''' CDRs (if they were restarted ''after'' the change)
* Remote probes show seconds for '''older''' CDRs that occurred before the restart
* In Local Processing mode, if probes were not restarted after schema migration, they will continue sending second-precision data regardless of the central server configuration
 
'''Solution:''' Restart all remote probes after completing the database schema migration on the central server.
 
== Troubleshooting ==
 
=== create_partition_v3 Stored Procedure Fails After Enabling Millisecond Precision ===
 
After enabling <code>time_precision_in_ms = yes</code>, you may encounter errors where the <code>create_partition_v3</code> stored procedure fails. This is typically caused by query cache holding old cached statements that conflict with the new schema.
 
;Step 1: Drop the old stored procedure
<syntaxhighlight lang="sql">
USE voipmonitor;
DROP PROCEDURE create_partition_v3;
</syntaxhighlight>
 
;Step 2: Disable query cache
Edit <code>/etc/voipmonitor.conf</code> and set:
<syntaxhighlight lang="ini">
query_cache = no
</syntaxhighlight>
 
;Step 3: Restart voipmonitor
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
</syntaxhighlight>
 
;Step 4: Remove cached query files
Delete all query cache files from the spool directory (the path may vary by installation):
<syntaxhighlight lang="bash">
rm -f /var/spool/voipmonitor/qoq-*
</syntaxhighlight>
 
;Step 5: Re-enable query cache
Edit <code>/etc/voipmonitor.conf</code> again and set:
<syntaxhighlight lang="ini">
query_cache = yes
</syntaxhighlight>
 
;Step 6: Restart voipmonitor again
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
</syntaxhighlight>
 
The <code>create_partition_v3</code> procedure will be automatically recreated with the correct schema when voipmonitor starts and successfully connects to the database.
 
=== Warning Code 1478: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope ===
If you encounter this error while running the <code>ALTER TABLE</code> commands on an older MySQL/MariaDB server (e.g., MariaDB 5.5), it means your database is using an outdated file format.
 
;Solution:
:Edit your <code>my.cnf</code> file and set the following parameters, then restart the MySQL service. This enables the modern "Barracuda" file format, which is required for page compression and other features.
<syntaxhighlight lang="ini">
# /etc/mysql/my.cnf
 
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION
innodb_file_format = barracuda
</syntaxhighlight>
After restarting MySQL, you should be able to run the <code>ALTER TABLE</code> commands successfully.
 
== AI Summary for RAG ==
'''Summary:''' This guide explains how to manage millisecond precision for timestamps in VoIPmonitor. All new installations (since 2025) have this feature '''enabled by default'''. For older installations, it provides two scenarios: Scenario A for new/empty databases (only requires setting <code>time_precision_in_ms=yes</code>), and Scenario B for existing databases with data (requires schema migration). The schema migration can be done via GUI "Check MySQL Schema" tool (recommended) or manual <code>ALTER TABLE</code> commands. For distributed architecture: Local Processing mode (<code>packetbuffer_sender=no</code>) requires setting on each probe AND central server; Packet Mirroring mode (<code>packetbuffer_sender=yes</code>) requires setting on central server only. Critical: restart all probes after schema migration. Troubleshooting covers <code>create_partition_v3</code> stored procedure failures (query cache issue) and MySQL warning 1478 (<code>innodb_file_format</code>).
 
'''Keywords:''' millisecond, precision, timestamp, calldate, datetime(3), database, schema, migration, alter table, time_precision_in_ms, Check MySQL Schema, existing data, new database, innodb_file_format, Barracuda, default setting, create_partition_v3, stored procedure, query cache, distributed architecture, remote probes, sensors, packetbuffer_sender, local processing, packet mirroring, client-server mode, central server, restart probes, schema synchronization
 
'''Key Questions:'''
* Is millisecond precision enabled by default in new installations?
* How do I change timestamp precision from seconds to milliseconds on an old system?
* What does the <code>time_precision_in_ms</code> option do?
* What is the difference between "Check MySQL Schema" tool and check_tables=1 solution for database schema errors?
* How do I update my database schema to support milliseconds?
* What <code>ALTER TABLE</code> commands are needed for millisecond timestamps?
* How to fix <code>create_partition_v3</code> stored procedure failure after enabling milliseconds?
* How to fix "Warning code 1478 InnoDB: ROW_FORMAT=COMPRESSED" during a schema upgrade?
* How do I enable millisecond precision in a distributed architecture with remote probes?
* Do I need to restart remote probes after migrating the database schema for milliseconds?
* Where should I configure <code>time_precision_in_ms</code> in Local Processing vs Packet Mirroring mode?
* Why are my remote probes still logging seconds after enabling milliseconds on the central server?

Latest revision as of 18:01, 6 January 2026


This guide explains how to manage millisecond precision for timestamps in VoIPmonitor. While all new installations since 2025 have this feature enabled by default, this guide details how to enable it on older systems.

Overview

VoIPmonitor can store and display all call-related timestamps (like calldate and callend) with either second or millisecond resolution. Millisecond precision is useful for granular analysis and troubleshooting of call timing issues.

  • Modern Installations (2025+): Millisecond precision is enabled by default. You do not need to take any action.
  • Older Installations: If your system was installed before this feature became the default, timestamps will be stored with second precision. This guide explains how to perform the necessary database migration.

The process for enabling this feature on an older system depends on whether your database already contains data.

Scenario A: For New or Empty Databases (Legacy Setups)

If you are setting up an older version of VoIPmonitor on a new, empty database, the process is a simple one-line configuration change.

Step 1
Edit voipmonitor.conf
Add the following line to /etc/voipmonitor.conf:
time_precision_in_ms = yes
Step 2
Restart the Sniffer
Restart the sensor service. On its first connection to the empty database, it will automatically create all tables with the correct DATETIME(3) and DECIMAL column types.
systemctl restart voipmonitor

Scenario B: For Existing Databases with Data (Legacy Setups)

If you have an existing, older database with call data, enabling millisecond precision requires altering numerous tables. This can be a very long and resource-intensive process, potentially taking hours or even days on databases with billions of records.

⚠️ Warning: It is strongly recommended to perform this during a planned maintenance window.

The process involves two steps: first enabling the setting in the configuration, and second, migrating the database schema.

Step 1: Enable the Setting in voipmonitor.conf

First, add the following line to /etc/voipmonitor.conf. This tells the sniffer to start using the new format once the database is ready. 

time_precision_in_ms = yes

⚠️ Warning: Do not restart the sniffer yet! The database schema must be updated first.

Step 2: Migrate the Database Schema

You can perform the migration either through the GUI (recommended) or manually via the command line.

Option 1: Using the GUI (Recommended)

The GUI provides a specific tool for this migration.

ℹ️ Note: The "Check MySQL Schema" tool described below is designed specifically for enabling millisecond precision. For other database schema errors (such as "Unknown column" errors), use the FAQ's check_tables=1 solution instead.

  1. Log in to the VoIPmonitor web interface.
  2. Navigate to Tools -> System Status -> Check MySQL Schema.
  3. The tool will scan your database and list any required changes.
  4. Check the boxes for all tables that show the message: "missing support for time accuracy in milliseconds".
  5. Click the Start Upgrade / Run SQL button.

⚠️ Warning: This process will lock tables as it modifies them and can make the GUI unresponsive until it is complete.

Option 2: Manual Migration (Advanced)

If you do not have GUI access, you can run the ALTER TABLE commands manually.

ℹ️ Note: * Connect to your MySQL/MariaDB server using a command-line client.

  • The following commands can take a very long time to execute on large tables.
  • You only need to run the ALTER command for tables that actually exist in your database.
-- Main CDR table
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;

-- CDR extension tables
ALTER TABLE cdr_next MODIFY COLUMN calldate DATETIME(3) NOT NULL;
ALTER TABLE cdr_proxy MODIFY COLUMN calldate DATETIME(3) NOT NULL;
-- ... (and so on for all relevant tables)
  • (A full list of all `ALTER` commands can be found in the original version of this document if needed.)*

Step 3: Restart Services

After the database migration is complete, you can safely restart the sniffer service. 

systemctl restart voipmonitor

You may also need to log out and log back into the GUI to see the changes reflected in the CDR view.

Distributed Architecture: Remote Probes

If you are using VoIPmonitor in a distributed setup with remote probes connecting to a central server, additional steps are required to enable millisecond precision across all sensors.

The process differs depending on your deployment mode (packetbuffer_sender setting).

Mode Analysis Location Where to Set time_precision_in_ms
Local Processing (packetbuffer_sender = no) Remote probe On each remote probe AND on central server
Packet Mirroring (packetbuffer_sender = yes) Central server On central server only

Enabling Milliseconds in Local Processing Mode

In Local Processing mode, remote probes analyze calls locally and send CDRs to the central server. For millisecond precision to work:

Step 1
Enable the setting on all remote probes
On each remote probe, add to /etc/voipmonitor.conf:
time_precision_in_ms = yes
Step 2
Restart all remote probes
systemctl restart voipmonitor
Step 3
Enable on the central server
On the central server, add to /etc/voipmonitor.conf:
time_precision_in_ms = yes
Step 4
Migrate the database schema on the central server
Follow the migration steps in Scenario B above. The central server handles all database operations.
Step 5
Restart all remote probes again
Critical: After migrating the database schema on the central server, you must restart each remote probe to force them to re-check the database schema and apply the new configuration. Without this restart, probes will continue using the old schema and send second-precision CDRs.
systemctl restart voipmonitor
Step 6
Verify
In the GUI, navigate to Settings -> Sensors and check that the uptime for each sensor reflects the recent restart.

Enabling Milliseconds in Packet Mirroring Mode

In Packet Mirroring mode, the central server receives raw packets from sensors and performs all analysis. The sensors act as forwarders only.

Step 1
Enable on the central server
On the central server only, add to /etc/voipmonitor.conf:
time_precision_in_ms = yes
Step 2
Migrate the database schema on the central server
Follow the migration steps in Scenario B above.
Step 3
Restart the central server
systemctl restart voipmonitor

No configuration changes are required on remote probes for this mode. However, to ensure probes re-check the database schema, it is recommended to restart them: 

systemctl restart voipmonitor
Step 4
Verify
In the GUI, navigate to Settings -> Sensors and check that the uptime for each sensor reflects the recent restart.

Critical Point: Remote Probes Must Restart After Schema Migration

⚠️ Warning: When you enable millisecond precision on an existing distributed installation, remote probes do not automatically detect database schema changes on the central server. They check the schema at startup and cache the column types.

If you change the database schema (e.g., converting DATETIME to DATETIME(3)), the change only propagates to probes when they restart.

Symptoms of missed probe restarts:

  • Central server has time_precision_in_ms = yes and migrated schema
  • Remote probes show milliseconds for new CDRs (if they were restarted after the change)
  • Remote probes show seconds for older CDRs that occurred before the restart
  • In Local Processing mode, if probes were not restarted after schema migration, they will continue sending second-precision data regardless of the central server configuration

Solution: Restart all remote probes after completing the database schema migration on the central server.

Troubleshooting

create_partition_v3 Stored Procedure Fails After Enabling Millisecond Precision

After enabling time_precision_in_ms = yes, you may encounter errors where the create_partition_v3 stored procedure fails. This is typically caused by query cache holding old cached statements that conflict with the new schema.

Step 1
Drop the old stored procedure
USE voipmonitor;
DROP PROCEDURE create_partition_v3;
Step 2
Disable query cache

Edit /etc/voipmonitor.conf and set: 

query_cache = no
Step 3
Restart voipmonitor
systemctl restart voipmonitor
Step 4
Remove cached query files

Delete all query cache files from the spool directory (the path may vary by installation): 

rm -f /var/spool/voipmonitor/qoq-*
Step 5
Re-enable query cache

Edit /etc/voipmonitor.conf again and set: 

query_cache = yes
Step 6
Restart voipmonitor again
systemctl restart voipmonitor

The create_partition_v3 procedure will be automatically recreated with the correct schema when voipmonitor starts and successfully connects to the database.

Warning Code 1478: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope

If you encounter this error while running the ALTER TABLE commands on an older MySQL/MariaDB server (e.g., MariaDB 5.5), it means your database is using an outdated file format.

Solution
Edit your my.cnf file and set the following parameters, then restart the MySQL service. This enables the modern "Barracuda" file format, which is required for page compression and other features.
# /etc/mysql/my.cnf

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION
innodb_file_format = barracuda

After restarting MySQL, you should be able to run the ALTER TABLE commands successfully.

AI Summary for RAG

Summary: This guide explains how to manage millisecond precision for timestamps in VoIPmonitor. All new installations (since 2025) have this feature enabled by default. For older installations, it provides two scenarios: Scenario A for new/empty databases (only requires setting time_precision_in_ms=yes), and Scenario B for existing databases with data (requires schema migration). The schema migration can be done via GUI "Check MySQL Schema" tool (recommended) or manual ALTER TABLE commands. For distributed architecture: Local Processing mode (packetbuffer_sender=no) requires setting on each probe AND central server; Packet Mirroring mode (packetbuffer_sender=yes) requires setting on central server only. Critical: restart all probes after schema migration. Troubleshooting covers create_partition_v3 stored procedure failures (query cache issue) and MySQL warning 1478 (innodb_file_format).

Keywords: millisecond, precision, timestamp, calldate, datetime(3), database, schema, migration, alter table, time_precision_in_ms, Check MySQL Schema, existing data, new database, innodb_file_format, Barracuda, default setting, create_partition_v3, stored procedure, query cache, distributed architecture, remote probes, sensors, packetbuffer_sender, local processing, packet mirroring, client-server mode, central server, restart probes, schema synchronization

Key Questions:

  • Is millisecond precision enabled by default in new installations?
  • How do I change timestamp precision from seconds to milliseconds on an old system?
  • What does the time_precision_in_ms option do?
  • What is the difference between "Check MySQL Schema" tool and check_tables=1 solution for database schema errors?
  • How do I update my database schema to support milliseconds?
  • What ALTER TABLE commands are needed for millisecond timestamps?
  • How to fix create_partition_v3 stored procedure failure after enabling milliseconds?
  • How to fix "Warning code 1478 InnoDB: ROW_FORMAT=COMPRESSED" during a schema upgrade?
  • How do I enable millisecond precision in a distributed architecture with remote probes?
  • Do I need to restart remote probes after migrating the database schema for milliseconds?
  • Where should I configure time_precision_in_ms in Local Processing vs Packet Mirroring mode?
  • Why are my remote probes still logging seconds after enabling milliseconds on the central server?
Retrieved from "https://www.voipmonitor.org/doc/index.php?title=How_to_enable_milliseconds_precision&oldid=9157"