Re: [Interest] Long running application with database connection
On 10/09/2013 04:46 PM, S R wrote: In a similar situation, if I recall correctly, we elected to open the database connection each time we needed it. Through testing we found that connection pooling in the the underlying infrastructure (windows, using oracle and ms sqlserver) worked well. The reconnection cost was usually trivial compared with the actual query especially if the connection was recently used; the resulting code was simpler/cleaner; and the approach seemed robust to lots of other reasons (not just idle time) that the connection might drop. I do recall that it was hard to test well. I considered that as well, but I see the problem that some subroutine that interacts with the database cannot know whether something higher up the call stack is also, in which case closing the connection would leave the higher stack frame with an invalid connection. Of course you could do something more involved like only close the connection if you opened it, but I'd rather avoid fixing up every single database access. I still don't know whether keeping the connection open for a long time is somehow a bad idea. But on the other hand, the mysqld wait_timeout can be configured to up to a year, so it can't be that bad. -- Mit freundlichen Gruessen / Best regards Marc Schmitzer SW-Entwicklung ESWL / ESWT Software development ESWL / ESWT * Richard Wolf GmbH Pforzheimer Strasse 32 75438 Knittlingen Tel.: +49 7043 35-1107 Fax: +49 7043 35-1255 Email:marc.schmit...@richard-wolf.com Web: http://www.richard-wolf.com * +++ Richard Wolf GmbH, Pforzheimer Strasse 32, 75438 Knittlingen Managing Directors: Siegfried Karst, Juergen Pfab, Juergen Steinbeck. Trade Register: Mannheim HRB 510031 +++ Richard Wolf GmbH routinely monitors the content of e-mail sent and received via its network for the purposes of ensuring compliance with its policies and procedures. Richard Wolf GmbH is not responsible for any changes made to the message after it has been sent. Where opinions are expressed, they are not necessarily those of Richard Wolf GmbH. This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. If you are not the intended addressee, or the person responsible for delivering it to them, you may not copy, forward, disclose, or otherwise use it or any part of it in any way. To do so may be unlawful. If you receive this e-mail by mistake, please advise the sender immediately. +++ ___ Interest mailing list Interest@qt-project.org http://lists.qt-project.org/mailman/listinfo/interest
Re: [Interest] Long running application with database connection
Op 10-10-2013 8:51, Marc Schmitzer schreef: On 10/09/2013 04:46 PM, S R wrote: In a similar situation, if I recall correctly, we elected to open the database connection each time we needed it. Through testing we found that connection pooling in the the underlying infrastructure (windows, using oracle and ms sqlserver) worked well. The reconnection cost was usually trivial compared with the actual query especially if the connection was recently used; the resulting code was simpler/cleaner; and the approach seemed robust to lots of other reasons (not just idle time) that the connection might drop. I do recall that it was hard to test well. I considered that as well, but I see the problem that some subroutine that interacts with the database cannot know whether something higher up the call stack is also, in which case closing the connection would leave the higher stack frame with an invalid connection. Of course you could do something more involved like only close the connection if you opened it, That would not even help, I think but I'd rather avoid fixing up every single database access. I still don't know whether keeping the connection open for a long time is somehow a bad idea. But on the other hand, the mysqld wait_timeout can be configured to up to a year, so it can't be that bad. It sounds to me like you have your database access too spread out in your application. I really try to confine access to resources into specialized (groups of) classes to manage that resource. That works well for databases, and makes it that much easier to manage issues like these. André -- You like Qt? I am looking for collegues to join me at i-Optics! ___ Interest mailing list Interest@qt-project.org http://lists.qt-project.org/mailman/listinfo/interest
Re: [Interest] Long running application with database connection
On 9 October 2013 11:27, Marc Schmitzer marc.schmit...@richard-wolf.com wrote: While this works well most of the time, we occasionally have queries fail with MySQL server has gone away errors. We know that the mysqld process has indeed *not* gone away, and re-opening the connection works. My theory is that the initial QSqlDatabase::open() creates a connection to the SQL server that is kept open permanently by the QSqlDatabase instance that Qt keeps internally (in the ConnectionDict) that references the same connection. The SQL server eventually closes the connection after some timeout, which the application doesn't notice until it tries to use the connection again. I've seen the same behavior from the python mysql drivers, so I think this is probably the correct explanation. I don't know if the mysql library offers a way to detect when you get disconnected that could be used to make the implementation reconnect automatically. Cheers Rich. ___ Interest mailing list Interest@qt-project.org http://lists.qt-project.org/mailman/listinfo/interest
Re: [Interest] Long running application with database connection
On 10/09/2013 12:27 PM, Marc Schmitzer wrote: While this works well most of the time, we occasionally have queries fail with MySQL server has gone away errors. We know that the mysqld process has indeed *not* gone away, and re-opening the connection works. Ok, this appears to be caused by the wait_timeout option of the MySQL server[1], which defaults to 8 hours. If you are unwilling/unable to change the server config, there are at least two ways of working around this: 1) Setting the reconnect option on the mysql connection. Unfortunately, this is not supported by the Qt-API (not even QSqlDatabase::setConnectOptions) and requires fetching the raw MYSQL* handle from the QSqlDriver and using mysql_options() from libmysqlclient. 2) Since the wait_timeout is an *idle* timeout, the connection can presumably be kept alive by making regular dummy queries. [1] http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout -- Mit freundlichen Gruessen / Best regards Marc Schmitzer SW-Entwicklung ESWL / ESWT Software development ESWL / ESWT * Richard Wolf GmbH Pforzheimer Strasse 32 75438 Knittlingen Tel.: +49 7043 35-1107 Fax: +49 7043 35-1255 Email:marc.schmit...@richard-wolf.com Web: http://www.richard-wolf.com * +++ Richard Wolf GmbH, Pforzheimer Strasse 32, 75438 Knittlingen Managing Directors: Siegfried Karst, Juergen Pfab, Juergen Steinbeck. Trade Register: Mannheim HRB 510031 +++ Richard Wolf GmbH routinely monitors the content of e-mail sent and received via its network for the purposes of ensuring compliance with its policies and procedures. Richard Wolf GmbH is not responsible for any changes made to the message after it has been sent. Where opinions are expressed, they are not necessarily those of Richard Wolf GmbH. This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. If you are not the intended addressee, or the person responsible for delivering it to them, you may not copy, forward, disclose, or otherwise use it or any part of it in any way. To do so may be unlawful. If you receive this e-mail by mistake, please advise the sender immediately. +++ ___ Interest mailing list Interest@qt-project.org http://lists.qt-project.org/mailman/listinfo/interest
Re: [Interest] Long running application with database connection
In a similar situation, if I recall correctly, we elected to open the database connection each time we needed it. Through testing we found that connection pooling in the the underlying infrastructure (windows, using oracle and ms sqlserver) worked well. The reconnection cost was usually trivial compared with the actual query especially if the connection was recently used; the resulting code was simpler/cleaner; and the approach seemed robust to lots of other reasons (not just idle time) that the connection might drop. I do recall that it was hard to test well. Sam ___ Interest mailing list Interest@qt-project.org http://lists.qt-project.org/mailman/listinfo/interest
