Skip to Main Content
Forums

MySQL Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

error in mysql restart after dropped and created partitioned table

keydbasMar 4 2020

I have dropped partitioned table and then created again with another partition key

as it is dropped but still in restart it shows earlier partiions' ibd files which no exists any more

200304  8:05:05  InnoDB: Error: trying to open a table, but could not

InnoDB: open the tablespace file './labrules/file_rule_hit_log#P#SEP@002d1@002d2018.ibd'!

InnoDB: Have you moved InnoDB .ibd files around without using the

InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?

InnoDB: It is also possible that this is a temporary table #sql...,

InnoDB: and MySQL removed the .ibd file for this.

InnoDB: Please refer to

InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html

InnoDB: for how to resolve the issue.

200304  8:05:05  InnoDB: Operating system error number 2 in a file operation.

InnoDB: The error means the system cannot find the path specified.

InnoDB: If you are installing InnoDB, remember that you must create

InnoDB: directories yourself, InnoDB does not create them.

200304  8:05:05  InnoDB: Error: trying to open a table, but could not

InnoDB: open the tablespace file './labrules/file_rule_hit_log#P#SEP@002d1@002d2019.ibd'!

InnoDB: Have you moved InnoDB .ibd files around without using the

InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?

InnoDB: It is also possible that this is a temporary table #sql...,

InnoDB: and MySQL removed the .ibd file for this.

InnoDB: Please refer to

InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html

InnoDB: for how to resolve the issue.

200304  8:05:05  InnoDB: Operating system error number 2 in a file operation.

InnoDB: The error means the system cannot find the path specified.

InnoDB: If you are installing InnoDB, remember that you must create

InnoDB: directories yourself, InnoDB does not create them.

200304  8:05:05  InnoDB: Error: trying to open a table, but could not

InnoDB: open the tablespace file './labrules/file_rule_hit_log#P#UPPER_CATCHALL.ibd'!

InnoDB: Have you moved InnoDB .ibd files around without using the

InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?

InnoDB: It is also possible that this is a temporary table #sql...,

InnoDB: and MySQL removed the .ibd file for this.

Comments

kuljeet singh -
if using exp give full path in FILE parameter
incase of expdp create directory with full path where you want to store dmp.
875255
im using exp. how to give parameters
Mahir M. Quluzade
Answer
If your dmp file in C:\Documents and Settings\user\TEST.dmp
You can copy to D:\DUMPS\TEST.dmp and use this file with IMP as 
  imp user/password file='D:\DUMPS\TEST.DMP'
Regards
Mahir M. Quluzade
Marked as Answer by 875255 · Sep 27 2020
875255
do we have an option like some software which automatically(makes backup after a specified period like after every hour) makes backup of the whole database as .dmp and saves it to specified location?????
JustinCave
1) Although a dump file is a logical backup, I sincerely hope you are not using it as your primary method of backing up a database. You need physical backups (RMAN or user-managed). Logical backups can compliment but not replace physical backups.

2) You could certainly schedule the export utility to run regularly and to write files to whatever location you desire. It would be exceedingly unusual to want a full database export every hour however. That generally implies that you're doing something wrong.

3) This doesn't appear to relate to database security. It would probably be more appropriate in the Export/Import/SQL Loader and External Tables forum or even in the Database - General forum.

Justin

Edited by: Justin Cave on Jun 23, 2012 1:30 AM
1 person found this helpful
1 - 5

Post Details

Added on Mar 4 2020
#advocate-mysql, #mysql-faqs, #mysql-user-groups
0 comments
294 views