Bug #31784 Tablespace and Logfile group operations not allowed within INFORMATION_SCHEMA
Submitted: 23 Oct 2007 11:18
Reporter: Hartmut Holzgraefe Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Cluster: Disk Data Severity:S3 (Non-critical)
Version:mysql-5.1 OS:Linux
Assigned to: CPU Architecture:Any
Tags: 5.1.22, usability

[23 Oct 2007 11:18] Hartmut Holzgraefe
Description:
While INFORMATION_SCHEMA is the active database no CREATE/ALTER/DROP operations on logfile groups and table spaces are allowed, these all fail with 

  ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

instead.

This doesn't make sense as tablespaces and logfile groups are not tied to any specific database, and esp. not to I_S, at all.

It is also pretty inconvenient as the I_S is the only place to retreive information on existing tablespaces and logfile groups, so one either has to explicitly use the fully qualified database.table name INFORMATION_SCHEMA.FILES while using a different active database than I_S to have access to this information and to be able to issue such commands or one has to switch between I_S and a different dummy database to do administrative work on tablespaces and logfile groups

How to repeat:
mysql> use information_schema;
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> CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.dat' ENGINE NDB;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'
mysql> use test
Database changed
mysql> CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.dat' ENGINE NDB;
Query OK, 0 rows affected (35.67 sec)

mysql> use information_schema
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> select distinct(logfile_group_name) from files;
+--------------------+
| logfile_group_name |
+--------------------+
| lg_1               | 
+--------------------+
1 row in set (0.01 sec)

mysql> DROP LOGFILE GROUP lg_1 ENGINE NDB;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'
mysql> use test
Database changed
mysql> DROP LOGFILE GROUP lg_1 ENGINE NDB;
Query OK, 0 rows affected (0.68 sec)

mysql> CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.dat' ENGINE NDB;