Async query problem - Query error #2014

[Somebi]

As i understand from code #2014 it says that buffer is not flushed from previous request.

This is my connection init method from bootstrap:

this.connectMysql=function(){

if(!this._config.mysql_config) {
    return this;
}

var self=this;

/**
 * Create connection
 */
this._mysql_conn=this.meryl._mysql_conn=this.meryl.mysql.createConnectionSync();

this._mysql_conn.connectSync(
    this._config.mysql_config.host,
    this._config.mysql_config.user,
    this._config.mysql_config.password,
    this._config.mysql_config.database,
    null, // default picked, 3306 or 3307
    this._config.mysql_config.socket
);

/**
 * Check connection status
 */
if (!this._mysql_conn.connectedSync()) {
    sys.puts("Connection error " + this._mysql_conn.connectErrno + ": " + this._mysql_conn.connectError);
    process.exit(1);
}

/**
 * Close connection on exit
 */
process.on('exit', function () {
    self._mysql_conn.closeSync();
});

/**
 * Set charset
 */
this._mysql_conn.realQuerySync('SET NAMES UTF8');

return this;

};

This is the piece of my model:

s.meryl._mysql_conn.query( statements.join(' UNION ALL ') ,function(err,res){

    if (err) {
        console.log('Failed at GetAds.js first query');
        throw err;
    }

    res.fetchAll(function(err,rows){
        if (err) {
            console.log('Failed at GetAds.js first query fetch');
            throw err;
        }

        /** Pass data to cache controller **/
        s.meryl.cache({key:'mainpage1_'+s.meryl.clang,data:rows});

        /** Return data to callback **/
        o.callback({data:rows});

    });

}

);

Using lastest v0.6.3 version of node. Don't know if this is an issue. Before upgrade used v0.3.3pre i think. Installed your v1.2.9 module from npm.

Before upgrade everything worked fine.

I'm currently working on a project, so it's very important to me. Can you please look into it. :)

Thanks for module btw ;)

[Sannis]

I've added test for UNIONs to master and test it with node v0.6.5. No errors :/

Are you running this in highload benchmark or it fails even for single request? Can you post statements.join(' UNION ALL ') result?

[mbabuskov]

Hello,

I can confirm this problem. It does not seem to be related to UNIONs, I don't have any UNION query in my code. My node instance does about 100 queries per second, more than 99% are simple UPDATE statements. The problem started to show up more often (once every two hours approx.) when I explicitly set the write timeout to a larger value:

conn.setOptionSync(conn.MYSQL_OPT_WRITE_TIMEOUT, 60);

I have a mix of sync and async queries in my application. I'm shooting in the dark here, but it is quite possible that sync operation gets started and then some previously started async operation completes. Maybe that matters. The typical workflow of my app. is like this:

• insert
• about 5600 updates
• insert ...etc.

I run INSERTS as sync and UPDATES as async.

P.S. If you're wondering why I set higher timeout. I had problems that I would get: "Lost connection to MySQL server during query" if I run some query via mysql admin tool that took more than 10 seconds. In that case node would lose the connection to mysql.

[mbabuskov]

I believe I found the problem. I used storeResultSync and fetchArraySync for a "select max()" query which returns only one row. So I only used one fetchArraySync and then freeSync. I assume that's what remains in the buffer.

I added another fetchArraySync and it seemed to help, but problem still appears, so maybe this is not the only reason.

Maybe freeSync() should check and empty the buffer (or whatever it is called in mysql land) if not all rows are fetched.

[Sannis]

I have a mix of sync and async queries in my application. I'm shooting in the dark here, but it is quite possible that sync operation gets started and then some previously started async operation completes. Maybe that matters.

You are right. It is not a good Idea to mix sync and async queries for one connection. Use async queries everywhere or just use second connection for sync inserts.