"stacking" mysql queries? multiple queries in millisecs each browser-refresh

[alexiovay]

Hello,

I'm trying to figure out this problem since days. I have to say that I'm pretty new to socket.io and nodejs and at first I didn't even noticed where the problem comes from until I 'console.log'ed nearly every function now.
I found out that refreshing the browser will "stack" up the console.log data and I assume also the mysql-queries I'm sending.
It's about an penny-auction website I'm building and I want to update every auction if there's a new bid or in another specific case. Every time this happens the auction-duration-time also increases.
The client-side-javascript file keeps the connection to socket.io and updates the countdown timer when the price changes and stores the "new duration" that is left in seconds in an array that counts down to not constantly sending queries to the mysql database every second.
The main problem is: If the timer drops to 0, the client-side-file visually changes an auction to a "SOLD"-state, even if that's not the case server-side. If you refresh your browser again, it's still counting down then.
If I restart my node server it works fine again. But I can't even image what will happen if multiple users refresh the website and will see the same not-intended behaviour. I also don't know how these "mulitple stacking" queries cause the problem to show the countdown counting to zero and doesn't reset the timer like it should be.

This is my server-side code:
http://pastebin.com/qUAtfiKk
This is my client-side code:
http://pastebin.com/mwYX9P16

I've documented the bug in a video to show it to you more clearly:
https://vimeo.com/126816408

However there must be something wrong in my server.js-code, I guess. I can't figure out where exactly.
I really hope anyone can help me somehow.

PS: The code structure comes from a nodejs-socket-io-mysql-tutorial: http://www.gianlucaguarini.com/blog/push-notification-server-streaming-on-a-mysql-database/

[dougwilson]

You need to use the pool, rather than a single connection. https://github.com/felixge/node-mysql#pooling-connections

[alexiovay]

Thank you for your response! Could you give me an advice in my special case to fit that in correctly in my code in combination with socket.io here http://pastebin.com/cypzUMuQ ?
My guess is to use a connection limit of 1, because this should be shared with all users connecting to the website asynchrounously. I also assume in my special case "connection.release();" shoudln't be used at all, because I want the connection to be alive forever, but I'm not sure if that would be correct?

My aim is that the server runs all the time, even if there isn't any user connected to the WebSocket and auctions should be updated all the time with my specific conditions. And every single user should see the same thing as all other users at the same time. That's why I ask you if my guesses are correct?

Thank you very much in advance!

[dougwilson]

Could you give me an advice in my special case to fit that in correctly in my code in combination with socket.io here http://pastebin.com/cypzUMuQ ?

I would have to look at and reason over the code first, but I'm not sure if I'll have enough time to really do that yet :(

My guess is to use a connection limit of 1, because this should be shared with all users connecting to the website asynchrounously. I also assume in my special case "connection.release();" shoudln't be used at all, because I want the connection to be alive forever, but I'm not sure if that would be correct?

So you're just describing doing what you're doing now by using the pool to just mimic a single connection. The whole point is that you cannot just use a single connection if you don't want your queries to get backed up; MySQL protocol is serial and everything happens one-by-one, unfortunately. This is the purpose to have the pool: to distribute your queries across multiple connections.

[dougwilson]

I think you may have gotten this resolved from the other issues. Please let me know if that's not the case and I can re-open this issue :)