Mysql will not start using ZFS pool for data (native ZFS on Ubuntu Linux)

Asked by Phil Marsh on 2014-06-01

OS: Ubuntu 14.04 kernel 3.13.0-27-generic #50-Ubuntu
MySQL version 5.5
MySQL will not start when data transferred to a ZFS pool. It fails with the following from the /var/log/mysql/error.log

140531 19:49:19 mysqld_safe Starting mysqld daemon with databases from /data/mysql/mysql
140531 19:49:19 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
140531 19:49:19 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
140531 19:49:19 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Table 'plugin' is read only
140531 19:49:19 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
140531 19:49:19 InnoDB: The InnoDB memory heap is disabled
140531 19:49:19 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140531 19:49:19 InnoDB: Compressed tables use zlib 1.2.8
140531 19:49:19 InnoDB: Using Linux native AIO
140531 19:49:19 InnoDB: Initializing buffer pool, size = 128.0M
140531 19:49:19 InnoDB: Completed initialization of buffer pool
140531 19:49:19 InnoDB: highest supported file format is Barracuda.
140531 19:49:19 InnoDB: Operating system error number 22 in a file operation.
InnoDB: Error number 22 means 'Invalid argument'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
InnoDB: File name ./ib_logfile0
InnoDB: File operation call: 'aio write'.
InnoDB: Cannot continue operation.
140531 19:49:19 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

How to repeat:
I had followed the procedure given in http://sharadchhetri.com/2013/05/18/how-to-change-mysql-default-data-directory-in-ubuntu/

to change my default MySQL data directory to a ZFS pool.

My my.conf file is:
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
#socket = /data/mysql/mysqld.sock
#innodb_use_native_aio = 0
# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
#socket = /data/mysql/mysqld.sock
nice = 0

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
#socket = /data/mysql/mysqld.sock
port = 3306
basedir = /usr
#datadir = /var/lib/mysql
datadir = /data/mysql/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

#####################################################

And my armorapp config file is: (usr.sbin.mysqld)

# vim:syntax=apparmor
# Last Modified: Tue Jun 19 17:37:30 2007
#include <tunables/global>

