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
https://dev.mysql.com/doc/refman/5.7/en/innodb-read-only-instance.html

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
1.2.11
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
instructions
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
'/var/db/mysql/ibtmp1'
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
STORAGE ENGINE failed.
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) {
                 return(srv_init_abort(err));
         }

in storage/innobase/srv/srv0start.cc which is not wrapped with a if
(!srv_read_only_mode)

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.

https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html

https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html

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?

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

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

Yours,
--
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