We were migrating our database from MySQL5.6 to MySQL5.7 and we noticed that the database was corrupted. We are still using the same database as we lost the original database (so there is no way we can get another backup). SonarQube 6.7.4 seems to be running ok for now. But our DB admin thinks there is some inconsistency that we might not be capturing at the moment and it could bite us later in the days.
I wanted to check to see if there is any way I can check the consistency state of the database by running a command in the background while starting it up or anything on the MySQL side to check the status ?
Also making sure that by doing the check for consistent state, we are not introducing a time lag or discrepancies in the reports while running SonarQube.
Before backing up the database, we did not shut down the SonarQube. So the database that was backed up had tables that were out of sync. And after the backup, the original DB file was deleted so we do not have any original file to backup once again.
That’s why looking for options to see if we can check for the consistent state.
Well it depends on how you ran the backup of the DB. Usually MySQL backups are made in a transactional fashion-way, so the connexion reading datas and writing them into the backup file is respectful of the current other running transactions (if you did not changed the default isolation level to something like READ_UNCOMMITED, which may cause dirty reads and lead to inconsistency). With the default isolation level, you will not have inconsistency, but you may have some data missing : basically any operation on the DB started AFTER the backup started.
So You would want to check to the transaction isolation level used for the backup, and check that your re-run any analysis performed after the backup started.
The default behaviour of mysqldump is to lock tables when performing the backup. In your case, you can check that everything is alright by re-scanning all projects.
Side note : MySQL support for SonarQube get dropped with the 7.9 version. You should upgrade at your early convenience, and in the process you will need to migrate from MySQL to another supported database (PostgreSQL, Oracle, Microsoft SQL Server). We developed a MySQL migrator tool to help you achieve this migration.