1

So I am developing this online telnet-like game and it's not very popular (who knows, one day), so the database connection of my game engine is not used for hours at night. It is one script that waits for events, so it keeps running.

The first time a query is done after several hours of inactivity, I receive the mariadb.DatabaseError when trying to execute the cursor. If I redo the query, it works again. So while the function throws the exception that the connection is lost, it does repair it.

My question: how should I handle this?

These are things I see as possible solutions, but in my opinion, they are not very good:

  • wrapping every query inside a try-except structure, makes the code bulky with mostly unnecessary and repetitive code
  • writing my own 'decorator' function to execute a query, which will then reinitialize the database when I get mariadb.DatabaseError, which seems better, but makes me write wrapper functions around (almost) perfectly working library functions
  • doing a mostly pointless 'ping' query every N minutes, which is stressing on the db which is useless 99.9% of the time.

Here is some code to illustrate:

import mariadb
class Db:
  ...
  def __init__(self):
    self.conn = mariadb.connect(user=self.__db_user, password=self.__db_pass, host=self.__db_host, port=self.__db_port, database=self.__db_name)

  def one_of_many_functions(self, ...):
    cur = self.conn.cursor()
    cur.execute('SELECT ...') # Here is where the mariadb.DatabaseError happens after long inactivity, and otherwise runs fine
    ...

I actually really don't understand why python's mariadb implementation doesn't handle this. When the connection is lost, cur.execute will throw a mariadb.DatabaseError, but no action is to be taken, because if I requery with that same database connection, it works again. So the connection does repair itself. Why does the component make me requery while it 'repairs' the connection itself and could query again?

But as it is what it is, my question is: what is the nicest way to handle this?

scippie
  • 2,011
  • 1
  • 26
  • 42
  • I really don't want to have to write a try-excet block around every query and a while loop around it to retry until it works... Also, writing a specific function for it is stupid for an error I can't control. – scippie Sep 15 '20 at 07:56
  • @matt why is leaving a DB connection open for one application that needs it once in a while not good? When should I close/reopen this connection? After 5 queries? After 5 minutes? It's very unpredictable how the db will be used because it's a request handling server for everyone that logs in into the game... Reopening a db connection for every query seems wrong to me too. – scippie Sep 15 '20 at 07:58
  • Ill repeat "It could be an issue that your db would fill up with open connections when applications don't close them though." – matt Sep 15 '20 at 07:59
  • Users are way slower than a db. Anytime you need to wait on user input, it is appropriate to open/close. Also, you might look into a connection pool. They can manage keeping a connection alive. – matt Sep 15 '20 at 08:03
  • Sorry @matt for not being so clear, but I know all these things you are talking about. I have written software on DB's for years and I have solved these problems with things like decorator functions. My point is that it shouldn't be necessary. My server also has a constant open redis connection where it listens to events. Why should a DB connection die after a while and why shouldn't it repair automatically? I understand that I can look into my mariadb settings, but then that's something to remember every time you change db. My question is in hopes of finding a proper solution for this. – scippie Sep 15 '20 at 08:10
  • And I know I could just create a new connection every time, and I know I could create a DB pool. Both should not be necessary. The first because a persistent connection is perfect for this situation. The latter because if this kind of load balancing is needed, I will use high availability splitting, starting an extra server. The game is based on that. – scippie Sep 15 '20 at 08:14
  • I found the reason on https://stackoverflow.com/questions/51506416/mariadb-server-times-out-client-connection-after-600-seconds – scippie Sep 15 '20 at 08:19
  • Ok. You must understand that the question grew from "not understanding" to "understanding but thinking it's not ok". Please don't take offense. It was not directed at you. What you say isn't true though. I did not ask 'what can I do', I asked for a better way than the ways I thought were 'not good enough' if you prefer. – scippie Sep 15 '20 at 12:05
  • My question was 'how should I handle it' however and nobody has given me a better way to handle it than those I already gave. It's sad that my question got -1-ed for this. – scippie Sep 15 '20 at 12:07
  • I'll change it. – scippie Sep 15 '20 at 12:40

3 Answers3

2

If you set a long time out value, there is even no guarantee, that the connection will drop due to other reasons (client timeout, 24 hr disconnect, ...)

An option would be to set auto_reconnect, as in the following example:

import mariadb

conn1= mariadb.connect()
conn2= mariadb.connect()

# Force MariaDB/Connector Python to reconnect
conn2.auto_reconnect= True

cursor1= conn1.cursor()

print("Connid of connection 2: %s" % conn2.connection_id);

# Since we don't want to wait, we kill the conn2 intentionally
cursor1.execute("KILL %s" % conn2.connection_id)

