Allow super previleges configuration

[membersound]

I'm writing an integration test for a batch LOAD DATA process.
Problem: the process to test executes a SET sql_log_bin = 0. On a normal mariadb that's no problem if the executing user has the correct permissions.

But the testcontainers user does not. Also also tried using root/test as login credentials for the testcontainers database. Same result.

So could you add some kind of boolean flag that allows super previleges being executed?

java.sql.DataSource ds;
java.sql.Connection con = DataSourceUtils.getConnection(ds);
Statement st = con.createStatement();
st.executeQuery("SET sql_log_bin = 0"); //this fails!
//...
st.executeBatch();
JdbcUtils.closeStatement(st);

Result:

java.sql.SQLSyntaxErrorException: (conn=9) Access denied; you need (at least one of) the SUPER privilege(s) for this operation
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:242) ~[mariadb-java-client-2.4.4.jar:na]
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:171) ~[mariadb-java-client-2.4.4.jar:na]
	at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:248) ~[mariadb-java-client-2.4.4.jar:na]
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:338) ~[mariadb-java-client-2.4.4.jar:na]
	at org.mariadb.jdbc.MariaDbStatement.executeQuery(MariaDbStatement.java:512) ~[mariadb-java-client-2.4.4.jar:na]
	at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:111) ~[HikariCP-3.4.2.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java) ~[HikariCP-3.4.2.jar:na]

[vcvitaly]

@membersound is it still relevant? Since Testcontainers allows configuring the database user I'm not sure I understand the problem. If you want we could chat in some messenger, @ Testcontainers slack for example, I've been using Testcontainers for a while and might be able to suggest something

[membersound]

Thank you, yes it is still relevant. And it can be reproduced simply as written above. I'm using testcontainers-mariadb for the junit test, and when I execute a sql statement requiring super previleges like "SET sql_log_bin = 0", the exception occurs.

If you have any clue how I can set up a testcontainers user that has those permissions, that would also be fine. So far I tried the default users test/test and root/test, both not having those permissions obviously.

[vcvitaly]

@membersound ok, could you please share some example configuration that I could easily run in Intellij perhaps?

[membersound]

Yes of course. Find a complete maven example attached.
Thanks for caring!
testcontainers-2627.zip

[vcvitaly]

I see that there is indeed the issue but didn't have time yet to think about it (I'm not a maintainer, just a user of Testcontainers)

If that's fine with you then try using the docker-compose mode, something like this

@ClassRule
    public static DockerComposeContainer docker = new DockerComposeContainer(
            new File("src/test/resources/compose-mysql-test.yml")
    )
            .withLocalCompose(true);

You can check the documentation here https://www.testcontainers.org/modules/docker_compose/#local-compose-mode

That should solve your problem since you will configure the user yourself and work with normal
mariadb instance

[membersound]

I would want to avoid this, because my tests must be runnable on any machine, independent of local docker presence.

Couldn't it be an option to introduce some kind of setter the simply allows all privileges for a user to execute? I mean, it's simply a testcontainer, so it shouldn't be an issue to give the testcontainer users wider permissions?

[rnorth]

Let me check - you're obtaining a root login to MySQL, right? Are you doing something like:

  .withUsername("root")
  .withPassword("test")

and connecting with those credentials?

[membersound]

I'm not yet, I simply use the spring settings:

spring.datasource.user=root
spring.datasource.password=test

But I could as well use the setter configuration approach. Anything that works would be welcome.

[rnorth]

I think we have a bug in MySQLContainer with root access. In the short term I think you should be able to use root access with a container created like:

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

and explicitly connecting using the root account, e.g. the equivalent of DriverManager.getConnection(db.getJdbcUrl(), "root", "test");

I'll create a draft PR with some repro examples which I think indicate a bug. Sorry that you've encountered this the hard way!

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If you believe this is a mistake, please reply to this comment to keep it open. If there isn't one already, a PR to fix or at least reproduce the problem in a test case will always help us get back on track to tackle this.

[membersound]

I still think this is relevant and should not be closed by a bot.