ERROR An operation controlled by Boost.MySQL timed out

[jphz]

I have encountered this problem, how can I make it work properly.

[jphz]

Appears when querying a large amount of data.

[jphz]

   boost::mysql::pooled_connection connection;
   while (true) {
       connection = _gameDB->async_get_connection(yield[ec]);
       if (!ec) {
           break;
       }
       continue;
   }
···
Is there any other way to solve it?

[anarthal]

This means that the connection pool has reached its upper limit and all connections are now in use. You can solve this in two ways:

• Increase the maximum number of concurrent connections. Please read this section of the docs to learn how to do it.
• Manage your connections more effectively. Bear in mind that a connection is in use until the pooled_connection object returned by async_get_connection is destroyed.

If you post a code snippet with more info, I can provide some help on the second point.

[jphz]

    boost::asio::spawn(_threadPool, [this, self = shared_from_this(), conn = conn->shared_from_this(),
                                     packet = packet->shared_from_this()](boost::asio::yield_context yield) {
        auto msgID = packet->readUInt16();
        uint64_t sn = packet->getParam();
        auto sql = packet->readString();
        LOG_DEBUG("DBServer::onReqMySQLQueryBySession type:{} sn:{} sql:{}", conn->getType(), sn, sql);

        POP_PACKET(sendPacket, Proto::REP_MYSQL_QUERY_BY_SESSION);
        sendPacket->setParam(sn);
        sendPacket->writeUInt16(msgID);

        boost::system::error_code ec{};
        boost::mysql::pooled_connection connection;
        while (true) {
            connection = _gameDB->async_get_connection(yield[ec]);
            if (!ec) {
                break;
            }

            LOG_ERROR("DBServer::onReqMySQLQueryBySession type:{} sn:{} code:{} error:{}", conn->getType(), sn,
                      ec.value(), ec.message());
            continue;
        }

        boost::mysql::results results{};
        connection->async_execute(sql, results, yield[ec]);
        if (ec) {
            LOG_ERROR("DBServer::onReqMySQLQueryBySession type:{} sn:{} code:{} error:{}", conn->getType(), sn,
                      ec.value(), ec.message());
            conn->sendMsg(sendPacket);
            return;
        }

        if (!serialize(results, sendPacket)) {
            sendPacket->reset();
            sendPacket->setMsgID(Proto::REP_MYSQL_QUERY_BY_SESSION);
            sendPacket->setParam(sn);
            sendPacket->writeUInt16(msgID);
        }

        conn->sendMsg(sendPacket);
    });

[jphz]

Do need any additional processing?

[anarthal]

Are the conn->sendMsg calls blocking?

[jphz]

void Connection::sendMsg(Packet::Ptr packet) {
    boost::asio::post(_stream.get_executor(),
                      [this, self = shared_from_this(), packet = packet->shared_from_this()]() {
                          onPack(packet);
                      });
}

[jphz]

Not blocking

[anarthal]

Does onPack call any blocking function (like network read or write)? How many concurrent requests is your application receiving?

[anarthal]

If you don't need any timeout, you can replace this

        while (true) {
            connection = _gameDB->async_get_connection(yield[ec]);
            if (!ec) {
                break;
            }

            LOG_ERROR("DBServer::onReqMySQLQueryBySession type:{} sn:{} code:{} error:{}", conn->getType(), sn,
                      ec.value(), ec.message());
            continue;
        }

by simply

// A zero timeout means "wait indefinitely until a connection is available"
connection = _gameDB->async_get_connection(std::chrono::seconds(0), yield[ec]);

[jphz]

Thank you very much.

[anarthal]

If the rest of your application uses blocking functions and doesn't integrate well with Asio's async model, you may consider using this code to get a synchronous equivalent of asnc_get_connection.

[jphz]

My code is all asynchronous, except for parsing network packets, which takes some time.

[anarthal]

Then you should be fine with what you are doing. Try increasing the number of allowed connections to the server if you get too much latency.

[jphz]

Yes, I need to increase the number of connections. The previous number of connections was too small.