cursor2= conn2.cursor()
cursor2.execute("select connection_id()")
row= cursor2.fetchall()
print("Connid of connection 2: %s" % conn2.connection_id);
print(row)

Output:

Connid of connection 2: 174
Connid of connection 2: 175
[(175,)]

So after connection 2 was killed, next cursor.execute will establish a new connection before executing the statement. This solution will not work if you use an existing open cursor, since the internal statement handle becomes invalid.

Georg Richter
  • 5,970
  • 2
  • 9
  • 15
  • OMG! I didn't know this existed! Yes, a broken connection during the use of a cursor is not something I try to fix. But automatic fixing of a broken connection before executing a query is what I need. Where did you find this information? I haven't found a real API for the mariadb connector. Do you know where I can find it? – scippie Sep 15 '20 at 16:25
  • Since I'm the author/maintainer of MariaDB Connector/Python it wasn't hard to find :-) It is also documented at https://mariadb-corporation.github.io/mariadb-connector-python/connection.html – Georg Richter Sep 15 '20 at 19:35
  • Haha. Great! Thanks! – scippie Sep 16 '20 at 06:28
  • Hm, it doesn't seem to work however, because today I had the error again on the first query after several hours of inactivity. Or am I misunderstanding? Shouldn't this prevent you from getting the error and shouldn't it automatically retry? – scippie Sep 17 '20 at 17:42
0

Are you using a socket or TCP/IP for connection?

TCP/IP connections are designed to be cleaned up after a period of no traffic. You might say it's idiotic, but there's really no better way to know if a program crashes.

For the same reason, databases have their own timeout mechanism. For MySQL it's called wait_timeout.

Normally, a connection object (or its wrapper) would take care of running some no-op query if there is nothing else going on with the connection, something like select 1. This is a standard practice. Check the documentation for your connection object - it might already be there, you just need to configure it. Use something like 30-60 seconds.

If not, you will have to implement it yourself. It doesn't matter how, the point is that you cannot expect connections to stay open forever. Either make connections short-lived (open it only when you need it and close it afterwards), or implement a timer that will insert some no-op query periodically. In the latter case note that you will need to implement synchronization mechanism to make sure that your application query never runs at the same time as no-op query.

jurez
  • 4,436
  • 2
  • 12
  • 20
  • Yes, you are right of course, and I would not expect it otherwise. But what I don't understand is that python's mariadb doesn't handle this automatically. It does reconnect after failure (I don't need to do it), so if you do the query, it gives the exception, and if you then query again, it works again. Why doesn't the implementation do this immediately for you? That's what I mean with the idiotic. – scippie Sep 15 '20 at 12:03
  • Anyway, I solved it in what I think is the least ugly way to handle it. – scippie Sep 15 '20 at 12:13
  • It works like that because somebody made it like that :-) Obviously they either didn't think about it or thought that this would be better or had other reasons. Perhaps it was what the bosses' wife said when she was drunk. You will find many, many, many, many, many, many bigger stupidities in software, so if this is your only problem, consider yourself blessed... – jurez Sep 15 '20 at 12:15
0

Have you considered using a connection pool.

# Create Connection Pool
pool = mariadb.ConnectionPool(
      #...,
      pool_size=1
   )

Then in your connection method.

try:
    pconn = pool.get_connection()

except mariadb.PoolError as e:
   # Report Error
   print(f"Error opening connection from pool: {e}")

The documentation doesn't say what happens when connections are closed or broken. I expect that it takes care of that, and always tries to provide a valid connection ( as long as your not asking for more connections than are in the pool.)

I got the code from their docs

matt
  • 10,892
  • 3
  • 22
  • 34
  • You mentioned this in the comments on my question also. I think this is one of the better solutions, but still, I feel like it is so much overkill. This feels like behind the scene, maybe 4, 8, 16? connections are made, and every time you receive one of them. I only need one connection. Having multiple connections at hand is not necessary and seems like a waste. – scippie Sep 15 '20 at 12:53
  • Allright, you just added the code for pool_size. It's a way to go of course, but also again, some bulky management object that shouldn't be necessary in a language like Python imo. – scippie Sep 15 '20 at 12:54
  • Bulky is relative, plus it is using mariadb's connection pooling, so it could be very effective. mariadb is a pretty low level api. It conforms to a python db api so it can be used with higher level dp libs. Like sqlalchemy, where you don't need to know the actual db used. – matt Sep 15 '20 at 13:00
  • That's true of course. I've written mysql handling code in C and C++ in the past (where I actually expect these problems) and somehow, I expected a Python implementation to be higher level. – scippie Sep 15 '20 at 13:02