Getting "Tablespace" error when trying to restore database

book

Article ID: 35763

calendar_today

Updated On:

Products

CA Network Flow Analysis (NetQos / NFA)

Issue/Introduction

Issue: 

When trying to restore a database (“poller” used as an example), you may receive this error:

D:\mysql poller < poller.sql

ERROR 1813 (HY000) at line 25: Tablespace for table '`poller`.`interfaces_snmp`' exists. Please DISCARD the tablespace before IMPORT.

Environment:  

NFA 9.3.2+

Cause: 

This is because the database restore steps were done improperly. You can’t stop services and delete the files in the database folders (installdir\MySql\data\databaseName) in NFA 9.3.2+ and restore a database like NFA 9.3.1 and priot because of the new innoDB features in MySQL. For more information on how to properly backup and restore from the start in NFA 9.3.2+, please see TEC1029127. If you are already at this point and received the above error please see the solution below.

Resolution/Workaround:

  1. 1.   Stop the CA MySQL Service on the server that won’t let you restore the database.
  2. 2.   Go to the directory of the database (installdir\MySql\data\databaseName) and make sure the folder is empty.
  3. 3.   Start the CA MySQL Service on the server back up.
  4. 4.   Open and CMD prompt and type “mysql”,
  5. 5.   Type “drop table databaseName;” which should return with no errors.
  6. 6.   Now you can proceed with the database restore without errors.
  7. 7.   Start all remaining services.

Additional Information:

TEC1029127 for more information on innoDB and how to properly backup and restore databases on NFA 9.3.2+. 

Environment

Release:
Component: NQRPTA