From 90c4debcbc961b342353a6576501ffe096329068 Mon Sep 17 00:00:00 2001
From: stolenvw <stolenvw@hotmail.com>
Date: Mon, 12 Apr 2021 16:54:58 -0400
Subject: Fixed where some commands where not reconnecting the MySQL database
 when connecting to database timedout do to being inactive

---
 code/botcmds/maincmd.py | 11 +----------
 code/plusbot.py         |  1 +
 code/utils/botsql.py    | 33 +++++++++++++++++++++++++++++++--
 3 files changed, 33 insertions(+), 12 deletions(-)

(limited to 'code')

diff --git a/code/botcmds/maincmd.py b/code/botcmds/maincmd.py
index e1e2000..77c05ce 100644
--- a/code/botcmds/maincmd.py
+++ b/code/botcmds/maincmd.py
@@ -14,13 +14,6 @@ class Main(commands.Cog):
 
     def __init__(self, bot):
         self.bot = bot
-        self.mydb = mysql.connector.connect(
-            host=config.SQL_HOST,
-            user=config.SQL_USER,
-            password=config.SQL_PASS,
-            database=config.SQL_DATABASE,
-            port=config.SQL_PORT,
-            )
 
     @commands.command(name="deaths",
                       brief="Deaths leaderboard",
@@ -83,10 +76,8 @@ class Main(commands.Cog):
 
         #Get data from mysql
         botsql = self.bot.get_cog('BotSQL')
-        mycursor = await botsql.get_cursor()
-        mycursor.close()
         sqls = """SELECT date, users FROM serverstats WHERE timestamp BETWEEN '%s' AND '%s'""" % (tlookup, int(time.time()))
-        df = pd.read_sql(sqls, self.mydb, parse_dates=['date'])
+        df = pd.read_sql(sqls, await botsql.get_mydb(), parse_dates=['date'])
         lastday = datetime.now() - timedelta(hours = user_range)
 
         # Plot formatting / styling matplotlib
diff --git a/code/plusbot.py b/code/plusbot.py
index e94da6e..4cf06f2 100644
--- a/code/plusbot.py
+++ b/code/plusbot.py
@@ -133,6 +133,7 @@ async def on_ready():
     bot.loop.create_task(serveronline())
     botsql = bot.get_cog('BotSQL')
     await botsql.mydbconnect()
+    await bot.change_presence(activity=discord.Activity(type=discord.ActivityType.watching, name="Stolenvw ValPlusBot"))
 
 # Main loop for reading log file and outputing events
 async def mainloop(file):
diff --git a/code/utils/botsql.py b/code/utils/botsql.py
index 5df39c6..d39156e 100644
--- a/code/utils/botsql.py
+++ b/code/utils/botsql.py
@@ -41,11 +41,19 @@ class BotSQL(commands.Cog):
     async def get_cursor(self):
         try:
             mydb.ping(reconnect=True, attempts=3, delay=5)
+        except NameError as er:
+               print(Fore.RED, er, "Reconnecting MySQL database" + Style.RESET_ALL)
+               if config.USEDEBUGCHAN == True:
+                  bugchan = self.bot.get_channel(config.BUGCHANNEL_ID)
+                  bugerror = discord.Embed(title=":sos: **ERROR** :sos:", description= "{} Reconnection MySQL database".format(er), color=0xFF001E)
+                  bugerror.set_author(name=config.SERVER_NAME)
+                  await bugchan.send(embed=bugerror)
+               await self.mydbconnect()
         except mysql.connector.Error as err:
-            await mydbconnect()
+            await self.mydbconnect()
             print(Fore.RED + "Connection to MySQL database went away... Reconnecting " + Style.RESET_ALL)
             if config.USEDEBUGCHAN == True:
-                bugchan = bot.get_channel(dbchanID)
+                bugchan = self.bot.get_channel(config.BUGCHANNEL_ID)
                 bugerror = discord.Embed(title=":sos: **ERROR** :sos:", description="Connection to MySQL database went away... Reconnecting", color=0xFF001E)
                 bugerror.set_author(name=config.SERVER_NAME)
                 await bugchan.send(embed=bugerror)
@@ -54,5 +62,26 @@ class BotSQL(commands.Cog):
     async def botmydb(self):
         mydb.commit()
 
+    async def get_mydb(self):
+        try:
+            mydb.ping(reconnect=True, attempts=3, delay=5)
+        except NameError as er:
+               print(Fore.RED, er, "Reconnecting MySQL database" + Style.RESET_ALL)
+               if config.USEDEBUGCHAN == True:
+                  bugchan = self.bot.get_channel(config.BUGCHANNEL_ID)
+                  bugerror = discord.Embed(title=":sos: **ERROR** :sos:", description= "{} Reconnection MySQL database".format(er), color=0xFF001E)
+                  bugerror.set_author(name=config.SERVER_NAME)
+                  await bugchan.send(embed=bugerror)
+               await self.mydbconnect()
+        except mysql.connector.Error as err:
+            await self.mydbconnect()
+            print(Fore.RED + "Connection to MySQL database went away... Reconnecting " + Style.RESET_ALL)
+            if config.USEDEBUGCHAN == True:
+                bugchan = self.bot.get_channel(config.BUGCHANNEL_ID)
+                bugerror = discord.Embed(title=":sos: **ERROR** :sos:", description="Connection to MySQL database went away... Reconnecting", color=0xFF001E)
+                bugerror.set_author(name=config.SERVER_NAME)
+                await bugchan.send(embed=bugerror)
+        return mydb
+
 def setup(bot):
     bot.add_cog(BotSQL(bot))
-- 
cgit v1.2.3-70-g09d2