Switch to MySql

Forum for posting problems using QxOrm library

Switch to MySql

Postby gianks » Tue Sep 27, 2016 11:26 pm

Hi,
i've finally moved my backend on MySql and even if everything in the beginning seemed fine i'm now encountering strange behaviuors with the relationships.
I don't fully undestand what's going on but i'm experiencing both, sometimes it doesn't take in consideration at all such relationships in insert (but normally loads everithing in fetch and 'apparently' works in update) and while saving an already existing object i obtain this error (after a bounce of successful ops on the same table):

"Column 'live_program_id' specified twice QMYSQL: Unable to execute query"

This is the attempted query:
INSERT INTO SubscriptionPlanItem (subscription_plan_id, live_program_id, subscription_plan_id, live_program_id) VALUES (:subscription_plan_id, :live_program_id, :subscription_plan_id, :live_program_id)
QMYSQL: Unable to execute query

This is a simple ManyMany extra table (2 cols in PK both involved in FK) but after having spent some hours in debugging it's now currently threated as a regular OneToMany/ManyToOne in the four cases. Even if i suppressed the many to many relationships hypothesizing a potential overlap causing the wrong query the behaviour isn't changed.

As a final note is funny that the entire application was perfecly working with SqLite3. Changing backend has broken something i guess.

Thanks a lot
gianks
 
Posts: 45
Joined: Mon Jul 04, 2016 1:04 pm

Re: Switch to MySql

Postby qxorm » Wed Sep 28, 2016 7:45 am

Hello,

"Column 'live_program_id' specified twice ...
2 cols in PK both involved in FK

I think that you have followed my advice in this topic to manage FK which are part of PK : viewtopic.php?f=2&t=730

And I think that MySQL is right and this is a bug in QxOrm library.
About SQLite : SQLite is just more permissive than MySQL ==> this is why SQLite doesn't throw any error in this case.

I will try to fix it ASAP and give you a BETA version of QxOrm library with the fix.
I hope I can provide you a fix this week...
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: Switch to MySql

Postby gianks » Wed Sep 28, 2016 5:42 pm

Hi, as usual i'd like to thank you for the quick and precise support you are giving.

The suggestion you gave me in that thread is still not applied because i had already found at that time a way that was working.
I've not explained completely the setup of A, B and the extra table: this is a different set of classes and no OneToOne is used here.

