Database troubleshooting

This page provides troubleshooting guidance for VoIPmonitor database-related issues including SQL queue problems, CDR delays, MySQL performance tuning, and database errors.

Quick Navigation - Database Troubleshooting
SQL Queue Issues	Database Errors	Performance & Migration
CDR Visibility Service Not Running (Quick Fix) CDR Delay Explained Delay Symptoms quick_save_cdr Option Time Change / Time Zone Issues SQL Queue SQLq/SQLf Metrics qoq Files Backlog query_cache Option	Table Corruption HEP Calls Not Appearing Real-Time 1062 - Lookup Table Limit (16777215) Find Affected Table Fix: cdr_reason_string_enable Permission Errors SUPER Privilege Error Related CDR Table INT Overflow (4B rows)	Tuning Database Threads MySQL Tuning Hardware Hardware Upgrade Signs MySQL to SSD Migration

Active Calls Visible, CDRs Not Showing (Server Crash/Restart)

If active calls are visible in the GUI but CDRs (Call Detail Records) are not displaying after a server crash and restart, the issue is likely that the VoIPmonitor sniffer service did not start automatically after the reboot. Active calls are retrieved from sensor memory in real-time, while CDRs require the running service to write completed call data to the database.

First Check: VoIPmonitor Service Status

The most common cause of missing CDRs after a server crash or restart is that the VoIPmonitor service is not running.

Step 1: Check Service Status

Check if the VoIPmonitor service is running:

systemctl status voipmonitor
# or
journalctl -u voipmonitor -n 50

Look for:

• Active: active (running) - Service is running correctly
• Active: inactive (dead), failed, or exited - Service is not running

Step 2: Start the Service If Not Running

If the service is inactive or failed, start it:

sudo systemctl start voipmonitor

Verify the service started successfully:

sudo systemctl status voipmonitor

You should see Active: active (running) and no errors.

Step 3: Verify CDRs Are Being Written

After starting the service, make a test call and verify that it appears in the CDR list within 10-60 seconds (depending on your quick_save_cdr setting). New calls should now be visible in the GUI.

ℹ️ Note: Active Calls remained visible because they are retrieved from sensor memory in real-time, not from the database. The database (CDR) writes stopped because the service was not running.

Step 4: Enable Auto-Start on Boot (Prevent Recurrence)

If the service was not running after the server reboot, ensure it is enabled for automatic startup:

sudo systemctl enable voipmonitor

Verify the service is enabled:

sudo systemctl is-enabled voipmonitor

Expected output: enabled. If you see disabled or static, the service may not start automatically on future reboots.

If Service Is Running But CDRs Still Missing

If the VoIPmonitor service is confirmed running but CDRs are still not appearing, proceed to the SQL Queue and CDR Delays section below for database-specific troubleshooting.

SQL Queue and CDR Delays

Delay between active call and CDR view

The Active Calls view in the GUI displays the timestart of calls (INVITEs) obtained from the VoIPmonitor sniffer service, whereas the CDR view shows the stored CDRs (after the call ends) from the database.

Symptoms of Database Delays

When the database cannot keep up with CDR insertion rates, you may experience:

• Slow CDR appearance in GUI - New calls take minutes to appear after they end
• "Crontab log is too old" warning - The cron job runs slowly (every 5-10 minutes or more) instead of every minute due to database overload
• Lag between call end and reporting - Daily reports and alerts process outdated data

To diagnose cron-related delays specifically, see "Crontab log is too old" troubleshooting.

SQLq/SQLf

In the service status (expanded status, status line) under GUI -> Settings -> Sensors : status, the SQLq/SQLf values represent the size of the queue before the CDRs are pushed to the database. When SQLq/SQLf is high, it usually indicates that the database is unable to process requests in a timely manner, causing them to queue.

Checking Sensor Status Logs for CDR Queue

If active calls are visible in the GUI but CDRs are missing from the database, check the sensor's status log for the SQLf[cdr: ...] metric. This shows the number of queued CDR files waiting to be inserted into the database.

To check the sensor status log:

# Method 1: Via GUI (Recommended)
# Navigate to: GUI -> Tools -> Generated Debug Log
# Look for SQLf[cdr: N] where N is the count of queued CDR files

# Method 2: Via service logs
journalctl -u voipmonitor -f | grep SQLf

The SQLf[cdr: ...] metric appears in the sensor's real-time status output. Monitor this value:

• Decreasing number - The sensor is processing the backlog and CDRs will appear in the GUI soon
• Stuck at high value - The database cannot keep up, consider performance tuning
• Near zero - All queued CDRs have been processed

This is especially useful in remote sensor deployments to verify whether the issue is a processing backlog (sensor SQLf queue) vs. a connection problem (sensor not sending data to central server).

Make sure that db config is not causing io overhead

If the MySQL configuration already follows the recommendations from the scaling section of our documentation (especially innodb_flush_log_at_trx_commit and innodb_buffer_pool_size):

https://www.voipmonitor.org/doc/Scaling#SSDs

Quick CDR Visibility (Reduce Delay)

If CDRs are taking too long to appear in the GUI after calls end (typically 30-60 seconds delay), you can reduce this delay by enabling quick CDR save mode. Add this parameter to /etc/voipmonitor.conf:

quick_save_cdr = yes

This speeds up the visibility of calls in the GUI by reducing the buffering delay before CDRs are written to the database.

⚠️ Warning: Performance Impact: Enabling quick_save_cdr increases CPU and I/O load on the database server by forcing more frequent CDR writes. Only use this if near-real-time CDR visibility is absolutely required.

Available options:

• no (default) - 10 second delay, recommended for most deployments
• yes - 3 second delay, moderate performance impact
• quick - 1 second delay, high performance impact

See Sniffer Configuration for more details on this parameter.

Enable Disk-Based Query Queue (Prevent OOM)

If your system is experiencing Out Of Memory (OOM) issues or if the SQL queue keeps growing during peak traffic, ensure that disk-based query queuing is enabled. Add or verify this parameter in /etc/voipmonitor.conf:

query_cache = yes

This is a critical feature. When enabled, SQL queries are first saved to disk-based queue files (qoq* files in the spool directory) before being sent to the database. This prevents OOM and data loss if the database is temporarily unavailable. The default is yes, but if it was previously set to no, changing it to yes will use disk storage instead of RAM for queued queries.

ℹ️ Note: Do not set query_cache = no to improve performance. While disabling query cache reduces CDR delay by holding queries in RAM instead of writing to qoq files, this creates a severe risk: if the database becomes unreachable and memory fills up, OOM killer will terminate the VoIPmonitor process and all queued CDRs will be lost. Keep query_cache = yes for data safety, and use quick_save_cdr instead for faster CDR visibility.

See Sniffer Configuration for more details on this parameter.

CDRs Not Showing After Server Time Change

If CDRs stop appearing in the GUI after a server time change, but active calls remain visible (since they are retrieved from sensor memory in real-time), this is typically caused by a timezone configuration mismatch, not a database or partitioning issue.

Diagnosis: Check SQLq/SQLf and Sensor Status

1. Navigate to Settings > Sensors and expand the sensor status 2. Check the SQLq/SQLf values:

  * If these are NOT growing, database processing is working correctly
  * Growing SQLq/SQLf indicates a database bottleneck (see SQL Queue Issues)

3. Check the last CDR stored to db timestamp:

  * If it is current (within the last few minutes), the database is receiving data
  * If it is stuck in the past or missing, there may be a timezone issue

ℹ️ Note: Active calls are visible because they are retrieved from sensor memory in real-time. CDRs are stored in the database after the call ends. If the timezone is misconfigured, CDRs may be written with timestamps that do not match what the GUI expects, causing them to appear invisible or be displayed incorrectly.

Verify Timezone Synchronization

Timezone configuration must be consistent across all components. Check the following settings:

GUI Host Timezone

The GUI host timezone setting must match the environment where the GUI is running:

1. Navigate to Settings > System Configuration > National 2. Verify the Timezone field matches the GUI server's timezone 3. On the GUI server, run: date to confirm the system time and timezone 4. The timezone format should be: Country/City (e.g., Europe/Prague)

This timezone setting affects report scheduling and alerts generated by the GUI.

Sensors Timezone

The Sensors Timezone setting controls how CDR timestamps are displayed in the GUI:

1. Navigate to Settings > System Configuration > National 2. Set the Sensors Timezone to match the timezone where your probes/sensors are generating CDRs 3. All sensors sending data to this database should use the same timezone 4. On sensor hosts, run: date to confirm the correct timezone and time are set

⚠️ Warning: If probes are configured to generate CDRs in different timezones, CDRs may not display correctly. Ensure all probes use either: (1) the same system timezone, OR (2) explicitly set the timezone or utc option in /etc/voipmonitor.conf to force consistency.

Override Sensor Timezone (Optional)

If a sensor's operating system timezone differs from the desired CDR timezone, you can override it in the sensor configuration:

Edit /etc/voipmonitor.conf on the sensor:

# Option 1: Override sensor timezone (system default is used if not set)
timezone = /usr/share/zoneinfo/Europe/London

# Option 2: Store all timestamps in UTC (recommended for multi-timezone deployments)
utc = yes

After making changes, restart the sensor:

systemctl restart voipmonitor

Verify Fix

After correcting timezone settings:

1. Complete a new test call (or wait for an ongoing call to finish) 2. Check the CDR list to verify new records appear with correct timestamps 3. If CDRs still do not appear, check the VoIPmonitor logs for database errors

💡 Tip: The regular Timezone setting (System Configuration > National) is for the GUI host itself (report scheduling, alerts). The Sensors Timezone setting controls CDR timestamp display. These can be different if your sensors and GUI are in different timezones.

See Also

• Settings - National configuration
• Sensor timezone configuration
• UTC storage option

More threads/connections to a db

You can also increase the number of threads used for connection to a db for particular use like CDRs - the VoIPmonitor sniffer service in /etc/voipmonitor.conf uses the option:

mysqlstore_max_threads_cdr = 8

Important: mysql_enable_set_id Limitation

If you are using mysql_enable_new_store = per_query or have enabled mysql_enable_set_id = yes, please note that this configuration limits your setup to a single sensor writing to the database. This option allows the central server to generate CDR IDs instead of waiting for MySQL's AUTO_INCREMENT, which can improve batch insert performance. However, this architecture only works when one sensor is responsible for all database writes. If you have multiple sensors writing to the same database, do not enable this option as it will cause ID conflicts and data corruption.

If you are processing a high volume of SIP OPTIONS, SUBSCRIBE, or NOTIFY messages and see the sip_msg queue growing, you can increase threads specifically for those messages:

mysqlstore_max_threads_sip_msg = 8

Automatic Thread Scaling

The VoIPmonitor sniffer can automatically increase SQL write threads when the database queue becomes large. This auto-scaling behavior provides additional throughput during high-traffic periods:

• Trigger condition: When the SQL queue exceeds 1,000 items
• Auto-scale limit: Threads automatically increase up to 99
• Applicable queues: CDR and SIP message (sip_msg) queues

This automatic scaling helps handle temporary traffic spikes without manual intervention. For sustained high traffic, you should still manually configure an appropriate base thread count.

ℹ️ Note:

However, if the database is waiting for storage I/O, increasing the number of threads will not help.

Clearing File Queue Backlog (qoq* Files)

When the file queue grows and recent calls are not appearing in CDR despite active calls being visible, you may have a backlog of qoq* files in the spool directory waiting to be processed.

What are qoq* Files?

The VoIPmonitor sniffer creates qoq* (queue) files in the spool directory (default: /var/spool/voipmonitor) to buffer database operations. These files contain queued SQL commands waiting to be inserted into the database.

When the database cannot keep up with the insertion rate:

• Active calls are visible in the GUI (retrieved from sensor memory in real-time)
• Recent CDRs do not appear (waiting in qoq queue files)
• SQLq metric stays high or continues growing

Solution 1: Wait for Queue Processing

The system will automatically process the qoq* files as the database catches up. Monitor the progress:

# Check the SQLf parameter in logs to monitor queue size
# SQLf should decrease over time as files are processed

# View current qoq files in spool directory
ls -lh /var/spool/voipmonitor/qoq* 2>/dev/null | wc -l

This is the preferred approach if losing older CDRs is not acceptable.

Solution 2: Delete qoq* Files (Emergency)

If immediate access to recent CDRs is critical and you can afford to lose older CDRs, you can delete the qoq* files to clear the backlog:

# WARNING: This will delete any CDRs still waiting in the files
# Stop the VoIPmonitor service first
systemctl stop voipmonitor

# Delete all qoq* files from the spool directory
rm -f /var/spool/voipmonitor/qoq*

# Start the VoIPmonitor service
systemctl start voipmonitor

⚠️ Warning: Data Loss Warning: Deleting qoq* files will delete any CDRs that were waiting in the queue files. Only use this method if: (1) Immediate access to recent CDRs is critical, (2) Losing older CDRs is acceptable, (3) You have exhausted all other options (configuration tuning, hardware upgrade).

This clears the backlog and allows new CDRs to be written immediately without waiting for the old queue to process.

MySQL/MariaDB Performance Tuning

Optimize MySQL Performance

Tune the MySQL/MariaDB server for better write performance to handle the high insert rate from VoIPmonitor.

Edit your MySQL configuration file (typically /etc/mysql/my.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf):

[mysqld]
# InnoDB buffer pool size - set to approximately 50-70% of available RAM on a dedicated database server
# On servers running VoIPmonitor and MySQL together, use approximately 30-50% of RAM
innodb_buffer_pool_size = 8G

# Reduce transaction durability for faster writes (may lose up to 1 second of data on crash)
innodb_flush_log_at_trx_commit = 2

Restart MySQL and VoIPmonitor:

systemctl restart mysql
systemctl restart voipmonitor

⚠️ Warning: Setting innodb_flush_log_at_trx_commit to 2 trades some data safety for performance. In the event of a power loss or crash, up to 1 second of the most recent transactions may be lost.

When Configuration Tuning Is Not Enough: Hardware Upgrade Required

If you have applied all the configuration optimizations above and the SQL queue continues to grow or the database remains significantly behind the processing queue, the underlying issue may be insufficient hardware.

Signs That Hardware Upgrade Is Necessary

• CPU load is consistently at or near 100% on both the database and GUI servers, even during normal traffic patterns
• Old or aging hardware - Servers with CPUs more than several years old may lack the performance of modern processors
• Configuration tuning provides only marginal improvement - After applying MySQL and VoIPmonitor optimizations, the delay between the "Last CDR in processing queue" and "Last CDR in database" remains significant

Diagnosing Hardware Limitations

Monitor CPU usage on both the database and GUI servers:

# Check CPU load during peak traffic
top

# Or use sar for historical data
sar -u 1 10

# Check CPU core usage per process
mpstat -P ALL 1 5

If the CPU load is consistently at or near 100% across multiple cores, the hardware cannot keep up with the traffic load. No amount of configuration tuning will solve this issue - the servers themselves need to be upgraded.

Hardware Upgrade Recommendations

Upgrade to a more modern CPU architecture with significantly better performance:

• CPU - Modern AMD EPYC or Intel Xeon Gold/Silver processors with more cores
• RAM - Ensure sufficient memory for the database buffer pool (see Memory Configuration)
• Storage - Use NVMe SSDs for the database to eliminate I/O bottlenecks

Architecture Consideration: Merge GUI and Database

In some cases, merging the GUI and database roles onto a single, powerful new server can be more efficient than maintaining separate, underpowered servers. A single modern server with sufficient CPU cores and RAM can often handle both workloads more effectively than multiple older servers.

For hardware sizing examples, see the Hardware page, which includes real-world deployments for various call volumes.

Migrating MySQL Data to Faster Storage

When upgrading from HDD or slow SATA SSDs to NVMe storage, you can migrate the MySQL data directory (`datadir`) while the system is running. There is no CDR loss expected with this method because the sniffer queues the CDRs internally and will process them after MySQL restarts.

Prerequisites:

• New SSD/NVMe storage installed and mounted
• Sufficient disk space on the new storage for the existing MySQL data
• MySQL/MariaDB service can be stopped briefly for the migration

Migration Procedure:

# 1. Prepare the new storage (if your SSD is hot-pluggable)
# Mount the new filesystem. Example:
mount /dev/nvme0n1p1 /mnt/fast_storage
mkdir /mnt/fast_storage/mysql

# 2. Stop the MySQL service
systemctl stop mysql
# Or for MariaDB:
# systemctl stop mariadb

# 3. Copy the MySQL data directory to the new location
# The -a flag preserves permissions and ownership, -x skips other filesystems
rsync -avx /var/lib/mysql/ /mnt/fast_storage/mysql/

# 4. Verify the copy
ls -la /mnt/fast_storage/mysql/
# Check that all databases are present

# 5. Update MySQL configuration to point to the new datadir location
# Edit /etc/mysql/my.cnf or /etc/my.cnf and change:
# datadir = /var/lib/mysql
# To:
# datadir = /mnt/fast_storage/mysql

# Also update the socket location if needed:
# socket = /tmp/mysql.sock  (or your preferred location)

# 6. Update AppArmor/SELinux (Ubuntu/Debian only)
# Edit /etc/apparmor.d/usr.sbin.mysqld and update the paths:
# /var/lib/mysql/ r,
# /var/lib/mysql/** rwk,
# Change to:
# /mnt/fast_storage/mysql/ r,
# /mnt/fast_storage/mysql/** rwk,

# Reload AppArmor:
systemctl reload apparmor

# 7. Start MySQL with the new datadir
systemctl start mysql

# 8. Verify MySQL is running and databases are accessible
mysql -e "SHOW DATABASES;"
mysql -e "SELECT COUNT(*) FROM cdr;"

# 9. Monitor the SQL queue in the VoIPmonitor GUI
# Navigate to GUI -> Settings -> Sensors -> Status
# The SQLq value should decrease as queued CDRs are processed

Important Notes:

• No CDR Loss: The VoIPmonitor sniffer queues CDRs in memory during MySQL downtime. These will be processed after MySQL restarts.
• Backup First: Always take a backup of `/var/lib/mysql` before migration.
• Service Downtime: Plan for MySQL to be stopped for the duration of the copy operation (depends on database size and storage speed).
• Storage Mount Options: For the new database partition, mount with ext4 optimizations:

/dev/nvme0n1p1  /mnt/fast_storage  ext4  defaults,noatime,data=writeback,barrier=0  0  1

• Permissions: Ensure the new datadir and all files are owned by the MySQL user (`mysql:mysql`).
• Symbolic Links Alternative: You can create a symbolic link instead of changing the datadir in my.cnf:

# After stopping MySQL and copying data:
mv /var/lib/mysql /var/lib/mysql.old
ln -s /mnt/fast_storage/mysql /var/lib/mysql
systemctl start mysql

Database Errors

Error 1062 - Lookup Table Auto-Increment Limit

If the sniffer logs show a database error `1062 - Duplicate entry '16777215' for key 'PRIMARY'` and new CDRs stop being stored, this is caused by a lookup table reaching its maximum auto-increment limit.

Symptoms

• CDRs stop being inserted into the database
• Sniffer logs show: `query error in [call __insert_10_0S1();]: 1062 - Duplicate entry '16777215' for key 'PRIMARY'`
• The error affects a lookup table (such as `cdr_sip_response` or `cdr_reason`)
• The value 16777215 (16,777,215) indicates the table is using `MEDIUMINT UNSIGNED` for the ID column

Root Cause

VoIPmonitor uses lookup tables (like `cdr_sip_response` or `cdr_reason`) to store unique values such as SIP response reason strings or custom response text. These are used to normalize data and reduce storage in the main `cdr` table.

When the system receives many unique SIP response strings or reason messages (e.g., different error messages from various carriers, devices with custom SIP header formats, or PBX-specific responses), the lookup table's auto-increment ID can reach the `MEDIUMINT` limit of 16,777,215. Once this limit is hit, new unique values cannot be inserted, causing all subsequent CDRs to fail with error 1062.

Identifying the Affected Table

Check which lookup table is hitting the limit:

-- Check the current AUTO_INCREMENT value for lookup tables
SELECT
    TABLE_NAME,
    COLUMN_TYPE,
    AUTO_INCREMENT
FROM
    INFORMATION_SCHEMA.TABLES
JOIN
    INFORMATION_SCHEMA.COLUMNS
USING (TABLE_SCHEMA, TABLE_NAME)
WHERE
    TABLE_SCHEMA = 'voipmonitor' AND
    (TABLE_NAME LIKE 'cdr_sip%' OR TABLE_NAME LIKE 'cdr_reason%') AND
    COLUMN_KEY = 'PRI' AND
    EXTRA LIKE '%auto_increment%'
ORDER BY AUTO_INCREMENT DESC;

Look for AUTO_INCREMENT values approaching or exceeding 16,000,000 in tables using `MEDIUMINT`.

Solution: Prevent New Unique Entries

The most effective solution is to configure VoIPmonitor to stop storing or normalize the unique SIP response text that is causing the rapid growth of the lookup table.

Option 1: Disable SIP Response Text Storage

Edit `/etc/voipmonitor.conf` on the sniffer to disable storing SIP response reason text:

# Disable storing SIP response reason strings in lookup tables
cdr_reason_string_enable = no

This prevents the system from creating new unique entries for SIP response reason strings. Restart the sniffer:

systemctl restart voipmonitor

Option 2: Normalize Response Text

If you need to keep some response text but reduce the number of unique entries, enable normalization in `/etc/voipmonitor.conf`:

# Normalize SIP response text to reduce unique entries
cdr_reason_normalisation = yes
cdr_sip_response_normalisation = yes
cdr_ua_normalisation = yes

Normalization transforms similar response strings into a single canonical form, significantly reducing the number of unique rows created. Include all three normalization options for maximum effectiveness.

Option 3: Clear Queued SQL Queries

If error 1062 persists after applying the configuration changes and restarting the service, there may be a large backlog of failed SQL queries queued in the spool directory. Clearing this queue can eliminate the persistent 1062 errors that are caused by previously buffered failed inserts.

⚠️ Warning: This step will DELETE all buffered CDRs in the queue. These CDRs will be permanently lost.

To clear the SQL queue:

# 1. Stop the VoIPmonitor service
systemctl stop voipmonitor

# 2. Remove the queued query files (qoq-* files)
# The default spool directory is /var/spool/voipmonitor
rm -f /var/spool/voipmonitor/qoq-*

# 3. Verify the files are removed
ls /var/spool/voipmonitor/qoq-*

