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] MySQL Verification Team
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] MySQL Verification Team
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.043 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.