Bug #98091 InnoDB does not initialize raw disk partitions
Submitted: 29 Dec 2019 15:01 Modified: 20 Oct 2020 5:54
Reporter: Saverio Miroddi Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.18, 5.7.29, 8.0.19 OS:Linux
Assigned to: CPU Architecture:x86

[29 Dec 2019 15:01] Saverio Miroddi
Description:
I'm following the instructions on https://dev.mysql.com/doc/refman/8.0/en/innodb-system-tablespace.html#innodb-raw-devices, in order to setup the InnoDB system tablespace on a raw disk partition, however, it seems that InnoDB does *not* perform the initialization of the partition.

How to repeat:
# Ensure that only one configuration file is used.
#
root:$ mysqld --help --verbose | grep cnf | head -n 1 | tee /dev/stderr | perl -pe 's/~/$ENV{HOME}/' | xargs ls -l 2> /dev/null
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
-rw-r--r-- 1 root root 808 Dec 29 12:48 /root/.my.cnf

root:$ parted /dev/sda print
# ...details...
Number  Start   End     Size    File system  Name  Flags
# ...other details...
 4      928GB   992GB   64,0GB
 5      992GB   1002GB  10,0GB

# Load the raw module, and bind the raw device.
#
root:$ modprobe raw
root:$ raw /dev/raw/raw1 /dev/sda4
/dev/raw/raw1:  bound to major 8, minor 4

# Verify the following:
#
# > When you use a raw disk partition, ensure that the user ID that runs the MySQL server has read and write privileges for that partition.
#
root:$ dd if=/dev/zero status=progress of=/dev/raw/raw1 bs=1M count=1
1+0 records in
1+0 records out
1048576 bytes (1,0 MB, 1,0 MiB) copied, 0,00247748 s, 423 MB/s

# Initialize the mysql data; now raw partition initialization yet.
#
root:$ cat > /root/.my.cnf << 'CONF'
[mysqld]

datadir                   = /media/disk_b/mysql_data
CONF

root:$ mysqld --user="$(whoami)" --initialize-insecure
2019-12-29T14:37:43.438865Z 0 [System] [MY-013169] [Server] /path/to/mysql-8.0.18-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.18) initializing of server in progress as process 5403
2019-12-29T14:37:45.956817Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

# Now write try to initialize the raw partition.
#
# > When you create a new data file, specify the keyword newraw immediately after the data file size for the innodb_data_file_path option
# > [mysqld]
# > innodb_data_home_dir=
# > innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw
#
root:$ cat > /root/.my.cnf << 'CONF'
[mysqld]

datadir                   = /media/disk_b/mysql_data
innodb_data_home_dir      =
innodb_data_file_path     = /dev/raw/raw1:2Gnewraw
CONF

root:$ mysqld --user="$(whoami)"
2019-12-29T14:38:53.780113Z 0 [System] [MY-010116] [Server] /path/to/mysql-8.0.18-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.18) starting as process 5470
2019-12-29T14:38:53.804420Z 1 [ERROR] [MY-012224] [InnoDB] Header page consists of zero bytes in datafile: /dev/raw/raw1, Space ID:0, Flags: 0. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2019-12-29T14:38:53.804508Z 1 [ERROR] [MY-012237] [InnoDB] Corrupted page [page id: space=0, page number=0] of datafile '/dev/raw/raw1' could not be found in the doublewrite buffer.
2019-12-29T14:38:53.804576Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Data structure corruption.
# ...details...

# Double check that the innodb_data_file_path value is parsed correctly, by feeding a wrong value.
#
root:$ cat > /root/.my.cnf << 'CONF'
[mysqld]

datadir                   = /media/disk_b/mysql_data
innodb_data_home_dir      =
innodb_data_file_path     = /dev/raw/raw1:2Gnewrawx
CONF

root:$ mysqld --user="$(whoami)"
2019-12-29T14:47:59.798128Z 0 [System] [MY-010116] [Server] /path/to/mysql-8.0.18-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.18) starting as process 7315
2019-12-29T14:47:59.803963Z 0 [ERROR] [MY-012261] [InnoDB] File Path Specification: '/dev/raw/raw1:2Gnewrawx' has unrecognized characters after '/dev/raw/raw1:2Gnewraw'
2019-12-29T14:47:59.803991Z 0 [ERROR] [MY-012370] [InnoDB] Unable to parse innodb_data_file_path=/dev/raw/raw1:2Gnewrawx
# ...details...

