Avatar of Bruno Buesser
Bruno Buesser
Flag for Switzerland asked on

Mutual blocking MySQL connections

I use FireDAC to Access a remote MySQL database and a local MySQL at the same time. For that I set up two TFdConnections.
The problem is that the two connections seem to affect each other.
When I switch off the remote MySQL Windows service I get long delays on the local connection.
When I pull the network cable from the remote machine the local connection is even completely blocked.


What causes this problems, the FireDac library or libmysql.dll and how can I solve it.
Thanks for your help, Bruno
DelphiDatabasesWindows OSNetworkingMySQL Server

Avatar of undefined
Last Comment
Geert G

8/22/2022 - Mon
David Favor

1) When I switch off the remote MySQL Windows service I get long delays on the local connection.

This is correct behavior.

When your remote MySQL database becomes unreachable, then client code will attempt to connect till a timeout occurs, then a connection failure will occur.

The "long delays" you mention relate to whatever timeout you have setup for your connections... in my.cnf or your equivalent.

2) When I pull the network cable from the remote machine the local connection is even completely blocked.

Same answer as #1.

Maybe clarify what you expect to have in each case, instead of a timeout followed by a failure.
Geert G

i'd contradict David completely
i doubt this is the behaviour that should happen

local is local, remote is remote
a connection to a db is 1 route
connecting to a local db does not go to the remote db, or should not

i'm not considering db linked connections

are you sure you don't always keep both connections open ?
maybe some function determines wrongly what db to use ?

maybe far fetched  ... using wireshark to see what connects to what
ASKER CERTIFIED SOLUTION
Bruno Buesser

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Geert G

not affect each other ?
not true

code in a thread can use any connection
a connection is oblivious to what thread/code is using it

a connection typically runs only 1 query at a time
if you'd use 2 querries in there own thread each, you'd find they both work, but the second will wait to start until the first is finished
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
David Favor

@Bruno, likely what you've done is simply mask the problem rather than fixing it.

Be sure to keep checking + if problem reoccurs, open a new question.
Bruno Buesser

ASKER
Thank you David and Geert for your help!
You are right my "solution" is not a true solution but it reduced the problem to an acceptable Level.
I tried out many things but couldn't find any true solution.
The two connections and their queries are really runing in two Independent threads. All works well when the network connections are ok. But when one network connection is  broken, due to a cable problem ore something else, trying to reconnect (when it cannot connect) affects the other connection whitch otherwise does not have any problems.  

It seems that FireDac cannot handle to connections independently in case of reconnections.
Fortunately this case of network problems only happens very seldom and needs an intervention of a service engineer anyway. So it's not an operating condition which lasts very long ( max. a few days). So I can live with my work around but will try out new FireDac versions in the future.
Geert G

i gave up on firedac after filing a sr about not being unicode compliant
some russian characters didn't pass correctly to database and back

i'd look at the devart components, not free, but way better
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.