Bug #87697 innodb_read_only results in the server being unable to start
Submitted: 7 Sep 2017 16:33 Modified: 26 Sep 2017 15:17
Reporter: Josh Paetzel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:5.7.19 OS:CentOS (7.x)
Assigned to: CPU Architecture:Any
Tags: innodb_read_only

[7 Sep 2017 16:33] Josh Paetzel
Description:
I am following the instructions in https://dev.mysql.com/doc/refman/5.7/en/innodb-read-only-instance.html

The MySQL data directory is mounted read only.

I have the following my.cnf:

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /mysql
log-error       = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_change_buffering = none
innodb_read_only = 1
event_scheduler = disabled

This results in the following:

2017-09-07T16:17:39.646238Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-09-07T16:17:39.674042Z mysqld_safe Starting mysqld daemon with databases from /mysql
2017-09-07T16:17:39.843866Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see docume$
tation for more details).
2017-09-07T16:17:39.844235Z 0 [Warning] Can't create test file /mysql/e88a28e81b83.lower-test
2017-09-07T16:17:39.844288Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.19) starting as process 20998 ...
2017-09-07T16:17:39.846787Z 0 [Warning] Can't create test file /mysql/e88a28e81b83.lower-test
2017-09-07T16:17:39.846798Z 0 [Warning] Can't create test file /mysql/e88a28e81b83.lower-test
2017-09-07T16:17:39.848186Z 0 [Note] InnoDB: Started in read only mode
2017-09-07T16:17:39.848207Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-09-07T16:17:39.848212Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-09-07T16:17:39.848215Z 0 [Note] InnoDB: Uses event mutexes
2017-09-07T16:17:39.848218Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-09-07T16:17:39.848221Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-09-07T16:17:39.848236Z 0 [Note] InnoDB: Using Linux native AIO
2017-09-07T16:17:39.848501Z 0 [Note] InnoDB: Number of pools: 1
2017-09-07T16:17:39.848614Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-09-07T16:17:39.848620Z 0 [Note] InnoDB: Disabling background log and ibuf IO write threads.
2017-09-07T16:17:39.850168Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M                                  [0/1908]
2017-09-07T16:17:39.858026Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-09-07T16:17:39.869279Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriorit
y().
2017-09-07T16:17:39.883060Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-09-07T16:17:39.894723Z 0 [ERROR] InnoDB: Operating system error number 30 in a file operation.
2017-09-07T16:17:39.894750Z 0 [ERROR] InnoDB: Error number 30 means 'Read-only file system'
2017-09-07T16:17:39.894755Z 0 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-code
s.html
2017-09-07T16:17:39.894761Z 0 [ERROR] InnoDB: File ./ibtmp1: 'delete' returned OS error 130.
2017-09-07T16:17:39.894768Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-09-07T16:17:39.894785Z 0 [ERROR] InnoDB: Operating system error number 30 in a file operation.
2017-09-07T16:17:39.894790Z 0 [ERROR] InnoDB: Error number 30 means 'Read-only file system'
2017-09-07T16:17:39.894793Z 0 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-code
s.html
2017-09-07T16:17:39.894796Z 0 [ERROR] InnoDB: File ./ibtmp1: 'create' returned OS error 130. Cannot continue operation
2017-09-07T16:17:39.894803Z 0 [ERROR] InnoDB: Cannot continue operation.
2017-09-07T16:17:40.555591Z mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

I've tried setting a writable location for the ibtmp files, however the server complains that option isn't compatible with innodb_read_only

How to repeat:
Start mysql 5.7.19 with the pasted my.cnf and a read only datadir.  I have verified the problem exists when using Centos 7.x and FreeBSD 11.1
[7 Sep 2017 16:35] Miguel Solorzano
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.
[7 Sep 2017 16:39] Josh Paetzel
There's either a doc bug here or a software bug.  When following the documentation the server doesn't start.

I'm all for getting community support if I'm not following the documentation, or otherwise "doing it wrong", but I don't see any evidence of that here.
[7 Sep 2017 17:28] Scott Noyes
This is a doc bug. https://dev.mysql.com/doc/refman/5.7/en/innodb-read-only-instance.html should specify that you must also set the innodb_temp_data_file_path to somewhere outside the datadir.
[7 Sep 2017 18:33] Josh Paetzel
I can confirm this works.  Thanks Scott.
[26 Sep 2017 15:17] Daniel Price
Posted by developer:
 
The following sections were updated:

Configuring InnoDB for Read-Only Operation
https://dev.mysql.com/doc/refman/5.7/en/innodb-read-only-instance.html

innodb_temp_data_file_path:
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

Thank you for the bug report.