mysql: Too many connections after several days



  • After several days of good working, Shinobi finaly ran into an issue with sql connection: The database connection could not be renewed because the mysql server answered: Too many connections. After restarting mysql, Shinobi immediately took all connections again which again caused "Too many connection" error ("show process list" displayed again all connections exhausted). The only workaround was to restart Shinobi (camera.js) AND mysql.


  • administrators

    based on this link https://stackoverflow.com/questions/32650604/close-connection-mysql-node-js it seems that if i do createPool rather than createConnection this problem should be fixed.



  • I found, that the hanging connections are caused by the way, how Shinobi does reconnection in case of database errors. If an error occurs, the error handler immediately re-opens a connection. For example today there was a connection lost and I see in the log that Shinobi retries six times in short time to reconnect. Now I have seven open connections in mysql (show full processlist), all in "Sleep" state. I think the error handler should be reworked to solve this issue. It calls s.disc() recursively (line 130) so that the amount of open connections increases if the new connection is unable to establish.


  • administrators

    Oh I see.. but if it never connected how is it putting a slot to sleep? if it doesn't establish how would I disconnect a "sleeping" connection? 😕



  • Sleeping connections are idle connections, so normal case if there is currently nothing to do. I think to avoid "too many connections" you could call sql.destroy (maybe in a try-catch-block) at start of s.disc so the old connection is definitely closed. (btw. the name s.disc is really odd for a function initializing sql connections).


 

Looks like your connection to Shinobi Forum was lost, please wait while we try to reconnect.