[Users] OpenSER MySQL Updates - Weird

[Douglas Garstang]

Posted this yesterday. Didn't get a reply...

> Bogdan, thanks for the reply. I'm don't quite understand. I'm 
> using db_mode 1, which the docs say writes all updates 
> immediately to the database. A 'openserctl ul show' still 
> shows cached entries though. Why? Is db_mode 1 supposed to 
> cache at all? Which db_mode should I use so that I can have 
> two or more OpenSER systems safely accessing the same database?
> 
> Douglas.



> -----Original Message-----
> From: Douglas Garstang 
> Sent: Monday, March 27, 2006 8:24 AM
> To: 'Bogdan-Andrei Iancu'
> Cc: users at openser.org
> Subject: RE: [Users] OpenSER MySQL Updates - Weird
> 
> 
> >> -----Original Message-----
> >> From: Bogdan-Andrei Iancu [mailto:bogdan at voice-system.ro]
> >> Sent: Monday, March 27, 2006 7:37 AM
> >> To: Douglas Garstang
> >> Cc: users at openser.org
> >> Subject: Re: [Users] OpenSER MySQL Updates - Weird
> >> 
> >> 
> >> Hi Doug,
> >> 
> >> 
> >> Douglas Garstang wrote:
> >> 
> >> >I have a setup where we have three OpenSER 1.0.1 systems all 
> >> referring to a common MySQL database. There are some very 
> >> weird things happening...
> >> > 
> >> >I was regularly seeing errors from OpenSER that stated 
> >> > 
> >> >Mar 23 10:05:27 artemis openser[11950]: submit_query: 
> >> Duplicate entry '80014157--sip:80014157 at xxx.187.128.104' for 
> >> key 'PRIMARY'
> >> >Mar 23 10:05:27 artemis openser[11950]: db_insert: Error 
> >> while submitting query
> >> >Mar 23 10:05:27 artemis openser[11950]: 
> >> db_insert_ucontact(): Error while inserting contact
> >> >Mar 23 10:05:27 artemis openser[11950]: wb_timer: Error 
> >> while inserting contact into database
> >> >
> >> >When I ran an ngrep on port 3306 on MySQL I could clearly 
> >> see the OpenSER select query where it looked for user 
> >> 80014157. Here's an example of another one:
> >> > 
> >> >T 172.31.141.234:35385 -> 172.31.142.200:3306 [AP]
> >> >  ?....select password,rpid from subscriber where 
> username='80014074'
> >> ># 
> >> >T 172.31.142.200:3306 -> 172.31.141.234:35385 [AP]
> >> >  
> >> .....H....def.vox180internal.subscriber.subscriber.password.pa
> >> ssword............. at ....def.vox180internal.subscriber.subscrib
> >> er 
> >> <mailto:.....H....def.vox180internal.subscriber.subscriber.pas
> >sword.password............. at ....def.vox180internal.subscriber
> .subscriber> .
> >>  rpid.rpid...........................vDEPuURM4eXEw..........
> >>##
> >>T 172.31.141.234:35385 -> 172.31.142.200:3306 [AP]
> >>  H....insert into location 
> (username,contact,expires,q,callid,cseq,flags,user_agent,recei
> ved,socket,domain ) values ('80014074',
> >>  'sip:80014074 at xxx.187.128.95','2006-03-23 17:39:11',-1.00 
>     ,'757d8e8a-fccff6d4-bca1e1ad at 216.187.128.95',20,0,'PolycomSoundPo
> >>  
> intIP-SPIP_601-UA/1.6.3.0067',NULL,xxx.187.142.234_5060','ipt.
oneeighty.com')
> >># 
> >>T 172.31.142.200:3306 -> 172.31.141.234:35385 [AP]
> >>  c....&.#23000Duplicate entry 
> '80014074-ipt.oneeighty.com-sip:80014074 at 216.187.128.95' for 
> key 'PRIMARY'
> >> 
> >>You can quite clearly see that MySQL does NOT return a row, 
> and OpenSER happily goes and does an insert then. I can't 
> understand why MySQL >then complains about a duplicate key 
> error. Why would it do this when the row wasn't found, and 
> presumably the key doesn't exist?
> >>
> >your trace shows two queries:
> >1) a DB authentication
> >2) a usrloc updated.
> >
> >based on the information from cache, openser knows if it 
> should do an 
> >update or insert.
> >the problem is if you use 2 opensers on same DB, each 
> server, based on 
> >private cache, will know they have to do insert (the contact 
> is not in 
> >cache and DB). and you will end with 2 duplicated inserts.
> 
> Bogdan, thanks for the reply. I'm don't quite understand. I'm 
> using db_mode 1, which the docs say writes all updates 
> immediately to the database. A 'openserctl ul show' still 
> shows cached entries though. Why? Is db_mode 1 supposed to 
> cache at all? Which db_mode should I use so that I can have 
> two or more OpenSER systems safely accessing the same database?
> 
> Douglas.
> 
>