Micronaut 4集成SQLite遇只读标志修改失败及启动异常

问题背景 #

执行curl --location 'http://localhost:8080/books'请求时触发内部错误，核心报错信息：

{
    "_links": {
        "self": [
            {
                "href": "/books",
                "templated": false
            }
        ]
    },
    "_embedded": {
        "errors": [
            {
                "message": "Internal Server Error: Could not set JDBC Connection [HikariProxyConnection@820093718 wrapping org.sqlite.jdbc4.JDBC4Connection@7d9e8ef7] read-only: Cannot change read-only flag after establishing a connection. Use SQLiteConfig#setReadOnly and SQLiteConfig.createConnection()."
            }
        ]
    },
    "message": "Internal Server Error"
}

问题根源：Micronaut 4默认会对读操作尝试将已建立的连接设为只读，但SQLite不支持在连接创建后修改只读状态。若全局设置连接为只读，又会导致Flyway启动时无法执行写操作，抛出：

io.micronaut.context.exceptions.BeanInstantiationException: Bean definition [javax.sql.DataSource] could not be loaded: Unable to check whether table "main"."flyway_schema_history" exists
...
Caused by: java.sql.SQLException: A statement has already been executed on this connection; cannot upgrade to write transaction

解决步骤 #

1. 禁用Micronaut Data的自动只读设置 #

在application.yaml中添加配置，阻止Micronaut Data为读操作修改连接状态：

micronaut:
  data:
    jdbc:
      read-only: false

2. 配置Hikari连接池适配SQLite #

修改数据源配置，确保SQLite连接初始即为读写模式，同时避免连接池的状态检查冲突：

datasources:
  default:
    url: jdbc:sqlite:./mydatabase.db
    username: sa
    password: ''
    driver-class-name: org.sqlite.JDBC
    schema-generate: NONE
    dialect: SQLITE # 替换ANSI为SQLite专属方言
    auto-commit: true
    maximum-pool-size: 5
    hikari:
      data-source-properties:
        open_mode: 6 # 对应SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE模式
      connection-test-query: "SELECT 1" # 禁用自动状态检查

3. 修正Repository的方言配置 #

将Repository的方言从ANSI改为SQLITE，让Micronaut Data针对SQLite做适配：

package com.sqlite.example;

import io.micronaut.core.annotation.NonNull;
import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.CrudRepository;
import java.util.Optional;

@JdbcRepository(dialect = Dialect.SQLITE)
public interface SqliteSampleRepository extends CrudRepository<Book,Long> {
    @NonNull Optional<Book> findById(Long id);
}

4. 调整Flyway配置（可选） #

若Flyway仍出现连接冲突，可增加连接重试次数或确保其使用独立连接：

flyway:
  datasources:
    default:
      enabled: true
      connect-retries: 3

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