Poll MySQL DB in Socket IO & Node JS

Question

I am attempting to poll a MySQL DB by iterating every 2 seconds and retrieving all records created less than 2 seconds ago.

If I use setInterval it seems to occasionally miss a record. If I use setTimeout it only fetches the first iteration.

How can I fetch the data more reliably to include all data?

io.on('connection', function(socket) {
    authorize (socket, function(shop_id) {
        setInterval(function() {
            var sql = "SELECT * FROM `stats` WHERE `shop_id` = " + connection.escape(shop_id) +
            " AND `created` > DATE_SUB(NOW(), INTERVAL 2 SECOND)";
            var query = connection.query(sql, function(err, rows, fields) {
                if (err == null) {
                    console.log (rows);
                    socket.emit('newData', rows);
                }
            });
        }, 2000);
     });
});

apscience · Accepted Answer · 2015-11-18 00:19:00Z

3

MySQL is not designed to be pulled every 2 seconds. This is not a godo system architecture and you will experience more issues later on, definitely in regards to scalability.

setInterval is not precise and does not guarantee it will sleep for absolute 2000 ms, it may sleep for 2001, 2200, or even 50 hours.

To resolve this, store the most recent created that your NodeJS script fetched in a variable, and fetch all rows that are created AFTER your last received created. This will get every single record and won't miss records.

io.on('connection', function(socket) {


 authorize (socket, function(shop_id) {
        setInterval(function() {
            var lastCreated = toMySQLDate(new Date());
            var sql = "SELECT * FROM `stats` WHERE `shop_id` = " + connection.escape(shop_id) +
            " AND `created` > " + lastCreated + " ORDER BY `created` DESC";
            var query = connection.query(sql, function(err, rows, fields) {
                if (err == null) {
                    if (rows.length) {
                        lastCreated = toMySQLDate(rows[0].created);
                    }
                    console.log (rows);
                    socket.emit('newData', rows);
                }
            });
        }, 2000);
     });
});

function toMySQLDate(date) {
    return date.toISOString().slice(0, 19).replace('T', ' ');
}

edited Nov 18, 2015 at 0:19

answered Nov 17, 2015 at 23:23

apscience

7,29311 gold badges58 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Asa Carter Over a year ago

toMySQLDate() also needs to be used where lastCreated is updated. It won't let me edit the code example. Also typo in SQL = should be +

Asa Carter Over a year ago

The stats data is not necessarily related to other tables in the database. What would be a better choice of data store for frequent polling with regards to scaling?

Collectives™ on Stack Overflow

Poll MySQL DB in Socket IO & Node JS

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related