30 Aug
2012
30 Aug
'12
10:09 a.m.
Hello,
On 8/29/12 9:15 PM, Øyvind Kolbu wrote:
No, you have to do some C coding. The target was to offer high
availability to access a database server, when one is down, try next.
Perhaps you can add some cross replication at database server layer.
I am not a postgres user, but if it exports it, then it is used
automatically by usrloc module.
On 2012-08-29 at 20:16, Daniel-Constantin Mierla
wrote:
this might not be very suitable for db_cluster usage if one db server is
done, because right now the reading is done to the first available server.
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.
IIRC, if the operation was successful, affected rows checks the last
used connection. In a cluster, the operation is considered to be
successful when int succeeded on a db server node.
I think you are looking more for replication, rather that high
availability of the db layer. This is not done by db_cluster, if the
nodes are up, then all should be fine, but if one goes down and then
back up, its content is not synchronized, should be done by the db
server itself.
Cheers,
Daniel
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(a)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
--
Daniel-Constantin Mierla - http://www.asipto.com
http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
Kamailio Advanced Training, Berlin, Nov 5-8, 2012 - http://asipto.com/u/kat