0

My script has been running continuously for about a week. Received this error and the script aborted:

Traceback (most recent call last):  
  File  "/opt/dba/home/swechsler/library/mysql/bin/./kill_duplicate_processes", line 208, in <module>  
    main()  
  File "/opt/dba/home/swechsler/library/mysql/bin/./kill_duplicate_processes", line 194, in main  
    cnx.commit()  
  File "/usr/local/lib/python3.9/site-packages/mysql/connector/connection_cext.py", line 487, in commit  
    self._cmysql.commit()  
_mysql_connector.MySQLInterfaceError: Lost connection to MySQL server during query

I'm trying to figure out why the script aborted when it was inside a try block. Here's the relevant code:

            try:
                cursor = cnx.cursor()
                cursor.execute(f"SET SESSION MAX_EXECUTION_TIME={query_timeout}")
                query = (f"SELECT * FROM information_schema.processlist WHERE command in('Query','Execute') AND STATE IS NOT NULL AND info LIKE 'SELECT%' AND TIME > {kill_time} and user not in('debezium','system user','snaplogic')")
#                print(f"running {query} on {server}")
                cursor.execute(query)
                processes = cursor.fetchall()
                cursor.close()

                # Group processes by INFO column
                grouped_processes = {}
                for process in processes:
                    pid = process[0]
                    info = process[7]
                    if info not in grouped_processes:
                        grouped_processes[info] = []
                    grouped_processes[info].append(pid)

                # Kill all but the most recent process for each group
                oldinfo = ''
                for info, pids in grouped_processes.items():
                    process_killed = False;
                    pids.sort(reverse=True)
                    num_processes = len(pids)
                    for pid in pids[1:]:
                        kill_query = f"CALL mysql.rds_kill({pid})"
                        cursor = cnx.cursor()
                        cursor.execute(kill_query)
                        cursor.close()
                        print(f"[{get_current_datetime()}]: Killed long running process on {server}: {pid}, info: {info}")
                        process_killed = True;

                    if process_killed:
                        message = f"Killed {num_processes - 1} duplicate long running processes on {server} {' '.join(map(str, pids[1:]))}: ```{info}```" if num_processes > 2 else f"killed duplicate long running process on {server} {pids[1:]}: ```{info\
}```"
                        send_msg(message, args.slack)
                cnx.commit()

            except mysql.connector.Error as err:
                if err.errno == mysql.connector.errorcode.CR_COMMANDS_OUT_OF_SYNC:
                    # MySQL Connector/Python may raise this exception when a query times out
                    print(f"[{current_time}]: Query on {server} timed out. Disconnecting...")
                else:
                    print(f"[{get_current_datetime()}]: Error communicating with {server}: {err}")
                cnx.close()
                connections[server] = None
Matthias
  • 12,873
  • 6
  • 42
  • 48
Swechsler
  • 101
  • 3
  • 3
    I wonder if `_mysql_connector.MySQLInterfaceError` really is a descendant of `mysql.connector.Error`. It should be, but then I don't understand why the exception wasn't caught. – Matthias Jul 20 '23 at 21:01
  • Is it possible that there is a try/except block within the MySQL module, and if so, would that interfere with my outer block? – Swechsler Jul 20 '23 at 21:17
  • 2
    It seems reasonable that the error you received is not a subclass of `MySQLInterfaceError`. Can you find out its inheritance tree for sure? – Mad Physicist Jul 20 '23 at 21:43
  • I'm not sure how to do that. I'm still a Python beginner; I looked at the module code but it's still not clear to me. But if it wasn't, why would it say `_mysql_connector.MySQLInterfaceError` in the message? – Swechsler Jul 20 '23 at 21:56
  • The above comment had a thinko -- it should have said "not a subclass of `mysql.connector.Error`". – Charles Duffy Jul 20 '23 at 21:56
  • Code for the module that aborted (albeit a more recent version) is [here](https://github.com/mysql/mysql-connector-python/blob/trunk/lib/mysql/connector/connection_cext.py) – Swechsler Jul 20 '23 at 22:00
  • Anyhow -- personally, the way I'd inspect this is to run `python -i yourscript`, and then when it fails with the exception use `import pdb; pdb.pm()` to pull up a debugger so you can nose around the code where the failure took place and inspect the exception object yourself. – Charles Duffy Jul 20 '23 at 22:01
  • Reading the code you linked, any exception will go through https://github.com/mysql/mysql-connector-python/blob/trunk/lib/mysql/connector/errors.py#L216, which is very configurable as to which type of exception object any given error code will be translated into. Look at the end of the file for the default table, but anything you load can update that table. – Charles Duffy Jul 20 '23 at 22:03
  • The trouble is, this is the first time this has happened in a week of the script running, and I don't know what caused the error (when a connection fails it usually happens earlier in the code). And I'd prefer to figure out the cause ahead of time, rather than waiting for it to fail again, since I want this to keep running. – Swechsler Jul 20 '23 at 22:04
  • Once you're at a REPL, anyhow, you can check inheritance trees with `isinstance()` and similar calls, so you can answer the questions that have been asked in this thread. – Charles Duffy Jul 20 '23 at 22:41

1 Answers1

-3

besides of except mysql.connector.Error as err:, you can also catch all errors except Exception as e: just to be thorough.

     except (mysql.connector.Error, Exception) as err:
Ben L
  • 171
  • 1
  • 9
  • Hmm, that's a good point; however, the error does appear to be coming from the mysql connector, so I don't think it explains why try didn't catch this. – Swechsler Jul 20 '23 at 21:41
  • @Swechsler can you confirm _mysql_connector.MySQLInterfaceError really is a descendant of mysql.connector.Error ? I don't have the source code of them at hand, so I can't check that. – Ben L Jul 20 '23 at 21:46
  • There is *no* reason to phrase it as `(mysql.connector.Error, Exception)`; `Exception` is a superclass of `mysql.connector.Error`, so you can just catch `Exception`. That said, this is a terrible solution; you're saying "Because the error we know how to handle wasn't caught properly, eh, just catch (almost) all errors and handle them as if they're the error we expect." So you'll catch `KeyError`s, `IndexError`s, `MemoryError`s, etc., and act like they're SQL-related errors you know how to handle (and note, you can't check the `errno` for any of those, because they don't have that attribute). – ShadowRanger Jul 20 '23 at 21:48
  • 1
    Boo, hiss; you want to cover all the exceptions you (as a human author) expect and know how to correctly handle, but _all of Exception_ is the opposite of good practice. Much better to just add the specific error that isn't being caught to the tuple. – Charles Duffy Jul 20 '23 at 21:50
  • The idea is to handle any unexpected error. You can always handle known exception elegantly, but in real life programming, you have to expect the unexpected error. – Ben L Jul 20 '23 at 21:52
  • 1
    The right way to handle an unexpected error is generally to exit! If you don't know the error, you don't know that it's safe to keep running without corrupting data. – Charles Duffy Jul 20 '23 at 21:53
  • 1
    And look at the OP's code: it expects exceptions to have an `errno` attribute. Obviously that's not true for every `Exception`. – Charles Duffy Jul 20 '23 at 21:53
  • 1
    In general, the right thing is to let exceptions bubble up to a layer that knows how to handle them; if no such layer exists, that's why responsible system administration has services like systemd that monitor for when things unexpectedly exit and restart them. – Charles Duffy Jul 20 '23 at 21:55
  • @ShadowRanger , phrasing it as (mysql.connector.Error, Exception) is to remind that the mysql.connector.Error is a known error, and also catch all unexpected error, just to be sure. It is more for documentation. – Ben L Jul 20 '23 at 22:07