|
| Timeout on failed MySql queries if you lose connection |
| Iniciado por guest, 15,abr. 2016 21:17 - 11 respuestas |
| |
| | | |
|
| |
| Publicado el 15,abril 2016 - 21: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. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 16,abril 2016 - 07:54 |
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. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 16,abril 2016 - 08:06 |
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. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 16,abril 2016 - 08:58 |
Hi Stefano,
Can you explain how you managed your timeout?
And do you manage the connection error? Reconnecting instead of crashing?
Cheers,
Adri |
| |
| |
| | | |
|
| | |
| |
| Publicado el 16,abril 2016 - 10:24 |
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. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 16,abril 2016 - 16:50 |
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. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 16,abril 2016 - 17:07 |
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. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 17,abril 2016 - 23:14 |
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. |
| |
| |
| | | |
|
| | |
| |
Miembro registrado
14 mensajes |
|
| Publicado el 24,julio 2018 - 12:10 |
Did you ever find solution for that probem?
Best regards! |
| |
| |
| | | |
|
| | |
| |
| Publicado el 24,julio 2018 - 13:09 |
| |
| |
| | | |
|
| | |
| |
Miembro registrado
14 mensajes |
|
| Publicado el 24,julio 2018 - 16:38 |
Yes off course, but thats nothing to do with the issue.
In MySQL Native Acces all timeout parameters is ignored, so you must set it, if you want, via HExecuteSQLQuery();
Problem is that HExecuteSQLQuery stop main thread of the app for 50 seconds in which the inependent thread is running because:
mysql> select @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
50 |
+----------------------------+
1 row in set (0.00 sec)
and the error is:
Error returned by <127.0.0.1> database:
Error Number = <1205>.
Error Message:
Lock wait timeout exceeded; try restarting transaction
The first connection and transaction is opened with independed thread, but not even for the same table.
After the error, the thread continues normaly until finished.
Will send you an example later!
Thanks for help.
Best regards! |
| |
| |
| | | |
|
| | |
| |
Miembro registrado
14 mensajes |
|
| Publicado el 24,julio 2018 - 22:34 |
This code does not work:
c1, c2 is connection
ds1, ds2 is Data Source
HOpenConnection(C1)
SQLTransaction(sqlStart, C1)
HExecuteSQLQuery(DS1, C1, hQueryWithoutCorrection, "update table set col1=col1")
HOpenConnection(C2)
SQLTransaction(sqlStart, C2)
IF NOT HExecuteSQLQuery(DS2, C2, hQueryWithoutCorrection, "update table set col1=col1") THEN
SQLTransaction(sqlRollBack, C2)
END
SQLTransaction(sqlRollBack, C2)
HCloseConnection(C2)
SQLTransaction(sqlRollBack, C1)
HCloseConnection(C1)
Seems that even two connection is used, main thread see only one connection to database, and wait until innodb_lock_wait_timeout does not expired.
Mysql server log open two connection and create two transaction rows in information_schema.innodb_trx as expected.
I was tried to change all session.tx_isolation levels but with no success.
Any clue?
Best regards!Mensaje modificado, 24,julio 2018 - 22:36 |
| |
| |
| | | |
|
| | | | |
| | |
|