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

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

AI Summary for RAG

Summary: VoIPmonitor database troubleshooting guide. SERVICE: If Active Calls visible but CDRs missing after restart, start voipmonitor service. SQL QUEUE: SQLq/SQLf in Settings→Sensors→Status shows DB queue size. Growing = DB cannot keep up. QUICK CDR: quick_save_cdr=yes (3s) or quick (1s) reduces CDR delay. QOQ FILES: SQL buffered to qoq* files in /var/spool/voipmonitor when DB lags. Emergency clear: rm qoq* (loses data). THREADS: mysqlstore_max_threads_cdr=8, auto-scales to 99 when queue>1000. 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. ERROR 1062: Lookup table (cdr_sip_response/cdr_reason) hit MEDIUMINT 16777215 limit. Fix: cdr_reason_string_enable=no or enable normalization. NOT a schema issue. SUPER PRIVILEGE: GRANT SUPER ON *.* (MySQL 5.7) or SYSTEM_VARIABLES_ADMIN (MySQL 8.0+). SCHEMA MISMATCH: Unknown column error = sensor newer than DB schema. Fix via GUI Check MySQL Schema tool. TIMEZONE: If CDRs disappear after time change, check Settings→System Configuration→National timezone settings.

Keywords: SQLq, SQLf, database delay, CDR delay, quick_save_cdr, query_cache, qoq files, mysqlstore_max_threads_cdr, innodb_buffer_pool_size, error 1062, 16777215, lookup table limit, cdr_reason_string_enable, SUPER privilege, SYSTEM_VARIABLES_ADMIN, unknown column, schema mismatch, disable_partition_operations, timezone, MySQL tuning, SSD migration, hardware upgrade

Key Questions:

• Why are CDRs not appearing but Active Calls work? (Service not running or database lag)
• What is SQLq/SQLf? (Queue size before CDRs pushed to database)
• How to reduce CDR delay? (quick_save_cdr=yes or quick)
• What are qoq files? (SQL queue files buffering DB operations)
• How to fix error 1062 duplicate entry 16777215? (cdr_reason_string_enable=no, NOT schema migration)
• How to fix SUPER privilege error? (GRANT SUPER ON *.* or SYSTEM_VARIABLES_ADMIN)
• How to fix unknown column error? (GUI Check MySQL Schema tool, prevent with disable_partition_operations on sensors)
• Why do CDRs disappear after time change? (Timezone mismatch in GUI settings)
• How to tune MySQL for VoIPmonitor? (innodb_buffer_pool_size=50-70% RAM, innodb_flush_log_at_trx_commit=2)
• When is hardware upgrade needed? (CPU at 100% after all tuning optimizations)

Installing New Sensor with Existing Database

When installing a new VoIPmonitor sensor and connecting it to an existing production database, there is a built-in safety mechanism to prevent automatic schema modifications that could cause downtime or data corruption.

Symptom

Concern that a new installation will automatically alter the database schema on an existing production database.

How the Safety Mechanism Works:

Database Size	Behavior
< 1000 CDRs	Sensor will automatically execute required `ALTER TABLE` queries on startup
≥ 1000 CDRs	Sensor will log required `ALTER` queries to syslog/messages/journalctl but will NOT execute them automatically

The 1000 CDR threshold prevents accidental schema modifications on large, production databases that could cause extended table locking and service disruption.

Procedure for Large Databases (≥ 1000 CDRs):

# 1. Restart the new sensor to generate ALTER queries
systemctl restart voipmonitor

# 2. View the logged ALTER queries (choose one method)
journalctl -u voipmonitor | grep -i ALTER
grep -i voipmonitor /var/log/syslog | grep -i ALTER
grep -i voipmonitor /var/log/messages | grep -i ALTER

# 3. Execute the queries manually during a maintenance window (overnight/low-traffic)
mysql -u root -p voipmonitor
# Paste ALTER commands from the log

⚠️ Warning: Monitor for table locking: `ALTER TABLE` queries can lock tables for extended periods on large datasets. Always execute during planned maintenance windows with minimal traffic.

Multiple Sensors with Same Database:

If running multiple sensors simultaneously writing to the same database, ensure each sensor has a unique `id_sensor` value:

# On Sensor 1: /etc/voipmonitor.conf
id_sensor = 1

# On Sensor 2: /etc/voipmonitor.conf
id_sensor = 2

# On Sensor 3: /etc/voipmonitor.conf
id_sensor = 3

ℹ️ Note: The `id_sensor` parameter uniquely identifies the source of CDRs. Duplicate values will cause data confusion.

Hardware-Dependent Settings:

When adding new sensors to an existing deployment, review and adjust hardware-specific settings on each sensor:

Parameter	Description	Notes
interface	Network interface to monitor	Must match local interface names
max_buffer_mem	Maximum RAM for packet buffer	Adjust based on available RAM and traffic volume
spooldir	PCAP spool directory	Ensure sufficient disk space, may be shared via NFS
maxpoolsize	Maximum PCAP storage size	Per-sensor or shared storage considerations