Bug #44897 PerformanceSchema: can create a ghost table in another database
Submitted: 15 May 2009 9:30 Modified: 14 Jan 2010 18:04
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:6.0-perfschema OS:Linux
Assigned to: Marc ALFF CPU Architecture:Any

[15 May 2009 9:30] Guilhem Bichot
Description:
use test;
create table t like performance_schema.EVENTS_WAITS_CURRENT;
show tables;
select * from t;

gives:
mysql> use test;
Database changed
mysql> create table t like performance_schema.EVENTS_WAITS_CURRENT;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t              | 
+----------------+
1 row in set (0.00 sec)

mysql> select * from t;
ERROR 1146 (42S02): Table 'test.t' doesn't exist

So "show tables" says that table exists, SELECT contradicts.
And in the "test" directory there is only t.frm.
This is because CREATE LIKE creates a table of the same engine as the original table; perfschema engine has no files, so we get only a frm.
Other example:
create table u (a int) engine=performance_schema;
show tables;
select * from u;
gives the same error.
The second example is a bit weird (who would do that?), but the first example (CREATE LIKE) did happen by accident without bad will. I just wanted to create a table having the same columns as the P_S table (having forgotten about the engine trap). So users will run into this too.

How to repeat:
see description
[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.
[4 Nov 2009 1:23] Marc ALFF
The fix for this bug is implemented in ha_perfschema::create(),
which checks the table fully qualified name,
and does not rely on privileges in sql_acl.cc

Tests have been added in misc.test (in mysql-trunk-perfschema)

Re-assigning guilhem as reviewer.
[4 Nov 2009 18:23] Marc ALFF
The fix is queued in mysql-trunk-perfschema
[14 Jan 2010 10:02] Marc ALFF
Merged in:
- mysql-next-mr (Celosia / 5.5.99-m3)
- mysql-6.0-codebase (6.0.14)
[14 Jan 2010 18:04] Paul DuBois
Not in any released version. No changelog entry needed.