The steps listed here are recommended by CA support and sustaining teams to ensure a way to resolve a potential corruption in the InnoDB data dictionary.
As of Spectrum 9.2.0 there are 12 Spectrum Report Manager mysql tables that were converted to utilize the new InnoDB mysql engine vs. the older mysql engine MyISAM.
When upgrading, one or more of these 12 tables can become corrupt and the InnoDB cannot repair itself. After working with MySQL support, our teams determined that we can proactively backup some files to resolve the issue after the upgrade is complete. At this time there is no way to determine pre upgrade or during the install if this will be a problem, the proactive and confirming steps below is our best solution at this time.
These are updated steps from our technical tip from sustaining team/MySQL Support (posted 11/9/2012):
BEFORE UPGRADING OR INSTALLING A SPECTRUM SERVICE PACK:
- Shutdown mysql server
- Make sure that mysqld process is not running anymore and the following messages appeared in error log (hostname.err in $SPECROOT/mysql/data/ or mysql.out in $SPECROOT/mysql):
[Note] D:/win32app/Spectrum/mysql/bin/mysqld.exe: Normal shutdown
[Note] D:/win32app/Spectrum/mysql/bin/mysqld.exe: Shutdown complete
- Make sure no [ERROR] messages occurred during shutdown
- Copy all *.ibd files and current ibdata1 file into backup location of your choice outside of the mysql area. ** This process could be time consuming depending on the size of the database;
- *.ibd files are located in $SPECROOT/mysql/data/reporting
- Ibdata1 file is located in $SPECROOT/mysql/data/
- Perform the Spectrum upgrade or install of hotfix patch
- Post install processd will restart the MySQL server, it will have to be shutdown before continuing on (Windows: use Spectrum MySQL Service, Unix/Linux use cmdC to stop and launchinstdbapp to start - see TEC516061 for more information about these two tools)
BEFORE RESTARTING THE MYSQL SERVER DO THE FOLLOWING;
- Copy the original ibdata1 file from step 4 above back into the $SPECROOT/mysql/data directory
- Now start MySQL server (Windows: use Spectrum MySQL Service, Unix/Linux use cmdC to stop and launchinstdbapp to start - see TEC516061 for more information about these two tools)
- Check the error log again for any error messages, similar to this example;
[ERROR] Cannot find or open table reporting/alarmactivity fromthe internal data dictionary of InnoDB though the .frm file for thetable exists
- If there are errors similar to the above, for each InnoDB table mentioned in an error message execute the following steps (replacing alarmactivity with actual table name):
- Run sql statement :
ALTER TABLE alarmactivity DISCARD TABLESPACE;
- Copy alarmactivity.ibd from backup location into database directory
- Run sql statement :
ALTER TABLE alarmactivity IMPORT TABLESPACE;
- Try to select data to make sure the table/s is accessible and returns no errors:
SELECT * from alarmactivity limit 2;
If you have already upgraded or installed a service pack or hotfix patch, you can do the following to determine if you have this problem or not.
Review the MySQL error log and check for errors regarding these tables not existing (similar to the error mentioned above):
If you still have errors or any questions regarding this process, please contact CA Support.
Please note: This is not an issue every customer may see, but it is a potential issue during upgrade. This can be an upgrade from 9.1 to 9.2, or a 9.2 upgrade to 9.2.2, or other service packs or hotfix patches.