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
LVL 1
Bruno BuesserAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David FavorLinux/LXD/WordPress/Hosting SavantCommented:
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 dbaCommented:
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
Bruno BuesserAuthor Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Geert GOracle dbaCommented:
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 FavorLinux/LXD/WordPress/Hosting SavantCommented:
@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 BuesserAuthor 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 dbaCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.