cppcms-users

[Cppcms-users] Cppcms-users] cppdb: test connect to database and reconnection

[Daniel V. <chi...@gm...>]

Hello.

I can't find a method to test database connection. I think there is no
such method (Similarly, there is no such method in SOCI).

I think this method would be useful for connection error handling and
robustness.

Another issue related: When connection is lost to database and we
reconnect later, we need to re build all prepared statements. 

The ideal would have a statement object to be rebuilt when its
associated session is reconnected.

This would be a code like this:

        cppdb::session sql("...");
        ...
        
        if (! sql.is_connect() )
        {
        sql.reconnect(); // reconnect session and rebuild all prepared
        statement associated
        }
        

Would it be possible in future cppdb releases?

For example in mysql by default the server closes the connection after
eight hours if nothing has happened. ( "MySQL server has gone away"
error ). Then a web application that is idle for 8 hours or more suffer
from such problems. 

What is the proper way to deal with this problem with actual version?

Thank you.

Re: [Cppcms-users] Cppcms-users] cppdb: test connect to database and reconnection

[Artyom <art...@ya...>]

>Hello.
>
>I can't find a method to test database connection. I think there is no such 
>method (Similarly, there is no such method in SOCI).
>
>I think this method would be useful for connection error handling and 
>robustness.

The simplest way to do this would be 

    int value;
    sql << "SELECT 1" >> cppdb::row >> value;

The problem is that usually you do not want to "ping" a server
for each query. The reason is round trip cost. So if you query 
the database every second you probably don't need this, and if
you idle for hours you probably not need to keep the idle connection
opened.

cppdb allows you use connection pooling.

Each connection in pool has its maximal idle timeout so if you
fetch a connection from the pool and it was idle for more
then certain period of time it would just be closed and
a new one opened for you.

So basically what you need it following: every time you need
a connection you just open it.

However if you specify option @pool_size=8 it would keep up to
8 open connections and each time you request it it would give you
an opened connection from pool. If the connection was idle
for too long time (10 min by default) it would be closed
and new connection would be opened for you.

For details see: http://art-blog.no-ip.info/sql/cppdb/connstr.html

For example:

    void some_func()
    {
        session sql("mysql:user=foo;password=bar;@pool_size=5");
        sql << "SELECT foo FROM bar";
        ...
    }

Thats it, all timeouts would be handled for you.
If you want to explicitly close all idle connections 
and not close them of attempt to connect.
you may use void cppdb::connections_manager::gc()

>
>Another issue related: When connection is lost to database and we reconnect 
>later, we need to re build all prepared statements. 
>
>
>The ideal would have a statement object to be rebuilt when its associated 
>session is reconnected.
>


When connection is closed all prepared statements are destroyed.
They would be prepared upon first request automatically.

Of course you should **not** keep them on your own, i.e.:

   class foo {
       foo()
       { 
         sql("mysql:...");
         my_insert = sql.prepare("INSERT ...");
       }
       void insert() {
         my_insert.bind(...);
         my_insert.exec() 
       }        
       session sql;
       statement my_insert;
   }

But rather access them as usually,

   class foo {
       foo()
       { 
          conn_str = "mysql:...;@pool_size=5"
       }
       void insert() {
         session sql(conn_str);
         sql << "INSERT ..." << x << exec;
       }
       std::string conn_str;
   }
  



> For example in mysql by default the server closes the connection after eight 
> hours if nothing has happened. ("MySQL server has gone away" error ). Then a 
>web 
>
> application that is idle for 8 hours or more suffer from such problems. 

I've had with issue with my blog - the cache was too damn good so I hadn't
need my database at all and it was gone after hours.

I had changed MySQL server options and increased idle time. However
with CppDB and connection pooling it would be much simpler.

Artyom


      

Re: [Cppcms-users] Cppcms-users] cppdb: test connect to database and reconnection

[Daniel V. <chi...@gm...>]

On Tue, 2010-12-28 at 23:50 -0800, Artyom wrote:
> When connection is closed all prepared statements are destroyed.
> They would be prepared upon first request automatically.
> 
> Of course you should **not** keep them on your own, i.e.:
> 
>    class foo {
>        foo()
>        { 
>          sql("mysql:...");
>          my_insert = sql.prepare("INSERT ...");
>        }
>        void insert() {
>          my_insert.bind(...);
>          my_insert.exec() 
>        }        
>        session sql;
>        statement my_insert;
>    }
> 
> But rather access them as usually,
> 
>    class foo {
>        foo()
>        { 
>           conn_str = "mysql:...;@pool_size=5"
>        }
>        void insert() {
>          session sql(conn_str);
>          sql << "INSERT ..." << x << exec;
>        }
>        std::string conn_str;
>    }
>   

My problem was that I kept them on my own!. Thank you!

I see into your code, and I figure out you not rebuild prepared
statements, instead you fetch them from cache.

I think this information is useful for new users to guide them on how to
use the library.