| Bug #44213 | PerformanceSchema: can drop a table but not recreate it; cannot alter it. | ||
|---|---|---|---|
| Submitted: | 11 Apr 2009 10:02 | Modified: | 14 Jan 2010 18:03 |
| Reporter: | Guilhem Bichot | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Performance Schema | Severity: | S3 (Non-critical) |
| Version: | 6.0-perf | OS: | Linux |
| Assigned to: | Marc ALFF | CPU Architecture: | Any |
[16 Apr 2009 9:29]
Sergei Golubchik
this should be solved the same way as it's done for I_S tables - for I_S tables privileges are defined so that everyone can SELECT from them (grant select on I_S.* to public) and no one can do anything else.
[26 May 2009 7:32]
Jørgen Austvik
I can confirm that this happens to all tables in PERFORMANCE_SCHEMA. I am expecting ER_DBACCESS_DENIED_ERROR.
[3 Jun 2009 18:18]
Marc ALFF
Fixed with: bzr commit into mysql-6.0-perfschema branch (marc.alff:3158)
[26 Jun 2009 9:12]
Guilhem Bichot
runtime team will be better-suited for reviewing this.
[20 Nov 2009 18:55]
Marc ALFF
Clarifications: As long as the server architecture is to rely on *.frm tables in storage engines, the *.frm tables will need to be created explicitly. Tables *.frm are created by mysql_upgrade, during the server upgrade process. For mysql_upgrade to work properly, the CREATE and DROP privileges need to be granted, and the CREATE TABLE / DROP TABLE operations need to be supported by the storage engine (the performance schema). If the current user is granted the CREATE and DROP privileges, the fact that this user can actually DROP and CREATE a performance schema table is the expected result. For ALTER TABLE, this operation is not supported by the performance schema, and this operation is not needed by the upgrade process (DROP+CREATE is used instead, as there is no persistent data) The fact that any user can not ALTER a performance schema table is the expected result. A fix for this bug has been implemented, to allow the CREATE to pass, which is under review, and the code behaves as expected in mysql-trunk-perfschema. Note that: DROP + CREATE might be forbidden in later releases if *.frm files are replaced with another mechanism, but for now, "DROP + CREATE" are allowed, and the current server behavior is as expected.
[1 Dec 2009 21:41]
Marc ALFF
Reviewed by Dmitry with the SQL_ACL changes
[1 Dec 2009 21:43]
Marc ALFF
Queued in mysql-trunk-perfschema
[14 Jan 2010 10:01]
Marc ALFF
Merged in: - mysql-next-mr (Celosia / 5.5.99-m3) - mysql-6.0-codebase (6.0.14)
[14 Jan 2010 18:03]
Paul DuBois
Not in any released version. No changelog entry needed.
Description: mysql> show tables; +----------------------------------------------+ | Tables_in_performance_schema | +----------------------------------------------+ | COND_INSTANCES | | EVENTS_WAITS_CURRENT | | EVENTS_WAITS_HISTORY | | EVENTS_WAITS_HISTORY_LONG | | EVENTS_WAITS_SUMMARY_BY_EVENT_NAME | | EVENTS_WAITS_SUMMARY_BY_INSTANCE | | EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME | | FILE_INSTANCES | | FILE_SUMMARY_BY_EVENT_NAME | | FILE_SUMMARY_BY_INSTANCE | | MUTEX_INSTANCES | | PERFORMANCE_TIMERS | | PROCESSLIST | | RWLOCK_INSTANCES | | SETUP_CONSUMERS | | SETUP_INSTRUMENTS | | SETUP_OBJECTS | | SETUP_TIMERS | +----------------------------------------------+ 18 rows in set (0.00 sec) mysql> drop table COND_INSTANCES; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +----------------------------------------------+ | Tables_in_performance_schema | +----------------------------------------------+ | EVENTS_WAITS_CURRENT | | EVENTS_WAITS_HISTORY | | EVENTS_WAITS_HISTORY_LONG | | EVENTS_WAITS_SUMMARY_BY_EVENT_NAME | | EVENTS_WAITS_SUMMARY_BY_INSTANCE | | EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME | | FILE_INSTANCES | | FILE_SUMMARY_BY_EVENT_NAME | | FILE_SUMMARY_BY_INSTANCE | | MUTEX_INSTANCES | | PERFORMANCE_TIMERS | | PROCESSLIST | | RWLOCK_INSTANCES | | SETUP_CONSUMERS | | SETUP_INSTRUMENTS | | SETUP_OBJECTS | | SETUP_TIMERS | +----------------------------------------------+ 17 rows in set (0.01 sec) mysql> CREATE TABLE performance_schema.COND_INSTANCES( -> NAME VARCHAR(128) character set utf8 collate utf8_unicode_ci not null, -> OBJECT_INSTANCE_BEGIN BIGINT not null -> ) ENGINE=PERFORMANCE_SCHEMA; ERROR 1050 (42S01): Table 'COND_INSTANCES' already exists mysql> show tables; +----------------------------------------------+ | Tables_in_performance_schema | +----------------------------------------------+ | EVENTS_WAITS_CURRENT | | EVENTS_WAITS_HISTORY | | EVENTS_WAITS_HISTORY_LONG | | EVENTS_WAITS_SUMMARY_BY_EVENT_NAME | | EVENTS_WAITS_SUMMARY_BY_INSTANCE | | EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME | | FILE_INSTANCES | | FILE_SUMMARY_BY_EVENT_NAME | | FILE_SUMMARY_BY_INSTANCE | | MUTEX_INSTANCES | | PERFORMANCE_TIMERS | | PROCESSLIST | | RWLOCK_INSTANCES | | SETUP_CONSUMERS | | SETUP_INSTRUMENTS | | SETUP_OBJECTS | | SETUP_TIMERS | +----------------------------------------------+ 17 rows in set (0.00 sec) So I am allowed to drop the table but cannot recreate it. How do I get COND_INSTANCES back? On the other hand, I am not allowed to ALTER it (I'm root):I understand that, but it will cause problems, see below. I suggest: - don't allow dropping at all (can probably be caught in the perfschema engine). - make sure that there is an upgrade path for the future; assuming a new column is added to a P_S table (surely it will happen), probably we will insert some "upgrade P_S tables" logic in scripts/mysql_fix_privilege_tables* (just like when we add a column to mysql.* tables), and this logic will likely use ALTER, so root has to be able to run ALTER. How to repeat: see above.