Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm not that experienced with MySQL and I'm trying to fix some databases.

In mysql, a "SHOW DATABASES" shows (among others) the main database "MainDB" and another database "MainDB.res". If I'm trying to DUMP or USE that second database (with `` around the name), I get the error
ERROR 1102 (42000): Incorrect database name 'MainDB.res'.
Dumping the MainDB works without problems. Now I'm wondering if MainDB.res is some special or maybe auto-generated database that the MySQL admin has no rights for? How can I look into that database?


Detailed information:
Originally I wanted to see all ibfk connections between all tables.
Now I'm searching for a bug in the databases because I can't look into information_schema, as a
mysql> SELECT * FROM information_schema.KEY_COLUMN_USAGE;
results in the error message
ERROR 1103 (42000): Incorrect table name 'accessgroup'
and I read some forum posts suggesting that the error happens because more than one table was found with that name, and renaming it may solve the problem. That is the reason I wanted to take a look at "MainDB.res" in the first place...

EDIT:

MainDB.res/ is indeed a direcory (in /var/lib/mysql/) containing some, but not all files in the directory MainDB/. Some missing files are of type .frm, some of .MYD and .MYI. 'accessgroup.frm' is in both directories. (I can't anonymize the output of ls -l enough to paste it here.) I'll try what happens if I move the directoy MainDB.res somewhere else.

share|improve this question
    
Please paste the output of ls -la /location_to_datadir/MainDB.res/...In my opinioun it is not the actual database –  Abdul Manaf May 29 '12 at 8:38
    
Can't paste that here, but added a description of the contents. –  Martin May 29 '12 at 8:56
add comment

3 Answers 3

The MainDB.res is not the Actual database.Someone by mistake have created it in your data directory.

Have a look at the content of my datadir

-rwx------ 1 mysql mysql     0 2012-05-23 14:48 debian-5.1.flag
drwx------ 2 mysql mysql  4096 2012-05-28 16:09 mysql
-rwx------ 1 mysql mysql     6 2012-05-23 14:49 mysql_upgrade_info
-rw-rw---- 1 mysql mysql     5 2012-05-29 09:15 OSS-240.pid
drwx------ 2 mysql mysql 36864 2012-05-28 16:58 test
drwx------ 2 mysql mysql  4096 2012-05-29 10:39 world
drwxr-xr-x 2 mysql mysql   4096 2012-05-29 14:29 world.res

where world.res is the directory which i have created as

mkdir world.res

and now when i want to acess it in the MySQL Shell see the result(The world.res is created as #mysql50#world.res by itself. )

mysql> use #mysql50#world.res
ERROR 1102 (42000): Incorrect database name '#mysql50#world.res'

So in my opinion the MainDB.res is not the database.

UPDATE

In MySQL you can't create the Database's that contains the word "."

mysql> create database test.res;
ERROR 1064 (42000): 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 '.res' at line 1

so it is sure that that was not your database.

share|improve this answer
    
You're right. It doesn't even work with ` backticks `. –  Martin May 29 '12 at 9:14
    
ok..cheers..!!! –  Abdul Manaf May 29 '12 at 9:15
add comment

if think if a database "MainDB.res" is shown then there exists a directory "MainDB.res" in the mysql directory. Perhaps you have created this directory with operation system tools. What is the content of the "MainDB.res" directory?

share|improve this answer
    
See my edit. I'll try stopping mysql and moving that directory somewhere else... –  Martin May 29 '12 at 8:57
    
Indeed the original owner of this database must have created that directory by copying (probably as a backup). I was able to resolve my problem, so I posted the answer for future reference. :) –  Martin May 29 '12 at 9:16
add comment
up vote 0 down vote accepted

The answer by miracle173 pointed me in the right direction:

The database 'MainDB.res' probably was a backup of 'MainDB' by copying the directory. That way, the copy was seen by MySQL as another database and this invalidated the entries in 'information_schema', as were the databases 'MainDBOld', 'MainDBTest' and 'mysql.old'...

After I moved 'MainDB.res' out of the MySQL directory, the ERROR 1103 changed to ERROR 1103 (42000): Incorrect table name 'columns_priv' (the table name changed).

After moving all "duplicates" (always stopping mysqld), the query SELECT * FROM information_schema.TABLE_CONSTRAINTS; worked immediately.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.