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


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

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