Mysql server take 10 minutes to stop without message after time drift

I _think_ adding After=ntp.service would be safe (does not imply Requires), but need to learn more about the ordering and dependency system in systemd first.

I'm not sure it's reasonable to adjust service startups. Following that logic, every package would have to start after ntp.service, and there should be a more general solution to that problem.

Can MySQL be adjusted to be a little more robust to the time changing when it is idle? I understand that in the general case this isn't really solvable though, which is exactly why ntp skews time. It is essential to have the system time on a server correct, and I don't think every case of things screwing up are reasonable to fix. It is reasonable to improve robustness if a fix is trivial, though.

Status changed to 'Confirmed' because the bug affects multiple users.

I go this problem, however, adding After=ntp.service wouldn't fix it.

I also have this problem with Ubuntu 16.04.2 LTS:

| $ lsb_release --all
| No LSB modules are available.
| Distributor ID: Ubuntu
| Description: Ubuntu 16.04.2 LTS
| Release: 16.04
| Codename: xenial
|
| $ dmidecode -s system-product-name
| VirtualBox

After a reboot the activation time of MySQL is one hour ahead.

1. If I wait one hour after reboot, I can shutdown MySQL instantly:

| $ systemctl status mysql.service
| ...
| Active: active (running) since Fri 2017-02-10 18:20:08 CET; 59min left
| ...
|
| $ date
| Fri Feb 10 17:20:54 CET 2017
|
| $ sleep 60m
|
| $ date
| Fri Feb 10 18:21:02 CET 2017
|
| $ systemctl status mysql.service
| ...
| Active: active (running) since Fri 2017-02-10 18:20:08 CET; 57s ago
| ...
|
| $ time systemctl stop mysql.service
|
| real 0m4.928s
| user 0m0.028s
| sys 0m0.008s

2. If I try it earlier, the shutdown fails after 10 minutes and MySQL gets killed by systemd with SIGKILL.

3. If I increase the systemd timeout of MySQL to two hours, then the shutdown successfully ends after one hour. The attached error-20170210.log contains this case and also the time jump:

| 2017-02-10T15:22:26.962776Z 0 [Note] /usr/sbin/mysqld: ready for connections.
| ...
| 2017-02-10T14:23:52.465978Z 0 [Note] Giving 0 client threads a chance to die gracefully

4. As suggested, I tried to start MySQL after the ntp.service, but it didn't helped:

| $ apt-get install ntp
|
| $ grep ^After /lib/systemd/system/mysql.service
| After=ntp.service
|
| $ systemctl show mysql.service | grep ^After
| After=ntp.service basic.target system.slice systemd-journald.socket sysinit.target

5. I also tried to start MySQL after the systemd-timesyncd.service, but it also didn't helped:

| $ cat /lib/systemd/system/systemd-timesyncd.service.d/disable-with-time-daemon.conf
| ...
| ConditionFileIsExecutable=!/usr/sbin/ntpd
| ...
|
| $ dpkg --purge ntp
|
| $ grep ^After /lib/systemd/system/mysql.service
| After=systemd-timesyncd.service
|
| $ systemctl show mysql.service | grep ^After
| After=systemd-timesyncd.service system.slice basic.target systemd-journald.socket sysinit.target

6. Is there anything else I can try or do?

7. Maybe the importance of this issue should be raised, because killing MySQL during its shutdown with SIGKILL feels a bit risky.

I solved my above time jump problem by installing the VirtualBox guest additions as described here: https://www.virtualbox.org/manual/ch04.html

Any fix to this problem yet?

I don't think there will be a fix to this problem in Ubuntu until it is fixed upstream. I suggest you ask there.

Confirmed, MySQL stops sometimes 10 minutes, there is no setting to change this ?

Got the same issue, found a possible explanation: my cloud provider store time in local timezone (earlier than UTC); at startup MySQL boot first, then NTP, which updates the time to UTC; therefore, MySQL literally "started in the future" (sounds interesting).

I can hotfix this by setting the timezone to UTC:

dpkg-reconfigure tzdata

