High CPU usage, MySQL process got stuck on "SHOW SESSION VARIABLES LIKE 'FOREIGN_KEY_CHECKS' "

[Hassan El Jacifi]

Scenario described here: https://bugs.mysql.com/bug.php?id=92387

MySQL version: 5.7.26-0ubuntu0.16.04.1

[Paride Legovini (paride)]

Thank you for taking the time to report this bug and helping to make Ubuntu better. This sounds like a valid bug, however I don't think we'll fix this in Ubuntu until after upstream have released the fix. This report is now tracking the upstream bug report; once upstream releases a fix we will evaluate how to port it to the existing Ubuntu releases; the action to take will largely depend on the scope of the patch and on its risk of causing regressions.

[Bryce Harrington (bryce)]

This now appears to have been fixed upstream, in November. The flagged commits for this fix are:

"""
Both commit ids need to be applied, the second commit
id was to fix a test case issue.

Pushed into repo mysql branch mysql-5.7 5.7.29
(hash:df622a4e0fd5b919f26ff217047ced988a90e1b4
committer:<email address hidden>)
(merge vers:5.7.29)

Pushed into repo mysql branch mysql-5.7 5.7.29
(hash:dc4c221afca22196967252f324431626dafbec06
committer:<email address hidden>)
(merge vers:5.7.29)
"""

Next action is to retrieve the patches, review them for acceptability per Ubuntu SRU policies, prepare packaging for the fix, and fill in the SRU. If anyone wishes to lend a hand on one or more of these tasks it may help speed this issue towards a fix. For reference, the SRU process is outlined at https://wiki.ubuntu.com/StableReleaseUpdates

[Lucas Kanashiro (lucaskanashiro)]

As Bryce mentioned the bug was fixed in version 5.7.29. The security team already made available version 5.7.30 in Xenial (and also Bionic). So the fix is already available if you have the security pocket enabled in your system.

[Hassan El Jacifi (waver)]

It doesn't seems to be fixed on the version 5.7.30 and Bryce never mentioned that the bug is fixed but need to retrieve patches.

I can still reproduce the bug with the version 5.7.30 "mysql-server-core 5.7.30-0ubuntu0.16.04.1":

# service mysql restart

2020-06-06T15:01:13.632638Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2020-06-06T15:01:13.632650Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2020-06-06T15:01:13.632671Z 0 [Note] Shutting down plugin 'InnoDB'
2020-06-06T15:01:13.802792Z 0 [Note] InnoDB: FTS optimize thread exiting.
2020-06-06T15:01:13.803959Z 0 [Note] InnoDB: Starting shutdown...
2020-06-06T15:01:13.904557Z 0 [Note] InnoDB: Dumping buffer pool(s) to /mysql/ib_buffer_pool
2020-06-06T15:01:13.908147Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 200606 17:01:13
2020-06-06T15:01:14.405452Z 0 [ERROR] [FATAL] InnoDB: Page [page id: space=118412, page number=324] still fixed or dirty
2020-06-06 17:01:14 0x7fa0b89d7740 InnoDB: Assertion failure in thread 140328268822336 in file ut0ut.cc line 918
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com. ...

[Paride Legovini (paride)]

Hi Hassen,

The patches had to be retrieved to patch mysql < 5.7.29, but since the upstream version containing the fix got packaged in Ubuntu there is no need to do so anymore.

Coming to your comment, you claim that the upstream patch didn't work for your. This could mean two things:

1. The upstream patch doesn't (completely) fix the problem. In this case we need to follow-up to the upstream bug and provide a case which shows that the problem didn't go away in version 5.7.30.

2. You are hitting a different problem with similar symptoms. In this case you should file a separate bug which then will worked at.

What I'd like to ask you is to:

1. Paste the output of `dpkg -l | grep mysql`, so more eyes will check that all the package versions are right.

2. Carefully go through the scenario described in the upstream bug and make sure that's exactly the problem you are facing, and share your finding here. We'll then try to figure out what the best next steps are.

I'm setting the status of this report to Incomplete for the moment. Please change it back to New after commenting back and we'll look at it again. Thanks!

Thanks!

[Hassan El Jacifi (waver)]

Hi Paride,

I'm sorry, I mixed between this bug and bug #1874226.

I can confirm that the issue was fixed.