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.

Rafał Kamiński
  • 187
  • 2
  • 5
  • 15

9 Answers9

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

Chandra Sekhar
  • 61
  • 1
  • 2
4

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

DROP DATABASE `#mysql50#mysql.backup`
Cheatah
  • 248
  • 1
  • 3
  • 1
    Is not possible :( mysql> DROP DATABASE `#mysql50#mysql.backup`; ERROR 1102 (42000): Incorrect database name '#mysql50#mysql.backup' I used backtick :( – Rafał Kamiński Oct 30 '12 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
lik
  • 176
  • 2
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.
user614393
  • 11
  • 1
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)

golja
  • 1,621
  • 10
  • 14
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.

Adia
  • 103
  • 3
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.

Raffael Luthiger
  • 2,001
  • 2
  • 17
  • 26
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.

ElusiveMind
  • 101
  • 1
0

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

cd /var/lib/mysql

sudo rm -R .mysql.backup
Michael Hampton
  • 244,070
  • 43
  • 506
  • 972
Ben2014
  • 1