mysqlpp connection timeout problem

October 3rd, 2011 by xrigher Leave a reply »

If you have a application having long time connection with mysql using mysqlpp. You may at first think of using Connection::connected() to determine whether the connection is still valid. But actually this doesn’t work if the connection gets timeout.

After checking the reference, I found out Connection::ping() would be a good way to determine timeout.

Here is a piece of test code:

#include <iostream>
#include <string>
#include <mysql++.h>

using namespace std;

int main() {
	string db_schema("DB_SCHEMA");
	string db_host("DB_HOST");
	string db_user("DB_USER");
	string db_pass("DB_PASS");
	string qry_str("SELECT MAX(user_id) FROM users");

	mysqlpp::Connection cn;
	cn.connect(db_schema.c_str(), db_host.c_str(), db_user.c_str(), db_pass.c_str());
	mysqlpp::Query qry = cn.query(qry_str);
	mysqlpp::StoreQueryResult res = qry.store();
	cout << "frist query result: " << res[0][0] << endl;

	string tmp;
	cout << "Wait for a period of time to let the connection timeout, and then input whatever a string to continue: " << endl;
	cin >> tmp;

	cout << "ping result: " << cn.ping() << endl;
	cout << "connected: " << cn.connected() << endl;
	
	if(!cn.ping())
		cn.connect(db_schema.c_str(), db_host.c_str(), db_user.c_str(), db_pass.c_str());
	mysqlpp::Query qry2 = cn.query(qry_str);
	res = qry2.store();
	cout << "second query result: " << res[0][0] << endl;

	cn.disconnect();
	
	return 0;	
}

But of course, I’m still new to mysqlpp. There may be a better way to do this. Please comment below if you have a better way.

NOTE: Here is how to change mysql connection idle timeout.

UPDATE: Just found another way to handle the timeout problem: use Connection::set_option(new mysqlpp::ReconnectOption(true));. This is much more elegant.
Here is a piece of sample code:

mysqlpp::Connection cn;
cn.set_option(new mysqlpp::ReconnectOption(true));
cn.connect(db_schema.c_str(), db_host.c_str(), db_user.c_str(), db_pass.c_str());

Advertisement

Leave a Reply