MySQL Bugs: #62908: User may create tables with names reserved for log tables

Bug #62908	User may create tables with names reserved for log tables
Submitted:	24 Oct 2011 11:47	Modified:	24 Oct 2011 20:02
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
User may create tables with names reserved (or rather 'should-be-reserved') for log tables.  This may lead to data loss when table logging gets enabled.

How to repeat:
SET GLOBAL general_log = 0;
SET GLOBAL log_output = 'file';

USE mysql; 
DROP TABLE IF EXISTS general_log;
CREATE TABLE general_log(id INT);

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 1;
SHOW COLUMNS FROM general_log;
-- the original user-created `general_log` table gets deleted without warning! 

Suggested fix:
The table names used by log tables should be reserved so that user may not CREATE such. It should be reserved for the server process to do.  Also DROP + DELETE FROM with no WHERE clause should be prevented (if it is not) as long as server is running with log_output = 'TABLE'. 

Or alternatively starting logging to table should return an error if a table named as a log table buth with different structure already exists.

I forgot version.  Now added.

Or maybe rather: The table names used by log tables should be reserved .. *in the `mysql` database*.

(I am perfectly aware that user should not create such table in `mysql` database . but people do weird things sometimes.)

While I agree with your concerns, I doubt we can (and should) prevent root from doing anything is mysql database...

Now, my experience is also a bit different:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.1.60 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET GLOBAL general_log = 0;
Query OK, 0 rows affected (0.02 sec)

mysql> SET GLOBAL log_output = 'file';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> USE mysql; 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DROP TABLE IF EXISTS general_log;
Query OK, 0 rows affected (0.18 sec)

mysql> CREATE TABLE general_log(id INT);
Query OK, 0 rows affected (0.08 sec)

mysql> SET GLOBAL log_output = 'TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL general_log = 1;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW COLUMNS FROM general_log;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> select * from mysql.general_log;
Empty set (0.00 sec)

mysql> desc mysql.general_log;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)

So, it is not the user table that is dropped silently, it is there, just logging to table does not happen it seems.

It seems that you are right. I tried again with 5.1.59:

SET GLOBAL general_log = 0;
SET GLOBAL log_output = 'file';

USE mysql; 
DROP TABLE IF EXISTS general_log;

CREATE TABLE general_log(id INT);
INSERT INTO general_log VALUES (1);
-- Error Code: 1556
-- You can't use locks with log tables. 
--
-- (so here the server *knows* that this is a reserved table name of some kind)

SELECT 1;
SELECT * FROM general_log; 
-- empty set

So 
1) User may specify and create a `general_log` table.
2) But she will not be able to INSERT INTO it (and the error message is also not good IMHO - but not the first time we have discussed error messages here.)
3) Starting logging to table fails with no warning or error sent to the client. 
4) I also now notice that the error log contains message "Failed to write to mysql.general_log" for every statement after logging was (attempted) started.

I still think the best solution would be to reserve the table name for the server process. What is the idea of letting user create it when it cannot even be used/INSERTED to? But I know of course that system tables have no special protection in MySQL. You can manipulate `mysql`.`user` and more as well if you only have privileges.. 

No. 2 solution is performing a check on the statement "SET GLOBAL log_output = 'table';", return an error to the client if a table non-fit for logging exists, and not just let logging fail (with a message in error log that may only be seen too late). 

Anyway I just came across this and though I would report it. It is not important for me personally.

Verified with 5.1.60. I think your suggested solution #2 (check if table with improper structure exists at the moment of enabling logging to table) makes perfect sense.