MySQL configuration » History » Revision 3
Revision 2 (Marius BĂLTEANU, 2023-11-20 23:36) → Revision 3/8 (Marius BĂLTEANU, 2023-11-20 23:43)
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), issue, 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, 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.