January 6, 2025

Note, in which cases will the MySQL configuration file be truncated?

Analysis of MySQL 5.7.44 config file truncation issues and solutions during initialization.

Overview

An error was reported when initializing the MySQL instance (version: 5.7.44).

[root@db1 ~]# /opt/mysql/base/5.7.44/bin/mysqld --defaults-file=/u01/my.cnf --datadir=/u01/3306 --basedir=/opt/mysql/base/5.7.44/ --initialize
mysqld: Can't read dir of '/etc/my.cnf.' (Errcode: 2 - No such file or directory)
mysqld: [ERROR] Fatal error in defaults handling. Program aborted!

Processing Steps

According to the error message, the directory /etc/my.cnf. cannot be found. It seems rather strange because there is a dot at the end of the directory name.

First, check the my.cnf configuration file and find that the only configuration related to my.cnf is !includedir /etc/my.cnf.d. Then, check the /etc/my.cnf.d directory and find that it exists.

[root@db1 ~]# cat /u01/my.cnf|grep /etc/my.cnf

!includedir /etc/my.cnf.d

[root@db1 ~]# ls -l /etc/my.cnf.d
total 4
-rw-r--r-- 1 root root 232 May 6 2020 mysql-clients.cnf

‍

Try to verify the correctness of the configuration file, and it is found that the situation remains the same.

[root@db1 ~]# /opt/mysql/base/5.7.44/bin/mysqld --defaults-file=/u01/my.cnf --help
mysqld: Can't read dir of '/etc/my.cnf.' (Errcode: 2 - No such file or directory)
mysqld: [ERROR] Fatal error in defaults handling. Program aborted!

‍

Therefore, it is suspected that there is a problem with the configuration file. After attempting to delete !includedir /etc/my.cnf.d, it is found that the file can be read normally.

[root@db1 ~]# cp /u01/my.cnf /u01/my.cnf.bak
[root@db1 ~]# vi /u01/my.cnf.bak
[root@db1 ~]# /opt/mysql/base/5.7.44/bin/mysqld --defaults-file=/u01/my.cnf.bak --help
/opt/mysql/base/5.7.44/bin/mysqld Ver 5.7.44-log for linux-glibc2.12 on x86_64 (MySQL Community Server (GPL))
Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Starts the MySQL database server.

Usage: /opt/mysql/base/5.7.44/bin/mysqld [OPTIONS]

For more help options (several pages), use mysqld --verbose --help.

‍

This is very strange. Why is /etc/my.cnf.d written, but the error reported is /etc/my.cnf.?

After comparing these two files, a problem was discovered, which shows:

[root@db1 ~]# diff /u01/my.cnf /u01/my.cnf.bak
169d168
< !includedir /etc/my.cnf.d
\ No newline at end of file

‍

No newline at end of file indicates that there is no newline character at the end of the file. Normally, after editing with compiling software such as vi, a newline character will be automatically added to the last line. For example, there is a newline at the last line of the /u01/my.cnf.bak file that was just modified. The difference can be visually found through the tail command.

[root@db1 ~]# tail -1 /u01/my.cnf.bak
#validate_password_policy = MEDIUM
[root@db1 ~]# tail -1 /u01/my.cnf
!includedir /etc/my.cnf.d[root@db1 ~]#

‍

After attempting to manually add it after /u01/my.cnf, the initialization was successful.

[root@db1 ~]# echo >> /u01/my.cnf
[root@db1 ~]# tail -1 /u01/my.cnf
!includedir /etc/my.cnf.d
[root@db1 ~]# /opt/mysql/base/5.7.44/bin/mysqld --defaults-file=/u01/my.cnf --datadir=/u01/3306 --basedir=/opt/mysql/base/5.7.44/ --initialize

Analysis of the Cause

First, restore the original configuration file and then use strace for debugging.

strace   -T -tt -s 100 -o /tmp/strace.log  /opt/mysql/base/5.7.44/bin/mysqld --defaults-file=/u01/my.cnf --datadir=/u01/3306 --basedir=/opt/mysql/base/5.7.44/ --initialize

11:43:17.274860 stat("/u01/my.cnf", {st_mode=S_IFREG|0644, st_size=4581, ...}) = 0 <0.000005>
11:43:17.274886 open("/u01/my.cnf", O_RDONLY) = 3 <0.000006>
11:43:17.274910 fstat(3, {st_mode=S_IFREG|0644, st_size=4581, ...}) = 0 <0.000004>
11:43:17.274927 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fa11a196000 <0.000005>
11:43:17.274945 read(3, "[universe]\niops = 0\nmem_limit_mb = 0\ncpu_quota_percentage = 0\nquota_limit_mb = 0\nscsi_pr_level = 0\nu"..., 4096) = 4096 <0.000007>
11:43:17.274997 read(3, "e-schema-consumer-events-stages-history=ON\nperformance-schema-consumer-events-stages-history-long=ON"..., 4096) = 485 <0.000004>
11:43:17.275018 read(3, "", 4096) = 0 <0.000004>
11:43:17.275035 openat(AT_FDCWD, "/etc/my.cnf./", O_RDONLY|O_NONBLOCK|O_CLOEXEC|O_DIRECTORY) = -1 ENOENT (No such file or directory) <0.000005>
11:43:17.275077 write(2, "mysqld: ", 8) = 8 <0.000009>
11:43:17.275101 write(2, "Can't read dir of '/etc/my.cnf.' (Errcode: 2 - No such file or directory)", 73) = 73 <0.000004>
11:43:17.275120 write(2, "\n", 1) = 1 <0.000004>
11:43:17.275140 close(3) = 0 <0.000005>
11:43:17.275157 munmap(0x7fa11a196000, 4096) = 0 <0.000007>
11:43:17.275179 write(2, "mysqld: ", 8) = 8 <0.000004>
11:43:17.275196 write(2, "[ERROR] Fatal error in defaults handling. Program aborted!", 58) = 58 <0.000004>
11:43:17.275213 write(2, "\n", 1) = 1 <0.000004>
11:43:17.275324 exit_group(1) = ?

‍

After adding a newline at the end of the configuration file, the initialization can be completed successfully, and the debugging results are as follows.

strace   -T -tt -s 100 -o /tmp/strace2.log  /opt/mysql/base/5.7.44/bin/mysqld --defaults-file=/u01/my.cnf --datadir=/u01/3306 --basedir=/opt/mysql/base/5.7.44/ --initialize

11:48:40.550423 stat("/u01/my.cnf", {st_mode=S_IFREG|0644, st_size=4582, ...}) = 0 <0.000005>
11:48:40.550449 open("/u01/my.cnf", O_RDONLY) = 3 <0.000006>
11:48:40.550473 fstat(3, {st_mode=S_IFREG|0644, st_size=4583, ...}) = 0 <0.000004>
11:48:40.550490 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7faf63733000 <0.000005>
11:48:40.550508 read(3, "[universe]\niops = 0\nmem_limit_mb = 0\ncpu_quota_percentage = 0\nquota_limit_mb = 0\nscsi_pr_level = 0\nu"..., 4096) = 4096 <0.000006>
11:48:40.550560 read(3, "e-schema-consumer-events-stages-history=ON\nperformance-schema-consumer-events-stages-history-long=ON"..., 4096) = 486 <0.000004>
11:48:40.550583 openat(AT_FDCWD, "/etc/my.cnf.d/", O_RDONLY|O_NONBLOCK|O_CLOEXEC|O_DIRECTORY) = 4 <0.000005>
11:48:40.550615 brk(NULL) = 0x336d000 <0.000003>
11:48:40.550630 brk(0x338f000) = 0x338f000 <0.000004>
11:48:40.550660 getdents(4, /* 3 entries */, 32768) = 88 <0.000006>
11:48:40.550680 getdents(4, /* 0 entries */, 32768) = 0 <0.000004>
11:48:40.550697 close(4) = 0 <0.000004>
11:48:40.550721 stat("/etc/my.cnf.d/mysql-clients.cnf", {st_mode=S_IFREG|0644, st_size=232, ...}) = 0 <0.000004>
11:48:40.550740 open("/etc/my.cnf.d/mysql-clients.cnf", O_RDONLY) = 4 <0.000004>

