Difference between revisions of "Upgrade to bigint"
Jump to navigation
Jump to search
(Created page with "== 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 TABL...") |
|||
| (3 intermediate revisions by one other user not shown) | |||
| Line 6: | Line 6: | ||
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_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_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`); | + | ALTER TABLE `cdr_siphistory` 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 `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; | 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; | + | |
| + | == 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; | 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; | ||
Latest revision as of 17:48, 24 April 2018
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;