Mutual blocking MySQL connections

Bruno Buesser
Bruno Buesser used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorFractional CTO
Distinguished Expert 2018

Commented:
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 GOracle dba
Top Expert 2009

Commented:
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
Each Connection runs in its own thread. So the two connections cannot affect each other in my code.
I found another post which reported exact the same problem and statet that Embarcadero told that this is an issue in the FireDac library. I first thought the problem is in the libmysql.dll, but it isn't.

To work around this problem I reduced the reconnection rate in my code. I tried to reconnect about each 5 seconds when the connection was closed which caused a permanent blocking of my second connection. Now, just after program launch, I try to connect each 60 seconds and after 10 minutes only each 15 minutes. This reduces the times the other connection is affected to an acceptable rate.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Geert GOracle dba
Top Expert 2009

Commented:
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
David FavorFractional CTO
Distinguished Expert 2018

Commented:
@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.

Author

Commented:
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 GOracle dba
Top Expert 2009

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial