[Users] OpenSER MySQL Updates - Weird

[Douglas Garstang]

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