Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

[rommy2015]

• sql/init_db.sql

CREATE DATABASE IF NOT EXISTS 'db_test;
CREATE USER `test`@`%` IDENTIFIED BY 'test';
GRANT ALL PRIVILEGES ON `db_test`.* TO `test`@`%`;

• in Spring boot

....
String pathToInitScripts = "sql";

        MY_SQL_CONTAINER = new MySQLContainer<>(mysql)
                .withDatabaseName("db_test")
                .withUsername("test")
                .withPassword("test")
                .withInitScript(pathToInitScripts + "/init_db.sql")
                .withReuse(true);

        MY_SQL_CONTAINER.start();
@DynamicPropertySource
    public static void registerPgProperties(DynamicPropertyRegistry propertyRegistry) {

        String jdbcUrl = MY_SQL_CONTAINER.getJdbcUrl();
        System.out.println(jdbcUrl);

        propertyRegistry.add("spring.datasource.url", getMySqlContainer()::getJdbcUrl);
        propertyRegistry.add("db_test", getMySqlContainer()::getDatabaseName);
        propertyRegistry.add("spring.datasource.username", getMySqlContainer()::getUsername);
        propertyRegistry.add("spring.datasource.password", getMySqlContainer()::getPassword);
    }

I have gotten errors:

Caused by: org.testcontainers.ext.ScriptUtils$ScriptStatementFailedException: Script execution failed (sql/init_db.sql:2): CREATE USER `test`@`%` IDENTIFIED BY 'test'
	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)
	... 71 more
Caused by: java.sql.SQLSyntaxErrorException: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)

How can this be fixed. ? I can't create tables in testcontainer, I can't use create and alter, create schema- commands and others because of an access error.

[rnorth]

As this is a usage related question, I'll convert to a discussion.

[kiview]

Hey @rommy2015,
it seems you are using the MySQLContainer implementation with a postgres image. The implementation makes certain assumptions about the image, that a postgres image can't provide. Use PostgreSQLContainer instead.

In addition, for this usage, the init_db.sql script should not be necessary in the first place, user and database are already provided, just use the values from the getters.

[rommy2015]

Hello. Everything is correct here, because it is a variable. It contains a link to the name of the image - "mysql". When SpirngBoot raised the tests, I saw that the mysql image was being pumped out.

[kiview]

The image will already have a database and a user, no need to create an additional user.

[dalegaspi]

@kiview I'm doing a similar thing here...i am creating a new database in the init scripts...

@SpringBootTest(properties = {"spring.r2dbc.url=r2dbc:tc:mariadb:///firewall?TC_IMAGE_TAG=10.9.3"})
@TestPropertySource(properties = "spring.config.location=classpath:application-test.yml")
@TestInstance(TestInstance.Lifecycle.PER_CLASS)
public class KeywordRepositoryTests  {
  // ...
}

all good so far, the db container is spinning up...however i need to create another db app_user (in addition to the one specified firewall
in the url).

i have this in my init sql script:

CREATE DATABASE if not exists `app_user`;

i get this error:

Failed to execute SQL script statement #1 of class path resource [sql/firewall-schema.sql]: CREATE DATABASE
 if not exists `app_user`; nested exception is io.r2dbc.spi.R2dbcBadGrammarException: [1044] [42000] Access
 denied for user 'test'@'%' to database 'app_user'
org.springframework.r2dbc.connection.init.ScriptStatementFailedException: Failed to execute SQL script 
statement #1 of class path resource [sql/firewall-schema.sql]: CREATE DATABASE if not exists `app_user`;
 nested exception is 
 io.r2dbc.spi.R2dbcBadGrammarException: [1044] [42000] Access denied for user 'test'@'%'  
 to database 'app_user'

even if i add this line, i get the same error

GRANT ALL PRIVILEGES ON `app_user`.* TO `test`@`%`;

i get the same error.

what am i doing wrong? thanks.

[dalegaspi]

it seems there is no way to override the user test with root even if i do something like this:

@DynamicPropertySource
    public static void dbProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.r2dbc.username", () -> "root");
}

because it will complain with the same error message, which still states that the user is test.

so I ended up overriding the container creation:

@SpringBootTest
@TestPropertySource(properties = "spring.config.location=classpath:application-test.yml")
@TestInstance(TestInstance.Lifecycle.PER_CLASS)
public class KeywordRepositoryTests  {
    @Autowired
    DatabaseClient databaseClient;

    @Container
    public static MariaDBContainer container = new MariaDBContainer<>("mariadb:10.9.3")
            .withPrivilegedMode(true)
            .withEnv("MYSQL_DATABASE", "firewall")
            .withEnv("MYSQL_USER", "root")
            .withEnv("MYSQL_ROOT_PASSWORD", "")
            .withEnv("MYSQL_ALLOW_EMPTY_PASSWORD", "yes");

    @DynamicPropertySource
    public static void dbProperties(DynamicPropertyRegistry registry) {
        container.start();
        registry.add("spring.r2dbc.url", () -> "r2dbc:mariadb://"
                + container.getHost() + ":" + container.getFirstMappedPort()
                + "/" + container.getDatabaseName());
        registry.add("spring.r2dbc.username", () -> "root");
        registry.add("spring.r2dbc.password", () -> container.getPassword());
    }

    @BeforeAll
    public void setup() {
        databaseClient.inConnection(connection -> {
            // https://www.demo2s.com/java/spring-encodedresource-tutorial-with-examples.html
            var res = new ClassPathResource("sql/firewall-schema.sql");
            return ScriptUtils.executeSqlScript(connection, res);
        }).block();
    }
}

I figure there should be an easy way to override the user in the URL passed to the @SpringBootTest annotation but there doesn't seem to be a way to do so even when i looked here...or maybe i'm missing something.