How to enable milliseconds precision: Difference between revisions

From VoIPmonitor.org
(Clarify that Check MySQL Schema tool is only for millisecond precision, not general schema errors)
(Review: improved formatting (Warning/Note templates, <code> tags), added distributed architecture diagram, streamlined AI Summary)
 
(One intermediate revision by the same user not shown)
Line 2: Line 2:
[[Category:GUI manual]]
[[Category:GUI manual]]


'''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 or disable it if needed.'''
'''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 ==
== 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.
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.


*  '''Modern Installations (2023+):''' Millisecond precision is '''enabled by default'''. You do not need to take any action.
*  '''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.
*  '''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.


Line 16: Line 16:


;Step 1: Edit voipmonitor.conf
;Step 1: Edit voipmonitor.conf
:Add the following line to `/etc/voipmonitor.conf`:
:Add the following line to <code>/etc/voipmonitor.conf</code>:
<pre>time_precision_in_ms = yes</pre>
<syntaxhighlight lang="ini">
time_precision_in_ms = yes
</syntaxhighlight>


;Step 2: Restart the Sniffer
;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.
: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.
<pre>systemctl restart voipmonitor</pre>
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
</syntaxhighlight>


== Scenario B: For Existing Databases with Data (Legacy Setups) ==
== 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. '''It is strongly recommended to perform this during a planned maintenance window.'''
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.
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 ===
=== 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.
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.
<pre>time_precision_in_ms = yes</pre>
<syntaxhighlight lang="ini">
'''Do not restart the sniffer yet!''' The database schema must be updated first.
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 ===
=== Step 2: Migrate the Database Schema ===
Line 39: Line 47:
The GUI provides a specific tool for this migration.
The GUI provides a specific tool for this migration.


'''Important:''' 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.
{{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.
# Log in to the VoIPmonitor web interface.
Line 47: Line 55:
# Click the '''Start Upgrade / Run SQL''' button.
# 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.
{{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) ====
==== Option 2: Manual Migration (Advanced) ====
If you do not have GUI access, you can run the `ALTER TABLE` commands manually.
If you do not have GUI access, you can run the <code>ALTER TABLE</code> commands manually.


'''Important:'''
{{Note|1=
* Connect to your MySQL/MariaDB server using a command-line client.
* 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.
* 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.
* You only need to run the <code>ALTER</code> command for tables that actually exist in your database.
}}


<pre>
<syntaxhighlight lang="sql">
# Main CDR table
-- 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;
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
-- CDR extension tables
ALTER TABLE cdr_next MODIFY COLUMN calldate DATETIME(3) NOT 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_proxy MODIFY COLUMN calldate DATETIME(3) NOT NULL;
# ... (and so on for all relevant tables)
-- ... (and so on for all relevant tables)
</pre>
</syntaxhighlight>
*(A full list of all `ALTER` commands can be found in the original version of this document if needed.)*
*(A full list of all `ALTER` commands can be found in the original version of this document if needed.)*


=== Step 3: Restart Services ===
=== Step 3: Restart Services ===
After the database migration is complete, you can safely restart the sniffer service.
After the database migration is complete, you can safely restart the sniffer service.
<pre>systemctl restart voipmonitor</pre>
<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.
You may also need to log out and log back into the GUI to see the changes reflected in the CDR view.


Line 78: Line 89:


The process differs depending on your deployment mode (<code>packetbuffer_sender</code> setting).
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"
{| class="wikitable"
Line 94: Line 129:
;Step 1: Enable the setting on all remote probes
;Step 1: Enable the setting on all remote probes
:On <b>each</b> remote probe, add to <code>/etc/voipmonitor.conf</code>:
:On <b>each</b> remote probe, add to <code>/etc/voipmonitor.conf</code>:
<pre>time_precision_in_ms = yes</pre>
<syntaxhighlight lang="ini">
time_precision_in_ms = yes
</syntaxhighlight>


;Step 2: Restart all remote probes
;Step 2: Restart all remote probes
<pre>systemctl restart voipmonitor</pre>
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
</syntaxhighlight>


;Step 3: Enable on the central server
;Step 3: Enable on the central server
:On the central server, add to <code>/etc/voipmonitor.conf</code>:
:On the central server, add to <code>/etc/voipmonitor.conf</code>:
<pre>time_precision_in_ms = yes</pre>
<syntaxhighlight lang="ini">
time_precision_in_ms = yes
</syntaxhighlight>


;Step 4: Migrate the database schema on the central server
;Step 4: Migrate the database schema on the central server
Line 108: Line 149:
;Step 5: Restart all remote probes again
;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.
:<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.
<pre>systemctl restart voipmonitor</pre>
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
</syntaxhighlight>


;Step 6: Verify
;Step 6: Verify
Line 119: Line 162:
;Step 1: Enable on the central server
;Step 1: Enable on the central server
:On the <b>central server</b> only, add to <code>/etc/voipmonitor.conf</code>:
:On the <b>central server</b> only, add to <code>/etc/voipmonitor.conf</code>:
<pre>time_precision_in_ms = yes</pre>
<syntaxhighlight lang="ini">
time_precision_in_ms = yes
</syntaxhighlight>


;Step 2: Migrate the database schema on the central server
;Step 2: Migrate the database schema on the central server
Line 125: Line 170:


;Step 3: Restart the central server
;Step 3: Restart the central server
<pre>systemctl restart voipmonitor</pre>
<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:
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:
<pre>systemctl restart voipmonitor</pre>
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
</syntaxhighlight>


;Step 4: Verify
;Step 4: Verify
Line 135: Line 184:
=== Critical Point: Remote Probes Must Restart After Schema Migration ===
=== Critical Point: Remote Probes Must Restart After Schema Migration ===


<b>Important:</b> 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.
{{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.}}


<b>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.</b>
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.


<b>Symptoms of missed probe restarts:</b>
'''Symptoms of missed probe restarts:'''
* Central server has <code>time_precision_in_ms = yes</code> and migrated schema
* Central server has <code>time_precision_in_ms = yes</code> and migrated schema
* Remote probes show milliseconds for <b>new</b> CDRs (if they were restarted <i>after</i> the change)
* Remote probes show milliseconds for '''new''' CDRs (if they were restarted ''after'' the change)
* Remote probes show seconds for <b>older</b> CDRs that occurred before the restart
* 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
* 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


<b>Solution:</b> Restart all remote probes after completing the database schema migration on the central server.
'''Solution:''' Restart all remote probes after completing the database schema migration on the central server.


== Troubleshooting ==
== Troubleshooting ==
Line 151: Line 200:
=== create_partition_v3 Stored Procedure Fails After Enabling Millisecond Precision ===
=== 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.
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
;Step 1: Drop the old stored procedure
<pre>
<syntaxhighlight lang="sql">
USE VOIPMONITOR;
USE voipmonitor;
DROP PROCEDURE create_partition_v3;
DROP PROCEDURE create_partition_v3;
</pre>
</syntaxhighlight>


;Step 2: Disable query cache
;Step 2: Disable query cache
Edit `/etc/voipmonitor.conf` and set:
Edit <code>/etc/voipmonitor.conf</code> and set:
<pre>query_cache = no</pre>
<syntaxhighlight lang="ini">
query_cache = no
</syntaxhighlight>


;Step 3: Restart voipmonitor
;Step 3: Restart voipmonitor
<pre>systemctl restart voipmonitor</pre>
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
</syntaxhighlight>


;Step 4: Remove cached query files
;Step 4: Remove cached query files
Delete all query cache files from the spool directory (the path may vary by installation):
Delete all query cache files from the spool directory (the path may vary by installation):
<pre>rm -f /var/spool/voipmonitor/qoq-*</pre>
<syntaxhighlight lang="bash">
rm -f /var/spool/voipmonitor/qoq-*
</syntaxhighlight>


;Step 5: Re-enable query cache
;Step 5: Re-enable query cache
Edit `/etc/voipmonitor.conf` again and set:
Edit <code>/etc/voipmonitor.conf</code> again and set:
<pre>query_cache = yes</pre>
<syntaxhighlight lang="ini">
query_cache = yes
</syntaxhighlight>


;Step 6: Restart voipmonitor again
;Step 6: Restart voipmonitor again
<pre>systemctl restart voipmonitor</pre>
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
</syntaxhighlight>


The `create_partition_v3` procedure will be automatically recreated with the correct schema when voipmonitor starts and successfully connects to the database.
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 ===
=== 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.
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:
;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.
: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.
<pre>
<syntaxhighlight lang="ini">
# /etc/mysql/my.cnf
# /etc/mysql/my.cnf


Line 190: Line 249:
sql_mode=NO_ENGINE_SUBSTITUTION
sql_mode=NO_ENGINE_SUBSTITUTION
innodb_file_format = barracuda
innodb_file_format = barracuda
</pre>
</syntaxhighlight>
After restarting MySQL, you should be able to run the `ALTER TABLE` commands successfully.
After restarting MySQL, you should be able to run the <code>ALTER TABLE</code> commands successfully.


== AI Summary for RAG ==
== AI Summary for RAG ==
'''Summary:''' This guide explains how to manage millisecond precision for timestamps in VoIPmonitor. It clarifies that all new installations (since 2025) have this feature '''enabled by default'''. For users with older installations, it provides two scenarios for enabling it. Scenario A is for new or empty databases, which only requires setting `time_precision_in_ms=yes` in `voipmonitor.conf`. Scenario B is a more complex guide for migrating existing databases with data, which involves first setting the configuration option and then updating the database schema. The guide presents two methods for the schema migration: using the built-in "Check MySQL Schema" tool in the GUI (recommended, specifically designed for millisecond precision migration only) or running a comprehensive list of `ALTER TABLE` commands manually, warning that this can be a very long process. '''IMPORTANT:''' For general database schema errors such as "Unknown column" errors, users should use the [[FAQ#Missing_or_Outdated_Database_Columns|check_tables=1 solution]] from the FAQ, NOT the GUI "Check MySQL Schema" tool which is only for enabling millisecond precision. A dedicated section covers distributed architecture: when using remote probes with Local Processing mode (`packetbuffer_sender=no`), enable `time_precision_in_ms` on each probe AND the central server; migrate schema on central server; then restart all probes CRITICALLY to force them to re-check the schema. With Packet Mirroring mode (`packetbuffer_sender=yes`), only the central server needs the setting and schema migration, but probe restarts are still recommended to ensure schema synchronization. The troubleshooting section covers two issues: `create_partition_v3` stored procedure failures due to query cache conflicts, and a MySQL warning code 1478 related to `innodb_file_format` on newer MySQL versions.
'''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, Settings Sensors
 
'''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:'''
'''Key Questions:'''
* Is millisecond precision enabled by default in new installations?
* Is millisecond precision enabled by default in new installations?
* How do I change timestamp precision from seconds to milliseconds on an old system?
* How do I change timestamp precision from seconds to milliseconds on an old system?
* What does the `time_precision_in_ms` option do?
* 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?
* 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?
* How do I update my database schema to support milliseconds?
* My database is full, can I still enable millisecond precision?
* What <code>ALTER TABLE</code> commands are needed for millisecond timestamps?
* What `ALTER TABLE` commands are needed for millisecond timestamps?
* How to fix <code>create_partition_v3</code> stored procedure failure after enabling milliseconds?
* 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 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?
* 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?
* 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?
* 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?
* 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"