New subject: [Users] OpenSER MySQL Updates - Weird

27 Mar 2006

...
   -----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,received,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.

RE: [Users] OpenSER MySQL Updates - Weird