Kotlin操作MariaDB连接一分钟后断开的问题及解决

问题场景 #

使用Kotlin开发程序，遍历维基百科数据库并将数据存入本地MariaDB（通过docker-compose部署）。程序运行约一分钟后，MariaDB服务器主动断开连接，导致程序终止。排除WAIT_TIMEOUT问题，因为程序全程在与服务器主动通信，并非空闲状态，且相同的Kotlin+MariaDB配置在另一个项目中运行正常。

报错信息 #

Exception in thread "main" org.jetbrains.exposed.exceptions.ExposedSQLException: java.sql.SQLNonTransientConnectionException: Could not connect to localhost:3306 : unexpected end of stream, read 0 bytes from 4 (socket was closed by server)

相关代码 #

数据库初始化代码 #

import org.jetbrains.exposed.sql.Database
import io.github.cdimascio.dotenv.dotenv
import me.koendev.database.*

val dotEnv = dotenv()
lateinit var articleService: ArticleService
lateinit var linkService: LinkService

fun main() {

    val database = Database.connect(
        url = dotEnv["DB_URL"],
        user = dotEnv["DB_USER"],
        password = dotEnv["DB_PASSWORD"],
        driver = "org.mariadb.jdbc.Driver"
    )
    articleService = ArticleService(database)
}

数据库调用代码 #

fun putInDatabase(title: String, link: String) {
    var articleID = articleService.read(title)
    if (articleID == null) {
        articleID = articleService.create(Article(title))
    }
}

Articles表定义代码 #

package me.koendev.database

import org.jetbrains.exposed.dao.IntEntity
import org.jetbrains.exposed.dao.IntEntityClass
import org.jetbrains.exposed.dao.id.EntityID
import org.jetbrains.exposed.dao.id.IntIdTable
import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.SchemaUtils
import org.jetbrains.exposed.sql.select
import org.jetbrains.exposed.sql.transactions.transaction

data class Article(val title: String)

class ArticleItem(id: EntityID<Int>): IntEntity(id) {
    companion object : IntEntityClass<ArticleItem>(ArticleService.Articles)

    var title by ArticleService.Articles.title
}

class ArticleService(database: Database) {
    object Articles : IntIdTable() {
        val title = varchar("title", length = 256)
    }

    init {
        transaction(database) {
            SchemaUtils.createMissingTablesAndColumns(Articles)
        }
    }

    fun create(article: Article): Int {
        var id: Int? = null
        transaction {
            id = ArticleItem.new {
                title = article.title
            }.id.value
        }
        return id as Int
    }

    fun read(title: String): Int? {
        var id: Int? = null
        transaction {
            id = Articles.select {
                Articles.title eq title
            }.map {
                it[Articles.id]
            }.singleOrNull()?.value
        }
        return id
    }
}

已尝试的无效方案 #

调整MariaDB的WAIT_TIMEOUT参数延长时长，问题依旧存在。

解决方法 #

改用HikariCP连接池管理数据库连接，配置如下：

// Setting up DataBase
val config = HikariConfig()
config.jdbcUrl = dotEnv["DB_URL"]
config.username = dotEnv["DB_USER"]
config.password = dotEnv["DB_PASSWORD"]
config.driverClassName = "org.mariadb.jdbc.Driver"
config.maximumPoolSize = 10
config.connectionTimeout = 30000
config.idleTimeout = 600000
val datasource = HikariDataSource(config)

使用连接池后，程序运行稳定，连接可长期保持，满足后续Web应用开发需求。

内容的提问来源于stack exchange，提问作者Mr Horseman