Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
8
Erster Beitrag:
vor 1 Jahr, 4 Monaten
Letzter Beitrag:
vor 1 Jahr, 4 Monaten
Beteiligte Autoren:
StefanoG, Adri, CCC2

Timeout on failed MySql queries if you lose connection

Startbeitrag von StefanoG am 15.04.2016 19:17

Finally I was able to manage my own timeout in failing HOpenConnection on MySql (Native Access) with a trick and threads.

And what if the connection to Mysql service fails during the normal working session, after a successful connection opening? Is there a way to prevent the 20 second standard waiting and having the program in idle for all this time after a query?

In practice, for giving a real sample, I estabilish the connection with a common hopenconnection running fine. Then I run a simple query just after removing the internet cable (stopping mysql service has the same effect). Now the program hangs (window not responding) for 20 seconds before getting the error message "lost connection, error 79". A HExecuteQuery in this condition is locking.
Obviously checking the connection before each query is not an option. I want to trap and manage quickly the exception.

Thank you.

Antworten:

Hi,

shortening the time out time may not be good idea .
specially on
- Big Database
the server may take long time to process your query

- crappy network
intermittent packet drop


if you create new connection before the server close the existing connection (with query still running). this will cause the server to create another new connection. it's not big deal if you only have few clients and not happen often . but if you have alot clients and connection time out happen every 5 minutes then you will see your server suddenly eat alot memory and cpu.

von CCC2 - am 16.04.2016 05:54
I agree but it's a pos software.
Having a cashier locked on sunday with a 20 people queue and the app freezed before switching to offline mode is not nice...
This is why i pay great attention to Timings.

von StefanoG - am 16.04.2016 06:06
Hi Stefano,

Can you explain how you managed your timeout?
And do you manage the connection error? Reconnecting instead of crashing?

Cheers,
Adri

von Adri - am 16.04.2016 06:58
When opening the connection I run the hopenconnection in a secondary thread and check its status in the main thread.
Example: use a global boolean variable shared by main and thread that becomes true when hopenconnection end successfully in the thread. So the main has only to do a loop for checking the evolution in time of that variable. After X checks / seconds you kill the program if you don't have estabilished the connection yet.
X is a number you can define in INI settings or fixed by program.

When the connection is done and you are working it is more difficult. You can't kill the app and lose all current data. You have to trap the first "DB error" and react with a procedure that switch on offline db and opens a new connection.

In my case the online DB is MySql and the SOS offline db is local HF on my own disk. Whren you repair the connection you have to send local data to MySql before restart working online.

von StefanoG - am 16.04.2016 08:24
SOLVED!!

HChangeConnection("*", ConnectionName)
before each hanging query. I don't know why and how but this make sudden fail the next query that otherwise will wait 20 seconds.

But adding this statement before each query could be heavy. Does it slow the app? And all the FOR EACH cicles on datafiles or resultsets?
I will use that at the beginning of macro-operations or transactions, not for each query.

von StefanoG - am 16.04.2016 14:50
Rollback... Not solved.

Inspecting MySql log i saw that each
HChangeConnection("*", ConnectionName)
will close the current connection (= QUIT) and restart a new one, too bad.

von StefanoG - am 16.04.2016 15:07
I give up, it's no use.
H functions on mysql NA hang the application until they return a result. If the mysql service goes down or you cannot reach the db server after the connection (= while doing common queries) there's no way to control the timeout before getting the FAIL result.
As you start the H function inside the main thread, it steals the maximum priority in the application.
If you start a thread just before the H function it does not run before the end of H fucntion..
If you start a timer just before the H function it does not run the same.
Even parallel tasks fail.

I could try to run all the queries in secondary thread but I think it is insane.

Example:
Info(1)
IF NOT HReadSeekFirst(products, code, mycode) THEN Error("FAIL")
Info(2)

If you cut the connection when showing info 1 you reach info 2 always after 20-30 seconds. No way to tell the app to give up after 5 or 10 seconds instead.

von StefanoG - am 17.04.2016 21:14
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.