MySQL driver - Some versions read but not write database

Andrea_M

Thank you very much for your reply.
Yesterday I did some experiments.

In my code, the user can insert a procedure, that is a simple list of actions, with some parameters:

For example:
Method "Assembly":
Phase 1: "check packing list"
Phase 2: "check code of peach parts"
Phase 3: ....

If the user needs to change, add or remove a single phase, the program accesses to database, remove all the phases for method "assembly" and rewrite the new list(I know it should be better an update, but this function is seldom used, so I prefer having al linear sequence of phases when I look directly to database).

This is an example of what the code does:

qryDel->prepare("DELETE FROM method_phase WHERE id_method=" "'"+id_Method+"'" "");
qryDel->exec();
...
AddTest_Method.prepare("INSERT INTO method_phase (id_method , id_phase, id_parameter, parameter_value) VALUES (:id_method , :id_phase, :id_parameter, :parameter_value)");
                                AddTest_Method.bindValue(":id_method",id_Method);
                                AddTest_Method.bindValue(":id_phase",id_Phase);
								......

Now I modify a method with 5 action, adding one action:
Method "Assembly":
Phase 1: "check packing list"
Phase 2: "check code of each part"
Phase 3: ....
Phase 4: ....
Phase 5: ....
Phase 6: "Say bye-bye to everyone"

I didn't receive any error checking in "queries.log" file, the messages related to different versions are the same:

Working version (5.13.2 64 bit)

3 Prepare	DELETE FROM method_phase WHERE id_method='34'
		     3 Reset stmt	
		     3 Execute	DELETE FROM method_phase WHERE id_method='34'
		     3 Close stmt	
		     3 Prepare	SELECT id_method FROM method WHERE method='Assembly'
		     3 Reset stmt	
		     3 Execute	SELECT id_method FROM method WHERE method='Assembly'
		     3 Close stmt	
			 ............
			 3 Prepare	INSERT INTO method_phase (id_method , id_phase, id_parameter, parameter_value) VALUES (? , ?, ?, ?)
		     3 Reset stmt	
		     3 Execute	INSERT INTO method_phase (id_method , id_phase, id_parameter, parameter_value) VALUES ('34' , '9', '5', '30')
		     3 Close stmt	
		     3 Close stmt	
		     3 Close stmt

Not-Working version (5.15.3 64 bit)

7 Prepare	DELETE FROM method_phase WHERE id_method='34'
		     7 Reset stmt	
		     7 Execute	DELETE FROM method_phase WHERE idmethod='34'
		     7 Close stmt	
		     7 Prepare	SELECT id_method FROM method WHERE method='Assembly'
		     7 Reset stmt	
		     7 Execute	SELECT id_method FROM method WHERE method='Assembly'
		     7 Close stmt	
			 ............
			 7 Prepare	INSERT INTO method_phase (id_method , id_phase, id_parameter, parameter_value) VALUES (? , ?, ?, ?)
		     7 Reset stmt	
		     7 Execute	INSERT INTO method_phase (id_method , id_phase, id_parameter, parameter_value) VALUES ('34' , '9', '5', '30')
		     7 Close stmt	
		     7 Close stmt	
		     7 Close stmt

I don't know if it's relevant, but I indicate the exact dimensions of the .dll files, so if someone will note differences than a working installation, I can think the problem is in plugin compiling:

C:\Qt5\5.13.2\mingw73_64\plugins\sqldrivers\ : 72 704 byte
C:\Qt5\5.15.3\mingw81_64\plugins\sqldrivers\qsqlmysql.dll : 159 041 byte
I'll contact the qt helpdesk to try to discover if there is a reason for this strange behavior.
Have a nice day!

Andrea_M

Hi,
I have a little updating.
I try to run the QT example, connecting with my database: it works with all versions!
So I start to investigate some possible critical setups, using Windows and Linux. Well, I think that the problem is in calling of DB connection.
In my original code, I have the main app connecting with the database, and all the other classes/functions use this default connection:

(myApp.h)

............
QSqlDatabase myDB;
............

(myApp.ccp)

............
myDB = QSqlDatabase::addDatabase("QMYSQL");
    myDB.setHostName("localhost");
    myDB.setDatabaseName("MainDB"); 
    myDB.setPort(3306);
    myDB.setUserName("root");
    myDB.setPassword("password");
        if (!myDB.open()) {
                QMessageBox::critical(this,"Error",myDB.lastError().text());
                return;
                }
............

(secondApp.h)

............
QSqlDatabase myDB;
QSqlQuery * qry;
............

(secondApp.cpp)

