MySQL command used for debugging the issue - Quick Ref

gistfile1.txt

1. Show data lock held or requested (https://dev.mysql.com/doc/refman/8.0/en/performance-schema-data-locks-table.html)
    
    mysql> SELECT * FROM performance_schema.data_locks

2. General information about tables
    
    mysql> SHOW TABLE STATUS

3. Show table information

   mysql> SELECT *
          FROM information_schema.TABLES
          WHERE TABLE_SCHEMA = '<DATABASE NAME>'

4. Get create table statement to view indexes and constraint in the table
    
    mysql> SHOW CREATE TABLE <TABLE_NAME>

5. View all the constraints
   
   mysql> SELECT 
            TABLE_NAME,
            COLUMN_NAME,
            CONSTRAINT_NAME,
            REFERENCED_TABLE_NAME,
            REFERENCED_COLUMN_NAME
          FROM information_schema.KEY_COLUMN_USAGE
          WHERE TABLE_SCHEMA = '<DATABASE_NAME>'
  
 5. View constraints of specific table
   
   mysql> SELECT 
            TABLE_NAME,
            COLUMN_NAME,
            CONSTRAINT_NAME,
            REFERENCED_TABLE_NAME,
            REFERENCED_COLUMN_NAME
          FROM information_schema.KEY_COLUMN_USAGE
          WHERE TABLE_SCHEMA = '<DATABASE_NAME>'
          AND TABLE_NAME='<TABLE_NAME>'
  
  6. Show process list
      
      mysql> SHOW FULL PROCESSLIST
      
      # To view only for particular db
      mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
             WHERE db = '<DATABASE_NAME>' 
             ORDER BY time DESC
             
             
       KILL [CONNECTION | QUERY] processlist_id
       
       KILL permits an optional CONNECTION or QUERY modifier:

            KILL CONNECTION is the same as KILL with no modifier: It terminates the connection associated with the given        processlist_id, after terminating any statement the connection is executing.
            KILL QUERY terminates the statement the connection is currently executing, but leaves the connection itself intact.
      
      # with above you could discover the long running queries and using the command below you could kill those problematic threads
      mysql> KILL <THREAD_ID>
      
      
      mysql> SELECT GROUP_CONCAT(CONCAT('KILL ',id,';') SEPARATOR ' ') 'Paste the following query to kill all processes' FROM information_schema.processlist WHERE user<>'system user'\G