MySQL Bugs: #83408: Renaming error log fails with Unknown Error 1105

Bug #83408	Renaming error log fails with Unknown Error 1105
Submitted:	17 Oct 2016 10:59	Modified:	14 Feb 2017 14:27
Reporter:	Claudiu Avram	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S2 (Serious)
Version:	5.6.28, 5.7.16, 5.7.17	OS:	CentOS (release 7.1.1503)
Assigned to:		CPU Architecture:	Any
Tags:	error_log, flush-logs, log-error

Description:
For keeping the log file size under control without restarting the server, the official documentation suggests renaming the log file and executing a FLUSH LOGS:
https://dev.mysql.com/doc/refman/5.6/en/log-file-maintenance.html

After renaming the error log file, executing the FLUSH ERROR LOGS command fails with "ERROR 1105 (HY000): Unknown error"

Additional info:
- The error does not reproduce if I don't rename the error log file.
- The log file size is 330GB

How to repeat:
In /etc/my.cnf I have:
> ...
> [mysqld_safe]
> log-error=/var/log/mysqld.log
> ...

Scenario 1 (with renaming):
1. Rename log file:
$ mv /var/log/mysqld.log /var/log/mysqld.log-old

2. In MySQL:
> FLUSH ERROR LOGS
=> ERROR 1105 (HY000): Unknown error

Scenario 2 (without renaming):
1. In MySQL:
> FLUSH ERROR LOGS
=> Query OK, 0 rows affected (0.00 sec)

updated tags

Hello Claudiu Avram,

Thank you for the report.
Observed this with 5.7.16 build on CentOS7.

Thanks,
Umesh

-- 5.7.16

[root@localhost Desktop]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'log%';
+----------------------------------------+---------------------+
| Variable_name                          | Value               |
+----------------------------------------+---------------------+
| log_bin                                | OFF                 |
| log_bin_basename                       |                     |
| log_bin_index                          |                     |
| log_bin_trust_function_creators        | OFF                 |
| log_bin_use_v1_row_events              | OFF                 |
| log_builtin_as_identified_by_password  | OFF                 |
| log_error                              | /var/log/mysqld.log |
| log_error_verbosity                    | 3                   |
| log_output                             | FILE                |
| log_queries_not_using_indexes          | OFF                 |
| log_slave_updates                      | OFF                 |
| log_slow_admin_statements              | OFF                 |
| log_slow_slave_statements              | OFF                 |
| log_statements_unsafe_for_binlog       | ON                  |
| log_syslog                             | OFF                 |
| log_syslog_facility                    | daemon              |
| log_syslog_include_pid                 | ON                  |
| log_syslog_tag                         |                     |
| log_throttle_queries_not_using_indexes | 0                   |
| log_timestamps                         | UTC                 |
| log_warnings                           | 2                   |
+----------------------------------------+---------------------+
21 rows in set (0.06 sec)

mysql> \q
Bye
[root@localhost Desktop]# mv /var/log/mysqld.log /var/log/mysqld.log.old
[root@localhost Desktop]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> flush logs;
ERROR 1105 (HY000): Unknown error
mysql> 
mysql> show errors;
+-------+------+---------------+
| Level | Code | Message       |
+-------+------+---------------+
| Error | 1105 | Unknown error |
+-------+------+---------------+
1 row in set (0.00 sec)

mysql> show warnings;
+-------+------+---------------+
| Level | Code | Message       |
+-------+------+---------------+
| Error | 1105 | Unknown error |
+-------+------+---------------+
1 row in set (0.00 sec)

mysql> \! ls -l /var/log/mysqld.log.old
-rw-r-----. 1 mysql mysql 185447 Oct 20 02:31 /var/log/mysqld.log.old
mysql> \! ls -l /var/log/mysqld.log
ls: cannot access /var/log/mysqld.log: No such file or directory
mysql> show variables like '%sys%';
+--------------------------------+------------------------------------------------------------------------------------------+
| Variable_name                  | Value                                                                                    |
+--------------------------------+------------------------------------------------------------------------------------------+
| character_set_filesystem       | binary                                                                                   |
| character_set_system           | utf8                                                                                     |
| log_syslog                     | OFF                                                                                      |
| log_syslog_facility            | daemon                                                                                   |
| log_syslog_include_pid         | ON                                                                                       |
| log_syslog_tag                 |                                                                                          |
| lower_case_file_system         | OFF                                                                                      |
| session_track_system_variables | time_zone,autocommit,character_set_client,character_set_results,character_set_connection |
| system_time_zone               | EDT                                                                                      |
+--------------------------------+------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)

mysql>

Hi!

When using /var/log/mysqld.log as error log, mysqld process (running as mysql user) can't recreate error log itself, only root can do that 
(/var/log directory writable for root user only).

Please try to recreate file after moving it. 

Use e.g. install command, something like this:

$ mv /var/log/mysqld.log /var/log/mysqld.log.old
$ install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log

and then flush logs.

Hi,

We made further analysis and concluded that this is not a bug. The conclusion is quite obvious, when you know how our server functions ...

With the server running, when we rename the error log file,
'flush error logs' query tries to reopen the error log file
with the old file name.

In the Community server, the error log path name is set to
'/var/log/mysqld.log' and only root user has access to recreate
the file. Since here the server runs as 'mysql' user, the file
recreating operation fails as it doesn't have the privileges to
do so.

Do note also, that we have already provided a workaround by our colleague Terje in this same bug.

Thank you.

Posted by developer:
 
Text added to section in question:

Note

For the server to recreate a given log file after you have renamed the file externally, the file location must be writable by the server. This may not always be the case. For example, on Linux, the server might write the error log as /var/log/mysqld.log, where /var/log is owned by root and not writable by mysqld. In this case, the log-flushing operation will fail to create a new log file.

To handle this situation, you must manually create the new log file with the proper ownershiop after renaming the original log file. For example, execute these commands as root:

shell> mv /var/log/mysqld.log /var/log/mysqld.log.old
shell> install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log