Mysql 5.7 ran out of disk space and now it won't even try to restart

Hi,

I was loading data into a new Percona 5.7.33 server, but unfortunately failed to keep track of the space available on the binlog mount. The DB crashed, but now that I’ve freed up some space, it still won’t start. systemctl just says it ‘failed’ and nothing new is spit out to the error.log.

It’s not continuously crashing. It only crashed the 1 time. Attempting to restart it with ‘systemctl start mysqld’ does not appear to do anything. The error log remains the same.

Here is the systemctl output:

[root@wg9plmsqmd01 ~]# systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: failed (Result: start-limit) since Tue 2022-02-22 19:30:24 CST; 1h 29min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 10476 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE)
Process: 10452 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 2928 (code=exited, status=2)

Feb 22 19:30:24 wg9plmsqmd01.pblipa.ca systemd[1]: Failed to start MySQL Server.
Feb 22 19:30:24 wg9plmsqmd01.pblipa.ca systemd[1]: Unit mysqld.service entered failed state.
Feb 22 19:30:24 wg9plmsqmd01.pblipa.ca systemd[1]: mysqld.service failed.
Feb 22 19:30:24 wg9plmsqmd01.pblipa.ca systemd[1]: mysqld.service holdoff time over, scheduling restart.
Feb 22 19:30:24 wg9plmsqmd01.pblipa.ca systemd[1]: Stopped MySQL Server.
Feb 22 19:30:24 wg9plmsqmd01.pblipa.ca systemd[1]: start request repeated too quickly for mysqld.service
Feb 22 19:30:24 wg9plmsqmd01.pblipa.ca systemd[1]: Failed to start MySQL Server.
Feb 22 19:30:24 wg9plmsqmd01.pblipa.ca systemd[1]: Unit mysqld.service entered failed state.
Feb 22 19:30:24 wg9plmsqmd01.pblipa.ca systemd[1]: mysqld.service failed.

There are still mysql.sock and mysql.sock.lock files that contains a no longer valid pid number. Would those be blocking the start?

For what it’s worth, ‘ps -ef | grep mysqld’ shows that there are no mysql processes currently running.

I’ve also tried adding ‘innodb_force_recovery’ to the my.cnf file, but again it doesn’t appear to even try to start.

Does anyone have any ideas?? I’m stumped.

Just in case it helps, the following is the last output within the error log:

2022-02-22T17:40:12.120782-06:00 117 [ERROR] /usr/sbin/mysqld: Binary logging no
t possible. Message: An error occurred during flush stage of the commit. ‘binlog
_error_action’ is set to ‘ABORT_SERVER’. Server is being stopped.
23:40:12 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.
Please help us make Percona Server better by reporting any
bugs at https://bugs.percona.com/

key_buffer_size=402653184
read_buffer_size=2097152
max_used_connections=4
max_threads=257
thread_count=5
connection_count=3
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1445446 K bytes of memory
Hope that’s ok; if not, decrease some variables in the equation.

Build ID: 38a1b1b717598d7fe5f8153c01fd1a5c62d9054e
Server Version: 5.7.33-36-log Percona Server (GPL), Release 36, Revision 7e403c5

Thread pointer: 0x7fa6781abe80
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
stack_bottom = 7fa6c26b1d30 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xf2793b]
/usr/sbin/mysqld(handle_fatal_signal+0x505)[0xd59bf5]
/lib64/libpthread.so.0(+0xf630)[0x7fab2f856630]
/lib64/libc.so.6(gsignal+0x37)[0x7fab2d9533d7]
/lib64/libc.so.6(abort+0x148)[0x7fab2d954ac8]
/usr/sbin/mysqld[0xeac16e]
/usr/sbin/mysqld[0xeb5e02]
/usr/sbin/mysqld(_ZN13MYSQL_BIN_LOG14ordered_commitEP3THD+0x1da)[0xebe6ca]
/usr/sbin/mysqld(_ZN13MYSQL_BIN_LOG6commitEP3THDb+0xdcc)[0xec235c]
/usr/sbin/mysqld(_Z15ha_commit_transP3THDbb+0x23e)[0x7b3f2e]
/usr/sbin/mysqld(_Z17trans_commit_stmtP3THD+0x32)[0xd28652]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THDb+0x63a)[0xc737aa]
/usr/sbin/mysqld(_Z11mysql_parseP3THDP12Parser_stateb+0x47d)[0xc7ad0d]
/usr/sbin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0xba2)[0xc7b982]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x1df)[0xc7d3ff]
/usr/sbin/mysqld(handle_connection+0x2c0)[0xd40320]
/usr/sbin/mysqld(pfs_spawn_thread+0x1b4)[0xf3fd34]
/lib64/libpthread.so.0(+0x7ea5)[0x7fab2f84eea5]
/lib64/libc.so.6(clone+0x6d)[0x7fab2da1b9fd]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fa5c6db5c80): INSERT INTO table_name …

