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='

8 files changed, 128 insertions, 49 deletions

diff --git a/NEWS b/NEWS
index 6895824..913b0f9 100644
--- a/NEWS
+++ b/NEWS
@@ -1,3 +1,2 @@
-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.
@@ -6,2 +5,18 @@ 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
@@ -33,3 +48,3 @@ Copyright information:
 
-Copyright (C) 2013-2017 Sergey Poznyakoff
+Copyright (C) 2013-2018 Sergey Poznyakoff
 
diff --git a/configure.ac b/configure.ac
index 81a5390..7a1272c 100644
--- a/configure.ac
+++ b/configure.ac
@@ -1,3 +1,3 @@
 # This file is part of vmod-dbrw -*- autoconf -*-
-# Copyright (C) 2013-2017 Sergey Poznyakoff
+# Copyright (C) 2013-2018 Sergey Poznyakoff
 #
@@ -16,3 +16,3 @@
 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])
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
@@ -15,3 +15,3 @@
 .\" 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
@@ -101,2 +101,14 @@ 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
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
@@ -32,3 +32,3 @@ Boston, MA 02110-1301 USA
 
-Copyright @copyright{} 2013-2017 Sergey Poznyakoff
+Copyright @copyright{} 2013-2018 Sergey Poznyakoff
 
@@ -312,2 +312,17 @@ 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
diff --git a/src/dbrw.h b/src/dbrw.h
index 438d963..7a0ee52 100644
--- a/src/dbrw.h
+++ b/src/dbrw.h
@@ -1,3 +1,3 @@
 /* This file is part of vmod-dbrw
-   Copyright (C) 2013-2017 Sergey Poznyakoff
+   Copyright (C) 2013-2018 Sergey Poznyakoff
 
@@ -45,2 +45,3 @@ struct dbrw_backend {
 				      unsigned);
+	int (*sql_idle_timeout)(struct dbrw_connection *);
 };
@@ -68,2 +69,3 @@ struct dbrw_config {
 	char status[HTTP_STATUS_LEN+1];
+	int idle_timeout;
 	VTAILQ_ENTRY(dbrw_config) list;
@@ -79,2 +81,3 @@ struct dbrw_connection {
 	size_t matchsize;          /* Total number of entries in match map */
+	time_t timestamp;          /* Last used at */           
 	void *data;                /* Backend-specific data */
@@ -107,2 +110,3 @@ 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);
 
