0

I init mariadb connection while webapp initialization like this:

con = MySQLdb.connect('localhost', 'user', 'pass', 'db')

Now I've found it doesn't work since there is a timeout to this connection. What is the best practice to set/keep connection to db? Increase timeout, create connection in each request or something more tuned?

d3im
  • 323
  • 2
  • 4
  • 18
  • Are you spending a long time connected but not doing any SQL? – Rick James Aug 01 '15 at 23:50
  • Yes, the connection is init in python script when webapp initializes (for now). For longer time there is no SQL, so connection times out. I don't know what is the common way how to overcome this (and how does this common frameworks - like django). – d3im Aug 02 '15 at 17:40
  • This is a web application? And it sits there for more than 30 seconds? And the users of the app put up with such sluggish web sites? I ask because the default for `wait_timeout` is more than generous for web pages, and I don't understand what you is different about your situation. – Rick James Aug 02 '15 at 17:52
  • It's just a small project, where users can check their info. There are just a few users and they do not have to check the info and they usually don't. I access the pages for changing that info just few times a month, when I need to change that info. I see that is not enough to be within timeout. As I said, I init DB connection when webapp starts (for all requests) not when users opens website i.e. in every request. I need to know what is the best practice to have connection to DB everytime when the request to DB is needed. The site is not sluggish. – d3im Aug 03 '15 at 08:02
  • What's running the webapp? My experience is mostly with Apache+PHP, wherein _each_ web page is isolated and must do its own connection. – Rick James Aug 03 '15 at 18:40
  • My configuration is Nginx + Python, but interaction with mariadb is done by mysqlclient. And this page have it's own connection for all subpages (one webapp). I'll first let try as I wrote below and let you know. – d3im Aug 04 '15 at 20:20

1 Answers1

0

Recommend you build a singleton object that returns the db connection (your con). The first time it is called, it performs the MySQLDB.connect(). Then it keeps a static copy of the connection for returning to the caller on subsequent calls.

In order to recover from disconnects... First, note that a timeout is not the only reason for losing a connection; a network glitch could cause it. So forget about timeouts and simply plan to reconnect when needed.

One way is to do a ping to see if its still open, and if it is not, reconnect.

Another way is to have "autoreconnect" turned on. But this has negative impact on transactions, @variables, and other thing that could bite you.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • You may be correct, now I found this: http://stackoverflow.com/a/982873/3123921 and it seems to have what I need. I'll try something like that soon. – d3im Aug 04 '15 at 20:13
  • A problem with that code (and probably with my suggestion): If you are in the middle of a transaction, it has been ROLLBACK'd. – Rick James Aug 04 '15 at 21:51