QUESTION:
How do I configure a test to detect crashed database tables?
SOLUTION:
To create a test in Traverse to detect a crashed MySQL database table on a BVE or DGE:
- Navigate to 'Administration -> Devices' . If logged into Traverse as a 'superuser' or member of an Admin Group, then select the department where the Traverse server is provisioned.
- Select the 'Tests' link for the Traverse server (either a BVE, combined BVE/DGE or a DGE only)
- Select 'Create New Standard Tests'
- Select 'Create new tests by selecting specific monitors', check the box for the 'sql_value' monitor, select 'Add Tests'
- Choose the Test Category 'SQL Query Result (MySQL)' to highlight it, then select 'Continue'
- Check the box to the left of 'SQL Query Result (MySQL)'
- Change enter the following values:
- Port: 7663
- User Name: emerald
- Password: mysql
- Password (again):
- Database: mysql
- Query: select count(*) from (select checksum, table_comment from information_schema.tables) as tb where table_comment like '%crashed%';
Note: The above query will indicate if a table is crashed in some database. It will not specify which database.
- As Test Value Rises, Severity: Ascends
- Thresholds warn: 1
- Thresholds critical: 1
- Do not change the value for the 'JDBC Driver'
- Ensure that the box to the left of 'SQL Query Result (MySQL)' is checked, then select 'Provision Selected Tests'
- Select the 'modify' icon (orange circle with embedded arrow) for the test 'SQL Query Result (MySQL)'
- Change the Test Name to 'Number of crashed database tables' then select 'Submit'
Allow a few minutes for the new test be scheduled and the result updated. The result reflects the number of crashed tables detected within the specified database and will either be 'Ok' (zero) or 'Critical' (value greater than zero).
Note: One can use the following query to ascertain which database is affected
select table_schema,table_name,table_comment from information_schema.tables where table_comment like '%crashed%';