MySQL Bugs: #29958: Weird message on DROP DATABASE if mysql.proc does not exist

Bug #29958	Weird message on DROP DATABASE if mysql.proc does not exist
Submitted:	22 Jul 2007 14:29	Modified:	20 Mar 2008 8:38
Reporter:	Philip Stoev	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0.41-debug-log	OS:	Linux
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
If DROP DATABASE is issued against a server missing the mysql database or the mysql.proc table, a non-descriptive error will be returned.

How to repeat:
mysql> drop database no_such_database;
ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist

Suggested fix:
In my humble opinion, the error message should read "no such database" regardless of the existance of a mysql.proc table.

If the lack of a mysql.proc table prevents MySQL from deleting any stored procedures associated with the database, this should be reflected in a warning.

Thank you for the bug report. Version 5.1 doesn't has this bug.

6.0.0-alpha-community-nt-debug seems to be OK.

mysql> drop table mysql.proc;
ERROR 1051 (42S02): Unknown table 'proc'
mysql> drop database no_such_database;
ERROR 1008 (HY000): Can't drop database 'no_such_database'; database doesn't exist

I am not sure, however it appears that sometimes even existing databases can not be dropped.

Alexander, the fix is to silence the error with an internal error handler when trying to drop procedures from a database.

I'm not sure we should fix it at all - after all this only happens when mysql. tables are not upgraded properly.

The way I see it, MySQL must function properly even when missing a mysql database. This helps when embedding MySQL (e.g. using libmysqld (although I have not tested if this particular issue applies to libmysqld)).

Yes, MySQL always used to work with old (not upgraded) privilege tables. And it's quite common not to upgrade them, e.g. to simplify a downgrade

Can we at least have a warning on startup when the mysql database is missing? I think that many installations out there operate with that database missing without realizing it, thus leading to those weird error messages. One scenario I can think of is installing MySQL from RPM, then uninstalling it and installing from a source tree without running the script that creates the mysql database.

Ok I just tried to drop a database:

[root@fedora6 mysql]# mysqladmin drop market
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'market' database [y/N] y
mysqladmin: DROP DATABASE market failed;
error: 'Table 'mysql.proc' doesn't exist'

and the database was not dropped. Therefore, if the mysql.proc table is missing, databases can not be dropped altogether.

I am moving this bug to the "Data Definition Language" category.

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/44271

ChangeSet@1.2566, 2008-03-20 11:36:35+03:00, anozdrin@quad. +2 -0
  A test case for Bug#29958: Weird message on DROP DATABASE
  if mysql.proc does not exist.

This bug seems to have been already fixed. Added a test case.
Pushed into 5.1-rt.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

No end-user changes to document - push involved test case only.