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.password.............@....def.vox180internal.subscriber.subscriber mailto:.....H....def.vox180internal.subscriber.subscriber.password.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 ,'757d8e8a-fccff6d4-bca1e1ad@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@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?
Anyone got any ideas? Has OpenSER ACTUALLY been tested with multiple systems all accessing a common MySQL database?
Doug.
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.password.............@....def.vox180internal.subscriber.subscriber mailto:.....H....def.vox180internal.subscriber.subscriber.password.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 ,'757d8e8a-fccff6d4-bca1e1ad@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@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.
regards, bogdan
-
Bogdan-Andrei Iancu -
Douglas Garstang