summaryrefslogtreecommitdiffabout
authorSergey Poznyakoff <gray@gnu.org.ua>2018-01-30 21:00:05 (GMT)
committer Sergey Poznyakoff <gray@gnu.org.ua>2018-01-30 21:06:11 (GMT)
commit4fef83230e851189fd3541f095c47a952968a411 (patch) (side-by-side diff)
tree021413ec3602ca4d0e0a0ee0f588d0a2782155ae
parente946449ccf15a0f44f402b03812d31d5f8ef4f7b (diff)
downloadvmod-dbrw-4fef83230e851189fd3541f095c47a952968a411.tar.gz
vmod-dbrw-4fef83230e851189fd3541f095c47a952968a411.tar.bz2
Introduce connection idle timeout.
* NEWS: Updated. * configure.ac: Version 2.2.90 * doc/vmod-dbrw.3: Document connection idle timeout. * doc/vmod-dbrw.texi: Likewise. * src/dbrw.h (dbrw_backend) <sql_idle_timeout>: New method. (dbrw_config)<idle_timeout>: New member. (dbrw_connection)<timestamp>: New member. (sql_idle_timeout): New proto. * src/mysql.c (vmod_mysql_data) <mysql>: Change storage. All uses changed. (s_mysql_idle_timeout): New function. * src/sql.c (sql_idle_timeout): New function. (sql_query): Update connection timestamp. (sql_idle_timeout): New function. * src/vmod_dbrw.c (dbrw_connection_get): Force disconnect if connection remained idle for too long. Initialize cfg->idle_timeout. (vmod_config): New parameter 'timeout='
Diffstat (more/less context) (ignore whitespace changes)
-rw-r--r--NEWS21
-rw-r--r--configure.ac4
-rw-r--r--doc/vmod-dbrw.314
-rw-r--r--doc/vmod-dbrw.texi17
-rw-r--r--src/dbrw.h8
-rw-r--r--src/mysql.c68
-rw-r--r--src/sql.c11
-rw-r--r--src/vmod_dbrw.c38
8 files changed, 130 insertions, 51 deletions
diff --git a/NEWS b/NEWS
index 6895824..913b0f9 100644
--- a/NEWS
+++ b/NEWS
@@ -1,12 +1,27 @@
-vmod-dbrw -- history of user-visible changes. 2017-08-10
-Copyright (C) 2013-2017 Sergey Poznyakoff
+vmod-dbrw -- history of user-visible changes. 2018-01-30
See the end of file for copying conditions.
Please send vmod-dbrw bug reports to <gray@gnu.org>
+Version 2.2.90 (Git)
+
+* SQL idle timeout
+
+For MySQL backend, the default connection idle timeout is set equal to
+the value of the MySQL variable 'wait_timeout'. For Postgres, default
+idle timeout is not yet implemented.
+
+Idle timeout can be configured using the timeout configuration option,
+e.g.:
+
+ dbrw.config("mysql", "database=dbrw;user=proxy;timeout=600",
+ {"select dest,pattern,value,flags from rewrite where
+ locate(url,'$url') = 1 order by weight asc;"});
+
+
Version 2.2, 2017-08-10
* Support for Varnish 5.1
Version 2.1, 2017-08-06
@@ -28,13 +43,13 @@ Version 1.0, 2013-07-20
Initial release
=========================================================================
Copyright information:
-Copyright (C) 2013-2017 Sergey Poznyakoff
+Copyright (C) 2013-2018 Sergey Poznyakoff
Permission is granted to anyone to make or distribute verbatim copies
of this document as received, in any medium, provided that the
copyright notice and this permission notice are preserved,
thus giving the recipient permission to redistribute in turn.
diff --git a/configure.ac b/configure.ac
index 81a5390..7a1272c 100644
--- a/configure.ac
+++ b/configure.ac
@@ -1,8 +1,8 @@
# This file is part of vmod-dbrw -*- autoconf -*-
-# Copyright (C) 2013-2017 Sergey Poznyakoff
+# Copyright (C) 2013-2018 Sergey Poznyakoff
#
# Vmod-dbrw is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 3, or (at your option)
# any later version.
#
@@ -11,13 +11,13 @@
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with vmod-dbrw. If not, see <http://www.gnu.org/licenses/>.
AC_PREREQ(2.69)
-AC_INIT([vmod-dbrw], 2.2, [gray@gnu.org])
+AC_INIT([vmod-dbrw], 2.2.90, [gray@gnu.org])
AC_CONFIG_AUX_DIR([build-aux])
AC_CONFIG_MACRO_DIR([m4])
AC_CONFIG_SRCDIR(src/vmod_dbrw.vcc)
AM_CONFIG_HEADER(config.h)
AC_SUBST([AC_VMOD_BASENAME],[dbrw])
diff --git a/doc/vmod-dbrw.3 b/doc/vmod-dbrw.3
index 139fbab..6f1dba8 100644
--- a/doc/vmod-dbrw.3
+++ b/doc/vmod-dbrw.3
@@ -10,13 +10,13 @@
.\" but WITHOUT ANY WARRANTY; without even the implied warranty of
.\" MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
.\" GNU General Public License for more details.
.\"
.\" You should have received a copy of the GNU General Public License
.\" along with vmod-dbrw. If not, see <http://www.gnu.org/licenses/>.
-.TH VMOD-DBRW 1 "August 5, 2017" "VMOD-DBRW" "User Reference"
+.TH VMOD-DBRW 1 "January 30, 2018" "VMOD-DBRW" "User Reference"
.SH NAME
vmod-dbrw \- Database-driven rewrite rules for Varnish Cache
.SH SYNOPSIS
.B import dbrw;
.PP
.BI "VOID dbrw.config(STRING " dbtype ", STRING " params ", STRING " query ");"
@@ -96,12 +96,24 @@ options file supplied with the \fBconfig\fR parameter. Default
section name is \fBclient\fR.
.TP
\fBcacert\fR=\fIFILE\fR
Use secure connection to the database server via SSL. The \fIFILE\fR
is a full pathname to the certificate authority file.
.TP
+\fBtimeout\fR=\fIN\fR
+Sets idle timeout for a single connection (seconds). The connection
+will be closed and opened again if the module is to use it after
+\fIN\fR or more seconds since its last use. Set \fBtimeout=-1\fR to
+disable idle timeout (sessions will remain open until the SQL
+server closes them). Set \fBtimeout=0\fR to close the connection after
+each request (not recommended).
+
+The default depends on the selected SQL backend. For MySQL, it equals
+the value of the \fBwait_timeout\fR global variable. For PostgreSQL,
+it is \fB-1\fR.
+.TP
\fBoptions\fR=\fISTRING\fR
(\fBPostgres\fR-specific) Connection options.
.TP
\fBuser\fR=\fINAME\fR
Database user name.
.TP
diff --git a/doc/vmod-dbrw.texi b/doc/vmod-dbrw.texi
index 82adda2..40fc84d 100644
--- a/doc/vmod-dbrw.texi
+++ b/doc/vmod-dbrw.texi
@@ -27,13 +27,13 @@
@copying
Published by the Free Software Foundation,
51 Franklin Street, Fifth Floor,
Boston, MA 02110-1301 USA
-Copyright @copyright{} 2013-2017 Sergey Poznyakoff
+Copyright @copyright{} 2013-2018 Sergey Poznyakoff
Permission is granted to copy, distribute and/or modify this document
under the terms of the GNU Free Documentation License, Version 1.3 or
any later version published by the Free Software Foundation; with no
Invariant Sections, no Front-Cover, and no Back-Cover texts.
A copy of the license is included in the section entitled ``GNU Free
@@ -307,12 +307,27 @@ section name is @samp{client}.
@cindex certificate authority file, SSL
@item cacert=@var{filename}
Use secure connection to the database server via SSL. The
@var{filename} argument is a full pathname of the certificate
authority file.
+@kwindex timeout
+@cindex timeout, idle, SQL
+@cindex idle timeout, SQL
+@item timeout=@var{n}
+Sets idle timeout for a single connection. The connection will be
+closed and opened again if the module is to use it after @var{n} or
+more seconds since its last use. Set @samp{timeout=-1} to disable idle
+timeout (session will remain open until the SQL server closes it). Set
+@samp{timeout=0} to close the connection after each request (not
+recommended).
+
+The default depends on the selected SQL backend. For MySQL, it equals
+the value of the @samp{wait_timeout} global variable. For PostgreSQL,
+it is @samp{-1}.
+
@kindex options
@cindex options, PostgreSQL
@item options=@var{string}
(Postgres-specific) Connection options.
@kindex user
diff --git a/src/dbrw.h b/src/dbrw.h
index 438d963..7a0ee52 100644
--- a/src/dbrw.h
+++ b/src/dbrw.h
@@ -1,8 +1,8 @@
/* This file is part of vmod-dbrw
- Copyright (C) 2013-2017 Sergey Poznyakoff
+ Copyright (C) 2013-2018 Sergey Poznyakoff
Vmod-dbrw is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 3, or (at your option)
any later version.
@@ -40,12 +40,13 @@ struct dbrw_backend {
int (*sql_query) (struct dbrw_connection *, const char *);
unsigned (*sql_num_tuples) (struct dbrw_connection *);
unsigned (*sql_num_fields) (struct dbrw_connection *);
int (*sql_free_result) (struct dbrw_connection *);
const char *(*sql_get_column)(struct dbrw_connection *, unsigned,
unsigned);
+ int (*sql_idle_timeout)(struct dbrw_connection *);
};
enum {
state_init,
state_connected,
state_result,
@@ -63,24 +64,26 @@ struct dbrw_config {
char **param;
char *param_str;
char *query;
int qdisp;
int regflags;
char status[HTTP_STATUS_LEN+1];
+ int idle_timeout;
VTAILQ_ENTRY(dbrw_config) list;
};
struct dbrw_connection {
unsigned magic;
#define DBRW_CONNECTION_MAGIC 0x6773716c
int state; /* Connection state */
int busy:1;
struct dbrw_config *conf; /* Pointer to the configuration data */
regmatch_t *matches; /* Match map */
size_t matchsize; /* Total number of entries in match map */
- void *data; /* Backend-specific data */
+ time_t timestamp; /* Last used at */
+ void *data; /* Backend-specific data */
VTAILQ_ENTRY(dbrw_connection) list;
};
void dbrw_debug(const char *fmt, ...);
void dbrw_error(const char *fmt, ...);
@@ -102,9 +105,10 @@ char *sql_escape(struct dbrw_connection *pd, const char *input);
int sql_query(struct dbrw_connection *pd, const char *input);
unsigned sql_num_tuples(struct dbrw_connection *pd);
unsigned sql_num_fields(struct dbrw_connection *pd);
void sql_free_result(struct dbrw_connection *pd);
void sql_destroy(struct dbrw_connection *pd);
const char *sql_get_column(struct dbrw_connection *pd, unsigned row, unsigned col);
+int sql_idle_timeout(struct dbrw_connection *conn);
char *findparam(char **params, char *name);
diff --git a/src/mysql.c b/src/mysql.c
index d7406ca..d97200a 100644
--- a/src/mysql.c
+++ b/src/mysql.c
@@ -1,8 +1,8 @@
/* This file is part of vmod-dbrw
- Copyright (C) 2013-2014 Sergey Poznyakoff
+ Copyright (C) 2013-2018 Sergey Poznyakoff
Vmod-dbrw is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 3, or (at your option)
any later version.
@@ -18,30 +18,30 @@
#include <mysql/mysql.h>
#include <mysql/errmsg.h>
#include <mysql/mysqld_error.h>
struct vmod_mysql_data
{
- MYSQL *mysql;
+ MYSQL mysql;
MYSQL_RES *result;
char *buffer;
size_t bufsize;
};
static void
check_errno(struct dbrw_connection *conn)
{
struct vmod_mysql_data *mp = conn->data;
- switch (mysql_errno(mp->mysql)) {
+ switch (mysql_errno(&mp->mysql)) {
case CR_SERVER_GONE_ERROR:
case CR_SERVER_LOST:
case ER_SERVER_SHUTDOWN:
case ER_ABORTING_CONNECTION:
- dbrw_error("query failed: %s", mysql_error(mp->mysql));
+ dbrw_error("query failed: %s", mysql_error(&mp->mysql));
sql_disconnect(conn);
if (conn->state == state_error) {
conn->state = state_disabled;
dbrw_error("disabling MySQL connection");
}
break;
@@ -55,19 +55,19 @@ check_errno(struct dbrw_connection *conn)
case ER_WRONG_FIELD_SPEC:
case ER_PARSE_ERROR:
case ER_EMPTY_QUERY:
case ER_FIELD_SPECIFIED_TWICE:
case ER_NO_SUCH_TABLE:
case ER_NOT_ALLOWED_COMMAND:
- dbrw_error("query failed: %s", mysql_error(mp->mysql));
+ dbrw_error("query failed: %s", mysql_error(&mp->mysql));
dbrw_error("disabling MySQL connection");
sql_disconnect(conn);
conn->state = state_disabled;
break;
default:
- dbrw_error("query failed: %s", mysql_error(mp->mysql));
+ dbrw_error("query failed: %s", mysql_error(&mp->mysql));
}
}
/* ************************************************************************* */
/* Interface routines */
@@ -85,13 +85,12 @@ s_mysql_init(struct dbrw_connection *conn)
static void
s_mysql_destroy(struct dbrw_connection *conn)
{
struct vmod_mysql_data *mp = conn->data;
free(mp->buffer);
- free(mp->mysql);
free(mp);
conn->data = NULL;
}
static int
@@ -99,20 +98,13 @@ s_mysql_connect(struct dbrw_connection *conn)
{
struct vmod_mysql_data *mp = conn->data;
char *host, *socket_name = NULL;
char *s;
int port = 0;
- mp->mysql = malloc(sizeof(MYSQL));
- if (!mp->mysql) {
- dbrw_error("not enough memory");
- conn->state = state_disabled;
- return -1;
- }
-
- mysql_init(mp->mysql);
+ mysql_init(&mp->mysql);
host = findparam(conn->conf->param, "server");
if (host && host[0] == '/') {
host = "localhost";
socket_name = host;
}
@@ -120,73 +112,72 @@ s_mysql_connect(struct dbrw_connection *conn)
s = findparam(conn->conf->param, "port");
if (s)
port = atoi(s);
s = findparam(conn->conf->param, "config");
if (s)
- mysql_options(mp->mysql, MYSQL_READ_DEFAULT_FILE, s);
+ mysql_options(&mp->mysql, MYSQL_READ_DEFAULT_FILE, s);
s = findparam(conn->conf->param, "group");
if (s)
- mysql_options(mp->mysql, MYSQL_READ_DEFAULT_GROUP, s);
+ mysql_options(&mp->mysql, MYSQL_READ_DEFAULT_GROUP, s);
s = findparam(conn->conf->param, "cacert");
if (s)
- mysql_ssl_set(mp->mysql, NULL, NULL, s, NULL, NULL);
+ mysql_ssl_set(&mp->mysql, NULL, NULL, s, NULL, NULL);
debug(conn->conf, 1, ("connecting to database"));
- if (!mysql_real_connect(mp->mysql,
+ if (!mysql_real_connect(&mp->mysql,
host,
findparam(conn->conf->param, "user"),
findparam(conn->conf->param, "password"),
findparam(conn->conf->param, "database"),
port,
socket_name,
CLIENT_MULTI_RESULTS)) {
- dbrw_error("cannot connect: %s", mysql_error(mp->mysql));
+ dbrw_error("cannot connect: %s", mysql_error(&mp->mysql));
return -1;
}
debug(conn->conf, 1, ("connected to database"));
return 0;
}
static int
s_mysql_disconnect(struct dbrw_connection *conn)
{
struct vmod_mysql_data *mp = conn->data;
- mysql_close(mp->mysql);
+ mysql_close(&mp->mysql);
return 0;
}
static int
s_mysql_query(struct dbrw_connection *conn, const char *query)
{
struct vmod_mysql_data *mp = conn->data;
int rc;
int i;
MYSQL *mysql;
for (i = 0; i < 10; i++) {
- mysql = mp->mysql;
- rc = mysql_query(mysql, query);
+ rc = mysql_query(&mp->mysql, query);
if (rc) {
check_errno(conn);
if (conn->state != state_init)
return -1;
sql_connect(conn);
if (conn->state != state_connected)
return -1;
continue;
}
- mp->result = mysql_store_result(mp->mysql);
+ mp->result = mysql_store_result(&mp->mysql);
if (mp->result) {
conn->state = state_result;
rc = 0;
- } else if (mysql_field_count(mp->mysql)) {
+ } else if (mysql_field_count(&mp->mysql)) {
dbrw_error("cannot store result: %s",
- mysql_error(mp->mysql));
+ mysql_error(&mp->mysql));
conn->state = state_error;
rc = 1;
} else
rc = 0;
break;
}
@@ -264,27 +255,48 @@ s_mysql_escape (struct dbrw_connection *conn, const char *arg)
return NULL;
}
mp->buffer = p;
mp->bufsize = size;
}
- mysql_real_escape_string(mp->mysql, mp->buffer, arg, len);
+ mysql_real_escape_string(&mp->mysql, mp->buffer, arg, len);
p = strdup(mp->buffer);
if (!p)
dbrw_error("not enough memory");
return p;
}
+static int
+s_mysql_idle_timeout(struct dbrw_connection *conn)
+{
+ const char *res;
+ int n;
+ if (sql_connect(conn) || conn->state != state_connected)
+ return -1;
+ if (sql_query(conn,
+ "SHOW VARIABLES WHERE Variable_name = 'wait_timeout'"))
+ return -1;
+ res = sql_get_column(conn, 0, 1);
+ if (res)
+ n = atoi(res);
+ else
+ n = -1;
+ sql_free_result(conn);
+ return n;
+}
+
+
struct dbrw_backend mysql_backend = {
"mysql",
s_mysql_init,
s_mysql_destroy,
s_mysql_connect,
s_mysql_disconnect,
s_mysql_escape,
s_mysql_query,
s_mysql_num_tuples,
s_mysql_num_fields,
s_mysql_free_result,
- s_mysql_get_column
+ s_mysql_get_column,
+ s_mysql_idle_timeout
};
diff --git a/src/sql.c b/src/sql.c
index c42c5de..3acd9de 100644
--- a/src/sql.c
+++ b/src/sql.c
@@ -1,8 +1,8 @@
/* This file is part of vmod-dbrw
- Copyright (C) 2013-2014 Sergey Poznyakoff
+ Copyright (C) 2013-2018 Sergey Poznyakoff
Vmod-dbrw is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 3, or (at your option)
any later version.
@@ -140,12 +140,13 @@ sql_query(struct dbrw_connection *conn, const char *input)
break;
default:
return 1;
}
} while (conn->state != state_connected);
+ conn->timestamp = time(NULL);
if (conn->conf->backend->sql_query(conn, input) == 0)
return 0;
return 1;
}
unsigned
@@ -182,6 +183,14 @@ sql_get_column(struct dbrw_connection *conn, unsigned row, unsigned col)
assert(conn->conf->backend->sql_get_column);
if (conn->state != state_result)
return NULL;
return conn->conf->backend->sql_get_column(conn, row, col);
}
+int
+sql_idle_timeout(struct dbrw_connection *conn)
+{
+ CONN_ASSERT_VAL(conn, -1);
+ if (!conn->conf->backend->sql_idle_timeout)
+ return -1;
+ return conn->conf->backend->sql_idle_timeout(conn);
+}
diff --git a/src/vmod_dbrw.c b/src/vmod_dbrw.c
index b6fe70c..d6785d7 100644
--- a/src/vmod_dbrw.c
+++ b/src/vmod_dbrw.c
@@ -1,8 +1,8 @@
/* This file is part of vmod-dbrw
- Copyright (C) 2013-2017 Sergey Poznyakoff
+ Copyright (C) 2013-2018 Sergey Poznyakoff
Vmod-dbrw is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 3, or (at your option)
any later version.
@@ -73,14 +73,26 @@ dbrw_connection_get(struct dbrw_config *cfg)
if (sql_init(cp)) {
free(cp);
cp = NULL;
} else
VTAILQ_INSERT_HEAD(&connect_pool, cp, list);
+ } else if (cp->state == state_connected
+ && cfg->idle_timeout >= 0
+ && cp->timestamp + cfg->idle_timeout < time(NULL)) {
+ sql_disconnect(cp);
}
+
cp->busy = 1;
+
+ if (cfg->idle_timeout == -2) {
+ cfg->idle_timeout = sql_idle_timeout(cp);
+ if (cfg->idle_timeout < -1)
+ cfg->idle_timeout = -1;
+ }
+
pthread_mutex_unlock(&connect_pool_mtx);
return cp;
}
static void
dbrw_connection_release(struct dbrw_connection *cp)
@@ -186,31 +198,24 @@ parse_flags(const char *arg, int *qdisp, int *flags, char status[])
ws.ws_wordv[i] + 2);
rc = 1;
} else
strncpy(status, ws.ws_wordv[i] + 2,
HTTP_STATUS_LEN);
} else {
- dbrw_error("unrecognized flag: %s", ws.ws_wordv[i]);
+ dbrw_error("unrecognized flag: %s", ws.ws_wordv[i]);
rc = 1;
}
}
wordsplit_free(&ws);
return rc;
}
/* Configure the module.
BACKEND - "mysql" or "pgsql"
- PARAM - VAR=VALUE*:
- db=S
- port=N
- socket=S
- user=S
- password=S
- options=S
- debug=N
+ PARAM - VAR=VALUE*
QUERY - Query to obtain the redirection target
*/
VCL_VOID
vmod_config(VRT_CTX, struct vmod_priv *priv,
VCL_STRING bkname, VCL_STRING param, VCL_STRING query)
{
@@ -255,22 +260,29 @@ vmod_config(VRT_CTX, struct vmod_priv *priv,
conf->param_str = strdup(param);
AN(conf->param_str);
conf->query = strdup(query);
AN(conf->query);
conf->backend = backend;
- s = findparam(conf->param, "debug");
- conf->debug_level = s ? atoi(s) : 0;
+ if ((s = findparam(conf->param, "debug")) != NULL)
+ conf->debug_level = atoi(s);
+
+ if ((s = findparam(conf->param, "timeout")) != NULL)
+ conf->idle_timeout = atoi(s);
+ else
+ conf->idle_timeout = -2;
conf->qdisp = QDISP_NONE;
conf->regflags = REG_EXTENDED;
conf->status[0] = 0;
s = findparam(conf->param, "flags");
if (s)
- parse_flags(s, &conf->qdisp, &conf->regflags,
+ parse_flags(s,
+ &conf->qdisp,
+ &conf->regflags,
conf->status);
AZ(pthread_mutex_lock(&config_pool_mtx));
VTAILQ_INSERT_HEAD(&config_pool, conf, list);
pthread_mutex_unlock(&config_pool_mtx);
}

Return to:

Send suggestions and report system problems to the System administrator.