|
|
| (11 intermediate revisions by 2 users not shown) |
| Line 2: |
Line 2: |
| [[Category:Troubleshooting]] | | [[Category:Troubleshooting]] |
|
| |
|
| This page provides troubleshooting guidance for VoIPmonitor database-related issues including SQL queue problems, CDR delays, MySQL performance tuning, and database errors. | | This page covers VoIPmonitor database troubleshooting: SQL queue issues, CDR delays, MySQL tuning, and database errors. |
|
| |
|
| {| class="wikitable" style="width:100%; background:#f8f9fa; border:2px solid #00A7E3; margin-bottom:20px;"
| | <kroki lang="mermaid"> |
| |-
| | %%{init: {'flowchart': {'nodeSpacing': 15, 'rankSpacing': 30}}}%% |
| ! colspan="3" style="background:#00A7E3; color:white; font-size:1.2em; padding:10px;" | Quick Navigation - Database Troubleshooting
| | flowchart TB |
| |-
| | START[CDRs Missing?] --> Q1{Service running?} |
| ! style="width:33%; background:#e0f4fc; padding:8px; vertical-align:top;" | SQL Queue Issues
| | Q1 -->|No| FIX1[systemctl start voipmonitor] |
| ! style="width:33%; background:#fef3e2; padding:8px; vertical-align:top;" | Database Errors
| | Q1 -->|Yes| Q2{SQLq/SQLf growing?} |
| ! style="width:33%; background:#f1f5f9; padding:8px; vertical-align:top;" | Performance & Migration
| | Q2 -->|Yes| Q3{CPU at 100%?} |
| |-
| | Q2 -->|No| Q4[Check timezone settings] |
| | style="vertical-align:top; padding:10px;" |
| | Q3 -->|Yes| FIX2[Hardware upgrade needed] |
| '''CDR Visibility''' | | Q3 -->|No| FIX3[Tune MySQL config] |
| * [[#Active Calls Visible, CDRs Not Showing (Server Crash/Restart)|Service Not Running (Quick Fix)]]
| | </kroki> |
| * [[#Delay between active call and CDR view|CDR Delay Explained]]
| |
| * [[#Symptoms of Database Delays|Delay Symptoms]]
| |
| * [[#Quick CDR Visibility (Reduce Delay)|quick_save_cdr Option]]
| |
| * [[#CDRs Not Showing After Server Time Change|Time Change / Time Zone Issues]]
| |
| | |
| '''SQL Queue'''
| |
| * [[#SQLq/SQLf|SQLq/SQLf Metrics]]
| |
| * [[#Clearing File Queue Backlog (qoq* Files)|qoq Files Backlog]]
| |
| * [[#Enable Disk-Based Query Queue (Prevent OOM)|query_cache Option]]
| |
| | style="vertical-align:top; padding:10px;" | | |
| '''Error 1062'''
| |
| * [[#Troubleshooting: Database Error 1062 - Lookup Table Auto-Increment Limit|Error 1062 - 16777215]]
| |
| * [[#Identifying the Affected Table|Find Affected Table]]
| |
| * [[#Solution: Prevent New Unique Entries|Fix: cdr_reason_string_enable]]
| |
| | |
| '''Permission Errors'''
| |
| * [[#MySQL SUPER Privilege Required for Global Operations|SUPER Privilege Error]]
| |
| | |
| '''Related'''
| |
| * [[Upgrade_to_bigint|CDR Table INT Overflow]] (4B rows)
| |
| | style="vertical-align:top; padding:10px;" |
| |
| '''Tuning'''
| |
| * [[#More threads/connections to a db|Database Threads]]
| |
| * [[#MySQL/MariaDB Performance Tuning|MySQL Tuning]]
| |
| | |
| '''Hardware'''
| |
| * [[#When Configuration Tuning Is Not Enough: Hardware Upgrade Required|Hardware Upgrade Signs]]
| |
| * [[#Migrating MySQL Data to Faster Storage|MySQL to SSD Migration]]
| |
| |}
| |
|
| |
|
| = Active Calls Visible, CDRs Not Showing (Server Crash/Restart) = | | = Service Not Running = |
|
| |
|
| 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. | | If Active Calls are visible but CDRs are missing after restart, the sniffer service likely didn't start. |
| | |
| == 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:
| |
|
| |
|
| <syntaxhighlight lang="bash"> | | <syntaxhighlight lang="bash"> |
| | # Check status |
| systemctl status voipmonitor | | systemctl status voipmonitor |
| # or
| |
| journalctl -u voipmonitor -n 50
| |
| </syntaxhighlight>
| |
|
| |
|
| Look for:
| | # Start if not running |
| * <code>Active: active (running)</code> - Service is running correctly
| | systemctl start voipmonitor |
| * <code>Active: inactive (dead)</code>, <code>failed</code>, or <code>exited</code> - Service is not running
| |
|
| |
|
| === Step 2: Start the Service If Not Running ===
| | # Enable auto-start on boot |
| | | systemctl enable voipmonitor |
| If the service is inactive or failed, start it:
| |
| | |
| <syntaxhighlight lang="bash">
| |
| sudo systemctl start voipmonitor
| |
| </syntaxhighlight> | | </syntaxhighlight> |
|
| |
|
| Verify the service started successfully:
| | {{Note|Active Calls come from sensor memory (real-time), while CDRs require the running service to write to database.}} |
| | |
| <syntaxhighlight lang="bash">
| |
| sudo systemctl status voipmonitor
| |
| </syntaxhighlight>
| |
| | |
| You should see <code>Active: active (running)</code> 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 <code>quick_save_cdr</code> 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:
| |
| | |
| <syntaxhighlight lang="bash">
| |
| sudo systemctl enable voipmonitor
| |
| </syntaxhighlight>
| |
| | |
| Verify the service is enabled:
| |
| | |
| <syntaxhighlight lang="bash">
| |
| sudo systemctl is-enabled voipmonitor
| |
| </syntaxhighlight>
| |
| | |
| Expected output: <code>enabled</code>. If you see <code>disabled</code> or <code>static</code>, 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|SQL Queue and CDR Delays]] section below for database-specific troubleshooting.
| |
|
| |
|
| = SQL Queue and CDR Delays = | | = SQL Queue and CDR Delays = |
|
| |
|
| == Delay between active call and CDR view == | | == Understanding SQLq/SQLf == |
|
| |
|
| 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. | | The '''SQLq/SQLf''' values in '''Settings → Sensors → Status''' show the queue size before CDRs are pushed to database. |
|
| |
|
| == Symptoms of Database Delays == | | {| class="wikitable" |
| | | |- |
| When the database cannot keep up with CDR insertion rates, you may experience:
| | ! Metric !! Meaning |
| | | |- |
| * '''Slow CDR appearance in GUI''' - New calls take minutes to appear after they end
| | | '''Decreasing''' || Database catching up, CDRs will appear soon |
| * '''"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
| | | '''Stuck/Growing''' || Database cannot keep up, needs tuning |
| | | |- |
| To diagnose cron-related delays specifically, see [[Alerts#Crontab_Log_is_Too_Old_Warning_-_Database_Performance_Issues|"Crontab log is too old" troubleshooting]].
| | | '''Near zero''' || All queued CDRs processed |
| | | |} |
| == 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:
| |
|
| |
|
| | Monitor via logs: |
| <syntaxhighlight lang="bash"> | | <syntaxhighlight lang="bash"> |
| # 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 | | journalctl -u voipmonitor -f | grep SQLf |
| </syntaxhighlight> | | </syntaxhighlight> |
|
| |
|
| The SQLf[cdr: ...] metric appears in the sensor's real-time status output. Monitor this value:
| | == Quick CDR Visibility == |
|
| |
|
| * '''Decreasing number''' - The sensor is processing the backlog and CDRs will appear in the GUI soon
| | Reduce delay between call end and CDR appearance in <code>/etc/voipmonitor.conf</code>: |
| * '''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).
| | {| class="wikitable" |
| | |- |
| | ! Setting !! Delay !! Impact |
| | |- |
| | | <code>quick_save_cdr = no</code> || 10s (default) || Lowest load |
| | |- |
| | | <code>quick_save_cdr = yes</code> || 3s || Moderate CPU/IO increase |
| | |- |
| | | <code>quick_save_cdr = quick</code> || 1s || High CPU/IO increase |
| | |} |
|
| |
|
| == Make sure that db config is not causing io overhead == | | == Disk-Based Query Queue (OOM Prevention) == |
|
| |
|
| 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):
| | <syntaxhighlight lang="ini"> |
| | | # /etc/voipmonitor.conf - KEEP THIS ENABLED |
| https://www.voipmonitor.org/doc/Scaling#SSDs
| | query_cache = yes |
| | | </syntaxhighlight> |
| == 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 <code>/etc/voipmonitor.conf</code>:
| |
| | |
| 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 <code>quick_save_cdr</code> 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:
| |
| * <code>no</code> (default) - 10 second delay, recommended for most deployments
| |
| * <code>yes</code> - 3 second delay, moderate performance impact
| |
| * <code>quick</code> - 1 second delay, high performance impact
| |
|
| |
|
| See [[Sniffer_configuration#Performance_.26_Schema|Sniffer Configuration]] for more details on this parameter.
| | {{Warning|1='''Never set <code>query_cache = no</code>''' — if database becomes unreachable and memory fills, OOM killer terminates VoIPmonitor and all queued CDRs are lost.}} |
|
| |
|
| == Enable Disk-Based Query Queue (Prevent OOM) == | | == qoq* Files Backlog == |
|
| |
|
| 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 <code>/etc/voipmonitor.conf</code>:
| | When database lags, SQL queries buffer to <code>qoq*</code> files in <code>/var/spool/voipmonitor</code>. |
|
| |
|
| query_cache = yes
| | '''Solution 1 (Preferred):''' Wait for automatic processing — monitor SQLf decreasing. |
| | |
| 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 <code>yes</code>, but if it was previously set to <code>no</code>, changing it to <code>yes</code> will use disk storage instead of RAM for queued queries.
| |
| | |
| {{Note|
| |
| <strong>Do not set <code>query_cache = no</code> to improve performance.</strong> 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 <code>query_cache = yes</code> for data safety, and use <code>quick_save_cdr</code> instead for faster CDR visibility.
| |
| }}
| |
| | |
| See [[Sniffer_configuration#Performance_.26_Schema|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 [[#SQLq/SQLf|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: <code>date</code> to confirm the system time and timezone
| |
| 4. The timezone format should be: <code>Country/City</code> (e.g., <code>Europe/Prague</code>)
| |
| | |
| 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: <code>date</code> 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:}}
| |
| {{Warning|* The same system timezone, OR}}
| |
| {{Warning|* Explicitly set the <code>timezone</code> or <code>utc</code> option in <code>/etc/voipmonitor.conf</code> 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 <code>/etc/voipmonitor.conf</code> on the sensor:
| |
|
| |
|
| | '''Solution 2 (Emergency):''' Delete backlog (loses queued CDRs): |
| <syntaxhighlight lang="bash"> | | <syntaxhighlight lang="bash"> |
| # Option 1: Override sensor timezone (system default is used if not set)
| | systemctl stop voipmonitor |
| timezone = /usr/share/zoneinfo/Europe/London
| | rm -f /var/spool/voipmonitor/qoq* |
| | | systemctl start voipmonitor |
| # Option 2: Store all timestamps in UTC (recommended for multi-timezone deployments)
| |
| utc = yes
| |
| </syntaxhighlight> | | </syntaxhighlight> |
|
| |
|
| After making changes, restart the sensor:
| | == Increasing Database Threads == |
|
| |
|
| <syntaxhighlight lang="bash"> | | <syntaxhighlight lang="ini"> |
| systemctl restart voipmonitor
| | # /etc/voipmonitor.conf |
| | mysqlstore_max_threads_cdr = 8 |
| | mysqlstore_max_threads_sip_msg = 8 |
| </syntaxhighlight> | | </syntaxhighlight> |
|
| |
|
| === Verify Fix ===
| | {{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.}} |
|
| |
|
| After correcting timezone settings:
| | {{Warning|1=<code>mysql_enable_set_id = yes</code> limits setup to '''single sensor''' writing to database. Do not use with multiple sensors.}} |
|
| |
|
| 1. Complete a new test call (or wait for an ongoing call to finish)
| | = Timezone Issues = |
| 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.}}
| | If CDRs stop appearing after time change but Active Calls work: |
|
| |
|
| === See Also === | | 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 |
|
| |
|
| * [[Settings#National|Settings - National configuration]]
| | Override in sensor config: |
| * [[Sniffer_configuration#timezone|Sensor timezone configuration]]
| | <syntaxhighlight lang="ini"> |
| * [[Sniffer_configuration#utc|UTC storage option]]
| | # /etc/voipmonitor.conf |
| | | timezone = /usr/share/zoneinfo/Europe/London |
| == More threads/connections to a db ==
| | # OR for multi-timezone deployments: |
| | | utc = yes |
| 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 <code>mysql_enable_new_store = per_query</code> or have enabled <code>mysql_enable_set_id = yes</code>, 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 <code>sip_msg</code> 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|The auto-scaling feature works in conjunction with your base thread configuration. Setting <code>mysqlstore_max_threads_cdr = 8</code> establishes a normal operating baseline, while the auto-scaling can temporarily increase to 99 if needed during peaks.}}
| |
| | |
| 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 <code>qoq*</code> (queue) files in the spool directory (default: <code>/var/spool/voipmonitor</code>) 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:
| |
| | |
| <syntaxhighlight lang="bash">
| |
| # 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
| |
| </syntaxhighlight> | | </syntaxhighlight> |
|
| |
|
| This is the preferred approach if losing older CDRs is not acceptable.
| | = MySQL/MariaDB Tuning = |
| | |
| === 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:
| | == Essential Configuration == |
| | |
| <syntaxhighlight lang="bash">
| |
| # 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
| |
| </syntaxhighlight>
| |
| | |
| {{Warning|
| |
| '''Data Loss Warning:''' Deleting qoq* files will delete any CDRs that were waiting in the queue files. Only use this method if:
| |
| * Immediate access to recent CDRs is critical
| |
| * Losing older CDRs is acceptable
| |
| * 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 <code>/etc/mysql/my.cnf</code> or <code>/etc/mysql/mariadb.conf.d/50-server.cnf</code>):
| |
|
| |
|
| <syntaxhighlight lang="ini"> | | <syntaxhighlight lang="ini"> |
| | # /etc/mysql/my.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf |
| [mysqld] | | [mysqld] |
| # InnoDB buffer pool size - set to approximately 50-70% of available RAM on a dedicated database server | | # 50-70% of RAM on dedicated DB server, 30-50% if shared with VoIPmonitor |
| # On servers running VoIPmonitor and MySQL together, use approximately 30-50% of RAM
| |
| innodb_buffer_pool_size = 8G | | innodb_buffer_pool_size = 8G |
|
| |
|
| # Reduce transaction durability for faster writes (may lose up to 1 second of data on crash) | | # Faster writes (may lose up to 1s of data on crash) |
| innodb_flush_log_at_trx_commit = 2 | | innodb_flush_log_at_trx_commit = 2 |
| </syntaxhighlight>
| |
|
| |
|
| Restart MySQL and VoIPmonitor:
| | # Disable slow query log if causing high memory (>90%) |
| <syntaxhighlight lang="bash">
| | slow_query_log = 0 |
| systemctl restart mysql
| |
| systemctl restart voipmonitor
| |
| </syntaxhighlight> | | </syntaxhighlight> |
|
| |
|
| {{Warning|Setting <code>innodb_flush_log_at_trx_commit</code> to <code>2</code> 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.}}
| | Restart services: |
| | |
| == 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:
| |
| | |
| <syntaxhighlight lang="bash"> | | <syntaxhighlight lang="bash"> |
| # Check CPU load during peak traffic
| | systemctl restart mysql && systemctl restart voipmonitor |
| top
| |
| | |
| # Or use sar for historical data
| |
| sar -u 1 10
| |
| | |
| # Check CPU core usage per process
| |
| mpstat -P ALL 1 5
| |
| </syntaxhighlight> | | </syntaxhighlight> |
|
| |
|
| 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.
| | For detailed tuning, see [[Scaling#MySQL.2FMariaDB_Configuration|Scaling - MySQL Configuration]]. |
| | |
| === 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 [[Scaling#Memory_Configuration|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 === | | == Hardware Upgrade Signs == |
|
| |
|
| 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.
| | If after all tuning optimizations: |
| | * CPU consistently at 100% |
| | * SQLq keeps growing |
| | * Large delay between "Last CDR in queue" and "Last CDR in database" |
|
| |
|
| '''Prerequisites:''' | | → '''Hardware upgrade required''' (modern CPU, NVMe SSD, more RAM). See [[Hardware]] for sizing. |
| * 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:'''
| | == MySQL Data Migration to SSD == |
|
| |
|
| <syntaxhighlight lang="bash"> | | <syntaxhighlight lang="bash"> |
| # 1. Prepare the new storage (if your SSD is hot-pluggable) | | # 1. Stop MySQL |
| # Mount the new filesystem. Example:
| |
| mount /dev/nvme0n1p1 /mnt/fast_storage
| |
| mkdir /mnt/fast_storage/mysql
| |
| | |
| # 2. Stop the MySQL service
| |
| systemctl stop mysql | | systemctl stop mysql |
| # Or for MariaDB:
| |
| # systemctl stop mariadb
| |
|
| |
|
| # 3. Copy the MySQL data directory to the new location | | # 2. Copy data (preserves permissions) |
| # The -a flag preserves permissions and ownership, -x skips other filesystems
| |
| rsync -avx /var/lib/mysql/ /mnt/fast_storage/mysql/ | | rsync -avx /var/lib/mysql/ /mnt/fast_storage/mysql/ |
|
| |
|
| # 4. Verify the copy | | # 3. Update /etc/mysql/my.cnf |
| ls -la /mnt/fast_storage/mysql/
| | # datadir = /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
| |
| </syntaxhighlight>
| |
| | |
| '''Important Notes:'''
| |
|
| |
|
| * '''No CDR Loss:''' The VoIPmonitor sniffer queues CDRs in memory during MySQL downtime. These will be processed after MySQL restarts.
| | # 4. Update AppArmor if applicable (Ubuntu/Debian) |
| * '''Backup First:''' Always take a backup of `/var/lib/mysql` before migration.
| | # 5. Start MySQL |
| * '''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:
| |
| <pre>
| |
| /dev/nvme0n1p1 /mnt/fast_storage ext4 defaults,noatime,data=writeback,barrier=0 0 1 | |
| </pre>
| |
| * '''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:
| |
| <syntaxhighlight lang="bash">
| |
| # 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 | | systemctl start mysql |
| </syntaxhighlight> | | </syntaxhighlight> |
|
| |
|
| = Database Errors =
| | {{Note|No CDR loss — VoIPmonitor queues CDRs during MySQL downtime.}} |
| | |
| == Troubleshooting: Database 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
| | === Long-Running Queries Blocking Operations === |
| * 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 ===
| | If long-running dashboard queries (GROUP BY aggregations on millions of CDR rows) are blocking partition maintenance operations (creation/dropping of partitions): |
|
| |
|
| 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.
| | {{Warning|1='''AuroraDB''' does '''not''' support the MySQL <code>KILL</code> command correctly and is '''not officially supported'''. Consider migrating to standard MySQL or MariaDB for production deployments.}} |
|
| |
|
| 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.
| | === Solution Steps === |
|
| |
|
| === Identifying the Affected Table ===
| | '''1. Implement query timeout via PHP script''' |
|
| |
|
| Check which lookup table is hitting the limit:
| | Create a PHP script to automatically kill queries running longer than 3600 seconds (1 hour): |
|
| |
|
| <syntaxhighlight lang="sql"> | | <syntaxhighlight lang="bash"> |
| -- Check the current AUTO_INCREMENT value for lookup tables
| | # Example script: add to cron to run periodically |
| SELECT
| | # Queries running > 3600 seconds will be terminated to prevent lock issues |
| 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;
| |
| </syntaxhighlight> | | </syntaxhighlight> |
|
| |
|
| Look for AUTO_INCREMENT values approaching or exceeding 16,000,000 in tables using `MEDIUMINT`.
| | '''2. Check network latency between GUI and database server''' |
|
| |
|
| === Solution: Prevent New Unique Entries ===
| | High latency can exacerbate query blocking issues. Ensure minimal network delay. |
|
| |
|
| 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.
| | '''3. Set MySQL max_execution_time''' |
|
| |
|
| ==== Option 1: Disable SIP Response Text Storage ====
| | Add to MySQL/MariaDB configuration: |
| | |
| Edit `/etc/voipmonitor.conf` on the sniffer to disable storing SIP response reason text:
| |
|
| |
|
| <syntaxhighlight lang="ini"> | | <syntaxhighlight lang="ini"> |
| # Disable storing SIP response reason strings in lookup tables | | # /etc/mysql/my.cnf or /etc/my.cnf.d/server.cnf |
| cdr_reason_string_enable = no
| | [mysqld] |
| | max_execution_time = 3600000 # 1 hour in milliseconds |
| </syntaxhighlight> | | </syntaxhighlight> |
|
| |
|
| This prevents the system from creating new unique entries for SIP response reason strings. Restart the sniffer:
| | '''4. Force internal MySQL grouping (fallback)''' |
| | |
| | If performance issues persist: |
|
| |
|
| <syntaxhighlight lang="bash"> | | <syntaxhighlight lang="bash"> |
| | # Delete charts binaries to force internal MySQL grouping |
| | rm -f /usr/local/sbin/charts* |
| systemctl restart voipmonitor | | systemctl restart voipmonitor |
| </syntaxhighlight> | | </syntaxhighlight> |
| | = Database Errors = |
|
| |
|
| ==== Option 2: Normalize Response Text ==== | | == Error 1062 - Lookup Table Limit (16777215) == |
|
| |
|
| If you need to keep some response text but reduce the number of unique entries, enable normalization in `/etc/voipmonitor.conf`:
| | '''Symptom:''' <code>1062 - Duplicate entry '16777215' for key 'PRIMARY'</code> on lookup tables (<code>cdr_sip_response</code>, <code>cdr_reason</code>). |
|
| |
|
| <syntaxhighlight lang="ini">
| | '''Cause:''' MEDIUMINT limit reached due to too many unique SIP response strings. |
| # Normalize SIP response text to reduce unique entries
| |
| cdr_reason_normalisation = yes
| |
| cdr_sip_response_normalisation = yes
| |
| cdr_ua_normalisation = yes
| |
| </syntaxhighlight>
| |
|
| |
|
| 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.
| | '''Fix (choose one):''' |
|
| |
|
| ==== Option 3: Clear Queued SQL Queries ==== | | {| class="wikitable" |
| | | |- |
| 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.
| | ! Option !! Configuration !! Notes |
| | | |- |
| {{Warning|This step will DELETE all buffered CDRs in the queue. These CDRs will be permanently lost.}}
| | | '''Disable storage''' || <code>cdr_reason_string_enable = no</code> || Stops creating new entries |
| | | |- |
| To clear the SQL queue:
| | | '''Enable normalization''' || <code>cdr_reason_normalisation = yes</code><br><code>cdr_sip_response_normalisation = yes</code><br><code>cdr_ua_normalisation = yes</code> || Reduces unique entries |
| | |- |
| | | '''Immediate fix''' || <code>TRUNCATE TABLE cdr_reason;</code> || Resets counter, loses lookup data |
| | |} |
|
| |
|
| | After config change, clear queued failed queries: |
| <syntaxhighlight lang="bash"> | | <syntaxhighlight lang="bash"> |
| # 1. Stop the VoIPmonitor service
| |
| systemctl stop voipmonitor | | 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-* | | rm -f /var/spool/voipmonitor/qoq-* |
|
| |
| # 3. Verify the files are removed
| |
| ls /var/spool/voipmonitor/qoq-*
| |
|
| |
| # 4. Restart the service
| |
| systemctl start voipmonitor | | systemctl start voipmonitor |
|
| |
| # 5. Check that service is running
| |
| systemctl status voipmonitor
| |
| </syntaxhighlight> | | </syntaxhighlight> |
|
| |
|
| 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.
| | {{Warning|1=This is '''NOT''' a schema issue — do not migrate to BIGINT. Different from main <code>cdr</code> table overflow (see [[Upgrade_to_bigint]]).}} |
| | |
| ==== 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.
| | == SUPER Privilege Error == |
|
| |
|
| {{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.}}
| | '''Symptom:''' <code>ERROR 1227 (42000): Access denied; you need SUPER privilege(s)</code> |
|
| |
|
| | '''Fix:''' |
| <syntaxhighlight lang="sql"> | | <syntaxhighlight lang="sql"> |
| -- Clear the cdr_reason table (adjust table name as needed based on error message) | | -- MySQL 5.7 / MariaDB |
| TRUNCATE TABLE cdr_reason;
| | GRANT SUPER ON *.* TO 'voipmonitor_user'@'%'; |
| </syntaxhighlight>
| |
|
| |
|
| === Verification ===
| | -- MySQL 8.0+ |
| | GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'voipmonitor_user'@'%'; |
|
| |
|
| After applying the fix:
| | FLUSH PRIVILEGES; |
| | |
| 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:
| |
| <syntaxhighlight lang="sql">
| |
| SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES
| |
| WHERE TABLE_NAME = 'cdr_sip_response' AND TABLE_SCHEMA = 'voipmonitor';
| |
| </syntaxhighlight> | | </syntaxhighlight> |
| 3. Monitor the error logs to confirm the 1062 error has stopped appearing
| |
|
| |
|
| === Important Note: NOT a Database Schema Issue ===
| | {{Note|SUPER is a global privilege — database-specific grants (<code>ALL ON voipmonitor.*</code>) do NOT include it.}} |
|
| |
|
| 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.
| | == Unknown Column Error (Schema Mismatch) == |
|
| |
|
| {{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.}}
| | '''Symptom:''' <code>Unknown column 'from_time' in 'field list'</code>, qoq files accumulating. |
|
| |
|
| == MySQL SUPER Privilege Required for Global Operations ==
| | '''Cause:''' Sensor version newer than database schema. |
|
| |
|
| 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). | | '''Fix:''' |
| | # '''GUI → Tools → System Status → Check MySQL Schema''' → Start Upgrade |
| | # If unavailable: backup with <code>mysqldump</code>, then recreate table (see [[Recovering_corrupted_database_tables]]) |
|
| |
|
| === Symptoms ===
| | '''Prevention (distributed setups):''' |
| | | <syntaxhighlight lang="ini"> |
| * Sensor startup errors such as "ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s)"
| | # On ALL sensors (not central server): |
| * Errors mentioning "global operations" or "table repairs" when the sensor attempts to initialize or repair database tables
| | disable_partition_operations = yes |
| * 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 <code>*.*</code> 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 (<code>/etc/voipmonitor/voipmonitor.conf</code> or <code>/etc/voipmonitor.conf</code>).
| |
| | |
| ==== For Older MySQL Versions (5.7 and below) or MariaDB ====
| |
| | |
| <syntaxhighlight lang="bash"> | |
| # Log in to MySQL as root | |
| mysql -u root -p
| |
| </syntaxhighlight> | | </syntaxhighlight> |
| | === Automatic Schema Upgrades and Production Safety === |
|
| |
|
| <syntaxhighlight lang="sql">
| | VoIPmonitor includes a '''built-in safety mechanism''' to prevent unexpected database schema modifications on production databases. |
| -- 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
| | '''Safety Threshold:''' Automatic schema modifications (ALTER TABLE) only occur if the database contains fewer than '''1000 CDRs'''. |
| FLUSH PRIVILEGES;
| |
|
| |
|
| -- Verify the grant | | {| class="wikitable" |
| SHOW GRANTS FOR 'voipmonitor_user'@'10.0.0.0/8';
| | |- |
| </syntaxhighlight>
| | ! Database Size !! Behavior |
| | | |- |
| ==== For MySQL 8.0 and Newer ====
| | | '''< 1000 CDRs''' || Automatic schema upgrades applied on sniffer startup |
| | | |- |
| MySQL 8.0 has split the SUPER privilege into more granular privileges. Use SYSTEM_VARIABLES_ADMIN instead:
| | ! '''> 1000 CDRs'''''' || ALTER queries logged to '''syslog/messages/journalctl''' - manual execution required |
| | | |} |
| <syntaxhighlight lang="sql">
| |
| -- Grant SYSTEM_VARIABLES_ADMIN privilege (modern equivalent of SUPER)
| |
| GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'voipmonitor_user'@'10.0.0.0/8';
| |
| | |
| FLUSH PRIVILEGES;
| |
| </syntaxhighlight>
| |
|
| |
|
| {{Note|You may also need to grant additional privileges depending on your MySQL version and specific operations:
| | For production databases (>1000 CDRs), when a new sniffer version detects required schema changes: |
| * <code>REPLICATION_CLIENT</code> - For replication status checks
| |
| * <code>PROCESS</code> - For monitoring process list
| |
| Consult your MySQL documentation for the exact privileges required.}}
| |
|
| |
|
| === Verifying the User Configuration ===
| | # Review logged ALTER queries: <code>journalctl -u voipmonitor | grep ALTER</code> or <code>grep ALTER /var/log/syslog</code> |
| | | # Execute manually during low-traffic period (e.g., overnight) to prevent table locking |
| Check your VoIPmonitor configuration file to confirm the database user being used:
| | # Restart sniffer after schema changes complete |
| | |
| <syntaxhighlight lang="bash"> | |
| # View database configuration settings
| |
| cat /etc/voipmonitor/voipmonitor.conf | grep mysql
| |
| </syntaxhighlight> | |
|
| |
|
| Look for:
| | {{Warning|Never rely on <code>disable_dbupgradecheck</code> for production safety - it's unnecessary because the 1000 CDR threshold already protects large databases from automatic modifications.}} |
| * <code>mysqluser</code> - The MySQL username (e.g., <code>voip_mon_rw</code>)
| |
| * <code>mysqlhost</code> - The database server address (affects the host in GRANT)
| |
|
| |
|
| === Restart the Sensor Service === | | {{See Also|1=For connecting additional sensors to an existing database, see [[Multiple_sniffer_instancies|Multiple Sniffer Instances]] - ensure unique <code>id_sensor</code> values.}} |
| | == Row Size Too Large (cdr_stat_values) == |
|
| |
|
| After granting the SUPER privilege, restart the VoIPmonitor sensor service:
| | '''Symptom:''' Calls don't appear in GUI until service restart, "Row size too large" errors. |
|
| |
|
| | '''Fix (destructive):''' |
| <syntaxhighlight lang="bash"> | | <syntaxhighlight lang="bash"> |
| systemctl restart voipmonitor | | systemctl stop voipmonitor |
| </syntaxhighlight>
| |
|
| |
|
| === Verification ===
| | # Save structure for analysis |
| | mysqldump -u root -p -d voipmonitor cdr_stat_values > cdr_stat_backup.sql |
|
| |
|
| Monitor the sensor startup logs to ensure the database initializes successfully without permission errors:
| | # Recreate database |
| | mysql -u root -p -e "DROP DATABASE voipmonitor; CREATE DATABASE voipmonitor;" |
|
| |
|
| <syntaxhighlight lang="bash">
| | systemctl start voipmonitor |
| # Check for startup errors
| |
| journalctl -u voipmonitor -n 50
| |
| | |
| # Or monitor in real-time
| |
| journalctl -u voipmonitor -f
| |
| </syntaxhighlight> | | </syntaxhighlight> |
|
| |
|
| You should see successful database connection and table initialization messages, without "access denied" or "command denied" errors.
| | {{Warning|This deletes all CDR data. Only use if data loss is acceptable.}} |
|
| |
|
| === Important: Database-Specific Grants vs. Global Grants === | | = See Also = |
|
| |
|
| The SUPER privilege must be granted with the global <code>*.*</code> scope because it applies to server-level operations, not just database-level operations. While database-specific grants like <code>ALL PRIVILEGES ON voipmonitor.*</code> are sufficient for standard CRUD operations (SELECT, INSERT, UPDATE, DELETE), they do NOT include the SUPER privilege required for global operations and table repairs.
| | * [[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 |
|
| |
|
| {{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.}}
| |
|
| |
|
| == See Also == | | = AI Summary for RAG = |
| * [[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. 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. |
| '''Summary:''' This page covers VoIPmonitor database troubleshooting including SQL queue issues, CDR delays, MySQL performance tuning, and database errors. SQL QUEUE DELAYS: The Active Calls view shows real-time data from sniffer while CDR view shows database records - delays occur when database cannot keep up. Symptoms include slow CDR appearance, "Crontab log is too old" warning, lag in reporting. Monitor SQLq/SQLf metrics in GUI Settings -> Sensors -> Status. QUICK CDR VISIBILITY: Use quick_save_cdr=yes (3s delay) or quick_save_cdr=quick (1s delay) in voipmonitor.conf to reduce CDR appearance delay (default is 10s). Warning: increases CPU/I/O load. QOQ FILES: SQL queries are buffered in qoq* files in /var/spool/voipmonitor when database is slow. To clear backlog: either wait for automatic processing (preferred), or emergency delete: stop voipmonitor, rm -f /var/spool/voipmonitor/qoq*, start voipmonitor (loses queued CDRs). PREVENT OOM: Keep query_cache=yes (default) to store queries on disk instead of RAM - never set to no as it risks OOM and CDR loss. DATABASE THREADS: Increase mysqlstore_max_threads_cdr and mysqlstore_max_threads_sip_msg for high traffic (default 4, can increase to 8-16). AUTO-SCALING: System automatically increases threads up to 99 when SQL queue exceeds 1,000 items (applies to CDR and sip_msg queues). This provides temporary boost during traffic spikes while base configuration handles sustained load. Note: mysql_enable_set_id=yes limits setup to single sensor writing to database. MYSQL TUNING: Set innodb_buffer_pool_size=50-70% RAM on dedicated DB or 30-50% on shared server. Set innodb_flush_log_at_trx_commit=2 for faster writes (may lose 1 second of data on crash). HARDWARE UPGRADE: If CPU is consistently at 100% after tuning, hardware upgrade is needed - modern AMD EPYC or Intel Xeon with NVMe SSD. MYSQL TO SSD MIGRATION: Stop MySQL, rsync /var/lib/mysql to new SSD, update datadir in my.cnf, update AppArmor paths, start MySQL. No CDR loss expected as sniffer queues internally. ERROR 1062 - LOOKUP TABLE LIMIT: If logs show "1062 - Duplicate entry '16777215' for key 'PRIMARY'" and CDRs stop being stored, this is lookup table (cdr_sip_response, cdr_reason) hitting MEDIUMINT limit (16,777,215). NOT a schema issue. SOLUTION: Set cdr_reason_string_enable=no in voipmonitor.conf to disable storing SIP response strings, OR enable all three normalization options (cdr_reason_normalisation=yes, cdr_sip_response_normalisation=yes, cdr_ua_normalisation=yes). IMMEDIATE FIX: TRUNCATE TABLE cdr_reason to reset auto-increment. CLEAR QUEUE: If error persists after config change, stop service, rm -f /var/spool/voipmonitor/qoq-*, restart. Do NOT confuse with cdr table INT overflow (4B rows) which requires Upgrade_to_bigint guide. SUPER PRIVILEGE ERROR: If sensor startup fails with "Access denied; you need SUPER privilege(s)" or errors about "global operations" and "table repairs", the MySQL user lacks SUPER privilege. This is a GLOBAL privilege that must be granted with . scope, not database-specific. SOLUTION for MySQL 5.7/MariaDB: GRANT SUPER ON . TO 'user'@'host'; FLUSH PRIVILEGES;. SOLUTION for MySQL 8.0+: Use SYSTEM_VARIABLES_ADMIN instead (SUPER is deprecated): GRANT SYSTEM_VARIABLES_ADMIN ON . TO 'user'@'host';. Database-specific grants like ALL PRIVILEGES ON voipmonitor.* do NOT include SUPER privilege required for global operations and table repairs. | |
|
| |
|
| '''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 | | '''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:''' | | '''Key Questions:''' |
| * Why are CDRs delayed in the GUI? (Database cannot keep up with insertion rate - check SQLq/SQLf metrics) | | * Why are CDRs not appearing but Active Calls work? (Service not running or database lag) |
| * 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)
| | * What is SQLq/SQLf? (Queue size before CDRs pushed to database) |
| * How do I reduce CDR delay in VoIPmonitor? (Set quick_save_cdr=yes or quick_save_cdr=quick in voipmonitor.conf) | | * How to reduce CDR delay? (quick_save_cdr=yes or quick) |
| * What are qoq files in VoIPmonitor? (SQL queue files in spool directory buffering database operations) | | * What are qoq files? (SQL queue files buffering DB operations) |
| * How do I clear SQL queue backlog? (Stop service, rm -f /var/spool/voipmonitor/qoq*, start service - loses queued CDRs) | | * How to fix error 1062 duplicate entry 16777215? (cdr_reason_string_enable=no, NOT schema migration) |
| * Why is SQL queue growing? (Database cannot keep up - check MySQL performance, increase threads, upgrade hardware)
| | * How to fix SUPER privilege error? (GRANT SUPER ON *.* or SYSTEM_VARIABLES_ADMIN) |
| * How do I increase database threads? (Set mysqlstore_max_threads_cdr=8 and mysqlstore_max_threads_sip_msg=8 in voipmonitor.conf)
| | * How to fix unknown column error? (GUI Check MySQL Schema tool, prevent with disable_partition_operations on sensors) |
| * What causes error 1062 - Duplicate entry '16777215' for key 'PRIMARY'? (Lookup table hitting MEDIUMINT limit due to too many unique SIP response strings)
| | * Why do CDRs disappear after time change? (Timezone mismatch in GUI settings) |
| * 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 to tune MySQL for VoIPmonitor? (innodb_buffer_pool_size=50-70% RAM, innodb_flush_log_at_trx_commit=2) |
| * How do I stop 1062 errors immediately? (TRUNCATE the affected lookup table to reset auto-increment counter) | | * When is hardware upgrade needed? (CPU at 100% after all tuning optimizations) |
| * 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)
| |
