Feature request: keep connection alive

[zhanyigo]

Hi, I got an exception: ‘Error:MySQL server has gone away’ when my program idle for a while.

My program used

soci-v3.2.3, mysql-server-v5.2

It cause by no requests on the connection. I think maybe soci can provide a feature to keep connection alived.

[mloskot]

You can try to catch the exception and call session::reconnect

[snikulov]

@mloskot I suspect reconnect will produce crash or leak. See #136 and #454 which expose this issue

[mloskot]

@snikulov Thanks for pointing this out. I don't use reconnect myself, so never experienced those problems.

[aliaksejenka]

mysqld breaks the connection after a wait_timeout that can not be configured to infiniteness, and using the try-catch everywhere to detect the loss of connection is not very practical; can help just setting the option on connection initialization:

my_bool reconnect = 1;
mysql_options(conn_, MYSQL_OPT_RECONNECT, &reconnect);

but it may have some drawbacks https://dev.mysql.com/doc/refman/5.7/en/auto-reconnect.html -- so the parameter on soci connection initialization string seems natural to turn on/off this option as desired

[zhanyigo]

I think we can create a thread to keep all connection alive.

1. Add an option: idle timeout.
2. Add last access time for session.
3. Create a timer which auto send a 'Ping' to server for the idle sessions.

MYSQL can use this function:

mysql_ping()

Oracle: execute a command.

SELECT 1 FROM DUAL;

[firesWu]

void reconnect(session& sql){
            mysql_session_backend * mysqlBackEnd = static_cast<mysql_session_backend *>(sql.get_backend());
            int i = mysql_ping(mysqlBackEnd->conn_);
            if(i==1){
                sql.reconnect();
            }
        }

It's my way to judge whether connection is still alive

[vadz]

FWIW there is now session::is_connected() which can be used to check if we're still connected to the database and call reconnect() if we're not. This works for at least PostgreSQL, Oracle and MS SQL Server (via ODBC). I didn't test MySQL, however.