Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.32, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
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
Attachments
Issue Links
Activity
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