Hello team, am having an maximum mysql connection...

# exposed

[david dereba]

Hello team, am having an maximum mysql connection exceptions . am using hikari datasource with 120 connections

does it mean that am not closing my exposed db connections?

java.sql.SQLSyntaxErrorException: User 'cashio' has exceeded the 'max_questions' resource (current value: 1000)

[david dereba]

This is my dao close, and I think the closing of connection is automatically handled.

object MessageRepository {

    suspend fun createMessage(dispatcher: CoroutineDispatcher = <http://Dispatchers.IO|Dispatchers.IO>, message: Message): Int =
        withContext(dispatcher) {
            val currentTime: LocalDateTime = LocalDateTime.now()

            suspendedTransactionAsync {
                val insertStatement = Messages.insert { row ->
                    message.content?.let { value -> row[Messages.content] = value }
                    message.groupChatId?.let { value -> row[groupChatId] = value }
                    row[sentAt] = currentTime
                    message.userId?.let { value -> row[userId] = value }
                }
                val generatedId = insertStatement[Messages.messageId]
                generatedId
            }.await()
        }

    suspend fun updateMessage(message: Message, messageId: Int): Boolean {
        val currentDateTime = LocalDateTime.now()
        return transaction {
            val updateStatement = Messages.update({ Messages.messageId eq messageId }) {
                message.content?.let { value -> it[content] = value }
                message.groupChatId?.let { value -> it[groupChatId] = value }
                message.userId?.let { value -> it[userId] = value }
                it[sentAt] = currentDateTime
            }
            updateStatement > 0
        }
    }


    suspend fun updateLastTimeOnline(userId: Int): Boolean {
        val currentDatetime: LocalDateTime = LocalDateTime.now()

        return transaction {
            val latestMessageIdSubQuery = Messages
                .slice(Messages.messageId)
                .select { (Messages.userId eq userId) }
                .orderBy(Messages.messageId, SortOrder.DESC)
                .limit(1)
                .singleOrNull()

            // Update the last_time_online for the latest record of the user
            val updateStatement = latestMessageIdSubQuery?.let {
                Messages.update({ Messages.messageId eq it[Messages.messageId] }) {
                    it[lastTimeOnline] = currentDatetime
                }
            }
            updateStatement?.let { it > 0 } ?: false
        }
    }

    suspend fun getAllMessagesAfterUserLastOnline1(userId: Int, pageNumber: Int = 1, pageSize: Int = 20): List<Message> {
        return transaction {
            val lastOnline =
                Messages
                    .slice(Messages.lastTimeOnline, Messages.messageId)
                    .select { (Messages.userId eq userId) }
                    .orderBy(Messages.lastTimeOnline, SortOrder.DESC)
                    .limit(1)
                    .singleOrNull()?.get(Messages.lastTimeOnline)

            val messages =
                Messages.slice(Messages.columns)
                    .select {
                        (Messages.userId neq userId) and (lastOnline?.let { Messages.sentAt greaterEq it } ?: Op.TRUE)
                    }
                    .orderBy(Messages.sentAt, SortOrder.ASC)
                    .limit(pageSize, offset = ((pageNumber - 1) * pageSize).toLong())
                    .map { it.toMessage() }

            messages
        }
    }

    suspend fun getAllMessagesAfterUserLastOnline(userId: Int, pageNumber: Int = 1, pageSize: Int = 20): List<Message> {
        return transaction {
            val lastOnline =
                Messages
                    .slice(Messages.lastTimeOnline)
                    .select { (Messages.userId eq userId) }
                    .orderBy(Messages.lastTimeOnline, SortOrder.DESC)
                    .limit(1)
                    .singleOrNull()?.get(Messages.lastTimeOnline)

            // Check if lastOnline is null, throw an exception or handle the case
            val lastOnlineNonNull = lastOnline ?: throw IllegalStateException("Last online time is null for user $userId")
            <http://logger.info|logger.info>("$greenColor The last online time determined: |  {}$resetColor",lastOnline)


            val messages =
                Messages.slice(Messages.columns)
                    .select {
                        (Messages.userId neq userId) and (Messages.sentAt greater lastOnlineNonNull)
                    }
                    .orderBy(Messages.sentAt, SortOrder.ASC)
                    .limit(pageSize, offset = ((pageNumber - 1) * pageSize).toLong())
                    .map { it.toMessage() }
            <http://logger.info|logger.info>("$greenColor The last online time determined: |  {}",lastOnline)

            messages
        }
    }

    suspend fun getGroupMessagesAfterUserLastOnline(userId: Int, groupChatId: Int, pageSize: Int = 20, pageNumber: Int = 1): List<Message> {
        return transaction {
            // Fetch the last time the user was online
            val lastOnlineMessageId = Messages
                .slice(Messages.messageId)
                .select { Messages.userId eq userId and (Messages.groupChatId eq groupChatId) } // Filtering by groupChatId
                .orderBy(Messages.lastTimeOnline, SortOrder.DESC)
                .limit(1)
                .singleOrNull()
                ?.get(Messages.messageId)

            // If no last online message id found, return empty list
            if (lastOnlineMessageId == null) {
                <http://logger.info|logger.info>("No messages found after the last online time for user $userId in group $groupChatId")
                return@transaction emptyList()
            }
            <http://logger.info|logger.info>("$greenColor The last online time determined: |  {}$resetColor",lastOnlineMessageId)

            // Fetch messages sent after the last online message id for the specified group
            val messages = Messages
                .innerJoin(GroupChats, { Messages.groupChatId }, { GroupChats.groupChatId }) // JOIN operation
                .select {
                    (Messages.userId neq userId) and
                            (Messages.messageId greater lastOnlineMessageId) and
                            (GroupChats.groupChatId eq groupChatId) // Filter by groupChatId
                }
                .orderBy(Messages.messageId, SortOrder.ASC)
                .limit(pageSize, offset = ((pageNumber - 1) * pageSize).toLong())
                .map { it.toMessage() }

            // Log the fetched messages
            messages.forEach { <http://logger.info|logger.info>("$greenColor Message Timestamp> {}$resetColor", it) }

            messages
        }
    }




    suspend fun getMessagesFromDateRange(
        startDateTime: LocalDateTime? = null,
        endDateTime: LocalDateTime? = null
    ): List<Message> {
        val defaultStartDateTime = LocalDateTime.now().minus(2, ChronoUnit.DAYS)
        val defaultEndDateTime = LocalDateTime.now()

        val startDate = startDateTime ?: defaultStartDateTime
        val endDate = endDateTime ?: defaultEndDateTime

        return transaction {
            Messages.select { Messages.sentAt.between(startDate, endDate) }
                .map { it.toMessage() }
        }
    }

    suspend fun getAllMessages(pageNumber: Int = 1, pageSize: Int = 20): List<Message> {
        return transaction {
            Messages.selectAll()
                .limit(pageSize, offset = ((pageNumber - 1) * pageSize).toLong())
                .map { it.toMessage() }
        }
    }

    suspend fun getMessageById(messageId: Int): Message? {
        return transaction {
            Messages.select { Messages.messageId eq messageId }
                .singleOrNull()?.toMessage()
        }
    }

    private fun ResultRow.toMessage(): Message {
        return Message(
            this[Messages.messageId],
            this[Messages.groupChatId],
            this[Messages.content],
            this[Messages.sentAt],
            this[Messages.userId],
            this[Messages.lastTimeOnline]
        )
    }
}

[david dereba]

what could be the issue?