Connection ID (thread ID): 117
Status: KILL_QUERY

You may download the Percona Server operations manual by visiting
. You may find information
in the manual which will help you identify the cause of the crash.

1 Like

Hi @jorr
Please try to remove sock files manually. Also please check that there is no other mysql processes running. Also please check what systemct status mysql show

1 Like

Thank you for the reply. I assume you meant “systemctl status mysql” (see results below, this was before I attempted another start).

[root@wg9plmsqmd01 ~]# systemctl status mysql
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: failed (Result: start-limit) since Tue 2022-02-22 19:30:24 CST; 13h ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 10476 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE)
Process: 10452 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 2928 (code=exited, status=2)

Feb 22 19:30:24 wg9plmsqmd01.pblipa.ca systemd[1]: Failed to start MySQL Server.
Feb 22 19:30:24 wg9plmsqmd01.pblipa.ca systemd[1]: Unit mysqld.service entered failed state.
Feb 22 19:30:24 wg9plmsqmd01.pblipa.ca systemd[1]: mysqld.service failed.
Feb 22 19:30:24 wg9plmsqmd01.pblipa.ca systemd[1]: mysqld.service holdoff time over, scheduling restart.
Feb 22 19:30:24 wg9plmsqmd01.pblipa.ca systemd[1]: Stopped MySQL Server.
Feb 22 19:30:24 wg9plmsqmd01.pblipa.ca systemd[1]: start request repeated too quickly for mysqld.service
Feb 22 19:30:24 wg9plmsqmd01.pblipa.ca systemd[1]: Failed to start MySQL Server.
Feb 22 19:30:24 wg9plmsqmd01.pblipa.ca systemd[1]: Unit mysqld.service entered failed state.
Feb 22 19:30:24 wg9plmsqmd01.pblipa.ca systemd[1]: mysqld.service failed.

After this I moved the mysql.sock* files out of their normal place and tried another start with the same result. Running a status after the attempt shows the same error message as above.

[root@wg9plmsqmd01 ~]# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See “systemctl status mysqld.service” and “journalctl -xe” for details.

Oh and “ps -ef | grep mysql” shows no mysql processes.

1 Like

@jorr try to run systemctl restart mysqld
Alsol after that check status and see what is in mysql log and in system log

1 Like

Thank you for the help, but we finally found the issue.

I had removed the /var/lib/mysql-files and /var/lib/mysql-keyring directories because they were empty and I found them annoying because they interfered with using the TAB-complete functionality.

I had run journalctl -xe (as the systemctl start error message suggested, but there was no mention of mysql-files), it wasn’t until a colleague ran journalctl -r that he found:

2022-02-23T08:48:10.646329-06:00 0 [ERROR] Aborting
Feb 23 08:48:10 wg9plmsqmd01.pblipa.ca mysqld[14710]: 2022-02-23T08:48:10.646194-06:00 0 [ERROR] Failed to access directory for --secure-file-priv. Please make sure that directory exists and
Feb 23 08:48:10 wg9plmsqmd01.pblipa.ca mysqld[14710]: mysqld: Error on realpath() on ‘/var/lib/mysql-files’ (Error 2 - No such file or directory)

Replacing the directory with the proper permissions appears to have worked.

Do you have any ideas why the mysql-files and mysql-keyring directories are needed?

1 Like

You can change the parameters in my.cnf to other paths if the default paths are annoying :slight_smile:

1 Like