Dropping database with invalid name

timjpriebe's picture

He has: 2,667 posts

Joined: Dec 2004

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's picture

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's picture

He has: 2,667 posts

Joined: Dec 2004

Yes, I did try that, to no avail.

greg's picture

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's picture

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.

teammatt3's picture

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's picture

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. Sad

Greg K's picture

He has: 2,140 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's picture

He has: 3,955 posts

Joined: Jun 2002

Plus, unfortunately, I do not have root access to my server, even though it's a dedicated server.

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 to DROP DATABASE on it. It failed with the usual 'incorrect database name' error. Sad

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's picture

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!

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.