Upgrade to bigint

Contents

CDR

ALTER TABLE `cdr`  MODIFY COLUMN `ID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `cdr_next`  MODIFY COLUMN `cdr_ID` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `cdr_proxy`  DROP COLUMN `ID`, MODIFY COLUMN `cdr_ID` BIGINT UNSIGNED NOT NULL, DROP PRIMARY KEY, ADD INDEX `calldate`(`calldate`);
ALTER TABLE `cdr_rtp`  DROP COLUMN `ID`, MODIFY COLUMN `cdr_ID` BIGINT UNSIGNED NOT NULL, DROP PRIMARY KEY, ADD INDEX `calldate`(`calldate`);
ALTER TABLE `cdr_dtmf`  DROP COLUMN `ID`, MODIFY COLUMN `cdr_ID` BIGINT UNSIGNED NOT NULL, DROP PRIMARY KEY, ADD INDEX `calldate`(`calldate`);
ALTER TABLE `cdr_sipresp`  DROP COLUMN `ID`, MODIFY COLUMN `cdr_ID` BIGINT UNSIGNED NOT NULL, DROP PRIMARY KEY, ADD INDEX `calldate`(`calldate`);
ALTER TABLE `cdr_siphistory`  DROP COLUMN `ID`, MODIFY COLUMN `cdr_ID` BIGINT UNSIGNED NOT NULL, DROP PRIMARY KEY, ADD INDEX `calldate`(`calldate`);
ALTER TABLE `cdr_tar_part`  DROP COLUMN `ID`, MODIFY COLUMN `cdr_ID` BIGINT UNSIGNED NOT NULL, DROP PRIMARY KEY, ADD INDEX `calldate`(`calldate`);

MESSAGE

ALTER TABLE `message`  MODIFY COLUMN `ID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `message_next_1`  MODIFY COLUMN `cdr_ID` BIGINT UNSIGNED NOT NULL;

REGISTER

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;


Following will find AUTO_INCREMENT values in tables

if value hits the 4294967295 for any cdr table - all the tables from section CDR (above) needs to be upgraded. When it is true for any register table, you need to alter tables from REGISTER section.

mysql> select `TABLE_NAME`,`AUTO_INCREMENT` from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'voipmonitor' and TABLE_NAME like '%cdr%' or TABLE_NAME like '%register%';


DROP triggers

when sniffers reports error after altering the tables from int to bigint, drop the triggers containing the auto_increment

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

Example of removing the triggers

DROP TRIGGER voipmonitor.cdr_proxy_auto_increment_tr;
DROP TRIGGER voipmonitor.cdr_siphistory_auto_increment_tr;
DROP TRIGGER voipmonitor.cdr_tar_part_auto_increment_tr;