MySQL Ping

[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

[IdeasVacuum]

Never done it but can you simply send a query per time interval?

[Warmonger]

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.

[Num3]

COF

Code: Select all

mysql_ping(hDB)

[Warmonger]

COF

Code: Select all

mysql_ping(hDB)

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.

[kinglestat]

ideally you query the time like select now()

[Warmonger]

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.

[Zach]

COF

Code: Select all

mysql_ping(hDB)

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.

[Warmonger]

COF

Code: Select all

mysql_ping(hDB)

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.

[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!

[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