Documentation unclear on multi-threading support, connections

[cjcox17]

On the page: https://www.tinyorm.org/database/getting-started

It states that multithreading support exists, excluding schema builder and migrations, but immediately after that, it says that DB::connection must be used in the thread that it was created in, which means that multi-threading support doesn't really exist at all. Maybe you mean that creating connections in multiple threads running simultaneously is supported? Though I may be misunderstanding.

Additionally, when creating multiple DB::create methods from multiple threads throws an exception

Only one instance of DatabaseManager is allowed per process.

Can you clarify on the specifics?

I would imagine that if each thread is capable of handling its own connections and you can have multiple threads, each with its own connection definitions, it must be possible to create a connection pool at a higher level and track if it's currently in use, lock it, and use it when it is not. Creating a large number of connections to SQL servers is an expensive operation rather than utilizing a connection pool where X number of connections can stay open, and new ones created when needed.

Is this possible?

[silverqx]

You can create xyz connection in 3 threads A, B, C at the same time and it will work ok, this connection can be to the same database or to another database.

You can not create a connection xyz in thread A and call a query on this connection instance from thread B, all queries for this connection instance must be called from thread A.
If you want to send a query from thread B you have to create a new xyz connection instance in thread B and then you can send a query.

Also, migrations and the schema builder can be called only from the main thread (or any thread but only one thread at a time), the main reason are static data members and static local variables which are not thread_local.

Also, what is the exact reason for this?

We have to follow the same rules and restrictions as the QtSql module has described here. Thanks god that Qt doesn't support this because it would be a nightmare to synchronize everything.

Is this possible?

Yes, it's possible, you can create a connection pool.

[silverqx]

Additionally, when creating multiple DB::create methods from multiple threads throws an exception

Only one instance of DatabaseManager is allowed per process.

The DB::create() method are shortcut methods that create a DatabaseManager instance, adds a given connection configuration, and sets a default connection name at once.

