| Bug #119667 | Assertion failure ddl0buffer.cc:197 with innodb-ddl-threads=4 + O_DIRECT | ||
|---|---|---|---|
| Submitted: | 13 Jan 11:42 | Modified: | 26 Jan 14:07 |
| Reporter: | Jacob Ding | Email Updates: | |
| Status: | Open | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 9.4.0 | OS: | Ubuntu (22.04) |
| Assigned to: | CPU Architecture: | x86 (x86_64) | |
| Tags: | assertion failure, DDL, innodb-ddl-threads, O_DIRECT | ||
[13 Jan 11:42]
Jacob Ding
[15 Jan 12:44]
MySQL Verification Team
For some reason I couldn't reproduce this:
Run server:
$ ./bin/mysqld-debug --no-defaults --basedir=. --datadir=data --port=3333 --socket=/tmp/2222.sock --innodb-ddl-threads=1 --innodb-flush-method=O_DIRECT --console
2026-01-15T12:41:41.899996Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2026-01-15T12:41:42.093247Z 0 [System] [MY-010116] [Server] /tmp/mysql-commercial-9.4.0-linux-glibc2.17-x86_64/bin/mysqld-debug (mysqld 9.4.0-commercial-debug) starting as process 16043
2026-01-15T12:41:42.093268Z 0 [System] [MY-015590] [Server] MySQL Server has access to 16 logical CPUs.
2026-01-15T12:41:42.093287Z 0 [System] [MY-015590] [Server] MySQL Server has access to 126064812032 bytes of physical memory.
2026-01-15T12:41:42.104132Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2026-01-15T12:41:42.714639Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2026-01-15T12:41:43.574816Z 0 [ERROR] [MY-011300] [Server] Plugin mysqlx reported: 'Setup of socket: '/tmp/mysqlx.sock' failed, can't open lock file /tmp/mysqlx.sock.lock'
2026-01-15T12:41:43.836707Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2026-01-15T12:41:43.836768Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2026-01-15T12:41:43.841598Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/export/home/tmp' in the path is accessible to all OS users. Consider choosing a different directory.
2026-01-15T12:41:43.986487Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
2026-01-15T12:41:43.986677Z 0 [System] [MY-010931] [Server] /tmp/mysql-commercial-9.4.0-linux-glibc2.17-x86_64/bin/mysqld-debug: ready for connections. Version: '9.4.0-commercial-debug' socket: '/tmp/2222.sock' port: 3333 MySQL Enterprise Server - Commercial - Debug.
$ mysql -uroot -h127.0.0.1 -P3333 -v
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 221
Server version: 9.4.0-commercial-debug MySQL Enterprise Server - Commercial - Debug
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Reading history-file /home/sbester/.mysql_history
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> source g.sql
--------------
drop database if exists `mtr_repro_prefix_index`
--------------
Query OK, 1 row affected (0.03 sec)
--------------
CREATE DATABASE IF NOT EXISTS `mtr_repro_prefix_index`
--------------
Query OK, 1 row affected (0.01 sec)
Database changed
--------------
CREATE TABLE worklog5743 ( col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , PRIMARY KEY `prefix_primary` (col_1_varchar(3072)) ) charset latin1 ROW_FORMAT=DYNAMIC, engine = innodb
--------------
Query OK, 0 rows affected (0.03 sec)
--------------
INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000))
--------------
Query OK, 1 row affected (0.00 sec)
--------------
CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072))
--------------
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
--------------
INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000))
--------------
Query OK, 1 row affected (0.01 sec)
--------------
ALTER TABLE worklog5743 DROP PRIMARY KEY
--------------
Query OK, 2 rows affected (0.09 sec)
Records: 2 Duplicates: 0 Warnings: 0
--------------
DROP INDEX prefix_idx ON worklog5743
--------------
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
--------------
ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_varchar(3072))
--------------
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
[16 Jan 6:23]
MySQL Verification Team
BTW, if you can try 9.5.0 also, it will be good.
However I've followed your exact steps on a ubuntu 22.04 VM (not docker) and still don't see any problem on 9.4.0-debug.
mysql> select @@global.innodb_flush_method,@@global.innodb_ddl_threads,version();
+------------------------------+-----------------------------+-------------+
| @@global.innodb_flush_method | @@global.innodb_ddl_threads | version() |
+------------------------------+-----------------------------+-------------+
| O_DIRECT | 4 | 9.4.0-debug |
+------------------------------+-----------------------------+-------------+
1 row in set (0.001 sec)
mysql>
mysql> CREATE DATABASE IF NOT EXISTS `mtr_repro_prefix_index`;
Query OK, 1 row affected (0.017 sec)
....
mysql>
mysql> ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_varchar(3072));
Query OK, 0 rows affected (0.189 sec)
Records: 0 Duplicates: 0 Warnings: 0
2026-01-16T06:16:51.288199Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2026-01-16T06:16:51.288291Z 0 [System] [MY-010931] [Server] /usr/global/support/sbester/bug119667/my/bin/mysqld: ready for connections. Version: '9.4.0-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution.
no crash.
Extra info:
-------------
$ lsblk -o NAME,PHY-SeC,SIZE
NAME PHY-SEC SIZE
loop0 512 104.2M
loop1 512 55.5M
loop2 512 63.8M
loop3 512 89.4M
loop4 512 100.4M
loop5 512 49.3M
loop6 512 105M
loop7 512 55.5M
loop8 512 63.8M
loop9 512 91.4M
sda 4096 125G
├─sda1 4096 124.9G
├─sda14 4096 4M
└─sda15 4096 106M
$ sudo blockdev --getbsz /dev/sda
4096
$ sudo blockdev --getss /dev/sda
512
$ sudo blockdev --getpbsz /dev/sda
4096
$ sudo fdisk -l /dev/sda
Disk /dev/sda: 125 GiB, 134217728000 bytes, 262144000 sectors
Disk model: BlockVolume
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 4096 bytes
I/O size (minimum/optimal): 4096 bytes / 1048576 bytes
Disklabel type: gpt
Disk identifier: C419A41F-2861-4103-9CFA-3854CDD8F03F
Device Start End Sectors Size Type
/dev/sda1 227328 262143966 261916639 124.9G Linux filesystem
/dev/sda14 2048 10239 8192 4M BIOS boot
/dev/sda15 10240 227327 217088 106M EFI System
Partition table entries are not in disk order.
$ stat -f /tmp
File: "/tmp"
ID: 95a394e5df7a87ec Namelen: 255 Type: ext2/ext3
Block size: 4096 Fundamental block size: 4096
Blocks: Total: 31709340 Free: 28741742 Available: 28737646
Inodes: Total: 16128000 Free: 15801944
$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 22.04.5 LTS
Release: 22.04
Codename: jammy
[16 Jan 6:31]
MySQL Verification Team
I'll try docker next and see if it reproduces there.
[16 Jan 6:52]
Jacob Ding
I tried, and found that the parameters are not actually needed to trigger the assertion failure following the commands acctually...@@global.innodb_flush_method | @@global.innodb_ddl_threads Sorry for the inconvenience. And it also worked for 9.5.0.
[16 Jan 6:55]
MySQL Verification Team
I've found similar bugfix in the past: https://github.com/search?q=repo%3Amysql%2Fmysql-server+36342792&type=commits
[16 Jan 7:06]
Jacob Ding
I analyzed the stack trace and the commit you linked. You are absolutely right—they are highly similar.
Location: My crash occurs in ddl::Key_sort_buffer::serialize called by bulk_add_row, which matches the context of Bug#36342792 exactly.
Logic: The assertion failure ptr + rec_size <= bounds.second explicitly indicates a write past the buffer end, which is the exact symptom described in that bug report ("aligned past end of buffer").
However, here is the critical point: I am reproducing this on the latest 9.5.0 (and 9.4.0) build, which certainly includes the fix from Bug#36342792.
This indicates that the previous fix (Change-Id: I607...) was likely incomplete or did not cover the specific edge case triggered by my test steps. The condition to "free up space in the buffer" (mentioned in the fix) might not be triggering correctly in this specific scenario, leading to the assertion failure.
So, this report likely represents a scenario that bypassed the previous fix.
[16 Jan 8:21]
MySQL Verification Team
I've tried in a docker too, still couldn't reproduce it. mysql> ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_varchar(3072)); Query OK, 0 rows affected (0.294 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> select version(); +-------------+ | version() | +-------------+ | 9.5.0-debug | +-------------+ 1 row in set (0.006 sec) mysql> select @@innodb_ddl_threads,@@innodb_flush_method; +----------------------+-----------------------+ | @@innodb_ddl_threads | @@innodb_flush_method | +----------------------+-----------------------+ | 4 | O_DIRECT | +----------------------+-----------------------+ 1 row in set (0.001 sec)
[16 Jan 9:05]
MySQL Verification Team
I've found another similar issue that was actually fixed in 9.5.0: https://github.com/search?q=repo%3Amysql%2Fmysql-server+37882398&type=commits so checking with the devs about this one.
[26 Jan 12:53]
Jacob Ding
Sorry for the late reply — I’ve been tied up for the last ~10 days. Quick update / context: - The source tree I’m testing is exactly mysql-9.4.0 at commit/tag `b79ac1111737174c1b36ab5f63275f0191c000dc`. - I noticed that this commit contains the fix for Bug#37882398 (assertion in `ddl0file-reader.cc:193`) related to DDL chunk sizes / 4KB sector alignment and input buffer limits (as described in the commit message). Why I’m following up: - My crash is a similar InnoDB debug assertion, but in a different location: `Assertion failure: ddl0buffer.cc:197: ptr + rec_size <= bounds.second` top frame: `ddl::Key_sort_buffer::serialize()` during `ALTER TABLE ... ADD PRIMARY KEY (col_1_varchar(3072))`. - This suggests there may still be an unhandled edge case in the DDL buffer / chunk / input-buffer sizing logic (even though 9.4.0 already includes the 37882398 fix), or a closely related regression triggered by the same scenario. New evidence uploaded: I uploaded a tarball of my `repro_out_manual/` output directory from a clean Ubuntu 22.04 based environment. It should be a complete evidence set to compare with the non-repro reports. It includes: - `mysqld.err` (BuildID + assertion + full backtrace) - BuildID[sha1]=`71738c4ea7bf644be5fd56e308c93e8359011ec6` - signature: `ddl0buffer.cc:197` + SIGABRT - `collect.pre.txt` / `collect.mid.txt` (server variables snapshot, including `@@innodb_ddl_threads`, `@@innodb_flush_method`, `@@innodb_use_native_aio`, etc.) - `show_create_table.collect.mid.txt` (schema right before the crashing `ALTER`, after dropping PK and the secondary index) - `import.stdout` / `import.stderr` Repro summary: - Execute the attached `min_repro_prefix_index_liftedlimit.sql` (from MTR). The final statement triggers the crash: `ALTER TABLE worklog5743 ADD PRIMARY KEY (col_1_varchar(3072));` - The crash reproduces with `innodb_ddl_threads=4` and also with `innodb_ddl_threads=1`, and also with `--no-defaults` in my environment. If helpful, I can also share the exact docker image / harness used to generate `repro_out_manual/` to ensure a 1:1 environment.
[26 Jan 14:07]
Jacob Ding
Update: I re-checked reproducibility across environments. In my docker repro environment (Ubuntu 22.04 container, MySQL 9.4.0-debug, BuildID 71738c4ea7bf644be5fd56e308c93e8359011ec6), the split repro still consistently crashes with the same debug assertion: storage/innobase/ddl/ddl0buffer.cc:197 (ddl::Key_sort_buffer::serialize()), followed by mysqld got signal 6. The full logs and evidence are attached in repro_out_manual (includes mysqld.err, collect.pre/mid/post.txt, and SHOW CREATE TABLE snapshots). On a separate Ubuntu 22.04 PVE VM, running the same SQL (min_repro_prefix_index_liftedlimit.sql) did NOT reproduce after 200 iterations (loop runner drops/recreates worklog5743 each time). tmpdir=/tmp on ext4; client connects via /tmp/mysql.sock. Next steps I’m trying: increasing data volume (more rows) and sweeping innodb_sort_buffer_size to see if the trigger is boundary/probabilistic. If you have any recommended knobs to force the DDL sort/merge path that hits this assertion, I can test them.
