Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Can't drop MySQL database with bad name

Posted on 2011-09-18
11
Medium Priority
?
787 Views
Last Modified: 2012-06-27
When using the command line interface, I had a finger check in my "source" file and accidentally created a database with an apostrophe as the last character of the name.  I have tried both command line source files and PHP MyAdmin to drop the database.  It either gives me a syntax error on the statement or tells me no database with that name exists.  It even gives me an error when I check the database in the PHP MyAdmin list and click the drop button.

Any idea on how I can kill this beast?

Thanks,
Bruce
0
Comment
Question by:springthorpeSoftware
11 Comments
 
LVL 21

Expert Comment

by:Papertrip
ID: 36558181
What are the errors?
0
 
LVL 23

Expert Comment

by:nemws1
ID: 36558213
But backtics around the database name:
DROP DATABASE `BadDatabase'`;

Open in new window

0
 
LVL 23

Expert Comment

by:nemws1
ID: 36558214
*Put (not But)
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36558221
rty

DROP DATABASE  IF EXISTS db_name

refer for more details about drop
http://dev.mysql.com/doc/refman/5.0/en/drop-database.html
0
 

Author Comment

by:springthorpeSoftware
ID: 36560322
phpMyAdmin - 2.11.9.5

Problem is that when processing MySQL converts ticks, double quotes and single quotes to single quotes. Have tried all of the suggestions so far.  Here are the two errors, with and without ticks in the input:

With ticks (note that the single quote is coverted to two, and ticks are converted to single quotes):
#1008 - Can't drop database 'db_xxx'''; database doesn't exist.

Without ticks (yes, that is an extra space and single quote added by MySQL before the statement was processed.  I entered db_xxx'.)

DROP DATABASE db_xxx ''

MySQL said:  

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''' at line 1

Other suggestions?

Thanks,
Bruce
0
 
LVL 23

Expert Comment

by:nemws1
ID: 36561366
You said in your initial comments that you had access to both phpMyAdmin and a command line interface.  Please try my suggestion again (backtics) using the command line interface:

 
DROP DATABASE `BadDatabase'`;

Open in new window


I just tested it on one of my databases, and I was able to both create and drop the database with a single quote on the end.
0
 

Author Comment

by:springthorpeSoftware
ID: 36562720
nemws1:

The original problem database was created by using a "source" file, e.g. "source [pathname]", not by typing a command directly into mysql DOS interface window.

Not 100% familiar with using the mysql command line, but here's what I tried:

To get to the mysql command prompt, I open a command prompt window, type in
   mysql -u [username] -p [database name]
and enter my password when prompted.  This produces a mysql command prompt.

When I type in the DROP command, there is no error but the database is not dropped.  The DOS prompt then displays an arrow, apparently waiting on the next command:

mysql> DROP DATABASE `db_xxx'`
     ->

Same thinig results from entering the command with a preceeding "go" or "ego".

Typing in another command, even "exit" or "quit" or "help", at that "arrow" prompt does nothing but display another "arrow" prompt.  I have to close the Command Prompt window, open a new one and log back into mysql to make it do anything.

Is there something else I should be doing?

Bruce
0
 
LVL 23

Accepted Solution

by:
nemws1 earned 1000 total points
ID: 36562757
Ah... you need to end it with a semi-colon, as I did in both my examples. ;-)
0
 

Author Comment

by:springthorpeSoftware
ID: 36562942
nemws1:

That did it!!  Many thanks!

Bruce
0
 

Author Closing Comment

by:springthorpeSoftware
ID: 36562950
Thanks for taking time to help!
0
 
LVL 23

Expert Comment

by:nemws1
ID: 36562993
Very glad I could help you.  It's always the little things that cause the biggest problems. ;-)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question