Lost connection to MySQL server during query

[Rick Lipkin]

Vilian

Forgive my comment if I may offend you ... looking at \source\function\adofuncs.prg ( built into all FW builds )

Code: Select all  Expand view

static saProviders := { ;
{ "DBASE",    "OLEDB", { "Microsoft.ACE.OLEDB.12.0", "Microsoft.Jet.OLEDB.4.0" }, "Extended Properties=dBASE III" }, ;
{ "MSACCESS", "OLEDB", { "Microsoft.ACE.OLEDB.12.0", "Microsoft.Jet.OLEDB.4.0" }, "" }, ;
{ "MSSQL",    "OLEDB", { "MSOLEDBSQL", "SQLNCLI11", "SQLNCLI10", "SQLNCLI", "SQLOLEDB" }, "" }, ;
{ "MYSQL",    "ODBC",  { "Provider=MSDASQL;Driver={MySQL ODBC 8.0 ANSI Driver}", ;
                         "Provider=MSDASQL;Driver={MySQL ODBC 5.3 ANSI Driver}", ;
                         "Provider=MSDASQL;Driver={MySQL ODBC 5.2w Driver}", ;
                         "Provider=MSDASQL;Driver={MySQL ODBC 5.2 ANSI Driver}", ;
                         "Provider=MSDASQL;Driver={MySQL ODBC 5.1 Driver}", ;
                         "Provider=MSDASQL;Driver={MySQL ODBC 3.51 Driver}" }, "Option=3" }, ;
{ "ORACLE",   "OLEDB", { "MSDAORA", "OraOLEDB.Oracle" }, "" }, ;
{ "SQLITE",   "ODBC",  { "Provider=MSDASQL;Driver=SQLite3 ODBC Driver" }, "" }, ;
{ "FOXPRO",   "OLEDB",  { "vfpoledb" }, "" }, ;
{ "POSTGRE",  "OLEDB", { "PostgreSQL OLE DB Provider" }, "" }, ;
{ "INFORMIX" }, ;
{ "ANYWHERE" } ;
}

 

It appears MySql needs an ODBC driver(s) to connect to your database .. When I was in State Gov I wrote Sql programs for over 5 thousand users and it seemed almost impractical to make sure the correct ODBC driver and configuration was loaded on each desktop .. In my opinion ODBC is a lot of configuration overhead .. where OLEDB or Microsoft.Jet.OLEDB.4.0 is built into EVERY Microsoft Windows operating system .. there is no verification needed if the workstation does not have ODBC loaded or configured properly and what if you have a "cranky" Windows desktop that intermittently decides to fail ( perhaps in your situation ) connecting or staying connected to your server ..

Using MS Access with .MDB is a great local database for smaller user apps .. and Ms Sql Server for Enterprise applications is FREE for less than 100 users or so and all the ADO ( ole ) drivers are already incorporated into the Windows Operating system .. and FREE is good and having the OLE drivers already present on every Windows desktop is a blessing .. I would seriously consider using Ms Access or MS Sql server for your database .. it will save you a lot of ( connection ) headaches .. have a look at and compile \samples\AdoRick.prg ..

Just a quick suggestion .. Rick Lipkin

ps .. if you are connecting with OLEDB\Sqloledb please dis-regard my comments ..

[vilian]

Hi Rick,
I'm using FWH MySql Native Support

[Rick Lipkin]

Vilian

Sorry ( again ) if I may have offended you ... Is this loss of connection isolated to 1 or 2 machines of is the connectivity problem randomly system wide ? .. I do not know anything about MySql ?

Rick

[vilian]

Rick,
No problem.
That's happing when a user let your computer with a window/dialog open for a long time, without do anything, stopped. So when the user returns and try use it, the message "lost Connection" is shown and an error message is generated.

Mr Rao says "But please keep in mind, you do not need to try to reconnect.
All that kind of work, the library has already done."

I just would like to know, the best approach to prevent this error message when a lost connection occurs.

[Rick Lipkin]

Vilian

Check the screen time out setting and or if there is a screensaver trying to kick in

W10 ... right click on the desktop
left click on personalize
Click on Lock Screen .. scroll down to the bottom and check "screen timeout settings" then click on screen saver settings

Go to the Control panel
Power Options
1- Choose what the power buttons do on Battery plugged in
when I press the power button shut down shut down
when I press the sleep button do nothing do nothing
when I close the lid shut down shut down
4 - Choose when to to turn off the display
Turn off the display Never Never
Put the Computer to sleep Never Never


These are my settings .... check your troubled computer and check any difference on the above settings

Rick Lipkin

[cjcardoza]

