[MDEV-4851] can't drop mysql.slow_log if slow_log=on even with log_output=FILE - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5.32, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.1.48
Component/s: Admin statements
Labels:
- upstream-fixed
- verified

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

links to

Bug #69953 - can't drop mysql.slow_log if slow_log=on even with log_output=FILE

Activity

Ascending order - Click to sort in descending order

Hartmut Holzgraefe created issue - 2013-08-07 17:48

Elena Stepanova added a comment - 2013-08-08 11:42

Reproducible on MySQL 5.1 - 5.6, but not 5.7, apparently it was fixed there

Elena Stepanova added a comment - 2013-08-08 11:42 Reproducible on MySQL 5.1 - 5.6, but not 5.7, apparently it was fixed there

Elena Stepanova made changes - 2013-08-08 11:42

Field	Original Value	New Value
Labels		upstream

Elena Stepanova made changes - 2013-08-08 11:42

Remote Link

This issue links to "Bug #69953 - can't drop mysql.slow_log if slow_log=on even with log_output=FILE (Web Link)" [ 12008 ]

Elena Stepanova made changes - 2013-08-08 11:43

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

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:
{code:sql}
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
{code}

Suggested fix:
only prevent drop of "magic" log tables if logging is enabled *and* configured to log to the table

Sergei Golubchik made changes - 2013-08-08 22:43

Fix Version/s

5.5.33 [ 13300 ]

Sergei Golubchik made changes - 2013-09-16 19:04

Fix Version/s		5.5.34 [ 13500 ]
Fix Version/s	5.5.33 [ 13300 ]

Sergei Golubchik made changes - 2013-09-20 18:22

Fix Version/s		5.5.34 [ 13700 ]
Fix Version/s	5.5.33a [ 13500 ]

Sergei Golubchik made changes - 2013-11-21 21:02

Fix Version/s		5.5.35 [ 14000 ]
Fix Version/s	5.5.34 [ 13700 ]

Sergei Golubchik made changes - 2014-01-29 10:10

Fix Version/s		5.5.36 [ 14600 ]
Fix Version/s	5.5.35 [ 14000 ]

Sergei Golubchik made changes - 2014-02-24 21:51

Fix Version/s		5.5.37 [ 15000 ]
Fix Version/s	5.5.36 [ 14600 ]

Sergei Golubchik made changes - 2014-04-15 23:53

Fix Version/s		5.5.38 [ 15400 ]
Fix Version/s	5.5.37 [ 15000 ]

Sergei Golubchik made changes - 2014-06-06 10:26

Fix Version/s		5.5.39 [ 15800 ]
Fix Version/s	5.5.38 [ 15400 ]

Sergei Golubchik made changes - 2014-06-13 15:06

Workflow

defaullt [ 28338 ]

MariaDB v2 [ 44269 ]

Elena Stepanova added a comment - 2014-11-10 02:33

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.

Elena Stepanova added a comment - 2014-11-10 02:33 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.

Elena Stepanova made changes - 2014-11-10 02:33

Fix Version/s		10.1 [ 16100 ]
Fix Version/s	5.5 [ 15800 ]
Labels	upstream	upstream-fixed

Elena Stepanova made changes - 2014-11-10 02:33

Affects Version/s

10.0 [ 16000 ]

Elena Stepanova made changes - 2015-05-01 04:06

Affects Version/s		10.1 [ 16100 ]
Affects Version/s		5.5 [ 15800 ]
Labels	upstream-fixed	upstream-fixed verified

Elena Stepanova made changes - 2015-05-01 04:09

Component/s

Admin statements [ 11400 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:51

Workflow

MariaDB v2 [ 44269 ]

MariaDB v3 [ 63269 ]

Sergei Golubchik made changes - 2016-12-13 09:45

Priority

Minor [ 4 ]

Major [ 3 ]

Alice Sherepa made changes - 2018-07-17 13:45

Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]

Alice Sherepa made changes - 2018-07-17 13:45

Status

Open [ 1 ]

Confirmed [ 10101 ]

Daniel Black made changes - 2020-10-05 02:27

Assignee

Daniel Black [ danblack ]

Daniel Black made changes - 2020-10-12 01:43

Fix Version/s		10.1.48 [ 25108 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	Confirmed [ 10101 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:39

Workflow

MariaDB v3 [ 63269 ]

MariaDB v4 [ 146908 ]

People

Assignee:: Daniel Black

Reporter:: Hartmut Holzgraefe

Votes:: 1 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2013-08-07 17:48

Updated:: 2020-10-12 01:43

Resolved:: 2020-10-12 01:43

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration