Database troubleshooting

This page covers VoIPmonitor database troubleshooting: SQL queue issues, CDR delays, MySQL tuning, and database errors.

Service Not Running

If Active Calls are visible but CDRs are missing after restart, the sniffer service likely didn't start.

# Check status
systemctl status voipmonitor

# Start if not running
systemctl start voipmonitor

# Enable auto-start on boot
systemctl enable voipmonitor

ℹ️ Note: Active Calls come from sensor memory (real-time), while CDRs require the running service to write to database.

SQL Queue and CDR Delays

Understanding SQLq/SQLf

The SQLq/SQLf values in Settings → Sensors → Status show the queue size before CDRs are pushed to database.

Metric	Meaning
Decreasing	Database catching up, CDRs will appear soon
Stuck/Growing	Database cannot keep up, needs tuning
Near zero	All queued CDRs processed

Monitor via logs:

journalctl -u voipmonitor -f | grep SQLf

Quick CDR Visibility

Reduce delay between call end and CDR appearance in /etc/voipmonitor.conf:

Setting	Delay	Impact
quick_save_cdr = no	10s (default)	Lowest load
quick_save_cdr = yes	3s	Moderate CPU/IO increase
quick_save_cdr = quick	1s	High CPU/IO increase

Disk-Based Query Queue (OOM Prevention)

# /etc/voipmonitor.conf - KEEP THIS ENABLED
query_cache = yes

⚠️ Warning: Never set query_cache = no — if database becomes unreachable and memory fills, OOM killer terminates VoIPmonitor and all queued CDRs are lost.

qoq* Files Backlog

When database lags, SQL queries buffer to qoq* files in /var/spool/voipmonitor.

Solution 1 (Preferred): Wait for automatic processing — monitor SQLf decreasing.

Solution 2 (Emergency): Delete backlog (loses queued CDRs):

systemctl stop voipmonitor
rm -f /var/spool/voipmonitor/qoq*
systemctl start voipmonitor

Increasing Database Threads

# /etc/voipmonitor.conf
mysqlstore_max_threads_cdr = 8
mysqlstore_max_threads_sip_msg = 8

ℹ️ Note: Auto-scaling: When queue exceeds 1,000 items, threads automatically increase up to 99. However, if database waits for storage I/O, more threads won't help.

⚠️ Warning: mysql_enable_set_id = yes limits setup to single sensor writing to database. Do not use with multiple sensors.

Timezone Issues

If CDRs stop appearing after time change but Active Calls work:

1. Check Settings → Sensors → Status: If SQLq/SQLf NOT growing and "last CDR stored" is current, it's a timezone issue 2. Verify Settings → System Configuration → National:

  * Timezone = GUI host timezone (for reports/alerts)
  * Sensors Timezone = CDR timestamp display timezone

Override in sensor config:

# /etc/voipmonitor.conf
timezone = /usr/share/zoneinfo/Europe/London
# OR for multi-timezone deployments:
utc = yes

MySQL/MariaDB Tuning

Essential Configuration

# /etc/mysql/my.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld]
# 50-70% of RAM on dedicated DB server, 30-50% if shared with VoIPmonitor
innodb_buffer_pool_size = 8G

# Faster writes (may lose up to 1s of data on crash)
innodb_flush_log_at_trx_commit = 2

# Disable slow query log if causing high memory (>90%)
slow_query_log = 0

Restart services:

systemctl restart mysql && systemctl restart voipmonitor

For detailed tuning, see Scaling - MySQL Configuration.

Hardware Upgrade Signs

If after all tuning optimizations:

• CPU consistently at 100%
• SQLq keeps growing
• Large delay between "Last CDR in queue" and "Last CDR in database"

→ Hardware upgrade required (modern CPU, NVMe SSD, more RAM). See Hardware for sizing.

MySQL Data Migration to SSD

# 1. Stop MySQL
systemctl stop mysql

# 2. Copy data (preserves permissions)
rsync -avx /var/lib/mysql/ /mnt/fast_storage/mysql/

# 3. Update /etc/mysql/my.cnf
#    datadir = /mnt/fast_storage/mysql

# 4. Update AppArmor if applicable (Ubuntu/Debian)
# 5. Start MySQL
systemctl start mysql

ℹ️ Note: No CDR loss — VoIPmonitor queues CDRs during MySQL downtime.

Database Errors

Automatic Schema Upgrade Safety Mechanism

When installing a new sensor and connecting to an existing production database, VoIPmonitor includes a built-in safety mechanism to protect against automatic schema modifications that could cause table locks.

Database Size	Behavior	Action Required
New or < 1000 CDRs	Automatically creates/changes tables	None (safe, minimal data at risk)
1000+ CDRs (production)	Logs ALTER queries to syslog, does not auto-apply	Review and run manually during low-traffic period

For production databases (1000+ CDRs), required schema changes are logged but not automatically applied:

# Check for logged upgrade queries
journalctl -u voipmonitor | grep -i "alter\|upgrade"
grep -i "alter\|upgrade" /var/log/syslog
grep -i "alter\|upgrade" /var/log/messages

Apply logged ALTER statements manually during off-peak hours to prevent table locking:

mysql -u voipmonitor -p voipmonitor
-- Paste each logged SQL statement:
ALTER TABLE cdr ADD COLUMN new_column INT DEFAULT 0;

💡 Tip: Use GUI → Tools → System Status → Check MySQL Schema for automated schema updates when safe to run.

Error 1062 - Lookup Table Limit (16777215)

Symptom: 1062 - Duplicate entry '16777215' for key 'PRIMARY' on lookup tables (cdr_sip_response, cdr_reason).

Cause: MEDIUMINT limit reached due to too many unique SIP response strings.

Fix (choose one):

Option	Configuration	Notes
Disable storage	cdr_reason_string_enable = no	Stops creating new entries
Enable normalization	cdr_reason_normalisation = yes cdr_sip_response_normalisation = yes cdr_ua_normalisation = yes	Reduces unique entries
Immediate fix	TRUNCATE TABLE cdr_reason;	Resets counter, loses lookup data

After config change, clear queued failed queries:

systemctl stop voipmonitor
rm -f /var/spool/voipmonitor/qoq-*
systemctl start voipmonitor

⚠️ Warning: This is NOT a schema issue — do not migrate to BIGINT. Different from main cdr table overflow (see Upgrade_to_bigint).

SUPER Privilege Error

Symptom: ERROR 1227 (42000): Access denied; you need SUPER privilege(s)

Fix:

-- MySQL 5.7 / MariaDB
GRANT SUPER ON *.* TO 'voipmonitor_user'@'%';

-- MySQL 8.0+
GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'voipmonitor_user'@'%';

FLUSH PRIVILEGES;

ℹ️ Note: SUPER is a global privilege — database-specific grants (ALL ON voipmonitor.*) do NOT include it.

Unknown Column Error (Schema Mismatch)

Symptom: Unknown column 'from_time' in 'field list', qoq files accumulating.

Cause: Sensor version newer than database schema.

Fix:

1. GUI → Tools → System Status → Check MySQL Schema → Start Upgrade
2. If unavailable: backup with mysqldump, then recreate table (see Recovering_corrupted_database_tables)

Prevention (distributed setups):

# On ALL sensors (not central server):
disable_partition_operations = yes

Row Size Too Large (cdr_stat_values)

Symptom: Calls don't appear in GUI until service restart, "Row size too large" errors.

Fix (destructive):

systemctl stop voipmonitor

# Save structure for analysis
mysqldump -u root -p -d voipmonitor cdr_stat_values > cdr_stat_backup.sql

# Recreate database
mysql -u root -p -e "DROP DATABASE voipmonitor; CREATE DATABASE voipmonitor;"

systemctl start voipmonitor

⚠️ Warning: This deletes all CDR data. Only use if data loss is acceptable.

See Also

• Sniffer_configuration - Configuration parameters reference
• Scaling - Performance tuning
• Hardware - Hardware sizing
• Upgrade_to_bigint - CDR table INT overflow (4B rows)
• Recovering_corrupted_database_tables - Table corruption recovery
• Sniffer_troubleshooting - Network/capture issues