# 4. Restart the service
systemctl start voipmonitor

# 5. Check that service is running
systemctl status voipmonitor

After restarting, the service should no longer attempt to re-insert the previously failed 1062 queries from the queue. Monitor the logs to confirm the error has stopped.

Option 4: Clean Existing Data (Immediate Fix)

The lookup table has reached its MEDIUMINT limit, preventing new CDRs from being stored. Truncating the table clears it and resets the auto-increment counter to 1, allowing CDRs to be written immediately.

⚠️ Warning: TRUNCATE permanently deletes all data. This will remove the exact SIP response text display in the GUI for historical CDRs, but will not affect the main CDR records or call data. Only do this if you are certain you no longer need the original response text.

-- Clear the cdr_reason table (adjust table name as needed based on error message)
TRUNCATE TABLE cdr_reason;

Verification

After applying the fix:

1. Check that CDRs are being stored again by monitoring the sniffer logs 2. Verify the lookup table AUTO_INCREMENT is no longer increasing rapidly:

SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'cdr_sip_response' AND TABLE_SCHEMA = 'voipmonitor';

3. Monitor the error logs to confirm the 1062 error has stopped appearing

Important Note: NOT a Database Schema Issue

This error is typically NOT solved by changing the database schema (e.g., migrating to BIGINT). The root cause is storing too many unique SIP response strings, which will continue to grow regardless of the ID column size. The correct solution is to configure VoIPmonitor to stop creating these unique entries via the `cdr_reason_string_enable` configuration option.

⚠️ Warning: Do NOT confuse this with the unrelated cdr table integer overflow problem. The main cdr table may encounter limits around 4 billion rows (32-bit INT), which is addressed in the Upgrade_to_bigint guide. Lookup table issues at 16.7 million (MEDIUMINT) are solved by configuration, not schema migration.

MySQL SUPER Privilege Required for Global Operations

If the VoIPmonitor sensor service fails to start during database initialization with errors indicating insufficient privileges for "global operations" or "table repairs," this indicates that the MySQL user specified in the VoIPmonitor configuration lacks the SUPER privilege (or SYSTEM_VARIABLES_ADMIN for newer MySQL 8.0 versions).

Symptoms

• Sensor startup errors such as "ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s)"
• Errors mentioning "global operations" or "table repairs" when the sensor attempts to initialize or repair database tables
• The sensor service starts but encounters database-related errors during regular operations

Root Cause

The SUPER privilege (or SYSTEM_VARIABLES_ADMIN in MySQL 8.0) is required for certain database operations that VoIPmonitor performs during startup and normal operation, including:

• Executing global variable changes
• Performing table repairs and optimizations
• Managing stored routines in some configurations

This is a GLOBAL-level privilege that must be granted with the *.* scope, not database-specific grants.

Solution: Grant SUPER Privilege

Log in to your MySQL server as an administrator and grant the SUPER privilege to the user specified in your VoIPmonitor configuration file (/etc/voipmonitor/voipmonitor.conf or /etc/voipmonitor.conf).

For Older MySQL Versions (5.7 and below) or MariaDB

# Log in to MySQL as root
mysql -u root -p

-- Grant SUPER privilege on all databases to the VoIPmonitor user
-- Replace 'voipmonitor_user' and '10.0.0.0/8' with your actual user and network
GRANT SUPER ON *.* TO 'voipmonitor_user'@'10.0.0.0/8';

-- Apply the changes
FLUSH PRIVILEGES;

-- Verify the grant
SHOW GRANTS FOR 'voipmonitor_user'@'10.0.0.0/8';

For MySQL 8.0 and Newer

MySQL 8.0 has split the SUPER privilege into more granular privileges. Use SYSTEM_VARIABLES_ADMIN instead:

-- Grant SYSTEM_VARIABLES_ADMIN privilege (modern equivalent of SUPER)
GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'voipmonitor_user'@'10.0.0.0/8';

FLUSH PRIVILEGES;

ℹ️ Note: You may also need to grant additional privileges depending on your MySQL version and specific operations:

• REPLICATION_CLIENT - For replication status checks
• PROCESS - For monitoring process list

Consult your MySQL documentation for the exact privileges required.

Verifying the User Configuration

Check your VoIPmonitor configuration file to confirm the database user being used:

# View database configuration settings
cat /etc/voipmonitor/voipmonitor.conf | grep mysql

Look for:

• mysqluser - The MySQL username (e.g., voip_mon_rw)
• mysqlhost - The database server address (affects the host in GRANT)

Restart the Sensor Service

After granting the SUPER privilege, restart the VoIPmonitor sensor service:

systemctl restart voipmonitor

Verification

Monitor the sensor startup logs to ensure the database initializes successfully without permission errors:

# Check for startup errors
journalctl -u voipmonitor -n 50

# Or monitor in real-time
journalctl -u voipmonitor -f

You should see successful database connection and table initialization messages, without "access denied" or "command denied" errors.

Important: Database-Specific Grants vs. Global Grants

The SUPER privilege must be granted with the global *.* scope because it applies to server-level operations, not just database-level operations. While database-specific grants like ALL PRIVILEGES ON voipmonitor.* are sufficient for standard CRUD operations (SELECT, INSERT, UPDATE, DELETE), they do NOT include the SUPER privilege required for global operations and table repairs.

⚠️ Warning: For MySQL 8.0+, SUPER has been deprecated and split into more granular privileges. Use SYSTEM_VARIABLES_ADMIN instead of SUPER when you see deprecation warnings.

Row Size Too Large - cdr_stat_values Corruption

If calls sent via HEP (or any calls) are not appearing in the GUI in real-time and only appear after restarting the VoIPmonitor service, this may indicate database corruption in the `cdr_stat_values` table.

