-- InnoDB Cluster Checks
-- mail @ meo.bogliolo.name
-- v. 1.0.0 1st April 2017
-- v. 1.0.1 15 August 2017     Updated primary key condition (unique key are valid too)
-- v. 1.0.2 1st January 2018   Multi-Primary mode checks, savepoints are supported

SHOW VARIABLES LIKE 'VERSION';

-- Used parameters: read-committed, log slave updates, ...
SELECT @@log_slave_updates, @@binlog_format, @@relay_log_info_repository, @@binlog_checksum,
       @@report_port, @@enforce_gtid_consistency, @@master_info_repository, @@log_bin,
       @@gtid_mode, @@transaction_write_set_extraction, @@GLOBAL.transaction_isolation,
       @@transaction_isolation, @@server_id, @@server_uuid, @@read_only, @@super_read_only;

-- InnoDB and Memory Engine tables only
SELECT table_schema, table_name, engine
  FROM information_schema.tables
 WHERE engine NOT IN ('InnoDB', 'MEMORY')
   AND table_schema NOT IN ('mysql', 'performance_schema', 'information_schema');

-- Primary on unique not null tables only
SELECT tables.table_schema, tables.table_name, tables.engine 
  FROM information_schema.tables 
  LEFT JOIN ( 
       SELECT table_schema , table_name 
         FROM information_schema.statistics 
        GROUP BY table_schema, table_name, index_name
        HAVING SUM( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks 
    ON tables.table_schema = puks.table_schema and tables.table_name = puks.table_name 
 WHERE puks.table_name is null 
   AND tables.table_type = 'BASE TABLE' AND Engine="InnoDB";

-- Savepoints
-- SELECT event_name, count_star, sum_errors 
--   FROM performance_schema.events_statements_summary_global_by_event_name 
--  WHERE event_name  like '%savepoint%'
--    AND count_star>0;

-- Cascading constraints (MP mode)
SELECT CONCAT(t1.table_name, '.', column_name) AS 'foreign key',     
       CONCAT(t1.referenced_table_name, '.', referenced_column_name) AS 'references',     
       t1.constraint_name AS 'constraint name', UPDATE_RULE, DELETE_RULE 
  FROM information_schema.key_column_usage as t1 
  JOIN information_schema.REFERENTIAL_CONSTRAINTS as t2 
 WHERE t2.CONSTRAINT_NAME = t1.constraint_name 
   AND t1.referenced_table_name IS NOT NULL 
   AND (DELETE_RULE = "CASCADE" OR UPDATE_RULE = "CASCADE");

-- Concurrent DDL (MP mode)
SELECT event_name, count_star, sum_errors 
  FROM sys.events_statements_summary_global_by_event_name 
 WHERE event_name  REGEXP '.*sql/(create|drop|alter).*' 
   AND event_name NOT REGEXP '.*user';


-- InnoDB cluster status
SELECT GROUP_NAME
  FROM performance_schema.replication_connection_status
 WHERE CHANNEL_NAME = 'group_replication_applier';

select member_host as "primary master" 
  from performance_schema.global_status s, performance_schema.replication_group_members m
 where s.variable_name='group_replication_primary_member'
   and member_id=variable_value;

SELECT MEMBER_STATE
  FROM performance_schema.replication_group_members as m
  JOIN performance_schema.replication_group_member_stats as s
    ON m.MEMBER_ID = s.MEMBER_ID;

SELECT *
  FROM performance_schema.replication_group_member_stats;