Removed Databases Still Show Up–Cannot Be Deleted In Sqlyog

[KevinC]

I needed to move some database files from an old drive to a new OS install (I am using it on openSUSE 11.3/WINE 1.3.16).

Some I later removed (I don't recall if I dropped them in another application or if I was lazy and just deleted them from the file system), but they continue to show up in SQLyog (but not other tools like the MySQL Administrator).

If I try to drop them, I get an error informing me that there isn't such as database.

How does MySQL 'remember' these long-gone tables and how can I induce some amnesia?

Regards,

Kevin

[peterlaursen]

1) It is not safe *at all* to delete database files if there are InnoDB tables.

2) if tables are MyISAM it is still not recommended – but should work if all files (.MYI, .MYD and .fm files) are deleted.

It is not SQLyog that *invents* what dabases are there. It is simply not stored locally. It it the server that tells what databases are there, Just execuce “SHOW DATABASES;” But anyway can't you simply DROP now.

Please understand that such unauthorized use of MySQL is not our problem and beyond our support. At least not if “SHOW DATABASES;” returns a list of databases from the server identical to what SQLyog displays (what I assume it does).

[KevinC]

>

'peterlaursen' wrote:

>1) It is not safe *at all* to delete database files if there are InnoDB tables.

I know, I know. Mea culpa.

>2) if tables are MyISAM it is still not recommended – but should work if all files (.MYI, .MYD and .fm files) are deleted.

>

>

>

>

>It is not SQLyog that *invents* what dabases are there. It is simply not stored locally. It it the server that tells what databases are there, Just execuce “SHOW DATABASES;” But anyway can't you simply DROP now.

! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! !

So how do I “fix” the problem?

I didn't post on this group to get browbeaten by some smart-ass “customer support” who was never taught proper manners.

! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! !

>Please understand that such unauthorized use of MySQL is not our problem and beyond our support. At least not if “SHOW DATABASES;” returns a list of databases from the server identical to what SQLyog displays (what I assume it does).

The DB was running locally on my personal workstation, in my home, which only I use. I hardly think it was unauthorized.

[peterlaursen]

I asked if you could not simple DROP DATABASE for the databases you what to get completely rid of. This was actually meant to be a solution. Right-click the database in the Object Browser (the left pane of SQLyog) .. More Database Options .. DROP Database.

If this fails please tell the exact error (no. and description) you get. And also tell if you use MyISAM tables, InnoBD tables or both.

[KevinC]

'peterlaursen' wrote:

I asked if you could not simple DROP DATABASE for the databases you what to get completely rid of. This was actually meant to be a solution. Right-click the database in the Object Browser (the left pane of SQLyog) .. More Database Options .. DROP Database.

If this fails please tell the exact error (no. and description) you get. And also tell if you use MyISAM tables, InnoBD tables or both.

I don't recall whether they were Inno or MyISAM. Much more likely to be MyISAM as they were huge databases that were totally replaced twice a year with an update (no transactions).

I get error 1051 (Unknown table ' mrdef').

[peterlaursen]

Try this:

1) Stop the MySQL server

2) Backup your complete MySQL data folder (just in case that this does not worrk!)

3) Open same folder. Delete all folders named as the databases you want to get rid of.

4) Start the MySQL server

This should work fine with MyISAM. I think the reason for your problems could be either

1) You did not delete what you thought as server was running and files were locked.

2) or you deleted files on the table level (not database level) and did not delete both .MYD, .MYI and .frm files (in particular .frm files are often forgotten).

If this does not work I would 'give up repair of the situation' and instead dump all data you want to keep, completely remove all traces of your current MySQL installation from the system (note that the uninstaller does not – data folder and configuration files will need to be deleted manually), do a fresh install of the server and import the dumps.

[Author]

Posts