MySQL database exists… but wait, no it doesn’t exist?

Posted January 13th, 2011 by bryanr

I recently updated our Mysql servers to version 5.1 and moved the installation to a 64 bit servers.  Afterward, I had some minors problems that led me to run the “mysql_upgrade” script on one of the servers.  Everything seemed functional after running the script, which checks the databases for incompatibilities.  Here is an explanation:

http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html

But no, one database was completely empty after performing the check.  So, I tried to restore from my handy backup.  Something strange happened and it created a new database with the same name instead of overwriting the existing schema.  I decided I wanted to remove both of the databases of concern and re-import, creating new schema.

When I tried to drop the database, I received the following error:

“Can’t drop database ‘dbname’; database doesn’t exist”

And when I tried to create a new database, it told me:

“Can’t create database ‘dbname’; database exists”

That just didn’t make any sense!  And the Mysql Administrator application still listed the database in the Catalog list.

The fix was simple; navigate to the /var/lib/mysql/ directory and remove the folder for the problem database.  After that, I was able to restore the database from my backup and everything worked.

2 Responses to “MySQL database exists… but wait, no it doesn’t exist?”

  1. Ivan Marsh

    Believe it or not when I’ve run into this issue the solution is the stupidest thing possible.

    drop database `databasename`;

    Using the backquote to surround the database name.

    Alternatively referred to as an acute, grave, grave accent, left quote, open quote, or a push, the back quote or backquote is a punctuation mark (`) found on the same U.S. computer keyboard key as the tilde.

  2. Rudi

    where I can find the /var/lib/mysql/ directory? Oh I'm very newbie about this things.

     

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>