Hello, ok -- that's clear an issue due to new rules with latest versions of mysql, definitely we need to investigate a bit more in this direction.
Cheers, Daniel On 10/11/16 08:56, Grant Bagdasarian wrote: > > 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:mico...@gmail.com] > *Sent:* dinsdag 8 november 2016 14:58 > *To:* Grant Bagdasarian <g...@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 > <sip:s@1.1.1.1:5060%27,1478611664,1.000000> > ,'56b9ca823f9a11d361e184cc5eed07fc@1.1.1.1 > <mailto: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 > > <udp:10.14.34.211:5060%27,NULL,1478611563,%27uloc-5821d267-22ed-1%27,NULL,0,0,-1,0,0,%27sip-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 > <mailto:56b9ca823f9a11d361e184cc5eed07fc@1.1.1.1>' AND > `domain`='sip-server-gw-01.test.voipdomain.local' > > > > > > *From:*Daniel-Constantin Mierla [mailto:mico...@gmail.com] > *Sent:* dinsdag 8 november 2016 12:38 > *To:* Grant Bagdasarian <g...@cm.nl> <mailto:g...@cm.nl>; Kamailio > (SER) - Users Mailing List <sr-users@lists.sip-router.org> > <mailto: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:mico...@gmail.com] > *Sent:* dinsdag 8 november 2016 11:32 > *To:* Grant Bagdasarian <g...@cm.nl> <mailto:g...@cm.nl>; Kamailio > (SER) - Users Mailing List <sr-users@lists.sip-router.org> > <mailto: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-boun...@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> > <mailto: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 list > > sr-users@lists.sip-router.org > <mailto:sr-users@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://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
_______________________________________________ SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list sr-users@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
