Database troubleshooting

From VoIPmonitor.org


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

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.

Template:See Also

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

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)
Retrieved from "https://www.voipmonitor.org/doc/index.php?title=Database_troubleshooting&oldid=10333"