Docker Image is not starting MySQL - 'Can't open and lock privilege tables: error 140'

[geerlingguy]

Issue Type

• Bug Report

Your OS

• macOS (10.12.6)

Full console output / Summary

After a docker-compose up, using the Drupal VM docker image doesn't have MySQL running—see the output below (after logging into the Drupal VM container with docker exec -it [container] /bin/bash):

root@2de0a967ad9a:/# systemctl status mysql
● mysql.service - LSB: Start and stop the mysql database server daemon
   Loaded: loaded (/etc/init.d/mysql)
   Active: failed (Result: exit-code) since Wed 2017-08-02 14:03:28 UTC; 1min 58s ago
  Process: 48 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

Aug 02 14:02:57 2de0a967ad9a systemd[1]: Starting LSB: Start and stop the mysql database server daemon...
Aug 02 14:03:28 2de0a967ad9a /etc/init.d/mysql[1136]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in
Aug 02 14:03:28 2de0a967ad9a /etc/init.d/mysql[1136]: [61B blob data]
Aug 02 14:03:28 2de0a967ad9a /etc/init.d/mysql[1136]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Aug 02 14:03:28 2de0a967ad9a /etc/init.d/mysql[1136]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
Aug 02 14:03:28 2de0a967ad9a /etc/init.d/mysql[1136]: 
Aug 02 14:03:28 2de0a967ad9a mysql[48]: Starting MySQL database server: mysqld . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . failed!
Aug 02 14:03:28 2de0a967ad9a systemd[1]: mysql.service: control process exited, code=exited status=1
Aug 02 14:03:28 2de0a967ad9a systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
Aug 02 14:03:28 2de0a967ad9a systemd[1]: Unit mysql.service entered failed state.

Checking MySQL's error log, I find:

170802 14:02:59 InnoDB: 5.5.57 started; log sequence number 1595926
170802 14:02:59 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
170802 14:02:59 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
170802 14:02:59 [Note] Server socket created on IP: '0.0.0.0'.
170802 14:02:59 [ERROR] Fatal error: Can't open and lock privilege tables: Got error 140 from storage engine

I've confirmed this with two projects—one where just last week everything worked fine.

The only other reference I could find with a similar error output is: docker/for-linux#72

[geerlingguy]

I wonder if I can try switching to another filesystem type to see if that helps... currently if I run docker info I get:

Server Version: 17.07.0-ce-rc1
Storage Driver: overlay2
 Backing Filesystem: extfs
 Supports d_type: true
 Native Overlay Diff: true

And apparently the switch from aufs to overlay2 happened sometime in the past year (not sure exactly when); see docker/for-mac#516

I'm also currently running the Docker Edge release. I may switch back to CE stable and see if that fixes things.

[geerlingguy]

Well, never mind about switching the storage driver... https://docs.docker.com/engine/userguide/storagedriver/selectadriver/#docker-ce mentions:

Docker for Mac and Docker for Windows
Docker for Mac and Docker for Windows are intended for development, rather than production. Modifying the storage driver on these platforms is not supported.

[geerlingguy]

Downgrading to CE (non-Edge) didn't make a difference. I'm going to try building a Drupal VM image locally and see if that image works (maybe it's something happening during the build on Docker Hub?

[geerlingguy]

Building a fresh instance locally with Docker non-Edge still resulted in MySQL not starting.

[geerlingguy]

Mounting a data volume for /var/lib/mysql seems to fix this issue in local builds—and is likely a recommended approach here, especially since you can leave the data volume in place after the container is killed/removed (this way you could rebuild the containers as needed without nuking the database data).

So, something like:

    volumes:
      ...
      - /var/lib/mysql

Suggestion seen in docker/for-linux#72

[geerlingguy]

That fix is good enough for me right now. I'm also going to be bumping my Drupal VM image to use Debian 9 instead of Debian 8 in a few minutes... so that might fix the other case (no special volume) as well. Might test that tonight before closing this ticket.

[aalexgabi]

If someone stumbles upon this here is the explanation and a workaround: docker/for-linux/issues/72

[oxyc]

I just tried out some of the docker stuff for the first time and while running composer docker-bake I got:

TASK [geerlingguy.mysql : Disallow root login remotely] ************************
failed: [localhost] (item=DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1')) => {"changed": false, "cmd": ["mysql", "-NBe", "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1')"], "delta": "0:00:00.114815", "end": "2017-09-23 17:00:44.315857", "failed": true, "item": "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1')", "msg": "non-zero return code", "rc": 1, "start": "2017-09-23 17:00:44.201042", "stderr": "ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)", "stderr_lines": ["ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)"], "stdout": "", "stdout_lines": []}

It was fixed by adding that /var/lib/mysql volume to the bake script. @geerlingguy should I open up an issue and patch it or am I doing something wrong / is this mac specific?