public inbox for [email protected]
 help / color / mirror / Atom feed
* [PATCH v2 0/2] Automatic recovery from a MySQL restart
@ 2022-10-30  5:49 Ammar Faizi
  2022-10-30  5:49 ` [PATCH v2 1/2] daemon: telegram: db: Allow the caller to reconnect Ammar Faizi
  2022-10-30  5:49 ` [PATCH v2 2/2] daemon: telegram: Handle MySQL error Ammar Faizi
  0 siblings, 2 replies; 5+ messages in thread
From: Ammar Faizi @ 2022-10-30  5:49 UTC (permalink / raw)
  To: GNU/Weeb Mailing List
  Cc: Ammar Faizi, Muhammad Rizki, Alviro Iskandar Setiawan

Hi,

This series adds a mechanism for the daemon to recover from a MySQL
restart event. Currently, the daemon is totally unusable when the MySQL
server is restarted. It's spinning in a loop with the following error:

   mysql.connector.errors.OperationalError: 2013 (HY000): Lost connection to MySQL server during query

When it happens, the only way to fix the situation is: restart the
daemon manually, which is obviously not productive. Create a mechanism
in the class DB to allow the caller to reconnect.

Handle MySQL error in the main daemon loop path. Do reconnect to the
database if such an error happens. This way, the daemon can
automatically recover from the MySQL server restart without having the
user restart the daemon.

There are two patches in this series:

  - Patch 1 is a preparation patch to create a recover mechanism.
  - Patch 2 is to implement the recover mechanism in the main daemon
    loop path.

## Changelog

v1 -> v2:

  - Use conn.ping(reconnect=True) instead of creating a new MySQL
    connection object (comment from Muhammad Rizki).

Signed-off-by: Ammar Faizi <[email protected]>
---

Ammar Faizi (2):
  daemon: telegram: db: Allow the caller to reconnect
  daemon: telegram: Handle MySQL error

 daemon/telegram/database/core.py   |  5 +++++
 daemon/telegram/mailer/listener.py | 31 ++++++++++++++++++++++++++----
 2 files changed, 32 insertions(+), 4 deletions(-)


base-commit: 6e94cf607287e5bc209e9c14c8156c7ad49455e3
-- 
Ammar Faizi


^ permalink raw reply	[flat|nested] 5+ messages in thread

* [PATCH v2 1/2] daemon: telegram: db: Allow the caller to reconnect
  2022-10-30  5:49 [PATCH v2 0/2] Automatic recovery from a MySQL restart Ammar Faizi
@ 2022-10-30  5:49 ` Ammar Faizi
  2022-10-30  5:49 ` [PATCH v2 2/2] daemon: telegram: Handle MySQL error Ammar Faizi
  1 sibling, 0 replies; 5+ messages in thread
From: Ammar Faizi @ 2022-10-30  5:49 UTC (permalink / raw)
  To: GNU/Weeb Mailing List
  Cc: Ammar Faizi, Muhammad Rizki, Alviro Iskandar Setiawan

The daemon is totally unusable when the MySQL server is restarted. It's
spinning in a loop with the following error:

   mysql.connector.errors.OperationalError: 2013 (HY000): Lost connection to MySQL server during query

When it happens, the only way to fix the situation is: restart the
daemon manually, which is obviously not productive. Create a mechanism
in the class DB to allow the caller to reconnect. This way, the caller
can automatically reconnect without having the user restart the daemon.

v2 (comment from Muhammad Rizki):
  - Use conn.ping(reconnect=True) instead of creating a new MySQL
    connection object.

Signed-off-by: Ammar Faizi <[email protected]>
---
 daemon/telegram/database/core.py | 5 +++++
 1 file changed, 5 insertions(+)

diff --git a/daemon/telegram/database/core.py b/daemon/telegram/database/core.py
index c34d7a8..26f671b 100644
--- a/daemon/telegram/database/core.py
+++ b/daemon/telegram/database/core.py
@@ -14,6 +14,11 @@ class DB(DBMethods):
 		self.conn.autocommit = True
 		self.cur = self.conn.cursor(buffered=True)
 
+	def ping(self, reconnect=True, attempts=3, delay=3):
+		self.conn.ping(reconnect=reconnect, attempts=attempts,
+				delay=delay)
+		self.cur = self.conn.cursor(buffered=True)
+
 
 	def __del__(self):
 		self.cur.close()
-- 
Ammar Faizi


^ permalink raw reply related	[flat|nested] 5+ messages in thread

