QSqlDatabase断开后无法重新连接数据库的问题求助

环境 #

• Ubuntu 22.04
• Qt 6.4.0

问题现象 #

程序启动时，无论数据库在线与否都能正常工作：离线时会每分钟尝试连接，直到主机恢复后成功连接。但已建立连接后断开数据库，程序能检测到连接丢失，但后续即使主机恢复，所有重连尝试都会失败，报错信息固定如下：

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.")

相关代码 #

初始化与连接函数 #

OdbcSql::OdbcSql(QObject *parent) : QObject(parent)
{
    // QSqlDatabase _db; declared in header
    _db = QSqlDatabase::addDatabase("QODBC", "production");
}

bool OdbcSql::connectToDB(QUrl host, QString database, QString username, QString password)
{
    _host = host;
    _database = database;
    _username = username;
    _password = password;

    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;
}

连接检测函数 #

bool OdbcSql::checkConnectivity()
{
    if (_db.transaction())
    {
        _db.rollback();
        return true;
    }
    else
    {
        qWarning() << ID << "Lost connection to database";
        return false;
    }
}

重连逻辑 #

当checkConnectivity()返回false时，1分钟后再次调用connectToDB()尝试重连。

原因与解决方案 #

核心原因 #

Qt的QSqlDatabase对象在连接失效后，内部会残留旧的连接状态信息，直接复用该对象调用open()无法正确初始化新连接，导致重连失败。

解决步骤 #

1. 重连前重置数据库对象：每次尝试重连时，先关闭并移除旧的数据库连接，再重新创建QSqlDatabase实例，避免状态残留。修改后的connectToDB()示例：

bool OdbcSql::connectToDB(QUrl host, QString database, QString username, QString password)
{
    _host = host;
    _database = database;
    _username = username;
    _password = password;

    qInfo() << ID << "Trying to open database...";

    // 关键：清理旧连接并重新创建对象
    if (_db.isValid()) {
        _db.close();
        QSqlDatabase::removeDatabase("production");
        _db = QSqlDatabase::addDatabase("QODBC", "production");
    }

    _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;
}

2. 优化连接检测逻辑：transaction()并非所有ODBC驱动都支持作为连接有效性检测手段，改用执行简单查询（如SELECT 1）更可靠，同时主动关闭无效连接：

bool OdbcSql::checkConnectivity()
{
    if (!_db.isOpen()) {
        qWarning() << ID << "Database is not open";
        return false;
    }

    QSqlQuery query(_db);
    if (query.exec("SELECT 1")) {
        return true;
    } else {
        qWarning() << ID << "Lost connection to database:" << query.lastError();
        _db.close();
        return false;
    }
}

内容的提问来源于stack exchange，提问作者Mark