[sr-dev] git:master: modules:sipcapture: fixed rotation scripts (maxvalue) and SQL schema.

Module: sip-router Branch: master Commit: 6f8973719551dec797c89452ba1abb87ef1ad894 URL: http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=6f89737… Author: Alexandr Dubovikov &lt;alexandr.dubovikov(a)gmail.com&gt; Committer: Alexandr Dubovikov &lt;alexandr.dubovikov(a)gmail.com&gt; Date: Thu Sep 29 15:03:46 2011 +0200 modules:sipcapture: fixed rotation scripts (maxvalue) and SQL schema. Thanks Dorn B. &lt;djbinter(a)gmail.com&gt; for reports and fixes. --- modules/sipcapture/examples/partrotate_todays.pl | 5 +++-- .../examples/partrotate_unixtimestamp.pl | 9 ++++++--- .../sipcapture/sql/create_part_unixtimestamp.sql | 7 ++----- modules/sipcapture/sql/sipcapture-create.sql | 8 ++------ 4 files changed, 13 insertions(+), 16 deletions(-) diff --git a/modules/sipcapture/examples/partrotate_todays.pl b/modules/sipcapture/examples/partrotate_todays.pl index f9e9059..e831743 100644 --- a/modules/sipcapture/examples/partrotate_todays.pl +++ b/modules/sipcapture/examples/partrotate_todays.pl @@ -74,8 +74,9 @@ for(my $i=0; $i<$newparts; $i++) { if(!$exist) { - $query = "ALTER TABLE ".$table." ADD PARTITION (PARTITION ".$newpartname - ."\n VALUES LESS THAN (".$curtodays.") ENGINE = MyISAM)"; + # Fix MAXVALUE. Thanks Dorn B. &lt;djbinter(a)gmail.com&gt; for report and fix. + $query = "ALTER TABLE ".$table." REORGANIZE PARTITION pmax INTO (PARTITION ".$newpartname + ."\n VALUES LESS THAN (".$curtodays.") ENGINE = MyISAM, PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM)"; $db->do($query); if (!$db->{Executed}) { print "Couldn't add partition: $newpartname\n"; diff --git a/modules/sipcapture/examples/partrotate_unixtimestamp.pl b/modules/sipcapture/examples/partrotate_unixtimestamp.pl index 40d36a4..6be3a66 100644 --- a/modules/sipcapture/examples/partrotate_unixtimestamp.pl +++ b/modules/sipcapture/examples/partrotate_unixtimestamp.pl @@ -82,10 +82,13 @@ for(my $i=0; $i<$newparts; $i++) { $exist+=0; if(!$exist) { - - $query = "ALTER TABLE ".$table." ADD PARTITION (PARTITION ".$newpartname - ."\n VALUES LESS THAN (".$curtstamp.") ENGINE = MyISAM)"; + + # Fix MAXVALUE. Thanks Dorn B. &lt;djbinter(a)gmail.com&gt; for report and fix. + $query = "ALTER TABLE ".$table." REORGANIZE PARTITION pmax INTO (PARTITION ".$newpartname + ."\n VALUES LESS THAN (".$curtstamp.") ENGINE = MyISAM, PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM)"; + $db->do($query); + if (!$db->{Executed}) { print "Couldn't add partition: $newpartname\n"; } diff --git a/modules/sipcapture/sql/create_part_unixtimestamp.sql b/modules/sipcapture/sql/create_part_unixtimestamp.sql index fdcc895..a7f8645 100644 --- a/modules/sipcapture/sql/create_part_unixtimestamp.sql +++ b/modules/sipcapture/sql/create_part_unixtimestamp.sql @@ -5,7 +5,7 @@ /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sip_capture` ( - `id` int(20) NOT NULL AUTO_INCREMENT, + `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `micro_ts` bigint(18) NOT NULL DEFAULT '0', `method` varchar(50) NOT NULL DEFAULT '', @@ -42,7 +42,7 @@ CREATE TABLE `sip_capture` ( `rtp_stat` varchar(256) NOT NULL, `type` int(2) NOT NULL, `node` varchar(125) NOT NULL, - `msg` longblob NOT NULL, + `msg` blob NOT NULL, PRIMARY KEY (`id`,`date`), KEY `ruri_user` (`ruri_user`), KEY `from_user` (`from_user`), @@ -81,6 +81,3 @@ PARTITION p2011082523 VALUES LESS THAN (1314298800) ENGINE = MyISAM, PARTITION pmax VALUES LESS THAN (MAXVALUE) ); - -/* if your mysql < 5.5 drop maxvalue partition */ -/* alter table homer_capture drop partition pmax; */ diff --git a/modules/sipcapture/sql/sipcapture-create.sql b/modules/sipcapture/sql/sipcapture-create.sql index e12a382..155dff2 100644 --- a/modules/sipcapture/sql/sipcapture-create.sql +++ b/modules/sipcapture/sql/sipcapture-create.sql @@ -4,7 +4,7 @@ DROP TABLE IF EXISTS `sip_capture`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sip_capture` ( - `id` int(20) NOT NULL AUTO_INCREMENT, + `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `micro_ts` bigint(18) NOT NULL DEFAULT '0', `method` varchar(50) NOT NULL DEFAULT '', @@ -41,7 +41,7 @@ CREATE TABLE `sip_capture` ( `rtp_stat` varchar(256) NOT NULL, `type` int(2) NOT NULL, `node` varchar(125) NOT NULL, - `msg` longblob NOT NULL, + `msg` blob NOT NULL, PRIMARY KEY (`id`,`date`), KEY `ruri_user` (`ruri_user`), KEY `from_user` (`from_user`), @@ -66,7 +66,3 @@ PARTITION p20110902 VALUES LESS THAN (734748) ENGINE = MyISAM, PARTITION pmax VALUES LESS THAN (MAXVALUE) ); -/* alter table homer_capture add partition (PARTITION p20110903 VALUES LESS THAN (734749) ENGINE = MyISAM); */ -/* if your mysql < 5.5 drop maxvalue partition */ -/* alter table sip_capture drop partition pmax; */ -