Description:
If I use multiple-table DELETE syntax with a fully qualified table name while the default database is Information Schema I get the error "ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'" However, the same query in different database works. And single-table DELETE also works inside Information Schema. The table does not belong to Information Schema. The user has DELETE permission on it.
How to repeat:
mysql> drop table test.t1;
Query OK, 0 rows affected (0.02 sec)
mysql> create table test.t1(f1 int);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test.t1 values(1),(2),(3), (4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
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> delete from test.t1 where f1=1;
Query OK, 1 row affected (0.00 sec)
mysql> delete foo from test.t1 as foo where f1=2;
Query OK, 1 row affected (0.00 sec)
mysql>
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> delete from test.t1 where f1=3;
Query OK, 1 row affected (0.00 sec)
mysql> delete foo from test.t1 as foo where f1=4;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'
mysql> use performance_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> delete foo from test.t1 as foo where f1=4;
Query OK, 1 row affected (0.00 sec)
Suggested fix:
Allow deleting from the table in a different database regardless the syntax.