Mysql table locking error

[Kutyus]

Hi!

In qt 5.13.2 I using mysql database plugin but table locking not working property.

The "lock table tablename" is fine, but executing the "unlock tables" gives a false result and the database is stayed in locked state.

Thanks

[jsulm]

@Kutyus What does https://doc.qt.io/qt-5/qsqlquery.html#lastError return after "unlock tables"?

[Kutyus]

@jsulm LastError().text() is empty.

[JonB]

@Kutyus
Probably show your code so we can see (including where "unlock tables" gives a false result but no lastError() information). Also read through e.g. https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html#table-lock-release to make sure you are complying with unlock requirements/behaviour.

[Kutyus]

@JonB
If I run a QSqlQuery::clear() before the "unlock tables", it is working, but I do not know why?

[JonB]

@Kutyus
This is purely a guess.

While you have some existing SQL query around/open which has a LOCK TABLES or is in a transaction with such, locks can't be released? You need to finish reading/close/terminate/clear the query to release the result set/previous query so that unlocking can proceed?

[Kutyus]

@JonB
Yes that is how it works.

If I comment out the query between lock and unlock, it works that way.

[SGaist]

Hi,

Can you show the code your using ?

[Christian Ehrlicher]

@JonB said in Mysql table locking error:

LOCK TABLES

Why do you need it at all?

[JonB]

@Christian-Ehrlicher
It's not me, it's the OP!

@Kutyus wrote:

The "lock table tablename" is fine, but executing the "unlock tables" gives a false result and the database is stayed in locked state.

I believe the OP is using LOCK/UNLOCK TABLES. Whether that is a good idea is another matter :) Perhaps they will explain.

[Kutyus]

@SGaist

I derived QSqlQuery and there is an error when I call prepare (), but the factory QSqlQuery also produces an error when prepare () is called but clear () is not.

QSqlQuery query = QSqlQuery(db);

query.prepare(QString("lock table categories write"));
query.exec();

query.prepare(QString("update categories set name = '%1' where id = %2").arg(name.text()).arg(id));
query.exec();

query.clear();
query.prepare(QString("unlock tables"));
query.exec();

[SGaist]

You should check the outcome of each of your queries and print the error if any occurs. That should give you more insight about what is going on.

[JonB]

@Kutyus said in Mysql table locking error:

query.prepare(QString("lock table categories write"));

You're supposed to take the time to read the documentation (https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html) of your database backend before asking for Qt help here. There is no lock table statement.

[Kutyus]

@SGaist
I found a solution, the lock table and the unlock tables statements cannot be prepared, I received the following message:
"This command is not supported in the prepared statement protocol yet QMYSQL3: Unable to prepare statement"