MySQL Bugs: #87697: innodb_read_only results in the server being unable to start

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

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

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.

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.

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.

I can confirm this works.  Thanks Scott.

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.