Lost connection to MySQL server during query

[vilian]

Here, the majority are happening for "time out". The user just forget a module openned and after some time try to use it, so the message of "Lost Connection" is shown.

If the message hasn't been shown, I could identify the error, repeat the command and the program would stopped.

[nageswaragunupudi]

What is the error number ?

[vilian]

There is no error number. The complete message error is as bellow:

SELECT mps.cequipto,mps.demanda,mps.dedicada,mps.cmat,Count(*) AS qte FROM tmovgpes AS mps WHERE mps.data = '2023-01-11'AND mps.funcao IN('O','F') GROUP BY mps.cequipto;SHOW FULL COLUMNS FROM tmovgpes [ ERROR: Lost connection to MySQL server during query ]

[nageswaragunupudi]

1) Normally an error log should look like this

Code: Select all | Expand

FWLOG TO ::cLogFile cCallStack( "<-", 3 ), cSql, uRet, ::nError, ::cError, ::cSqlInfo

Error number is shown

2) Please check

Code: Select all | Expand

? oCn:wait_timeout

By default should be 8 hours

Also, please let us know your MySql server version.

Code: Select all | Expand

? oCn:cServerInfo
? oCn:nVersion
? oCn:OS

Now we are improving the internal reconnection logic and testing.
We will provide DATA bOnLostServer
If assigned with a codeblock, instead of displaying message, the codeblock will be evaluated. If the codeblock returns .T., the operation will be retried.

Will this be ok for you?

[vilian]

Good Morning,

? oCn:wait_timeout = 86400
? oCn:cServerInfo = 5.7.27-log
? oCn:nVersion = 5.72700000
? oCn:OS = Win64

Now we are improving the internal reconnection logic and testing.
We will provide DATA bOnLostServer
If assigned with a codeblock, instead of displaying message, the codeblock will be evaluated. If the codeblock returns .T., the operation will be retried.
Will this be ok for you?

It'll be perfect !!!

[nageswaragunupudi]

do any of these statement work for you?

Code: Select all | Expand

? oCn:max_statement_time
//or
? oCn:max_execution_time

[vilian]

oCn:max_statement_time

Is generating the error bellow:
Called from: FWMARIA.PRG => FWMARIACONNECTION:MAX_STATEMENT_TIME( 10176 )

? oCn:max_execution_time = 0

[vilian]

I've got a complete log of the moment when an error happened.

Code: Select all | Expand

06/09/2023 16:42:08: FWMARIACONNECTION:EXECUTE_SQL( 6665 )  cCallStack( "<-", 3 ) = "FWMARIACONNECTION:QUERY( 7352 )<-TTARTES:EDITTASK( 6404 )<-(b)TTARTES_DEFINEDIALOGS( 433 )<-TBUTTONBMP:CLICK( 179 )<-TBUTTON:HANDLEEVENT( 1781 )<-TBUTTONBMP:HANDLEEVENT( 261 )<-_FWH( 3560 )<-SENDMESSAGE( 0 )<-TPANEL:COMMAND( 1149 )<-TWINDOW:HANDLEEVENT( 0 )<-TPANEL:HANDLEEVENT( 1827 )<-_FWH( 3560 )<-SYSREFRESH( 0 )<-STOPUNTIL( 71 )<-STV226( 54 )<-SYSEXECFUNCAOBIN( 208 )<-CHAMAPRG( 67 )<-(b)FAZMENUMOD( 433 )<-TMENU:ACTIVATE( 1621 )<-TRBTN:LBUTTONUP( 886 )<-TCONTROL:HANDLEEVENT( 1817 )<-TRBTN:HANDLEEVENT( 1578 )<-_FWH( 3560 )<-WINRUN( 0 )<-TMDIFRAME:ACTIVATE( 1097 )<-START( 178 )"  cSql = "SELECT * FROM ttartes LIMIT 1;SHOW FULL COLUMNS FROM ttartes"   uRet =  ::nError = 2013 ::cError = "Lost connection to MySQL server during query"   ::cSqlInfo = "" 
 

[PAUL SIMM]

Is there any way I can change the error message "Lost Connection to MySql server during query" to only log the error to a text file end return false to the calling fintion. At the moment the error is a MsgInfo and the process which is automatic with a Timer blocks the aplication.

This code do'snt do this

oCon:Execute(cQwery)

if oCon:nError != 0
MSGWAIT("Error executando query","Line 10961")
oCon:Close()
RETURN .F.
endif

I used to work with Eagle I could define what to do when an MySql error occured with this funcion.
FUNCTION MyMsgError(cArg1,cArg2 )
MSGWAIT(cArg1,cArg2+" Web2Reco")
YMENSCEN(cArg1+ " "+cArg2) //log error
RETURN NIL
******************************************************************************

[JoséQuintas]

I have this error on some situations:

1) On machine with ODBC 3.51
Same query ok on ODBC 5.3 returns that message on ODBC 3.51

2) When update from MySQL 5.6 TO 5.7
I was using INNER JOIN between INT and VARCHAR ( 1 and 0000001 ), this was ok on 5.6.
From 5.7 this is a problem, that requires internal codepage conversion and slow down queries execution

3) A really long time execution query (or do not exists index to optimize query)

4) A query executed by a user with no privileges to do it

5) Connection fail

A first test is try to execute same query on HEIDISQL or anything else, with same user and server, to check result.

If query is the problem, execute again will not solve problem.

Another common problem using connections on Windows:
Before Windows XP service pack 5, half open connections limit was anything about 67 millions, and from service pack 5 limit was reduced to 8, only 8 units.
When close connection, windows move connection to halfopen for a limited time, before really to close it.
connection open/close/open/close/open/close, this can crash application and windows too.

if you are using port 3306 for MySQL, you can check this using
netstat -a | find /i "3306"

[vilian]

Yes,
You can use bOnLostServer

* FWMariaConnection:
Reconnection logic improved when the app loses connection to the server.
New data bOnLostServer: If this codeblock is specified, it is called when the
application loses connection with the server.

Is there any way I can change the error message "Lost Connection to MySql server during query" to only log the error to a text file end return false to the calling fintion. At the moment the error is a MsgInfo and the process which is automatic with a Timer blocks the aplication.

This code do'snt do this

oCon:Execute(cQwery)

if oCon:nError != 0
MSGWAIT("Error executando query","Line 10961")
oCon:Close()
RETURN .F.
endif

I used to work with Eagle I could define what to do when an MySql error occured with this funcion.
FUNCTION MyMsgError(cArg1,cArg2 )
MSGWAIT(cArg1,cArg2+" Web2Reco")
YMENSCEN(cArg1+ " "+cArg2) //log error
RETURN NIL
******************************************************************************