I'm trying to finish developing a kamailio server cluster using database replication (db_mode=3). One of the issues is "split brain" when there is network connection problem that prevents the database servers from replicating data to each other for some time. This is especially bad for the serial int type primary keys defined in current kamailio tables. When there is a conflict in serial int primary keys, the record with the latest timestamp would win, but that record may not always be the correct one, or may not even for the same user. It seem the best solution to this problem is to use globally unique id as primary key instead of serial int.
I have tried to change the id in location table from serial int to uuid, and tested with pjsib clients. Things seem to work. uuid is supported by most common databases now (postgresql and mysql at least). Wonder if it is a good idea to change all serial int to uuid for all kamailio tables. I didn't verify all modules, but it seems the db modules can handle all db types without any code change. If I missed anything, or there is some spot we need to make code change, please let me know.
Here is the change I made to location table (postgresql). current id: id SERIAL PRIMARY KEY NOT NULL, to: id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/issues/274
I don't think this is realistic at all, given the enormous amount of software infrastructure out there (e.g. various web MVC frameworks, ORMs, database APIs) that take numerical primary keys and/or row identifiers for granted.
Moreover, while it is quite possible that all or almost all major RDBMs have some way of generating a textual GUID these days, the method and the resulting token length is likely to vary considerably. Highly generic database interactions are an important design tenet of the intermediate Kamailio database APIs.
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/issues/274#issuecomment-128242304
Appreciate the insights. Hopefully, all RDBMs would converge on UUID standard such as https://www.ietf.org/rfc/rfc4122.txt. PostgreSQL and MySQL do support RFC 4122 in current releases. But I'd agree UUID is far less prevalent than serial int as primary key.
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/issues/274#issuecomment-128409655
First, this kind of debate is not appropriate for tracker, it is a discussion for mailing lists. Here we track bugs or request for feature, not using it as a forum.
Column id is not used internally in most of the modules, but some do and I guess they need int. usrloc doesn't use it. On the other hand, mysql has the option to set the initial and increment value, so if you can set to add 10 instead of 1 and each server start with a different value between 1 and 10.
For further discussions, use mailing list. Going to close here.
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/issues/274#issuecomment-128690527
Closed #274.
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/issues/274#event-376282460
-
Alex Balashov -
Daniel-Constantin Mierla
-
mading087