Database/Table/View structure and procedures/function/triggers checksums

[davidducos]

After merging this: #141 the testings are much simpler, as the data consistency is automatically validated. However, we are not able to check if the structure of the tables, databases, procedure, functions and triggers is correct.

[davidducos]

This might be the query to determine the checksum per table:

SELECT COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS(column_name, ordinal_position, data_type,column_type)) AS UNSIGNED)), 10, 16)), 0) AS crc 
FROM information_schema.columns 
WHERE table_schema='luna' AND table_name='t1';

per routine:

SELECT  COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(ROUTINE_DEFINITION) AS UNSIGNED)), 10, 16)), 0) AS crc FROM information_schema.routines WHERE ROUTINE_SCHEMA='sys' AND routine_name='create_synonym_db';

per trigger:

SELECT  COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(ACTION_STATEMENT) AS UNSIGNED)), 10, 16)), 0) AS crc FROM information_schema.triggers WHERE trigger_SCHEMA='sys' AND trigger_name='sys_config_insert_set_user';

per view:

SELECT  COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(replace(VIEW_DEFINITION,TABLE_SCHEMA,'')) AS UNSIGNED)), 10, 16)), 0) AS crc FROM information_schema.views WHERE TABLE_SCHEMA='sys' AND TABLE_NAME='x$waits_global_by_latency';

For database, we can use the show database statement and compare the string.
per database:

SELECT COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(concat(DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='percona' ;

We had to change some queries as the schema name was being added by mysql