I had the same problem when I work in the cloud and I solve it with DEFINE TIMER to check each time interval if the connection exists
At the beginning of the application I activate it
** oMAE00VAR:tiempo = number of minutes, normally I put it 2 minutes and define it as an external variable
** MEMVAR->oWPrincipal = It is my main dialog when opening the application
MEMVAR->NTIMER :=oMAE00VAR:tiempo*60000
//----------------------------------------------------------------------------//
STATIC FUNCTION iniciapri()
IF MEMVAR->NTIMER>0
DEFINE TIMER MEMVAR->oTimerpri INTERVAL MEMVAR->NTIMER OF MEMVAR->oWPrincipal ACTION ISCONEXION()
MEMVAR->oTimerpri:Activate()
ENDIF
RETURN NIL
//----------------------------------------------------------------------------//
FUNCTION ISCONEXION(cTitulo,lMsgOff)
LOCAL oQry, lRta,oWait
DEFAULT cTitulo:="Conectando a Softgestion.pe"
DEFAULT lMsgOff:=.T.
IF MEMVAR->oConex <> nil.AND.MEMVAR->NTIMER>0
WaitOn(cTitulo,@oWait)
SysRefresh()
oQry:=MEMVAR->oConex:Query("SELECT 1")
WaitOff(@oWait)
IF oQry:RecCount()==0
MsgWait("Reconectando con el servidor...",cTitulo,3)
lRta:=MEMVAR->oConex:Ping()
IF !lRta
MsgWait("Se desconecto el servidor!","Error",3)
QUIT
ENDIF
ENDIF
ENDIF
RETURN NIL

Another important observation verifies that the computer constantly disconnects, is with a trusted antivirus and fully scans the disk.
I had problems with a couple of clients that kept disconnecting and checking it with the antivirus I found several Trojans that were lodged when
they formatted the computer they use the driverspack that they download from the internet that arrive infected
I hope it is useful

[Jimmy]

hi,

That's happing when a user let your computer with a window/dialog open for a long time, without do anything, stopped. So when the user returns and try use it, the message "lost Connection" is shown and an error message is generated.

modern PC all use "Energy Saving" which also let Network Card go "sleep"

i have try to get WM_POWERBROADCAST under Fivewin but that does not halp
so i use my old Ot4XB CODE to call "SetThreadExecutionState"
https://learn.microsoft.com/en-us/windows/win32/api/winbase/nf-winbase-setthreadexecutionstate

Enables an application to inform the system that it is in use, thereby preventing the system from entering sleep or turning off the display while the application is running.

Code: Select all  Expand view

FUNCTION SetStandbyOFF( lOnOff )
LOCAL nRet := 0

   DEFAULT lOnOff := .F.

   IF lOnOff = .T.
      // nRet := @KERNEL32:SetThreadExecutionState( nOr(ES_CONTINUOUS, ES_SYSTEM_REQUIRED,ES_DISPLAY_REQUIRED ,ES_AWAYMODE_REQUIRED) )
      nRet := DllCall( "KERNEL32.Dll",, "SetThreadExecutionState", nOr( ES_CONTINUOUS, ES_SYSTEM_REQUIRED, ES_DISPLAY_REQUIRED, ES_AWAYMODE_REQUIRED ) )
   ELSE
      // nRet := @KERNEL32:SetThreadExecutionState( ES_CONTINUOUS )
      nRet := DllCall( "KERNEL32.Dll",, "SetThreadExecutionState", ES_CONTINUOUS )
   ENDIF
RETURN nRet

[vilian]

I just would like to know, the best approach to prevent this error message when a lost connection occurs.

[nageswaragunupudi]

I just would like to know, the best approach to prevent this error message when a lost connection occurs.

Pls wait

[karinha]

Buén dia Vilian, usted ya intento esto?

Code: Select all  Expand view

mysql -uroot -proot --connect-timeout 120
 

Code: Select all  Expand view

SHOW VARIABLES LIKE "%timeout";
 

Code: Select all  Expand view

SET GLOBAL connect_timeout = 600;
 

Code: Select all  Expand view

[mysqld]
connect_timeout = 10
net_read_timeout = 30
wait_timeout = 28800
interactive_timeout = 28800
 

https://sebhastian.com/lost-connection-mysql-server-during-query/

Regards, saludos.

[vilian]

Thank you Karinha,
But, Mr Rao said i haven't to do anything in my program for this kind of error. So I just wanted to know his advice for adjust this in my program.

[vilian]

+1

[nageswaragunupudi]

Sorry for the delay
Please wait
Meanwhile please let me know your FWH version.

[vilian]

My FWH version is 05.2021, but i'm wanting to get the next upgrade.

[nageswaragunupudi]

Our FWH maria library initially connects to the server with MYSQL_OPT_RECONNECT option.
As many other users advised, time out or sleeping and any such reasons are irrelevant.
Even if timed out, any communication with the server ( eg even a simple oCn:Ping() ) will automatically wake up the connection and continues the work.

The only reason of this "lost server" error is physical connectivity issue. It can be transient at times like lose connections or unstable internet connection or rarely a problem with the provider of the cloud server. If the error is displayed, the user needs to physically make sure or restore the connectivity and "retry" the same operation or do any other operations. Gets automatically reconnects and continues with the work as if nothing has happened.

We are planning to retry the operation with a small delay and give the message only thereafter.

Please feel free to provide your comments and advise to us.