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

Question

This error message shows up when I use ubuntu 16.04 and the latest mysql 5.7.19-0ubuntu0.16.04.1 in a Docker image.

What could be done to fix this?

To reproduce the error

1. Get the Dockerfile:

   FROM ubuntu:16.04
   
   RUN apt update
   RUN DEBIAN_FRONTEND=noninteractive apt install -y mysql-server
   

   (also available here)

2. Build and run:

   docker build -t mysqlfail . 
   docker run -it mysqlfail tail -1 /var/log/mysql/error.log
   

   would have been shown the following error log:

   2017-08-26T11:48:45.398445Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

   Which was exactly what we wanted: a mysql with no root password set yet.

3. In the past (ubuntu 14.04 / mysql 5.5) a service mysql start was possible. Now if you try this it fails

   docker run -it mysqlfail service mysql start
    * Starting MySQL database server mysqld    
     No directory, logging in with HOME=/  
                                                                           [fail]
   

   and /var/log/mysql/error.log contains a line:

   2017-08-26T11:59:57.680618Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table storage engine for 'user' doesn't have this option

---

build log (for the complete Dockerfile)

Sending build context to Docker daemon   2.56kB
Step 1/4 : FROM ubuntu:16.04
 ---> ebcd9d4fca80
...
Step 4/4 : RUN service mysql start
 ---> Running in 5b899739d90d
 * Starting MySQL database server mysqld
   ...fail!
The command '/bin/sh -c service mysql start' returned a non-zero code: 1

---

weird continuation

After experiments as outlined in my answer attempt, I created a shell script that does a

select count(*)

query on each table in the mysql space three times in a row (because experiments show that on some tables the query will fail exactly twice :-( ).

Then a

mysql_upgrade   

and the

service mysql restart

is tried. In the Dockerfile the script is made available via

COPY mysqltest.sh .

The trials with this script give weird/crazy results.

1. For the Docker environment the start still fails

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

2. Running the script

   sh mysqltest.sh root
   

   in the docker environment leads to

   2017-08-27T09:12:47.021528Z 12 [ERROR] /usr/sbin/mysqld: Table './mysql/db' is marked as crashed and should be repaired
   2017-08-27T09:12:47.050141Z 12 [ERROR] Couldn't repair table: mysql.db
   2017-08-27T09:12:47.055925Z 13 [ERROR] /usr/sbin/mysqld: Table './mysql/db' is marked as crashed and should be repaired
   2017-08-27T09:12:47.407700Z 54 [ERROR] /usr/sbin/mysqld: Table './mysql/proc' is marked as crashed and should be repaired
   2017-08-27T09:12:47.433516Z 54 [ERROR] Couldn't repair table: mysql.proc
   2017-08-27T09:12:47.440695Z 55 [ERROR] /usr/sbin/mysqld: Table './mysql/proc' is marked as crashed and should be repaired
   2017-08-27T09:12:47.769485Z 81 [ERROR] /usr/sbin/mysqld: Table './mysql/tables_priv' is marked as crashed and should be repaired
   2017-08-27T09:12:47.792061Z 81 [ERROR] Couldn't repair table: mysql.tables_priv
   2017-08-27T09:12:47.798472Z 82 [ERROR] /usr/sbin/mysqld: Table './mysql/tables_priv' is marked as crashed and should be repaired
   2017-08-27T09:12:47.893741Z 99 [ERROR] /usr/sbin/mysqld: Table './mysql/user' is marked as crashed and should be repaired
   2017-08-27T09:12:47.914288Z 99 [ERROR] Couldn't repair table: mysql.user
   2017-08-27T09:12:47.920459Z 100 [ERROR] /usr/sbin/mysqld: Table './mysql/user' is marked as crashed and should be repaired

What is going on here to cause this strange behavior?

Answer 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.

Answer 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'

Answer 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

Answer 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

Answer 5

This might not quite be the solution yet. Anyways it might point others to a "proper" answer

The docker bash session log below shows a sequence of steps that lead to strange errors and finaly being able to start the mysql daemon properly in the docker environment.

Trying to start the daemon in this session fails twice - once due to the mysql.user table and once due to the mysql.db table. Running the mysql daemon with --skip-grant-tables works but there is also problems with simple select * from commands on these tables.

Strangely doing two simple queries:

select host,user from mysql.user;
select user from mysql.db

and then killing the daemon to start it properly with

service mysql start

seems to work. I'll now try to automate this as a workaround. I am still seeking for a "proper" solution to the problem and some clue what the reason for this strange behavior is.

Dockerfile

#*********************************************************************
#
# Dockerfile for https://serverfault.com/questions/870568/2017-08-26t113924-509100z-0-error-fatal-error-cant-open-and-lock-privilege
#
#*********************************************************************

# Ubuntu image
FROM ubuntu:16.04

# 
# Maintained by Wolfgang Fahl / BITPlan GmbH http://www.bitplan.com
# 
MAINTAINER Wolfgang Fahl info@bitplan.com

RUN \
 export DEBIAN_FRONTEND=noninteractive;apt-get update -y && apt-get install -y \
        vim \
    mysql-server 

RUN mkdir /var/run/mysqld;chown mysql /var/run/mysqld
WORKDIR /var/log/mysql

build log

docker build .

Sending build context to Docker daemon  8.704kB
Step 1/5 : FROM ubuntu:16.04
 ---> ebcd9d4fca80
Step 2/5 : MAINTAINER Wolfgang Fahl info@bitplan.com
 ---> Using cache
 ---> b84df9d5de50
Step 3/5 : RUN export DEBIAN_FRONTEND=noninteractive;apt-get update -y && apt-get install -y         vim    mysql-server
 ---> Using cache
 ---> b51bd2bb172c
Step 4/5 : RUN mkdir /var/run/mysqld;chown mysql /var/run/mysqld
 ---> Using cache
 ---> 5b7455fede6b
Step 5/5 : WORKDIR /var/log/mysql
 ---> c4c333e811ab
Removing intermediate container cfc49e460c96
Successfully built c4c333e811ab

bash session log

docker run -it c4c333e811ab

root@607fa9fe8d98:/var/log/mysql# service mysql start
 * Starting MySQL database server mysqld                                                             No directory, logging in with HOME=/
                                                                                              [fail]
root@607fa9fe8d98:/var/log/mysql# grep ERROR error.log 
2017-08-27T07:56:21.377919Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table storage engine for 'user' doesn't have this option
2017-08-27T07:56:21.378149Z 0 [ERROR] Aborting
mysqld_safe --skip-grant-tables&sleep 2;mysql -u root -p=""
select * from mysql.user;
ERROR 1031 (HY000): Table storage engine for 'user' doesn't have this option
select host,user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | debian-sys-maint |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)
show variables like "%locking%";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| skip_external_locking | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)
root@607fa9fe8d98:/var/log/mysql# pgrep -fla mysql
721 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
1083 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 --log-syslog=1 --log-syslog-facility=daemon --log-syslog-tag=
pkill -f mysql
echo "" > error.log
service mysql start
 * Starting MySQL database server mysqld                                                             No directory, logging in with HOME=/.      [fail]
grep ERROR error.log 
2017-08-27T08:03:12.918047Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table storage engine for 'db' doesn't have this option
2017-08-27T08:03:12.918278Z 0 [ERROR] Aborting
mysqld_safe --skip-grant-tables&sleep 2;mysql -u root -p=""
select * from mysql.db;
ERROR 1031 (HY000): Table storage engine for 'db' doesn't have this option
select user from mysql.db;
+---------------+
| user          |
+---------------+
| mysql.session |
| mysql.sys     |
+---------------+
2 rows in set (0.00 sec)
echo "" > error.log
root@607fa9fe8d98:/var/log/mysql# service mysql start
 * Starting MySQL database server mysqld      [ OK ]

Answer 6

If you are using docker or mariadb docker, create script to fix that bug like this:

    docker rm repair_maria -f
    docker run -d -it \
    --restart always \
    --name repair_maria \
    -e MYSQL_USER=your_user \
    -e MYSQL_ROOT_PASSWORD=your_password \
    -e MYSQL_PASSWORD=your_password1 \
    -e BIND_ADDRESS=0.0.0.0 \
    -v /home/username/bcs/volumes/mysql:/var/lib/mysql \
    -p 3306:3306 \
    your_maria_image /bin/bash

go inside docker:

docker exec -it repair_maria bash
su mysql
/usr/sbin/mysqld --skip-grant-tables --general-log &
exit

update 10/2022

Above of my guide, just create new docker mariadb empty and mount volume to another path, after mariadb come up, just dump mysql.user table into repair.sql file and import into broken mariadb above by script of repair_maria. After import new mysql.user table, just delete repair_maria container and run your original docker script which had error before => it will run perfectly.