Reconnect to remote MySQL ODBC database when host returns online
-
Here some snippet from my actual code:
// QSqlDatabase _db; declared in header bool OdbcSql::connectToDB(QUrl host, QString database, QString username, QString password) { _host = host; _database = database; _username = username; _password = password; // I tried to add this section in order to have a clean connection every time _db.close(); _db.removeDatabase("production"); _db = QSqlDatabase::addDatabase("QODBC", "production"); // ******************************************************** qInfo() << ID << "Trying to open database..."; _db.setDatabaseName(QStringLiteral("Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.1.1; Server=%1;Database=%2; Encrypt=No;").arg(host.url()).arg(database)); _db.setUserName(username); _db.setPassword(password); _db.setConnectOptions("SQL_ATTR_CONNECTION_TIMEOUT=5;SQL_ATTR_LOGIN_TIMEOUT=5"); bool ret = _db.open(); if (ret) qInfo() << ID << "Database opened successfully"; else qWarning() << ID << "Database opening failed:" << _db.lastError(); return ret; }
Periodically, I check the connection to the database:
bool OdbcSql::checkConnectivity() { if (_db.transaction()) { _db.rollback(); return true; } else { qWarning() << ID << "Lost connection to database"; return false; } }
and if it fails (i.e. the remote host is offline) I call again the above connectToDB() after 1 minute.
On startup it works as expected either the database is online or offline. In this case it tries every minute until the host comes up and then it connects.
Instead, if it is connected and then the connection goes down, it correctly detects the lost of connection, but any further attempt will fail, even when the remote host is available again.
The error reported is always the same:
QSqlError("258", "QODBC: Unable to connect", "[Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x102 [Microsoft][ODBC Driver 18 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to 192.168.0.50. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.")
or, sometimes:
QODBC: Unable to connect", "[unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed"
I also tried to turning off connection pooling of my
unixODBC
instance.What does prevent my code to connect again after a disconnection?
Ubuntu 22.04, Qt 6.4.0.