2

I have a weird issue with MySQL on a Debian (Jessie) server.

When the server is restarted, systemd will try to start mysqld, but it fails (looks like the port 3306 is already used, but I don't see why it would).

However, if I SSH to the server only minutes later and run sudo systemctl start mysql it will succeed.

The issue can be reproduced each time I restart the server.

Anyone already had this issue, or any guess what could prevent MySQL to start correctly? I could add a delay before starting MySQL after reboot, but I'd like to understand what is going on, and actually I'm not even sure that would fix the issue.

Here is the result of sudo systemctl status mysql -l after reboot:

● mysql.service - LSB: Start and stop the mysql database server daemon
   Loaded: loaded (/etc/init.d/mysql)
   Active: failed (Result: exit-code) since Fri 2017-09-01 21:54:44 CEST; 1min 41s ago
  Process: 581 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

Sep 01 21:54:06 gimli systemd[1]: Starting LSB: Start and stop the mysql database server daemon...
Sep 01 21:54:44 gimli mysql[581]: Starting MySQL database server: mysqld . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . failed!
Sep 01 21:54:44 gimli systemd[1]: mysql.service: control process exited, code=exited status=1
Sep 01 21:54:44 gimli systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
Sep 01 21:54:44 gimli systemd[1]: Unit mysql.service entered failed state.

and the corresponding log from /var/log/mysql/error.log

170901 21:54:16 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
170901 21:54:16 [Note] Plugin 'FEDERATED' is disabled.
170901 21:54:17 InnoDB: The InnoDB memory heap is disabled
170901 21:54:17 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170901 21:54:17 InnoDB: Compressed tables use zlib 1.2.8
170901 21:54:17 InnoDB: Using Linux native AIO
170901 21:54:17 InnoDB: Initializing buffer pool, size = 128.0M
170901 21:54:17 InnoDB: Completed initialization of buffer pool
170901 21:54:17 InnoDB: highest supported file format is Barracuda.
170901 21:54:22  InnoDB: Waiting for the background threads to start
170901 21:54:23 InnoDB: 5.5.57 started; log sequence number 351234412
170901 21:54:23 [Note] Server hostname (bind-address): '192.168.1.14'; port: 3306
170901 21:54:23 [Note]   - '192.168.1.14' resolves to '192.168.1.14';
170901 21:54:23 [Note] Server socket created on IP: '192.168.1.14'.
170901 21:54:23 [ERROR] Can't start server: Bind on TCP/IP port: Cannot assign requested address
170901 21:54:23 [ERROR] Do you already have another mysqld server running on port: 3306 ?
170901 21:54:23 [ERROR] Aborting

170901 21:54:23  InnoDB: Starting shutdown...
170901 21:54:24  InnoDB: Shutdown completed; log sequence number 351234412
170901 21:54:24 [Note] /usr/sbin/mysqld: Shutdown complete

However, if I run sudo systemctl start mysql manually, it works correctly:

170901 22:01:36 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
170901 22:01:36 [Note] Plugin 'FEDERATED' is disabled.
170901 22:01:36 InnoDB: The InnoDB memory heap is disabled
170901 22:01:36 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170901 22:01:36 InnoDB: Compressed tables use zlib 1.2.8
170901 22:01:36 InnoDB: Using Linux native AIO
170901 22:01:36 InnoDB: Initializing buffer pool, size = 128.0M
170901 22:01:36 InnoDB: Completed initialization of buffer pool
170901 22:01:36 InnoDB: highest supported file format is Barracuda.
170901 22:01:36  InnoDB: Waiting for the background threads to start
170901 22:01:37 InnoDB: 5.5.57 started; log sequence number 351234412
170901 22:01:37 [Note] Server hostname (bind-address): '192.168.1.14'; port: 3306
170901 22:01:37 [Note]   - '192.168.1.14' resolves to '192.168.1.14';
170901 22:01:37 [Note] Server socket created on IP: '192.168.1.14'.
170901 22:01:37 [Note] Event Scheduler: Loaded 0 events
170901 22:01:37 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.57-0+deb8u1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)

To my knowledge, the systemd script has not been modified (it's the one that comes with the Debian mysql package), and as far as I know, it never worked correctly. The 3306 port is not supposed to be used by another process.

2

Try to SSH into the server right after rebooting and running netstat -lp | grep 3306 as root to check what program is actually listening on that port so you can further debug the issue.

  • Hi, thanks for the hint ; I haven't tried because in the mean time I found the issue (I mean I found the issue and then came here and saw your answer). And I don't think your idea would have worked, though that would have been a hint in itself, because apparently the problem was that DHCP did not complete in time, so i don't think I could have ssh to see that (see my answer) – youen Sep 2 '17 at 15:55
  • I see! My suggestion was aiming precisely at figuring out whether the right services were running. I'm glad you could fix it! – Oxfist Sep 2 '17 at 15:58
2

Apparently the problem was that the network interface was not up at the time mysql starts. This is probably related to DHCP taking a bit of time.

At least, now that I set the interface to static IP, I don't observe the mysql issue anymore (again, reproducible on every startup), so that solved the problem.

Here is the procedure to set a static IP on debian:

  1. Make sure you have physical access to your server (power button, keyboard, screen) before attempting to mess with the network since you could loose the ssh ability
  2. edit /etc/network/interfaces
  3. replace iface eth0 inet dhcp by:

    iface eth0 inet static
        address 192.168.1.14
        network 192.168.1.0
        netmask 255.255.255.0
        broadcast 192.168.1.255
        gateway 192.168.1.1
    
  4. reboot (note: restarting the network should be enough, for example with systemctl restart networking, but in my case I lost connection but couldn't reconnect without restarting the server with the power button)

In this example I assign the static IP 192.168.1.14 and gateway 192.168.1.1

Of course this is only a workaround, the real issue being that mysql should wait for the network interface to be up, but I don't know systemd well enough to do that.

  • I'm afraid, Jessie does not ship "proper" systemd service file--instead, systemd generates one on the fly from the SYSV-init file /etc/init.d/mysql. So I have really no easy idea of what to do. One solution could possibly be to copy the generated systemd service file somewhere under /etc/systemd, hack it to make it start MySQL after the network is "fully up" (systemd can do that), issue the systemctl daemon-reload command, disable and enable the mysql service and see how it works. – kostix Sep 4 '17 at 14:53
  • Hm. Actually, the generated service file has Wants=network-online.target in it, so looks like systemd what it's supposed to do w.r.t. the availability of the network... – kostix Sep 4 '17 at 14:57
  • @kostix What version of debian? I have a /etc/init.d/mysql script (which apparently also works when using systemctl commands). So actually it's not a systemd script, and I don't think it can express dependencies? – youen Sep 5 '17 at 9:49
  • 1
    Jessie. Please see if you have /run/systemd/generator.late/mysql.service around -- that's what systemd-sysv-generator produces from the rc-scripts (those /etc/init.d/whatever files). – kostix Sep 5 '17 at 10:11
  • You're right, I have this file, and it has a line Wants=network-online.target. Yet, it did not work before I set my IP as static, I have no explanation. I'll test again with DHCP to double check this is actually the change that makes the difference. – youen Sep 5 '17 at 13:54

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.