MySQL Bugs: #29751: Do not rename the error log at FLUSH LOGS

Bug #29751	Do not rename the error log at FLUSH LOGS
Submitted:	12 Jul 2007 8:01	Modified:	14 Oct 2010 13:13
Reporter:	Axel Schwenke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:	Dmitry Shulga	CPU Architecture:	Any

Description:
At FLUSH LOGS the MySQL server used to only rotate the binary log. The other logs were just flushed and re-opened under the same name. At some point in time the server started to rename the error log to $OLD_NAME . "-old".

This behaviour is inconsistent. Why is the error log renamed but not the slow query and general log?

The default behaviour for log rotation in UNIX is to just flush and reopen log files. Since UNIX allows renaming open files, the user can rename the log files and then use FLUSH LOGS to open new files. However this is unusual for Windows users. Windows forbids to rename open files. Also log files used to be open while a program is running. Therefore a log rotating mechanism for a Windows program should rename log files by itself. This is currently done for the error log only. Seemingly the slow query and general log files are closed between writes now, so they can be renamed in Windows also (however this is not documented, see bug #29750).

How to repeat:
n/a

Suggested fix:
1. on UNIX the error log should not be renamed at FLUSH LOGS since this is not neccessary and may collide with normal log rotation procedures.

2. on Windows, FLUSH LOGS should rename *all* log files. Since it is possible to rename the slow query and general log manually, the old logs should only be renamed if they exist (and probably only if size is > 0).

I'd like to request re-triage of this issue and am raising it to S2.  This is a much more serious issue than first appears.

I have ran into a customer who wants to "FLUSH LOGS" to rotate the binary logs, a perfectly reasonable request with his backup each night.  The problem is that if you run "FLUSH LOGS" twice, you completely lose your error log history! Because it renames to -old, and then does it again.

This is going to be a critical issue for support - having a complete error log history is often critical to diagnosing issues.  And there is no other way to FLUSH the other logs.  This is a big change from 5.0.

In this case they had an issue we are unable to track down because the error log history is lost.

Please note - that part of the problem here is the only way to rotate the binary logs is the same command.

Some kind of options to 'skip' or 'specifically' rotate certain logs would be great.

This becomes even more important in 5.1 as it's no longer possible to NOT
specify log-error to mysqld when using mysqld_safe.
The reason is that mysqld_safe will now always do one of two things:
1) use syslog (--syslog)
2) pass log-error onto mysqld (append_arg_to_args "--log-error=$err_log")

This changed in 5.1 because since using syslog is now an option we
can't simply always use I/O redirection when starting mysqld (as we
did prior to 5.1):
>> $err_log 2>&1

This means that as of 5.1 FLUSH LOGS will ALWAYS get rid of any AND ALL
error log data (no -old file is created) when using mysqld_safe to start
mysqld.  We really need an option to tell mysqld NOT to rotate the error
log as this can contain very critical data when trying to diagnose a
problem.

Dmitry, let's restore the original behaviour - do not rename anything, just reopen.
The request to rename logs on Windows should be extracted into a separate bug report (feature request).

This is not a regression and works as documented since the
optoin --log-error was introduced.

Quoting 5.0 manual:

http://dev.mysql.com/doc/refman/5.0/en/flush.html:

If the server is writing error output to a named file (for example, if it was started with the --log-error option), FLUSH LOGS  causes it to rename the current error log file with a suffix of -old and create a new empty log file. No renaming occurs if the server is not writing to a named file (for example, if it is writing errors to the console). 

This bug report is therefore a feature request for an incompatible change.

Correction: this is already in 4.0:
http://dev.mysql.com/doc/refman/4.1/en/flush.html:

Beginning with MySQL 4.0.10, if the server was started with the --log-error option), FLUSH LOGS  causes it to rename the current error log file with a suffix of -old and create a new empty log file. No renaming occurs if the server is not writing to a named file (for example, if it is writing errors to the console).

Based on the above investigation, please change 5.1 to not rename the file.
We do not need to rename the file on Windows either, since on all modern Windows systems it's possible to rename or delete an open file, if the file is opened by the process with a respective property.
MySQL, however (judging by the code) does not set this attribute (FILE_SHARE_DELETE) for the error log. A separate feature request needs to be filed and implemented so that this attribute is set for all MySQL log files.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/115298

3470 Dmitry Shulga	2010-08-09
      Fixed bug #29751 - do not rename the error log at FLUSH LOGS.
      Added open log file with FILE_SHARE_DELETE flag on Windows.
     @ sql/log.cc
        added reopen_fstreams();
        modified redirect_std_streams(): call to sequence of freopen()
        replaced to reopen_fstreams();
        modified flush_error_log(): removed file rename for flushed
        error log file.
     @ sql/mysqld.cc
        modified main() and init_server_components(): do open log error file
        over call to reopen_fstreams().

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/115365

