Project

General

Profile

MySQL configuration » History » Version 3

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

1 1 Marius BĂLTEANU
h1. MySQL configuration
2
3
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
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
* The path to this file depends on the Operating System, for example, in Ubuntu is @/etc/mysql/conf.d/mysql.cnf@.
30
* 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.