# Try to initialize both the mysql data and the partition in one pass
#
root:$ rm -rf /media/disk_b/mysql_data
root:$ cat > /root/.my.cnf << 'CONF'
[mysqld]

datadir                   = /media/disk_b/mysql_data
innodb_data_home_dir      =
innodb_data_file_path     = /dev/raw/raw1:2Gnewraw
CONF
root:$ mysqld --user="$(whoami)" --initialize-insecure
2019-12-29T14:57:49.819750Z 0 [System] [MY-013169] [Server] /path/to/mysql-8.0.18-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.18) initializing of server in progress as process 8835
2019-12-29T14:57:49.825484Z 1 [ERROR] [MY-012279] [InnoDB] The innodb_system data file '/dev/raw/raw1' was not found but one of the other data files '/dev/raw/raw1' exists.
2019-12-29T14:57:49.825620Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
[31 Jan 2020 13:36] MySQL Verification Team
You can try several different options:

* omit `datadir` in the configuration
* make sure that a device is owned by mysql
* see if secure initialisation works ......
[7 Feb 2020 5:19] MySQL Verification Team
Hello Saverio M,

Thank you for the report and feedback.
Observed this with 5.7.29 and 8.0.19 builds on OL7.

regards,
Umesh
[7 Feb 2020 5:20] MySQL Verification Team
Test results - 8.0.19, 5.7.29

Attachment: 98091.results (application/octet-stream, text), 11.76 KiB.

[9 Feb 2020 10:08] Saverio Miroddi
> Thank you for the report and feedback.
> Observed this with 5.7.29 and 8.0.19 builds on OL7.

Thanks Umesh. I'm fairly loaded right now, so it would have taken me a while to perform further tests.

Of course, if there's anything additional I should test, let me know (it seems not, based on the last comment).
[31 Jul 2020 15:10] Jason Peng
I verified it in mysql 8.0.20
=================================================================
[root@db1 etc]# grep innodb_data my.cnf 
innodb_data_home_dir           = 
innodb_data_file_path          =/dev/ib_data1:102400Mnewraw
#innodb_data_file_path          = /dev/ib_data2:51200Mnewraw
[root@db1 etc]# 
[root@db1 etc]# ls -l /dev/ib_data*
brw-rw---- 1 mysql8 mysql 8, 34 Jul 31 22:52 /dev/ib_data1
brw-rw---- 1 mysql8 mysql 8, 35 Jul 31 22:52 /dev/ib_data2
[root@db1 etc]# 
[root@db1 etc]# cd ..
[root@db1 sql8ha3336]# cd log
[root@db1 log]# cat mysql-error.log 
2020-07-31T22:59:34.711525+08:00 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be
 removed in a future release. Please use binlog_expire_logs_seconds instead.
2020-07-31T22:59:34.711681+08:00 0 [System] [MY-013169] [Server] /usr/local/mysql80/bin/mysqld (mysqld 8.0.20) initializi
ng of server in progress as process 1988
2020-07-31T22:59:34.713797+08:00 0 [Warning] [MY-010122] [Server] One can only use the --user switch if running as root
2020-07-31T22:59:34.720098+08:00 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-07-31T22:59:34.720258+08:00 1 [ERROR] [MY-012279] [InnoDB] The innodb_system data file '/dev/ib_data1' was not found
 but one of the other data files '/dev/ib_data1' exists.
2020-07-31T22:59:34.720453+08:00 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-07-31T22:59:34.720526+08:00 0 [ERROR] [MY-013236] [Server] The designated data directory /kingdee/mysql/sql8ha3336/d
ata/ is unusable. You can remove all files that the server added to it.
2020-07-31T22:59:34.720635+08:00 0 [ERROR] [MY-010119] [Server] Aborting
2020-07-31T22:59:34.721182+08:00 0 [System] [MY-010910] [Server] /usr/local/mysql80/bin/mysqld: Shutdown complete (mysqld
[3 Aug 2020 12:55] MySQL Verification Team
Thank you, Mr. Peng.

I also can't find any ChangeLog entry in 8.0.21 that would indicate it is fixed there .......
[10 Aug 2020 12:31] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.22 release, and here's the proposed changelog
entry from the documentation team:
 
A failure occurred while attempting to initialize the system tablespace
on a raw disk partition. Additionally, a INPLACE DDL operation on the
raw-disk partition tablespace failed with an error instead of switching to
the COPY algorithm.
[10 Aug 2020 12:49] MySQL Verification Team
Thank you, Daniel.