WARNING: Please use an extreme level of caution when interacting with the database as some changes are irreversible.
If any of the following services are running, please stop them before proceeding.
- All Observer services
- Sphinx
- Redis
Check the database logs in {Database Home}\data\*.err file. If it indicates tables are crashed or corrupted see below for instruction on how to repair those tables.
In most cases the MariaDB files will be located in C:\Program Files\MariaDB xx.xx\data\
It is also possible that the database files may be written to a folder in an attached RAID array at C:\video, possibly "SQL" or "database" (depends on the name given to it when a VDMS tech migrated the database tablespace to the RAID array)
Repairing a Database
In SQLyog:
1. Choose Tools > Table Diagnostics from the toolbar,
2. Select the correct Database from the "Select database and table(s)" drop down
3. Make sure the indicated table is checked in the list underneath the drop down. By default all tables in the database will be checked when opening this tool.
4. Click on "Repair..."
In Heidi SQL:
1. Choose Tools > Maintenance from the toolbar
2. In the left hand pane, expand the Database and tables and check the correct tables for repair
3. In the Operation drop down, select "repair"
4. Click on execute
From command line:
1. Open an Administrator command prompt
2. Run the following command: mysqlcheck -u root -pfirstbest -A --auto-repair
* Please note that this command will check ALL database tables and can take some time to complete. The following link is documentation for the command and options can be used to produce an analysis of the database and more directly target tables for repair: https://mariadb.com/kb/en/mysqlcheck/
MySQL Will Not Repair or Will Not Start Post Repair
If the MariaDB service will not start, or the *.err file has the following message "Tablespace is missing for table xxxx", it will be necessary to run the database in recovery mode.
1. If the MySQL service is running, it will be necessary to stop it.
- If it becomes stuck in stopping, it will be necessary to use Task Manager to "End Process" on the mysqld.exe process
2. In the {Database Home}\data\ folder, open my.ini file in a text editor (notepad or notepad++) and add the following line under the [mysqld] section
innodb_force_recovery=1
3. Start the MySQL service
3. After MySQL starts and recovers the tables, stop the MySQL service and remove the "innodb_force_recovery" line from the my.ini file
4. If the MySQL will not start, increase the value of the number in "innodb_force_recovery" by 1
for example: innodb_force_recovery=2
5. If the MySQL will not start at innodb_force_recovery=4 or lower, then proceed to the next section.
Rebuilding Databases
If MySQL will only start with innodbo_force_recovery value of 5 or 6 (max value is 6), then it will be necessary to rebuild the databases. Once the database starts, it will be necessary to make a backup of the databases. use the following commands to create backups of the tables in the C:\backup folder:
mysqldump -u root -pfirstbest observer --ignore-table observer.tbl_logs --result-file=\backup\observer.sql
mysqldump -u root -pfirstbest dpiindexing --result-file=\backup\dpiindexing.sql
mysqldump -u root -pfirstbest ccindexing --result-file=\backup\ccindexing.sql
mysqldump -u root -pfirstbest indexing_hdw --result-file=\backup\indexing_hdw.sql
mysqldump -u root -pfirstbest indexing --result-file=\backup\indexing.sql
mysqldump -u root -pfirstbest hmailserver --result-file=\hmailserver.sql
Please note the following items about running mysqldump:
- If any of the above databases do not exist, those commands can be skipped. If there's a database that exists on the server that's no listed above, use the syntax above to backup the database.
- The mysqldump command for the observer databse is run with the --ignore-table option. This is to exclude the very large tbl_logs table. It only contains encoder events, and in most cases is not necessary for the customer to retain this information
- It will not be necessary to do these steps with the information_schema, mysql, performance_schema, or test databases.
Database Backup Failure
If the backup continuously fails with any of databases, either by a failure error or an error that says the connection has been lost with the SQL server wen trying to backup a database, then the database has experienced a severe level of corruption. More than likely the data within the database will ultimately be lost.
It will be necessary to backup the databases structure. At a minimum, the database structure will need to be backed up in order to rebuild a database in this state. To back up the database structure, us the following syntax. Modify the syntax to backup the appropriate database.
mysqldump --no-data -u root -pfirstbest indexing_hdw --result-file=\backup\indexing_hdw.sql
Dropping Old Databases
Note: Dropping a database will delete the database from the system and all of its data. Ensure that you have a backup before dropping a database.
Once the database mysqldump backups have been created, it will be necessary to drop those databases. Take note of the names of the databases, this will be important for recreating them later. Dropping the old databases can be done in the following ways. It is not be recommended to drop the database from the cmd line.
In SQLyog:
1. Highlight the database in the left hand pane
2. Click on "Database > More Database Operations > Drop Database" from the tool bar
In HeidiSQL:
1. Right click on the database in the left hand pane and select Drop...
Restoring Databases
After dropping the old databases, use the following steps to restore the databases.
1. Stop the MySQL service
2. Remove the "innodb_force_recovery" line from my.ini
3. Start the MySQL Service
4. Create new databases (with default options) with the same names as those that were dropped
5. Import the .sql files that were created earlier using the mysqldump commands. Modify the syntax for any .sql files that were created, but not listed here.
mysqldump -u root -pfirstbest observer < \backup\observer.sql
mysqldump -u root -pfirstbest dpiindexing < backup\dpiindexing.sql
mysqldump -u root -pfirstbest ccindexing < \backup\ccindexing.sql
mysqldump -u root -pfirstbest indexing_hdw < \backup\indexing_hdw.sql
mysqldump -u root -pfirstbest indexing < \backup\indexing.sql
mysqldump -u root -pfirstbest hmailserver < \hmailserver.sql
6. Restart any services that were stopped previously.
