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.

Long-Running Queries Blocking Operations

If long-running dashboard queries (GROUP BY aggregations on millions of CDR rows) are blocking partition maintenance operations (creation/dropping of partitions):

⚠️ Warning: AuroraDB does not support the MySQL KILL command correctly and is not officially supported. Consider migrating to standard MySQL or MariaDB for production deployments.

Solution Steps

1. Implement query timeout via PHP script

Create a PHP script to automatically kill queries running longer than 3600 seconds (1 hour):

# Example script: add to cron to run periodically
# Queries running > 3600 seconds will be terminated to prevent lock issues

2. Check network latency between GUI and database server

High latency can exacerbate query blocking issues. Ensure minimal network delay.

3. Set MySQL max_execution_time

Add to MySQL/MariaDB configuration:

# /etc/mysql/my.cnf or /etc/my.cnf.d/server.cnf
[mysqld]
max_execution_time = 3600000  # 1 hour in milliseconds

4. Force internal MySQL grouping (fallback)

If performance issues persist:

# Delete charts binaries to force internal MySQL grouping
rm -f /usr/local/sbin/charts*
systemctl restart voipmonitor

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

Automatic Schema Upgrades and Production Safety

VoIPmonitor includes a built-in safety mechanism to prevent unexpected database schema modifications on production databases.

Safety Threshold: Automatic schema modifications (ALTER TABLE) only occur if the database contains fewer than 1000 CDRs.

Database Size	Behavior
< 1000 CDRs	Automatic schema upgrades applied on sniffer startup
> 1000 CDRs'	ALTER queries logged to syslog/messages/journalctl - manual execution required

For production databases (>1000 CDRs), when a new sniffer version detects required schema changes:

1. Review logged ALTER queries: journalctl -u voipmonitor | grep ALTER or grep ALTER /var/log/syslog
2. Execute manually during low-traffic period (e.g., overnight) to prevent table locking
3. Restart sniffer after schema changes complete

⚠️ Warning: Never rely on disable_dbupgradecheck for production safety - it's unnecessary because the 1000 CDR threshold already protects large databases from automatic modifications.

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)