Symptoms

• Calls (including HEP calls) are visible in real-time after sensor restart but stop appearing until next restart
• VoIPmonitor service logs show partition creation errors
• MySQL errors mentioning "Row size too large" on the `cdr_stat_values` table
• The `cdr_stat_values` table structure is corrupted

Root Cause

The `cdr_stat_values` table can become corrupted due to database structure issues, particularly in partition creation operations. When this table is corrupted, the VoIPmonitor service cannot properly store CDR statistics, causing calls to not appear in the GUI until the service is restarted (which forces a temporary work-around).

Diagnosis: Check Service Logs

Examine the VoIPmonitor service logs for database-related errors:

journalctl -u voipmonitor -n 200 | grep -i "partition\|cdr_stat_values\|row size"
tail -f /var/log/voipmonitor.log | grep -i "partition"

Look for:

• Errors during partition creation (e.g., "cannot create partition", "partition creation failed")
• "Row size too large" errors on `cdr_stat_values` table
• Database lock or deadlock errors during partition operations

Solution: Drop and Recreate the VoIPmonitor Database

The most effective solution is to drop and recreate the entire VoIPmonitor database. This will restore the correct table structure and allow calls to be processed and displayed correctly.

⚠️ Warning: DATA LOSS WARNING: Dropping the database will delete all CDR data, statistics, and stored records. Only proceed if you are certain you can afford to lose this data or have a recent backup.

Step 1: Stop VoIPmonitor Service

systemctl stop voipmonitor

Step 2: Save Table Structure for Future Analysis (Preventative)

Before recreating the database, save the table structure of the corrupted table for future analysis:

# Save the cdr_stat_values table structure only
mysqldump -u root -p -d voipmonitor cdr_stat_values > cdr_stat_values_structure_$(date +%Y%m%d).sql

The -d flag exports only the schema (structure) without the data, which is useful for diagnosing root causes if the issue recurs.

Step 3: Drop and Recreate the Database

-- Connect to MySQL
mysql -u root -p

-- Drop the corrupted database
DROP DATABASE voipmonitor;

-- Recreate a fresh database
CREATE DATABASE voipmonitor;

Step 4: Start VoIPmonitor to Initialize

systemctl start voipmonitor

The VoIPmonitor service will automatically create all required tables (including `cdr_stat_values`) with the correct structure.

ℹ️ Note: Service startup may take longer than usual as it initializes all partitions. Monitor the logs to confirm successful initialization.

Step 5: Verify Fix

# Check that VoIPmonitor started successfully
systemctl status voipmonitor

# Make a test call and verify it appears in the GUI in real-time
# Check for the call in the CDR list or Active Calls view

Calls should now appear in the GUI in real-time without requiring a service restart.

Prevention

To prevent this issue from recurring:

• Monitor database logs for early warning signs of partition creation issues
• Ensure MySQL/MariaDB configuration is optimized for VoIPmonitor (see Scaling)
• Maintain regular backups using mysqldump to recover data if database recreation is needed
• Keep MySQL/MariaDB versions up to date

Troubleshooting Recurring Issues

If the issue recurs after recreating the database:

1. Examine the saved table structure from Step 2:

   cat cdr_stat_values_structure_20250101.sql

2. Compare with a fresh VoIPmonitor installation to identify structural differences

3. Report the issue to VoIPmonitor support with:

  * The saved table structure file
  * Service log excerpts showing partition errors
  * MySQL/MariaDB version information

Unknown Column Error (Sensor Schema Mismatch)

If sensors are failing to send CDRs to a master database with errors like "send store query error" and "Unknown column 'from_time' in 'field list'", and you see an accumulation of unprocessed qoq-* files in the spool directory, this indicates a database schema version mismatch.

Symptoms

• Log errors: send store query error
• Database errors: Unknown column 'column_name' in 'field list'
• Accumulation of qoq-* files in the spool directory (typically /var/spool/voipmonitor)
• SQLf queue growing because database writes are failing

Root Cause

The error indicates that the **sensor software version is newer than the database schema**. The sensor is attempting to use columns, tables, or data types that do not exist in your current database structure. When the database write fails, the sensor buffers the data locally into qoq (Queue Offline Queue) files to prevent data loss.

This commonly occurs after:

• Upgrading the sensor binary without updating the database schema
• Adding a new sensor with a newer version to an existing database
• Running schema check/upgrade tools on multiple sensors simultaneously

Solution 1: Use Check MySQL Schema Tool (Recommended)

The quickest way to resolve this is to use the built-in database schema upgrade tool.

1. Log in to the **VoIPmonitor Web GUI** 2. Navigate to **Tools → System Status** 3. Locate the **Check MySQL Schema** section 4. The tool will report missing columns or tables. It will specifically identify the schema issues 5. Check the box next to the required changes (or "Select All") 6. Click **Start Upgrade / Run SQL**

ℹ️ Note: This process may lock tables while running. On large databases, this can take time. Consider performing during a maintenance window.

Solution 2: Manual Schema Update (Fallback)

If the Check MySQL Schema tool is unavailable or fails, you can manually recreate the table schema.

⚠️ Warning: CRITICAL: Backup your data first! Before any schema changes, create a backup using mysqldump.

-- Backup the affected table
mysqldump -u root -p voipmonitor cdr > cdr_backup_$(date +%Y%m%d).sql

Then either:

Option A: Transportable Tablespaces (Preserves Data)

This method preserves your existing data while recreating the table structure. See Recovering Corrupted Database Tables for the complete transportable tablespaces procedure.

Option B: Drop and Recreate Table (Destructive)

Use this ONLY if you have a recent backup and can afford to lose data:

-- 1. Stop VoIPmonitor service
-- systemctl stop voipmonitor

