Kill expensive and duplicated queries of a MySQL process

killDuplicatedQueries.py

import sys
import getopt
import mysql.connector
from mysql.connector import Error
from datetime import datetime

silent = False

class process:
    def __init__(self, id, sql):
        self.id = id
        self.sql = sql
        self.count = 1
        self.ids = [id]

    def count_one(self, process_id):
        self.ids.append(process_id)
        self.count += 1

    def get_id(self):
        return self.id

    def get_sql(self):
        return self.sql

    def get_count(self):
        return self.count

    def get_ids(self):
        return self.ids

def help(argv):
    db_host = '127.0.0.1'
    db_port = 3306
    db_user = 'root'
    db_password = ''
    silent = False
    kill = False
    arg_help = '{0} --host=<host> --port=<port> --user=<user> --password=<password>'.format(argv[0])

    try:
        opts, args = getopt.getopt(argv[1:], 'hi:u:o', ['help', 'host=', 'port=', 'user=', 'password=', 'silent', 'kill'])
    except:
        print('Execution failed (i dont know why Dave)')
        print(arg_help)
        sys.exit(2)
    
    for opt, arg in opts:
        if opt in ('-h', '--help'):
            print(arg_help)
            sys.exit(0)
        elif opt in ('-H', '--host'):
            db_host = arg
        elif opt in ('-P', '--port'):
            db_port = arg
        elif opt in ('-u', '--user'):
            db_user = arg
        elif opt in ('-p', '--password'):
            db_password = arg
        elif opt in ('-s', '--silent'):
            silent = True
        elif opt in ('-k', '--kill'):
            kill = True

    return { 'host': db_host, 'port': db_port, 'user': db_user, 'password': db_password, 'silent': silent, 'kill': kill }

def is_query_already_in_processes(process_id, query_to_check, processes):
    for process in processes:
        if query_to_check == process.get_sql() and process_id != process.get_id():
            return True

    return False

def count_query_in_processes(query_to_check, process_id, processes):
    for process in processes:
        if query_to_check == process.get_sql():
            process.count_one(process_id)

def is_processes_duplicated(processes):
    for process in processes:
        if process.get_count() > 1:
            return True

    return False

def kill_process_by_ids(connection, ids):
    cursor = connection.cursor()

    print('\nStatus:')
    for id in ids:
        try: 
            cursor.execute(
                """
                    KILL %d
                """ % (int(id)))
            print_text('\n- Catched pokecess {}'.format(id))
        except Error as err:
            print_text('\n- Not caught pokecess {} because: {}'.format(id, err))

    cursor.close()
    print('\n')

def print_text(text):
    print(text)

    if silent:
        return

    save_log(text)

def save_log(text):
    with open('QueriesKilled.log', 'a+') as file:
        file.write(text)

if __name__ == "__main__":
    processes = []
    db_data = help(sys.argv)
    silent = db_data['silent']
    kill = db_data['kill']

    try:
        connection_config = {
            'host': db_data['host'],
            'port': db_data['port'],
            'user': db_data['user'],
            'password': db_data['password'],
        }

        connection = mysql.connector.connect(**connection_config)

        if connection.is_connected():
            db_info = connection.get_server_info()

            if silent == False:
                save_log('Executed in {}'.format(datetime.now()))

            print_text('\nConnected to MySQL Server version {}'.format(db_info))

            cursor = connection.cursor()
            cursor.execute(
                """
                    SELECT 
                        id, 
                        info 
                    FROM information_schema.processlist 
                        WHERE time > 10 
                        AND info IS NOT NULL
                """)
            queries_return = cursor.fetchall()

            for query_return in queries_return:
                id = query_return[0]
                query = query_return[1]

                if is_query_already_in_processes(id, query, processes):
                    count_query_in_processes(query, id, processes)
                    continue

                processes.append( process(id, query) )

            if len(processes) == 0 or is_processes_duplicated(processes) == False:
                print_text('\nTrainer, there`s no duplicated queries')
                sys.exit(0)

            list_count = 0
            print('\nPokecess duplicated:')
            for process in processes:
                if process.get_count() <= 1:
                    continue

                print("{} - a wild process ID {} appears duplicated {} times - {}".format(list_count, 
                    process.get_id(), process.get_count(), process.get_sql()[:100]))
                list_count += 1

            if kill:
                for process_to_kill in processes:
                    print_text('\nCatching query: {}\nPokecess: {}'.format(process_to_kill.get_sql(), process_to_kill.get_ids()))
                    kill_process_by_ids(connection, process_to_kill.get_ids())
                sys.exit(2)

            input_process_count_id = False
            if list_count > 1:
                input_process_count_id = input("\nSelect pokecess [0-{}][a=for all]: ".format(list_count))
            else:
                kill_all = input('\nWant to catch the only duplicated pokecess? [y/n] ')

            if input_process_count_id != False:

                if input_process_count_id != 'a':
                    try:
                        process_count_id = int(input_process_count_id)
                    except ValueError:
                        print('\nSorry, select a real number in range [0-{}] or [a=for all]'.format(list_count))
                        sys.exit(2)
                else:
                    process_count_id = 'a'

                if process_count_id == 'a':
                    kill_all = input('\nWant to catch all duplicated pokecess? [y/n] ')
                elif process_count_id > list_count or process_count_id < 0:
                    print('\nSorry, select only the process in range [0-{}] or [a=for all]'.format(list_count))
                    sys.exit(2)
                else:
                    kill_process = input('Want to catch the pokecess [{}]? [y/n] '.format(process_count_id))
                    print_text('\nCatching query: {}\nPokecess: {}'.format(processes[process_count_id].get_sql(), processes[process_count_id].get_ids()))
                    kill_process_by_ids(connection, processes[process_count_id].get_ids())
                    sys.exit(0)

            if kill_all == 'n':
                print('\nFine, not a good time to catch `em all')
                sys.exit(2)
            elif kill_all == 'y':
                for process_to_kill in processes:
                    print_text('\nCatching query: {}\nPokecess: {}'.format(process_to_kill.get_sql(), process_to_kill.get_ids()))
                    kill_process_by_ids(connection, process_to_kill.get_ids())
            else:
                print('\nSorry, confirm cordially [y/n]'.format(list_count))
                sys.exit(2)

    except Error as err:
        print_text('\nError while connecting to MySQL {}'.format(err))
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print_text('\nMySQL connection is closed\n')

            if silent == False:
                save_log('\n----------------------------------\n\n')

