MySQL configuration » History » Revision 1
Revision 1/7
| Next »
Marius BĂLTEANU, 2023-11-20 23:30
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 5.1.1 contains important fixes to mitigate this 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.
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:
production:
adapter: mysql2
database: redmine
host: localhost
username: root
password: ""
# Use "utf8" instead of "utfmb4" for MySQL prior to 5.7.7
encoding: utf8mb4
variables:
transaction_isolation: "READ-COMMITTED"
2. Change transaction_isolation
in the MySQL configuration file on the server¶
transaction_isolation="READ-COMMITTED"
- 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
Updated by Marius BĂLTEANU 9 months ago · 1 revisions