Groovy DBA

  To check multiple variables at once: Sql Copy code WHERE gv.VARIABLE_NAME IN ( 'sql_mode' , 'max_connections' , 'time_zone' ); To search by pattern: Sql Copy code WHERE gv.VARIABLE_NAME LIKE 'innodb%' ;

  here’s a single MySQL query that shows a system parameter’s GLOBAL and SESSION values side-by-side. You just replace 'sql_mode' with the variable you want to check. Sql Copy code SELECT gv.VARIABLE_NAME, gv.VARIABLE_VALUE AS GLOBAL_VALUE, sv.VARIABLE_VALUE AS SESSION_VALUE FROM performance_schema.global_variables gv JOIN performance_schema.session_variables sv ON gv.VARIABLE_NAME = sv.VARIABLE_NAME WHERE gv.VARIABLE_NAME = 'sql_mode' ;

  In MySQL, you can view system parameters (also called system variables ) using the SHOW VARIABLES statement or by querying the performance_schema tables. Here are the main ways: 1. Show all system parameters Sql Copy code SHOW VARIABLES; This lists every system variable and its current value for your session. 2. Show specific parameters by name pattern Sql Copy code SHOW VARIABLES LIKE 'max_connections' ; SHOW VARIABLES LIKE 'innodb%' ; % is a wildcard for matching multiple variables. 3. Show global vs session values Sql Copy code -- Global values (affect all connections) SHOW GLOBAL VARIABLES; -- Session values (specific to your current connection) SHOW SESSION VARIABLES; 4. Using performance_schema Sql Copy code SELECT * FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE 'max_connections' ; This is useful for more complex filtering or joining with other metadata. ✅ Tip: If you want to see both the current session ...

 SELECT      r.trx_id AS waiting_trx_id,     r.trx_mysql_thread_id AS waiting_thread,     r.trx_query AS waiting_query,     b.trx_id AS blocking_trx_id,     b.trx_mysql_thread_id AS blocking_thread,     b.trx_query AS blocking_query FROM performance_schema.data_lock_waits w JOIN information_schema.innodb_trx b      ON w.blocking_engine_transaction_id = b.trx_id JOIN information_schema.innodb_trx r      ON w.requesting_engine_transaction_id = r.trx_id;                    SELECT      r.trx_id AS waiting_trx_id,     r.trx_mysql_thread_id AS waiting_thread,     r.trx_query AS waiting_query,     b.trx_id AS blocking_trx_id,     b.trx_mysql_thread_id AS blocking_thread,     b.trx_query AS blocking_query,     b.trx_started as blocking_start_time FROM performance_schem...

       

 SELECT     r.rolname AS role_name,     p.proname AS procedure_name,     n.nspname AS schema_name,     pg_get_function_identity_arguments(p.oid) AS arguments FROM     pg_proc p JOIN     pg_namespace n ON n.oid = p.pronamespace JOIN     pg_roles r ON has_function_privilege(r.rolname, p.oid, 'EXECUTE') ORDER BY     procedure_name, role_name;