14

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?

  • 1
    mysqld --skip-grant-tables --skip-networking seems to work – Wolfgang Fahl Aug 26 '17 at 12:13
  • mkdir /var/run/mysqld;chown mysql /var/run/mysqld might also be needed. – Wolfgang Fahl Aug 26 '17 at 14:24
29

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.

  • 2
    Same problem here. I had a working a two-stage build, and the "parent" Dockerfile already runs this chmod command. The build succeeds when I run it on a remote Ubuntu server, but fails when I run it on my local machine (OS X). Adding the chmod command to the child Dockerfile fixed the problem. Strange. – senderle Nov 7 '17 at 20:27
  • 2
    Thank you! I knew someone else out there had to have similar use case as my own. – threed Jul 9 '18 at 22:14
  • 1
    @senderle just found this thread as well. "Build once, deploy anywhere" they said – duhaime Sep 19 at 1:31
8

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

  • 1
    This solved my issue in Ubuntu 16:04. I had configured previously the overlay2 storage driver in docker. Thanks. – Havok Dec 7 '17 at 5:23
  • This prevented docker from restarting on OSX :( – duhaime Sep 19 at 1:36
7

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:

  • 1
    This is horrible news. I wonder why docker isn't fixed. – Wolfgang Fahl Jan 18 '18 at 17:44
  • 2
    Honestly? Me neither! I mean this has to affect a lot of applications, because it is really a basic file operation. Of course the problem occurs only under certain conditions, but they are not that far away... – Murmel Jan 18 '18 at 17:49
1

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 ]
1

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

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.