-- 2. Get the fresh CREATE TABLE statement from:
--           - A fresh VoIPmonitor installation of the same version, OR
--           - The GUI: Tools → System Status → Check MySQL Schema, OR
--           - The sniffer command line tool

-- 3. Drop the problematic table
USE voipmonitor;
DROP TABLE IF EXISTS cdr;  -- Replace with actual table name

-- 4. Recreate with correct schema for your DB version (MariaDB or MySQL)
-- Paste the CREATE TABLE statement obtained in step 2

⚠️ Warning: This will delete all data in the table. Only use if you have a recent backup or data loss is acceptable.

Prevention: Centralize Schema Management

To prevent schema conflicts in distributed setups with multiple sensors:

⚠️ Warning: In distributed deployments, only ONE instance should manage database structure and partitions.

Edit /etc/voipmonitor.conf on **all sensor probes**:

# Disable partition operations on sensors
disable_partition_operations = yes

On the **central server instance**, leave this setting disabled (or set to no) so it can manage schema and partitions. Designate a single instance as the "Master" for all database structure changes.

This prevents race conditions where multiple sensors try to alter the table structure simultaneously, causing "Unknown column" or "Duplicate column" errors.

ℹ️ Note:

Queue Drainage After Fix

Once the schema correction is complete:

1. Restart the VoIPmonitor sensor service to apply changes:

systemctl restart voipmonitor

2. Monitor the logs. The sensor should now successfully connect to the database

3. **Automatic Queue Processing:** The sensor will automatically detect and process the qoq-* files in the spool directory once the database connection is stable and the schema matches. Monitor the sensor logs or GUI → Settings → Sensors to watch the **SQLq** (SQL Queue) metric decrease as the files are drained

See Also

• Recovering Corrupted Database Tables - For other types of table corruption
• Database Structure - Reference for VoIPmonitor table schemas
• Scaling - MySQL/MariaDB performance tuning for high-volume deployments

See Also

• Sniffer_configuration - Complete configuration reference for voipmonitor.conf
• Scaling - Performance tuning and optimization
• Hardware - Hardware sizing guidelines
• Upgrade_to_bigint - Migrating CDR table to BIGINT (for main cdr table INT overflow)
• GUI_troubleshooting - GUI-specific issues
• Sniffer_troubleshooting - Sensor/sniffer issues (no calls, network problems)

AI Summary for RAG

Summary: VoIPmonitor database troubleshooting guide covering SQL queue issues, CDR delays, MySQL tuning, and database errors. SQL QUEUE: Active Calls shows real-time sniffer data while CDR view shows DB records. Delays occur when DB cannot keep up. Monitor SQLq/SQLf in Settings->Sensors->Status. QUICK CDR: Use quick_save_cdr=yes (3s) or quick (1s) in voipmonitor.conf (default 10s, increases CPU/IO). QOQ FILES: SQL queries buffered in qoq* files in /var/spool/voipmonitor. Clear backlog: wait (preferred) or emergency delete qoq* files (loses CDRs). OOM PREVENTION: Keep query_cache=yes (default) - never set to no. THREADS: Increase mysqlstore_max_threads_cdr/sip_msg for high traffic. Auto-scales to 99 threads when queue >1000. Note: mysql_enable_set_id=yes limits to single sensor. MYSQL TUNING: innodb_buffer_pool_size=50-70% RAM, innodb_flush_log_at_trx_commit=2. HARDWARE: If CPU at 100% after tuning, upgrade needed. SSD MIGRATION: Stop MySQL, rsync to SSD, update datadir, start. No CDR loss. ERROR 1062 (16777215): Lookup table (cdr_sip_response, cdr_reason) hit MEDIUMINT limit. FIX: cdr_reason_string_enable=no OR enable all normalization options. IMMEDIATE: TRUNCATE affected table. NOT a schema issue - different from cdr table INT overflow (see Upgrade_to_bigint). SUPER PRIVILEGE: GRANT SUPER ON *.* (MySQL 5.7/MariaDB) or SYSTEM_VARIABLES_ADMIN (MySQL 8.0+). Required for global operations. CDR_STAT_VALUES CORRUPTION: Calls not appearing in GUI until restart indicates partition errors. FIX: DROP/recreate database (loses data). Check journalctl for "row size too large" errors.

UNKNOWN COLUMN ERROR: Sensor version > database schema causes "Unknown column 'from_time'" and "send store query error" with qoq files accumulating. SOLUTION 1 (RECOMMENDED): GUI -> Tools -> System Status -> Check MySQL Schema -> Start Upgrade. SOLUTION 2 (FALLBACK): mysqldump backup first, then Option A transportable tablespaces (preserve data, see Recovering_corrupted_database_tables) OR Option B drop/recreate table (destructive, needs backup). Get fresh CREATE TABLE from fresh installation or GUI tool. PREVENTION: On distributed setups, set disable_partition_operations=yes on all sensors, leave disabled on central server for schema management. Queue drains automatically after fix - monitor SQLq metric decreasing. DIFFERENT FROM: table corruption (physical .par file issues) or lookup table error 1062.

