[PATCH RESEND v3 0/2] Automatic recovery from a MySQL restart

[PATCH RESEND v3 0/2] Automatic recovery from a MySQL restart

[Ammar Faizi]

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

v2 -> v3:

  - Simplify try and except statement (comment from Muhammad Rizki).

v1 -> v2:

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

Signed-off-by: Ammar Faizi <ammarfaizi2@gnuweeb.org>
---

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 | 29 +++++++++++++++++++++++++----
 2 files changed, 30 insertions(+), 4 deletions(-)


base-commit: 6e94cf607287e5bc209e9c14c8156c7ad49455e3
-- 
Ammar Faizi

[PATCH RESEND v3 1/2] daemon: telegram: db: Allow the caller to reconnect

[Ammar Faizi]

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:
  - Use conn.ping(reconnect=True) instead of creating a new MySQL
    connection object (comment from Muhammad Rizki).

Signed-off-by: Ammar Faizi <ammarfaizi2@gnuweeb.org>
---
 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

[PATCH RESEND v3 2/2] daemon: telegram: Handle MySQL error

[Ammar Faizi]

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.

v3:
  - Simplify try and except statement (comment from Muhammad Rizki).

v2:
  * no changes *

Signed-off-by: Ammar Faizi <ammarfaizi2@gnuweeb.org>
---
 daemon/telegram/mailer/listener.py | 29 +++++++++++++++++++++++++----
 1 file changed, 25 insertions(+), 4 deletions(-)

diff --git a/daemon/telegram/mailer/listener.py b/daemon/telegram/mailer/listener.py
index 1c92f23..46ccf93 100644
--- a/daemon/telegram/mailer/listener.py
+++ b/daemon/telegram/mailer/listener.py
@@ -5,6 +5,7 @@
 #
 
 from pyrogram.types import Message
+from mysql.connector.errors import OperationalError, DatabaseError
 from apscheduler.schedulers.asyncio import AsyncIOScheduler
 from telegram.packages import DaemonClient
 from atom import Scraper
@@ -43,13 +44,33 @@ 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 (OperationalError, DatabaseError) as e:
+			await self.handle_db_error(e)
+		except:
+			print(traceback.format_exc())
 
 		if not self.isRunnerFixed:
 			self.isRunnerFixed = True
-- 
Ammar Faizi

Re: [PATCH RESEND v3 1/2] daemon: telegram: db: Allow the caller to reconnect

[Alviro Iskandar Setiawan]

On Mon, Oct 31, 2022 at 11:13 PM Ammar Faizi wrote:
> 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:
>   - Use conn.ping(reconnect=True) instead of creating a new MySQL
>     connection object (comment from Muhammad Rizki).
>
> Signed-off-by: Ammar Faizi <ammarfaizi2@gnuweeb.org>

Reviewed-by: Alviro Iskandar Setiawan <alviro.iskandar@gnuweeb.org>

Re: [PATCH RESEND v3 2/2] daemon: telegram: Handle MySQL error

[Alviro Iskandar Setiawan]

On Mon, Oct 31, 2022 at 11:13 PM Ammar Faizi wrote:
> 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.
>
> v3:
>   - Simplify try and except statement (comment from Muhammad Rizki).
>
> v2:
>   * no changes *
>
> Signed-off-by: Ammar Faizi <ammarfaizi2@gnuweeb.org>

Reviewed-by: Alviro Iskandar Setiawan <alviro.iskandar@gnuweeb.org>

Re: [PATCH RESEND v3 1/2] daemon: telegram: db: Allow the caller to reconnect

[Muhammad Rizki]

On 31/10/22 23.12, Ammar Faizi wrote:
> 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:
>    - Use conn.ping(reconnect=True) instead of creating a new MySQL
>      connection object (comment from Muhammad Rizki).
> 
> Signed-off-by: Ammar Faizi <ammarfaizi2@gnuweeb.org>
> ---
>   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()

Okay, this looks fine to me.

Re: [PATCH RESEND v3 2/2] daemon: telegram: Handle MySQL error

[Muhammad Rizki]

On 31/10/22 23.12, Ammar Faizi wrote:
> 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.
> 
> v3:
>    - Simplify try and except statement (comment from Muhammad Rizki).
> 
> v2:
>    * no changes *
> 
> Signed-off-by: Ammar Faizi <ammarfaizi2@gnuweeb.org>
> ---
>   daemon/telegram/mailer/listener.py | 29 +++++++++++++++++++++++++----
>   1 file changed, 25 insertions(+), 4 deletions(-)
> 
> diff --git a/daemon/telegram/mailer/listener.py b/daemon/telegram/mailer/listener.py
> index 1c92f23..46ccf93 100644
> --- a/daemon/telegram/mailer/listener.py
> +++ b/daemon/telegram/mailer/listener.py
> @@ -5,6 +5,7 @@
>   #
>   
>   from pyrogram.types import Message
> +from mysql.connector.errors import OperationalError, DatabaseError
>   from apscheduler.schedulers.asyncio import AsyncIOScheduler
>   from telegram.packages import DaemonClient
>   from atom import Scraper
> @@ -43,13 +44,33 @@ 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 (OperationalError, DatabaseError) as e:
> +			await self.handle_db_error(e)
> +		except:
> +			print(traceback.format_exc())
>   
>   		if not self.isRunnerFixed:
>   			self.isRunnerFixed = True

This fine too. Can't wait for you to apply. I will continue the logger 
version until its applied, thanks.

Re: [PATCH RESEND v3 0/2] Automatic recovery from a MySQL restart

[Ammar Faizi]

On Mon, 31 Oct 2022 23:34:15 +0700, Muhammad Rizki wrote:
> This fine too. Can't wait for you to apply. I will continue the logger
> version until its applied, thanks.

Applied, thanks!

[1/2] daemon: telegram: db: Allow the caller to reconnect
      commit: 9b13624c53f234f81954dec19653376ee0959c3d
[2/2] daemon: telegram: Handle MySQL error
      commit: 529d804eb47518d367dfac51907dbf76944b5c0e

Best regards,
-- 
Ammar Faizi