Welcome, Guest
Username: Password: Remember me
Components and Libraries for Database Development, discussions, problems and suggestions
  • Page:
  • 1

TOPIC:

Bug in ZeOS DBO 8 years 8 months ago #7976

  • Javor
  • Javor's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • M.Sc in Computer Science, (ex-)freelancer
  • Posts: 72
  • Thank you received: 8
Found a severe bug in ZeOS DBO, connecting to MySQL server. Here's what I found and tested:
CodeTyphon: 5.2
ZeOS DBO: 7.2.0-beta (this ships with CT 5.2). Currently there's no newer version (last stable is 7.1.4) I think.
MySQL server: 5.1.47-community
Bug is when one try to use several threads, each with its own TZConnection and TZQuery, which connect to MySQL server and run a query. According to info on libmysql.dll, it's thread safe as long as it's not running more than one query on the same connection in each thread. When TZConnection is put in a thread and connects, query runs fine (at the same time from several threads), but when connection is disconnecting or freeing - exception is thrown.
Trying this from several classes, each with its own TZConnection and TZQuery is OK - everything runs fine, connections are freed.
Can't believe nobody tested this so far :ohmy:
Sadly, I don't have time to trace and dig deeper.

Please Log in or Create an account to join the conversation.

Bug in ZeOS DBO 8 years 8 months ago #7977

  • Sternas Stefanos
  • Sternas Stefanos's Avatar
  • Offline
  • Moderator
  • Moderator
  • Ex Pilot, M.Sc, Ph.D
  • Posts: 4518
  • Thank you received: 1102
Sir
my suggestion is to update your CT installation
to LAB CodeTyphon ver 5.5 and test again
PilotLogic Architect and Core Programmer

Please Log in or Create an account to join the conversation.

Bug in ZeOS DBO 8 years 8 months ago #7978

  • Javor
  • Javor's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • M.Sc in Computer Science, (ex-)freelancer
  • Posts: 72
  • Thank you received: 8
Sure thing. When I have time, I'll download and check CT Lab 5.5. Right now I'm working on a big and very important project, done with CT and can't afford to spend 1/2 day of installing and compiling. In the meantime, it would be great if anyone can test and confirm this problem.

Please Log in or Create an account to join the conversation.

Bug in ZeOS DBO 8 years 7 months ago #8234

  • Javor
  • Javor's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • M.Sc in Computer Science, (ex-)freelancer
  • Posts: 72
  • Thank you received: 8
Mr. Sternas, I've installed CT 5.5 (the official and not Lab version). Version of ZeOS DBO components is still 7.2.0 beta :(
And errors are again here. Tried both 'mysql' and 'mysql-5' protocols, yet the same.
I can provide here an example project. Seems like ZeOS have huge problems working with and creating/destroying connections to mysql server. No such problems happen if PostgreSQL server used...
Thought CT 5.5 will come with a newer version of ZeOS components. It's really important to me to find a way to work with several connections each in a thread simultaneously... Hope we can find a way to solve this problem.
Here's what's in ctinfo.txt:
pl_ZeosDBO
02-09-2015 ver 7.2.0.22 SVN trunk Rev 3641

Please Log in or Create an account to join the conversation.

Bug in ZeOS DBO 8 years 7 months ago #8235

  • Sternas Stefanos
  • Sternas Stefanos's Avatar
  • Offline
  • Moderator
  • Moderator
  • Ex Pilot, M.Sc, Ph.D
  • Posts: 4518
  • Thank you received: 1102
Sir
we want help for this
PilotLogic Architect and Core Programmer

Please Log in or Create an account to join the conversation.

Bug in ZeOS DBO 8 years 7 months ago #8236

  • Javor
  • Javor's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • M.Sc in Computer Science, (ex-)freelancer
  • Posts: 72
  • Thank you received: 8
Here's the example project as an attachment and an exception if mysql connection is NOT initialized in the main thread first. So far I think, I'll move my project to either MariaDB or PostgreSQL. Last one works for me in another big project. When I have more time, I'll dig deeper into this, but sadly deadline for current project are close, so...
Regarding the Exception (see comments in the example project), it occurs at
InternalCreate;
in ZDbcConnection, line 818. Sorry I can't provide more info and make debuggers life easier, but currently I have only this as info and no time to trace...

P.S. Situation with other databases is as following:
MariaDB 10: almost the same. I say almost, because the same code sometimes works, sometimes an exception is raised: "SQL Error: unknown MySQL server host 127.0.0.1 10093". Again no chance of working if lib is not initialized in the main thread. Sad.
PostgreSQL: works like a charm. It works even if you make 25 threads simultaneously open a table and read from it. It's all the same code. No need to init anything in the main thread, nor sync whatever.
Think I'll move my project to PostgreSQL so far.

