31 Jul
2009
31 Jul
'09
9:05 a.m.
Hi!
Some years ago I developed a little patch that allows to add multiple
MySQL servers to the MySQL database backend, so if the first server
isn't reachable, perdition falls back to the second etc.
I was sure, that I sent this patch to Simon or to this list, but the
patch never appeared in the upstream source and when I checked all my
outbound mail, I didn't find a mail with this patch, so I seem to have
forgotten to do so.
Today I updated my patch to the current 1.17.1. Maybe someone else
will find this patch useful.
Tschoeeee
Roland
--
Roland Rosenfeld - Content Delivery - NED - Technik
NetCologne Gesellschaft für Telekommunikation mbH - HRB 25580, AG Köln
Am Coloneum 9 50829 Köln Tel.: +49-221-2222-373 Fax: +49-221-2222-7373
Geschäftsführer: Werner Hanf, Karl-Heinz Zankel
6:58 p.m.
Hi Roland,
this is what I'm just searching for month's.
I tried failover with mysql-proxy, but this patch let do this perdition
and there is no more overhead with mysql-proxy.
I find it usefull and will try it immediately.
Thanks
Urban
Roland Rosenfeld schrieb:
Hi!
Some years ago I developed a little patch that allows to add multiple
MySQL servers to the MySQL database backend, so if the first server
isn't reachable, perdition falls back to the second etc.
I was sure, that I sent this patch to Simon or to this list, but the
patch never appeared in the upstream source and when I checked all my
outbound mail, I didn't find a mail with this patch, so I seem to have
forgotten to do so.
Today I updated my patch to the current 1.17.1. Maybe someone else
will find this patch useful.
Tschoeeee
Roland
------------------------------------------------------------------------
______________________________________________
Perdition-users mailing list
Perdition-users(a)vergenet.net
http://lists.vergenet.net/listinfo/perdition-users
9:28 p.m.
Hi again,
I tried the patch and it works as expected. Great :-)
I have only one problem.
If I shut down the first DB backend (the hole server, it does no more
respond -> simulating a total network failure) it takes about 3 minutes
to automatically failover to the second backend.
This because "mysql_real_connect" uses the default tcp configuration of
the kernel.
net.ipv4.tcp_syn_retries = 5
This timeout seem's to long. A timeout of about 15 seconds should be enough.
Changing the default kernel tcp configuration is not possible for me,
because there are running some more virtual servers on this hardware,
and all are sharing the same kernel.
How do you deal with this timeout problem?
Thanks and regards
Urban
Roland Rosenfeld wrote:
Hi!
Some years ago I developed a little patch that allows to add multiple
MySQL servers to the MySQL database backend, so if the first server
isn't reachable, perdition falls back to the second etc.
I was sure, that I sent this patch to Simon or to this list, but the
patch never appeared in the upstream source and when I checked all my
outbound mail, I didn't find a mail with this patch, so I seem to have
forgotten to do so.
Today I updated my patch to the current 1.17.1. Maybe someone else
will find this patch useful.
Tschoeeee
Roland
------------------------------------------------------------------------
______________________________________________
Perdition-users mailing list
Perdition-users(a)vergenet.net
http://lists.vergenet.net/listinfo/perdition-users
12 Aug
12 Aug
8:19 a.m.
Hi Urban!
On Fri, 31 Jul 2009, Urban Loesch wrote:
I tried the patch and it works as expected. Great :-)
I have only one problem.
If I shut down the first DB backend (the hole server, it does no more
respond -> simulating a total network failure) it takes about 3 minutes
to automatically failover to the second backend.
This because "mysql_real_connect" uses the default tcp configuration of
the kernel.
net.ipv4.tcp_syn_retries = 5
This timeout seem's to long. A timeout of about 15 seconds should be enough.
Changing the default kernel tcp configuration is not possible for me,
because there are running some more virtual servers on this hardware,
and all are sharing the same kernel.
How do you deal with this timeout problem?
To say the truth, I never tried this out before. For testing purpose
I simply stopped the MySQL server, which works as expected.
But you are true, I can reproduce the problem with a switched off
server. Google only told me, that we aren't alone with this problem
(http://forums.mysql.com/read.php?45,62601,62601), but I didn't find a
simple solution for this :-(
Tschoeeee
Roland
--
Roland Rosenfeld - Content Delivery - NED - Technik
NetCologne Gesellschaft für Telekommunikation mbH - HRB 25580, AG Köln
Am Coloneum 9 50829 Köln Tel.: +49-221-2222-373 Fax: +49-221-2222-7373
Geschäftsführer: Werner Hanf, Karl-Heinz Zankel
3 Aug
3 Aug
12:48 a.m.
On Fri, Jul 31, 2009 at 11:05:35AM +0200, Roland Rosenfeld wrote:
Hi!
Some years ago I developed a little patch that allows to add multiple
MySQL servers to the MySQL database backend, so if the first server
isn't reachable, perdition falls back to the second etc.
I was sure, that I sent this patch to Simon or to this list, but the
patch never appeared in the upstream source and when I checked all my
outbound mail, I didn't find a mail with this patch, so I seem to have
forgotten to do so.
Today I updated my patch to the current 1.17.1. Maybe someone else
will find this patch useful.
Hi Roland,
this looks good to me, thanks for your patch :-)
Could I ask you to do two things?
1. Provide a Signed-off-by line as per the instructions at
section 5 of http://linux.yyz.us/patch-format.html
2. Provide a patch to perdition/perditiondb.5.
A separate patch with a Signed-off-by is fine.
Although if you'd prefer to revise the patch
below that is fine too.
Thanks
12 Aug
12 Aug
7:40 a.m.
Hi Simon!
On Mon, 03 Aug 2009, Simon Horman wrote:
1. Provide a Signed-off-by line as per the
instructions at
section 5 of http://linux.yyz.us/patch-format.html
2. Provide a patch to perdition/perditiondb.5.
A separate patch with a Signed-off-by is fine.
Although if you'd prefer to revise the patch
below that is fine too.
Okay, here we go:
The attached patch provides the feature to fall back from one MySQL
server to a list of others, it the first one is not reachable.
Signed-off-by: Roland Rosenfeld <rrosenfeld(a)netcologne.de>
diff -urNpd perdition-1.17.1/perdition/db/mysql/perditiondb_mysql.c
perdition-1.17.1-ro/perdition/db/mysql/perditiondb_mysql.c
--- perdition-1.17.1/perdition/db/mysql/perditiondb_mysql.c 2005-06-22 07:50:04.000000000
+0200
+++ perdition-1.17.1-ro/perdition/db/mysql/perditiondb_mysql.c 2009-07-31
10:51:24.000000000 +0200
@@ -42,7 +42,9 @@
static vanessa_dynamic_array_t *a=NULL;
-static char *dbhost = PERDITIONDB_MYSQL_DEFAULT_DBHOST;
+static vanessa_dynamic_array_t *dbhosts_array=NULL;
+static size_t dbhosts_count = 0;
+static char *dbhosts = PERDITIONDB_MYSQL_DEFAULT_DBHOSTS;
static char *dbname = PERDITIONDB_MYSQL_DEFAULT_DBNAME;
static unsigned int dbport = PERDITIONDB_MYSQL_DEFAULT_DBPORT;
static char *dbtable = PERDITIONDB_MYSQL_DEFAULT_DBTABLE;
@@ -92,10 +94,10 @@ int dbserver_fini(void){
* dbserver_init
* Parse options string.
* pre: options_str: Options string. String is of the form
- *
[dbhost[:port[:dbname[:dbtable[:dbuser[:dbpwd[:dbsrvcol[:dbusercol[:dbportcol]]]]]]]]]
+ *
[dbhost1[,dbhost2[,...]][:port[:dbname[:dbtable[:dbuser[:dbpwd[:dbsrvcol[:dbusercol[:dbportcol]]]]]]]]]
* post: Options string is parsed if not null into
* static vanessa_dynamic_array_t a and
- * static char *dbhost, *dbname, *dbtable, *dbuser, *dbpwd are
+ * static char *dbhosts, *dbname, *dbtable, *dbuser, *dbpwd are
* set to pointers insiside a or defaults as neccesary.
* return: 0 on success
* -1 on db access error
@@ -130,8 +132,16 @@ int dbserver_init(char *options_str){
}
count=vanessa_dynamic_array_get_count(a);
- if(count>PERDITIONDB_MYSQL_DBHOST){
- dbhost=vanessa_dynamic_array_get_element(a, PERDITIONDB_MYSQL_DBHOST);
+ if(count>PERDITIONDB_MYSQL_DBHOSTS){
+ dbhosts=vanessa_dynamic_array_get_element(a, PERDITIONDB_MYSQL_DBHOSTS);
+ if ((dbhosts_array = vanessa_dynamic_array_split_str(dbhosts,
+ PERDITIONDB_MYSQL_HOSTS_DELIMITER)) == NULL) {
+ VANESSA_LOGGER_DEBUG("vanessa_dynamic_array_split_str");
+ a=NULL;
+ free(tmp_str);
+ return(-1);
+ }
+ dbhosts_count = vanessa_dynamic_array_get_count(dbhosts_array);
}
if(count>PERDITIONDB_MYSQL_DBNAME){
dbname=vanessa_dynamic_array_get_element(a, PERDITIONDB_MYSQL_DBNAME);
@@ -211,6 +221,7 @@ int dbserver_get(
MYSQL_ROW row;
char sqlstr[PERDITIONDB_MYSQL_QUERY_LENGTH];
size_t servername_len;
+ size_t hcnt = 0;
rc = mysql_init(&db);
if(!rc){
@@ -219,7 +230,13 @@ int dbserver_get(
return(-1);
}
- rc = mysql_real_connect(&db,dbhost,dbuser,dbpwd,dbname,dbport,NULL,0);
+ while (hcnt < dbhosts_count
+ && !(rc=mysql_real_connect(&db,
+ vanessa_dynamic_array_get_element(dbhosts_array, hcnt),
+ dbuser,dbpwd,dbname,dbport,NULL,0))) {
+ perditiondb_mysql_log("mysql_connect", &db);
+ hcnt++;
+ }
if(!rc){
perditiondb_mysql_log("mysql_connect", &db);
mysql_close(&db);
diff -urNpd perdition-1.17.1/perdition/db/mysql/perditiondb_mysql.h
perdition-1.17.1-ro/perdition/db/mysql/perditiondb_mysql.h
--- perdition-1.17.1/perdition/db/mysql/perditiondb_mysql.h 2005-06-22 07:50:04.000000000
+0200
+++ perdition-1.17.1-ro/perdition/db/mysql/perditiondb_mysql.h 2009-07-31
09:49:25.000000000 +0200
@@ -43,8 +43,9 @@
#include <vanessa_adt.h>
#define PERDITIONDB_MYSQL_FIELD_DELIMITER ':'
+#define PERDITIONDB_MYSQL_HOSTS_DELIMITER ','
#define PERDITIONDB_MYSQL_MAX_SLEEP 1800
-#define PERDITIONDB_MYSQL_DEFAULT_DBHOST "localhost"
+#define PERDITIONDB_MYSQL_DEFAULT_DBHOSTS "localhost"
#define PERDITIONDB_MYSQL_DEFAULT_DBPORT 0
#define PERDITIONDB_MYSQL_DEFAULT_DBNAME "dbPerdition"
#define PERDITIONDB_MYSQL_DEFAULT_DBTABLE "tblPerdition"
@@ -54,7 +55,7 @@
#define PERDITIONDB_MYSQL_DEFAULT_DBSRVCOL "servername"
#define PERDITIONDB_MYSQL_DEFAULT_DBPORTCOL "port"
-#define PERDITIONDB_MYSQL_DBHOST 0
+#define PERDITIONDB_MYSQL_DBHOSTS 0
#define PERDITIONDB_MYSQL_DBPORT 1
#define PERDITIONDB_MYSQL_DBNAME 2
#define PERDITIONDB_MYSQL_DBTABLE 3
diff -urNpd perdition-1.17.1/perdition/perditiondb.5
perdition-1.17.1-ro/perdition/perditiondb.5
--- perdition-1.17.1/perdition/perditiondb.5 2005-06-22 07:50:05.000000000 +0200
+++ perdition-1.17.1-ro/perdition/perditiondb.5 2009-08-12 09:34:55.000000000 +0200
@@ -332,7 +332,14 @@ the arguments to avoid confusion.
Database servers may be grouped together for higher performance
or high availability by using ODBC and accessing
them using the ODBC module.
-
+.P
+.B Multiple MySQL Servers
+.P
+It is possible to specify multiple MySQL servers by specifying them,
+comma separated (without any space), in the dbhost column. In this
+case all MySQL servers need to have an identical configuration,
+because all other options are shared. If the first server is not
+reachable, perdition will fall back to the second etc.
.P
.B
POSTGRESQL
--
Roland Rosenfeld - Content Delivery - NED - Technik
NetCologne Gesellschaft für Telekommunikation mbH - HRB 25580, AG Köln
Am Coloneum 9 50829 Köln Tel.: +49-221-2222-373 Fax: +49-221-2222-7373
Geschäftsführer: Werner Hanf, Karl-Heinz Zankel
13 Aug
13 Aug
12:24 a.m.
On Wed, Aug 12, 2009 at 09:40:23AM +0200, Roland Rosenfeld wrote:
Hi Simon!
On Mon, 03 Aug 2009, Simon Horman wrote:
Thanks, I have applied the change to
http://hg.vergenet.net/perdition/perdition
1. Provide a Signed-off-by line as per the
instructions at
section 5 of http://linux.yyz.us/patch-format.html
2. Provide a patch to perdition/perditiondb.5.
A separate patch with a Signed-off-by is fine.
Although if you'd prefer to revise the patch
below that is fine too.
Okay, here we go:
The attached patch provides the feature to fall back from one MySQL
server to a list of others, it the first one is not reachable.
Signed-off-by: Roland Rosenfeld <rrosenfeld(a)netcologne.de>
5376
days inactive
5389
days old
7 comments
6 participants
participants (6)
-
Jimmy Brake -
Roland Rosenfeld -
Roland Rosenfeld -
Simon Horman -
Urban Loesch -
UrbanLoesch