Bug #96128 Doc: documentation is inaccurate when InnoDB starts with innodb_read_only
Submitted: 8 Jul 2019 15:36 Modified: 27 Sep 2019 10:22
Reporter: Calvin Sun Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0.16 OS:Any
Assigned to: CPU Architecture:Any

[8 Jul 2019 15:36] Calvin Sun
Description:
According to docs, https://dev.mysql.com/doc/refman/8.0/en/innodb-read-only-instance.html, “All background threads other than I/O read threads are turned off.” This is not true. IO write threads are still on.

Other than IO write threads, additional bkg threads are created too.

How to repeat:
Start mysqld with --innodb-read-only.

mysql> select @@innodb_read_only;
+--------------------+
| @@innodb_read_only |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

mysql> select THREAD_ID, NAME,TYPE,RESOURCE_GROUP from performance_schema.threads where name like "thread/innodb%" order by name;
+-----------+---------------------------------------------+------------+----------------+
| THREAD_ID | NAME                                        | TYPE       | RESOURCE_GROUP |
+-----------+---------------------------------------------+------------+----------------+
|        12 | thread/innodb/buf_resize_thread             | BACKGROUND | SYS_default    |
|         3 | thread/innodb/io_read_thread                | BACKGROUND | SYS_default    |
|         4 | thread/innodb/io_read_thread                | BACKGROUND | SYS_default    |
|         5 | thread/innodb/io_read_thread                | BACKGROUND | SYS_default    |
|         6 | thread/innodb/io_read_thread                | BACKGROUND | SYS_default    |
|         7 | thread/innodb/io_write_thread               | BACKGROUND | SYS_default    |
|         8 | thread/innodb/io_write_thread               | BACKGROUND | SYS_default    |
|         9 | thread/innodb/io_write_thread               | BACKGROUND | SYS_default    |
|        10 | thread/innodb/io_write_thread               | BACKGROUND | SYS_default    |
|        11 | thread/innodb/page_flush_coordinator_thread | BACKGROUND | SYS_default    |
+-----------+---------------------------------------------+------------+----------------+
10 rows in set (0.00 sec)

Suggested fix:
Turn off IO write threads; also update docs.
[9 Jul 2019 2:25] zhai weixiang
I think that's because user clients or optimizer may create innodb temp table even with innodb_read_only on, and that generates dirty page, which need IO write threads to persist them on disk
[9 Jul 2019 2:57] Calvin Sun
Also noticed: 

"If the MySQL server is started with --innodb-read-only but the data directory is still on writeable media, the root user can still perform DCL operations such as GRANT and REVOKE."

So, the doc "All background threads other than I/O read threads are turned off." is incorrect.

I also suggest to enable "Saving and Restoring the Buffer Pool" feature even InnoDB starts as read only. Right now, buf_dump_thread is turned off.
[9 Jul 2019 3:00] Calvin Sun
Change the bug title & category.
[9 Jul 2019 12:20] MySQL Verification Team
Hi Cheshung,

Another great report from you !!!!!

Verified as reported.
[9 Jul 2019 13:38] Calvin Sun
Sinisa, thanks for the quick verification!
[27 Sep 2019 10:22] Daniel Price
Posted by developer:
 
The referenced documentation has been updated.
https://dev.mysql.com/doc/refman/8.0/en/innodb-read-only-instance.html

Thank you for the bug report.
[27 Sep 2019 12:40] MySQL Verification Team
Thank you, Daniel ....