MySQL Bugs: #31764: log tables should be in a PERFORMANCE

Bug #31764	log tables should be in a PERFORMANCE_SCHEMA schema
Submitted:	22 Oct 2007 22:07	Modified:	15 Mar 2011 17:33
Reporter:	Marc ALFF	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S2 (Serious)
Version:	5.1.23	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In 5.2, the following statement:

BACKUP DATABASE mysql to 'mysql.bak';

fails with the error ER_CANT_LOCK_LOG_TABLE

The error itself is correct: log tables can not be locked explicitly,
per design, as this would constitute a DOS attack.

However, this illustrate that the general_log ans slow_log tables should
*not* be part of the mysql schema, as this create issues related to locking with mysqldump / BACKUP.

How to repeat:
In 5.1:
SHOW CREATE TABLE mysql.general_log;
SHOW CREATE TABLE mysql.slow_log;

In 5.2:
BACKUP DATABASE mysql to 'mysql.bak';

Suggested fix:
In 5.1, rename the mysql.general_log and mysql.slow_log tables
to PERFORMANCE_SCHEMA.general_log and PERFORMANCE_SCHEMA.slow_log

This will avoid having to rework client applications during the 5.1 to 5.2 upgrade, since performance schema tables should be in the long term part of the PERFORMANCE_SCHEMA schema.

BACKUP DATABASE mysql ... is illegal, there will be an error message.
The error might not be ER_CANT_LOCK_LOG_TABLE, because the entire
mysql database will be affected by this rule.

I agree that general_log and slow_log tables should go in the
PERFORMANCE schema. I see the PERFORMANCE schema as a place for truly
transient data, for example these tables would be appropriate in it:
GLOBAL_STATUS, PROCESSLIST, and most of the FALCON_* tables
(and also the new online_backup_progress table, but Lars disagrees).
And I see slow_log as a temporarily-interesting list that's collected
for performance reasons.
Clearly general_log might have a different purpose -- auditing --
but in that case (a) it can be in a file (b) it will be superseded
when we've done WL#2878 "Simple data auditing".
So yes, placing general_log and slow_log in mysql tables was a mistake.

But I cannot agree that we should do anything about it. It's done.

In the MySQL Server Roadmap
https://inside.mysql.com/wiki/MySQL_Server_Future
the items relating to "performance schema", e.g. WL#2360, are
in category "MySQL 6.x WANT TO HAVES". This makes me think that
we will have performance schema in 6.1 or 6.2. Once it's done,
slow_log and general_log can be shifted to it, unless they are
superseded by more informative tables.

Marc is right, this means that client applications will 
have to change in 6.1 or 6.2. That damage has to be set
against the fact that we want to declare 5.1 GA really
really soon. Since I give that fact immense weight, I
believe Bug#31764 is not a P1 bug with a 5.1 target,
but a Feature Request with a 6.1 target.

It has been decided that the existing tables won't be moved. New log tables should consider using performance schema.