1

I want to get the connection Id (threadId) when executing long query just in case the user at frontend (Vue.js) wants to cancel the query (in case query never ends...). So when the user clicks "Stop query", I would perform KILL (threadId) on Mysql..

I am using pooling with knew (client=mysql). I believe this is something trivial, but can please someone describe how can I access this. my backend (node.js) is set up differently - -- was made by a clever guy, who defined pools and connection as follows:

connection.connect(function(err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }

  console.log('connected as id ' + connection.threadId);
});

const knex = require("knex");

// Helpers -------------------------------------------------------------------
const connection = (user, password) =>
  knex({
    client: "mysql",
    connection: {
      multipleStatements: true,
      host: process.env.MYSQL_IP,
      port: process.env.MYSQL_PORT,
      user,
      password,
      database: "",
      dateStrings: true,
      idleTimeoutMillis: -1
    },
    debug: true,
    pool: {
      min: 0,
      max: 7
    }
  });

const knex_session = pool => (user, password) => {
  if (pool[user] === undefined) {
    pool[user] = connection(user, password);
  }
  return pool[user];
};

// Pool ----------------------------------------------------------------------
const connections = {};

// Exports -------------------------------------------------------------------
exports.admin = connection(
  process.env.MYSQL_USERNAME,
  process.env.MYSQL_PASSWORD
);
exports.client = knex_session(connections);

// ---------------------------------------------------------------------------

I am looking for a way to get the mysql threadId somehow - I imagine from the function(route). Orther options are aother call to mysql (SELECT CONNECTION_ID()) or by somehow extracting his data from request object (however, was not successful here as well). Than I would execute KILL QUERY (threadId) to make sure that the query does not hang and that I can display proper vue component to the user.

Gregor Sotošek
  • 357
  • 1
  • 4
  • 22

1 Answers1

0

As you have discovered, the MySQL connection id is found as an attribute of the connection object: connection.threadId. That's true for knex, because it's layered on mysql.

You can grab it from the connection object whenever it's in scope and put it in a useful place ... something like this if you're in express.

    function whatever (req, res, next) {
       ...
       res.dbmsThreadId = connection.threadId
       ...
    }
O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Tricky part is to knex how to get the connection which is used to make the query since they are hidden behind pool and not allocated before query is done. Good to know though where mysql stores that info. – Mikael Lepistö Apr 10 '19 at 09:43
  • Where do i put this ? I am getting error - UnhandledPromiseRejectionWarning: ReferenceError: connection is not defined – Gregor Sotošek Sep 19 '20 at 06:04