* [PATCH v2 2/2] daemon: telegram: Handle MySQL error
  2022-10-30  5:49 [PATCH v2 0/2] Automatic recovery from a MySQL restart Ammar Faizi
  2022-10-30  5:49 ` [PATCH v2 1/2] daemon: telegram: db: Allow the caller to reconnect Ammar Faizi
@ 2022-10-30  5:49 ` Ammar Faizi
  2022-10-30  9:49   ` Muhammad Rizki
  1 sibling, 1 reply; 5+ messages in thread
From: Ammar Faizi @ 2022-10-30  5:49 UTC (permalink / raw)
  To: GNU/Weeb Mailing List
  Cc: Ammar Faizi, Muhammad Rizki, Alviro Iskandar Setiawan

A previous patch creates a mechanism to allow the caller to reconnect
to the database by calling db.connect(). Handle MySQL error in the
main daemon loop path. Do reconnect to the database if such an error
happens. This way, the daemon can automatically recover from the MySQL
server restart without having the user restart the daemon.

Signed-off-by: Ammar Faizi <[email protected]>
---
 daemon/telegram/mailer/listener.py | 31 ++++++++++++++++++++++++++----
 1 file changed, 27 insertions(+), 4 deletions(-)

diff --git a/daemon/telegram/mailer/listener.py b/daemon/telegram/mailer/listener.py
index 1c92f23..4b48e21 100644
--- a/daemon/telegram/mailer/listener.py
+++ b/daemon/telegram/mailer/listener.py
@@ -7,6 +7,7 @@
 from pyrogram.types import Message
 from apscheduler.schedulers.asyncio import AsyncIOScheduler
 from telegram.packages import DaemonClient
+from mysql import connector
 from atom import Scraper
 from atom import utils
 from enums import Platform
@@ -43,13 +44,35 @@ class Bot():
 		)
 
 
+	async def handle_db_error(self, e):
+		#
+		# TODO(ammarfaizi2):
+		# Ideally, we also want to log and report this situation.
+		#
+		print(f"Database error: {str(e)}")
+		print("Reconnecting to the database...")
+
+		#
+		# Don't do this too often to avoid reconnect burst.
+		#
+		delay_in_secs = 3
+		reconnect_attempts = 3
+
+		self.db.ping(reconnect=True, attempts=reconnect_attempts,
+			     delay=delay_in_secs)
+
+
 	async def __run(self):
 		print("[__run]: Running...")
-		for url in self.db.get_atom_urls():
-			try:
+		try:
+			for url in self.db.get_atom_urls():
 				await self.__handle_atom_url(url)
-			except:
-				print(traceback.format_exc())
+		except connector.errors.OperationalError as e:
+			await self.handle_db_error(e)
+		except connector.errors.DatabaseError as e:
+			await self.handle_db_error(e)
+		except:
+			print(traceback.format_exc())
 
 		if not self.isRunnerFixed:
 			self.isRunnerFixed = True
-- 
Ammar Faizi


^ permalink raw reply related	[flat|nested] 5+ messages in thread

* Re: [PATCH v2 2/2] daemon: telegram: Handle MySQL error
  2022-10-30  5:49 ` [PATCH v2 2/2] daemon: telegram: Handle MySQL error Ammar Faizi
@ 2022-10-30  9:49   ` Muhammad Rizki
  2022-10-30 12:45     ` Ammar Faizi
  0 siblings, 1 reply; 5+ messages in thread
From: Muhammad Rizki @ 2022-10-30  9:49 UTC (permalink / raw)
  To: Ammar Faizi, GNU/Weeb Mailing List; +Cc: Alviro Iskandar Setiawan

On 30/10/22 12.49, Ammar Faizi wrote:
> +from mysql.connector.errors import OperationalError, DatabaseError
> +
> +
>   	async def __run(self):
>   		print("[__run]: Running...")
> -		for url in self.db.get_atom_urls():
> -			try:
> +		try:
> +			for url in self.db.get_atom_urls():
>   				await self.__handle_atom_url(url)
> -			except:
> -				print(traceback.format_exc())
> +		except (OperationalError, DatabaseError) as e:
> +			await self.handle_db_error(e)

I prefer to do it like this way. What do you think?

^ permalink raw reply	[flat|nested] 5+ messages in thread

* Re: [PATCH v2 2/2] daemon: telegram: Handle MySQL error
  2022-10-30  9:49   ` Muhammad Rizki
@ 2022-10-30 12:45     ` Ammar Faizi
  0 siblings, 0 replies; 5+ messages in thread
From: Ammar Faizi @ 2022-10-30 12:45 UTC (permalink / raw)
  To: Muhammad Rizki, GNU/Weeb Mailing List; +Cc: Alviro Iskandar Setiawan

On 10/30/22 4:49 PM, Muhammad Rizki wrote:
> On 30/10/22 12.49, Ammar Faizi wrote:

I didn't write that. Don't change the quotation anyway.

>> +from mysql.connector.errors import OperationalError, DatabaseError
>> +
>> +
>>       async def __run(self):
>>           print("[__run]: Running...")
>> -        for url in self.db.get_atom_urls():
>> -            try:
>> +        try:
>> +            for url in self.db.get_atom_urls():
>>                   await self.__handle_atom_url(url)
>> -            except:
>> -                print(traceback.format_exc())
>> +        except (OperationalError, DatabaseError) as e:
>> +            await self.handle_db_error(e)
> 
> I prefer to do it like this way. What do you think?

Looks fine to me, will send a v3 with your changes applied.

Thanks.

-- 
Ammar Faizi


^ permalink raw reply	[flat|nested] 5+ messages in thread

end of thread, other threads:[~2022-10-30 12:45 UTC | newest]

Thread overview: 5+ messages (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
2022-10-30  5:49 [PATCH v2 0/2] Automatic recovery from a MySQL restart Ammar Faizi
2022-10-30  5:49 ` [PATCH v2 1/2] daemon: telegram: db: Allow the caller to reconnect Ammar Faizi
2022-10-30  5:49 ` [PATCH v2 2/2] daemon: telegram: Handle MySQL error Ammar Faizi
2022-10-30  9:49   ` Muhammad Rizki
2022-10-30 12:45     ` Ammar Faizi

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox