MySQL Bugs: #104735: InnoDB: A long semaphore wait

Bug #104735	InnoDB: A long semaphore wait
Submitted:	26 Aug 2021 9:20	Modified:	27 Aug 2021 11:46
Reporter:	GUILLOT Julien	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Windows (Server 2016 Standard)
Assigned to:		CPU Architecture:	Any (Inter Xeon CPU E5620 2.40 GHZ)

Description:
Hello,

Sometimes, i get the message "InnoDB: A long semaphore wait" when i run this command :

C:
cd "C:\Program Files\MySQL\MySQL Server 5.7\bin\"

mysql -u root -xxxxxx -h localhost  < "D:\JOBS\PROD\PRJ_015_SPHERE\JOB_015_10_L_DUMP_TO_MYSQL\DATA\DROP_DUMP.sql"

mysql -u root -xxxxxx -h localhost cclsiel < "D:\JOBS\PROD\PRJ_015_SPHERE\JOB_015_10_L_DUMP_TO_MYSQL\DATA\cclsiel.sql"

pause

For information, the command DROP_DUMP.sql do this :
DROP DATABASE `cclsiel`;
CREATE DATABASE cclsiel;

The dump cclsiel.sql to import done 6 538 235 Ko

This causes the server to crash ...

Thank you for your help :)
 

How to repeat:
C:
cd "C:\Program Files\MySQL\MySQL Server 5.7\bin\"

mysql -u root -xxxxxx -h localhost  < "D:\JOBS\PROD\PRJ_015_SPHERE\JOB_015_10_L_DUMP_TO_MYSQL\DATA\DROP_DUMP.sql"

mysql -u root -xxxxxx -h localhost cclsiel < "D:\JOBS\PROD\PRJ_015_SPHERE\JOB_015_10_L_DUMP_TO_MYSQL\DATA\cclsiel.sql"

pause

Logs

Attachment: logs.txt (text/plain), 83.15 KiB.

Hi Mr. GUILLOT,

Thank you for your bug report.

However, you have not provided us with all the details that would enable us to process your report.

First of all, the other of the two batch files is unknown to us. We must have both, so that we can try to repeat the behaviour.

Next, " A long semaphore wait " is not a crash due to some bug. It is a controlled setting, with its own variable, in such a manner, then when that time is exceeded a controlled crash occurs. Hence , it is not a bug, but it shows you that you should change the operation and sequence of some of your batch jobs , in such a manner that there is no contention on the InnoDB locks.

Next, we do hope that you are using latest 5.7 release, since latest release contains lots of improvements.

Hello,

Is this the cclsiel.sql file you need?

How should i change the operation and sequence of some of my batch jobs , in such a manner that there is no contention on the InnoDB locks ?

How do i check that i have the latest 5.7 release ? And how to update if necessary ?

Hi,

All these questions belong to problem solving, which makes them support questions. 

But, you can refer to our Reference Manual, read the entire section on the InnoDB storage engine and the one on transactions and you should be able to help yourself.