[SR-Users] usrloc module expires column issues
Daniel-Constantin Mierla
miconda at gmail.com
Tue Nov 8 14:57:52 CET 2016
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 at 1.1.1.1:5060',1478611664,1.000000
> ,'56b9ca823f9a11d361e184cc5eed07fc at 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 at 1.1.1.1:5060'
> where `username`='sip-server-ivr-01' AND
> `contact`='sip:s at 1.1.1.1:5060' AND `call
>
> id`='56b9ca823f9a11d361e184cc5eed07fc at 1.1.1.1' AND
> `domain`='sip-server-gw-01.test.voipdomain.local'
>
>
>
>
>
> *From:*Daniel-Constantin Mierla [mailto:miconda at gmail.com]
> *Sent:* dinsdag 8 november 2016 12:38
> *To:* Grant Bagdasarian <gb at cm.nl>; Kamailio (SER) - Users Mailing
> List <sr-users at 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 at gmail.com]
> *Sent:* dinsdag 8 november 2016 11:32
> *To:* Grant Bagdasarian <gb at cm.nl> <mailto:gb at cm.nl>; Kamailio
> (SER) - Users Mailing List <sr-users at lists.sip-router.org>
> <mailto:sr-users at 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 at 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 at lists.sip-router.org>
> <mailto:sr-users at 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 list
>
> sr-users at lists.sip-router.org
> <mailto:sr-users at lists.sip-router.org>
>
> http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
>
>
>
>
>
> --
>
> Daniel-Constantin Mierla
>
> http://twitter.com/#!/miconda <http://twitter.com/#%21/miconda> - http://www.linkedin.com/in/miconda
>
> Kamailio Advanced Training, Berlin, Oct 24-26, 2016 - http://www.asipto.com
>
>
>
>
> --
>
> Daniel-Constantin Mierla
>
> http://twitter.com/#!/miconda <http://twitter.com/#%21/miconda> - http://www.linkedin.com/in/miconda
>
> Kamailio Advanced Training, Berlin, Nov 28-30, 2016 - http://www.asipto.com
>
>
>
> --
> Daniel-Constantin Mierla
> http://twitter.com/#!/miconda <http://twitter.com/#%21/miconda> - http://www.linkedin.com/in/miconda
> Kamailio 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sip-router.org/pipermail/sr-users/attachments/20161108/8469fd6a/attachment.html>
More information about the sr-users
mailing list