Page 1 of 1
MySQL Ping
Posted: Mon Jul 02, 2012 7:49 pm
by Warmonger
I looked through the database functions, and it seems there isn't anything to ping MySQL to keep the connection active. I wrote a loginserver in PB and its completely stable. The only downside is overnight, it looses its connection with MySQL (the connection with MySQL has gone away). Is there a way in PureBasic to ping MySQL so the connection isn't lost?
SOLVED!
Code: Select all
; ------------------------------------------------------------
; MySQL_Ping - Pings MySQL To Keep An Active Connection
; ------------------------------------------------------------
Procedure MySQL_Ping(Parameter)
Timer = ElapsedMilliseconds()
Repeat
Timed = ElapsedMilliseconds()-Timer
If Timed > 10000
Query = DatabaseQuery(0, "SELECT 1")
If Query
While NextDatabaseRow(0)
PingResult.s = GetDatabaseString(0, 0)
Wend
If PingResult = "1"
Timer = ElapsedMilliseconds()
Else
ConsoleColor(12, 0)
Print("[ERROR] ")
ConsoleColor(7, 0)
PrintN("Lost Connection With MySQL")
EndIf
EndIf
Else
Delay(1)
EndIf
ForEver
EndProcedure
Re: MySQL Ping
Posted: Mon Jul 02, 2012 11:50 pm
by IdeasVacuum
Never done it but can you simply send a query per time interval?
Re: MySQL Ping
Posted: Tue Jul 03, 2012 4:21 am
by Warmonger
IdeasVacuum wrote:Never done it but can you simply send a query per time interval?
I thought of that possibility, and is using "Select 1" query to see if MySQL is still responding. But I rather have a native PureBasic function that acts like MySQL_Ping.
Re: MySQL Ping
Posted: Tue Jul 03, 2012 9:18 am
by Num3
COF
Re: MySQL Ping
Posted: Tue Jul 03, 2012 4:26 pm
by Warmonger
Num3 wrote:COF
mysql_ping() doesn't exist in the PureBasic help file. I don't think its an internal function of the PureBasic library. I wrote a MySQL library that I could simply call it from, but I don't want to use a wrapper at all. I am trying to do it through PureBasic natively. Using "SELECT 1" query seems to work for now, as all I have to do is check for return value 1. Tho executing a query every 10 seconds doesn't seem too efficient even tho the query takes less then 0.00ms to execute.
Re: MySQL Ping
Posted: Tue Jul 03, 2012 4:46 pm
by kinglestat
ideally you query the time like select now()
Re: MySQL Ping
Posted: Tue Jul 03, 2012 5:46 pm
by Warmonger
kinglestat wrote:ideally you query the time like select now()
That would only give me a more complex variable that is harder to use. I rather just do "SELECT 1" and see if MySQL will return that value.
Re: MySQL Ping
Posted: Wed Jul 04, 2012 10:53 pm
by Zach
Warmonger wrote:Num3 wrote:COF
mysql_ping() doesn't exist in the PureBasic help file. I don't think its an internal function of the PureBasic library. I wrote a MySQL library that I could simply call it from, but I don't want to use a wrapper at all. I am trying to do it through PureBasic natively. Using "SELECT 1" query seems to work for now, as all I have to do is check for return value 1. Tho executing a query every 10 seconds doesn't seem too efficient even tho the query takes less then 0.00ms to execute.
...as opposed to pinging the server every 10 seconds??
I don't think it should really matter whether you use a Native PB command, or a workaround, as long as it is doing the job and it is reliable and predictable behavior. Perhaps you should ping only once per minute, or try other higher values to see what the actual timeout of the server is. Then you only need to ping once every so often within that time frame.
Is there not a query itself that you could send to the server, to return the value for how long it waits before timing out a connection? You said the problem only exists overnight, so I am assuming it is not a "every 10 seconds" issue (if it is that seems stupid, but I'm not a Database person either, so...).
Sending a keep-alive query every 60 seconds or however long the window is, should work fine I would think. All a ping does is send a packet of data, and all you are doing now is sending a packet of data. The difference is only in the external method.
Re: MySQL Ping
Posted: Thu Jul 05, 2012 1:51 am
by Warmonger
Zach wrote:Warmonger wrote:Num3 wrote:COF
mysql_ping() doesn't exist in the PureBasic help file. I don't think its an internal function of the PureBasic library. I wrote a MySQL library that I could simply call it from, but I don't want to use a wrapper at all. I am trying to do it through PureBasic natively. Using "SELECT 1" query seems to work for now, as all I have to do is check for return value 1. Tho executing a query every 10 seconds doesn't seem too efficient even tho the query takes less then 0.00ms to execute.
...as opposed to pinging the server every 10 seconds??
I don't think it should really matter whether you use a Native PB command, or a workaround, as long as it is doing the job and it is reliable and predictable behavior. Perhaps you should ping only once per minute, or try other higher values to see what the actual timeout of the server is. Then you only need to ping once every so often within that time frame.
Is there not a query itself that you could send to the server, to return the value for how long it waits before timing out a connection? You said the problem only exists overnight, so I am assuming it is not a "every 10 seconds" issue (if it is that seems stupid, but I'm not a Database person either, so...).
Sending a keep-alive query every 60 seconds or however long the window is, should work fine I would think. All a ping does is send a packet of data, and all you are doing now is sending a packet of data. The difference is only in the external method.
The function I wrote above works perfect. When executed you can return the value of 1 from the first row. You can use this to check if MySQL executed the query or not. If it did then it will return 1, if not then it will return an error. I have it on a tight loop because the original server file im emulating pings MySQL every 10 seconds as well. The query is light and executes near instantly, so. I guess my problem is solved.
Edit: MySQL's default connection timeout is 15 seconds. So you have to ping it one way or another every 10 seconds at least to keep it alive.
Re: MySQL Ping
Posted: Thu Jul 05, 2012 6:31 am
by culita
TOVARISH you must use this bomb:
Code: Select all
InitNetwork()
Procedure TestInternetConnection()
;copyright: netmaestro the ugly frog
connection = OpenNetworkConnection("www.freewebmasterhelp.com", 3306)
If connection
CloseNetworkConnection(connection)
ProcedureReturn 1
Else
ProcedureReturn 0
EndIf
EndProcedure
Debug TestInternetConnection()
replace
http://www.freewebmasterhelp.com with your domain,
and make sure you have permiss to access it out of localhost,
in some cases you will need special permisions.
this example if you run it must work because
http://www.freewebmasterhelp.com have mysql
GOOD LUCK!
Re: MySQL Ping
Posted: Fri Jul 06, 2012 6:08 am
by culita
maybe this will help you
Code: Select all
InitNetwork()
Procedure TestInternetConnection(domain$,port)
;copyright: netmaestro the ugly frog
connection = OpenNetworkConnection(domain$,port)
If connection
CloseNetworkConnection(connection)
ProcedureReturn 1
Else
ProcedureReturn 0
EndIf
EndProcedure
;###############################################################
; !!!!!!!!!!!!!!!!!!! SEE this list !!!!!!!!!!!!!!!!!!!!!!!!!!!!
;http://en.wikipedia.org/wiki/List_of_TCP_and_UDP_port_numbers
;###############################################################
If TestInternetConnection("www.freewebmasterhelp.com", 3306)
Debug "mysql service is up"
Else
Debug "mysql service is down / the mysql server doesn't accept external connections for security reasons"
EndIf
If TestInternetConnection("www.freewebmasterhelp.com", 80)
Debug "http service is up"
Else
Debug "http service is down"
EndIf
If TestInternetConnection("www.freewebmasterhelp.com", 21)
Debug "FTP—control (command) service is up"
Else
Debug "FTP—control (command) service is down / the ftp server doesn't accept external connections for security reasons"
EndIf
If TestInternetConnection("www.freewebmasterhelp.com", 25)
Debug "smtp service is up"
Else
Debug "smtp service is down"
EndIf