Mysql 4: Invalid database name (howto delete?)

[fopetesl]

"show databases"

Databases
usernames.May2007
usernames.June2007
usernames

When I execute "use usernames.May2007" I get error:

Error 1102 (42000) : Invalid database name 'usernames.May2007'

I don't now need to keep the '..2007' databases but how do I delete them?
I get a similar error when I execute "drop usernames.May2007"

[Atli]

Hi.

To use special and reserved characters in database/table/column names, enclose them in back-ticks.
(Note, these are not the same as single-quotes!)

USE `weird.database name!`;
SELECT
 `weird.column.name`
FROM 
 `weird table name`;
 

But it's good practice to always use names that do not require the back-ticks.

[fopetesl]

Thanks, Atli. But doesn't work. :(
So is either wrong back-tick, isn't recognised or I don't have it on my UK keyboard.
On mine it's just above the <tab> key. Right? like: `````````

[Atli]

Yep, that is the right character.

As it turns out, you can not use a dot in database or table names.
(See 8.2. Schema Object Names)

I guess the only way to change the name of the database would be to rename the actual data directory. Each database on your MySQL server is stored in it's own directory under the MySQL data directory. If you rename the directory, the name of the database will also change.

So, you would have to find out where that directory is.
If you tell us which OS you are using we might be able to help.

[fopetesl]

You got it, Atli !!
What threw me was that I had the databases as named in

/var/lib/mysql

and had already deleted the '.' ones without any apparent change.
So I dug some more and found the same databases in

/usr/local/mysql/data

so I deleted the offending directories and restarted mysql .. voila! Offending databases gone. :)

So a) why were there apparent copy databases in /var/lib/mysql? And of what use?
b) why doesn't the back-tick `name` work?
c) what other methods are available to rename badly named databases?

Thanks for your leaders.

Oh. Ubuntu Breezy Badger

[Atli]

@fopetesl

1. On my Ubuntu 8.10 installation, MySQL stores it's data in /var/lib/mysql.
   I tried renaming the directories there, and the MySQL server reflected the change instantly.
   I simply installed my MySQL server from the apt package manager. Maybe you did something else?
2. It seems that even when enclosed in back-ticks, MySQL checks the name for invalid characters. And because a dot is not allowed in database and table names, it will fail the validation and bail out before the command is executed.
3. Prior to MySQL 5.1, MySQL had no way of renaming databases. The best method to get around this seems to be creating new databases and copying the contents of an old one over using mysqldump. Somewhat like:
   

   mysqldump oldDatabase -p | mysql -D newDatabase -p

   In MySQL 5.1, they changed the ALTER DATABASE command, and added a RENAME DATABASE command.
   (The RENAME command seems to have serious limitations, however)

[fopetesl]

So. I guess what you are intimating is that somewhere mysql (on my system) has been redirected to /usr/local/mysql/data and away from the usual(?) /var/lib/mysql.
I certainly haven't done anything to effect this - I don't yet have sufficient knowledge.

The puzzling aspect is the mirror status of the databases and deleting from ... lib/mysql has no apparent effect.

The system has remained, or so I believe, stable for years.

What I need to do now is to massage the backup-to-flash-drive code to reflect the real database location!

Thanks for your help. Something new every day :-))

[Atli]

@fopetesl
That would be my guess.

Although, I have never had MySQL 4 installed on Ubuntu, I've only ever used MySQL 5, so it could be that the /usr/local/mysql/data directory is the correct location.
Somebody may have installed and uninstalled MySQL 5, leaving the /var/lib/mysql data behind... I don't really know.

But... the location used by MySQL can be found in the "my.cnf" file, which is usually located in /etc/mysql/ on Ubuntu. Look for the "datadir" directive.

[fopetesl]

Hmmm. Interesting.
In /etc/mysql there are four files:

dead.letter
debian-log-rotate.conf
debian-start (executable)
debian.cnf

None of them make reference to any directories. Seem to be start-up scripts only.

So I did a search for "my.cnf" :- not on my system.
Googling (briefly) confirms what you see: "my.cnf" should be in "etc/mysql"

The system runs OK as is but it would be reassuring to know just why my databases are in "/usr/local...."? I have a small concern that I may be storing problems for the future.