TOPIC:

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
Sadly, I don't have time to trace and dig deeper.

Sir
my suggestion is to update your CT installation
to LAB CodeTyphon ver 5.5 and test again

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.

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

Sir
we want help for this

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 atin 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.

Sir
we will test and report

@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

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

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?

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.

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

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!

Thanks Sir
we will update CT ASAP

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?

No news on UTF8toXXX and the reverse ones, Sir
We try some "things" before Final CT 5.70
we will see...

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...)?