?
Solved

Can't drop MySQL database with bad name

Posted on 2011-09-18
11
Medium Priority
?
778 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
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

764 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