MySQL Bugs: #96128: Doc: documentation is inaccurate when InnoDB starts with innodb_read

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

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.

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

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.

Change the bug title & category.

Hi Cheshung,

Another great report from you !!!!!

Verified as reported.

Sinisa, thanks for the quick verification!

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.

Thank you, Daniel ....