Upgrade to bigint

This is an expert-level guide for migrating the primary key columns in a legacy VoIPmonitor database from 32-bit `INT` to 64-bit `BIGINT`. This procedure is critical for very high-volume environments to prevent data loss due to integer overflow.

Overview: The Integer Overflow Problem

Older VoIPmonitor database schemas used a standard 32-bit `INT` for the `ID` column in the `cdr` table and other related tables. A 32-bit integer has a maximum value of 4,294,967,295.

In a high-traffic environment that generates millions of calls per month, it is possible to reach this limit. When the `AUTO_INCREMENT` value hits this ceiling, MySQL/MariaDB can no longer insert new rows, and all new call data will be lost.

Modern VoIPmonitor installations use `BIGINT` by default, which has a virtually unreachable limit (over 18 quintillion). This guide explains how to manually upgrade an older schema to use `BIGINT`.

Step 1: Check if Your Database is Affected

First, determine if this migration is necessary for your system. Run the following SQL query to check the current `AUTO_INCREMENT` values for your key tables.

SELECT
    `TABLE_NAME`,
    `AUTO_INCREMENT`
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'voipmonitor' AND
    (TABLE_NAME LIKE 'cdr' OR TABLE_NAME LIKE 'message' OR TABLE_NAME LIKE 'register_%');

What to look for:

• Examine the `AUTO_INCREMENT` column. If any value is approaching the 4.2 billion limit, you should plan this migration immediately.*

Step 2: Full Database Backup (Critical)

Before making any schema changes, perform a complete and verified backup of your VoIPmonitor database. The `ALTER TABLE` operations performed in the next step are irreversible and can take a very long time.

mysqldump -u your_user -p voipmonitor > /path/to/voipmonitor_backup_before_bigint.sql

Step 3: Perform the Schema Migration

The following SQL commands will modify the column types from `INT` to `BIGINT`. These operations can be very slow and will lock the tables while running, making the GUI unresponsive. Perform this during a planned maintenance window and stop the voipmonitor sniffer service first.

Connect to your database and run the following commands.

A. CDR-Related Tables

These tables are all linked to the main `cdr` table's `ID`.

-- Main CDR table
ALTER TABLE `cdr` MODIFY COLUMN `ID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;

-- Tables with foreign keys to cdr.ID
ALTER TABLE `cdr_next` MODIFY COLUMN `cdr_ID` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `cdr_proxy` MODIFY COLUMN `cdr_ID` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `cdr_rtp` MODIFY COLUMN `cdr_ID` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `cdr_dtmf` MODIFY COLUMN `cdr_ID` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `cdr_sipresp` MODIFY COLUMN `cdr_ID` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `cdr_siphistory` MODIFY COLUMN `cdr_ID` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `cdr_tar_part` MODIFY COLUMN `cdr_ID` BIGINT UNSIGNED NOT NULL;

-- Note: The original article also dropped primary keys on some extension tables.
-- This is often safe as they are not strictly required, but proceed with caution.
-- Example: ALTER TABLE `cdr_proxy` DROP PRIMARY KEY;

B. Message-Related Tables

ALTER TABLE `message` MODIFY COLUMN `ID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
-- Apply to all message_next_* tables if they exist
-- ALTER TABLE `message_next_1` MODIFY COLUMN `cdr_ID` BIGINT UNSIGNED NOT NULL;

C. Register-Related Tables

ALTER TABLE `register_state` MODIFY COLUMN `ID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `register_failed` MODIFY COLUMN `ID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;

Step 4: Troubleshooting Post-Migration Errors

After altering the tables, restart the VoIPmonitor sniffer. In some cases, you may see errors in the log related to database triggers. This happens because old triggers may still be defined with the old `INT` data type.

1. Identify Problematic Triggers

Run this query to find any triggers that might be causing issues.

USE voipmonitor;
SHOW TRIGGERS LIKE '%auto_increment%'\G

2. Drop the Old Triggers

If the sniffer reports errors, you may need to manually drop the old triggers. The sniffer will recreate them correctly on the next startup.

-- Example of dropping triggers
DROP TRIGGER voipmonitor.cdr_proxy_auto_increment_tr;
DROP TRIGGER voipmonitor.cdr_siphistory_auto_increment_tr;

After completing these steps and restarting the sniffer service, your database will be fully migrated to use 64-bit `BIGINT` keys, protecting you from future integer overflow issues.

AI Summary for RAG

Summary: This guide provides an expert-level walkthrough for migrating a legacy VoIPmonitor database from 32-bit `INT` to 64-bit `BIGINT` primary keys to prevent critical data loss from integer overflow. It begins by explaining the ~4.2 billion row limit of `INT` columns and provides an SQL query to check if a database is approaching this limit. The core of the guide is a four-step migration plan: 1) Checking if the migration is necessary. 2) Performing a full database backup with `mysqldump`. 3) Stopping the sniffer and running a comprehensive set of `ALTER TABLE` commands to modify the column types for all `cdr`, `message`, and `register` related tables. 4) A troubleshooting section that explains how to resolve post-migration errors by finding and dropping outdated database triggers using `SHOW TRIGGERS` and `DROP TRIGGER`. The guide emphasizes that this is a slow, resource-intensive operation that must be performed during a maintenance window. Keywords: bigint, int, integer overflow, database, migration, alter table, primary key, auto_increment, `4294967295`, mysql, mariadb, schema, upgrade, trigger, drop trigger Key Questions:

• Why do I need to upgrade my database columns to BIGINT?
• What happens when the CDR table ID reaches its maximum value?
• How can I check if my database is at risk of integer overflow?
• What SQL commands are needed to migrate from INT to BIGINT?
• How long does it take to ALTER a large table?
• What should I do if the sniffer shows trigger errors after a database migration?