Bug #24062 Incorrect error msg after execute DROP TABLE IF EXISTS on information_schema
Submitted: 7 Nov 2006 20:43 Modified: 12 Nov 2009 19:19
Reporter: Aurel Pekarcik Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.0.27 OS:Any (WinXP)
Assigned to: Sergei Glukhov CPU Architecture:Any

[7 Nov 2006 20:43] Aurel Pekarcik
Description:
Command "drop [temporary] table if exists information_schema.XXX"
results to error 1109 - unknown table 'XXX' in information_schema.

How to repeat:
try 

drop table if exists information_schema.XXX
drop temporary table if exists information_schema.XXX

Suggested fix:
drop if exists shoud never results to error message 1109
[7 Nov 2006 21:10] MySQL Verification Team
Any CREATE TABLE or DROP TABLE command issued against any information_schema tables will result in a 1109 error code.

This database is completely read-only so any access to modify it should return an error.

mysql> use information_schema;
Database changed
mysql> create table `testing1` (f1 INT);
ERROR 1109 (42S02): Unknown table 'testing1' in information_schema
mysql> drop table if exists `testing`;
ERROR 1109 (42S02): Unknown table 'testing' in information_schema

-- Matthew Montgomery
[8 Nov 2006 12:39] Aurel Pekarcik
Thats ok, I am understand that this database is read only...

But much better errmsg is "Access denied" then "Unknown table", isnt it ?
[9 Mar 2007 14:59] Daniel Fischer
Another example of an information_schema error message being "slightly" misleading is the following:

mysql> CREATE TRIGGER mysql.bleh AFTER INSERT ON information_schema.TABLES FOR EACH ROW SET @a=1;

ERROR 1146 (42S02): Table 'information_schema.TABLES' doesn't exist

(yes, it does exist)
[26 Mar 2008 15:12] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44460

ChangeSet@1.2572, 2008-03-26 19:01:03+04:00, gluh@mysql.com +3 -0
  Bug#24062 Incorrect error msg after execute DROP TABLE IF EXISTS on information_schema
  issue ER_DBACCESS_DENIED_ERROR error for commands
  which try to use metadata changing commands on I_S tables
[17 Dec 2008 16:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/61887

2818 Sergey Glukhov	2008-12-17
      Bug#24062 Incorrect error msg after execute DROP TABLE IF EXISTS on information_schema
      issue ER_DBACCESS_DENIED_ERROR error for commands
      which try to use metadata changing commands on I_S tables
[18 Dec 2008 10:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/61949

2822 Sergey Glukhov	2008-12-18
      additional fix for bug#24062
[20 Jan 2009 19:00] Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)
[29 Jan 2009 20:43] Paul Dubois
Noted in 6.0.10 changelog.

DROP TABLE for INFORMATION_SCHEMA tables produced an "Unknown table"
error rather than the more appropriate "Access denied".
[26 Feb 2009 14:29] MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=43227 has been marked as duplicate of this one.
[23 Oct 2009 7:55] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/87899

2915 Sergey Glukhov	2009-10-23
      Bug#24062 Incorrect error msg after execute DROP TABLE IF EXISTS on information_schema
      backport to Betony
     @ mysql-test/r/information_schema.result
        Bug#24062 Incorrect error msg after execute DROP TABLE IF EXISTS on information_schema
        backport to Betony
     @ mysql-test/suite/funcs_1/datadict/basics_mixed1.inc
        Bug#24062 Incorrect error msg after execute DROP TABLE IF EXISTS on information_schema
        backport to Betony
     @ mysql-test/suite/funcs_1/datadict/datadict_priv.inc
        Bug#24062 Incorrect error msg after execute DROP TABLE IF EXISTS on information_schema
        backport to Betony
     @ mysql-test/suite/funcs_1/r/is_basics_mixed.result
        Bug#24062 Incorrect error msg after execute DROP TABLE IF EXISTS on information_schema
        backport to Betony
     @ mysql-test/suite/funcs_1/r/processlist_priv_no_prot.result
        Bug#24062 Incorrect error msg after execute DROP TABLE IF EXISTS on information_schema
        backport to Betony
     @ mysql-test/suite/funcs_1/r/processlist_priv_ps.result
        Bug#24062 Incorrect error msg after execute DROP TABLE IF EXISTS on information_schema
        backport to Betony
     @ mysql-test/suite/funcs_1/t/is_basics_mixed.test
        Bug#24062 Incorrect error msg after execute DROP TABLE IF EXISTS on information_schema
        backport to Betony
     @ mysql-test/t/information_schema.test
        Bug#24062 Incorrect error msg after execute DROP TABLE IF EXISTS on information_schema
        backport to Betony
     @ sql/sql_parse.cc
        Bug#24062 Incorrect error msg after execute DROP TABLE IF EXISTS on information_schema
        backport to Betony
[31 Oct 2009 8:18] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091031081410-qkxmjsdzjmj840aq) (version source revid:lars-erik.bjork@sun.com-20091023144402-05x9kn8fz878z011) (merge vers: 6.0.14-alpha) (pib:13)
[31 Oct 2009 17:23] Paul Dubois
Already fixed in 6.0.10.
[31 Oct 2009 17:24] Paul Dubois
Setting report to NDI pending push to 5.5.x
[12 Nov 2009 8:21] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091102100915-a2nbfxaqprpgptfw) (merge vers: 5.5.0-beta) (pib:13)
[12 Nov 2009 19:19] Paul Dubois
Noted in 5.5.0 changelog.