Mysql2 hanging when trying to create connection and make query and connection is closed in mid-protocol

I opened new issue, since in this case we are just using single connection and there are no queued commands like in #695.

I'm running my stress test script which breaks connection very often like every 1000 sent bytes and every 2 seconds. After running some seconds or some minutes, script hangs when trying to make query (or during connecting + making query).

node mysql2-random-hanging-every-now-and-then.js 2> /dev/null | grep -B500 -A2 -- "- STATS" 
...
<LOTS OF DEBUG PRINTS>
...
    at Promise (/Users/mikaelle/Projects/Vincit/knex/scripts/stress-test/mysql2-random-hanging-every-now-and-then.js:14:11)
    at new Promise (<anonymous>)
    at stdMysqlQuery (/Users/mikaelle/Projects/Vincit/knex/scripts/stress-test/mysql2-random-hanging-every-now-and-then.js:12:10)
    at mysql2Query (/Users/mikaelle/Projects/Vincit/knex/scripts/stress-test/mysql2-random-hanging-every-now-and-then.js:49:21)
    at loopQueries (/Users/mikaelle/Projects/Vincit/knex/scripts/stress-test/mysql2-random-hanging-every-now-and-then.js:124:31)
    at loopQueries (/Users/mikaelle/Projects/Vincit/knex/scripts/stress-test/mysql2-random-hanging-every-now-and-then.js:115:15)
    at <anonymous> code: 'EPIPE', fatal: true }
========== Reconnecting mysql2
Add command: Connection
================ MYSQL2 Running query ======
Add command: query
 raw: 0a352e372e313800a12d000030593f1a2a06300300ffff080200ffc11500000000000000000000701a3c7e57420c786c3a3c51006d7973716c5f6e61746976655f70617373776f726400
296 undefined ==> Connection#handshakeInit(0,,78)
Server hello packet: capability flags:3254779903=(long password, found rows, long flag, connect with db, no schema, compress, odbc, local files, ignore space, protocol 41, interactive, ssl, ignore sigpipe, transactions, reserved, secure connection, multi statements, multi results, ps multi results, plugin auth, connect attrs, plugin auth lenenc client data, can handle expired passwords, session track, deprecate eof, ssl verify server cert, remember options)
Sending handshake packet: flags:8582095=(long password, found rows, long flag, connect with db, odbc, local files, ignore space, protocol 41, ignore sigpipe, transactions, reserved, secure connection, multi results, session track)
296 11681 <== Connection#handshakeInit(1,,63)
296 11681 <== 3b000001cff3820000000000e00000000000000000000000000000000000000000000000726f6f7400142c9a7106dc5bba637643115e7aeb4552e93cf5a500
mysql2 { Error: Connection lost: The server closed the connection.
    at Socket.<anonymous> (/Users/mikaelle/Projects/Vincit/knex/node_modules/mysql2/lib/connection.js:100:35)
    at emitNone (events.js:111:20)
    at Socket.emit (events.js:208:7)
    at endReadableNT (_stream_readable.js:1056:12)
    at _combinedTickCallback (internal/process/next_tick.js:138:11)
    at process._tickCallback (internal/process/next_tick.js:180:9) fatal: true, code: 'PROTOCOL_CONNECTION_LOST' }
================ MYSQL2 Running query ======
Add command: query
------------------------ STATS PER SECOND ------------------------
{ mysql2: { queries: 10, results: 7, errors: 3 } }
------------------------------- EOS ------------------------------
--
--
{ mysql2: { queries: 10, results: 7, errors: 3 } }
------------------------------- EOS ------------------------------
------------------------ STATS PER SECOND ------------------------
{ mysql2: { queries: 0, results: 0, errors: 0 } }
------------------------------- EOS ------------------------------
--
--
{ mysql2: { queries: 0, results: 0, errors: 0 } }
------------------------------- EOS ------------------------------
------------------------ STATS PER SECOND ------------------------
{ mysql2: { queries: 0, results: 0, errors: 0 } }
------------------------------- EOS ------------------------------
--
--
{ mysql2: { queries: 0, results: 0, errors: 0 } }
------------------------------- EOS ------------------------------
------------------------ STATS PER SECOND ------------------------
{ mysql2: { queries: 0, results: 0, errors: 0 } }
------------------------------- EOS ------------------------------
^C
Mikaels-MacBook-Pro-2:stress-test mikaelle$ 

Test code / environment are as follows:

mysql2@1.5.2
mysql@2.15.0
node v8.9.0

docker-compose.yml (starts mysql (33306) + toxiproxy (23306))

version: '3'

