| Bug #117337 | Transaction Commit Allowed on Blackhole Table After read_only=1 is Enabled | ||
|---|---|---|---|
| Submitted: | 30 Jan 12:09 | Modified: | 31 Jan 13:57 |
| Reporter: | Aftab Khan | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Storage Engines | Severity: | S2 (Serious) |
| Version: | 8.0.34-26, 8.0.41 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | blackhole, read_only | ||
[30 Jan 12:09]
Aftab Khan
[30 Jan 13:00]
MySQL Verification Team
Hi Mr. Khan, Thank you for your bug report. However, this storage engine is no longer supported. You can take a look at the category menu and you will see that Blackhole storage engine is no longer listed. Unsupported.
[30 Jan 15:50]
Tsubasa Tanaka
> However, this storage engine is no longer supported. It's my first time to hear it. Is it official opoinion? Therefore, you have to update Reference Manual and nutshell -- BLACKHE Storage Engine is deprecated and already unsupported. https://dev.mysql.com/doc/refman/8.4/en/blackhole-storage-engine.html > You can take a look at the category menu and you will see that Blackhole storage engine is no longer listed. I never found such a category from 2012. Was it really exsited? https://bugs.mysql.com/search.php?cmd=display&status[]=All&severity=all&search_for=%2Bblac...
[31 Jan 7:55]
MySQL Verification Team
Hello Tanaka San, Agree with you, BLACKHOLE Storage Engine is supported. Thank you. Hello Aftab, Thank you for the report and feedback. I quickly tried to reproduce and confirmed that blackhole SE honours the read_only setting. Quoting from our manual "If you change a global system variable, the value is remembered and used to initialize the session value for new sessions until you change the variable to a different value or the server exits. The change is visible to any client that accesses the global value. However, the change affects the corresponding session value only for clients that connect after the change. The global variable change does not affect the session value for any current client sessions (not even the session within which the global value change occurs)." =================== -- release/opt BugNumber=117337 rm -rf $BugNumber/ bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/$BugNumber --log-error-verbosity=3 bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/$BugNumber --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/$BugNumber/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 2>&1 & -- bin/mysql -uroot -S/tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.41 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE DATABASE foo; Query OK, 1 row affected (0.01 sec) mysql> USE foo; Database changed mysql> CREATE TABLE t1 (a INT, b INT) ENGINE=BLACKHOLE; Query OK, 0 rows affected (0.01 sec) mysql> CREATE USER 'test_user'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON foo.* TO 'test_user'@'%'; Query OK, 0 rows affected (0.01 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) -- connect using 'test_user'@'%' - first session bin/mysql -utest_user --prompt='Session1>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.41 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. Session1>use foo Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed Session1>select @@global.read_only, @@read_only; +--------------------+-------------+ | @@global.read_only | @@read_only | +--------------------+-------------+ | 0 | 0 | +--------------------+-------------+ 1 row in set (0.00 sec) Session1>-- Start a Transaction and Insert Data Session1>BEGIN; Query OK, 0 rows affected (0.00 sec) Session1>INSERT INTO t1 VALUES (9999, 3); Query OK, 1 row affected (0.00 sec) Session1>INSERT INTO t1 VALUES (9998, 3); Query OK, 1 row affected (0.00 sec) Session1>select @@global.read_only, @@read_only; +--------------------+-------------+ | @@global.read_only | @@read_only | +--------------------+-------------+ | 1 | 1 | +--------------------+-------------+ 1 row in set (0.00 sec) -- Second session set read_only bin/mysql -uroot -S/tmp/mysql.sock --prompt='SecondSession>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 8.0.41 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. SecondSession>SET GLOBAL read_only = 1; Query OK, 0 rows affected (0.01 sec) SecondSession>SELECT @@global.read_only; +--------------------+ | @@global.read_only | +--------------------+ | 1 | +--------------------+ 1 row in set (0.00 sec) SecondSession> -- Commit the transaction in the first session Session1>COMMIT; Query OK, 0 rows affected (0.00 sec) Session1> .
[31 Jan 7:56]
MySQL Verification Team
- Continued #######. -- Let me repeat once and confirm this bin/mysql -uroot -S/tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.41 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE DATABASE foo; Query OK, 1 row affected (0.01 sec) mysql> USE foo; Database changed mysql> CREATE TABLE t1 (a INT, b INT) ENGINE=BLACKHOLE; Query OK, 0 rows affected (0.01 sec) mysql> CREATE USER 'test_user'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON foo.* TO 'test_user'@'%'; Query OK, 0 rows affected (0.01 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> \q Bye -- connect using 'test_user'@'%' - first session bin/mysql -utest_user --prompt='Session1>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.41 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. Session1>select @@global.read_only, @@read_only; +--------------------+-------------+ | @@global.read_only | @@read_only | +--------------------+-------------+ | 0 | 0 | +--------------------+-------------+ 1 row in set (0.00 sec) Session1>use foo Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed Session1>BEGIN; Query OK, 0 rows affected (0.00 sec) Session1>INSERT INTO t1 VALUES (9999, 3); Query OK, 1 row affected (0.00 sec) Session1>INSERT INTO t1 VALUES (9998, 3); Query OK, 1 row affected (0.00 sec) Session1> -- Second session bin/mysql -uroot -S/tmp/mysql.sock --prompt='SecondSession>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.41 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. SecondSession>SET GLOBAL read_only = 1; Query OK, 0 rows affected (0.00 sec) SecondSession>SELECT @@global.read_only; +--------------------+ | @@global.read_only | +--------------------+ | 1 | +--------------------+ 1 row in set (0.00 sec) SecondSession> -- connect using 'test_user'@'%' - first session and commit but reconnect to see how it behaves bin/mysql -utest_user --prompt='Session1>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.41 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. Session1>select @@global.read_only, @@read_only; +--------------------+-------------+ | @@global.read_only | @@read_only | +--------------------+-------------+ | 0 | 0 | +--------------------+-------------+ 1 row in set (0.00 sec) Session1>use foo Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed Session1>BEGIN; Query OK, 0 rows affected (0.00 sec) Session1>INSERT INTO t1 VALUES (9999, 3); Query OK, 1 row affected (0.00 sec) Session1>INSERT INTO t1 VALUES (9998, 3); Query OK, 1 row affected (0.00 sec) Session1>\r Connection id: 10 Current database: foo Session1>BEGIN; Query OK, 0 rows affected (0.00 sec) Session1>INSERT INTO t1 VALUES (9999, 3); ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement Session1>INSERT INTO t1 VALUES (9998, 3); ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement Session1> =================== Also, I see this : ###Verify Read-Only Mode is Disabled ```sql mysql> select @@global.read_only, @@read_only; +--------------------+-------------+ | @@global.read_only | @@read_only | +--------------------+-------------+ | 1 | 1 | +--------------------+-------------+ 1 row in set (0.00 sec) ^^ Maybe you mistakenly include above in the first session while confirming read_only? I assume it should be listing disabled. Or am I missing anything here? Pls let me know. regards, Umesh
[31 Jan 12:19]
Aftab Khan
Thanks for recognising that Blackhole tables are still supported. The following steps demonstrate that, unlike the InnoDB storage engine, Blackhole tables do not honour the read_only setting for in-flight transactions: -- Connect using 'test_user'@'%' - First session mysql(SESSION #1) > select @@global.read_only, @@read_only; +--------------------+-------------+ | @@global.read_only | @@read_only | +--------------------+-------------+ | 0 | 0 | +--------------------+-------------+ 1 row in set (0.00 sec) mysql(SESSION #1) > CREATE DATABASE foo; mysql(SESSION #1) > USE foo; mysql(SESSION #1) > CREATE TABLE t1 (a INT, b INT) ENGINE=BLACKHOLE; mysql(SESSION #1) > BEGIN; Query OK, 0 rows affected (0.00 sec) mysql(SESSION #1) > insert into t1 values (9998,99); Query OK, 1 row affected (0.00 sec) # From Session#2 enable read_only -- connect using 'root'@'%' - Second session mysql(SESSION #2) > set global read_only=1; Query OK, 0 rows affected (0.00 sec) ----------------------------------------------------------------------------------------- #From first session check read_only status (now enabled) mysql(SESSION #1) > select @@global.read_only, @@read_only; +--------------------+-------------+ | @@global.read_only | @@read_only | +--------------------+-------------+ | 1 | 1 | +--------------------+-------------+ 1 row in set (0.00 sec) mysql(SESSION #1) > commit; Query OK, 0 rows affected (0.00 sec) # ( This ends in-flight transaction, subsequent inserts will fail e.g.) mysql(SESSION #1) > insert into t1 values (9998,99); ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
[31 Jan 12:21]
MySQL Verification Team
Thank you for your feedback. I'll get back to you if anything further needs to investigate this further. regards, Umesh
[31 Jan 12:38]
Aftab Khan
I have repeated the steps using mysql version 8.40 too, e.g. % mysql -u test_user -p -h 127.0.0.1 -P 3306 --prompt='mysql-session-1 >' Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17 Server version: 8.0.40 Homebrew Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql-session-1 >CREATE DATABASE foo; Query OK, 1 row affected (0.00 sec) mysql-session-1 >USE foo; Database changed mysql-session-1 >CREATE TABLE t1 (a INT, b INT) ENGINE=BLACKHOLE; Query OK, 0 rows affected (0.00 sec) mysql-session-1 >SHOW GRANTS; +----------------------------------------------------+ | Grants for test_user@% | +----------------------------------------------------+ | GRANT USAGE ON *.* TO `test_user`@`%` | | GRANT ALL PRIVILEGES ON `foo`.* TO `test_user`@`%` | +----------------------------------------------------+ 2 rows in set (0.01 sec) mysql-session-1 >select @@global.read_only, @@read_only; +--------------------+-------------+ | @@global.read_only | @@read_only | +--------------------+-------------+ | 0 | 0 | +--------------------+-------------+ 1 row in set (0.00 sec) mysql-session-1 >BEGIN; Query OK, 0 rows affected (0.00 sec) mysql-session-1 >INSERT INTO t1 VALUES (9999, 3); Query OK, 1 row affected (0.00 sec) ------------------------------------------------------------------------------- From second session enable read_only % mysql -u root -p -h 127.0.0.1 -P 3306 --prompt='mysql-session-2 ' Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 8.0.40 Homebrew Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql-session-2 select @@global.read_only; +--------------------+ | @@global.read_only | +--------------------+ | 0 | +--------------------+ 1 row in set (0.00 sec) mysql-session-2 set global read_only=1; Query OK, 0 rows affected (0.00 sec) mysql-session-2 select @@global.read_only; +--------------------+ | @@global.read_only | +--------------------+ | 1 | +--------------------+ 1 row in set (0.00 sec) ----------------------------------------------------------------------- Back to session 1 mysql-session-1 >COMMIT; Query OK, 0 rows affected (0.01 sec) mysql-session-1 >INSERT INTO t1 VALUES (9999, 3); ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement mysql-session-1 >
[31 Jan 13:57]
MySQL Verification Team
Thank you for the feedback. regards, Umesh
[31 Jan 18:32]
Perry Harrington
Here is my analysis as part of our internal conversation: I believe I've found the root cause of why BH doesn't abide by the rules. The check for read_only is done in 2 places: executing a statement and committing a transaction. In executing a statement the flag for "raise error if readonly" is set to false, in ha_commit_trans it is set to true. When committing an Innodb transaction the first check happens and the second check raises the error. When using blackhole there is only the first check because Blackhole doesn't implement a transaction handler. The blackhole engine does not implement the commit slot for handlers and thus it ends up be a NULL operation, so commits are not rolled back or blocked when the server is set to readonly. The fix is to implement the commit handler and put in a check_readonly with an argument of true. That would prevent blackhole from polluting GTID sets. To further clarify the last sentence, GTIDs are allocated at commit time, so if a BH transaction is held open when read_only is toggled to 1, when the transaction commits it generates an errant GTID set due to the GTID being allocated *after* read_only is set to 1. The implications of this are that replication failover for ReplicaSet can result in errant GTIDs on the previous source. Possible workarounds include killing open transactions (and thus forcing a rollback) when read_only is set to 1, maintaining circular replication for a short period of time (but for how long is only determinable if there are ACTIVE transactions still pending). But the core issue is that BLACKHOLE violates ACID and it needs to respect read_only and super_read_only.
