Fatal error: Can't open and lock privilege tables: Table storage engine for 'user' doesn't have this option

Solution 1:

Ran into the same problem today. I'm running the MySQL service during docker build for the unit tests and upgrading to MySQL CE 5.7.19 from MariaDB broke the build. What did solve the issue for me was running chown -R mysql:mysql /var/lib/mysql /var/run/mysqld each time before starting the mysql service.

So my Dockerfile looks like this now:

RUN chown -R mysql:mysql /var/lib/mysql /var/run/mysqld && \
    service mysql start && \
    mvn -q verify site

Hope this helps.

Solution 2:

Workaround

find /var/lib/mysql -type f -exec touch {} \; && service mysql start

---

Problem description

The underlying problem as stated by aalexgabi is due to the implementation of the POSIX standards of OverlayFS:

open(2): OverlayFS only implements a subset of the POSIX standards. This can result in certain OverlayFS operations breaking POSIX standards. One such operation is the copy-up operation. Suppose that your application calls fd1=open("foo", O_RDONLY) and then fd2=open("foo", O_RDWR). In this case, your application expects fd1 and fd2 to refer to the same file. However, due to a copy-up operation that occurs after the second calling to open(2), the descriptors refer to different files. The fd1 continues to reference the file in the image (lowerdir) and the fd2 references the file in the container (upperdir). A workaround for this is to touch the files which causes the copy-up operation to happen. All subsequent open(2) operations regardless of read-only or read-write access mode will be referencing the file in the container (upperdir).

Reference:

• github.com - docker/for-linux - Issues - MySQL does not start with overlay2 and overlay but starts with aufs
• github.com - drupal-vm - Issues - Docker Image is not starting MySQL - 'Can't open and lock privilege tables: error 140'

---

Solution 3:

I confirmed the error on overlayfs (overlay2) that is the default on Docker for Mac. The error happens when starting mysql on the image, after creating a image with mysql.

2017-11-15T06:44:22.141481Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table storage engine for 'user' doesn't have this option

Switching to "aufs" solved the issue. (On Docker for Mac, the "daemon.json" can be edited by choosing "Preferences..." menu, and selecting "Daemon" tab, and selecting "Advanced" tab.)

/etc/docker/daemon.json :

{
  "storage-driver" : "aufs",
  "debug" : true,
  "experimental" : true
}

Ref:

https://github.com/moby/moby/issues/35503

https://qiita.com/Hige-Moja/items/7b1208f16997e2aa9028

---

Solution 4:

Here's an answer I don't see here yet.

Add this to your dockerfile: VOLUME /var/lib/mysql

This will cause the /var/lib/mysql folder to use the native filesystem rather than overlayFS. This circumvents this issue.

This is the solution the official mysql docker image uses to deal with this as you can see here: https://github.com/docker-library/mysql/blob/9d1f62552b5dcf25d3102f14eb82b579ce9f4a26/5.7/Dockerfile