MySQL Bugs: #2839: DROP TABLE IF EXISTS causes warning if table does not exist

Bug #2839	DROP TABLE IF EXISTS causes warning if table does not exist
Submitted:	17 Feb 2004 7:01	Modified:	19 Dec 2005 13:59
Reporter:	Rob Blick	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.1	OS:	Linux (Redhat 9)
Assigned to:		CPU Architecture:	Any

Description:
It would be nice if the use of "IF EXISTS" did not cause a warning if the table does not exist.  When restoring from a mysqldump, I get a warning for every DROP TABLE IF EXISTS tbl_name that gets executed (note that the DROP TABLE IF EXISTS are inserted even if I don't use the --add-drop-table option w/ mysqldump).

How to repeat:
Execute:
DROP TABLE IF EXISTS bogus;

where bogus is the name of a table that does not exist.

Suggested fix:
Do not produce a warning if the table does not exist.  The "IF EXISTS" clause implies that the user doesn't care whether the table already exists, but rather just wants to make sure the table doesn't exist after execution of the command.  A warning SHOULD be produced if the user tries to drop a non-existant table and does not specify IF NOT EXISTS.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

There are different levels of warning, and in this case the level, as mentioned in the DROP TABLE Syntax chapter of the manual, is merely a note.

It can be argued either way as to whether or not one cares to know whether the table actually existed, so this is purely an informational message for those who may in fact care.

OK, if it's not considered a bug, then call it a "feature request."  Whatever you call it, when restoring a database, you don't want to see warnings for stuff like this (because you can't examine the warnings and verify that they're just "notes" - show warnings only works for the most recent command).  Any warning when restoring a database needs to be investigated, and the only way to handle this is to go into the mysqldump file and comment out the "DROP TABLE IF EXISTS."  Do you see my point?

Changing to a feature request as above; perhaps add SHOW NOTES and extend mysql_info similarly.

Thank you for a feature request. Looks like no SQL mode now allows to get rid of these warnings.

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

There's --show-warnings option of mysql command-line client.

Has anyone addressed this?  It seems to still behave this way.  

I agree with original poster.  This should be considered a bug.  You should not generate a warning when the behavior is as expected.  There is a already a way to check if a table exists if someone wants to know that (succinctly and without generating a warning).  However, I don't know another way to do what "if exists" does as succinctly.  It leads one to believe that the command doesn't work.  

I am a big believer in that your code (or sql in this case) is not complete if you are still getting warnings.  Is the only other way to do this to search through INFORMATION_SCHEMA?

Totally agree its simply misleading and takes out the meaning of IF EXISTS.

I agree this is a bug (for me).

We use liquibase here and it fails when warnings are returned.

Maybe liquibase should not do that, but I think in the end these messages are not real warnings since we specified "IF EXISTS".

Can someone re-open this issue ?

I agree completely with the previous comments, this should not cause a warning. 

Note that there is a workaround that works in some situations, although it is cumbersome to implement in existing code.

SET sql_notes = 0;
... IF NOT EXISTS;
SET sql_notes = 1;

I also agree this clutters up monitoring and his wholly unnecessary.

Agreed! This is still an issue. I am seeing the same problem with MySQL v8.0.16

The whole idea of `DROP TEMPORARY TABLE IF EXISTS` is to not attempt to drop the table when the table does not exists. For that reason, it should not throw a warning.