MySQL Bugs: #98729: DB forced crash after long semaphore wait

Bug #98729	DB forced crash after long semaphore wait
Submitted:	25 Feb 2020 1:16	Modified:	25 Feb 2020 9:52
Reporter:	Mateusz Kamiński	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0.19	OS:	Debian
Assigned to:	MySQL Verification Team	CPU Architecture:	x86

Description:
Hello,
We have migrated single innodb table to another drive using DATA DIRECTORY switch in table. It worked fine, but we needed to migrate more data to additional drive. In order to do it, we have created new table using DATA DIRECTORY on second disk drive. While filling it with data, our query was timeouted and started rolling back, which probably was a root cause for database crash. 
To make situation even more serious, database did not came up after that as it was crashing while trying to find tablespace. We managed to get it up manually adding --innodb-directories option. 

How to repeat:
We are running database in docker, with singe directory used to store data and innodb-file-per-table. Then we created new innodb table with DATA DIRECTORY option pointing to another drive (which was not listed in innodb-directories). It was not to big and after filling it with data it worked fine. When we tried to do same with bigger table, query timeouted from client side and transaction rollback caused unrecoverable crash.
We don't know how to repeat this behavior, but maybe logs will put more light on it if we will attach them.

While the server was not completely hanged,  it was severely degraded.
There's a separate FR to make teh long semaphore wait configurable timeout and/or disable it completely.

In your case it's a configuration problem.  Increase innodb_buffer_pool_size and innodb_log_file_size and you'll be better off.    Snippets from error log.

Lock wait:
------- TRX HAS BEEN WAITING 33 SEC FOR THIS LOCK TO BE GRANTED:

For large transaction:
ROLLING BACK 170963 lock struct(s), heap size 23552208, 50604301 row lock(s), undo log entries 43062743  

Disk I/O bound:
Pending flushes (fsync) log: 0; buffer pool: 22
Pending reads      1

Slow I/O:
5321.97 reads/s, 16384 avg bytes/read, 57.85 writes/s, 49.50 fsyncs/s

Can all be alleviated by increasing innodb_buffer_pool_size:
Total large memory allocated 137363456

if this is really 32-bit,  then you cannot expect huge transactions as the buffer pool size is severely limited to 2 or 3GB.  Stick with small transactions.

Hi,
It is not 32 bit, it is normal x86_64. So this parameter configuration could explain why it crashed initially. Is there any idea why it didn't wanted to go up after crash? We cannot restart this db without --innodb-directories parameter now as it is production, but tests on different database did not required --innodb-directories parameter to include disk where we created tablespace for single table. We are able to restart test database with such setup and it cames up without problems.