Docker环境下MariaDB 11.3.2执行特定INSERT时崩溃求助

使用DockerHub上的MariaDB 11.3.2容器（版本标识：11.3.2-MariaDB-1:11.3.2+maria~ubu2204），执行一段批量插入SQL时服务器会触发崩溃（Fatal signal 11），每次崩溃后重启服务，该语句就能正常执行，本次操作计划插入约1400条数据。

崩溃日志 #

2024-04-09 13:45:52 information that should help you find out what is causing the crash.
2024-04-09 13:45:52 Writing a core file...
2024-04-09 13:45:52 Working directory at /var/lib/mysql
2024-04-09 13:45:52 Resource Limits:
2024-04-09 13:45:52 Limit                     Soft Limit           Hard Limit           Units     
2024-04-09 13:45:52 Max cpu time              unlimited            unlimited            seconds   
2024-04-09 13:45:52 Max file size             unlimited            unlimited            bytes     
2024-04-09 13:45:52 Max data size             unlimited            unlimited            bytes     
2024-04-09 13:45:52 Max stack size            8388608              unlimited            bytes     
2024-04-09 13:45:52 Max core file size        0                    unlimited            bytes     
2024-04-09 13:45:52 Max resident set          unlimited            unlimited            bytes     
2024-04-09 13:45:52 Max processes             unlimited            unlimited            processes 
2024-04-09 13:45:52 Max open files            1048576              1048576              files     
2024-04-09 13:45:52 Max locked memory         unlimited            unlimited            bytes     
2024-04-09 13:45:52 Max address space         unlimited            unlimited            bytes     
2024-04-09 13:45:52 Max file locks            unlimited            unlimited            locks     
2024-04-09 13:45:52 Max pending signals       63274                63274                signals   
2024-04-09 13:45:52 Max msgqueue size         819200               819200               bytes     
2024-04-09 13:45:52 Max nice priority         0                    0                    
2024-04-09 13:45:52 Max realtime priority     0                    0                    
2024-04-09 13:45:52 Max realtime timeout      unlimited            unlimited            us        
2024-04-09 13:45:52 Core pattern: /mnt/wslg/dumps/core.%e
2024-04-09 13:45:52 
2024-04-09 13:45:52 Kernel version: Linux version 5.15.133.1-microsoft-standard-WSL2 (root@1c602f52c2e4) (gcc (GCC) 11.2.0, GNU ld (GNU Binutils) 2.37) #1 SMP Thu Oct 5 21:02:42 UTC 2023
2024-04-09 13:45:52 
2024-04-09 13:45:52 Fatal signal 11 while backtracing

触发崩溃的SQL语句 #

SELECT @group_id := id FROM `groups` WHERE name = 'olab';
SELECT @role_id := id FROM `roles` WHERE name = 'learner';
SELECT @max_id := MAX(id) FROM `users`;
SET @batch_size = @max_id / 10;
SET @num_batches = @max_id / @batch_size;

DELIMITER //
FOR l in 0..@num_batches
DO
    START TRANSACTION;
    SET @start = l * @batch_size;
    SET @end = ( l + 1 ) * @batch_size;
    INSERT INTO `user_groups` (user_id, group_id, role_id )
        SELECT id, @group_id, @role_id
        FROM `users` WHERE id >= @start and id <= @end;
    COMMIT;
END FOR;
//

排查方向与解决办法 #

1. 版本BUG排查 #

Fatal signal 11是段错误，大概率是MariaDB 11.3.2版本的未修复BUG。可以尝试：

• 降级到10.11系列的LTS稳定版本
• 升级到11.3.x的后续小版本，看官方是否已修复该问题

2. 调整批量插入逻辑 #

原SQL存在两个潜在问题：

• 除法计算@batch_size可能得到小数，导致范围判断异常，改成CEIL(@max_id /10)确保是整数
• FOR循环在MariaDB中属于存储过程专属语法，直接执行可能引发异常，换成WHILE循环更稳妥

修改后的SQL示例：

SELECT @group_id := id FROM `groups` WHERE name = 'olab';
SELECT @role_id := id FROM `roles` WHERE name = 'learner';
SELECT @max_id := MAX(id) FROM `users`;
SET @batch_size = CEIL(@max_id / 10);
SET @current = 0;

DELIMITER //
WHILE @current <= @max_id DO
    START TRANSACTION;
    INSERT INTO `user_groups` (user_id, group_id, role_id )
        SELECT id, @group_id, @role_id
        FROM `users` WHERE id >= @current and id < @current + @batch_size;
    COMMIT;
    SET @current = @current + @batch_size;
END WHILE;
//
DELIMITER ;

3. 容器资源调整 #

WSL2环境下的容器可能存在隐性内存限制，尝试：

• 给Docker容器分配更多内存（比如调整到4G以上）
• 修改MariaDB配置中的innodb_buffer_pool_size参数，提升内存分配

4. 临时禁用并行优化 #

可以临时关闭innodb_parallel_read_threads或调整optimizer_switch中的并行选项，排查是否是并行处理导致的崩溃。

内容的提问来源于stack exchange，提问作者pathrider