Bug #121775 InnoDB DDL buffers need 4KiB alignment for O_DIRECT on 4Kn storage
Description:
When innodb_disable_sort_file_cache=ON and innodb_tmpdir is on an ext4 filesystem
backed by a device with 4096-byte logical sectors, CREATE INDEX can fail with:
ERROR 1878 (HY000): Temporary file write failure.
The error log reports MY-012639/MY-012640: a DDL temporary-file write at offset 0,
with requested length 4096, failed with errno 22 (EINVAL / Invalid argument).
Both the offset and length are already multiples of 4096.
The DDL temporary-file buffers are allocated with
ut::make_unique_aligned<byte[]>(..., UNIV_SECTOR_SIZE, ...).
UNIV_SECTOR_SIZE is 512. This guarantees only 512-byte memory-address alignment,
whereas the test filesystem requires 4096-byte alignment for direct I/O.
Consequently, failure depends on the address returned by the allocator; it need
not occur on every execution, even on the same filesystem.
An independent O_DIRECT pwrite probe on the same test mount isolated this
requirement, using a length of 4096 and offset of 0 in both cases:
Buffer address modulo 4096 Bytes written errno
0 4096 0
512 -1 22
The original SQL succeeds with innodb_disable_sort_file_cache=OFF on the same
4Kn mount. It also succeeds with the option ON on ordinary ext4 backed by
512-byte logical sectors. A 4096-byte filesystem block size alone does not
establish that the filesystem enforces 4096-byte direct-I/O buffer alignment;
the logical-sector check and the direct-I/O probe are important controls.
Expected behavior:
The index is created successfully and the indexed lookup returns (1, 12467),
including when direct I/O for DDL temporary files requires 4096-byte alignment.
Actual behavior:
CREATE INDEX fails with ERROR 1878, and the server reports EINVAL for a
4096-byte temporary-file write at offset 0.
How to repeat:
1. On Linux, create a disposable 128 MiB disk-image file and a loop device with
4096-byte logical sectors. Format only this newly created image; do not use
an existing disk or partition. The commands below require loop-device and
mount privileges, util-linux, and e2fsprogs. Set MYSQL_OS_USER to the operating
system account used by the dedicated test mysqld instance.
MYSQL_OS_USER=mysql
TEST_ROOT=$(mktemp -d /tmp/mysql-ddl-4kn.XXXXXX)
chmod 755 "$TEST_ROOT"
printf 'Test directory: %s\n' "$TEST_ROOT"
truncate -s 128M "$TEST_ROOT/disk.img"
mkfs.ext4 -q -F -b 4096 "$TEST_ROOT/disk.img"
LOOP_DEVICE=$(sudo losetup --find --show --sector-size 4096 "$TEST_ROOT/disk.img")
mkdir "$TEST_ROOT/mnt"
sudo mount -o nodev,nosuid,noexec "$LOOP_DEVICE" "$TEST_ROOT/mnt"
sudo install -d -o "$MYSQL_OS_USER" -m 700 "$TEST_ROOT/mnt/innodb-tmp"
sudo blockdev --getss "$LOOP_DEVICE"
stat -f -c '%S' "$TEST_ROOT/mnt"
findmnt --target "$TEST_ROOT/mnt"
Both numeric checks must report 4096. The attached direct-I/O probe should
accept a 4096-aligned buffer and reject a buffer whose address is 512 modulo
4096, with length=4096 and offset=0 in both cases.
2. Initialize and start a dedicated MySQL debug test instance. Its data directory
may remain on the ordinary filesystem; only DDL temporary files need the
strict-alignment mount. Add these options to its normal startup command,
expanding TEST_ROOT to the actual directory printed/recorded in step 1:
--innodb-tmpdir="$TEST_ROOT/mnt/innodb-tmp"
--innodb-disable-sort-file-cache=ON
Keep the shell variables from step 1 for cleanup. If using a configuration
file, use the resolved absolute innodb_tmpdir path rather than a shell variable.
3. Connect to the dedicated instance and select a new, empty test database. Run
the following SQL, also provided verbatim in repro.sql:
SELECT VERSION();
SELECT @@global.innodb_disable_sort_file_cache;
CREATE TABLE sbtest1 (
id INT NOT NULL AUTO_INCREMENT,
k INT NOT NULL DEFAULT 0,
c CHAR(120) NOT NULL DEFAULT '',
pad CHAR(60) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=InnoDB;
INSERT INTO sbtest1 SET id=1, k=12467,
c='31451373586-15688153734-79729593694-96509299839-83724898275-86711833539-78981337422-35049690573-51724173961-87474696253',
pad='98996621624-36689827414-04092488557-09587706818-65008859162';
CREATE INDEX k_1 ON sbtest1(k);
SELECT id, k FROM sbtest1 FORCE INDEX(k_1) WHERE k=12467;
DROP TABLE sbtest1;
The recorded run failed at CREATE INDEX with ERROR 1878. Collect the server
error log. If an allocation happens to be 4096-aligned, that run can succeed;
repeat in a clean test database or after restarting the dedicated instance.
A successful single run does not establish that the allocator guarantees the
required alignment. After a failure, sbtest1 may remain in the test database.
4. Repeat with innodb_disable_sort_file_cache=OFF on the same mount. The original
SQL should succeed, as it did in the recorded control. A second control uses
ON with ordinary ext4 backed by 512-byte logical sectors.
5. After stopping the dedicated test server, unmount and detach only the loop
device created in step 1:
sudo umount "$TEST_ROOT/mnt"
sudo losetup --detach "$LOOP_DEVICE"
The disposable image remains under TEST_ROOT and can be removed afterward.
Suggested fix:
Use IO_BLOCK_SIZE (4096), rather than UNIV_SECTOR_SIZE (512), as the requested
alignment for these four DDL temporary-file buffer allocations:
storage/innobase/ddl/ddl0builder.cc Builder::init
storage/innobase/ddl/ddl0file-reader.cc File_reader::prepare
storage/innobase/ddl/ddl0fts.cc FTS::Parser::init
storage/innobase/ddl/ddl0merge.cc Merge_file_sort::sort
Add debug assertions at ddl::pread and ddl::pwrite to check that the buffer
address is aligned to IO_BLOCK_SIZE. This gives debug regression tests a way
to detect invalid buffer alignment even on filesystems which accept 512-byte
direct-I/O buffers.
Extend innodb.create_index_with_disable_sort_file_cache with coverage for the
one-row reproducer and the affected DDL paths, and validate on the strict 4Kn
mount as well as through ordinary MTR runs.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: When innodb_disable_sort_file_cache=ON and innodb_tmpdir is on an ext4 filesystem backed by a device with 4096-byte logical sectors, CREATE INDEX can fail with: ERROR 1878 (HY000): Temporary file write failure. The error log reports MY-012639/MY-012640: a DDL temporary-file write at offset 0, with requested length 4096, failed with errno 22 (EINVAL / Invalid argument). Both the offset and length are already multiples of 4096. The DDL temporary-file buffers are allocated with ut::make_unique_aligned<byte[]>(..., UNIV_SECTOR_SIZE, ...). UNIV_SECTOR_SIZE is 512. This guarantees only 512-byte memory-address alignment, whereas the test filesystem requires 4096-byte alignment for direct I/O. Consequently, failure depends on the address returned by the allocator; it need not occur on every execution, even on the same filesystem. An independent O_DIRECT pwrite probe on the same test mount isolated this requirement, using a length of 4096 and offset of 0 in both cases: Buffer address modulo 4096 Bytes written errno 0 4096 0 512 -1 22 The original SQL succeeds with innodb_disable_sort_file_cache=OFF on the same 4Kn mount. It also succeeds with the option ON on ordinary ext4 backed by 512-byte logical sectors. A 4096-byte filesystem block size alone does not establish that the filesystem enforces 4096-byte direct-I/O buffer alignment; the logical-sector check and the direct-I/O probe are important controls. Expected behavior: The index is created successfully and the indexed lookup returns (1, 12467), including when direct I/O for DDL temporary files requires 4096-byte alignment. Actual behavior: CREATE INDEX fails with ERROR 1878, and the server reports EINVAL for a 4096-byte temporary-file write at offset 0. How to repeat: 1. On Linux, create a disposable 128 MiB disk-image file and a loop device with 4096-byte logical sectors. Format only this newly created image; do not use an existing disk or partition. The commands below require loop-device and mount privileges, util-linux, and e2fsprogs. Set MYSQL_OS_USER to the operating system account used by the dedicated test mysqld instance. MYSQL_OS_USER=mysql TEST_ROOT=$(mktemp -d /tmp/mysql-ddl-4kn.XXXXXX) chmod 755 "$TEST_ROOT" printf 'Test directory: %s\n' "$TEST_ROOT" truncate -s 128M "$TEST_ROOT/disk.img" mkfs.ext4 -q -F -b 4096 "$TEST_ROOT/disk.img" LOOP_DEVICE=$(sudo losetup --find --show --sector-size 4096 "$TEST_ROOT/disk.img") mkdir "$TEST_ROOT/mnt" sudo mount -o nodev,nosuid,noexec "$LOOP_DEVICE" "$TEST_ROOT/mnt" sudo install -d -o "$MYSQL_OS_USER" -m 700 "$TEST_ROOT/mnt/innodb-tmp" sudo blockdev --getss "$LOOP_DEVICE" stat -f -c '%S' "$TEST_ROOT/mnt" findmnt --target "$TEST_ROOT/mnt" Both numeric checks must report 4096. The attached direct-I/O probe should accept a 4096-aligned buffer and reject a buffer whose address is 512 modulo 4096, with length=4096 and offset=0 in both cases. 2. Initialize and start a dedicated MySQL debug test instance. Its data directory may remain on the ordinary filesystem; only DDL temporary files need the strict-alignment mount. Add these options to its normal startup command, expanding TEST_ROOT to the actual directory printed/recorded in step 1: --innodb-tmpdir="$TEST_ROOT/mnt/innodb-tmp" --innodb-disable-sort-file-cache=ON Keep the shell variables from step 1 for cleanup. If using a configuration file, use the resolved absolute innodb_tmpdir path rather than a shell variable. 3. Connect to the dedicated instance and select a new, empty test database. Run the following SQL, also provided verbatim in repro.sql: SELECT VERSION(); SELECT @@global.innodb_disable_sort_file_cache; CREATE TABLE sbtest1 ( id INT NOT NULL AUTO_INCREMENT, k INT NOT NULL DEFAULT 0, c CHAR(120) NOT NULL DEFAULT '', pad CHAR(60) NOT NULL DEFAULT '', PRIMARY KEY (id) ) ENGINE=InnoDB; INSERT INTO sbtest1 SET id=1, k=12467, c='31451373586-15688153734-79729593694-96509299839-83724898275-86711833539-78981337422-35049690573-51724173961-87474696253', pad='98996621624-36689827414-04092488557-09587706818-65008859162'; CREATE INDEX k_1 ON sbtest1(k); SELECT id, k FROM sbtest1 FORCE INDEX(k_1) WHERE k=12467; DROP TABLE sbtest1; The recorded run failed at CREATE INDEX with ERROR 1878. Collect the server error log. If an allocation happens to be 4096-aligned, that run can succeed; repeat in a clean test database or after restarting the dedicated instance. A successful single run does not establish that the allocator guarantees the required alignment. After a failure, sbtest1 may remain in the test database. 4. Repeat with innodb_disable_sort_file_cache=OFF on the same mount. The original SQL should succeed, as it did in the recorded control. A second control uses ON with ordinary ext4 backed by 512-byte logical sectors. 5. After stopping the dedicated test server, unmount and detach only the loop device created in step 1: sudo umount "$TEST_ROOT/mnt" sudo losetup --detach "$LOOP_DEVICE" The disposable image remains under TEST_ROOT and can be removed afterward. Suggested fix: Use IO_BLOCK_SIZE (4096), rather than UNIV_SECTOR_SIZE (512), as the requested alignment for these four DDL temporary-file buffer allocations: storage/innobase/ddl/ddl0builder.cc Builder::init storage/innobase/ddl/ddl0file-reader.cc File_reader::prepare storage/innobase/ddl/ddl0fts.cc FTS::Parser::init storage/innobase/ddl/ddl0merge.cc Merge_file_sort::sort Add debug assertions at ddl::pread and ddl::pwrite to check that the buffer address is aligned to IO_BLOCK_SIZE. This gives debug regression tests a way to detect invalid buffer alignment even on filesystems which accept 512-byte direct-I/O buffers. Extend innodb.create_index_with_disable_sort_file_cache with coverage for the one-row reproducer and the affected DDL paths, and validate on the strict 4Kn mount as well as through ordinary MTR runs.