Add support for nodejs mysql2 package

[alextanhongpin]

Is there any possibility that support will be added for the module mysql2?

[tuananh]

I have a bit of time. I can take a look at it if no one else is on it.

[alextanhongpin]

Appreciate it 👍

[billnbell]

We also need the mysql2 client. Mostly due to the support for prepared statements.

See http://docs.aws.amazon.com/xray/latest/devguide/xray-sdk-nodejs-sqlclients.html

var AWSXRay = require('aws-xray-sdk');
var mysql = AWSXRay.captureMySQL(require('mysql'));
...
var connection = mysql.createConnection(config);

Can we do the following with mysql2? We need to use MYSQL2 for Prepared statements. https://github.com/sidorares/node-mysql2#using-prepared-statements

var AWSXRay = require('aws-xray-sdk');
var mysql = AWSXRay.captureMySQL(require('mysql2'));
...
var connection = mysql.createConnection(config);

[awssandra]

Hi billnbell,

The patcher needs to be modified to gather data from the connection.execute call for mysql2. The current patcher will not extract the data for prepared statements.
Additional integration work is required to support this.

Additionally, for automatic mode, CLS does not support async/await so any async/await functionality in mysql2 will not be supported. Also, the SDK itself does not have promise support as of yet (although there is one WIP PR with a suggested solution for this we are reviewing at the moment) so that too would not be supported for mysql2 as of yet.

[billnbell]

OK. What is the estimated time for CLS to support async/await?

I did notice this: https://github.com/othiym23/cls-middleware

Wondering if that could help the team?

[tc]

Any updates on supporting mysql2? we would like to use this with sequelize.

[jafl]

I submitted #62 as a first attempt to support mysql2: just query()

If that is accepted, then I would be willing tackle prepared statements.

[jafl]

#62 has been merged. I will tackle prepared statements tomorrow.

[kh01]

I got this message. Do you know what it mean?
Failed to capture MySQL. Cannot resolve sub/segment

[haotianw465]

@kh01 hi there, are you using the head of this repo with #62 or just the official release SDK? Do you have a code snippet where it generates this error?

A SQL transactions is usually a subtask of serving an event/request so it is captured as a subsegment. It needs a parent segment/subsegment which represents the parent task. If the tracing context is not preserved when the SQL transaction happens, then the SDK doesn't know which parent to attach to resulting in an error like this. You can learn more about the segment/subsegment concepts here: https://docs.aws.amazon.com/xray/latest/devguide/xray-concepts.html#xray-concepts-segments

[kh01]

@haotianw465: I'm using the official release. I think it has to do with how tracing context for async/await.
I'm assuming that if I add this, it will capture automatically.
var mysql = AWSXRay.captureMySQL(require('mysql'));
But then I realize that from #60 , there seems an issue with async/await.
Normally, we can do this var connection = mysql.createConnection(config);
but in our case, we turn it to an async function so that we can use with async/await.
something like this implementation https://chenshenhai.github.io/koa2-note/note/mysql/async.html

[haotianw465]

@kh01 Yes. The async/await part of mysql2 is blocked until we solve #60. We will update our progress as soon as possible.

[jafl]

@kh01 Partial support for mysql2 was added in #62 and #68

[yvele]

Any workaround to wrap mysql2/promise? How can I already benefits from partial support?

I tried

import mysql from "mysql2"; // v1.6.4
import captureMySQL from "aws-xray-sdk-mysql"; // v2.1.0

captureMySQL(mysql);

const mysqlPromise = require("mysql2/promise");

const pool = mysqlPromise.createPool({
  user, password, database, host, port,
  ssl : "Amazon RDS",
  connectionLimit : 1
});

await pool.execute("SELECT * FROM table"); // <-- Using `query` works BTW

But tracing is throwing in mysql_p.js at line 39

Cannot read property 'values' of undefined

PS: Even when manually wrapping the callback in a promise, I get the sam error 🤔

Edit: Ok I found a workaround, the problem is with execute, if I use query everything is working fine 👍

[jafl]

The problem with pool.execute is that node-mysql2 doesn't return a connection object, so aws-xray-sdk-node doesn't have anything to work with :(

[Suvab-rently]

@jafl ,@yvele , Do you know how we can use AWS Xray for tracing the sequelize ORM queries?
I am using PSQL but the documentation only shows how to use with pg client, Does anyone know how to use with Sequelize?

[willarmiros]

Support for mysql2 (promisified queries) should be available with the aws-xray-sdk-mysql package thanks to #328! Closing this feature request, please open a new issue if there are any problems.

[dipakchavda2912]

@willarmiros Based on the previous conversation and above comments, I have used the modules but it does not work as expected. aws-xray-sdk does not log the mysql query in aws-xray.

I have another different solution, but it requires a lot of rework. Hence this would be the best feasible solutions. I have also referred following link, but it does not work.

#275

const mysql2 = require("mysql2");

// Prevent to throw an error of aws xray sdk,
// while executing a standalone script offline.
const awsXRay = require("aws-xray-sdk");
const captureMySQL = require('aws-xray-sdk-mysql');
const mysql = captureMySQL(mysql2);  

module.exports.getConnection = async (client, service) => {
  const poolConfig = {
    host: mysqlHost,
    port: mysqlPort,
    user: mysqlUser,
    password: mysqlPass,
    database: mysqlDb,
    multipleStatements: true
  };
  const connection = mysql.createConnection(poolConfig);
  connection.connect();
  // create promise to use await
  connection.query = util.promisify(connection.query);
  return connection;
};