Details
-
Type:
Bug
-
Status: Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 5.5.32, 10.1, 10.0, 5.5
-
Fix Version/s: 10.1
-
Component/s: Admin statements
-
Labels:
Description
See also http://bugs.mysql.com/bug.php?id=69953
Can't drop mysql.slow_log if slow_log=on even with log_output=FILE or NONE
I can understand preventing dropping of a log table that is in active use,
but with log_output!=TABLE it should be dropable the same way as it is when logging is not active at all
How to repeat:
SET GLOBAL log_output='FILE'; SET GLOBAL slow_query_log=1; SET GLOBAL slow_query_log_file='/tmp/slow.log'; DROP TABLE mysql.slow_log; # => ERROR 1580 (HY000): You cannot 'DROP' a log table if logging is enabled SET GLOBAL log_output='NONE'; DROP TABLE mysql.slow_log; # => ERROR 1580 (HY000): You cannot 'DROP' a log table if logging is enabled SET GLOBAL slow_query_log=0; DROP TABLE mysql.slow_log; # => success
Suggested fix:
only prevent drop of "magic" log tables if logging is enabled and configured to log to the table
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Fixed in 5.7.0:
revno: 4513
revision-id: nisha.gopalakrishnan@oracle.com-20120920070431-rzm71cm934l01kmo
parent: marko.makela@oracle.com-20120920060910-53jj3opi391gn2ph
committer: Nisha Gopalakrishnan <nisha.gopalakrishnan@oracle.com>
branch nick: mysql-trunk-11763447
timestamp: Thu 2012-09-20 12:34:31 +0530
message:
BUG#11763447: 'YOU CANNOT 'ALTER' A LOG TABLE IF LOGGING IS ENABLED'
EVEN IF I LOG TO FILE.
Analysis:
----------
MYSQL_UPGRADE of the master breaks the replication when
the query logging is enabled with FILE/NONE 'log-output'
option on the slave.
mysql_upgrade modifies the 'general_log' and 'slow_log'
tables after the logging is disabled as below:
SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE general_log
MODIFY event_time TIMESTAMP NOT NULL,
( .... );
SET GLOBAL general_log = @old_log_state;
and
SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE slow_log
MODIFY start_time TIMESTAMP NOT NULL,
( .... );
SET GLOBAL slow_query_log = @old_log_state;
In the binary log, only the ALTER statements are logged
but not the SET statements which turns ON/OFF the logging.
So when the slave replays the binary log,the ALTER of LOG
tables throws an error since the logging is enabled. Also
the 'log-output' option is not checked to determine
whether to allow/disallow the ALTER operation.
Fix:
----
The 'log-output' option is included in the check while
determining whether the query logging happens using the
log tables.
Reproducible on MySQL 5.1 - 5.6, but not 5.7, apparently it was fixed there