Keywords: SQL queue, SQLq, SQLf, database delay, CDR delay, active calls, CDR view, mysqlstore_max_threads_cdr, mysqlstore_max_threads_sip_msg, quick_save_cdr, query_cache, qoq files, queue files, spool directory, database backlog, innodb_buffer_pool_size, innodb_flush_log_at_trx_commit, MySQL tuning, MariaDB tuning, hardware upgrade, CPU 100%, AMD EPYC, Intel Xeon, NVMe SSD, datadir migration, MySQL to SSD, rsync MySQL, AppArmor MySQL, symbolic link database, OOM, out of memory, 1062 duplicate entry, 16777215, lookup table, MEDIUMINT limit, cdr_sip_response, cdr_reason, cdr_reason_string_enable, auto-increment limit, normalization, cdr_reason_normalisation, cdr_sip_response_normalisation, cdr_ua_normalisation, TRUNCATE, database error, mysql_enable_set_id, mysql_enable_new_store, central writer, single sensor, auto-scaling, automatic thread scaling, auto scale, 99 threads, 1000 queue, traffic spikes, thread pool scaling, SUPER privilege,SUPER privilege error, access denied, command denied, global operations, table repairs, GRANT SUPER ON *.*, SYSTEM_VARIABLES_ADMIN, MySQL 8.0 privilege, 1227 access denied, mysql privileges, global grant, database vs global grants, row size too large, cdr_stat_values corruption, partition creation error, HEP calls not appearing real time, calls not appearing until restart, drop recreate database, mysqldump structure save, unknown column, schema mismatch, sensor version, database version, schema version, from_time error, send store query error, check mysql schema, mysql schema upgrade, disable_partition_operations, central server, master sensor, schema management, distributed database

Key Questions:

• What causes unknown column error in VoIPmonitor? (Sensor software version newer than database schema - sensor uses columns that do not exist)
• How do I fix unknown column 'from_time' in field list error? (Use GUI Tools->System Status->Check MySQL Schema tool to upgrade, OR manual mysqldump backup and recreate table)
• What is send store query error in VoIPmonitor? (Database write failure due to schema mismatch - sensor cannot insert CDRs with new columns)
• Why are qoq files accumulating in spool directory? (Database schema mismatch - sensor buffering failed writes, will auto-drain after schema fix)
• Should I backup database before schema upgrade? (YES - always mysqldump before any schema changes)
• How do I get fresh CREATE TABLE statement for schema update? (From fresh installation of same version, or GUI Check MySQL Schema tool shows current schema)
• What is disable_partition_operations for? (Set on all sensors to prevent schema conflicts - only central server manages partitions in distributed setups)
• How does qoq queue drain after schema fix? ( Automatically - sensor detects and processes files once DB connection stable and schema matches, monitor SQLq metric decreasing)
• Does VoIPmonitor auto-scale database threads? (Yes - auto-scales up to 99 threads when SQL queue exceeds 1,000 items, applies to CDR and sip_msg queues)
• How do I reduce CDR delay in VoIPmonitor? (Set quick_save_cdr=yes or quick_save_cdr=quick in voipmonitor.conf)
• What are qoq files in VoIPmonitor? (SQL queue files in spool directory buffering database operations)
• How do I clear SQL queue backlog? (Stop service, rm -f /var/spool/voipmonitor/qoq*, start service - loses queued CDRs)
• Why is SQL queue growing? (Database cannot keep up - check MySQL performance, increase threads, upgrade hardware)
• How do I increase database threads? (Set mysqlstore_max_threads_cdr=8 and mysqlstore_max_threads_sip_msg=8 in voipmonitor.conf)
• What causes error 1062 - Duplicate entry '16777215' for key 'PRIMARY'? (Lookup table hitting MEDIUMINT limit due to too many unique SIP response strings)
• How do I fix error 1062 in cdr_sip_response or cdr_reason tables? (Set cdr_reason_string_enable=no in voipmonitor.conf, restart sniffer)
• How do I stop 1062 errors immediately? (TRUNCATE the affected lookup table to reset auto-increment counter)
• How do I prevent error 1062 from recurring? (Set cdr_reason_string_enable=no, or enable ALL THREE normalization options)
• Should I migrate lookup table to BIGINT to fix error 1062? (No, the root cause is storing too many unique strings - use configuration fix)
• Why does error 1062 persist after configuration change? (Failed queries remain queued in qoq-* files - clear them)
• How do I migrate MySQL to SSD? (Stop MySQL, rsync data to SSD, update datadir in my.cnf, start MySQL)
• When should I upgrade database hardware? (When CPU is consistently at 100% after all tuning optimizations)
• What is mysql_enable_set_id limitation? (Limits setup to single sensor writing to database - do not use with multiple sensors)
• How do I optimize MySQL for VoIPmonitor? (Set innodb_buffer_pool_size=50-70% RAM, innodb_flush_log_at_trx_commit=2)
• What causes "Access denied; you need SUPER privilege(s)" error? (MySQL user lacks SUPER privilege for global operations and table repairs)
• How do I fix SUPER privilege error in MySQL 5.7/MariaDB? (GRANT SUPER ON *.* TO 'user'@'host'; FLUSH PRIVILEGES;)
• How do I fix SUPER privilege error in MySQL 8.0? (Use SYSTEM_VARIABLES_ADMIN instead of SUPER: GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'user'@'host';)
• Do ALL PRIVILEGES ON database.* grant SUPER privilege? (No - database-specific grants do NOT include SUPER privilege, must use global *.* grant)
• Why does VoIPmonitor need SUPER privilege? (For global operations, table repairs, optimizations, and stored routines during sensor startup)
• Why are HEP calls not appearing in GUI real-time? (May be cdr_stat_values table corruption with "Row size too large" error - check journalctl for partition errors)
• What causes calls to not appear until sensor restart? (Database corruption in cdr_stat_values table prevents CDR storage - only workaround is restarting service which works temporarily)
• How do I fix cdr_stat_values corruption? (Stop service, save table structure with mysqldump -d, DROP DATABASE voipmonitor, CREATE DATABASE, restart service - DELETES ALL CDR DATA)
• How do I diagnose cdr_stat_values "Row size too large" errors? (Check journalctl -u voipmonitor for "partition cdr_stat_values row size" errors)
• How do I save table structure for database corruption analysis? (Use mysqldump -d voipmonitor cdr_stat_values > file.sql to save schema without data)