QueriesKilled.log

Executed in 2022-05-03 10:33:21.410481
Connected to MySQL Server version 5.5.5-10.1.30-MariaDB-1~jessie
Catching query: select benchmark(10000000000, md5('when will it end 3?'))
Pokecess: [254, 251, 248, 244, 242, 240, 237, 234, 230]
- Catched pokecess 254
- Catched pokecess 251
- Catched pokecess 248
- Catched pokecess 244
- Catched pokecess 242
- Catched pokecess 240
- Catched pokecess 237
- Catched pokecess 234
- Catched pokecess 230
MySQL connection is closed

----------------------------------

Executed in 2022-05-03 10:33:49.517605
Connected to MySQL Server version 5.5.5-10.1.30-MariaDB-1~jessie
Catching query: select benchmark(100000000, md5('when will it end 2?'))
Pokecess: [255, 252, 247, 245, 243, 238, 236, 232, 231]
- Catched pokecess 255
- Catched pokecess 252
- Catched pokecess 247
- Catched pokecess 245
- Catched pokecess 243
- Catched pokecess 238
- Catched pokecess 236
- Catched pokecess 232
- Catched pokecess 231
Catching query: select benchmark(1000000000, md5('when will it end?'))
Pokecess: [253, 250, 249, 246, 241, 239, 235, 233, 229]
- Catched pokecess 253
- Catched pokecess 250
- Catched pokecess 249
- Catched pokecess 246
- Catched pokecess 241
- Catched pokecess 239
- Catched pokecess 235
- Catched pokecess 233
- Catched pokecess 229
MySQL connection is closed

----------------------------------

Executed in 2022-05-03 10:34:10.090338
Connected to MySQL Server version 5.5.5-10.1.30-MariaDB-1~jessie
Trainer, there`s no duplicated queries
MySQL connection is closed

----------------------------------

Executed in 2022-05-03 10:34:41.886794
Connected to MySQL Server version 5.5.5-10.1.30-MariaDB-1~jessie
Trainer, there`s no duplicated queries
MySQL connection is closed

----------------------------------

Executed in 2022-05-03 10:34:47.799772
Connected to MySQL Server version 5.5.5-10.1.30-MariaDB-1~jessie
Trainer, there`s no duplicated queries
MySQL connection is closed

----------------------------------

Executed in 2022-05-03 10:34:49.562957
Connected to MySQL Server version 5.5.5-10.1.30-MariaDB-1~jessie
Catching query: select benchmark(1000000000, md5('when will it end?'))
Pokecess: [263, 259]
- Catched pokecess 263
- Catched pokecess 259
MySQL connection is closed

----------------------------------

Executed in 2022-05-03 10:35:14.355327
Connected to MySQL Server version 5.5.5-10.1.30-MariaDB-1~jessie
Catching query: select benchmark(10000000000, md5('when will it end 3?'))
Pokecess: [262, 260]
- Catched pokecess 262
- Catched pokecess 260
MySQL connection is closed

----------------------------------

Executed in 2022-05-03 10:35:22.778984
Connected to MySQL Server version 5.5.5-10.1.30-MariaDB-1~jessie
Catching query: select benchmark(100000000, md5('when will it end 2?'))
Pokecess: [264, 261]
- Catched pokecess 264
- Not caught pokecess 261 because: 1094 (HY000): Unknown thread id: 261
MySQL connection is closed

----------------------------------