MySQL Bugs: #94689: MySQL Instance restarts

Bug #94689	MySQL Instance restarts
Submitted:	18 Mar 2019 11:32	Modified:	8 Apr 2019 13:14
Reporter:	Jan Eschweiler	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.7.25	OS:	Debian (8.11)
Assigned to:		CPU Architecture:	Any

Description:
The following bug occurs at least once a day on our production environment:
error.log:
2019-03-13 02:51:43 0x7fc68338e700  InnoDB: Assertion failure in thread 140490581796608 in file btr0btr.cc line 2157
2019-03-13T01:51:48.742628Z 0 [Warning] Changed limits: max_open_files: 5000 (requested 7500)
2019-03-13T01:51:48.742787Z 0 [Warning] Changed limits: table_open_cache: 1745 (requested 2000)
2019-03-13T01:51:48.945750Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see docu
mentation for more details).
2019-03-13T01:51:48.946463Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2019-03-13T01:51:48.949334Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.16-log) starting as process 32297 ...
2019-03-13T01:51:48.965543Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-03-13T01:51:48.965579Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-03-13T01:51:48.965585Z 0 [Note] InnoDB: Uses event mutexes
2019-03-13T01:51:48.965590Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-03-13T01:51:48.965595Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2019-03-13T01:51:48.965603Z 0 [Note] InnoDB: Using Linux native AIO
2019-03-13T01:51:48.967618Z 0 [Note] InnoDB: Number of pools: 1
2019-03-13T01:51:48.968138Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-03-13T01:51:48.970774Z 0 [Note] InnoDB: Initializing buffer pool, total size = 64G, instances = 8, chunk size = 128M
2019-03-13T01:51:54.144686Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-03-13T01:51:55.127940Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setprio
rity().
2019-03-13T01:51:55.142055Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-03-13T01:51:55.898481Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 7532240498295

syslog:
Mar 13 02:51:48 hostname systemd[1]: mysql.service: main process exited, code=exited, status=2/INVALIDARGUMENT
Mar 13 02:51:48 hostname systemd[1]: Unit mysql.service entered failed state.
Mar 13 02:51:48 hostname systemd[1]: mysql.service holdoff time over, scheduling restart.
Mar 13 02:51:48 hostname systemd[1]: Stopping MySQL Community Server...
Mar 13 02:51:48 hostname systemd[1]: Starting MySQL Community Server...
Mar 13 02:51:52 hostname kernel: [29296316.800059] e1000 0000:02:00.0 eth0: Reset adapter
Mar 13 02:51:52 hostname kernel: [29296316.860609] e1000: eth0 NIC Link is Up 1000 Mbps Full Duplex, Flow Control: None
Mar 13 02:52:28 hostname systemd[1]: Started MySQL Community Server.

How to repeat:
<none>

Moved to critical, because MySQL has a downtime with this error

Hi,

Thank you for your bug report.

Let me explain to you, in short terms, what is happening. You are performing an INSERT in an InnoDB table that does not have a small number of rows. Then, a row is inserted successfully, although it should not have been the case. Simply, a failure was expected again, but it did not happen.

We can not know why has this occurred. Hence, we need a fully repeatable test case, so that we can reproduce that error. What we need is a full dump of the table that gets those INSERTs. A dump should include  CREATE TABLE statement, with as many INSERTs as necessary, including the one that produces this assert.

Then, when we get that error ourselves, then we will be able to verify this bug.

Many thanks in advance.

Hi,

We do have some further info for you. The assert() does not happen during an INSERT command.

The assert happens when you run a SELECT which requires the use of the large temporary disk table. Hence, it would be nice if you would find that SELECT that leads to the assert. Here is some further info, that might help you avoid the problem.

This seems to happen when a SELECT uses innodb intrinsic table.

It seems the function btr_cur_pessimistic_insert returns an error of some kind, that is not expected.

btr_cur_pessimistic_insert might return errors:
  o) error from btr_cur_ins_lock_and_undo (DB_WAIT_LOCK for example).
  o) DB_OUT_OF_FILE_SPACE
  o) DB_TOO_BIG_RECORD

Anyway,  please try a workaround to prevent crash ::

[mysqld]
internal-tmp-disk-storage-engine=MyISAM

That would essentially solve your problem.

The workaround solves our issue:

[mysqld]
internal-tmp-disk-storage-engine=MyISAM

Cheers
Jan

Thank you for your feedback.

Hi ,

We do not process reports on the products that are not ours.