Web lists-archives.com

Re: innodb_read_only issues

Hello Josh,

On 9/6/2017 11:01 PM, Josh Paetzel wrote:
I've followed the instructions at

Which starts with:

14.6.2 Configuring InnoDB for Read-Only Operation

You can now query InnoDB tables where the MySQL data directory is on
read-only media, by enabling the --innodb-read-only configuration option
at server startup.

Exactly what I want to do.

However the server bails out trying to create files.

2017-09-07T02:12:33.688368Z 0 [Note] InnoDB: Started in read only mode
2017-09-07T02:12:33.688405Z 0 [Note] InnoDB: Mutexes and rw_locks use
GCC atomic builtins
2017-09-07T02:12:33.688410Z 0 [Note] InnoDB: Uses event mutexes
2017-09-07T02:12:33.688415Z 0 [Note] InnoDB: GCC builtin
__atomic_thread_fence() is used for memory barrier
2017-09-07T02:12:33.688419Z 0 [Note] InnoDB: Compressed tables use zlib
2017-09-07T02:12:33.688695Z 0 [Note] InnoDB: Number of pools: 1
2017-09-07T02:12:33.688803Z 0 [Note] InnoDB: Using CPU crc32
2017-09-07T02:12:33.688810Z 0 [Note] InnoDB: Disabling background log
and ibuf IO write threads.
2017-09-07T02:12:33.690040Z 0 [Note] InnoDB: Initializing buffer pool,
total size = 1G, instances = 8, chunk size = 128M
2017-09-07T02:12:33.809821Z 0 [Note] InnoDB: Completed initialization of
buffer pool
2017-09-07T02:12:33.836689Z 0 [Note] InnoDB: Highest supported file
format is Barracuda.
2017-09-07T02:12:33.852709Z 0 [Note] InnoDB: Creating shared tablespace
for temporary tables
2017-09-07T02:12:33.852757Z 0 [ERROR] InnoDB: Operating system error
number 13 in a file operation.
2017-09-07T02:12:33.852764Z 0 [ERROR] InnoDB: The error means mysqld
does not have the access rights to the directory.
2017-09-07T02:12:33.852769Z 0 [ERROR] InnoDB: Operating system error
number 13 in a file operation.
2017-09-07T02:12:33.852774Z 0 [ERROR] InnoDB: The error means mysqld
does not have the access rights to the directory.
2017-09-07T02:12:33.852779Z 0 [ERROR] InnoDB: Cannot open datafile
2017-09-07T02:12:33.852784Z 0 [ERROR] InnoDB: Unable to create the
shared innodb_temporary
2017-09-07T02:12:33.852789Z 0 [ERROR] InnoDB: Plugin initialization
aborted with error Cannot open a file
2017-09-07T02:12:34.067298Z 0 [ERROR] Plugin 'InnoDB' init function
returned error.
2017-09-07T02:12:34.067315Z 0 [ERROR] Plugin 'InnoDB' registration as a
2017-09-07T02:12:34.067322Z 0 [ERROR] Failed to initialize plugins.
2017-09-07T02:12:34.067327Z 0 [ERROR] Aborting

For what it's worth /var/db/mysql is chmod 550, chown mysql:mysql, so
the mysql user has read access to the directory but can't create files
in it.

I've examined the source and found:

         /* Open temp-tablespace and keep it open until shutdown. */

         err = srv_open_tmp_tablespace(create_new_db, &srv_tmp_space);

         if (err != DB_SUCCESS) {

in storage/innobase/srv/srv0start.cc which is not wrapped with a if

Is this a bug I am hitting or am I holding it wrong?

What appears to be missing on that page is a discussion what to do with the "temporary tablespace" used to hold intrinsic (internal) temporary tables created by different types of queries you could execute.



If you point the setting --innodb-temp-data-file-path to a location that is writeable (and accessible to the user that your mysqld is running as), does that get you past this problem?


Think of it as allocating "scratch space" for this mysqld to "think" while it processes your queries against the data.

Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ for details.

MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql