MySQL: Error: Tablespace for table & #39; 'database'. 'temp '& #39; exists. Please DISCARD the tablespace before IMPORT. Solution: createtablespace

MySQL: Error: Tablespace for table 'database'. 'temp ''exists. Please DISCARD the tablespace before IMPORT. Solution: createtablespace

Today, when I operate mysql database tables on navicat, I suddenly fail to respond. Then restart the mysql service. Then start the service and check the table. If a table card is missing, add a new table. An error is reported:

Error: Tablespace for table 'database'. 'temp ''exists. Please DISCARD the tablespace before IMPORT.

But show databases does not have this table at all. I did not find a similar situation on the Internet for half a day. It took several hours.

Finally, the solution is as follows:

1. Disable all mysql operations and stop the mysql service.

2. Find the database file in the installation directory, and open the database name folder in the data folder under your installation directory. Find the file corresponding to the table and delete the damaged file.

The file name in the database should correspond to your table name. One table, two files, one data file, and the other a log file. In addition to this, there is also a "db. do not delete the opt file. Delete all other garbled files and files with errors.

3. Restart the mysql service and add the table with the same name again.

Write a program for file backup

Note that this script is used in archive log mode. In addition, you can change the username and password of the database in the script. The script contains parameters and the parameters are the target directory of the backup, note that you must have the write permission. #! /Bin/sh
Mypath = $1
If [-z $ mypath]; then
Echo 'Please Use This Command Like./backup/u01/backup'
Else
If [-d $ mypath]; then
ORACLE_SID = dzzd; export ORACLE_SID
ORAENV_ASK = NO; export ORAENV_ASK
Rm-Rf aa.txt
Sqlplus-s "system/oracle9i" <EOF
Set head off
Set feedback off
Spool aa.txt
Select tablespace_name from dba_tablespaces;
Spool off
EXIT
EOF
For spacename in 'cat aa.txt'
Do
Echo $ spacename | grep "^ TEMP"
If ["$? "-Eq" 0 "]
Then
Echo TEMP no need backup else
Else
Echo begin backup $ spacename
Sqlplus-s system/oracle9i <SETSTA
Alter tablespace $ spacename begin backup;
EXIT
SETSTA
Rm-Rf bb.txt
Sqlplus-s system/oracle9i <DATAFILE1
Set head off
Set feedback off
Spool bb.txt
SELECT 'cp' | file_name | '$ mypath' | SUBSTR (FILE_NAME, INSTR (FILE_NAME,'/',-1, 1 ))
FROM dba_data_files where tablespace_name = trim ('$ spacename ');
Spool off
EXIT
DATAFILE1
Sh bb.txt
Sqlplus-s system/oracle9i <SETSTA
Alter tablespace $ spacename end backup;
EXIT
SETSTA
Echo end backup $ spacename
Fi
Done
Else
Echo "Please Input a Right Direction"
Fi
Fi
Mhere reply to: 15:07:05
Good!
Can I have a text attachment?
Txfy replied to: 15:18:13
You cannot upload a text file and paste it into the backup file. You can run./backup/u01 after chmod 755 backup.
Steal replied to: 15:27:42 ...... the remaining full text>