Location table update instead of insert

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Location table update instead of insert

Schneur Rosenberg
Hi, the last few days my location table displays only a fraction of
the registered users, I did some digging and I enabled query logging
on MySQL and I can see that OpenSIPS is doing updates instead of
inserts, and being that a update on a WHERE that does not

My system is a cluster of 2 OpenSIPS servers version 2.4.1 running
"full-sharing" "cluster_mode", "restart_persistency" is set to
"load-from-sql", "sql_write_mode" is set to "write-back" and
"skip_replicated_db_ops" is set to "1" as of now its a active/passive
setup, when a server comes up it loads what its got in DB and then
after approx 3 minutes after all users have registered to the other
server the server will be syced.

I tried bringing down one server at a time and the problem persists, I
suspect that when it comes up and it gets synced over binary it
assumes those users are in the DB and when the server becomes ACTIVE
it runs a update instead of a insert, I assume that if I would bring
down the entire cluster and delete the rows in the location table and
bring it back up it will operate properly but I would really love to
avoid that, is there another way?

being that "contact_id"  is a PRIMARY KEY, is there a reason OpenSIPS
is not using  a INSERT with "ON DUPLICATE KEY"? or perhaps OpenSIPS
can run a INSERT if no rows were updated? and how can I avoid this in
the future?

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: Location table update instead of insert

Liviu Chircu
Hi Schneur,

Does each of your active/passive servers have its own DB, or do they
share one?

If they have individual DBs (which seems like it), then you should *not*
enable "skip_replicated_db_ops".  Upon a node restart and sync
operation, the node will properly dump its contacts to DB with INSERTs,
followed by UPDATEs.

If they use a shared DB: we do not recommend this setup anymore, however
"skip_replicated_db_ops" should do the job.  After sync'ing up, the
receiver node will avoid key conflicts by not re-doing the INSERTs when
its boots up.  Later, when it switches to primary, the UPDATEs will work
just fine.

Best regards,

Liviu Chircu
OpenSIPS Developer
http://www.opensips-solutions.com

On 23.10.2018 10:39, Schneur Rosenberg wrote:

> Hi, the last few days my location table displays only a fraction of
> the registered users, I did some digging and I enabled query logging
> on MySQL and I can see that OpenSIPS is doing updates instead of
> inserts, and being that a update on a WHERE that does not
>
> My system is a cluster of 2 OpenSIPS servers version 2.4.1 running
> "full-sharing" "cluster_mode", "restart_persistency" is set to
> "load-from-sql", "sql_write_mode" is set to "write-back" and
> "skip_replicated_db_ops" is set to "1" as of now its a active/passive
> setup, when a server comes up it loads what its got in DB and then
> after approx 3 minutes after all users have registered to the other
> server the server will be syced.
>
> I tried bringing down one server at a time and the problem persists, I
> suspect that when it comes up and it gets synced over binary it
> assumes those users are in the DB and when the server becomes ACTIVE
> it runs a update instead of a insert, I assume that if I would bring
> down the entire cluster and delete the rows in the location table and
> bring it back up it will operate properly but I would really love to
> avoid that, is there another way?
>
> being that "contact_id"  is a PRIMARY KEY, is there a reason OpenSIPS
> is not using  a INSERT with "ON DUPLICATE KEY"? or perhaps OpenSIPS
> can run a INSERT if no rows were updated? and how can I avoid this in
> the future?
>
> _______________________________________________
> Users mailing list
> [hidden email]
> http://lists.opensips.org/cgi-bin/mailman/listinfo/users

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: Location table update instead of insert

Schneur Rosenberg
I do have a shared DB and "skip_replicated_db_ops" is set to "1" and I
have the problem that the lots of users were erased from the location
table somehow and the server thinks the record is there and just does
a update, I think the system should do a insert if it sees that 0 rows
were updated during the update query
On Sat, Oct 27, 2018 at 5:58 PM Liviu Chircu <[hidden email]> wrote:

>
> Hi Schneur,
>
> Does each of your active/passive servers have its own DB, or do they
> share one?
>
> If they have individual DBs (which seems like it), then you should *not*
> enable "skip_replicated_db_ops".  Upon a node restart and sync
> operation, the node will properly dump its contacts to DB with INSERTs,
> followed by UPDATEs.
>
> If they use a shared DB: we do not recommend this setup anymore, however
> "skip_replicated_db_ops" should do the job.  After sync'ing up, the
> receiver node will avoid key conflicts by not re-doing the INSERTs when
> its boots up.  Later, when it switches to primary, the UPDATEs will work
> just fine.
>
> Best regards,
>
> Liviu Chircu
> OpenSIPS Developer
> http://www.opensips-solutions.com
>
> On 23.10.2018 10:39, Schneur Rosenberg wrote:
> > Hi, the last few days my location table displays only a fraction of
> > the registered users, I did some digging and I enabled query logging
> > on MySQL and I can see that OpenSIPS is doing updates instead of
> > inserts, and being that a update on a WHERE that does not
> >
> > My system is a cluster of 2 OpenSIPS servers version 2.4.1 running
> > "full-sharing" "cluster_mode", "restart_persistency" is set to
> > "load-from-sql", "sql_write_mode" is set to "write-back" and
> > "skip_replicated_db_ops" is set to "1" as of now its a active/passive
> > setup, when a server comes up it loads what its got in DB and then
> > after approx 3 minutes after all users have registered to the other
> > server the server will be syced.
> >
> > I tried bringing down one server at a time and the problem persists, I
> > suspect that when it comes up and it gets synced over binary it
> > assumes those users are in the DB and when the server becomes ACTIVE
> > it runs a update instead of a insert, I assume that if I would bring
> > down the entire cluster and delete the rows in the location table and
> > bring it back up it will operate properly but I would really love to
> > avoid that, is there another way?
> >
> > being that "contact_id"  is a PRIMARY KEY, is there a reason OpenSIPS
> > is not using  a INSERT with "ON DUPLICATE KEY"? or perhaps OpenSIPS
> > can run a INSERT if no rows were updated? and how can I avoid this in
> > the future?
> >
> > _______________________________________________
> > Users mailing list
> > [hidden email]
> > http://lists.opensips.org/cgi-bin/mailman/listinfo/users
>
> _______________________________________________
> Users mailing list
> [hidden email]
> http://lists.opensips.org/cgi-bin/mailman/listinfo/users

_______________________________________________
Users mailing list
[hidden email]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users