On 2012-08-29 at 20:16, Daniel-Constantin Mierla wrote:
is usrloc module configured in db only mode?
Yes, db_mode is 3.
Read operation cannot be done in 'parallel' fashion of the cluster, it is only serial or round robin, stopping at first successful query (even when there is no data records in the result of the query).
Not related to this thread, but would it be possible to force it to try until data is found?
If usrloc keeps also the records in memory, there is no select done at runtime, only updates, which are write operations.
db_check_update is only for SQL UPDATE command, in MySQL you can see the number of affected rows and if it is 0, then the module does an insert (another write operation).
I use Postgres, which as also returns the number of affected rows. Though perhaps Kamailio doesn't support it for Postgres.
From my previous post:
DEBUG: db_postgres [km_dbase.c:224]: sending query ok: 0xb7c9df08 (7) - [update [...]] DEBUG: <core> [db_res.c:118]: allocate 28 bytes for result set at 0xb7cd2d90 DEBUG: db_postgres [km_dbase.c:494]: 0xb7c9df08 PQresultStatus(PGRES_COMMAND_OK) PQgetResult(0x9058e78) DEBUG: db_postgres [km_dbase.c:393]: PQclear(0x9058e78) result set DEBUG: <core> [db_res.c:81]: freeing 0 columns DEBUG: <core> [db_res.c:136]: freeing result set at 0xb7cd2d90
but it did succed and updated one row, though not reflected in the logs.
So, the question is why do you think the check is done on one 'read table', or you don't refer to the check of affected rows for UPDATE?
Hm, perhaps I was a bit confused on how the affected rows works. Anyhow if a row is missing on a secondary server the UPDATE will fail and it will never be fixed with an INSERT.
Similar problem if the inital SELECT from the read-only sql-server returns noting, it will perform an INSERT on each of the write-servers, without first attempting to UPDATE. This will create duplicate contacts if the contact already exists on the write-servers. The stale entry will of course be pruned after a while..
Below is from another experiment where I deleted the row in the location-table on the primary server, but left it on the secondary server:
DEBUG: auth [api.c:218]: check_response: Authorization is OK [...] DEBUG: db_cluster [dbcl_api.c:436]: use table (voip_location) - cluster [data2] DEBUG: db_cluster [dbcl_api.c:455]: set write table (voip_location) - cluster [data2] (9/0) DEBUG: db_cluster [dbcl_api.c:455]: set write table (voip_location) - cluster [data2] (9/1) DEBUG: db_cluster [dbcl_api.c:445]: set read table (voip_location) - cluster [data2] (8/0) DEBUG: db_cluster [dbcl_api.c:445]: set read table (voip_location) - cluster [data2] (7/0) DEBUG: db_cluster [dbcl_api.c:299]: executing db cluster query command DEBUG: db_cluster [dbcl_api.c:300]: serial operation - cluster [data2] (8/0) DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 7 chars, out: 7 chars DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 20 chars, out: 20 chars DEBUG: db_postgres [km_dbase.c:224]: sending query ok: 0xb7d03fd0 (7) - [select [... long statement ...]] [...] DEBUG: db_postgres [km_res.c:211]: no rows returned from the query DEBUG: db_postgres [km_dbase.c:393]: PQclear(0x933fe78) result set DEBUG: usrloc [udomain.c:599]: aor 2549619@hometest.voip.uio.no not found in table voip_location DEBUG: db_cluster [dbcl_api.c:284]: executing db cluster free-result command [...] DEBUG: <core> [sruid.c:176]: new sruid is [uloc-503e5fe0-26a8-4] (4 / 20) DEBUG: db_cluster [dbcl_api.c:436]: use table (voip_location) - cluster [data2] DEBUG: db_cluster [dbcl_api.c:455]: set write table (voip_location) - cluster [data2] (9/0) DEBUG: db_cluster [dbcl_api.c:455]: set write table (voip_location) - cluster [data2] (9/1) DEBUG: db_cluster [dbcl_api.c:445]: set read table (voip_location) - cluster [data2] (8/0) DEBUG: db_cluster [dbcl_api.c:445]: set read table (voip_location) - cluster [data2] (7/0) DEBUG: db_cluster [dbcl_api.c:333]: executing db cluster insert command DEBUG: db_cluster [dbcl_api.c:334]: parallel operation - cluster [data2] (9/0) DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 7 chars, out: 7 chars DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 26 chars, out: 26 chars DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 41 chars, out: 41 chars DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 42 chars, out: 42 chars DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 24 chars, out: 24 chars DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 20 chars, out: 20 chars DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 20 chars, out: 20 chars DEBUG: db_postgres [km_dbase.c:224]: sending query ok: 0xb7d03f08 (7) - [insert into voip_location [... long statment ..]] DEBUG: <core> [db_res.c:118]: allocate 28 bytes for result set at 0xb7d076b8 DEBUG: db_postgres [km_dbase.c:494]: 0xb7d03f08 PQresultStatus(PGRES_COMMAND_OK) PQgetResult(0x933fe78) DEBUG: db_postgres [km_dbase.c:393]: PQclear(0x933fe78) result set DEBUG: <core> [db_res.c:81]: freeing 0 columns DEBUG: <core> [db_res.c:136]: freeing result set at 0xb7d076b8 DEBUG: db_cluster [dbcl_api.c:334]: parallel operation - cluster [data2] (9/1) DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 7 chars, out: 7 chars DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 26 chars, out: 26 chars DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 41 chars, out: 41 chars DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 42 chars, out: 42 chars DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 24 chars, out: 24 chars DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 20 chars, out: 20 chars DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 20 chars, out: 20 chars DEBUG: db_postgres [km_dbase.c:224]: sending query ok: 0xb7d03fd0 (7) - [insert into voip_location [ ... another blind insert ]] DEBUG: <core> [db_res.c:118]: allocate 28 bytes for result set at 0xb7d076b8 DEBUG: db_postgres [km_dbase.c:494]: 0xb7d03fd0 PQresultStatus(PGRES_COMMAND_OK) PQgetResult(0x933fe78) DEBUG: db_postgres [km_dbase.c:393]: PQclear(0x933fe78) result set DEBUG: <core> [db_res.c:81]: freeing 0 columns DEBUG: <core> [db_res.c:136]: freeing result set at 0xb7d076b8