Hi Daniel,
Thank you! Changing to bigint in the DB fixed it. Both expires and last_modified now contain a unix timestamp.
Reloading also works now!
Regards,
Grant Bagdasarian
CM
From: Daniel-Constantin Mierla [mailto:miconda@gmail.com]
Sent: dinsdag 8 november 2016 14:58
To: Grant Bagdasarian <gb@cm.nl>; Kamailio (SER) - Users Mailing List <sr-users@lists.sip-router.org>
Subject: Re: [SR-Users] usrloc module expires column issues
Hello,
so it seems that inserting a timestamp value to a datetime column is no longer accepted, but no complaints in the syslog that value is invalid or db operation has failed, right? Nice behaviour from db server, I would say ...
An option for now could be setting expires_type to bigint:
https://www.kamailio.org/docs/modules/stable/modules/usrloc.html#usrloc.p.expires_type
But this kind of behaviour for datetime from database server needs to be analyzed for several modules and fixed if it's the case.
Cheers,
Daniel
On 08/11/16 14:33, Grant Bagdasarian wrote:
Hi Daniel,
Nice tip!
I noticed the following:
The expires and last_modified columns are of DATETIME type, but the insert and update statements pass a unix timestamp.
I can change the datatype to a varchar for the expires and last_modified columns, but I’m not sure if I will break the usrloc module when preloading the rows.
kamailio -V
version: kamailio 4.4.3 (x86_64/linux) 5a2195
flags: STATS: Off, USE_TCP, USE_TLS, USE_SCTP, TLS_HOOKS, USE_RAW_SOCKS, DISABLE_NAGLE, USE_MCAST, DNS_IP_HACK, SHM_MEM, SHM_MMAP, PKG_MALLOC, Q_MALLOC, F_MALLOC, TLSF_MALLOC, DBG_SR_MEMORY, USE_FUTEX, FAST_LOCK-ADAPTIVE_WAIT, USE_DNS_CACHE, USE_DNS_FAILOVER, USE_NAPTR, USE_DST_BLACKLIST, HAVE_RESOLV_RES
ADAPTIVE_WAIT_LOOPS=1024, MAX_RECV_BUFFER_SIZE 262144, MAX_LISTEN 16, MAX_URI_SIZE 1024, BUF_SIZE 65535, DEFAULT PKG_SIZE 8MB
poll method support: poll, epoll_lt, epoll_et, sigio_rt, select.
id: 5a2195
compiled on 16:42:49 Sep 26 2016 with gcc 4.9.2
cat /usr/local/src/kamailio-4.4.3/utils/kamctl/mysql/usrloc-create.sql
CREATE TABLE `location` (
`id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
`ruid` VARCHAR(64) DEFAULT '' NOT NULL,
`username` VARCHAR(64) DEFAULT '' NOT NULL,
`domain` VARCHAR(64) DEFAULT NULL,
`contact` VARCHAR(255) DEFAULT '' NOT NULL,
`received` VARCHAR(128) DEFAULT NULL,
`path` VARCHAR(512) DEFAULT NULL,
`expires` DATETIME DEFAULT '2030-05-28 21:32:15' NOT NULL,
`q` FLOAT(10,2) DEFAULT 1.0 NOT NULL,
`callid` VARCHAR(255) DEFAULT 'Default-Call-ID' NOT NULL,
`cseq` INT(11) DEFAULT 1 NOT NULL,
`last_modified` DATETIME DEFAULT '1900-01-01 00:00:01' NOT NULL,
`flags` INT(11) DEFAULT 0 NOT NULL,
`cflags` INT(11) DEFAULT 0 NOT NULL,
`user_agent` VARCHAR(255) DEFAULT '' NOT NULL,
`socket` VARCHAR(64) DEFAULT NULL,
`methods` INT(11) DEFAULT NULL,
`instance` VARCHAR(255) DEFAULT NULL,
`reg_id` INT(11) DEFAULT 0 NOT NULL,
`server_id` INT(11) DEFAULT 0 NOT NULL,
`connection_id` INT(11) DEFAULT 0 NOT NULL,
`keepalive` INT(11) DEFAULT 0 NOT NULL,
`partition` INT(11) DEFAULT 0 NOT NULL,
CONSTRAINT ruid_idx UNIQUE (`ruid`)
);
insert into `location` (`username`,`contact`,`expires`,`q`,`callid`,`cseq`,`flags`,`cflags`,`user_agent`,`received`,`path`,`socket`,`methods`,`last_modified`,`ruid`,`instance`,`reg_id`,`server_id`,`connection_id`,`keepalive`,`partition`,`domain` )
values ('sip-server-ivr-01','sip:s@1.1.1.1:5060',1478611664,1.000000 ,'56b9ca823f9a11d361e184cc5eed07fc@1.1.1.1',15819,0,0,'Asterisk PBX 13.9.1',NULL,NULL,'udp:10.14.34.211:5060',NULL,1478611563,'uloc-5821d267-22ed-1',NULL,0,0,-1,0,0,'sip-server-gw-01.test.voipdomain.local')
update `location` set `path`=NULL,`expires`=1478611744,`q`=1.000000 ,`cseq`=15821,`flags`=0,`cflags`=0,`user_agent`='Asterisk PBX 13.9.1',`received`=NULL,`socket`='udp:10.14.34.211:5060',`methods`=NULL,`last_modified`=14786116
49,`ruid`='uloc-5821d267-22ed-1',`instance`=NULL,`reg_id`=0,`server_id`=0,`connection_id`=-1,`keepalive`=0,`contact`='sip:s@1.1.1.1:5060' where `username`='sip-server-ivr-01' AND `contact`='sip:s@1.1.1.1:5060' AND `call
id`='56b9ca823f9a11d361e184cc5eed07fc@1.1.1.1' AND `domain`='sip-server-gw-01.test.voipdomain.local'
From: Daniel-Constantin Mierla [mailto:miconda@gmail.com]
Sent: dinsdag 8 november 2016 12:38
To: Grant Bagdasarian <gb@cm.nl>; Kamailio (SER) - Users Mailing List <sr-users@lists.sip-router.org>
Subject: Re: [SR-Users] usrloc module expires column issues
Hello,
the version I noticed some changes in dealing with unsigned int values was 5.7 ...
Can you enable sql query logging and see what's the insert statement for location table? Or, if the connection to the database server is over IP (and not tls), then you can do ngrep on port 3306 (iirc by heart the default port for mysql) -- you should be able to spot the text query along some binary headers.
It would be good to see if the sql is ok or broken, to know where to look further.
Cheers,
Daniel
On 08/11/16 11:36, Grant Bagdasarian wrote:
Hi Daniel,
My apologies, we’re not running MariaDB, but a regular instance of MySQL:
mysql -v
mysql Ver 14.14 Distrib 5.5.49, for debian-linux-gnu (x86_64) using readline 6.3
Regards,
Grant
From: Daniel-Constantin Mierla [mailto:miconda@gmail.com]
Sent: dinsdag 8 november 2016 11:32
To: Grant Bagdasarian <gb@cm.nl>; Kamailio (SER) - Users Mailing List <sr-users@lists.sip-router.org>
Subject: Re: [SR-Users] usrloc module expires column issues
Hello,
I haven't encountered such problem so far and no similar report was done to kamailio project recently. I noticed that newer versions of mysql (maybe mariadb as well) are behaving differently than old ones -- what mariadb version do you run?
Cheers,
Daniel
On 08/11/16 10:42, Grant Bagdasarian wrote:
Hi Daniel,
Sorry for the late reply.
We’re using a Galera Cluster based on Maria DB.
Regards,
Grant Bagdasarian
From: sr-users [mailto:sr-users-bounces@lists.sip-router.org] On Behalf Of Daniel-Constantin Mierla
Sent: donderdag 6 oktober 2016 12:50
To: Kamailio (SER) - Users Mailing List <sr-users@lists.sip-router.org>
Subject: Re: [SR-Users] usrloc module expires column issues
Hello,
what database server are you using?
Cheers,
Daniel
On 26/09/16 16:50, Grant Bagdasarian wrote:
Hello,
When saving in the location table, the expires and last_modified columns contain the value 000-00-00 00:00:00 which causes issues when Kamailio is restarted and the preload modparam is set to load the location table from db in memory at startup.
I’ve already upgraded Kamailio to 4.4.3, and used the dbtables script from version 4.4 to recreate the location table, but without success.
Anyone else having this issue? Any resolution for it?
Regards,
Grant Bagdasarian
CM
_______________________________________________SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing listsr-users@lists.sip-router.orghttp://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
--Daniel-Constantin Mierlahttp://twitter.com/#!/miconda - http://www.linkedin.com/in/micondaKamailio Advanced Training, Berlin, Oct 24-26, 2016 - http://www.asipto.com
--Daniel-Constantin Mierlahttp://twitter.com/#!/miconda - http://www.linkedin.com/in/micondaKamailio Advanced Training, Berlin, Nov 28-30, 2016 - http://www.asipto.com
--Daniel-Constantin Mierlahttp://twitter.com/#!/miconda - http://www.linkedin.com/in/micondaKamailio Advanced Training, Berlin, Nov 28-30, 2016 - http://www.asipto.com
--
Daniel-Constantin Mierla
http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
Kamailio Advanced Training, Berlin, Nov 28-30, 2016 - http://www.asipto.com