MySQL TestContainers初始化-脚本架构创建失败

Question

尝试在MySQL测试容器实例中创建架构时遇到以下问题。测试用户似乎没有适当的权限？

配置：

spring.datasource.driver-class-name=org.testcontainers.jdbc.ContainerDatabaseDriver
spring.datasource.url=jdbc:tc:mysql:5.7.22:///test?TC_INITSCRIPT=component/db/init_mysql.sql

Caused by: org.testcontainers.ext.ScriptUtils$UncategorizedScriptException: Failed to execute database script from resource [create schema new_schema CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;]
    at org.testcontainers.ext.ScriptUtils.executeDatabaseScript(ScriptUtils.java:375)
    at org.testcontainers.ext.ScriptUtils.executeDatabaseScript(ScriptUtils.java:313)
    at org.testcontainers.jdbc.ContainerDatabaseDriver.runInitScriptIfRequired(ContainerDatabaseDriver.java:196)
    at org.testcontainers.jdbc.ContainerDatabaseDriver.connect(ContainerDatabaseDriver.java:132)
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:136)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:369)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:198)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:467)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:541)
    ... 61 more
Caused by: org.testcontainers.ext.ScriptUtils$ScriptStatementFailedException: Script execution failed (component/db/init_mysql.sql:1): create schema new_schema CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
    at org.testcontainers.jdbc.JdbcDatabaseDelegate.execute(JdbcDatabaseDelegate.java:49)
    at org.testcontainers.delegate.AbstractDatabaseDelegate.execute(AbstractDatabaseDelegate.java:34)
    at org.testcontainers.ext.ScriptUtils.executeDatabaseScript(ScriptUtils.java:362)
    ... 69 more
Caused by: java.sql.SQLSyntaxErrorException: Access denied for user 'test'@'%' to database 'new_schema'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:782)
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:666)
    at org.testcontainers.jdbc.JdbcDatabaseDelegate.execute(JdbcDatabaseDelegate.java:42)
    ... 71 more

组件/db/init_mysql.sql

create schema new_schema CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

在JDBC集成之外创建mysql镜像时遇到了类似的问题：

private static final MySQLContainer mysqlContainer = new MySQLContainer<>(DockerImageName.parse("mysql:5.7"))
            .withInitScript("component/db/init_mysql.sql")
            .withUsername("user")
            .withPassword("password")
            .withExposedPorts(3306)
            .withReuse(true);

关于如何在不使用预先创建的模式创建自定义docker映像的情况下实现这一点，您有什么想法吗？

任何帮助都将不胜感激我觉得我一定是遗漏了一些东西。

Answer 1

通过使用预先创建的模式创建自定义MySQL图像来解决此问题。会很想知道有没有更好的方法。

Answer 2

尝试创建MySQL容器，并对root使用与test用户相同的密码，在本例中为whatever。

@Container
private static MySQLContainer database = new MySQLContainer(MYSQL_DOCKER_TEST_VERSION)
            .withUsername("test")
            .withPassword("whatever")
...

通过@DynamicPropertySource动态添加属性。

    @DynamicPropertySource
    static void databaseProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", database::getJdbcUrl);
        registry.add("spring.datasource.username", database::getUsername);
        registry.add("spring.datasource.password", database::getPassword);
        registry.add("flyway.user", () -> "root");
        registry.add("flyway.password", database::getPassword);
    }

我没有使用flyway，但我相信它应该可以工作。

Answer 3

正如在testcontainers存储库上创建的测试中所看到的，解决方法是为root用户指定密码，并在测试中使用root用户：

MySQLContainer<?> db = new MySQLContainer<>(image)
            .withUsername("test")
            .withPassword("test")
            .withEnv("MYSQL_ROOT_PASSWORD", "test")
            ...

参考：https://github.com/testcontainers/testcontainers-java/commit/2fa00ddbd1f42a20656d5e39d58cdedfbd483dcf#diff-f49cf0d09246a5351504ca32a08deb74e80ac7d61ad012f02d8f09c36508737bR65