services:
  toxiproxy:
    image: shopify/toxiproxy
    ports:
      - "8474:8474"
      - "23306:23306"
    links:
      - "mysql"

  mysql:
    image: mysql:5.7
    ports:
      - "33306:3306"
    environment:
      - TZ=UTC
      - MYSQL_ROOT_PASSWORD=mysqlrootpassword

mysql2-random-hanging-every-now-and-then.js:

/**
 * Test case for figuring out robust way to recognize if connection is dead
 * for mysql based drivers.
 */
const Bluebird = require('bluebird');
const toxiproxy = require('toxiproxy-node-client');
const toxicli = new toxiproxy.Toxiproxy('http://localhost:8474');
const rp = require('request-promise-native');
const _ = require('lodash');

async function stdMysqlQuery(con, sql) {  
  return new Promise((resolve, reject) => {
    try {
      con.query({
        sql,
        timeout: 500
      }, function (error, results, fields) {
        if (error) {
          reject(error);
        } else {
          resolve(results);
        }
      });    
    } catch (err) {
      reject(err); // double sure...
    }
  });
}

// here it is not enough to just change import to require('mysql'), 
// also way to recognize dead connection must be changed (mysql2Con.state === 'disconnected')
const mysql2 = require('mysql2');
let mysql2Con = {_fatalError: 'initmefirst'};
async function mysql2Query(sql) {
  // recreate connection on fatal error
  if (mysql2Con._fatalError) {
    console.log('========== Reconnecting mysql2');
    mysql2Con = mysql2.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'mysqlrootpassword',
      port: 23306,
      connectTimeout: 500,
      debug: true
    });
    mysql2Con.on('error', err => {
      console.log('- STATS Mysql2 connection died:', err);
    });
  }
  console.log('================ MYSQL2 Running query ======');
  const res = await stdMysqlQuery(mysql2Con, sql);
  console.log('====================== done ================');
  return res;
}

const counters = {};
function setMysqlQueryCounters(name) {
  const counts = counters[name] = {queries: 0, results: 0, errors: 0};
}
setMysqlQueryCounters('mysql2');

// start printing out counters
let lastCounters = _.cloneDeep(counters);
setInterval(() => {
  const reqsPerSec = {};
  for (let key of Object.keys(counters)) {
    reqsPerSec[key] = {
      queries: (counters[key].queries - lastCounters[key].queries),
      results: (counters[key].results - lastCounters[key].results),
      errors: (counters[key].errors - lastCounters[key].errors),
    }
  }
  console.log('------------------------ STATS PER SECOND ------------------------');
  console.dir(reqsPerSec, {colors: true});
  console.log('------------------------------- EOS ------------------------------');
  lastCounters = _.cloneDeep(counters);
}, 1000);  

async function recreateProxy(serviceName, listenPort, proxyToPort) {
  try {
    await rp.delete({
      url: `${toxicli.host}/proxies/${serviceName}`
    });  
  } catch(err) {}

  const proxy = await toxicli.createProxy({
    name: serviceName,
    listen: `0.0.0.0:${listenPort}`,
    upstream: `${serviceName}:${proxyToPort}`
  });

  // add some latency
  await proxy.addToxic(new toxiproxy.Toxic(proxy, {
    type: 'latency',
    attributes: {latency: 1, jitter: 1}
  }));

  // cause connections to be closed every some transferred bytes
  await proxy.addToxic(new toxiproxy.Toxic(proxy, {
    type: 'limit_data',
    attributes: {bytes: 1000}
  }));
}

async function main() {
  await recreateProxy('mysql', 23306, 3306);
  setInterval(() => recreateProxy('mysql', 23306, 3306), 2000);

  async function loopQueries(prefix, query) {
    const counts = counters[prefix];

    while(true) {
      try {
        counts.queries += 1;
        // without this delay we might endup to busy failure loop
        await Bluebird.delay(0);
        await query();
        counts.results += 1;
      } catch (err) {
        counts.errors += 1;
        console.log(prefix, err);
      }
    }
  }

  loopQueries('mysql2', () => mysql2Query('select 1'));

  // wait forever
  while(true) {
    await Bluebird.delay(1000);
  }
}

main().then(() => console.log('DONE')).catch(err => console.log(err));

Again sorry for the pretty complicated test setup and finding such difficult problem to figure out. I just hope you are able to reproduce theses findings.

I'm going to add these tests to knex repo to be able to verify in future knex versions that all the dialects have robust way to recognize connection state and drop/create connections to pool even in case when connections are lost in middle of protocol.

Start with the stress-test script and docker-compose.yml, then inspect node_modules/mysql2/lib/connection.js around the socket error handler. Reproduce the failure with MySQL, toxiproxy, and the limit_data toxic while running the query loop. Done means a connection closed mid-protocol does not leave the query waiting indefinitely and the connection can be recognized as failed for recreation.