The DatabaseManager can exist only once per whole process/application and you can call all methods on the DatabaseManager or DB facade from any thread (it's synchronized).

Use DB::addConnection() to add another connection, you can also create DatabaseManager using the DatabaseManager::create().

[cjcox17]

In this case:

QSqlDatabasePrivate::database: requested database does not belong to the calling thread. terminate called after throwing an instance of 'Orm::Exceptions::SqlError' what(): Failed to open database connection in Connector::createQSqlDatabaseConnection(). QSqlError(Driver not loaded, Driver not loaded)

I imagine that I'll need to create a method to create unique connection names per thread, and then remove them from the Database Manager when that thread has ended?

[silverqx]

You don't have Qt database driver ready, to what DB you are trying to connect and which OS?

[silverqx]

Yeah, you are right, connection names have to be unique across threads if I correctly remember.

[silverqx]

You can also try QSqlDatabase::addDatabase() and then QSqlQuery("select version()", connectioName), skipping tinyorm to test if it works.

[cjcox17]

I have it working on one thread, I’ve moved things around so there is only one instance of database manager ever called, but in order to get the database drivers loaded I have to use QApplication::addLibraryPath(“xyz”)I’m wondering if this is also thread specific. I’ll try again in a bit.Sent from my iPhoneOn Mar 11, 2023, at 12:15 PM, Silver Zachara ***@***.***> wrote: You don't have Qt database driver ready, to what DB you are trying to connect and which OS? —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you authored the thread.Message ID: ***@***.***>

[silverqx]

I have it working on one thread

I was a little confused with the error message, I think it's because of the same connection name, it happened to me too, but it was 1 year ago so I can be wrong.

I have to use QApplication::addLibraryPath(“xyz”)I’m wondering if this is also thread specific.

I don't think so.

[cjcox17]

I've confirmed that QApplication::addLibraryPath(“xyz”) is not thread specific

[silverqx]

TinyOrmPlayground calls ~1500 queries against 3 databases in 3 threads, 500 against MySQL, 500 PostgreSQL, and 500 SQLite at the same time. The performance gain was amazing when I implemented this.

But I have never tried to create more connections from different threads to one or to the same database though.

[cjcox17]

This works.

Connection names must be unique across all threads, but you can create any combination of anything else, drivers, threads, at the same time. I'll just need to write wrappers to create unique names, and remove them when a thread has ended. Hopefully the build up/tear down isn't too much of a performance hit. It's unfortunate that as great as the Qt library is, they've really killed SQL usability here with these thread limitations.

Though, I've run into some other issues with TinyORM while testing this since I'm now required to use on("connection-name").

This works

Post::find(1);
T::on("model_helper_mysql")->find(1);

This also works
Post::destroy(1);

This does not
Post::on("model_helper_mysql")->destroy(1);

There is no method available.

Additionally, while using model->dump(), on a model after using

Post::on("connection-name")->find(1);

The connection doesn't persist, and it tries to load from the database again instead of grabbing off the the model that's already been found, causing it to crash like before.

[cjcox17]

Ok, so TinyORM at least crashes when connection names are reused.

DB::addConnection({connection});
Post::find(1);
DB::removeConnection({connection});

Fails when done repeated if the connection name is the same even though it was removed, then readded.

I'm trying to bypass TInyORM and just do QSqlQuery("select version()", connectioName) but there is no QSQLQuery constructor that takes a connection name as the second paramater. At least in Qt 6.4

[silverqx]

I check it tomorrow, if you find anything else don't be shy to post it.

QSqlQuery takes QSqlDatabase as the second argument, like QSqlQuery("select version()", QSqlDatabase::database("xyz")), and of course first you have to create a DB connection, look docs.

[cjcox17]

In order for this to work properly the QSqlDatabase and QSqlQuery must be out of scope, which poses another issue because it doesn't work otherwise. But this does work without any issues.

       {
            QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL", "model_helper_mysql");
            db.setHostName(config.get("mysql_host").toString());
            db.setDatabaseName(config.get("mysql_database").toString());
            db.setUserName(config.get("mysql_user").toString());
            db.setPassword(config.get("mysql_password").toString());
            db.setPort(config.get("mysql_port").toInt());
            db.open();
            auto query = QSqlQuery("select version()", db);
            query.exec();
            qDebug() << query.result();
            query.clear();
            db.close();
        }
        QSqlDatabase::removeDatabase("model_helper_mysql");`

[silverqx]

This does not
Post::on("model_helper_mysql")->destroy(1);

To have destroy() also after calling on() it's not possible and would be ~5 lines to clarify the reason why it's so, it's an API design thing. But after calling on() you can call query builder (or in this case TinyBuilder) methods, like Post::on("model_helper_mysql")->whereKey(1).remove() or deleteModels() is alias for remove().

Ok, so TinyORM at least crashes when connection names are reused.

DB::addConnection({connection});
Post::find(1);
DB::removeConnection({connection});

Nice catch friend, really 👍, this is a bug and not a simple one, omg. how is possible it never happened to me, I'm invoking and compiling the whole library 100 times per day and I never saw it. 😲

[cjcox17]

but still need to be able to access connection pools without having to worry about threading(think connections tracked in a deque and reconnected as needed using shared pointers with global pools on the heap.

How are you connecting to the DB now? Without Qt, you must use MySQL c++ drivers directly in this case, or postgres c driver.

Building up a ton of new connections constantly and then dropping them for only a single select by primary key limit 1 is expensive and has way too much overhead.

You don't need to drop them, you can create as many worker threads as the CPU has with connections inside and then, ok now I see, you don't have any possibility to send the query to a particular thread to query the DB, is this the problem?

But have you considered moving from Qt to the Std libraries and writing some connection factories for the standard mariadb/mysql/pgsql/sqllite libs and a string-based query builder for SQL?

Noo 😎, the query builder is absolutely amazing, also the connection factory is amazing. ❤️

I'd love to see something like this be independent of another framework.

This would not be possible without the QVariant and especially QtSql module, I would have to write my own DB driver translation layer for every DB and instead of depending on only the Qt, it would depend on MySQL 8.0 C API, libpq - C Library for Postgres, and SQLite C/C++ Interface. I would practically rewrite what the QtSql is doing.

[silverqx]

Ok, so TinyORM at least crashes when connection names are reused.

DB::addConnection({connection});
Post::find(1);
DB::removeConnection({connection});

I have fixed it and pushed a new v0.28.1 release, currently, CI pipelines are running. Can you try if you can reproduce it?

Additionally, while using model->dump(), on a model after using

Post::on("connection-name")->find(1);

The connection doesn't persist, and it tries to load from the database again instead of grabbing off the the model that's already been found, causing it to crash like before.

Also, how can I reproduce this?

[cjcox17]

I've switched gears development wise, when I get to a stopping point I'll switch branches and try out the add/remove connection code again and see if it fails.

Also, I can't copy and paste that code anymore, but it was something like this

DB::addConnection({connection});
auto post = Post::on('connection')->find(1);
post->dump()

The issue is that calling post->dump() threw a fresh query(this may be expected behavior), but I anticipated that it would attempt to dump cached data for the query if it existed instead of making another database call.

Because it created a new database call, it didn't attempt to make it on the connection that was originally made with ``auto post = Post::on('connection')->find(1);` and it crashed throwing a DB exception similar to the ones I got when running a model::find(x) on connections that didn't exist.

[cjcox17]

Basically this would work:

auto post = Post::find(1);
post->dump()

But this wouldn't

auto post = Post::on('connection')->find(1);
post->dump()

I think what's happening is that post->dump():

• Only attempts to get the default connection.
• Does not attempt to get the connection given to on()
• Runs a fresh query when ideally it should dump already fetched data.

[silverqx]

Basically this would work:

auto post = Post::find(1); post->dump()

But this wouldn't

auto post = Post::on('connection')->find(1); post->dump()

I have removed the Model::dump() and Model::dd() methods from the ModelProxies, they were pointless and confusing. They were not supposed to call like that. They were static methods so they should be called like User::dump() and internally they called User::query()->dump().

The dump() should be called on the query builder, and now after I have removed the proxies, it's the only one possibility, like:

User::whereEq("name", "xyz")->select({"id", "name"}).orderBy("name")->dump();

[silverqx]

I have checked this morning how threading works in the libpq and MySQL c API.

From MySQL c API docs:

Multiple threads cannot send a query to the MySQL server at the same time on the same connection. In particular, you must ensure that between calls to mysql_real_query() (or mysql_query()) and mysql_store_result() in one thread, no other thread uses the same connection. To do this, use a mutex lock around.

From libpq docs:

One thread restriction is that no two threads attempt to manipulate the same PGconn object at the same time. In particular, you cannot issue concurrent commands from different threads through the same connection object. (If you need to run concurrent commands, use multiple connections.)

From QtSql docs:

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.

The QtSql module is breaking this feature, I'm at 100% sure that this would also work with the QtSql practically without any changes, it would be enough to remove this thread check and make check that will record current thread id that made the query and ensure that all other or following queries are from the same thread. Or remove that check entirely and leave it totally to the user how it will synchronize it.

But it would have to be tested first if everything works correctly, I think it would be a lot of work to make it real. TinyORM doesn't have a problem with it, I have checked all globals and it would be totally possible, it's treated in tinyorm code.

[silverqx]

The most funning thing is that Qt is talking about restrictions in third-party libraries, but the biggest restriction is on their side .😎

From Qt docs:

In addition, the third party libraries used by the QSqlDrivers can impose further restrictions on using the SQL Module in a multithreaded program. Consult the manual of your database client for more information