............
QSqlQuery * qry;
qry= new QSqlQuery(myDB);
qry->prepare("SELECT * FROM ......); 
............

(thirdApp.h)

............
QSqlDatabase myDB;
QSqlQuery * qry2;
............

(thirdApp.cpp)

............
QSqlQuery * qry2;
qry2= new QSqlQuery(myDB);
qry2->prepare("SELECT * FROM ......); 
............

Now I tried to start a new connection, for example:

(secondApp.h)


myDB2 = QSqlDatabase::addDatabase("QMYSQL","SecondConnection");
    myDB2.setHostName("localhost");
    myDB2.setDatabaseName("MainDB"); 
    myDB2.setPort(3306);
    myDB.setUserName("root");
    myDB2.setPassword("password");
        if (!myDB2.open()) {
                QMessageBox::critical(this,"Error",myDB2.lastError().text());
                return;
                }
............

QSqlDatabase myDB2;
QSqlQuery * qry;
............

(secondApp.cpp)

QSqlQuery * qry;
qry= new QSqlQuery(myDB2);
qry->prepare("SELECT * FROM ......);

it works (it is able to write datas), but it crashes each time, after the execution of the insert or update function.

It seems that the default connection had some problem, but the strange point is that with 5.13.2/64 bit I haven't this behavior.
I'm thinking the used method for db connection has some wrong points, even if I try to understand what is the mistake.

Christian Ehrlicher

@Andrea_M said in MySQL driver - Some versions read but not write database:

and all the other classes/functions use this default connection:

In the same thread? You must not share a QSqlDatabase accross different threads.
You also should not save the QSqlDatabase as member (see documentation):

"Warning: It is highly recommended that you do not keep a copy of the QSqlDatabase around as a member of a class, as this will prevent the instance from being correctly cleaned up on shutdown. If you need to access an existing QSqlDatabase, it should be accessed with database(). If you chose to have a QSqlDatabase member variable, this needs to be deleted before the QCoreApplication instance is deleted, otherwise it may lead to undefined behavior."

qry= new QSqlQuery(myDB2);

Why do you create them on the heap (and maybe forget to delete them)?

Andrea_M

Hi, Thank you very much for your suggestion: I looked at different tutorials and code examples, but reading the official documentation should be a better way!
I started to modify my app according to your notes, in particular about QSqlDatabase object as a member of a class, and to obtain positive results!
This is my app schemes:
main -> Login -> MainWindow (with stacked widgets) -> Test widget (that is the widget (stacked) reading and writing some records in database)

In main.cpp I start a default connection:
....
QApplication a(argc, argv);
QSqlDatabase myDB = QSqlDatabase::database();
myDB = QSqlDatabase::addDatabase("QMYSQL");
myDB.setHostName("localhost");
myDB.setDatabaseName("Database");
myDB.setPort(3306);
myDB.setUserName("root");
myDB.setPassword("password");
myDB.open();
login w;
w.show();
...

If I start directly "Test" widget from main.cpp ( for example, modifying "login w" with "Test w") ,all ok.
If I want to load "Test" as a stacked widget as usual, I need to create a new connection. As I had about 10 stacked widgets, I had to call ten connections (I figure stacked widgets need it as the threads).

I didn't yet understand why in 5.13.2 it works without the new connections.

Of course, if this solves the fault, it is not a problem having multiple connections: it's a locally hosted database with modest traffic, so I think it's not a great problem about data exchange, but I'm wondering there is a different way.
Thank a lot to everyone!

JonB

@Andrea_M said in MySQL driver - Some versions read but not write database:

If I want to load "Test" as a stacked widget as usual, I need to create a new connection. As I had about 10 stacked widgets, I had to call ten connections (I figure stacked widgets need it as the threads).

Of course, if this solves the fault, it is not a problem having multiple connections: it's a locally hosted database with modest traffic, so I think it's not a great problem about data exchange, but I'm wondering there is a different way.

You should have one database connection, not 10. Stacked widget or not. I don't know what "need it as the threads" means.

Andrea_M

@JonB
Hi,
At first time I tried to use default connection, but in this case my widget loaded data from database, but it faulted in writing. By now, the only way to obtain a read/write access is creating a new connection at every widget.

    myDB2 = QSqlDatabase::addDatabase("QMYSQL","SecondConnection");
    myDB2.setHostName("localhost");
    myDB2.setDatabaseName("MainDB"); 
.....

This seems me strange, but I can't find other solutions to solve it.
Have a nice day!

jsulm

@Andrea_M said in MySQL driver - Some versions read but not write database:

but it faulted in writing

Then you should analyse this problem instead of creating so many connections. What was the error/problem?

Andrea_M

@jsulm
I have no visible error: when I debug the application at the "INSERT" or "UPDATE" query, it goes ahead without any message.
I open the default connection as shown in the previous post (in main.cpp), then in each widget, where required, I call, for example:

QSqlDatabase myDB = QSqlDatabase::database();
....
QSqlQuery * qry;
qry= new QSqlQuery(myDB);
qry->prepare("INSERT ......);
qry->exec();
....

The new record is shown in the TableView linked to the database, but when I check directly the database I can't see anything. It seems just like I'm working on a temporary cloned database. I'm dealing with QT assistance about this, but, for now, the only method working is creating multiple connections

jsulm

@Andrea_M said in MySQL driver - Some versions read but not write database:

qry= new QSqlQuery(myDB);

Why do you allocate the query in the heap?
You should check what exec() returns.
You also should check what https://doc.qt.io/qt-5/qsqlquery.html#lastError returns after exec().

JonB

@Andrea_M
In addition to @jsulm, even earlier than the exec() you must and should check the return value of qry->prepare("INSERT ......);

Returns true if the query is prepared successfully; otherwise returns false.

There is a strong chance that, depending on the content of your INSERT statement, preparation may fail.

As a general observation, all Qt/system/etc. calls which return some kind of result should always be checked. Especially before reporting a fault or if you see unexplained behaviour. This practice is essential for good software development.

Andrea_M

This was the first checking I did, I placed in INSERT, UPDATE and DELETE queries a qDebug,
for example:

if(qryDel->exec())
 {
 qDebug()<<"DELETE OK";
 }

And I received "DELETE OK", but in database the record was yet present. I forced the QSqlQuery::lastError() message (previously it was used only when the query gives an error), but no message yet. I suppose there is an error somewhere in my code, but with the 5.13.2 version I didn't have any problem, so I can't find a clear reason for this.
I added after these queries:

qDebug()<<"DB status"<<QSqlDatabase::database();

to check if the default connection is ok, and I received:

DB status QSqlDatabase(driver="QMYSQL", database="myDB", host="localhost", port=3306, user="root", open=true)

No result in the database, but, as usual, compiling with old versions (5.13.2 5.11.3), all is ok.