After that MySQL can be stopped immediately. Even after I switch timezone back to local time, it can still stop or restart without problems. After rebooting the server (a KVM), I have to do this again unless the NTP timezone issue is fixed.

BTW, @darek334:

> MySQL stops sometimes 10 minutes

10 minutes is the timeout period defined in system/multi-user.target.wants/mysql.service (TimeoutSec=600).

Hi,
Same happening on ubuntu server 18.04. Fresh install , just apache and mysql without ntp. Machine is a VM in esxi 6.5. During restart “A stop job is running to for Mysql Community Server” and 10 min of waiting.

I was trying tzdata reconfigure and ntp service, but it's not permanent.

Any solution soon ?
Best regards

I think the problem can be solved with:

* recent systemd fixes https://github.com/systemd/systemd/issues/5097 (with a workaround for older versions)
* fixing mysql.service to use After=time-sync.target and Wants=time-sync.target

Regards.

> * fixing mysql.service to use After=time-sync.target and Wants=time-sync.target

As far as I understand this is unsuitable to be done by default since not all systems will necessarily ever reach "time synced", for example if they're intentionally being used offline.

But this does seem like a reasonable workaround for sysadmins to use locally when using MySQL in broken environments. In this case, you can override packaged service behaviour locally in /etc (see systemd-system.conf(5)).

I just made the test to be sure: using Wants= does not prevent MySQL to start event if the system never reaches “time-sync.target”.

I added an ntpdate.service:

  # /etc/systemd/system/ntpdate.service
  [Unit]
  Description=Force synchronisation of time
  ConditionPathExists=/etc/default/ntpdate
  DefaultDependencies=no
  Conflicts=shutdown.target
  After=network-online.target
  Before=time-sync.target shutdown.target
  Wants=network-online.target time-sync.target

  [Service]
  Type=oneshot
  Environment="NTPSERVERS=pool.ntp.org"
  EnvironmentFile=/etc/default/ntpdate
  ExecStart=/usr/sbin/ntpdate -v $NTPSERVERS
  RemainAfterExit=yes

  [Install]
  WantedBy=sysinit.target

And an override for MySQL:

  # /etc/systemd/system/mysql.service.d/override.conf
  [Unit]
  After=network-online.target time-sync.target
  Wants=network-online.target time-sync.target

ntpdate.service is failed:

  systemctl status ntpdate.service
  ● ntpdate.service - Force synchronisation of time
     Loaded: loaded (/etc/systemd/system/ntpdate.service; enabled; vendor preset: enabled)
     Active: failed (Result: exit-code) since Mon 2018-10-15 16:51:25 CEST; 40s ago
    Process: 1129 ExecStart=/usr/sbin/ntpdate -v $NTPSERVERS (code=exited, status=1/FAILURE)
   Main PID: 1129 (code=exited, status=1/FAILURE)

But MySQL is started:

  systemctl status mysql.service
  ● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
    Drop-In: /etc/systemd/system/mysql.service.d
             └─override.conf
     Active: active (running) since Mon 2018-10-15 16:51:27 CEST; 3min 17s ago
    Process: 1600 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid (code=exited, status=0/SUCCESS)
    Process: 1550 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 1602 (mysqld)
      Tasks: 30 (limit: 4915)
     CGroup: /system.slice/mysql.service
             └─1602 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

Regards.

I'm just going by https://github.com/systemd/systemd/issues/5097#issuecomment-276787961

Yes, it will delay the start of MySQL server but the consequence of going backward in time after MySQL is started are important enough to me.

Regards.

https://bugs.mysql.com/bug.php?id=93708

" Fixed as of the upcoming 5.7.27, 8.0.17 release, and here's the changelog entry "

So, judging by:

 mysql-server | 5.7.31-0ubuntu0.18.04.1 | bionic-updates | all

and

 mysql-server | 8.0.20-0ubuntu0.19.10.1 | eoan-updates | all

and

 mysql-server | 8.0.21-0ubuntu0.20.04.3 | focal-updates | all

this is Fix Released in all 3 of them.