MySQL Bugs: #91235: crash with InnoDB: Assertion failure in thread 140136481191680 in file ut0ut.cc

Bug #91235	crash with InnoDB: Assertion failure in thread 140136481191680 in file ut0ut.cc
Submitted:	13 Jun 2018 12:35	Modified:	14 Jul 2018 17:05
Reporter:	Malcolm Jackson	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.7.20	OS:	Red Hat (7.4)
Assigned to:		CPU Architecture:	x86

Description:
InnoDB: ###### Diagnostic info printed to the standard error stream
2018-06-13T09:12:15.581468Z 0 [ERROR] [FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.
2018-06-13 11:12:15 0x7f74112e7700  InnoDB: Assertion failure in thread 140136481191680 in file ut0ut.cc line 916
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.
09:12:15 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.

How to repeat:
mysqldump --all-databases --single-transaction --set-gtid-purged=AUTO --triggers --routines -h mysql-host-prod-01 -u mysql -pxxxx > $BACKUPLOCATION/mysql_dump_$DATE.sql

Output from within the crashed mysqldump file ->

--
-- Table structure for table `slave_relay_log_info`
--

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `slave_relay_log_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
  `Number_of_workers` int(10) unsigned NOT NULL,
  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
  `Channel_name` char(64) NOT NULL COMMENT 'The channel on which the slave is connected to a source. Used in Multisource Replication',
  PRIMARY KEY (`Channel_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `slave_worker_info`
--

DROP TABLE IF EXISTS `slave_worker_info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `slave_worker_info` (
  `Id` int(10) unsigned NOT NULL,
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Relay_log_pos` bigint(20) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_seqno` int(10) unsigned NOT NULL,
  `Checkpoint_group_size` int(10) unsigned NOT NULL,
  `Checkpoint_group_bitmap` blob NOT NULL,
  `Channel_name` char(64) NOT NULL COMMENT 'The channel on which the slave is connected to a source. Used in Multisource Replication',
  PRIMARY KEY (`Channel_name`,`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `slave_worker_info`
--

Please try version 5.7.22. Thanks.
https://dev.mysql.com/downloads/mysql/5.7.html#downloads

Thanks for the feedback Godofredo Miguel Solorzano.

If possible, are you able to confirm for me if the problem that I reported IS in fact resolved by the new version 5.7.22 or is this merely a recommendation for us to update then see if it is solved?

I perused the 5.7.22 Release Notes in search of a specific fix, is it possibly one of these items?

InnoDB: A tablespace import operation on a server with a default row format of REDUNDANT raised an assertion failure. (Bug #26960215)

InnoDB: A RENAME TABLE operation that renamed the schema failed to rename full-text search common auxiliary tables that were left behind when the full-text search index was removed previously, resulting in a assertion failure when attempting to drop the old schema. (Bug #26334149)

InnoDB: An invalid debug condition caused a buffer pool chunk allocation failure, which resulted in an assertion failure when a purge thread attempted to access an unallocated chunk. (Bug #23593654)

Thanks in advance for your hoped for feedback.

Thank you for the bug report. I couldn't repeat on my side but that's not meant it's was fixed since I could lack something on my test to be repeatable asking to test with latest released version is a requirement on filing a bug, you could create a test install server. Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I had a small variant of the same with the following error:
"
2019-05-30T08:10:03.233508Z 28374161 [Warning] InnoDB: Orphan table encountered during DROP DATABASE. This is possible if '`<database>`.`<table>`.frm' was lost.
2019-05-30T08:10:03.248889Z 28374161 [ERROR] [FATAL] InnoDB: Tablespace id is 1845 in the data dictionary but i
n file ./<database>/<table>.ibd it is 15824!
2019-05-30 08:10:03 0x7f63861dd700  InnoDB: Assertion failure in thread 140065428592384 in file ut0ut.cc line 9
43
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.
08:10:03 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.
"

I find some clue in our production.

we can see Thread 140415877949184 wait for thread id 140415877949184. And the Thread id is SAME! please investigate!

--Thread 140415877949184 has waited at mtr0mtr.ic line 153 for 886.00 seconds the semaphore:
X-lock (wait_ex) on RW-latch at 0x7fc4c86fe3b0 created in file buf0buf.cc line 1456
a writer (thread id 140415877949184) has reserved it in mode  wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file row0ins.cc line 2999
Last time write locked in file /mnt/workspace/percona-server-5.7-redhat-binary-rocks/label_exp/min-centos-6-x64/rpmbuild/BUILD/percona-server-5.7.21-21/percona-server-5.7.21-21/storage/innobase/include/mtr0mtr.ic line 153
--Thread 140397140211456 has waited at row0ins.cc line 2999 for 831.00 seconds the semaphore:
S-lock on RW-latch at 0x7fc4c86fe3b0 created in file buf0buf.cc line 1456
a writer (thread id 140415877949184) has reserved it in mode  wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file row0ins.cc line 2999
Last time write locked in file /mnt/workspace/percona-server-5.7-redhat-binary-rocks/label_exp/min-centos-6-x64/rpmbuild/BUILD/percona-server-5.7.21-21/percona-server-5.7.21-21/storage/innobase/include/mtr0mtr.ic line 153

found in 5.7.21