diff --git a/src/mysql.c b/src/mysql.c
index d7406ca..d97200a 100644
--- a/src/mysql.c
+++ b/src/mysql.c
@@ -1,3 +1,3 @@
 /* This file is part of vmod-dbrw
-   Copyright (C) 2013-2014 Sergey Poznyakoff
+   Copyright (C) 2013-2018 Sergey Poznyakoff
 
@@ -23,3 +23,3 @@ struct vmod_mysql_data
 {
-	MYSQL *mysql;
+	MYSQL mysql;
 	MYSQL_RES *result;
@@ -35,3 +35,3 @@ check_errno(struct dbrw_connection *conn)
 
-	switch (mysql_errno(mp->mysql)) {
+	switch (mysql_errno(&mp->mysql)) {
 	case CR_SERVER_GONE_ERROR:
@@ -40,3 +40,3 @@ check_errno(struct dbrw_connection *conn)
 	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);
@@ -60,3 +60,3 @@ check_errno(struct dbrw_connection *conn)
 	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");
@@ -66,3 +66,3 @@ check_errno(struct dbrw_connection *conn)
 	default:
-		dbrw_error("query failed: %s", mysql_error(mp->mysql));
+		dbrw_error("query failed: %s", mysql_error(&mp->mysql));
 	}
@@ -90,3 +90,2 @@ s_mysql_destroy(struct dbrw_connection *conn)
 	free(mp->buffer);
-	free(mp->mysql);
 	free(mp);
@@ -104,10 +103,3 @@ s_mysql_connect(struct dbrw_connection *conn)
 	
-	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);
 
@@ -125,3 +117,3 @@ s_mysql_connect(struct dbrw_connection *conn)
 	if (s)
-		mysql_options(mp->mysql, MYSQL_READ_DEFAULT_FILE, s);
+		mysql_options(&mp->mysql, MYSQL_READ_DEFAULT_FILE, s);
 
@@ -129,3 +121,3 @@ s_mysql_connect(struct dbrw_connection *conn)
 	if (s)
-		mysql_options(mp->mysql, MYSQL_READ_DEFAULT_GROUP, s);
+		mysql_options(&mp->mysql, MYSQL_READ_DEFAULT_GROUP, s);
 
@@ -133,5 +125,5 @@ s_mysql_connect(struct dbrw_connection *conn)
 	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,
@@ -143,3 +135,3 @@ s_mysql_connect(struct dbrw_connection *conn)
 				CLIENT_MULTI_RESULTS)) {
-		dbrw_error("cannot connect: %s", mysql_error(mp->mysql));
+		dbrw_error("cannot connect: %s", mysql_error(&mp->mysql));
 		return -1;
@@ -155,3 +147,3 @@ s_mysql_disconnect(struct dbrw_connection *conn)
 	struct vmod_mysql_data *mp = conn->data;
-	mysql_close(mp->mysql);
+	mysql_close(&mp->mysql);
 	return 0;
@@ -168,4 +160,3 @@ s_mysql_query(struct dbrw_connection *conn, const char *query)
 	for (i = 0; i < 10; i++) {
-		mysql = mp->mysql;
-		rc = mysql_query(mysql, query);
+		rc = mysql_query(&mp->mysql, query);
 		if (rc) {
@@ -179,3 +170,3 @@ s_mysql_query(struct dbrw_connection *conn, const char *query)
 		}
-		mp->result = mysql_store_result(mp->mysql);
+		mp->result = mysql_store_result(&mp->mysql);
 		if (mp->result) {
@@ -183,5 +174,5 @@ s_mysql_query(struct dbrw_connection *conn, const char *query)
 		        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;
@@ -269,3 +260,3 @@ s_mysql_escape (struct dbrw_connection *conn, const char *arg)
 			 
-	mysql_real_escape_string(mp->mysql, mp->buffer, arg, len);
+	mysql_real_escape_string(&mp->mysql, mp->buffer, arg, len);
 
@@ -277,2 +268,22 @@ s_mysql_escape (struct dbrw_connection *conn, const char *arg)
 
+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 = {
@@ -288,3 +299,4 @@ struct dbrw_backend mysql_backend = {
 	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,3 +1,3 @@
 /* This file is part of vmod-dbrw
-   Copyright (C) 2013-2014 Sergey Poznyakoff
+   Copyright (C) 2013-2018 Sergey Poznyakoff
 
@@ -145,2 +145,3 @@ sql_query(struct dbrw_connection *conn, const char *input)
 	} while (conn->state != state_connected);
+	conn->timestamp = time(NULL);
 	if (conn->conf->backend->sql_query(conn, input) == 0) 
@@ -187 +188,9 @@ sql_get_column(struct dbrw_connection *conn, unsigned row, unsigned 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,3 +1,3 @@
 /* This file is part of vmod-dbrw
-   Copyright (C) 2013-2017 Sergey Poznyakoff
+   Copyright (C) 2013-2018 Sergey Poznyakoff
 
@@ -78,4 +78,16 @@ dbrw_connection_get(struct dbrw_config *cfg)
 			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);
@@ -202,10 +214,3 @@ parse_flags(const char *arg, int *qdisp, int *flags, char status[])
    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   
@@ -260,4 +265,9 @@ vmod_config(VRT_CTX, struct vmod_priv *priv,
 
-		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;
 
@@ -269,3 +279,5 @@ vmod_config(VRT_CTX, struct vmod_priv *priv,
 		if (s)
-			parse_flags(s, &conf->qdisp, &conf->regflags,
+			parse_flags(s,
+				    &conf->qdisp,
+				    &conf->regflags,
 				    conf->status);