Link to home
Create AccountLog in
Avatar of Bruno Buesser
Bruno BuesserFlag 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
Avatar of David Favor
David Favor
Flag of United States of America image

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.
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
Avatar of Bruno Buesser
Bruno Buesser
Flag of Switzerland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
@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.
Avatar of 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.
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