A has (had at this point, but i'm gonna rollback every cahged i did in the last two days on this) both a OneToMany with the extra table and a ManyToMany with the final table.
Same thing for B. The extra table has a composed PK and has two OneToMany relationships, one with A, one with B. As said in this case the trick you proposed is not necessary since QxOrm doesn't raise any error and i think this is allowed by the complex PK handling which uses (i guess) the 'piped columns name' instead of the individuals cols, making it acceptable.

I decided to use vector for items (extra table rows) and collection as the many to many container.
This let me access both the list of item or the collection browsing it by id of the counterpart table, all using templates.
This way fetching objects with all relations (A or B) i was obtaining a complete representation of both the many to many and the one to many.

The code i've written on top of QxOrm does the job of keeping aligned at runtime any change i do on any of the structures: if i add an element to the vector (any), the counter vector, the collections and the relative items are updated correctly so i can actually apply the changes in any convenient way, depending on the goal. The extra table is integrated in this mechanism so in case i alter any of the two PK columns this is reflected in the three involved objects (old A/B, new A/B, party A or B) in all the relevant lists.
A caching mechanism is finally avoiding runtime duplication of an already fetched object which is still 'alive', replacing before the fetch completion all the objects that are already in cache in the resulting list.

That said now i'd like to ask you: is there any problem with this strategy that can lead QxOrm to misunderstand (after the new fix) my intentions with mysql?
One downside in this approach is the need to keep in memory a structure of all the depending objects otherwise the alignment ops (vectors and collections) are gonna fail since the relationships are assumed to be fully loaded each time. The second disadvantage is in saving operations: i cannot use anymore with both A and B save_with_all_relations since i've noticed QxOrm is doubling the ops against the extra table, first following what's stated in the vector (OneToMany) and than what is in the collection (ManyToMany): to fix this i just have to explicitly tell QxOrm which relations to save the object with, avoiding to include both the vector and the collection, which is not a big deal.
NOTE: If the extra table contains other cols than the PK then the collection relation has to be avoided since it correctly doesn't consider them on insert/update loosing data.

I couldn't be shorter, sorry.
Thanks a lot.
gianks
 
Posts: 45
Joined: Mon Jul 04, 2016 1:04 pm

Re: Switch to MySql

Postby qxorm » Wed Sep 28, 2016 9:30 pm

is there any problem with this strategy that can lead QxOrm to misunderstand (after the new fix) my intentions with mysql ?

No, it should work with MySQL like on SQLite.

Here is a BETA version where I tried to fix your issue : https://www.qxorm.com/version/QxOrm_1.4.3_BETA_49.zip
Could you please test it and let me know if it is ok or not now (it would be great if you could test on SQLite and on MySQL) ?

FYI, here is the changes log of this version :
- Support CMake : new CMakeLists.txt file added to build QxOrm library with CMake
- Improve SQL error messages when qx::dao functions return a database error
- New parameter in singleton class qx::QxSqlDatabase to log SQL bound values (setTraceSqlBoundValues) : by default, bound values are logged when an error occurred
- New syntax to select columns to not fetch : -{ col_1, col_2, etc... }
- New function qx::dao::call_query_without_prepare() to execute specific SQL queries without prepared statement
- Improve QxModelView module : all QxOrm models (based on qx::IxModel interface) can be serialized to JSON format (including all relationships levels) : this is now another way to work with relationships and QML (thanks to JSON.parse() and JSON.stringify() javascript functions) without using nested models concept (so without using QxEntityEditor model/view generated classes)
- Improve qxBlogModelView sample project and QxOrm manual to show how to access to relationships data in QML (nested models or JSON)
- Fix a memory leak in qx::QxSqlRelation class
- Reduce output binary size (~20%) and compilation times (~20%) to build persistent classes based on QxOrm library
- Support unity build concept to reduce compilation times to build QxOrm library and C++ persistent classes generated by QxEntityEditor application : for more details, see _QX_UNITY_BUILD compilation option in QxOrm.pri or QxOrm.cmake configuration file
- Improve QxConvert module : possibility to store in database complex QVariant properties which contain QVariantMap, QVariantHash or QVariantList types (JSON format)
- Fix an issue with some databases when a foreign key is also a part of the primary key
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: Switch to MySql

Postby gianks » Fri Sep 30, 2016 12:01 am

Hi and thanks for the fast fix.
I'm encountering linking problem against the beta:

Code: Select all
build/Debug/GNU-Linux/main.o: nella funzione "qx::QxSqlQueryBuilder_Update<NGS::GSS::Server::GssUserPerson>::buildSql(QStringList const&, qx::QxSqlRelationLinked*)":
/usr/local/include/QxDao/QxSqlQueryBuilder.h:291: riferimento non definito a "qx::IxSqlQueryBuilder::verifyColumns(QStringList const&) const"


Is there anything changed in the pri i have to take note of? I've copied the 1.4.2 file in the folder before compiling and no error/warnings where returned.

Please let me know.
Thanks
gianks
 
Posts: 45
Joined: Mon Jul 04, 2016 1:04 pm

Re: Switch to MySql

Postby qxorm » Fri Sep 30, 2016 12:54 pm

Hello,

I'm encountering linking problem against the beta

Be careful : I think that you mix debug and release shared libraries.
I think that you have built QxOrm in release mode, and try to use it in your project in debug mode.

Anyway, if you really want to do that, try this new BETA version : https://www.qxorm.com/version/QxOrm_1.4.3_BETA_50.zip
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: Switch to MySql

Postby gianks » Fri Sep 30, 2016 4:29 pm

Hello,
i've recompiled the 49 BETA with 'make debug' instead of 'make', my fault.
I've clean the entire project but the linking process of the main application still raises these errors:

Code: Select all
/usr/local/include/QxDao/QxSqlQueryBuilder.h:291: riferimento non definito a "qx::IxSqlQueryBuilder::verifyColumns(QStringList const&) const"
build/Debug/GNU-Linux/main.o: nella funzione "qx::QxSqlQueryBuilder_Update<NGS::GSS::Server::GssRecording>::buildSql(QStringList const&, qx::QxSqlRelationLinked*)":
/usr/local/include/QxDao/QxSqlQueryBuilder.h:291: riferimento non definito a "qx::IxSqlQueryBuilder::verifyColumns(QStringList const&) const"
build/Debug/GNU-Linux/main.o: nella funzione "qx::QxSqlQueryBuilder_FetchAll<NGS::GSS::GssLiveProgramMedia>::buildSql(QStringList const&, qx::QxSqlRelationLinked*)":
/usr/local/include/QxDao/QxSqlQueryBuilder.h:186: riferimento non definito a "qx::IxSqlQueryBuilder::verifyColumns(QStringList const&) const"
build/Debug/GNU-Linux/main.o: nella funzione "qx::QxSqlQueryBuilder_FetchAll<NGS::GSS::Server::GssRecording>::buildSql(QStringList const&, qx::QxSqlRelationLinked*)":
/usr/local/include/QxDao/QxSqlQueryBuilder.h:186: riferimento non definito a "qx::IxSqlQueryBuilder::verifyColumns(QStringList const&) const"
collect2: error: ld returned 1 exit status


After the recompilation of the library some others linker issues are disappeared but these are not... my application is surely compiled in debug with all my libraries.

I'm going to try with the new releaseyou provided me but i'd like to understand how to fix this if really coming from a trouble in my settings.
The project isn't changed and with the last 1.4.2 (debug) the compilation occurs without problems.

Thanks
gianks
 
Posts: 45
Joined: Mon Jul 04, 2016 1:04 pm

Re: Switch to MySql

Postby gianks » Fri Sep 30, 2016 4:56 pm

Hi,
i wish to confirm that the compilation is successful with the BETA 50.
Still trying to figure out what's wrong with my compilation environment... aren't any other potential causes of that?

I'm using NetBeans and the setup is 1 Qt Application + 4 Qt Shared Libraries. All the five projects are setup with the Degug configuration.

Last note aboute QxOrm building: i've noticed that a common 'make debug && make install' produces the compilation of the source twice as the make install is using the default/release way. To obtain the debug version installed i issued 'make debug install' which has compiled the lib one time and finally installed as expected.
Is this correct? Honestly i'm not a big expert of QMake / Make, sorry.

Thanks a lot, i'm gonna test the fix on both DB as requested right now, will share the results.
Kind regards
gianks
 
Posts: 45
Joined: Mon Jul 04, 2016 1:04 pm

Re: Switch to MySql

Postby qxorm » Fri Sep 30, 2016 5:20 pm

i wish to confirm that the compilation is successful with the BETA 50

Great !

Still trying to figure out what's wrong with my compilation environment... aren't any other potential causes of that?

Before releasing a version, even a BETA version, I test it on all my environments Windows, Linux and MacOSX.
And there is at least 1 customer which is using a very recent QxOrm 1.4.3 BETA version at a production level.
So I think there is something wrong in your environment.

QxOrm 1.4.3 BETA 50 is more permissive about mixing debug/release shared libraries, this is why you don't have any error now.
But I would recommend to check your dev environment.

About Netbeans and make install : sorry I don't know.
I suppose make install is just for release mode, but maybe I'm wrong, I'm not working like that.
And I'm not an expert in Unix development environment.

i'm gonna test the fix on both DB as requested right now, will share the results

Great, I will wait for your results.
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: Switch to MySql

Postby gianks » Fri Sep 30, 2016 6:59 pm

I was sure the problem was mine but i wanted to be sure.
Can you tell me what you did to make it more permissive? I've no clues to start from to dig into this and maybe the change you did to allow can enlighten my way :D

Currently i've found a bug my side and i'm fixiting it before doing any test, it will require some additional time, will be back ASAP.
Thanks
gianks
 
Posts: 45
Joined: Mon Jul 04, 2016 1:04 pm

Next

Return to QxOrm - Help

Who is online

Users browsing this forum: No registered users and 4 guests

cron