[mysql] Duplicate subsegments when using createPool().query

[dwickr]

When using connection pools, the pool.query method can be called directly as a shortcut instead of calling pool.getConnection, then querying (doc):

[pool.query] is a shortcut for the pool.getConnection() -> connection.query() -> connection.release() code flow.

As a result, the subsegments for that query are duplicated because both pool.query and pool.getConnection().query have been captured to create subsegments, and both are called.

Here's an example:

const AWSXRay = require('aws-xray-sdk')

AWSXRay.captureMySQL(require('mysql2'))
const mysqlPromise = require('mysql2/promise')

const ns = AWSXRay.getNamespace()

async function testPromisePoolQuery() {
  try {
    const pool = mysqlPromise.createPool({
      host: 'localhost',
      user: 'user',
      password: 'password',
      database: 'test',
      connectionLimit: 10,
      queueLimit: 0,
    })

    await pool.query('SELECT 1')

    await pool.end()
  } catch (err) {
    console.error('Error connecting to database:', err)
  }
}

ns.run(async () => {
  let segment = new AWSXRay.Segment('testPromisePoolQuery')

  AWSXRay.setSegment(segment)
  await testPromisePoolQuery()
  console.log(
    `testPromisePoolQuery # of subsegments: ${AWSXRay.getSegment().subsegments?.length}`
  )
  segment.close()
})

The result printed from this is testPromisePoolQuery # of subsegments: 2, and if you view the subsegment you can see that the queries are the same, and timings are almost exactly the same.

[dwickr]

I'm hacking around this by patching createPool before calling captureMySQL to prevent it from capturing pool.query and pool.execute. This way it only captures the connections returned from pool.getConnection within the query/execute calls.

function patchCreatePool(mysql: any) {
  mysql._createPool = mysql.createPool
  mysql.createPool = function (config: any) {
    const pool = mysql._createPool(config)

    pool.__query = () => {}
    pool.__execute = () => {}
    return pool
  }
  return mysql
}

[mxiamxia]

Thanks for sharing the details and workaround. X-Ray team is prioritizing the support on OTel SDK where does not have the same problem. We currently not sure when/if we will fix this issue for the current SDK or not, sorry for the inconvenience.
https://github.com/open-telemetry/opentelemetry-js-contrib/blob/main/plugins/node/opentelemetry-instrumentation-mysql2/src/instrumentation.ts