[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Can't drop MySQL database with bad name

Posted on 2011-09-18
11
Medium Priority
?
783 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, we’ll look at how to deploy ProxySQL.
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

650 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