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

You should try to uncomment the

#innodb_use_native_aio = 0

line.

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

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

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

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

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

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