Bug #43012 Bad error message handling in "drop database"
Submitted: 19 Feb 2009 10:04 Modified: 4 Jun 2009 22:43
Reporter: Jørgen Løland Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:6.0 OS:Any
Assigned to: Alexander Nozdrin CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[19 Feb 2009 10:04] Jørgen Løland
Description:
MySQL 6.0 handles error messages from drop database wrongly:

1) The warning stack is not reset, and therefore contains errors from the previous operation executed
2) The reported error is not shown in "show warnings"
3) The error message is misleading

The following line in sql_table.cc seems to be involved, as changing it fixes problem 2:

1654: /* Don't give warnings for not found errors, as we already generate notes */
1655: thd->no_warnings_for_error= 1;

How to repeat:
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);

connection con1;
set autocommit=0;
create database try;
use try;
create table t3(id int) engine=falcon;
insert into t3 values(1),(2);

connection con2;

create database try;
ERROR HY000: Can't create database 'try'; database exists
show warnings;
Level	Code	Message
Error	1007	Can't create database 'try'; database exists

## Notice how the error message from a failing create database 
## is both reported as an error *and* shown in the warning list

drop database try;
ERROR 42S02: Unknown table 't3'
show warnings;
Level	Code	Message
Error	1007	Can't create database 'try'; database exists

## Notice that the error message from "create table" is still here, that 
## the error message seems wrong, and that the error message is not in
## the error list

Suggested fix:
1) Clear warning stack for drop database statement: thd->warning_info->opt_clear_warning_info(thd->query_id);

2) Maybe remove the no_warnings_... =1;? Needs more thinking

3) Change the error message to something like "Error dropping database"
[19 Feb 2009 11:39] Miguel Solorzano
Thank you for the bug report. Verified as described.
[20 Feb 2009 12:02] Alexander Nozdrin
Basically, there are two problems here:
  - Warning stack is not cleared for DROP DATABASE;
  - Error message is misleading.

Problem 1: incorrect warning stack
----------------------------------

The warning stack is not cleared due to the following:

  - it is cleared in sql_parse.cc in the following lines:
      if (all_tables)
        thd->warning_info->opt_clear_warning_info(thd->query_id);

  - since DROP DATABASE (as well as some other statements,
    e.q. CREATE DATABASE) does not have tables, all_tables is NULL,
    so the warning stack is not cleared.

That can easily be checked by the following test case:

  > CREATE DATABASE db1;
    --> Ok

  > CREATE DATABASE db1;
    --> Error: Can't create database 'db1'; database exists

  > SHOW WARNINGS;
    Can't create database 'db1'; database exists
  -- That's Ok.

  > CREATE DATABASE db2;
     --> Ok

  > SHOW WARNINGS;
    Can't create database 'db1'; database exists
  -- This warning is from the previous 'CREATE DATABASE db1'
  -- and should not be here.

So, basically, what we did:
  - issue a statement, that throws a warnings
    (or both an error and a warning)
  - issue successful statement, that doesn't have tables;
  - see that warnings are left from the previous statement.

Probably, that's needed to be reported/dealt with as a separate
issue/bug.

NOTE: if CREATE DATABASE fails, it throws both an error and warning.
If DROP DATABASE fails, it throws only an error
due to THD::no_warnings_for_error flag.
So, when CREATE DATABASE fails, the warning stack will be correct.
When DROP DATABASE fails the warning stack will contain warnings
from the previous statement.

Problem 2: misleading error message
-----------------------------------

"Unknown table" error comes from mysql_rm_table_part2() function,
which was originally written to handle DROP TABLE statement.
The error message is Ok for this statement.

Nowadays, the function is used also in processing
of DROP DATABASE statement, but the error message is not good
for that purpose.

Ideally, high-level error handling should be done on high-level
functions. Here high-level error messages are generated in the
low-level functions. We need either to invent an error message
that would fit for both purposes, or move error handling to
level of DROP TABLE / DROP DATABASE handlers.
[20 Feb 2009 21:56] Konstantin Osipov
The fact that the error is also present in the warning list is not a bug, it's a documented behavior:
http://dev.mysql.com/doc/refman/5.0/en/show-warnings.html

In MySQL, error, warnings, and notes are all reported as warnings and only differ in severity.

The other bit of the test case, when one gets "No such table" for DROP DATABASE call I can't repeat.

Please clarify what the complain is about.
[20 Feb 2009 21:58] Konstantin Osipov
Alik:
DROP DATABASE is not a statement that uses tables. 
Therefore if it succeeds, the warning stack should not be cleared.
If it fails, the error for the failure will clear all previous errors.

Quoting the manual page above:

Statements that do not use tables and do not generate messages have no effect on the message list.
[20 Feb 2009 22:00] Konstantin Osipov
Alik, your next statement:

When DROP DATABASE fails the warning stack will contain warnings
from the previous statement.

actually represents a bug. Since it's not part of the test case in "How to repeat" section, and one can't edit this section, I suggest to report it separately.
[20 Feb 2009 22:01] Konstantin Osipov
With regard to the "No such table" error message, I don't see how you can repeat it.
[23 Feb 2009 11:47] Jørgen Løland
Repeat "No such table" (mtr script):

TEST FILE:
----------
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);

connection con1;
set autocommit=0;

create database try;
use try;
create table t3(id int) engine=falcon;
insert into t3 values(1),(2);

connection con2;

--error 1051
drop database try;
show warnings;

RESULT FILE:
------------ 
set autocommit=0;
create database try;
use try;
create table t3(id int) engine=falcon;
insert into t3 values(1),(2);
drop database try;
ERROR 42S02: Unknown table 't3'
show warnings;
Level	Code	Message
[23 Feb 2009 11:53] Jørgen Løland
Regarding "The fact that the error is also present in the warning list is not a bug, it's a documented behavior: http://dev.mysql.com/doc/refman/5.0/en/show-warnings.html"

I'm not saying that it's a bug that the error message is in the warning list like this:

create database try;
ERROR HY000: Can't create database 'try'; database exists
show warnings;
Level	Code	Message
Error	1007	Can't create database 'try'; database exists

I'm saying that it's a bug that the error message is not in the warning list here:

drop database try;
ERROR 42S02: Unknown table 't3'
show warnings;
Level	Code	Message
[23 Feb 2009 15:35] Jørgen Løland
Reopening bug. Feedback given
[23 Feb 2009 22:50] Konstantin Osipov
Hm.. this is either a Falcon bug or a duplicate of Bug#989
[24 Feb 2009 10:44] Alexander Nozdrin
Reported Bug#43138: DROP DATABASE failure does not clean up message list.
[30 Apr 2009 15:47] Alexander Nozdrin
As this is going to be fixed by a patch for Bug#43138,
I'm setting this bug to Patch Queued.
[4 Jun 2009 8:41] Alexander Nozdrin
Pushed into 6.0.12-alpha (revid:alik@sun.com-20090516083402-0avycdy7w6dnn0tv) (version
source revid:serg@mysql.com-20090513180858-8n20oo3kc248e8p6) (merge vers: 6.0.12-alpha)
(pib:6)
[4 Jun 2009 22:43] Paul Dubois
Noted in 6.0.12 changelog.

DROP DATABASE did not clear the message list.
[23 Oct 2009 15:06] Paul Dubois
Noted in 5.5.0 changelog.