MySQL Bugs: #91423: Can't run mysql on Ubuntu systems with long recovery time

Bug #91423	Can't run mysql on Ubuntu systems with long recovery time
Submitted:	26 Jun 2018 12:21	Modified:	12 Jul 2019 15:57
Reporter:	Evgeniy Patlan (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Packaging	Severity:	S1 (Critical)
Version:	8.0.1, 5.7.22	OS:	Debian
Assigned to:	Lars Tangvald	CPU Architecture:	Any

Description:
Hi.
If the recovery take longer then 10 minutes mysql service couldn't be started:
===================
Jun 18 21:04:07 rocky mysqld[29333]: 2018-06-19T01:04:07.878308Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...
Jun 18 21:05:01 rocky CRON[29547]: pam_unix(cron:session): session opened for user root by (uid=0)
Jun 18 21:05:01 rocky CRON[29548]: (root) CMD (command -v debian-sa1 > /dev/null && debian-sa1 1 1)
Jun 18 21:05:01 rocky CRON[29547]: pam_unix(cron:session): session closed for user root
Jun 18 21:05:58 rocky systemd[1]: mysql.service: State 'stop-final-sigterm' timed out. Killing.
===================

The root of the issue - defined TimeoutSec parameter in systemd unitfile.
As I can see on Debian/Ubuntu systems this parameter is 600 seconds.

So if recovery takes longer then 600 second mysql service will not be started.

How to repeat:
The easiest way to repeat this is the simulation of such delay:

modify /usr/share/mysql-8.0/mysql-systemd-start in the following way:
===========================
--- /usr/share/mysql-8.0/mysql-systemd-start    2018-06-26 11:30:10.437964667 +0000
+++ /usr/share/mysql-8.0/mysql-systemd-start    2018-06-26 11:30:26.937964667 +0000
@@ -30,6 +30,7 @@
 
 # Include helper functions
 . /usr/share/mysql-8.0/mysql-helpers
+sleep 30
 sanity () {
 # Make sure database and required directories exist
        verify_ready $1
===========================

And also modify the parameter in systemd unitfile:

===========================
--- /lib/systemd/system/mysql.service   2018-06-26 11:33:44.725964667 +0000
+++ /lib/systemd/system/mysql.service   2018-06-26 11:34:08.053964667 +0000
@@ -38,7 +38,7 @@
 PermissionsStartOnly=true
 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre
 ExecStart=/usr/sbin/mysqld
-TimeoutSec=600
+TimeoutSec=10
 LimitNOFILE = 10000
 Restart=on-failure
 RestartPreventExitStatus=1
===========================

Suggested fix:
diff --git a/packaging/deb-in/mysql-packagesource-server.mysql.service.in b/packaging/deb-in/mysql-packagesource-server.mysql.service.in
index 68a9de7..75c94cd 100644
--- a/packaging/deb-in/mysql-packagesource-server.mysql.service.in
+++ b/packaging/deb-in/mysql-packagesource-server.mysql.service.in
@@ -38,7 +38,7 @@ Type=notify
 PermissionsStartOnly=true
 @DEB_SERVICE_SERVER_EXECPRE@
 ExecStart=/usr/sbin/mysqld
-TimeoutSec=600
+TimeoutSec=0
 LimitNOFILE = 10000
 Restart=on-failure
 RestartPreventExitStatus=1
diff --git a/packaging/deb-in/mysql-packagesource-server.mysql@.service.in b/packaging/deb-in/mysql-packagesource-server.mysql@.service.in
index ed1caac..4253ff2 100644
--- a/packaging/deb-in/mysql-packagesource-server.mysql@.service.in
+++ b/packaging/deb-in/mysql-packagesource-server.mysql@.service.in
@@ -38,7 +38,7 @@ Type=notify
 PermissionsStartOnly=true
 @DEB_SERVICE_SERVER_EXECPRE@ @%I
 ExecStart=/usr/sbin/mysqld --defaults-group-suffix=@%I
-TimeoutSec=600
+TimeoutSec=0
 LimitNOFILE = 10000
 Restart=on-failure
 RestartPreventExitStatus=1

Hi!

Thanks for report!

Deb packaging differ from generic systemd service:

 https://github.com/mysql/mysql-server/blob/8.0/scripts/systemd/mysqld.service.in#L43

this should be corrected.

As I know  scripts/systemd/mysqld.service.in is used for rpm packaging but for debs it should use files in packaging directory.
According to deb packaging policies systemd and init.d scripts/templates should be placed in the packaging directory.

=========================================
root@ubuntu-xenial:~# sudo apt-get install mysql-community-serverReading package lists... Done
Building dependency tree       
Reading state information... Done
The following additional packages will be installed:
  mysql-client mysql-common mysql-community-client mysql-community-client-core mysql-community-server-core
The following NEW packages will be installed:
  mysql-client mysql-common mysql-community-client mysql-community-client-core mysql-community-server
  mysql-community-server-core
0 upgraded, 6 newly installed, 0 to remove and 121 not upgraded.
Need to get 0 B/36.7 MB of archives.
After this operation, 347 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LC_TIME = "ba_RU.UTF-8",
        LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to a fallback locale ("en_US.UTF-8").
locale: Cannot set LC_ALL to default locale: No such file or directory
Preconfiguring packages ...
/usr/bin/locale: Cannot set LC_ALL to default locale: No such file or directory
Selecting previously unselected package mysql-common.
(Reading database ... 82046 files and directories currently installed.)
Preparing to unpack .../mysql-common_8.0.11-1ubuntu16.04_amd64.deb ...
Unpacking mysql-common (8.0.11-1ubuntu16.04) ...
Selecting previously unselected package mysql-community-client-core.
Preparing to unpack .../mysql-community-client-core_8.0.11-1ubuntu16.04_amd64.deb ...
Unpacking mysql-community-client-core (8.0.11-1ubuntu16.04) ...
Selecting previously unselected package mysql-community-client.
Preparing to unpack .../mysql-community-client_8.0.11-1ubuntu16.04_amd64.deb ...
Unpacking mysql-community-client (8.0.11-1ubuntu16.04) ...
Selecting previously unselected package mysql-client.
Preparing to unpack .../mysql-client_8.0.11-1ubuntu16.04_amd64.deb ...
Unpacking mysql-client (8.0.11-1ubuntu16.04) ...
Selecting previously unselected package mysql-community-server-core.
Preparing to unpack .../mysql-community-server-core_8.0.11-1ubuntu16.04_amd64.deb ...
Unpacking mysql-community-server-core (8.0.11-1ubuntu16.04) ...
Selecting previously unselected package mysql-community-server.
Preparing to unpack .../mysql-community-server_8.0.11-1ubuntu16.04_amd64.deb ...
Unpacking mysql-community-server (8.0.11-1ubuntu16.04) ...
Processing triggers for man-db (2.7.5-1) ...
Processing triggers for systemd (229-4ubuntu21.1) ...
Processing triggers for ureadahead (0.100.0-19) ...
Setting up mysql-common (8.0.11-1ubuntu16.04) ...
update-alternatives: using /etc/mysql/my.cnf.fallback to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Setting up mysql-community-client-core (8.0.11-1ubuntu16.04) ...
Setting up mysql-community-client (8.0.11-1ubuntu16.04) ...
Setting up mysql-client (8.0.11-1ubuntu16.04) ...
Setting up mysql-community-server-core (8.0.11-1ubuntu16.04) ...
Setting up mysql-community-server (8.0.11-1ubuntu16.04) ...
locale: Cannot set LC_ALL to default locale: No such file or directory
update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Processing triggers for systemd (229-4ubuntu21.1) ...
Processing triggers for ureadahead (0.100.0-19) ...
root@ubuntu-xenial:~# grep TimeoutSec /lib/systemd/system/mysql*.service/lib/systemd/system/mysql.service:TimeoutSec=600
/lib/systemd/system/mysql@.service:TimeoutSec=600
root@ubuntu-xenial:~#
=========================================

I know, that's why bug was set to verified :-) 

Timeout should be set to 0 in Deb packaging versions of service files too.

(In future we should maybe try to use same servic files for all package formats).

Posted by developer:
 
Fixed in 8.0.18.

On Debian, long InnoDB recovery times at startup could cause systemd
service startup failure. The default systemd service timeout is now
disabled (consistent with RHEL) to prevent this from happening.