Project

General

Profile

MySQL configuration » History » Version 5

Marius BĂLTEANU, 2023-11-20 23:44

1 1 Marius BĂLTEANU
h1. MySQL configuration
2
3 5 Marius BĂLTEANU
Redmine using MySQL 5.6, 5.7, 8.0 and 8.1 have known issues in case of concurrent modifications to the issue nested set, i.e. by parallel modification of various issues' parent_id). More details can be found in issue #39437 and all the related issues to that ticket. 
4 1 Marius BĂLTEANU
5 3 Marius BĂLTEANU
Redmine higher or equal with version:"5.1.1" contains important fixes to mitigate this issue (r22458, r22459 and r22460), but those fixes requires also to change the @transaction_isolation@ to @READ COMMITTED@ in order to properly work. The default MySQL transaction isolation level is @REPEATABLE READ@ according to the "official documentation":https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html. For MySQL version 5.7.20 or older, the setting is @tx_isolation@ instead of @transaction_isolation@.
6 1 Marius BĂLTEANU
7
There are at least two ways to set this in persistent way:
8
9 2 Marius BĂLTEANU
*1. Change Redmine database configuration file*
10 1 Marius BĂLTEANU
11
Add to @database.yml@ configuration file the key @variables@ with @transaction_isolation: "READ-COMMITTED"@ under it as below:
12
13
<pre><code class="yml">
14
production:
15
  adapter: mysql2
16
  database: redmine
17
  host: localhost
18 2 Marius BĂLTEANU
  [...]
19 1 Marius BĂLTEANU
  variables:
20
    transaction_isolation: "READ-COMMITTED"
21
</code></pre>
22
23 2 Marius BĂLTEANU
*2. Change @transaction_isolation@ in the MySQL configuration file on the server*
24 1 Marius BĂLTEANU
25
<pre><code class="conf">
26
transaction_isolation="READ-COMMITTED"
27
</code></pre>
28
29 4 Marius BĂLTEANU
* The path to this file depends on the operating system, for example, in Ubuntu is @/etc/mysql/conf.d/mysql.cnf@.
30 1 Marius BĂLTEANU
* Changing this setting, requires a server restart. If you don't want to restart the server, the setting can be applied also at the runtime by running the following query: @SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;@, but it is importat to make also the change in the configuration file in order to persist the setting after restart.
31
* All major cloud providers allow changing this setting from the cloud administration page.
32
33 2 Marius BĂLTEANU
As any other change made to a production system, is it strongly recommended to test this change before making it in the production. In case of a more advanced setup with replica, this change can may also change the binary log format of your server because only row-based binary logging is supported with the @READ COMMITTED@ isolation level.