handling mysql server lost connection
1 files changed, 49 insertions(+), 15 deletions(-)

M syslogd.py
M syslogd.py +49 -15
@@ 27,18 27,41 @@ import mysql.connector
 from datetime import datetime
 from gevent.server import DatagramServer
 from priority import SyslogMatrix
+from mysqlx.helpers import escape as pure_python_escape
 
 
-syslog_matrix =  SyslogMatrix()
-db = mysql.connector.connect(
-    user=MYSQL_USER,
-    password=MYSQL_PASSWORD,
-    host=MYSQL_SERVER,
-    database=MYSQL_DB
-)
-cursor = db.cursor()
+class SyslogUDPHandler(DatagramServer):
+
+    syslog_matrix =  SyslogMatrix()
+
+    def __init__(self, *args, **kwargs):
+        super().__init__(*args, **kwargs)
+        self.setup()
+        if DEBUG:
+            print(f'Listening on UDP {args[0]}')
+
+    def __del__(self):
+        self.db.close()
 
-class SyslogUDPHandler(DatagramServer):
+    def setup(self):
+        self.db = mysql.connector.connect(
+            user=MYSQL_USER,
+            password=MYSQL_PASSWORD,
+            host=MYSQL_SERVER,
+            database=MYSQL_DB
+        )
+        self.cursor = self.db.cursor()
+
+    def escape(self, msg):
+        try:
+            return self.db._cmysql.escape_string(msg).decode()
+        except AttributeError:
+            return  pure_python_escape(msg)
+        except Exception as e:
+            if DEBUG:
+                print('Cannot escape string')
+            raise(e)
+
     def handle(self, data, address):
         ReceivedAt = datetime.now()  # Generated
         data = data.decode()

          
@@ 73,7 96,7 @@ class SyslogUDPHandler(DatagramServer):
                 ProcessID = '0'
             Message = data[data.find(': ')+2:]
             code = data[data.find('<')+1:data.find('>')]
-            Facility, Priority = syslog_matrix.decode_int(code)
+            Facility, Priority = self.syslog_matrix.decode_int(code)
             FromHost = address[0]
             InfoUnitID = 1  # Hardcoded
             sql_command = SQL % (

          
@@ 81,19 104,31 @@ class SyslogUDPHandler(DatagramServer):
                 ReceivedAt.strftime('%Y-%m-%d %H:%M:%S'),
                 DeviceReportedTime.strftime('%Y-%m-%d %H:%M:%S'),
                 SysLogTag, ProcessID,
-                db._cmysql.escape_string(Message).decode()
+                self.escape(Message)
             )
             try:
-                cursor.execute(sql_command)
+                self.cursor.execute(sql_command)
             except Exception as e:
                 if DEBUG:
                     print(sql_command)
                     print(e)
-                db.rollback()
+                try:
+                    self.db.rollback()
+                except Exception as ee:
+                    if DEBUG:
+                        print('Fatal Exception! Restarting Process')
+                        print(ee)
+                    self.setup()
             else:
                 if DEBUG:
                     print(data)
-                db.commit()
+                try:
+                    self.db.commit()
+                except Exception as ee:
+                    if DEBUG:
+                        print('Fatal Exception! Restarting Process')
+                        print(ee)
+                    self.setup()
 
 
 if __name__ == "__main__":

          
@@ 103,5 138,4 @@ if __name__ == "__main__":
         raise
     except KeyboardInterrupt:
         print ("Crtl+C Pressed. Shutting down.")
-    db.close()