MySQL Error 1030: A Container Permission Postmortem

I thought it was the storage engine's fault, but it turned out that I knocked it with my own hands two days ago. chown.

Let’s talk about the conclusion first

If you encounter this in Docker deployed MySQL:

The query is normal, but an error is reported when creating the table. 1030 (168)
There is sufficient disk space, the SQL syntax is OK, and there are no residual files.

The first reaction is not to check MySQL, first check the directory permissions.

Most likely you are on the host machine chown The mounting directory is missing, resulting in a mismatch between the host UID and the UID in the container, and the MySQL user in the container "can read but not write".

Problem site

Environment: Ubuntu + Docker deploy MySQL, data is mounted on the host /opt/resumeai/docker/resumeai/mysql/data.

Executing table creation SQL, an error is reported:

[HY000][1030] Got error 168 - 'Unknown (generic) error from engine' from storage engine

The strange thing is:SELECT Normally, data in existing tables can be queried, but new tables cannot be created.

Troubleshooting process

Step 1: Is the disk full?

df -h   # Usage rate 30%, plenty of space
df -i   # inode Usage rate 9%, No problem

exclude.

Step 2: Have a problem with SQL syntax?

The same SQL runs fine in the local environment, and the table name has no special characters.

exclude.

Step 3: Are there any remaining physical file conflicts?

ls /opt/resumeai/docker/resumeai/mysql/data/<Library name>/ | grep release_note
# No output

no residue .ibd File, exclude.

Step 4: View container logs

docker logs --tail 50 <Container name>

Permission denied related errors appeared in the log.The direction has changed - it's not a MySQL problem, it's an operating system permissions problem.

Key turning point: Why "can read but not write"?

Let’s take a look at the two operations:

operate	What are you doing essentially?	What permissions are needed
`SELECT` Query	Read existing `.ibd` document	File read permission (`r`)
`CREATE TABLE` Create table	Create a new one in the directory `.ibd` document	Directory write permission (`w`)

At that time, the permissions of directories and files were as follows:

document:644(-rw-r--r--) → Others have read permission, so SELECT Can run
Table of contents:755(drwxr-xr-x) → Only the owner can write, others do not have write permission, so CREATE TABLE fail

So who are the "others"? This is the root cause.

Root cause confirmation

Recalling what operations I have done recently——In order to save having to type every time sudo For trouble, I executed:

sudo chown -R ubuntu:ubuntu /opt/resumeai

This line of command changes the owner of the entire data directory to ubuntu User (UID 1000).

But the official image of MySQL in the Docker container mysql User UID is 999.

Linux does not look at the user name to determine permissions, but the numeric UID.

The MySQL process (UID 999) in the container comes to the host directory (owner UID 1000) and is treated as a strange "other person" - with read permissions but no write permissions.

This is the real reason why "it can be checked but not built".

solution

# 1. Check the container first mysql User's UID
docker exec -it <Container name> id -u mysql
# output 999

# 2. Change the data directory back to the container MySQL of UID
sudo chown -R 999:999 /opt/resumeai/docker/resumeai/mysql/data

# 3. Restart container
docker restart <Container name>

Re-execute the table creation SQL and it passes.

If your MySQL image is not official (such as MariaDB or a custom image), the UID may not be 999. Check first and then change it:

DOCKER_UID=$(docker exec <Container name> id -u mysql)
sudo chown -R ${DOCKER_UID}:${DOCKER_UID} /path/to/data

What this taught me

3 core understandings of Linux permissions

1. Linux recognizes numeric UID, not username.

on the host ubuntu(UID 1000) and in the container mysql(UID 999), in the eyes of the system, they are two completely different people. Even if you change the user name in the container to ubuntu, the UID is different and there is still no permission.

2. Directory permissions ≠ file permissions.

File permissions control "whether the contents of this file can be read/written"; directory permissions control "whether files can be created, deleted, and renamed in this directory." The two are independent.

3. The permission of Docker to mount the volume is determined by the UID of the host file.

The permissions seen by the process in the container are not defined by the container itself, but are determined by the UID/GID of the file/directory on the host. This is the easiest trap for Docker data volumes.

How to avoid it in the future

Don't want to lose every time sudo? The correct approach is to configure password-free, not dynamic directory permissions:

sudo visudo
# Add to: 
ubuntu ALL=(ALL) NOPASSWD: /usr/bin/docker

In this way, docker commands can be executed without password and the permission system of the data directory will not be destroyed.

Golden rules for Docker volume permissions:

The UID of the process in the container = the UID of the owner of the host data directory

Remember this and 90% of Docker permission issues will not be encountered.

Summarize

Dimensions	content
question	MySQL creates a table and reports 1030, but the query is normal
root cause	`chown` The directory UID has been changed and does not match the MySQL UID in the container.
solution	Change the directory owner back to the in-container UID (usually 999)
lesson	Don't be casual `chown` Docker mounting directory; use `sudo` Password-free substitution

After an hour of troubleshooting, the root cause was a line I typed half a year ago. chown. The more "I thought there was no problem at the time" the operation is more likely to be the source of subsequent problems.

I hope this article will help you avoid a detour.

If this article is helpful to you, please give it a like and leave 🙏 Have you ever experienced pitfalls related to Docker permissions? Can you tell us about it in the comment area?