Please Log in or Create an account to join the conversation.

Last edit: by Javor. Reason: Further tests made

Bug in ZeOS DBO 8 years 7 months ago #8237

  • Sternas Stefanos
  • Sternas Stefanos's Avatar
  • Offline
  • Moderator
  • Moderator
  • Ex Pilot, M.Sc, Ph.D
  • Posts: 4518
  • Thank you received: 1102
Sir
we will test and report
PilotLogic Architect and Core Programmer

Please Log in or Create an account to join the conversation.

Bug in ZeOS DBO 8 years 3 months ago #9091

  • Michael Hiergeist
  • Michael Hiergeist's Avatar
  • Offline
  • Junior Member
  • Junior Member
  • Posts: 24
  • Thank you received: 7
@Javour,

You'r such one having no idea about mutli threading + MySQL, sorry for my rough words.
But you're not alone:

See:
synopse.info/forum/viewtopic.php?id=3044
synopse.info/forum/viewtopic.php?id=3034
You'll find loads of more threads accordingly your regression. However read documentations instead of writing bad critics. Use a connection pool or set proper timeouts..

There is NO issue using Zeos + mutithreading + mysql. i'm doing this since years!
USE ONE Connection for each thread. Plug in a threadinpool which controls the timeouts.

END

Please Log in or Create an account to join the conversation.

Last edit: by Michael Hiergeist.

Bug in ZeOS DBO 8 years 3 months ago #9093

  • Javor
  • Javor's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • M.Sc in Computer Science, (ex-)freelancer
  • Posts: 72
  • Thank you received: 8

Michael Hiergeist wrote: @Javour,

You'r such one having no idea about mutli threading + MySQL, sorry for my rough words.
But you're not alone:

See:
synopse.info/forum/viewtopic.php?id=3044
synopse.info/forum/viewtopic.php?id=3034
You'll find loads of more threads accordingly your regression. However read documentations instead of writing bad critics. Use a connection pool or set proper timeouts..

There is NO issue using Zeos + mutithreading + mysql. i'm doing this since years!
USE ONE Connection for each thread. Plug in a threadinpool which controls the timeouts.

END


No offense dude, but I use multi-threading in DBs (especially MySQL) for such a long time, you probably haven't even coded back then. So far never ever had problems with it, especially under Delphi. I haven't tried REST or any other service, just use a simple connection and queries. Have you ever tried to look at my sample using the version of ZeosDBO that was used by CT version? I bet no, so please, don't talk about things you're not aware of. Ever since I code, I use a connection in every thread. Ever since then it worked like a charm. Ever since I tried this version of ZeosDBO (from my post along with CT from my post - read carefully), it happened. And the one to blame is Zeos, man. Downgrading back to 7.1 stable solved my both problems with MySQL and PostgreSQL with no further change made to fpc trunk or CT. I'm sorry that I don't have time to play around with CT, but my projects depend heavily on it and I have no time to waste on trial-and-error scenarios.
Please, read before post any further nonsense about this.
No offense again.
Have a happy coding :)

Please Log in or Create an account to join the conversation.

Bug in ZeOS DBO 8 years 3 months ago #9129

  • Michael Hiergeist
  • Michael Hiergeist's Avatar
  • Offline
  • Junior Member
  • Junior Member
  • Posts: 24
  • Thank you received: 7
Hi Javor,

THIS is no offence now. But my previous statment was just clear and right.
I made some researches for you which you should review because MySQL is NOT threadsafe on open a connection:
this costs me hours just to support you

Some routines 7.2 calls before a real connect happens and they are strictly required for embedded setup or user defined server setups:
dev.mysql.com/doc/refman/5.7/en/mysql-library-init.html
dev.mysql.com/doc/refman/5.7/en/mysql-server-init.html <--deprecated

dev.mysql.com/doc/refman/5.7/en/mysql-init.html


and also the the mysql_real_connect isn't threadsafe:
bugs.mysql.com/bug.php?id=79510

All in one i just did correct your example and added some notes. It's not a question of how many threads you're running or if libmysql has been loaded on startup. Nope you just have to connect before the thread is going async. This is clearly documented by mysql. Running several threads isn't the proplem but you need to open the connection on creating the thread before going asynced in background.

Since 7.2 dynloading different libs with multiple setups are supported. This was broken on 7.1. which strictly did load the libs.

What do you think?
Attachments:

Please Log in or Create an account to join the conversation.

Last edit: by Michael Hiergeist.

Bug in ZeOS DBO 8 years 3 months ago #9131

  • Javor
  • Javor's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • M.Sc in Computer Science, (ex-)freelancer
  • Posts: 72
  • Thank you received: 8
