3

How can i delete database with # in name? Like that: #mysql50#mysql.backup

I don't know who is possible to created it, but it happened. When I try DROP DATABASE name, I have error:

ERROR 1102 (42000): Incorrect database name

Thank you for helping.

4
  • What is the command you tried to run? Oct 30, 2012 at 10:38
  • 1
    I wonder if you could just delete the myisam files. Risky, but if all else fails. Oct 30, 2012 at 10:41
  • I tried "DROP DATABASE #mysql50#mysql.backup" Oct 30, 2012 at 10:46
  • 1
    Is it correct that the database name is "#mysql50#mysql" and that "backup" is a table? Even when using proper escaping, a dot (.) should not be allowed in names.
    – Cheatah
    Oct 30, 2012 at 11:08

9 Answers 9

6

You might have hidden folder in data directory. Please do a ls -larth and the remove mysql.backup directory.

By default any folders inside the mysql data directories will be read as database and will be shown in "show databases".

4

You should always escape database names using backtick quotes, at least in case of doubt.

DROP DATABASE `#mysql50#mysql.backup`
1
  • 1
    Is not possible :( mysql> DROP DATABASE #mysql50#mysql.backup; ERROR 1102 (42000): Incorrect database name '#mysql50#mysql.backup' I used backtick :( Oct 30, 2012 at 11:09
2

Any directory in MySQL datadir is considered as database by MySQL. Most likely you have dedicated mount point for /var/lib/mysql with ext{4,3,2} filesystem and default 'lost+found' directory present (lost+found directory is used by file system check tools (fsck)). You can say MySQL to treat this directory as directory and not database with the help of ignore_db_dir option in my.cnf:

ignore_db_dir=lost+found

In your particular (#mysql50#mysql.backup) case it would be:

ignore_db_dir=mysql.backup
1

If the storage engine of the database is MYISAM just stop the database server and remove the directory named after your database in you data directory. (default var/lib/mysql)

1

I had this issue as well. I could (and did) move the .rocksdb database folder out of /var/lib/mysql but as soon as mariadb was restarted it recreated the darn thing. This was creating havoc with my sql backup routine. Final fix was:

  1. stop mariadb and trash/mv the /var/lib/mysql/.rocksdb
  2. edit /etc/my.cnf.d/rocksdb.cnf
  3. Comment out "plugin-load-add=ha_rocksdb.so"
  4. restart mariadb 5)Live happily ever after.
0

What you could try is to change the name of the database by changing the name of its data directory and then drop the database or just directly delete the directory if you want and the database is deleted as well.

2
  • Your guess is wrong. Both the hash (#) and dot (.) are normally not allowed in database/table/column names.
    – Cheatah
    Oct 30, 2012 at 11:07
  • You are right Cheatah! I will fix that now.
    – Adia
    Oct 30, 2012 at 11:21
0

Could it be that you were in a different SQL mode when this table got created? Database names like this would be possible on different SQL servers. What you can try is to switch MySQL to a different SQL mode as described here and then try to drop the table.

If this doesn't work then I would recommend as well to delete the database files.

0

I had this same problem. I found the following problems.In my /var/lib/mysql/ folder there were items like this:

._cache_block.frm ._cache_block.ibd ._ctools_css_cache.frm ._ctools_css_cache.ibd ._field_revision_field_slide_show_image.frm ._field_revision_field_slide_show_image.ibd ._field_revision_field_tags.frm ._field_revision_field_tags.ibd ._taxonomy_vocabulary.frm ._taxonomy_vocabulary.ibd

I removed them. I was then able to run my MySQL commands (in this case mysqldump) properly.

0

For CentOS 7 change directory to /var/lib/mysql

cd /var/lib/mysql

sudo rm -R .mysql.backup

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .