New subject: [Users] OpenSER MySQL Updates - Weird

28 Mar 2006

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(a)openser.org
 Subject: RE: [Users] OpenSER MySQL Updates - Weird

 > -----Original Message-----
> From: Bogdan-Andrei Iancu [mailto:bogdan@voice-system.ro]
> Sent: Monday, March 27, 2006 7:37 AM
> To: Douglas Garstang
> Cc: users(a)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@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.............@....def.vox180internal.subscriber.subscrib
 er 
 <mailto:.....H....def.vox180internal.subscriber.subscriber.pas
sword.password.............@....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@xxx.187.128.95','2006-03-23 17:39:11',-1.00      
,&#39;757d8e8a-fccff6d4-bca1e1ad(a)216.187.128.95&#39;,20,0,&#39;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@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. > 
> 