We are frequently experiencing hangs restoring data from dumps taken by mysqldump. This happens whether or not the data comes in through the replication or a direct communication.

We've tried to search for an existing report, but the lack of error message provides a challenge.

Once the hang happens, other queries get stuck.

Type    Name    Status

InnoDB          

=====================================

2023-11-20 15:44:39 0x7f4834099640 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 239 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 3306 srv_idle

srv_master_thread log flush and writes: 3305

----------

SEMAPHORES

----------

------------

TRANSACTIONS

------------

Trx id counter 119646003

Purge done for trx's n:o < 119645990 undo n:o < 0 state: running

History list length 13

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 119646002, ACTIVE 1859 sec

2 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1

MariaDB thread id 6, OS thread handle 139948236305984, query id 28520 creating table

CREATE TABLE `contact_password_reset` (

  `contact_password_reset_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `contact_id` int(10) unsigned NOT NULL,

  `reset_token` varchar(50) NOT NULL,

  `email` varchar(128) DEFAULT NULL,

  `creation_date` datetime NOT NULL,

  `expiry_date` datetime NOT NULL,

  PRIMARY KEY (`contact_password_reset_id`),

  UNIQUE KEY `reset_token_email` (`reset_token`,`email`),

  KEY `contact_id_idx` (`contact_id`),

  KEY `reset_token_idx` (`reset_token`),

  KEY `email_idx` (`email`)

) ENGINE=InnoDB ROW_FORMAT=compressed AUTO_INCREMENT=3362 DEFAULT CHARSET=utf8mb4 COL

---TRANSACTION (0x7f483d7a2180), not started

0 lock struct(s), heap size 1128, 0 row lock(s)

---TRANSACTION (0x7f483d7a1680), not started

0 lock struct(s), heap size 1128, 0 row lock(s)

---TRANSACTION (0x7f483d7a0b80), not started

0 lock struct(s), heap size 1128, 0 row lock(s)

--------

FILE I/O

--------

Pending flushes (fsync) log: 0; buffer pool: 0

8166 OS file reads, 2862054 OS file writes, 88453 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf: size 1, free list len 45380, seg size 45382, 0 merges

merged operations:

 insert 0, delete mark 0, delete 0

discarded operations:

 insert 0, delete mark 0, delete 0

0.00 hash searches/s, 0.00 non-hash searches/s

---

LOG

---

Log sequence number 10432719223072

Log flushed up to   10432719220871

Pages flushed up to 10432670563571

Last checkpoint at  10432668152489

0 pending log flushes, 0 pending chkp writes

58390 log i/o's done, 0.00 log i/o's/second

----------------------

BUFFER POOL AND MEMORY

----------------------

Total large memory allocated 1082130432

Dictionary memory allocated 10949448

Buffer pool size   64896

Free buffers       1591

Database pages     105124

Old database pages 38805

Modified db pages  1236

Percent of dirty pages(LRU & free pages): 1.158

Max dirty pages percent: 90.000

Pending reads 0

Pending writes: LRU 0, flush list 223

Pages made young 1932, not young 58625

0.00 youngs/s, 0.00 non-youngs/s

Pages read 9599, created 941677, written 2789530

0.00 reads/s, 0.00 creates/s, 0.00 writes/s

No buffer pool page gets since the last printout

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 105124, unzip_LRU len: 10543

I/O sum[27262]:cur[2416], unzip sum[355]:cur[406]

--------------

ROW OPERATIONS

--------------

0 read views open inside InnoDB

Process ID=0, Main thread ID=0, state: enforcing dict cache limit

Number of rows inserted 31314879, updated 90, deleted 2, read 110

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

Number of system rows inserted 18185, updated 0, deleted 18173, read 18187

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

----------------------------

END OF INNODB MONITOR OUTPUT

============================

{/code}

2023-11-20 14:57:22 5 [Note] Slave IO thread is reconnected to receive Gtid_log_event 2-2-21297. It is to skip 1 already received events including the gtid one
231120 16:36:09 [ERROR] mysqld got signal 6 ;
Sorry, we probably made a mistake, and this is a bug.

Your assistance in bug reporting will enable us to fix this for the next release.
To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Server version: 10.6.16-MariaDB-1:10.6.16+maria~ubu2204-log source revision: b83c379420a8846ae4b28768d3c81fa354cca056
key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=12
max_threads=10002
thread_count=14
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 22044277 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x0 thread_stack 0x40000
mysys/stacktrace.c:216(my_print_stacktrace)[0x561c35e42d52]
sql/signal_handler.cc:238(handle_fatal_signal)[0x561c358f41f8]
/lib/x86_64-linux-gnu/libc.so.6(+0x42520)[0x7f4847b77520]
/lib/x86_64-linux-gnu/libc.so.6(__poll+0x4f)[0x7f4847c4ddbf]
sql/mysqld.cc:6191(handle_connections_sockets())[0x561c355d08ba]
psi/mysql_thread.h:745(mysqld_main(int, char**))[0x561c355d18eb]
/lib/x86_64-linux-gnu/libc.so.6(+0x29d90)[0x7f4847b5ed90]
/lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0x80)[0x7f4847b5ee40]
/usr/sbin/mariadbd(_start+0x25)[0x561c355c6265]
The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mariadbd/ contains
information that should help you find out what is causing the crash.
Writing a core file...
Working directory at /var/lib/mysql
Resource Limits:
Limit Soft Limit Hard Limit Units
Max cpu time unlimited unlimited seconds
Max file size unlimited unlimited bytes
Max data size unlimited unlimited bytes
Max stack size 8388608 unlimited bytes
Max core file size unlimited unlimited bytes
Max resident set unlimited unlimited bytes
Max processes 63675 63675 processes
Max open files 32768 32768 files
Max locked memory 524288 524288 bytes
Max address space unlimited unlimited bytes
Max file locks unlimited unlimited locks
Max pending signals 63675 63675 signals
Max msgqueue size 819200 819200 bytes
Max nice priority 0 0
Max realtime priority 0 0
Max realtime timeout unlimited unlimited us
Core pattern: core

Kernel version: Linux version 5.15.0-87-generic (buildd@lcy02-amd64-011) (gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, GNU ld (GNU Binutils for Ubuntu) 2.38) #97-Ubuntu SMP Mon Oct 2 21:09:21 UTC 2023

I have attached the backtrace of the relevant thread.