|
|
| 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. |
|
| |
|
| <kroki lang="mermaid"> | | <kroki lang="mermaid"> |
| Line 14: |
Line 14: |
| Q3 -->|Yes| FIX2[Hardware upgrade needed] | | Q3 -->|Yes| FIX2[Hardware upgrade needed] |
| Q3 -->|No| FIX3[Tune MySQL config] | | Q3 -->|No| FIX3[Tune MySQL config] |
| FIX1 --> DONE[CDRs should appear]
| |
| FIX2 --> DONE
| |
| FIX3 --> DONE
| |
| Q4 --> DONE
| |
| </kroki> | | </kroki> |
|
| |
|
| {| class="wikitable" style="width:100%; background:#f8f9fa; border:2px solid #00A7E3; margin-bottom:20px;"
| | = Service Not Running = |
| |-
| |
| ! colspan="3" style="background:#00A7E3; color:white; font-size:1.2em; padding:10px;" | Quick Navigation - Database Troubleshooting
| |
| |-
| |
| ! style="width:33%; background:#e0f4fc; padding:8px; vertical-align:top;" | SQL Queue Issues
| |
| ! style="width:33%; background:#fef3e2; padding:8px; vertical-align:top;" | Database Errors
| |
| ! style="width:33%; background:#f1f5f9; padding:8px; vertical-align:top;" | Performance & Migration
| |
| |-
| |
| | style="vertical-align:top; padding:10px;" |
| |
| '''CDR Visibility'''
| |
| * [[#Active Calls Visible, CDRs Not Showing (Server Crash/Restart)|Service Not Running (Quick Fix)]]
| |
| * [[#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;" |
| |
| '''Table Corruption'''
| |
| * [[#Row Size Too Large - cdr_stat_values Corruption|HEP Calls Not Appearing Real-Time]]
| |
| * [[#Error 1062_-_Lookup_Table_Auto-Increment_Limit|1062 - Lookup Table Limit (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) =
| |
| | |
| 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.
| |
|
| |
|
| == First Check: VoIPmonitor Service Status ==
| | If Active Calls are visible but CDRs are missing after restart, the sniffer service likely didn't start. |
| | |
| 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 ===
| |
|
| |
|
| If the service is inactive or failed, start it:
| | # Enable auto-start on boot |
| | | systemctl enable voipmonitor |
| <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|1='''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.
| |
| | |
| == Enable Disk-Based Query Queue (Prevent OOM) ==
| |
| | |
| 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>:
| |
| | |
| query_cache = yes
| |
| | |
| 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|1=<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.
| | {{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.}} |
|
| |
|
| === Sensors Timezone === | | == qoq* Files Backlog == |
|
| |
|
| The '''Sensors Timezone''' setting controls how CDR timestamps are displayed in the GUI:
| | When database lags, SQL queries buffer to <code>qoq*</code> files in <code>/var/spool/voipmonitor</code>. |
|
| |
|
| 1. Navigate to '''Settings > System Configuration > National'''
| | '''Solution 1 (Preferred):''' Wait for automatic processing — monitor SQLf decreasing. |
| 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|1=If probes are configured to generate CDRs in different timezones, CDRs may not display correctly. Ensure all probes use either: (1) the same system timezone, OR (2) 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>
| |
| | |
| This is the preferred approach if losing older CDRs is not acceptable.
| |
| | |
| === 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:
| |
| | |
| <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> | | </syntaxhighlight> |
|
| |
|
| {{Warning|1='''Data Loss Warning:''' Deleting qoq* files will delete any CDRs that were waiting in the queue files. Only use this method if: (1) Immediate access to recent CDRs is critical, (2) Losing older CDRs is acceptable, (3) You have exhausted all other options (configuration tuning, hardware upgrade).}}
| | = MySQL/MariaDB Tuning = |
|
| |
|
| This clears the backlog and allows new CDRs to be written immediately without waiting for the old queue to process.
| | == Essential Configuration == |
| | |
| = 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:
| |
| <syntaxhighlight lang="bash">
| |
| systemctl restart mysql
| |
| systemctl restart voipmonitor
| |
| </syntaxhighlight>
| |
|
| |
|
| {{Warning|1=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.}}
| | # Disable slow query log if causing high memory (>90%) |
| | |
| == Slow Query Log Configuration ==
| |
| | |
| The MySQL slow query log can consume significant memory on high-traffic systems. If you are experiencing high memory utilization alerts (>90% for 30 minutes) on the database server, the slow query log may be the cause.
| |
| | |
| {{Warning|Disabling the slow query log removes the ability to analyze slow queries for performance optimization. Only disable it temporarily or if you are certain you do not need it.}}
| |
| | |
| Edit your MySQL configuration file (typically <code>/etc/mysql/my.cnf</code> or <code>/etc/mysql/mariadb.conf.d/50-server.cnf</code> or <code>/etc/my.cnf.d/mysql-server.cnf</code>):
| |
| | |
| <syntaxhighlight lang="ini">
| |
| [mysqld]
| |
| # Disable slow query log (set to 1 to enable)
| |
| slow_query_log = 0 | | slow_query_log = 0 |
|
| |
| # Alternative: Increase threshold to only log extremely slow queries (e.g., 600 seconds = 10 minutes)
| |
| long_query_time = 600
| |
| </syntaxhighlight>
| |
|
| |
| Restart MySQL and VoIPmonitor:
| |
|
| |
| <syntaxhighlight lang="bash">
| |
| systemctl restart mysql # or: systemctl restart mariadb
| |
| systemctl restart voipmonitor
| |
| </syntaxhighlight> | | </syntaxhighlight> |
|
| |
|
| For additional MySQL performance tuning details, see [[Scaling#Slow_Query_Log|Scaling - Slow Query Log]].
| | 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 ===
| | == Hardware Upgrade Signs == |
|
| |
|
| Upgrade to a more modern CPU architecture with significantly better performance:
| | If after all tuning optimizations: |
| | * CPU consistently at 100% |
| | * SQLq keeps growing |
| | * Large delay between "Last CDR in queue" and "Last CDR in database" |
|
| |
|
| * '''CPU''' - Modern AMD EPYC or Intel Xeon Gold/Silver processors with more cores
| | → '''Hardware upgrade required''' (modern CPU, NVMe SSD, more RAM). See [[Hardware]] for sizing. |
| * '''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 === | | == MySQL Data Migration to SSD == |
| | |
| 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 ===
| |
| | |
| 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.
| |
| | |
| '''Prerequisites:'''
| |
| * 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:'''
| |
|
| |
|
| <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 | | # 4. Update AppArmor if applicable (Ubuntu/Debian) |
| | # 5. Start MySQL |
| systemctl start mysql | | 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> | | </syntaxhighlight> |
|
| |
|
| '''Important Notes:'''
| | {{Note|No CDR loss — VoIPmonitor queues CDRs during MySQL downtime.}} |
| | |
| * '''No CDR Loss:''' The VoIPmonitor sniffer queues CDRs in memory during MySQL downtime. These will be processed after MySQL restarts.
| |
| * '''Backup First:''' Always take a backup of `/var/lib/mysql` before migration.
| |
| * '''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:
| |
| <syntaxhighlight lang="text">
| |
| /dev/nvme0n1p1 /mnt/fast_storage ext4 defaults,noatime,data=writeback,barrier=0 0 1
| |
| </syntaxhighlight>
| |
| * '''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
| |
| </syntaxhighlight>
| |
|
| |
|
| = Database Errors = | | = Database Errors = |
|
| |
|
| == Error 1062 - Lookup Table Auto-Increment Limit == | | == Error 1062 - Lookup Table Limit (16777215) == |
|
| |
|
| 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.
| | '''Symptom:''' <code>1062 - Duplicate entry '16777215' for key 'PRIMARY'</code> on lookup tables (<code>cdr_sip_response</code>, <code>cdr_reason</code>). |
|
| |
|
| === Symptoms ===
| | '''Cause:''' MEDIUMINT limit reached due to too many unique SIP response strings. |
|
| |
|
| * CDRs stop being inserted into the database
| | '''Fix (choose one):''' |
| * 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 === | | {| class="wikitable" |
| | | |- |
| 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.
| | ! Option !! Configuration !! Notes |
| | | |- |
| 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.
| | | '''Disable storage''' || <code>cdr_reason_string_enable = no</code> || Stops creating new entries |
| | | |- |
| === Identifying the Affected Table === | | | '''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 |
| | | |- |
| Check which lookup table is hitting the limit:
| | | '''Immediate fix''' || <code>TRUNCATE TABLE cdr_reason;</code> || Resets counter, loses lookup data |
| | | |} |
| <syntaxhighlight lang="sql"> | |
| -- Check the current AUTO_INCREMENT value for lookup tables | |
| SELECT
| |
| 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> | |
| | |
| Look for AUTO_INCREMENT values approaching or exceeding 16,000,000 in tables using `MEDIUMINT`.
| |
| | |
| === Solution: Prevent New Unique Entries === | |
| | |
| 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.
| |
| | |
| ==== Option 1: Disable SIP Response Text Storage ====
| |
| | |
| Edit `/etc/voipmonitor.conf` on the sniffer to disable storing SIP response reason text:
| |
| | |
| <syntaxhighlight lang="ini"> | |
| # Disable storing SIP response reason strings in lookup tables
| |
| cdr_reason_string_enable = no
| |
| </syntaxhighlight> | |
| | |
| This prevents the system from creating new unique entries for SIP response reason strings. Restart the sniffer:
| |
|
| |
|
| | After config change, clear queued failed queries: |
| <syntaxhighlight lang="bash"> | | <syntaxhighlight lang="bash"> |
| systemctl restart voipmonitor
| |
| </syntaxhighlight>
| |
|
| |
| ==== Option 2: Normalize Response Text ====
| |
|
| |
| If you need to keep some response text but reduce the number of unique entries, enable normalization in `/etc/voipmonitor.conf`:
| |
|
| |
| <syntaxhighlight lang="ini">
| |
| # 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.
| |
|
| |
| ==== Option 3: Clear Queued SQL Queries ====
| |
|
| |
| 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.
| |
|
| |
| {{Warning|1=This step will DELETE all buffered CDRs in the queue. These CDRs will be permanently lost.}}
| |
|
| |
| To clear the SQL queue:
| |
|
| |
| <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) ==== | | == SUPER Privilege Error == |
|
| |
|
| 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.
| | '''Symptom:''' <code>ERROR 1227 (42000): Access denied; you need SUPER privilege(s)</code> |
| | |
| {{Warning|1=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.}}
| |
|
| |
|
| | '''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:
| |
| | |
| 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>
| |
| 3. Monitor the error logs to confirm the 1062 error has stopped appearing
| |
| | |
| === Important Note: NOT a Database Schema Issue ===
| |
| | |
| 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.
| |
| | |
| {{Warning|1=Do NOT confuse this with the unrelated <code>cdr</code> table integer overflow problem. The main <code>cdr</code> 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.}}
| |
| | |
| == MySQL SUPER Privilege Required for Global Operations ==
| |
| | |
| 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).
| |
| | |
| === Symptoms ===
| |
| | |
| * Sensor startup errors such as "ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s)"
| |
| * Errors mentioning "global operations" or "table repairs" when the sensor attempts to initialize or repair database tables
| |
| * 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 lang="sql">
| |
| -- 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
| |
| FLUSH PRIVILEGES;
| |
| | |
| -- Verify the grant
| |
| SHOW GRANTS FOR 'voipmonitor_user'@'10.0.0.0/8';
| |
| </syntaxhighlight>
| |
| | |
| ==== For MySQL 8.0 and Newer ====
| |
| | |
| MySQL 8.0 has split the SUPER privilege into more granular privileges. Use SYSTEM_VARIABLES_ADMIN instead:
| |
| | |
| <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; | | FLUSH PRIVILEGES; |
| </syntaxhighlight> | | </syntaxhighlight> |
|
| |
|
| {{Note|You may also need to grant additional privileges depending on your MySQL version and specific operations: | | {{Note|SUPER is a global privilege — database-specific grants (<code>ALL ON voipmonitor.*</code>) do NOT include it.}} |
| * <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 === | | == Unknown Column Error (Schema Mismatch) == |
|
| |
|
| Check your VoIPmonitor configuration file to confirm the database user being used:
| | '''Symptom:''' <code>Unknown column 'from_time' in 'field list'</code>, qoq files accumulating. |
|
| |
|
| <syntaxhighlight lang="bash">
| | '''Cause:''' Sensor version newer than database schema. |
| # View database configuration settings
| |
| cat /etc/voipmonitor/voipmonitor.conf | grep mysql
| |
| </syntaxhighlight>
| |
|
| |
|
| Look for:
| | '''Fix:''' |
| * <code>mysqluser</code> - The MySQL username (e.g., <code>voip_mon_rw</code>)
| | # '''GUI → Tools → System Status → Check MySQL Schema''' → Start Upgrade |
| * <code>mysqlhost</code> - The database server address (affects the host in GRANT)
| | # If unavailable: backup with <code>mysqldump</code>, then recreate table (see [[Recovering_corrupted_database_tables]]) |
|
| |
|
| === Restart the Sensor Service ===
| | '''Prevention (distributed setups):''' |
| | | <syntaxhighlight lang="ini"> |
| After granting the SUPER privilege, restart the VoIPmonitor sensor service:
| | # On ALL sensors (not central server): |
| | | disable_partition_operations = yes |
| <syntaxhighlight lang="bash">
| |
| systemctl restart voipmonitor
| |
| </syntaxhighlight>
| |
| | |
| === Verification ===
| |
| | |
| Monitor the sensor startup logs to ensure the database initializes successfully without permission errors:
| |
| | |
| <syntaxhighlight lang="bash"> | |
| # Check for startup errors | |
| journalctl -u voipmonitor -n 50
| |
| | |
| # Or monitor in real-time
| |
| journalctl -u voipmonitor -f
| |
| </syntaxhighlight>
| |
| | |
| You should see successful database connection and table initialization messages, without "access denied" or "command denied" errors.
| |
| | |
| === Important: Database-Specific Grants vs. Global Grants ===
| |
| | |
| 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.
| |
| | |
| {{Warning|1=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.}}
| |
| | |
| == Row Size Too Large - cdr_stat_values Corruption ==
| |
| | |
| If calls sent via HEP (or any calls) are not appearing in the GUI in real-time and only appear after restarting the VoIPmonitor service, this may indicate database corruption in the `cdr_stat_values` table.
| |
| | |
| === Symptoms ===
| |
| | |
| * Calls (including HEP calls) are visible in real-time after sensor restart but stop appearing until next restart
| |
| * VoIPmonitor service logs show partition creation errors
| |
| * MySQL errors mentioning "Row size too large" on the `cdr_stat_values` table
| |
| * The `cdr_stat_values` table structure is corrupted
| |
| | |
| === Root Cause ===
| |
| | |
| The `cdr_stat_values` table can become corrupted due to database structure issues, particularly in partition creation operations. When this table is corrupted, the VoIPmonitor service cannot properly store CDR statistics, causing calls to not appear in the GUI until the service is restarted (which forces a temporary work-around).
| |
| | |
| === Diagnosis: Check Service Logs ===
| |
| | |
| Examine the VoIPmonitor service logs for database-related errors:
| |
| | |
| <syntaxhighlight lang="bash">
| |
| journalctl -u voipmonitor -n 200 | grep -i "partition\|cdr_stat_values\|row size"
| |
| tail -f /var/log/voipmonitor.log | grep -i "partition"
| |
| </syntaxhighlight> | | </syntaxhighlight> |
|
| |
|
| Look for:
| | == Row Size Too Large (cdr_stat_values) == |
| * Errors during partition creation (e.g., "cannot create partition", "partition creation failed")
| |
| * "Row size too large" errors on `cdr_stat_values` table
| |
| * Database lock or deadlock errors during partition operations
| |
|
| |
|
| === Solution: Drop and Recreate the VoIPmonitor Database ===
| | '''Symptom:''' Calls don't appear in GUI until service restart, "Row size too large" errors. |
| | |
| The most effective solution is to drop and recreate the entire VoIPmonitor database. This will restore the correct table structure and allow calls to be processed and displayed correctly.
| |
| | |
| {{Warning|1=<strong>DATA LOSS WARNING:</strong> Dropping the database will delete all CDR data, statistics, and stored records. Only proceed if you are certain you can afford to lose this data or have a recent backup.}}
| |
| | |
| ==== Step 1: Stop VoIPmonitor Service ====
| |
|
| |
|
| | '''Fix (destructive):''' |
| <syntaxhighlight lang="bash"> | | <syntaxhighlight lang="bash"> |
| systemctl stop voipmonitor | | systemctl stop voipmonitor |
| </syntaxhighlight>
| |
|
| |
|
| ==== Step 2: Save Table Structure for Future Analysis (Preventative) ====
| | # Save structure for analysis |
| | mysqldump -u root -p -d voipmonitor cdr_stat_values > cdr_stat_backup.sql |
|
| |
|
| Before recreating the database, save the table structure of the corrupted table for future analysis:
| | # Recreate database |
| | mysql -u root -p -e "DROP DATABASE voipmonitor; CREATE DATABASE voipmonitor;" |
|
| |
|
| <syntaxhighlight lang="bash">
| |
| # Save the cdr_stat_values table structure only
| |
| mysqldump -u root -p -d voipmonitor cdr_stat_values > cdr_stat_values_structure_$(date +%Y%m%d).sql
| |
| </syntaxhighlight>
| |
|
| |
| The <code>-d</code> flag exports only the schema (structure) without the data, which is useful for diagnosing root causes if the issue recurs.
| |
|
| |
| ==== Step 3: Drop and Recreate the Database ====
| |
|
| |
| <syntaxhighlight lang="sql">
| |
| -- Connect to MySQL
| |
| mysql -u root -p
| |
|
| |
| -- Drop the corrupted database
| |
| DROP DATABASE voipmonitor;
| |
|
| |
| -- Recreate a fresh database
| |
| CREATE DATABASE voipmonitor;
| |
| </syntaxhighlight>
| |
|
| |
| ==== Step 4: Start VoIPmonitor to Initialize ====
| |
|
| |
| <syntaxhighlight lang="bash">
| |
| systemctl start voipmonitor | | systemctl start voipmonitor |
| </syntaxhighlight> | | </syntaxhighlight> |
|
| |
|
| The VoIPmonitor service will automatically create all required tables (including `cdr_stat_values`) with the correct structure.
| | {{Warning|This deletes all CDR data. Only use if data loss is acceptable.}} |
| | |
| {{Note|Service startup may take longer than usual as it initializes all partitions. Monitor the logs to confirm successful initialization.}}
| |
| | |
| ==== Step 5: Verify Fix ====
| |
| | |
| <syntaxhighlight lang="bash">
| |
| # Check that VoIPmonitor started successfully
| |
| systemctl status voipmonitor
| |
| | |
| # Make a test call and verify it appears in the GUI in real-time
| |
| # Check for the call in the CDR list or Active Calls view
| |
| </syntaxhighlight>
| |
| | |
| Calls should now appear in the GUI in real-time without requiring a service restart.
| |
| | |
| === Prevention ===
| |
| | |
| To prevent this issue from recurring:
| |
| | |
| * Monitor database logs for early warning signs of partition creation issues
| |
| * Ensure MySQL/MariaDB configuration is optimized for VoIPmonitor (see [[Scaling]])
| |
| * Maintain regular backups using <code>mysqldump</code> to recover data if database recreation is needed
| |
| * Keep MySQL/MariaDB versions up to date
| |
| | |
| === Troubleshooting Recurring Issues ===
| |
| | |
| If the issue recurs after recreating the database:
| |
| | |
| 1. Examine the saved table structure from Step 2:
| |
| <syntaxhighlight lang="bash">
| |
| cat cdr_stat_values_structure_20250101.sql
| |
| </syntaxhighlight>
| |
| | |
| 2. Compare with a fresh VoIPmonitor installation to identify structural differences
| |
| | |
| 3. Report the issue to VoIPmonitor support with:
| |
| * The saved table structure file
| |
| * Service log excerpts showing partition errors
| |
| * MySQL/MariaDB version information
| |
| | |
| == Unknown Column Error (Sensor Schema Mismatch) ==
| |
| | |
| If sensors are failing to send CDRs to a master database with errors like "send store query error" and "Unknown column 'from_time' in 'field list'", and you see an accumulation of unprocessed qoq-* files in the spool directory, this indicates a database schema version mismatch.
| |
| | |
| === Symptoms ===
| |
| | |
| * Log errors: <code>send store query error</code>
| |
| * Database errors: <code>Unknown column 'column_name' in 'field list'</code>
| |
| * Accumulation of qoq-* files in the spool directory (typically <code>/var/spool/voipmonitor</code>)
| |
| * SQLf queue growing because database writes are failing
| |
| | |
| === Root Cause ===
| |
| | |
| The error indicates that the **sensor software version is newer than the database schema**. The sensor is attempting to use columns, tables, or data types that do not exist in your current database structure. When the database write fails, the sensor buffers the data locally into qoq (Queue Offline Queue) files to prevent data loss.
| |
| | |
| This commonly occurs after:
| |
| * Upgrading the sensor binary without updating the database schema
| |
| * Adding a new sensor with a newer version to an existing database
| |
| * Running schema check/upgrade tools on multiple sensors simultaneously
| |
| | |
| == Solution 1: Use Check MySQL Schema Tool (Recommended) ==
| |
| | |
| The quickest way to resolve this is to use the built-in database schema upgrade tool.
| |
| | |
| 1. Log in to the **VoIPmonitor Web GUI**
| |
| 2. Navigate to **Tools → System Status**
| |
| 3. Locate the **Check MySQL Schema** section
| |
| 4. The tool will report missing columns or tables. It will specifically identify the schema issues
| |
| 5. Check the box next to the required changes (or "Select All")
| |
| 6. Click **Start Upgrade / Run SQL**
| |
| | |
| {{Note|This process may lock tables while running. On large databases, this can take time. Consider performing during a maintenance window.}}
| |
| | |
| == Solution 2: Manual Schema Update (Fallback) ==
| |
| | |
| If the Check MySQL Schema tool is unavailable or fails, you can manually recreate the table schema.
| |
| | |
| {{Warning|1=<strong>CRITICAL: Backup your data first!</strong> Before any schema changes, create a backup using mysqldump.}}
| |
| | |
| <source lang="sql">
| |
| -- Backup the affected table
| |
| mysqldump -u root -p voipmonitor cdr > cdr_backup_$(date +%Y%m%d).sql
| |
| </source>
| |
| | |
| Then either:
| |
| | |
| === Option A: Transportable Tablespaces (Preserves Data) ===
| |
| | |
| This method preserves your existing data while recreating the table structure. See [[Recovering_corrupted_database_tables|Recovering Corrupted Database Tables]] for the complete transportable tablespaces procedure.
| |
| | |
| === Option B: Drop and Recreate Table (Destructive) ===
| |
| | |
| Use this ONLY if you have a recent backup and can afford to lose data:
| |
| | |
| <syntaxhighlight lang="sql">
| |
| -- 1. Stop VoIPmonitor service
| |
| -- systemctl stop voipmonitor
| |
| | |
| -- 2. Get the fresh CREATE TABLE statement from:
| |
| -- - A fresh VoIPmonitor installation of the same version, OR
| |
| -- - The GUI: Tools → System Status → Check MySQL Schema, OR
| |
| -- - The sniffer command line tool
| |
| | |
| -- 3. Drop the problematic table
| |
| USE voipmonitor;
| |
| DROP TABLE IF EXISTS cdr; -- Replace with actual table name
| |
| | |
| -- 4. Recreate with correct schema for your DB version (MariaDB or MySQL)
| |
| -- Paste the CREATE TABLE statement obtained in step 2
| |
| </syntaxhighlight>
| |
| | |
| {{Warning|This will delete all data in the table. Only use if you have a recent backup or data loss is acceptable.}} | |
| | |
| == Prevention: Centralize Schema Management ==
| |
| | |
| To prevent schema conflicts in distributed setups with multiple sensors:
| |
| | |
| {{Warning|In distributed deployments, only ONE instance should manage database structure and partitions.}}
| |
| | |
| Edit <code>/etc/voipmonitor.conf</code> on **all sensor probes**:
| |
| | |
| <syntaxhighlight lang="ini">
| |
| # Disable partition operations on sensors
| |
| disable_partition_operations = yes
| |
| </syntaxhighlight>
| |
| | |
| On the **central server instance**, leave this setting disabled (or set to <code>no</code>) so it can manage schema and partitions. Designate a single instance as the "Master" for all database structure changes.
| |
| | |
| This prevents race conditions where multiple sensors try to alter the table structure simultaneously, causing "Unknown column" or "Duplicate column" errors.
| |
| | |
| {{Note|The <code>disable_dbupgradecheck</code> option controls automatic schema checks on startup. If you want sensors to check but not apply changes, use <code>disable_partition_operations = yes</code> instead.}}
| |
| | |
| == Queue Drainage After Fix ==
| |
| | |
| Once the schema correction is complete:
| |
| | |
| 1. Restart the VoIPmonitor sensor service to apply changes:
| |
| <syntaxhighlight lang="bash">
| |
| systemctl restart voipmonitor
| |
| </syntaxhighlight>
| |
| | |
| 2. Monitor the logs. The sensor should now successfully connect to the database
| |
| | |
| 3. **Automatic Queue Processing:** The sensor will automatically detect and process the qoq-* files in the spool directory once the database connection is stable and the schema matches. Monitor the sensor logs or GUI → Settings → Sensors to watch the **SQLq** (SQL Queue) metric decrease as the files are drained
| |
| | |
| === See Also ===
| |
|
| |
|
| * [[Recovering_corrupted_database_tables|Recovering Corrupted Database Tables]] - For other types of table corruption
| | = See Also = |
| * [[Database_structure|Database Structure]] - Reference for VoIPmonitor table schemas
| |
| * [[Scaling]] - MySQL/MariaDB performance tuning for high-volume deployments
| |
|
| |
|
| == See Also ==
| | * [[Sniffer_configuration]] - Configuration parameters reference |
| * [[Sniffer_configuration]] - Complete configuration reference for voipmonitor.conf | | * [[Scaling]] - Performance tuning |
| * [[Scaling]] - Performance tuning and optimization | | * [[Hardware]] - Hardware sizing |
| * [[Hardware]] - Hardware sizing guidelines | | * [[Upgrade_to_bigint]] - CDR table INT overflow (4B rows) |
| * [[Upgrade_to_bigint]] - Migrating CDR table to BIGINT (for main cdr table INT overflow) | | * [[Recovering_corrupted_database_tables]] - Table corruption recovery |
| * [[GUI_troubleshooting]] - GUI-specific issues | | * [[Sniffer_troubleshooting]] - Network/capture issues |
| * [[Sniffer_troubleshooting]] - Sensor/sniffer issues (no calls, network problems) | |
|
| |
|
| == AI Summary for RAG ==
| | = AI Summary for RAG = |
| '''Summary:''' VoIPmonitor database troubleshooting guide covering SQL queue issues, CDR delays, MySQL tuning, and database errors. SQL QUEUE: Active Calls shows real-time sniffer data while CDR view shows DB records. Delays occur when DB cannot keep up. Monitor SQLq/SQLf in Settings->Sensors->Status. QUICK CDR: Use quick_save_cdr=yes (3s) or quick (1s) in voipmonitor.conf (default 10s, increases CPU/IO). QOQ FILES: SQL queries buffered in qoq* files in /var/spool/voipmonitor. Clear backlog: wait (preferred) or emergency delete qoq* files (loses CDRs). OOM PREVENTION: Keep query_cache=yes (default) - never set to no. THREADS: Increase mysqlstore_max_threads_cdr/sip_msg for high traffic. Auto-scales to 99 threads when queue >1000. Note: 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. SLOW QUERY LOG: Can cause high memory utilization (>90% on database server). Disable with slow_query_log=0 in my.cnf, or increase threshold with long_query_time=600. Requires restarting MySQL and voipmonitor services. HARDWARE: If CPU at 100% after tuning, upgrade needed. SSD MIGRATION: Stop MySQL, rsync to SSD, update datadir, start. No CDR loss. ERROR 1062 (16777215): Lookup table (cdr_sip_response, cdr_reason) hit MEDIUMINT limit. FIX: cdr_reason_string_enable=no OR enable all normalization options. IMMEDIATE: TRUNCATE affected table. NOT a schema issue - different from cdr table INT overflow (see Upgrade_to_bigint). SUPER PRIVILEGE: GRANT SUPER ON *.* (MySQL 5.7/MariaDB) or SYSTEM_VARIABLES_ADMIN (MySQL 8.0+). Required for global operations. CDR_STAT_VALUES CORRUPTION: Calls not appearing in GUI until restart indicates partition errors. FIX: DROP/recreate database (loses data). Check journalctl for "row size too large" errors.
| |
|
| |
|
| UNKNOWN COLUMN ERROR: Sensor version > database schema causes "Unknown column 'from_time'" and "send store query error" with qoq files accumulating. SOLUTION 1 (RECOMMENDED): GUI -> Tools -> System Status -> Check MySQL Schema -> Start Upgrade. SOLUTION 2 (FALLBACK): mysqldump backup first, then Option A transportable tablespaces (preserve data, see Recovering_corrupted_database_tables) OR Option B drop/recreate table (destructive, needs backup). Get fresh CREATE TABLE from fresh installation or GUI tool. PREVENTION: On distributed setups, set disable_partition_operations=yes on all sensors, leave disabled on central server for schema management. Queue drains automatically after fix - monitor SQLq metric decreasing. DIFFERENT FROM: table corruption (physical .par file issues) or lookup table error 1062.
| | '''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:''' 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, slow query log, slow_query_log, long_query_time, high memory utilization, memory alert, database memory, memory optimization, MySQL memory, MariaDB memory, 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, row size too large, cdr_stat_values corruption, partition creation error, HEP calls not appearing real time, calls not appearing until restart, drop recreate database, mysqldump structure save, unknown column, schema mismatch, sensor version, database version, schema version, from_time error, send store query error, check mysql schema, mysql schema upgrade, disable_partition_operations, central server, master sensor, schema management, distributed database | | '''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:''' |
| * What causes unknown column error in VoIPmonitor? (Sensor software version newer than database schema - sensor uses columns that do not exist)
| | * Why are CDRs not appearing but Active Calls work? (Service not running or database lag) |
| * How do I fix unknown column 'from_time' in field list error? (Use GUI Tools->System Status->Check MySQL Schema tool to upgrade, OR manual mysqldump backup and recreate table)
| | * What is SQLq/SQLf? (Queue size before CDRs pushed to database) |
| * What is send store query error in VoIPmonitor? (Database write failure due to schema mismatch - sensor cannot insert CDRs with new columns)
| | * How to reduce CDR delay? (quick_save_cdr=yes or quick) |
| * Why are qoq files accumulating in spool directory? (Database schema mismatch - sensor buffering failed writes, will auto-drain after schema fix) | | * What are qoq files? (SQL queue files buffering DB operations) |
| * Should I backup database before schema upgrade? (YES - always mysqldump before any schema changes)
| | * How to fix error 1062 duplicate entry 16777215? (cdr_reason_string_enable=no, NOT schema migration) |
| * How do I get fresh CREATE TABLE statement for schema update? (From fresh installation of same version, or GUI Check MySQL Schema tool shows current schema)
| | * How to fix SUPER privilege error? (GRANT SUPER ON *.* or SYSTEM_VARIABLES_ADMIN) |
| * What is disable_partition_operations for? (Set on all sensors to prevent schema conflicts - only central server manages partitions in distributed setups) | | * How to fix unknown column error? (GUI Check MySQL Schema tool, prevent with disable_partition_operations on sensors) |
| * How does qoq queue drain after schema fix? ( Automatically - sensor detects and processes files once DB connection stable and schema matches, monitor SQLq metric decreasing)
| | * Why do CDRs disappear after time change? (Timezone mismatch in GUI settings) |
| * 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)
| | * How to tune MySQL for VoIPmonitor? (innodb_buffer_pool_size=50-70% RAM, innodb_flush_log_at_trx_commit=2) |
| * How do I fix high memory utilization on database server? (Disable slow query log with slow_query_log=0 in my.cnf, or increase threshold with long_query_time=600)
| | * When is hardware upgrade needed? (CPU at 100% after all tuning optimizations) |
| * How do I reduce CDR delay in VoIPmonitor? (Set quick_save_cdr=yes or quick_save_cdr=quick in voipmonitor.conf) | |
| * What are qoq files in VoIPmonitor? (SQL queue files in spool directory buffering database operations) | |
| * How do I clear SQL queue backlog? (Stop service, rm -f /var/spool/voipmonitor/qoq*, start service - loses queued CDRs) | |
| * Why is SQL queue growing? (Database cannot keep up - check MySQL performance, increase threads, upgrade hardware)
| |
| * How do I increase database threads? (Set mysqlstore_max_threads_cdr=8 and mysqlstore_max_threads_sip_msg=8 in voipmonitor.conf)
| |
| * What causes error 1062 - Duplicate entry '16777215' for key 'PRIMARY'? (Lookup table hitting MEDIUMINT limit due to too many unique SIP response strings)
| |
| * 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 do I stop 1062 errors immediately? (TRUNCATE the affected lookup table to reset auto-increment counter) | |
| * 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)
| |
| * Why are HEP calls not appearing in GUI real-time? (May be cdr_stat_values table corruption with "Row size too large" error - check journalctl for partition errors)
| |
| * What causes calls to not appear until sensor restart? (Database corruption in cdr_stat_values table prevents CDR storage - only workaround is restarting service which works temporarily)
| |
| * How do I fix cdr_stat_values corruption? (Stop service, save table structure with mysqldump -d, DROP DATABASE voipmonitor, CREATE DATABASE, restart service - DELETES ALL CDR DATA)
| |
| * How do I diagnose cdr_stat_values "Row size too large" errors? (Check journalctl -u voipmonitor for "partition cdr_stat_values row size" errors)
| |
| * How do I save table structure for database corruption analysis? (Use mysqldump -d voipmonitor cdr_stat_values > file.sql to save schema without data)
| |
