MySQL configuration » History » Revision 4
Revision 3 (Marius BĂLTEANU, 2023-11-20 23:43) → Revision 4/8 (Marius BĂLTEANU, 2023-11-20 23:44)
h1. MySQL configuration
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.
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@.
There are at least two ways to set this in persistent way:
*1. Change Redmine database configuration file*
Add to @database.yml@ configuration file the key @variables@ with @transaction_isolation: "READ-COMMITTED"@ under it as below:
<pre><code class="yml">
production:
adapter: mysql2
database: redmine
host: localhost
[...]
variables:
transaction_isolation: "READ-COMMITTED"
</code></pre>
*2. Change @transaction_isolation@ in the MySQL configuration file on the server*
<pre><code class="conf">
transaction_isolation="READ-COMMITTED"
</code></pre>
* The path to this file depends on the operating system, Operating System, for example, in Ubuntu is @/etc/mysql/conf.d/mysql.cnf@.
* 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.
* All major cloud providers allow changing this setting from the cloud administration page.
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.