0

I am using @mysql/xdevapi in my Express.js application for creating REST web APIs.

My current way of using @mysql/xdevapi is

const mysqlx = require('@mysql/xdevapi')

const options = {
    host: 'mysql',
    port: 33060,
    password: 'password',
    user: 'root',
    schema: 'DB_name'
}

const session = await mysqlx.getSession(options)

var getData = async function (req, res) {
    try {
        let { id } = req.body
        var data = (await session.sql(`SELECT * FROM data_table WHERE id = ${id}`).execute()).fetchAll()
        res.json({ success: true, data: data })
    } catch (error) {
        res.json({ success: false, message: "Error in getData" })
    }
}

var app = express()
app.post('/getData', getData)

var server = app.listen(config.express.bindPort, config.express.bindIp, function () {
    console.log(`Express listening on port ${config.express.bindIp}:${config.express.bindPort}`)
})

This setup give me error after few hours

Error: This session was closed because the connection has been idle too long. Use "mysqlx.getSession()" or "mysqlx.getClient()" to create a new one.

What should be right way to use @mysql/xdevapi with Express.js? Any good Boilerplate code?

Alok
  • 7,734
  • 8
  • 55
  • 100

1 Answers1

0

You are creating a connection to the database on startup. The connection is always bound to the value specified by the wait_timeout variable in the MySQL server. This means that eventually, if there is no activity, the server will close it.

In your specific case, I would suggest you use a connection pool and then each handler should acquire its own connection from the pool. In theory, you can do the same with standalone connections, but it's way more costly.

So, you can do something like the following (simplified):

const mysqlx = require('@mysql/xdevapi')

// ...

const pool = mysqlx.getClient(options)

var getData = async function (req, res) {
  let session
  
  try {
    // acquire a connection from the pool
    session = await pool.getSession()
    // ...
    let { id } = req.body
    var data = (await session.sql(`SELECT * FROM data_table WHERE id = ${id}`).execute()).fetchAll()
    res.json({ success: true, data: data })
  } catch (error) {
    res.json({ success: false, message: "Error in getData" })
  } finally {
    if (session) {
      // ensure the connection returns to the pool
      await session.close()
    }
  }
}

// ...

Disclaimer: I'm the lead developer of the MySQL X DevAPI Connector for Node.js

ruiquelhas
  • 1,905
  • 1
  • 17
  • 17
  • In this approach should close session e.g. `session.close()` or no need to close? – Alok Jul 20 '23 at 09:41
  • Yes, you can close the session to return the connection back to the pool. Otherwise, you will have to rely on the pool configuration options (in particular, `maxIdleTime`). I'll update the code snippet. – ruiquelhas Jul 20 '23 at 09:44
  • Earler I was using this pool based approch only with `poolingOptions = { enabled: true, maxSize: 20, maxIdleTime: 60000, queueTimeout: 15000 }` but I was getting error `Error: Could not retrieve a connection from the pool. Timeout of 15000 ms was exceeded.`. I was closing session properly still I was gettting this and I am sure I was not using more than 20 connection parallelly. So I stopped using pool based approch as I have to descide pool size too. – Alok Jul 20 '23 at 09:52
  • You must have been, somehow, exceeding 20 open connections. Otherwise, you would not be getting that error. If you think that is not the case, I suggest you open a bug report at https://bugs.mysql.com/ using the `Connector for Node.js` category, preferably with some test case or sample to reproduce the issue. – ruiquelhas Jul 20 '23 at 10:03