Setting MySQL variables through Ecto in Phoenix

There are situations when you need to change the default limits that MySQL ships with by default. For the most part, you can do this directly through the MySQL client by modifying the appropriate variables. However, when interacting with the database through a Relational Mapper like Ecto, the way to accomplish this is not as clear.

We ran into an issue recently with the GROUP_CONCAT() function that highlighted this.

In MySQL, GROUP_CONCAT() function allows you to join multiple rows into a single string with a comma separating the values.

For an example, if your users table looked like this,

+--------+--------+------------------+
| id     | name   | favourite_colour |
+--------+---------------------------+
| 1      | Jane   | purple           | 
| 2      | John   | purple           | 
| 3      | Nancy  | red              | 
+--------+---------------------------+

The result of running the following query,

SELECT favourite_colour,GROUP_CONCAT(name)
    -> FROM users
    -> GROUP BY favourite_colour;

would look like this.

+------------------+----------------------+
| favourite_colour | GROUP_CONCAT(name)   |
+------------------+----------------------+
| purple           | Jane,John            | 
| red              | Nancy                | 
+------------------+----------------------+

Though this works as expected most of the time, if the string you are generating is long, MySQL will silently trim the excess characters!

You can check the maximum length by running the following

SHOW VARIABLES LIKE '%group_concat_max_len%';

By default, the value is 1024 characters.

As you can imagine, given that the string gets truncated silently without errors, you could end up in situations where data silently gets corrupted over a period of time. Fortunately when this happened to us, we were able to spot it during the early testing stages.

In order to increase the limit, we added the following to config/prod.exs in the repo configuration:

config :myapp, MyApp.Repo,
  adapter: Ecto.Adapters.MySQL,
  username: "username",
  password: "password",
  ...
  after_connect: {Mariaex, :query!, ["SET group_concat_max_len = 1000000", []]}

This meant that when we establish the connection via Ecto, we set the value as a part of that session, allowing us to work with larger strings.

This is one of the many ways you can utilise the after_connect callback. Essentially any variable that can be set using SET variable = value in MySQL can be done using the after_connect callback.

More information can be found in the docs https://hexdocs.pm/ecto_sql/Ecto.Adapters.MySQL.html#module-after-connect-callback