3470 Dmitry Shulga	2010-08-10
      Fixed bug #29751 - do not rename the error log at FLUSH LOGS.
      Added open log file with FILE_SHARE_DELETE flag on Windows.
     @ sql/log.cc
        added reopen_fstreams();
        modified redirect_std_streams(): call to sequence of freopen()
        replaced to reopen_fstreams();
        modified flush_error_log(): removed file rename for flushed
        error log file.
     @ sql/mysqld.cc
        modified main() and init_server_components(): do open log error file
        over call to reopen_fstreams().

>  A separate feature request needs to be filed and implemented so that this
attribute is set for all MySQL log files.

Done; Bug #56235

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116717

3489 Dmitry Shulga	2010-08-25
      Fixed bug #29751 - do not rename the error log at FLUSH LOGS.
      Added open log file with FILE_SHARE_DELETE flag on Windows.
     @ sql/log.cc
        added reopen_fstreams();
        modified redirect_std_streams(): call to sequence of freopen()
        replaced to reopen_fstreams();
        modified flush_error_log(): removed file rename for flushed
        error log file.
     @ sql/mysqld.cc
        modified main() and init_server_components(): do open log error file
        over call to reopen_fstreams().

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/117113

3178 Dmitry Shulga	2010-08-30
      Follow-up for Bug#29751: FLUSH LOGS doesn't create -old file,
      so test case has to be updated.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/117118

3184 Dmitry Shulga	2010-08-30
      Follow-up for Bug#29751: FLUSH LOGS doesn't create -old file,
      so test case has to be updated.

Unbelievable :) I have worked on this "feature request" during the weekend and just submitted a file to mysql support pfft :)

from what I am seeing you also need to change set_var.cc so as it shows in show variables, I am attaching my version of the patch.

Darren

patch for ignore error log during flush logs

Attachment: diff_flush_logs_ignore_error_log.txt (text/plain), 0 bytes.

patch for ignore error log during flush logs

Attachment: diff_flush_logs_ignore_error_log.txt (text/plain), 6.17 KiB.

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100901130501-4g2k86dub29auj8y) (version source revid:alik@sun.com-20100901130012-9bmmvzcnnw6n5rw6) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100901130614-pgop3m80rmutewxn) (version source revid:alik@sun.com-20100901130033-8k19cjn6n2blm3py) (pib:21)

Pushed into mysql-5.5 5.5.7-m3 (revid:alik@sun.com-20100901125952-4hsrosoa0xreionr) (version source revid:alik@sun.com-20100901125952-4hsrosoa0xreionr) (merge vers: 5.5.7-m3) (pib:21)

Regarding selective log flushing, this is available as of MySQL 5.5.3. See Bug#14104.

http://dev.mysql.com/doc/refman/5.5/en/flush.html

http://dev.mysql.com/doc/refman/5.1/en/query-log.html says this about the flushing and renaming the general query log:

"
Server restarts and log flushing do not cause a new general query log file to be generated (although flushing closes and reopens it). On Unix, you can rename the file and create a new one by using the following commands:

shell> mv host_name.log host_name-old.log
shell> mysqladmin flush-logs
shell> cp host_name-old.log backup-directory
shell> rm host_name-old.log
"

Will that same general procedure work for the error log now as well? And in particular, will it work on Windows?

FLUSH LOGS statement now just reopens the error log. The patch is adding support for non-exclusive open of error log for Windows. Thus the administrator can do the following commands:

shell> mv host_name.err host_name-old.err
shell> mysqladmin flush-logs
shell> cp host_name-old.err backup-directory
shell> rm host_name-old.err

Noted in 5.1.51, 5.5.7, 5.6.1 changelogs.

Previously, if you flushed the logs using FLUSH LOGS or mysqladmin
flush-logs and mysqld was writing the error log to a file (for
example, if it was started with the --log-error option), it renamed
the current log file with the suffix -old, then created a new empty
log file. This had the problem that a second log-flushing operation
thus caused the original error log file to be lost unless you saved
it under a different name. For example, you could use the following
commands to save the file:

shell> mysqladmin flush-logs
shell> mv host_name.err-old backup-directory

To avoid the preceding file-loss problem, renaming no longer occurs.
The server merely closes and reopens the log file. To rename the
file, you can do so manually before flushing. Then flushing the logs
reopens a new file with the original file name. For example, you can
rename the file and create a new one using the following commands:

shell> mv host_name.err host_name.err-old
shell> mysqladmin flush-logs
shell> mv host_name.err-old backup-directory

Also updated the error log section.

Pushed into mysql-5.1 5.1.52 (revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (version source revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (merge vers: 5.1.52) (pib:21)

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)

Already documented in the 5.1.51 changelog; no additional changelog entries required. Closed.