MySQL Bugs: #43012: Bad error message handling in "drop database"

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

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"

Thank you for the bug report. Verified as described.

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.

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.

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.

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.

With regard to the "No such table" error message, I don't see how you can repeat it.

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

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

Reopening bug. Feedback given

Hm.. this is either a Falcon bug or a duplicate of Bug#989

Reported Bug#43138: DROP DATABASE failure does not clean up message list.

As this is going to be fixed by a patch for Bug#43138,
I'm setting this bug to Patch Queued.

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)

Noted in 6.0.12 changelog.

DROP DATABASE did not clear the message list.

Noted in 5.5.0 changelog.