2

I am having a strange issue that's been bugging me for days: I am using a php websocket script for an intranet site to alert staff about certain things. it was a cheap and fairly easy to implement solution and it works fine. however, I am having to restart the server every morning.

as the php code runs on an endless loop and every task is run in sequence rather than simultaneously, I assumed that one persistent connection to the DB would suffice, so I just open a connection right in the beginning of the code with a $GLOBALS variable and reuse it over and over. it works fine until the next morning when all requests will come back as false. the connection is still alive. error catchers such as "->connect_error" and "->connect_stat()" won't return errors.

apparently MySQL runs a self-update check every night at midnight. I'm not sure this is what's causing problems but I am hoping I won't have to disable this feature.

the only way I managed to work around this issue was to send a query that can only return a positive response. if it doesn't than it shuts and re-opens the connection. it doesn't seem smart to me. there has to be a way to detect when the connection becomes unreliable.

I'm using the latest versions of PHP and MySQL.

any ideas?

A. Richards
  • 123
  • 1
  • 9

1 Answers1

0

There might not be a connexion error as it has succesfully connected first... Check the last error instead with $mysqli->error. You'll probably receive "Mysql Server has gone away". If you do you can close the connection and restart a new one.

Salketer
  • 14,263
  • 2
  • 30
  • 58
  • I tried that as I can see the line with that code is commented. I have not seen that error at all. the only error that shows in the log when the first user tries to use the service is "Error while sending QUERY packet. PID=[number] and the file name and line number. – A. Richards Sep 10 '15 at 09:57
  • This should not be related... but the way you handle the problematic seems about right anyways. – Salketer Sep 10 '15 at 10:02
  • I found the scheduled task in Windows to fire the update task manually but it still works after that which just makes the whole thing more confusing. if it isn't the self-update process doing this I don't know what is. I am opening the connection with $GLOBALS["Conn"] = new MySQLi("p:localhost", "xxxxxxxxxx", "xxxxxxxxxx") and $GLOBALS["Conn"]->options(MYSQLI_OPT_CONNECT_TIMEOUT, 86400) – A. Richards Sep 10 '15 at 10:09
  • The other commenters have it about right. You can't expect a tcp socket to hang around for ever, you have to be able to handle cases where the connection has died and reconnect if required – delatbabel Sep 10 '15 at 11:13
  • @delatbabel: I accept your argument. my intention was never to keep it open forever (hence my 24 hour timeout) but to figure out a proper way to detect this particular type of situation in which the connection appears to be open, the mechanisms for error detection are not reliable and yet data is not being passed to/from. what if instead of 16 I had 1600 users? sending a test query before every query would unnecessarily stress the server. – A. Richards Sep 10 '15 at 21:36
  • Do not send the test query every time, just when you get returned false, to check the legitimity of that false... – Salketer Sep 11 '15 at 05:57
  • Hi Amanda, sadly there probably is no such method that will work in all cases. If you look at the source code various pieces of software that maintain long running tcp connections you'll see a crazy number of different ways of doing this -- anything from closing the socket and quitting, to checking the socket status after each send, etc. Some network libraries have mechanisms to maintain connection state for you, but I unfortunately I don't believe there is such a thing for PHP. – delatbabel Sep 11 '15 at 05:59