MySql Database and Threads

[moosa]

Hi all,

My problem is as follows: I have an SSL server where multiple clients can connect to. For each client I open a separate thread.

For each request to the database I need to prepare the database as shown in function "authenticateUser" and close the connection once I am finished in order not to overload the database.


The code of the thread is as follows:

SslThread::SslThread(int handle, int timeout, QObject *parent) :
    QThread(parent)
{
    m_sockHandle = handle;
    m_connName = QVariant(handle).toString();
    m_connTimeOut = timeout;
}
 
SslThread::~SslThread()
{
    qDebug("Thread Destructor");
    qDebug()<<"socket = "<< m_connName;
    QSqlDatabase::removeDatabase(m_connName);
}
 
void SslThread::run()
{
    QSslSocket  socket;
    m_rxTx = new RxTx(&socket);
 
    if (!socket.setSocketDescriptor(m_sockHandle))
    {
        qDebug()<<tr("Descriptor failed with %1").arg(socket.errorString());
        return;
    }
    socket.setPrivateKey(":/Certificates/mycert_key.pem");
    socket.setLocalCertificate(":/Certificates/mycert.pem");
 
    QFile f(":/Certificates/cacert.pem");
    f.open(QIODevice::ReadOnly);
    QSslCertificate cert(f.readAll());
    if (!cert.isValid())
        qDebug("Invalid Certificate");
    else
        socket.addCaCertificate(cert);
 
    connect(&socket, SIGNAL(disconnected()), this, SLOT(killThread()));
    connect(m_rxTx, SIGNAL(packetReceived(QByteArray)), this, SLOT(handleIncomingMessage(QByteArray)));
 
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL", m_connName);
    if (!db.isValid())
    {
        qCritical() << tr("Could not open the database QMYSQL:%1").arg(m_connName);
        return;
    }
 
    db.setDatabaseName("testDB");
    db.setHostName("localhost");
    db.setPort(3306);
 
    if (!db.open("dbUser", "dbPass"))
    {
        qDebug()<<"OpenDB Error:" << db.lastError().text();
        return;
    }
    db.close();
    socket.startServerEncryption();
    //errTimer.setInterval(m_connTimeOut);
    //connect(&errTimer, SIGNAL(timeout()), this, SLOT(killThread()));
    //errTimer.start();
    exec();
    qDebug("Just after exec...");
}
 
void SslThread::killThread()
{
    quit();
}
 
void SslThread::handleIncomingMessage(QByteArray ba)
{
    QDataStream ds(&ba, QIODevice::ReadOnly);
    quint32 command;
    ds >> command;
    switch (command)
    {
    case PROT_LOGIN:
        {
            QString user;
            QString pass;
            ds >> user;
            ds >> pass;
            authenticateUser(user, pass);
        }
        break;
    default:
        qWarning() << tr("Unknown command %1 from client").arg(command);
        break;
    }
}
 
void SslThread::authenticateUser(QString userName, QString password)
{
    QSqlDatabase db = QSqlDatabase::database(m_connName, false);
    db.setDatabaseName("testDB");
    db.setHostName("localhost");
    db.setPort(3306);
    if (!db.isValid())
    {
        qWarning() << tr("The database is not vaild");
        return;
    }
    if (!db.open("dbUser", "dbPass"))
    {
        qWarning() << tr("Could not open the database");
        return;
    }
    QSqlQuery query(db);
    query.exec("Do something...");
    ....
    db.close();
}

SslThread::SslThread(int handle, int timeout, QObject *parent) :
    QThread(parent)
{
    m_sockHandle = handle;
    m_connName = QVariant(handle).toString();
    m_connTimeOut = timeout;
}

SslThread::~SslThread()
{
    qDebug("Thread Destructor");
    qDebug()<<"socket = "<< m_connName;
    QSqlDatabase::removeDatabase(m_connName);
}

void SslThread::run()
{
    QSslSocket  socket;
    m_rxTx = new RxTx(&socket);

    if (!socket.setSocketDescriptor(m_sockHandle))
    {
        qDebug()<<tr("Descriptor failed with %1").arg(socket.errorString());
        return;
    }
    socket.setPrivateKey(":/Certificates/mycert_key.pem");
    socket.setLocalCertificate(":/Certificates/mycert.pem");

    QFile f(":/Certificates/cacert.pem");
    f.open(QIODevice::ReadOnly);
    QSslCertificate cert(f.readAll());
    if (!cert.isValid())
        qDebug("Invalid Certificate");
    else
        socket.addCaCertificate(cert);

    connect(&socket, SIGNAL(disconnected()), this, SLOT(killThread()));
    connect(m_rxTx, SIGNAL(packetReceived(QByteArray)), this, SLOT(handleIncomingMessage(QByteArray)));

    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL", m_connName);
    if (!db.isValid())
    {
        qCritical() << tr("Could not open the database QMYSQL:%1").arg(m_connName);
        return;
    }

    db.setDatabaseName("testDB");
    db.setHostName("localhost");
    db.setPort(3306);

    if (!db.open("dbUser", "dbPass"))
    {
        qDebug()<<"OpenDB Error:" << db.lastError().text();
        return;
    }
    db.close();
    socket.startServerEncryption();
    //errTimer.setInterval(m_connTimeOut);
    //connect(&errTimer, SIGNAL(timeout()), this, SLOT(killThread()));
    //errTimer.start();
    exec();
    qDebug("Just after exec...");
}

void SslThread::killThread()
{
    quit();
}

void SslThread::handleIncomingMessage(QByteArray ba)
{
    QDataStream ds(&ba, QIODevice::ReadOnly);
    quint32 command;
    ds >> command;
    switch (command)
    {
    case PROT_LOGIN:
        {
            QString user;
            QString pass;
            ds >> user;
            ds >> pass;
            authenticateUser(user, pass);
        }
        break;
    default:
        qWarning() << tr("Unknown command %1 from client").arg(command);
        break;
    }
}

void SslThread::authenticateUser(QString userName, QString password)
{
    QSqlDatabase db = QSqlDatabase::database(m_connName, false);
    db.setDatabaseName("testDB");
    db.setHostName("localhost");
    db.setPort(3306);
    if (!db.isValid())
    {
        qWarning() << tr("The database is not vaild");
        return;
    }
    if (!db.open("dbUser", "dbPass"))
    {
        qWarning() << tr("Could not open the database");
        return;
    }
    QSqlQuery query(db);
    query.exec("Do something...");
    ....
    db.close();
}

To copy to clipboard, switch view to plain text mode 

My first question: If I have many functions like "authenticateUser" which need to access the database, how do I prepare a single instance of the database connection and set up a query and use it immediately instead of setting database parameters each time?

My second question: If I commit the following section in the "run" function:

#if 0
    if (!db.open("dbUser", "dbPass"))
    {
        qDebug()<<"OpenDB Error:" << db.lastError().text();
        return;
    }
#endif

#if 0
    if (!db.open("dbUser", "dbPass"))
    {
        qDebug()<<"OpenDB Error:" << db.lastError().text();
        return;
    }
#endif

To copy to clipboard, switch view to plain text mode 

and the connection is dropped (by client or by closing the sever), I get the following error:
"Error in my_thread_global_end(): 1 threads didn't exit"
It's like I need to open the connection for the first time in order to use it later by "QSqlDatabase::database(m_connName, false)" function.


I really need help on this one.

Thanks in advance.

[moosa]

I forgot to supply the system configuration:

OS: Ubuntu 10.04 64 bit
Qt version: 4.7.1
MySql version: 5.1.41