MySql

Testing

To test against MySQL docker test container set the platform to mysql in src/test/resources/application-test.yaml

Refer to docs / testing if application-test.yaml doesn't exist yet.

Collation

With MySql can (and probably should) specify the collation and character set we want the database to use. In the example below we specify the collation as utf8mb4_unicode_ci.

ebean:
  test:
    platform: mysql #, h2, postgres, mysql, oracle, sqlserver
    ddlMode: dropCreate # none | dropCreate | migrations | create
    dbName: test
    mysql:
      collation: utf8mb4_unicode_ci
      characterSet: utf8mb4

The above will use the following defaults:

"default" - Collation

We can specify the collation to be default and with that the collection will be left up to the default that MySql would use.

ebean:
  test:
    platform: mysql #, h2, postgres, mysql, oracle, sqlserver
    ddlMode: dropCreate # none | dropCreate | migrations | create
    dbName: test
    mysql:
      version: 8.0
      collation: default

In the logs with logging DEBUG level for io.ebean.docker we can see: --character-set-server and --collation-server

... INFO  io.ebean.docker.commands.Commands - Run container ut_mysql with port:4306 db:my_app user:my_app mode:Create shutdown:
... DEBUG io.ebean.docker.commands.Commands - docker run -d --name ut_mysql -p 4306:3306 -e MYSQL_ROOT_PASSWORD=admin mysql:5.6

ebean-mysql dependency

We can use the io.ebean:ebean-mysql dependency rather than io.ebean:ebean if we want to only bring in the MySql specific platform code. Depending on io.ebean:ebean will bring in all platforms.

Types

UUID

UUID is not a native MySQL type and can be mapped to either BINARY(16) or VARCHAR(36).

JSON

We can use @DbJson to map content.

History support

History support for MySQL is provided by generating triggers and history table.

Docker container

We can programmatically start a docker container version of mysql. This can be a useful way to run an application locally.

The below uses ebean-test-docker dependency which already comes with ebean-test. If we do not have a dependency on ebean-test then add io.ebean:ebean-test-docker:5.0 as a dependency.

package main;

import io.ebean.docker.commands.MySqlContainer;

public class Main {

  public static void main(String[] args) {

    MySqlContainer container = MySqlContainer.newBuilder("8.0")
      .dbName("my_db")
      .user("my_user")
      .password("my_pass")
      .build();

    // by default this mysql docker collation is case sensitive using utf8mb4_bin
    // builder.collation("default");
    // builder.collation("utf8mb4_unicode_ci");
    // builder.characterSet("utf8mb4");

    container.start();
  }
}

The above will programmatically start a MySql 8 docker container on port 4306. It will create a database and user with container name ut_mysql. It will drop and re-create the container if the container already exists.

Docker trace logging

Set the logging level for io.ebean.docker to trace to help trouble shoot any issues or understand what ebean-test-docker is doing.

15:59:48.965 [main] TRACE i.e.d.c.Commands - sqlRun: select User from user where User = 'my_user'
15:59:48.980 [main] TRACE i.e.d.c.Commands - sqlRun: show databases like 'my_db'
15:59:48.982 [main] DEBUG i.e.d.c.Commands - sqlRun: drop database my_db
15:59:48.987 [main] TRACE i.e.d.c.Commands - sqlRun: show databases like 'my_db'
15:59:48.989 [main] DEBUG i.e.d.c.Commands - sqlRun: create database my_db
15:59:48.995 [main] DEBUG i.e.d.c.Commands - sqlRun: set global log_bin_trust_function_creators=1
15:59:48.996 [main] TRACE i.e.d.c.Commands - sqlRun: select User from user where User = 'my_user'
15:59:48.997 [main] DEBUG i.e.d.c.Commands - sqlRun: create user 'my_user'@'%' identified by 'my_pass'
15:59:49.001 [main] DEBUG i.e.d.c.Commands - sqlRun: grant all on my_db.* to 'my_use