Dropping database with invalid name
Wow, it's been quite awhile since I've posted here, or even so much as lurked. Leave it to a weird database problem to bring me back...
I'm using MySQL 4.1.20, and I was somehow able to create a dabatase with a period in it, which I later found out is an invalid character. Well, now it won't let me drop the database or even view its contents. When I try to do anything involving it, I get the following message:
#1102 - Incorrect database name 'xxxxxxx.xxx'
Any ideas how I can get rid of this database?
pr0gr4mm3r posted this at 01:10 — 24th February 2008.
He has: 1,502 posts
Joined: Sep 2006
Have you tried escaping the dot? So, if the database was "Some.DB", type in "Some\.DB".
timjpriebe posted this at 07:53 — 24th February 2008.
He has: 2,667 posts
Joined: Dec 2004
Yes, I did try that, to no avail.
greg posted this at 09:28 — 24th February 2008.
He has: 1,581 posts
Joined: Nov 2005
Urm, I know this sounds kinda obvious which leaves me thinking it's not possible or you have already tried it, can't you rename the DB?
Or export all the tables, create another DB with the name you want and import the tables into that.
timjpriebe posted this at 15:06 — 24th February 2008.
He has: 2,667 posts
Joined: Dec 2004
I actually don't even need the db any more. However, it won't let me delete or rename it, or even view the tables within it. I'm fine with completely deleting it, as it was just a temporary copy of another table.
Tim
http://www.tandswebdesign.com
teammatt3 posted this at 16:36 — 24th February 2008.
He has: 2,102 posts
Joined: Sep 2003
Do you have root access to the server? You should be able to go into the mysql data dir and delete the database at a file level. My data dir is /var/lib/mysql. Inside that directory, you will see all your databases (as directories), just remove the directory named after the database.
It worked for me. I don't know about the repercussions of doing it though...
timjpriebe posted this at 19:13 — 24th February 2008.
He has: 2,667 posts
Joined: Dec 2004
The repercussions are what worries me. I have over a dozen clients using databases on the same db server, so I need to know that whatever fix I try is not going to screw their stuff up.
Plus, unfortunately, I do not have root access to my server, even though it's a dedicated server.
Tim
http://www.tandswebdesign.com
Greg K posted this at 21:10 — 24th February 2008.
He has: 2,145 posts
Joined: Nov 2003
try properly calling it's name wrapping it with mySQL's quoting character, the backtick. ` <- to the left of the 1 key
SELECT `fieldname` FROM `database`.`table`;
DROP DATABASE `data.base`;
-Greg
JeevesBond posted this at 00:41 — 25th February 2008.
He has: 3,956 posts
Joined: Jun 2002
If what Greg K suggested doesn't work, you're probably going to have to raise a support ticket with your hosts. Not having root access is a pain! We have that problem here.
I gave it a try myself, by manually creating a folder in
/var/lib/mysql/borked.name, then tried toDROP DATABASEon it. It failed with the usual 'incorrect database name' error.It doesn't help much, because you don't have root access, but deleting the database directory didn't have any nasty side-effects (as far as I could see: your mileage may vary).
a Padded Cell our articles site!
timjpriebe posted this at 06:30 — 25th February 2008.
He has: 2,667 posts
Joined: Dec 2004
The directory has been deleted, and there seems to be no adverse effects. Thanks for all the help!
harishaero posted this at 08:33 — 19th June 2013.
They have: 1 posts
Joined: Jun 2013
check if you might have used dot anywhere in the database.. be it in the name of table or column...
I used a dot in "D.O.B" which is the reason for error..
working fine after i removed the dot
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.