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