In this blog, I will share the steps I took to debug an error ‘ERROR 1030 (HY000): Got error 168 – ‘Unknown (generic) error from engine’ from storage engine’ while creating a table in MySQL on my lab machine.
I attempted to create a table using the following command:
mysql> CREATE TABLE `email` (
-> `email_id` varchar(50) CHARACTER SET latin1 NOT NULL,
-> `email_from` varchar(255) CHARACTER SET latin1 NOT NULL,
-> `created_at` datetime NOT NULL,
-> `sent_at` datetime DEFAULT NULL,
-> PRIMARY KEY (`email_id`),
-> KEY `idx_email_created_at` (`created_at`),
-> KEY `idx_email_sent_at` (`sent_at`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;However, I received the following error message:
ERROR 1030 (HY000): Got error 168 – ‘Unknown (generic) error from engine’ from storage engine
I verified that the disk was functional by running the df -h command, which confirmed that there was enough space on the disk.
[root@ip-172-31-82-182 test]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 3.8G 0 3.8G 0% /dev
tmpfs 3.8G 0 3.8G 0% /dev/shm
tmpfs 3.8G 564K 3.8G 1% /run
tmpfs 3.8G 0 3.8G 0% /sys/fs/cgroup
/dev/mapper/rhel-root 19G 9.0G 11G 48% /
/dev/xvda2 1014M 138M 877M 14% /boot
tmpfs 777M 0 777M 0% /run/user/1000
overlay 19G 9.0G 11G 48% /var/lib/docker/overlay2/3c7a3368393279a5fc542cb3d177dc541d2af40a2e5cdb1e2945d892e032c975/mergedI then turned to the error log, where I found the following error message:
tail /var/log/mysql/mysqld.log
...
2023-04-07T12:52:31.975043Z 14489 [ERROR] [MY-012592] [InnoDB] Operating system error number 13 in a file operation.
2023-04-07T12:52:31.975054Z 14489 [ERROR] [MY-012595] [InnoDB] The error means mysqld does not have the access rights to the directory.
2023-04-07T12:52:31.975063Z 14489 [ERROR] [MY-012126] [InnoDB] Cannot create file './test/email.ibd'hmmm… I see the “[InnoDB] Cannot create file” in MySQL error log and “perror” suggests that is “Permission denied”.
[root@ip-172-31-82-182 test]# perror 13
OS error code 13: Permission denied
MySQL error code MY-000013: Can't get stat of '%s' (OS errno %d - %s)
[root@ip-172-31-82-182 test]#I just decided to take the matter in my hands and try writing a file myself in that directory.
[root@ip-172-31-82-182 test]# su mysql
This account is currently not available.Wait what? This account is currently not available. Not available? Is this the problem? Nope.
[root@ip-172-31-82-182 ~]# cat /etc/passwd | grep mysql
mysql:x:27:27:MySQL Server:/var/lib/mysql:/sbin/nologin
[root@ip-172-31-82-182 ~]#The default shell for the user mentions /sbin/nologin. I just logged in specifying explicit shell.
[root@ip-172-31-82-182 test]# su mysql -s /bin/bash
bash-4.4$ pwd
/var/lib/mysql/test
bash-4.4$ touch x
bash-4.4$ ls -lhtr
total 2.0M-rw-r--r--. 1 mysql mysql 304K Apr 3 14:08 dupl_index_chk.ibd
-rw-r--r--. 1 mysql mysql 112K Apr 3 14:08 mytable.ibd
-rw-r--r--. 1 mysql mysql 112K Apr 3 14:08 user_email_settings.ibd
-rw-r--r--. 1 mysql mysql 112K Apr 3 14:08 t1.ibd
-rw-r--r--. 1 mysql mysql 0 Apr 7 13:55 x
bash-4.4$So, I could touch the file with MySQL user in data directory but I couldn’t do that with MySQL client!
I went on verifying which user MySQL is running with and it is “mysql”
[root@ip-172-31-82-182 test]# ps -ef | grep mysqld
mysql 66740 1 0 Apr04 ? 00:31:19 /usr/libexec/mysqld --basedir=/usrThis time I vim-ed the error log and noted that the errors are for every table:
[root@ip-172-31-82-182 test]# vi /var/log/mysql/mysqld.log
…
2023-04-07T12:58:59.455500Z 0 [System] [MY-010116] [Server] /usr/libexec/mysqld (mysqld 8.0.30) starting as process 1619218
2023-04-07T12:58:59.495718Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-04-07T12:58:59.504768Z 1 [Warning] [MY-012197] [InnoDB] Unable to open './test/user_email_settings.ibd'
2023-04-07T12:58:59.504768Z 1 [Warning] [MY-012197] [InnoDB] Unable to open
…Well, clearly something is blocking MySQL, and who other than firewalls? Immediately turned to SELinux. SELinux is a Linux kernel security module that provides a mechanism for supporting access control security policies.
[root@ip-172-31-82-182 ~]# sestatus
SELinux status: enabled
SELinuxfs mount: /sys/fs/selinux
SELinux root directory: /etc/selinux
Loaded policy name: targeted
Current mode: enforcing
Mode from config file: enforcing
Policy MLS status: enabled
Policy deny_unknown status: allowed
Memory protection checking: actual (secure)
Max kernel policy version: 33
[root@ip-172-31-82-182 ~]#
[root@ip-172-31-82-182 test]# getenforce
EnforcingI checked the SELinux audit log by running the following command:
[root@ip-172-31-82-182 test]# tail -f /var/log/audit/audit.log | grep mysql
The output of the command showed that there were several denials related to MySQL:
type=AVC msg=audit(1617792152.868:636): avc: denied { getattr } for pid=58278 comm="mysqld" path="/var/lib/mysql/test/email.ibd" dev="xvda1" ino=2880727 scontext=system_u:system_r:mysqld_t:s0 tcontext=system_u:object_r:var_lib_t:s0 tclass=file permissive=0
type=AVC msg=audit(1617792152.869:637): avc: denied { create } for pid=58278 comm="mysqld" name="email.ibd" scontext=system_u:system_r:mysqld_t:s0 tcontext=system_u:object_r:var_lib_t:s0 tclass=file permissive=0These denials indicate that MySQL is being denied permission to create or access files in the /var/lib/mysql directory.
To resolve this issue, we need to configure SELinux to allow MySQL to access the necessary files and directories as follows:
[root@ip-172-31-82-182 ~]# chcon -Rv --type=mysql_db_t /var/lib/mysql
changing security context of '/var/lib/mysql/mysql/general_log.CSM'
changing security context of '/var/lib/mysql/mysql/general_log.CSV'
changing security context of '/var/lib/mysql/mysql/slow_log_214.sdi'
changing security context of '/var/lib/mysql/mysql/slow_log.CSM'
changing security context of '/var/lib/mysql/mysql/slow_log.CSV'
changing security context of '/var/lib/mysql/mysql/general_log_213.sdi'
changing security context of '/var/lib/mysql/mysql'
changing security context of '/var/lib/mysql/performance_schema/events_waits_his_85.sdi'
changing security context of '/var/lib/mysql/performance_schema/error_log_83.sdi'
…Though in my lab machine I could just change the SELinux mode to “permissive” which allows operations that are not permitted in enforcing mode and logs those operations to the SELinux audit log.
[root@ip-172-31-82-182 test]# setenforce 0
[root@ip-172-31-82-182 test]# getenforce
Permissive[root@ip-172-31-82-182 ~]# sestatus
SELinux status: enabled
SELinuxfs mount: /sys/fs/selinux
SELinux root directory: /etc/selinux
Loaded policy name: targeted
Current mode: permissive
Mode from config file: enforcing
Policy MLS status: enabled
Policy deny_unknown status: allowed
Memory protection checking: actual (secure)
Max kernel policy version: 33
[root@ip-172-31-82-182 ~]#I restarted MySQL which came up clean and I could create my table. I noted that the system was restarted and I hadn’t persisted the configuration earlier.
[root@ip-172-31-82-182 ~]# uptime
18:50:14 up 1 days, 9:04, 1 user, load average: 0.26, 0.13, 0.11TL;DR If you see Unknown (generic) error from engine, check SELinux / Firewall.
Conclusion
In conclusion, permissions errors in Linux can be frustrating and time-consuming to troubleshoot, but by understanding the root causes and common solutions, you can quickly resolve these issues and get back to work. This blog walks you through the steps of identifying and fixing the issue. Remember to always check the file and directory permissions, as well as the ownership and group settings, when encountering a permissions error. By following these best practices, you can avoid many common permissions errors and keep your MySQL on Linux system running smoothly.
