11

I'm using QMYSQL to connect to a local database. The application runs over several threads. Each thread connects to database using an independent connection. Sometimes Qt throws following error when I try to connect to database. What's the problem?

QMYSQL: Unable to allocate a MYSQL object

Update

Added the code used to connect. This object is moved to a thread, the connection is named. critical is a signal emitted to main window to terminate application execution after a critical error (showing a message). log is a signal that is emitted to log incidences into database. 

void ClientWorker::connect() {
    m_database = QSqlDatabase::addDatabase("QMYSQL","wsc");
    m_database.setHostName(m_host);
    m_database.setDatabaseName(m_databaseName);
    m_database.setPort(m_port);
    m_database.setUserName(m_db_username);
    m_database.setPassword(m_db_password);
    if(!m_database.open()) {
        QString error = "Unable to connect to database. Reason:\n";
        error+= m_database.lastError().text();
        log("Unable to connect to database! ", error, "ERROR" );
        emit critical(tr("Database Error!"),error);
    } else {
        log("Connected to datbase successfully.", "", "NOTICE" );
}

Update 2

I just realized that every time a connection is made out of main thread, (with no active connections in main tread) the driver fails to load. I just added a small dummy connection code in main() that connects and disconnects immediately (before any thread connects). Adding that code, everything works fine. I'm not sure why threads can't connect before a connection in main thread, but I think it looks like a bug. Hope this helps someone, Took 3 days of mine :/

sorush-r
  • 10,490
  • 17
  • 89
  • 173
  • 1
    Can you show the code you're using to create the database connections? – eclarkso Jul 13 '16 at 14:44
  • 1
    @eclarkso Updated my question – sorush-r Jul 15 '16 at 06:49
  • 1
    It sure looks like you're using the same connection name ("wsc") across all your threads - but in that case, I would expect a noticeable warning message that would have caught your attention (assuming different thread connections are active at the same time)? Might be worth appending the thread id or something to the connection name to make sure that's not the issue? Assuming it's not, did you compile the Qt MySQL plugin or did it come from some 3rd party? What version of MySQL? – eclarkso Jul 15 '16 at 14:59
  • 1
    How many simultaneous connections does your database support? How many simultaneous connections are you making with threads that do not coordinate with each other over a limited number of connections? – eh9 Jul 15 '16 at 16:42
  • 1
    FWIW, it looks like this error is from [the Qt plugin](http://code.qt.io/cgit/qt/qtbase.git/commit/?id=28ceb2ea5e79d65904d2ad83b8259e75b60acff6) and not ultimately the MySQL driver. No guesses yet why `mysql_init()` would fail (which appears to be what's triggering the error). – eclarkso Jul 15 '16 at 19:10
  • 1
    @eclarkso No. I'm not using same connection name across threads. And I'm using pre-built binaries of 5.7 MSVC (64-bit) – sorush-r Jul 16 '16 at 07:21
  • 1
    Are you sure the connections are added and opened in the same threads they are actually used in? Try printing the thread IDs to be sure. – Silicomancer Jul 18 '16 at 07:31
  • 1
    @Silicomancer Yes I'm sure. I moved the owner object into a QThread – sorush-r Jul 18 '16 at 08:44
  • 1
    I am not sure if you do it the right way. You mentioned "moving the object". Please post that code. Note that Qt documentation says: "A connection can only be used from within the thread that created it. Moving connections between threads or creating queries from a different thread is not supported." See http://doc.qt.io/qt-5/threads-modules.html#threads-and-the-sql-module. – Silicomancer Jul 18 '16 at 08:48
  • 1
    This means addDatabase(), open() and query exec must be called in the same thread. moveToThread() doesn't help here. I would try to confirm this by printing the thread IDs to the console where these three functions are called. – Silicomancer Jul 18 '16 at 09:00
  • 1
    @Silicomancer `movToThread()` does help if `addDatabase()`,`open()` and all other methods of `QSqlDatabase` instance are executed within a slot , invoked by a signal from another thread. In other words, all code of database business is executed in event loop of the thread that the object is moved to. I'm gonna write a small test case at home tonight. – sorush-r Jul 18 '16 at 09:03
  • 1
    Yes, if it is done that way, it should work. – Silicomancer Jul 18 '16 at 09:08

2 Answers2

9

You might not care after your last update, but I have a theory based on it: this indicates mysql_library_init() has to be called from main() for multithreaded applications.

If you look in the Qt plugin source, that method is wrapped in qLibraryInit(), which is called from the QMYSQLDriver : QSqlDriver constructors, which in turn I believe get created indirectly by addDatabase() in your usage context.

The MySQL docs note that the mysql_library_init() can be done protected by a mutex, which would entail the QtSql code protecting all QSqlDriver construction, which I don't think it does. So I'm not sure if this would be considered a Qt code bug or a gap in documentation.

This all fits the behavior you've described, but I'm still doubtful of myself - if this is correct it surprises me that more people haven't run into this and it's not more evident on SO and other fora. I guess it's a little unusual to do your first DB activity on a spawned thread vs. at least some initial work in the main thread?

Community
  • 1
  • 1
eclarkso
  • 1,087
  • 9
  • 21
  • 1
    This is exactly the reason to failure, and no it's not documented anywhere in Qt. thanks – sorush-r Jul 19 '16 at 07:58
  • 1
    Found https://bugreports.qt.io/browse/QTBUG-31468 after the fact, and reported https://bugreports.qt.io/browse/QTBUG-54872 as a related but separate issue. – eclarkso Jul 20 '16 at 15:49
  • @sorush-r this is very old, but Qt folk have tried and failed to replicate this issue (https://bugreports.qt.io/browse/QTBUG-54872?focusedCommentId=477366). I cannot either (on 5.9). Any chance you can? – eclarkso Sep 23 '19 at 15:50
-1

There is a bug related with QSqlDatabase::isOpen() in Qt. http://bugreports.qt-project.org/browse/QTBUG-223

QSqlQuery::lastError() should give you an error if your query via QSqlQuery::exec() has failed. Also QSqlDatabase::isOpen() should report the state of your connection, QSqlDatabase::lastError() is also available

Vanya Avchyan
  • 880
  • 7
  • 24