Core: Knex Timeout due to concurrent request

[lhmboyz]

Hi,

Can you help me?
I receive this error when i try to integrated my API with my friend API:

Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

My friend send some request at my API at the same time for every minute like this:

x.x.x.x - - [19/Feb/2018:16:02:00 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 499 0 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:00 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 499 0 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:00 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 499 0 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:00 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 499 0 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:00 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 499 0 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:00 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 499 0 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:00 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 499 0 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:00 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 499 0 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:01 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 499 0 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:01 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 499 0 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:01 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 499 0 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:01 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 499 0 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:01 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 499 0 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:01 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 499 0 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:05 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 409 116 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:05 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 409 116 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:05 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 409 116 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:05 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 409 116 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:05 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 409 116 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:05 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 409 116 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:05 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 409 116 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:05 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 409 116 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:05 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 409 116 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:05 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 409 116 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:05 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 409 116 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:05 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 409 116 "-" "-" "y.y.y.y" x.x.x.x - - [19/Feb/2018:16:02:09 +0700] "POST /v1/ecommerce_transactions/import HTTP/1.1" 499 0 "-" "-" "y.y.y.y"

And the result was my server was down / severe.
How should i handle this kind of problem?

[thetutlage]

It's hard to say unless we see some code.

Also which database, connection settings

[lhmboyz]

Here are the code:

//IMPORT
async import({
    request,
    response,
    session
}) {
    let validator = []
    let status = true

    //Find customer, if not found then create
    let customer = {}
    try {
        customer = await Contact.findOrCreate(
            { client_id: bodies.client_id, origin_id: bodies.customer.id, type: 'user' },
            { client_id: bodies.client_id, origin_id: bodies.customer.id, type: 'user', name: bodies.customer.name }
        )
    } catch (error) {
        status = false

        validator.push({
            param: Antl.forLocale(lang).get('params.CUSTOMER'),
            message: Antl.forLocale(lang).get('validations.WRONG_DATA_FORMAT')
        })
    }

    //Find merchant, if not found then create
    let merchant = {}
    try {
        merchant = await Contact.findOrCreate(
            { client_id: bodies.client_id, origin_id: bodies.merchant.id, type: 'merchant' },
            { client_id: bodies.client_id, origin_id: bodies.merchant.id, type: 'merchant', name: bodies.merchant.name }
        )
    } catch (error) {
        status = false

        validator.push({
            param: Antl.forLocale(lang).get('params.MERCHANT'),
            message: Antl.forLocale(lang).get('validations.WRONG_DATA_FORMAT')
        })
    }

    //Find payment channel, if not found the create
    let paymentChannel = {}
    try {
        paymentChannel = await PaymentChannel.findOrCreate(
            { client_id: bodies.client_id, origin_id: bodies.payment_channel.id },
            { client_id: bodies.client_id, origin_id: bodies.payment_channel.id, name: bodies.payment_channel.name }
        )
    } catch (error) {
        status = false

        validator.push({
            param: Antl.forLocale(lang).get('params.PAYMENT_CHANNEL'),
            message: Antl.forLocale(lang).get('validations.WRONG_DATA_FORMAT')
        })
    }

    if (status) {
        //Find if transaction_id is unique
        let eCommerceTransaction = await ECommerceTransaction.query()
            .where('client_id', bodies.client_id)
            .where('transaction_id', bodies.transaction_id)
            .fetch()

        //Error if not unique
        if (eCommerceTransaction.rows.length > 0) {
            return response.status(409).json({
                meta: await Meta.response('error', 409, {
                    message: Antl.forLocale(lang).get('models.ECOMMERCE_TRANSACTION') + ' ' + Antl.forLocale(lang).get('statuses.ALREADY_EXISTS')
                })
            })
        } else {
            eCommerceTransaction = new ECommerceTransaction()
            this.collectInput({
                eCommerceTransaction,
                bodies
            })
            eCommerceTransaction.customer_id = customer.toJSON().id
            eCommerceTransaction.merchant_id = merchant.toJSON().id
            eCommerceTransaction.payment_channel_id = paymentChannel.toJSON().id

            //Save data
            const trx = await Database.beginTransaction()
            await eCommerceTransaction.save(trx)
            status = await this.saveItems(bodies.items, true, {
                eCommerceTransaction,
                lang,
                trx
            })

            //If status = true, then save
            if (status) {
                await eCommerceTransaction.save(trx)
                trx.commit()

                return response.status(201).json({
                    data: eCommerceTransaction.toJSON(),
                    meta: await Meta.response('success', 201, {
                        message: Antl.forLocale(lang).get('models.ECOMMERCE_TRANSACTION') + ' ' + Antl.forLocale(lang).get('statuses.CREATED_SUCCESSFULLY')
                    })
                })
            } else {
                trx.rollback()

                return response.status(400).json({
                    meta: await Meta.response('error', 400, {
                        message: Antl.forLocale(lang).get('errors.ITEM_DATA_INPUT_IS_NOT_VALID')
                    })
                })
            }
        }
    } else {
        return response.status(400).json({
            meta: await Meta.response('error', 400, validator)
        })
    }
}

[lhmboyz]

The API is used for migrate transaction from another database to my database through my API.

Before I insert the transaction, here are the step my API does:

• Check if Customer is already inserted, if not found then create new Customer
• Check if Merchant is already inserted, if not found then create new Merchant
• Check if Payment Channel is already inserted, if not found then create new Payment Channel
• Check if Total Calculation if right / wrong
• Try save the transaction using

const trx = await Database.beginTransaction()
            await eCommerceTransaction.save(trx)
            status = await this.saveItems(bodies.items, true, {
                eCommerceTransaction,
                lang,
                trx
            })

• Commit the transaction if there is no error on save the Items.

[lhmboyz]

Hi,

I'm using mysql connection, and with standard configuration like this:

mysql: {
    client: 'mysql',
    connection: {
      host: Env.get('DB_HOST', 'localhost'),
      port: Env.get('DB_PORT', '3306'),
      user: Env.get('DB_USER', 'root'),
      password: Env.get('DB_PASSWORD', 'admin'),
      database: Env.get('DB_DATABASE', 'testing')
    }
  },

[thetutlage]

My friend send some request at my API at the same time for every minute like this

I don't think the request is sent every minute, it is being sent every second indeed.

The issue is directly related to knex, or infact with MYSQL itself. The transaction holds a complete connection with itself and hence too many requests at a same time fails.

For more reference you can read https://github.com/tgriesser/knex/issues/1909, https://github.com/tgriesser/knex/issues/1381 and https://github.com/tgriesser/knex/issues/2302

You can try to tweak the pool size and acquireConnectionTimeout values.

[lhmboyz]

Hi,

Thank friend for your advice, the problem is solved by setup pool and acquireConnectionTimeout like you advised.

mysql: {
    client: 'mysql',
    connection: {
      host: Env.get('DB_HOST', 'localhost'),
      port: Env.get('DB_PORT', ''),
      user: Env.get('DB_USER', 'root'),
      password: Env.get('DB_PASSWORD', ''),
      database: Env.get('DB_DATABASE', 'adonis')
    },
    pool: { min: 1, max: 100 },
    acquireConnectionTimeout: 10000
  },

Thank you so much, very appreciate it

[thetutlage]

Cool, going to close it then.

[princenaman]

First of all, I am very sorry to post on a dormant issue. 🙏
But I have been facing this issue from quite some time and don't know what to do 😢

The issue is directly related to knex, or infact with MYSQL itself. The transaction holds a complete connection with itself and hence too many requests at a same time fails.

Is this really true?
I have an application running adonis 3 (knex 0.13)
Whenever there are more than 100 requests at a time Knex throws an error even though mysql is working fine. I have to manually restart the node application to make it work again.

My db config:

  mysql: {
    client: 'mysql',
    connection: {
      host: Env.get('DB_HOST', 'localhost'),
      port: Env.get('DB_PORT', ''),
      user: Env.get('DB_USER', 'root'),
      password: Env.get('DB_PASSWORD', ''),
      database: Env.get('DB_DATABASE', 'adonis')
    },
    debug: true,
    pool: {
      min: 8,
      max: 80
    },
    acquireConnectionTimeout: 60000
  },

The code:

* functionName(request, response) {
    // Basic checks

    // Start a transaction
    const trx = yield Database.beginTransaction()

    try {

        // 500 lines of code with a few if else statements 
        // 2 for loops max complexity n^2 where max n = 5
        // 10-12 mysql save and update queries

        // Save transaction
        trx.commit()

        // send a success email

        return response.status(200).json({
            status: 'success',
            message: 'Thank you.'
        })
    } catch (e) {
        trx.rollback()

        if (e.code === 'ER_DUP_ENTRY') {
            return response.status(200).json({
                status: 'success',
                message: 'Thank you.',
                error: e.toString()
            })
        }

        return response.status(401).json({
            status: 'error',
            message: 'Error. Try again!',
            error: e.toString()
        })
    }
}

Is there anything I can do to prevent this?
Any suggestion is appreciated.

[lock[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.