mysql table exists and doesn't

I am trying to restore from a dump:

DROP TABLE IF EXISTS `patients`;
CREATE TABLE `patients` (
  `patientID` int(11) NOT NULL auto_increment,
  `QS1CODE` varchar(24) character set utf8 collate utf8_bin default NULL,
  `DoctorCode` int(11) default NULL,
....
  PRIMARY KEY  (`patientID`)
) ENGINE=InnoDB AUTO_INCREMENT=184163 DEFAULT CHARSET=utf8;



And I get:

ERROR 1051 (42S02): Unknown table 'patients'
ERROR 1050 (42S01): Table 'patients' already exists
ERROR 1146 (42S02): Table 'rperry_wha.patients' doesn't exist
ERROR 1146 (42S02): Table 'rperry_wha.patients' doesn't exist
ERROR 1146 (42S02): Table 'rperry_wha.patients' doesn't exist
ERROR 1146 (42S02): Table 'rperry_wha.patients' doesn't exist
ERROR 1146 (42S02): Table 'rperry_wha.patients' doesn't exist
ERROR 1146 (42S02): Table 'rperry_wha.patients' doesn't exist
rperry_whaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

racekCommented:
add name of the schema

DROP TABLE IF EXISTS perry_wha.patients;
0
rperry_whaAuthor Commented:
I've tried that:

mysql> use rperry_wha;
Database changed
mysql> drop table rperry_wha.patients;
ERROR 1051 (42S02): Unknown table 'patients'
mysql> FLUSH TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table rperry_wha.patients;
ERROR 1051 (42S02): Unknown table 'patients'
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> UNLOCK TABLES ;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table rperry_wha.patients;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
0
UmeshSenior Principal Technical Support EngineerCommented:
It seems you have copied InnoDB tables from one server to another..and you ended up having only .frm files and no reference to that table in InnoDB data files/data dictionary..  those tables are orphan tables and can't be simply dropped using SQL DROP command... you need to manually delete the files from the file system ( .frm files )

You just locate the patients.frm file in the MySQL's datadir/rperry_wha/patients.frm  once you delete this file manually you can execute/import the SQL file as you were trying earlier.

For more details better you take a look at the MySQL error log.... which instructs you in similar way
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.