/usr/sbin/mysqld {
  #include <abstractions/base>
  #include <abstractions/nameservice>
  #include <abstractions/user-tmp>
  #include <abstractions/mysql>
  #include <abstractions/winbind>

  capability dac_override,
  capability sys_resource,
  capability setgid,
  capability setuid,

  network tcp,

  /etc/hosts.allow r,
  /etc/hosts.deny r,

  /etc/mysql/*.pem r,
  /etc/mysql/conf.d/ r,
  /etc/mysql/conf.d/* r,
  /etc/mysql/*.cnf r,
  /usr/lib/mysql/plugin/ r,
  /usr/lib/mysql/plugin/*.so* mr,
  /usr/sbin/mysqld mr,
  /usr/share/mysql/** r,
  /var/log/mysql.log rw,
  /var/log/mysql.err rw,
  ####
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
  /data/mysql/mysql/ r,
  /data/mysql/mysql/** rwk,
  ######
  /var/log/mysql/ r,
  /var/log/mysql/* rw,
  /var/run/mysqld/mysqld.pid rw,
  /var/run/mysqld/mysqld.sock w,
  /run/mysqld/mysqld.pid rw,
  /run/mysqld/mysqld.sock w,

  /sys/devices/system/cpu/ r,

  # Site-specific additions and overrides. See local/README for details.
  #include <local/usr.sbin.mysqld>
}
########################################################################

Note that my new MySQL data directory is /data/mysql/mysql which is in a ZFS pool (native ZFS on Linux)
Also note that I tried
innodb_use_native_aio = 0
(was uncommented when tried) in my my.cnf file and it had no effect see

140531 19:49:19 InnoDB: Using Linux native AIO

The above worked when /data/mysql/mysql was NOT from a ZFS pool but rather an ext4 formatted SSD.
I've tried hours of searching to no avail! I want MySQL to store its data on my RAIDZ2 zpool rather than my ssd
Thanks so much for any help!
Phil

Suggested fix:
Must find a way to disable Linux native AIO

Question information

Language:
English Edit question
Status:
Solved
For:
Ubuntu Edit question
Assignee:
No assignee Edit question
Solved by:
Phil Marsh
Solved:
2014-06-26
Last query:
2014-06-26
Last reply:
2014-06-02
Thomas Krüger (thkrueger) said : #1

You should try to uncomment the

#innodb_use_native_aio = 0

line.

Phil Marsh (marsh-pf) said : #2

Hi Thomas,
Thanks very much for your response.
I apologize. I was unclear. I had tried the above with the innodb_use_native_aio=0 active (uncommented) in the my.cnf and the above error messages were the result and Linux native AIO was apparently not disabled when the above variable innodb_use_native_aio=0 was set in my.cnf.
Moreover, I had tried invoking MySQL server directly from the command line as:
# mysqld --innodb_use_native_aio=0
This did, in fact, apparently did turn off native Linux AIO (as reported from the error.log file) but still, mysqld failed to start with more error messages.
What's strange to me is that I did demonstrate successfully changing the mysql data file location, via the above methods, but only on my ext4 SSD drive and NOT on the zfs pool as the new location. How would MySQL know about or care about the underlying filesystem?
Thanks,
Phil

Phil Marsh (marsh-pf) said : #3

Hi again,
I've verified again that the mysql data move DOES work on ext4 filesystem but DOES NOT work on ZFS on Linux (native) with all else being equal. Again my OS is Ubuntu 14.04 64 bit.
Also, I am noting that ZFS on Linux native does not yet support AIO from the Google posts. AND apparently mysql does not support the mysqld --innodb_use_native_aio=0 setting in my.cnf as I saw an error message to this effect.

When I run:
# mysqld --innodb_use_native_aio=0
with a zfs pool mysql data location, I get the following error messages from /var/log/mysql/error.log

140601 14:07:14 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'plugin' is read only
140601 14:07:14 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
140601 14:07:14 InnoDB: The InnoDB memory heap is disabled
140601 14:07:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140601 14:07:14 InnoDB: Compressed tables use zlib 1.2.8
140601 14:07:14 InnoDB: Initializing buffer pool, size = 128.0M
140601 14:07:14 InnoDB: Completed initialization of buffer pool
140601 14:07:14 InnoDB: highest supported file format is Barracuda.
140601 14:07:15 InnoDB: Waiting for the background threads to start
140601 14:07:16 InnoDB: 5.5.37 started; log sequence number 1595685
140601 14:07:16 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
140601 14:07:16 [Note] - '127.0.0.1' resolves to '127.0.0.1';
140601 14:07:16 [Note] Server socket created on IP: '127.0.0.1'.
140601 14:07:16 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'host' is read only

So apparently the --innodb_use_native_aio=0 option worked here but still no joy. I'm out of ideas. Any ideas?
Thanks again,
Phil

Thomas Krüger (thkrueger) said : #4

This looks like a permission issue. Can you check the permission of the table files?

Phil Marsh (marsh-pf) said : #5

Hi Thomas,
I tried exactly the same configuration as above with the same table and data files and same directory name for
1. The directory mounted on the ext4 filesystem (SSD drive)
2. The same directory name and files as 1. mounted on the ZFS pool.
Case 1. worked i.e. mysql started normally but case 2. resulted in mysql always failing as discussed previously.
I think that at least part of the problem is that native ZFS on Linux does not yet support AIO but mysql still appears to fail even when AIO is not native Linux.
Thanks again for your advice and help,
Phil

Phil Marsh (marsh-pf) said : #6

Hi, I still cannot figure out why MySQL did not start on a ZFS pool. The permissions are correct because the same mountpoint and data files work (with the same permissions) on a ext4 mountpoint.
Thanks,
Phil

Phil Marsh (marsh-pf) said : #7

Hi,
I finally found the problem! Yes, mysql can be made to work on ZFS on Linux after all!
From my experience, I think it turns out that the location of the directive:
innodb_use_native_aio=0
is critical to the disable Linux native AIO as needed for the mysql database to use the ZFS filesystem for its data.
Here is a copy of my /etc/mysql/my.cnf file: (this is Ubuntu 14.04)
note carefully the location of the innodb_use_native_aio=0 directive.
i.e. just prior to the directives defining the directories used by MySQL.

########################################################################
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
#
# * Basic Settings
#
innodb_use_native_aio=0
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
#datadir = /var/lib/mysql
datadir = /mysqldata
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

###################################################################################

But we're not done yet. You then must still modify the apparmor file: /etc/apparmor.d/
Or you can use
$ sudo aa-complain /usr/sbin/mysqld
to stop apparmor from preventing MySQL from running after the above changes
Phil