英文:
Mysql : Cannot set Transaction Isolation level
问题
Mysql版本：5.7.41-0ubuntu0.18.04.1
无法修改隔离级别为Drupal10数据库所需的READ COMMITTED：
以root身份运行以下命令
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables WHERE Variable_name LIKE "%isolation";
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0.00 sec)
或者将以下内容添加到settings.php文件中的数据库数组中：
'init_commands' => [
'isolation_level' => 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED',
],
有什么解决的想法吗？
英文:
Mysql version :5.7.41-0ubuntu0.18.04.1
Cannot modify the isolation level into READ COMMITTED needed for a Drupal10 database:
Running these commands as root
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0,00 sec)
mysql> show variables WHERE Variable_name LIKE "%isolation";
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0,00 sec)
or adding
'init_commands' => [
'isolation_level' => 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED',
],
to the database array in settings.php
Any idea how to solve?
答案1
得分: 1
SET GLOBAL改变了全局值，但不会改变您_当前会话的_值。您当前的会话在会话开始时从全局设置中复制了值。会话不会继承后续的全局设置更改（有少数例外，例如read_only）。
您有两种解决方法：
-
使用SET SESSION而不是SET GLOBAL。
-
关闭并重新打开您的连接，这将开始一个新的会话并复制当前的全局值。
英文:
SET GLOBAL changes the global value but does not change your current session's value. Your current session copied values from the global settings at the time the session started. Sessions don't inherit subsequent global setting changes (with a few exceptions, e.g. read_only).
You have two alternatives to fix this:
-
Use SET SESSION instead of SET GLOBAL.
-
Close and reopen your connection, which will start a new session and copy the current global values.
答案2
得分: 0
感谢您的回答。我很惊讶地发现，在mysql提示符下运行"SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED"可以完美运行，但在Drupal设置的php中却不行。原因是文件顶部存在两个数据库数组块，第一个包含"init_commands isolation_level..."，而第二个块中缺少"init_commands"（并且愚蠢地覆盖了先前的设置）。
英文:
Thanks for your answer. I was surprised that running SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED worked perfectly from the mysql prompt but not from the Drupal settings php.
The reason was the presence of 2 blocks of database array, a first one at the top of the file containing the init_commands isolation_level...`and a second block where the init_commands was missing. (and silly overwriting the previous setting)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论