default: &default
  encoding: utf8
  adapter: mysql2
  variables:
    sql_mode: TRADITIONAL

development:
  <<: *default
  database: whitehall_development
  username: whitehall
  password: whitehall
  url: <%= ENV["DATABASE_URL"] %>
  # Note that there is also a 'whitehall_fe' user, used
  # by the 'whitehall_frontend' machines. It should have
  # only 'SELECT' privileges on the database.

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test: &test
  <<: *default
  database: whitehall_test<%= "_executor_#{ENV['EXECUTOR_NUMBER']}_" if ENV['EXECUTOR_NUMBER']%><%= ENV['TEST_ENV_NUMBER'] %>
  username: whitehall
  password: whitehall
  url: <%= ENV["TEST_DATABASE_URL"] %><%= ENV['TEST_ENV_NUMBER'] if ENV["TEST_DATABASE_URL"] %>

production:
  <<: *default
  database: whitehall_production
  pool: 10

cucumber:
  <<: *test

default: &default
  encoding: utf8
  adapter: mysql2
  variables:
    sql_mode: TRADITIONAL
    # We need to set `sort_buffer_size` to work around a bug in MySQL 8.0.27 [1]
    # This doubles the default sort buffer size, which seems to provide enough headroom to allow sorting to work correctly with our dataset.
    # The bug is fixed in MySQL 8.0.28 (currently unreleased), so we should be able to remove this fix once we're on that version.
    # [1]: https://bugs.mysql.com/bug.php?id=105304
    sort_buffer_size: 524288

development:
  <<: *default
  database: whitehall_development
  username: whitehall
  password: whitehall
  url: <%= ENV["DATABASE_URL"] %>
  # Note that there is also a 'whitehall_fe' user, used
  # by the 'whitehall_frontend' machines. It should have
  # only 'SELECT' privileges on the database.

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test: &test
  <<: *default
  database: whitehall_test<%= "_executor_#{ENV['EXECUTOR_NUMBER']}_" if ENV['EXECUTOR_NUMBER']%><%= ENV['TEST_ENV_NUMBER'] %>
  username: whitehall
  password: whitehall
  url: <%= ENV["TEST_DATABASE_URL"] %><%= ENV['TEST_ENV_NUMBER'] if ENV["TEST_DATABASE_URL"] %>

production:
  <<: *default
  database: whitehall_production
  pool: 10

cucumber:
  <<: *test

Work around a MySQL 8.0.27 bug that broke sorting
Work around a MySQL 8.0.27 bug that broke sorting

There's [a bug in MySQL 8.0.27][1] which increases the memory required to order result sets containing JSON or TEXT fields.

This affected us when trying to list Policy Groups in the Whitehall Admin.

When a user visits:

```
/government/admin/groups
```

Rails performs a query like:

```
> SELECT * FROM policy_groups ORDER BY name;
ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size
```

Which was resulting in a 500 Internal Server Error response from the server.

---

MySQL query performance can decrease as the [sort buffer size][2] is increased, so it's important not to increase it more than is necessary.

[Guidance on the MySQL bug][3] suggests the sort buffer size needs to be at least 15 times that of the largest row in the sort. In our case, the longest value in the `policy_groups` table is 26227 characters (in the `description` TEXT field). Assuming this field makes up the bulk of the row's size, it means the sort buffer size should be at least 15 × 26227 = 393405.

The default value of 262144 is less than this. But doubling it to 524288 allows the query to execute successfully and seems to provide enough headroom for some flexibility.

---

This bug should be resolved in the next patch release of MySQL (version 8.0.28). We should therefore try removing this workaround once we're able to run against that newer version.

[1]: https://bugs.mysql.com/bug.php?id=105304
[2]: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sort_buffer_size
[3]: https://bugs.mysql.com/bug.php?id=105304#c517545