‍

When the last line is not of the !includedir or !include type and there is also no newline, the debugging results are as follows, and the initialization can be completed successfully.

12:18:10.341731 stat("/u01/my.cnf", {st_mode=S_IFREG|0644, st_size=4599, ...}) = 0 <0.000005>
12:18:10.341756 open("/u01/my.cnf", O_RDONLY) = 3 <0.000006>
12:18:10.341783 fstat(3, {st_mode=S_IFREG|0644, st_size=4599, ...}) = 0 <0.000005>
12:18:10.341807 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f304d65a000 <0.000005>
12:18:10.341827 read(3, "[universe]\niops = 0\nmem_limit_mb = 0\ncpu_quota_percentage = 0\nquota_limit_mb = 0\nscsi_pr_level = 0\nu"..., 4096) = 4096 <0.000009>
12:18:10.341897 read(3, "e-schema-consumer-events-stages-history=ON\nperformance-schema-consumer-events-stages-history-long=ON"..., 4096) = 503 <0.000005>
12:18:10.341923 openat(AT_FDCWD, "/etc/my.cnf.d/", O_RDONLY|O_NONBLOCK|O_CLOEXEC|O_DIRECTORY) = 4 <0.000006>
12:18:10.341948 brk(NULL) = 0x34fb000 <0.000003>
12:18:10.341963 brk(0x351d000) = 0x351d000 <0.000005>
12:18:10.341988 getdents(4, /* 3 entries */, 32768) = 88 <0.000007>
12:18:10.342016 getdents(4, /* 0 entries */, 32768) = 0 <0.000005>
12:18:10.342038 close(4) = 0 <0.000004>
12:18:10.342062 stat("/etc/my.cnf.d/mysql-clients.cnf", {st_mode=S_IFREG|0644, st_size=232, ...}) = 0 <0.000004>
12:18:10.342081 open("/etc/my.cnf.d/mysql-clients.cnf", O_RDONLY) = 4 <0.000005>

‍

When the last line is of the !include type and there is also no newline. The debugging results are as follows:

12:24:16.055588 stat("/u01/my.cnf", {st_mode=S_IFREG|0644, st_size=4596, ...}) = 0 <0.000005>
12:24:16.055614 open("/u01/my.cnf", O_RDONLY) = 3 <0.000006>
12:24:16.055653 fstat(3, {st_mode=S_IFREG|0644, st_size=4596, ...}) = 0 <0.000007>
12:24:16.055676 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fcb0b65c000 <0.000005>
12:24:16.055695 read(3, "[universe]\niops = 0\nmem_limit_mb = 0\ncpu_quota_percentage = 0\nquota_limit_mb = 0\nscsi_pr_level = 0\nu"..., 4096) = 4096 <0.000006>
12:24:16.055747 read(3, "e-schema-consumer-events-stages-history=ON\nperformance-schema-consumer-events-stages-history-long=ON"..., 4096) = 500 <0.000004>
12:24:16.055768 read(3, "", 4096) = 0 <0.000004>
12:24:16.055788 stat("/etc/my.cnf.d/mysql-clients.cn", 0x7ffd8c379280) = -1 ENOENT (No such file or directory) <0.000004>
12:24:16.055809 read(3, "", 4096) = 0 <0.000004>
12:24:16.055827 close(3) = 0 <0.000005>

Summary

When MySQL initializes and parses the configuration file:

When the last line is of the !includedir type and there is no newline, the last character will be truncated automatically, so an error Can't read dir of '/etc/my.cnf.' will be reported, resulting in initialization failure.
When the last line is of the !include type and there is no newline, the referenced file name will be truncated by one character, causing the file to not be found, but it will not cause the initialization program to abort. The initialization can be completed normally.

In conclusion, it is recommended to add a newline at the end of the configuration file; otherwise, some abnormal situations may occur.