[Need help]Streamming rows problem

[dsonet]

I'm using a pool, get one connection to fetch rows, use another one to update rows back to mysql. When the dataset is very small, like 100 rows, it works. But when it's little bigger, it stuck, the updateRow seems never invoked.any one could help?

pool.getConnection(function (err, conn) {
    conn.query("SELECT * FROM table")
            .on('result', function (row) {
                conn.pause()
                updateRow(row, function(){conn.resume()}) //update it back
            })
})
function updateRow(row, resume) {
    pool.getConnection(function (err, conn) {
        conn.query('UPDATE table SET ....', function(err, result) {
            resume()
        })
    })   
}

[dougwilson]

If you pasted the full code, then you are missing the conn.release() call in your updateRow, which would cause the pool to get exhausted. Add conn.release() right before resume().

[dsonet]

@dougwilson Thanks for your reply. I released connection in updateRow. Finally I figure it out. The problem here is caused by the MyISAM engine. It use table block. Since I opreation the same table.

[dsonet]

But still strange is that I tried replace the updateRow simple as

function updateRow(row, resume) {
    setTimeout(resume, 100)
}

It's seems never reach the 'end' events. usually lasts about 6 minutes then just stuck.

[tienvx]

If you want to see the real error:

var connection = mysql.createConnection(config.db);
connection
    .query(sql)
    .on('result', callback);

In my case, the error is: Error: Connection lost: The server closed the connection. Maybe SELECT * FROM table is too big for mysql for streaming, I changed to SELECT id FROM table and the error gone.

[sidorares]

@tienvx how big is result in your case ( number of rows and columns?) can you post a schema so I can try to replicate?

[tienvx]

Hey @sidorares , sorry the schema is private so I can not post it.

The table have 110k rows, 17 columns (including 1 longtext column). Also in my case, the code is

pool.getConnection(function (err, conn) {
    conn.query("SELECT id, a, b, c, d, e, f FROM table")
            .on('result', function (row) {
                conn.pause()
                pushRowToRabbitmq(function () {
                      conn.resume();
                });
            })
})

Not the same with @dsonet .Hope it help.

[sidorares]

ok I'll just try to test it on a some 17x110k table and get back for more help if unable to reproduce

Do you get any errors if you do non-streaming version?

var connection = mysql.createConnection(config.db);
connection.query(sql, callback);

[tienvx]

There are no errors with non-streaming version.

[sidorares]

can't reproduce. What's in your callback? Is it heavy? It's supposet to be executed 100k times