Hello Michael and thanks for the efforts made. I'm aware about a-must-connect-in-the-main-app-first (the hard way of course and which I believe I do in my sample project), before using any connections in threads (the async way as you call it). The fact, that I never had problems before (before using Zeos) made me wonder why all this is happening... But finding out it is impossible to use MySQL in a multithreaded environment, right after porting a project from Delphi hurts a lot. It's about what I do @ work and not a trial and error case. My project depended on MySQL and the case with 7.1 made me look for alternative SQL servers, with which Zeos works. Therefore I went to PostgreSQL.
Anyway, back in ver. 7.1 even the connection was opened in the main thread before spawning other threads was a problem. And, believe me, it wasn't with MySQL... I don't know if we're both clear enough to each other with the explanations and I don't intend to blame anyone. I just noticed there is something abnormal (so far in my experience) and that's all. If I had the time to write my own components, I probably should, but I'm on big projects and can't afford that.

Please Log in or Create an account to join the conversation.

Bug in ZeOS DBO 8 years 3 months ago #9132

  • Michael Hiergeist
  • Michael Hiergeist's Avatar
  • Offline
  • Junior Member
  • Junior Member
  • Posts: 24
  • Thank you received: 7
Hi Javor,

it might be possible entering a TCriticalSection resolves all the issued MySQL connect stuff. IMHO it's user task to setup the critical-sections for MySQL and build a theading pool. I can't change what MySQL is.. Fetching/Executing works well with MySQL + mutithread environment. Of couse all other drivers zeos does support are threadsafe. Just the MySQL issues in this domain. But it's documented.. and not a blame for Zeos as you stated before.

Cheers, Michael

Please Log in or Create an account to join the conversation.

Bug in ZeOS DBO 8 years 3 months ago #9170

  • Michael Hiergeist
  • Michael Hiergeist's Avatar
  • Offline
  • Junior Member
  • Junior Member
  • Posts: 24
  • Thank you received: 7
Hi Javor, Starnas Sir,

i took a moment and added the TCriticalSection to the TMySQLConnection.Connect. Your template works now without any problems. And without open a connection in the Main-Thread of course.

Current Trunk R3865 contains the fix and loads more FPC3 fixes.

Have fun!
The following user(s) said Thank You: Sternas Stefanos, Javor

Please Log in or Create an account to join the conversation.

Last edit: by Michael Hiergeist.

Bug in ZeOS DBO 8 years 3 months ago #9172

  • Sternas Stefanos
  • Sternas Stefanos's Avatar
  • Offline
  • Moderator
  • Moderator
  • Ex Pilot, M.Sc, Ph.D
  • Posts: 4518
  • Thank you received: 1102
Thanks Sir
we will update CT ASAP
PilotLogic Architect and Core Programmer

Please Log in or Create an account to join the conversation.

Bug in ZeOS DBO 8 years 3 months ago #9174

  • Javor
  • Javor's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • M.Sc in Computer Science, (ex-)freelancer
  • Posts: 72
  • Thank you received: 8
Thank you. Sorry for the delayed answer, but I was on a way around. Hope fix will be applied in the official versions too ;)
Regarding FPC fixes, any news on UTF8toXXX and the reverse ones?

Please Log in or Create an account to join the conversation.

Bug in ZeOS DBO 8 years 3 months ago #9175

  • Sternas Stefanos
  • Sternas Stefanos's Avatar
  • Offline
  • Moderator
  • Moderator
  • Ex Pilot, M.Sc, Ph.D
  • Posts: 4518
  • Thank you received: 1102
No news on UTF8toXXX and the reverse ones, Sir
We try some "things" before Final CT 5.70
we will see...
PilotLogic Architect and Core Programmer
The following user(s) said Thank You: Javor

Please Log in or Create an account to join the conversation.

Bug in ZeOS DBO 8 years 1 month ago #9394

  • Javor
  • Javor's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • M.Sc in Computer Science, (ex-)freelancer
  • Posts: 72
  • Thank you received: 8
Sorry to revive old topic but seems like I'm out of luck... again
This time I have to use ZeosDBO with MSSQL and of course with threads. @Michael - tried Critical Section and the result is the same as it was before with MySQL, i.e. Exception is raised when disconnecting a TZConnection from a thread. When there's only one thread it works, but 2+ produce the Exception. Bad thing is there will be number of threads used.
Scenario:
FPC:
CT Lab 5.7/2016-01-14
FPC version: 3.1.1
SVN version: 51232


pl_ZeosDBO
19-12-2015 ver 7.2.0.29 SVN trunk Rev 3685

Any solution (if possible w/o updating CT, because I'm not sure what feature is changed and what will stop working...)?

Please Log in or Create an account to join the conversation.

  • Page:
  • 1