Fixing MySQL 5.7 “Can’t open and lock privilege tables” Error in Dockerized Ubuntu 16.04


2 views

When attempting to run MySQL 5.7.19 in a Docker container based on Ubuntu 16.04, I encountered a particularly stubborn error during service startup:

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

This occurs after a fresh installation using:

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

The installation completes without errors, but MySQL fails to start with the privilege tables error. Even more puzzling is the subsequent behavior when trying to manually repair tables:

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

After extensive testing, here's the Dockerfile configuration that resolves the issue:

FROM ubuntu:16.04

RUN apt-get update && \
    DEBIAN_FRONTEND=noninteractive apt-get install -y mysql-server && \
    rm -rf /var/lib/apt/lists/*

# Initialize MySQL properly
RUN mysqld --initialize-insecure --user=mysql

# Set ownership of mysql files
RUN chown -R mysql:mysql /var/lib/mysql /var/run/mysqld

# Configure MySQL to work with Docker
RUN echo '[mysqld]\nskip-host-cache\nskip-name-resolve' > /etc/mysql/conf.d/docker.cnf

CMD ["mysqld_safe"]

The critical steps that make this work:

  • Proper initialization of the data directory with mysqld --initialize-insecure
  • Correct file ownership for the MySQL user
  • Configuration adjustments for containerized environments
  • Using mysqld_safe as the entry point

To confirm the solution works:

docker build -t fixed-mysql .
docker run -d --name mysql-container fixed-mysql
docker exec -it mysql-container mysql -e "SHOW DATABASES;"

You should see the standard MySQL databases listed without errors.

For those who prefer official MySQL images, consider using:

FROM mysql:5.7
COPY custom.cnf /etc/mysql/conf.d/

This bypasses the Ubuntu package issues entirely while providing more control over configuration.

The original error occurs because the privilege tables weren't properly initialized for the storage engine being used. The solution ensures:

  • Proper table initialization
  • Correct filesystem permissions
  • Container-appropriate configuration
  • Clean separation from host system dependencies

When trying to run MySQL 5.7.19 in a Docker container based on Ubuntu 16.04, you might encounter this frustrating error:

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

This typically occurs right after installation when attempting to start the MySQL service.

The root cause appears to be permission and storage engine problems with system tables during the initialization phase. MySQL 5.7 made significant changes to the default storage engine and initialization process compared to MySQL 5.5.

Solution 1: Proper Initialization Sequence

Here's a working Dockerfile approach:

FROM ubuntu:16.04

RUN apt-get update && \
    DEBIAN_FRONTEND=noninteractive apt-get install -y mysql-server && \
    rm -rf /var/lib/apt/lists/*

RUN mkdir -p /var/run/mysqld && \
    chown mysql:mysql /var/run/mysqld && \
    chmod 777 /var/run/mysqld

VOLUME /var/lib/mysql

COPY my.cnf /etc/mysql/my.cnf

RUN mysqld --initialize-insecure --user=mysql && \
    service mysql start && \
    mysql_upgrade -uroot --force && \
    service mysql restart

CMD ["mysqld_safe"]

Solution 2: Manual Table Repair

If you're already stuck with corrupted tables, try this sequence:

# Stop mysql if running
service mysql stop

# Start mysql in recovery mode
mysqld --skip-grant-tables --skip-networking &

# Repair tables
mysqlcheck -uroot --repair --all-databases

# Restart normally
service mysql restart

Ensure your my.cnf contains these critical settings:

[mysqld]
user = mysql
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
log_error = /var/log/mysql/error.log
innodb_force_recovery = 1

The issue stems from several Docker-specific factors:

  • Filesystem permissions not being preserved correctly
  • MySQL's initialization process being interrupted
  • Missing required directories (/var/run/mysqld)
  • Storage engine incompatibilities during upgrades

To avoid these issues completely:

# Use the official MySQL image instead
docker run --name mysql57 \
  -e MYSQL_ROOT_PASSWORD=secret \
  -v /my/own/datadir:/var/lib/mysql \
  -d mysql:5.7

# Or if you must build from Ubuntu:
FROM ubuntu:16.04
RUN apt-get update && apt-get install -y mysql-server-5.7
RUN mkdir -p /var/lib/mysql /var/run/mysqld
RUN chown -R mysql:mysql /var/lib/mysql /var/run/mysqld
VOLUME /var/lib/mysql
EXPOSE 3306
CMD ["mysqld"]