Bug #97452 InnoDB: Assertion failure in thread xxx in file ut0ut.cc line 910
Submitted: 1 Nov 2019 6:33 Modified: 11 Nov 2019 6:21
Reporter: Anthony Qiu Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.7.27 OS:CentOS (CentOS release 6.10 (Final))
Assigned to: CPU Architecture:x86 (Intel(R) Xeon(R) CPU E5-2687W v4)
Tags: assertion failure, crash, hung, semaphore wait

[1 Nov 2019 6:33] Anthony Qiu
Description:
=====================================
2019-11-01 07:10:49 0x2b3eb1b2a700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 2 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1279502 srv_active, 0 srv_shutdown, 1126 srv_idle
srv_master_thread log flush and writes: 1280628
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 4343247
--Thread 47516681033472 has waited at trx0trx.cc line 1185 for 887  seconds the semaphore:
Mutex at 0x38a9b5c0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1

--Thread 47521114507008 has waited at trx0trx.cc line 1185 for 832  seconds the semaphore:
Mutex at 0x38a9b5c0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1

--Thread 47529770030848 has waited at trx0trx.cc line 1185 for 647  seconds the semaphore:
Mutex at 0x38a9b5c0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1

--Thread 47521060194048 has waited at trx0trx.cc line 1185 for 334  seconds the semaphore:
Mutex at 0x38a9b5c0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1

--Thread 47567722559232 has waited at trx0trx.cc line 1185 for 947  seconds the semaphore:
Mutex at 0x38a9b5c0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1

--Thread 47521121429248 has waited at trx0trx.cc line 1185 for 827  seconds the semaphore:
Mutex at 0x38a9b5c0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1

--Thread 47549188630272 has waited at buf0flu.cc line 1209 for 704  seconds the semaphore:
SX-lock on RW-latch at 0x2b38843c5d50 created in file buf0buf.cc line 1460
a writer (thread id 47567434147584) has reserved it in mode  exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file trx0undo.ic line 190
Last time write locked in file /export/home/pb2/build/sb_0-34537258-1560179931.8/mysql-5.7.27/storage/innobase/include/trx0undo.ic line 171
--Thread 47525448406784 has waited at trx0trx.cc line 1185 for 823  seconds the semaphore:
Mutex at 0x38a9b5c0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1

--Thread 47525473167104 has waited at trx0trx.cc line 1185 for 800  seconds the semaphore:
Mutex at 0x38a9b5c0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1

--Thread 47516643493632 has waited at trx0trx.cc line 1185 for 906  seconds the semaphore:
Mutex at 0x38a9b5c0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1

--Thread 47567434147584 has waited at trx0rseg.ic line 48 for 949  seconds the semaphore:
X-lock on RW-latch at 0x2b375ca9bb68 created in file buf0buf.cc line 1460
a writer (thread id 47548273444608) has reserved it in mode  exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file not yet reserved line 0
Last time write locked in file /export/home/pb2/build/sb_0-34537258-1560179931.8/mysql-5.7.27/storage/innobase/include/trx0rseg.ic line 48
--Thread 47529839003392 has waited at trx0trx.cc line 1185 for 491  seconds the semaphore:
Mutex at 0x38a9b5c0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1

--Thread 47521053271808 has waited at trx0trx.cc line 1185 for 850  seconds the semaphore:
Mutex at 0x38a9b5c0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1

--Thread 47516673578752 has waited at trx0trx.cc line 1185 for 891  seconds the semaphore:
Mutex at 0x38a9b5c0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1

--Thread 47525497128704 has waited at trx0trx.cc line 1185 for 785  seconds the semaphore:
Mutex at 0x38a9b5c0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1

--Thread 47529715975936 has waited at trx0trx.cc line 1185 for 707  seconds the semaphore:
Mutex at 0x38a9b5c0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1

--Thread 47516664526592 has waited at trx0trx.cc line 1185 for 894  seconds the semaphore:
Mutex at 0x38a9b5c0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1

--Thread 47548273444608 has waited at trx0undo.ic line 171 for 949  seconds the semaphore:
X-lock on RW-latch at 0x2b38843c5d50 created in file buf0buf.cc line 1460
a writer (thread id 47567434147584) has reserved it in mode  exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file trx0undo.ic line 190
Last time write locked in file /export/home/pb2/build/sb_0-34537258-1560179931.8/mysql-5.7.27/storage/innobase/include/trx0undo.ic line 171
--Thread 47567711377152 has waited at trx0trx.cc line 1185 for 943  seconds the semaphore:
Mutex at 0x38a9b5c0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1
...
END OF INNODB MONITOR OUTPUT
============================
InnoDB: ###### Diagnostic info printed to the standard error stream
2019-11-01T07:10:53.369209+08:00 0 [ERROR] [FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.
2019-11-01 07:10:53 0x2b3eb1929700  InnoDB: Assertion failure in thread 47548267140864 in file ut0ut.cc line 910
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
23:10:53 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=8388608
read_buffer_size=16777216
max_used_connections=1875
max_threads=3000
thread_count=1874
connection_count=1874
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 147504340 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 = 0 thread_stack 0x40000
/data/svr/mysql57/bin/mysqld(my_print_stacktrace+0x35)[0xf54815]
/data/svr/mysql57/bin/mysqld(handle_fatal_signal+0x4a4)[0x7d21b4]
/lib64/libpthread.so.0(+0xf7e0)[0x2b364a5767e0]
/lib64/libc.so.6(gsignal+0x35)[0x2b364b9a84f5]
/lib64/libc.so.6(abort+0x175)[0x2b364b9a9cd5]
/data/svr/mysql57/bin/mysqld(_Z18ut_print_timestampP8_IO_FILE+0x0)[0x7c157e]
/data/svr/mysql57/bin/mysqld(_ZN2ib5fatalD1Ev+0xb3)[0x115c0a3]
/data/svr/mysql57/bin/mysqld(srv_error_monitor_thread+0x98e)[0x110d2de]
/lib64/libpthread.so.0(+0x7aa1)[0x2b364a56eaa1]
/lib64/libc.so.6(clone+0x6d)[0x2b364ba5ec4d]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

---
This undo configure is:
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 2147483648 |
| innodb_undo_directory    | ./         |
| innodb_undo_log_truncate | ON         |
| innodb_undo_logs         | 128        |
| innodb_undo_tablespaces  | 3          |
+--------------------------+------------+

---
This 5.7.27 version is from 5.6.38 upgrade. Use 5.6.38 is does not crash.

---
Whether undo is too small´╝č

How to repeat:
It's not repeated today.
[5 Nov 2019 12:51] Sinisa Milivojevic
Hi Mr. Qiu,

Thank you for your bug report.

However, we do not have sufficient info in order to repeat the problem that you observe. Hence, we need a fully repeatable test case that will always lead to the assert that you experience. We also do not have backtraces for each of the thread that is involved in waiting on the locks.

Meanwhile, you can change some 5.7 defaults to 5.6 defaults and see if it helps.
[8 Nov 2019 2:46] Anthony Qiu
OK,Thanks Sinisa.
[8 Nov 2019 13:30] Sinisa Milivojevic
You are welcome, Mr. Qiu .....
[8 Nov 2019 13:36] Sinisa Milivojevic
Hi,

I have analysed your log and found out that this report is not a bug.

You simply have many transactions holding a very large amout of locks. Each of these transactions takes lots of time waiting on something. This is a problem in your application, where your transactions gets open and then wait on something, like end-user input, network, some unrelated processing or similar. This is a typical application problem. Not database problem.

Regarding deadlocks, they are expected behaviour in relational, ACID databases and are a proof that InnoDB SE works properly.
[11 Nov 2019 6:21] Anthony Qiu
ok,thanks.