Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Content-Type: text/plain; charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 9.2 \(3112\))
Subject: Re: Reconnecting automatically after DB downtime
From: Hugi Thordarson <hugi@karlmenn.is>
In-Reply-To: <9A950AAC-FC6B-4161-8224-FE65F17F5ED0@objectstyle.org>
Date: Wed, 9 Mar 2016 08:52:40 +0000
Content-Transfer-Encoding: quoted-printable
Message-Id: <840FE108-023F-49BC-A711-57C27C44665D@karlmenn.is>
References: <15E01460-2B2C-4E0A-BE44-86D8020060F7@karlmenn.is>
 <DAF905B5-75AD-4B73-83FC-DCC1A4A66994@objectstyle.org>
 <586DD623-7C0F-4AF9-80C5-53DDB80A6ADA@karlmenn.is>
 <55049F21-3527-4B88-90BC-202D1451EDB7@objectstyle.org>
 <1CE7D336-E62F-443E-B6A1-4FC9863FC94F@karlmenn.is>
 <CAA-upzo_-SV-9oxCqK+EtTo4iZrFn3k1urNBF9U=YefjesTFeA@mail.gmail.com>
 <C3A7F3DE-D1E8-4070-AE49-B9157870F31A@karlmenn.is>
 <CAA-upzrvqmcguXuvXHv0MidQdoayDjeHWKrsmtZ=qu7PMknE4Q@mail.gmail.com>
 <CAOUwSGuQmu22s_c01OSX1A7ko=tV3-N_6O2pZ11rMnQEv9GOYQ@mail.gmail.com>
 <09C20F0B-12DB-4B54-BAD9-D41FD594D168@karlmenn.is>
 <372DFE65-3669-43D2-803F-0E4DE2B1700D@objectstyle.org>
 <945486CE-F9A3-4E4D-8EBB-5738D0AF121A@karlmenn.is>
 <D0DAB495-5309-4A69-9043-556E15F49259@karlmenn.is>
 <9A950AAC-FC6B-4161-8224-FE65F17F5ED0@objectstyle.org>
To: user@cayenne.apache.org

Nice, works like a charm :).

On a side note, websites that I=E2=80=99ve switched to HikariCP have =
been up since the switch, so it does seem to have been a problem with =
Cayenne=E2=80=99s connection pool.

Also, the websites I=E2=80=99ve switched over feel like they=E2=80=99ve =
had a caffeine enema or something of the ilk. They feel vastly faster in =
many cases. But that may also have something to do with configuration =
(HikariCP has great pages about DB specific configuration for the most =
common DBs).

https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration

Cheers,
- hugi


> On 8. mar. 2016, at 14:37, Andrus Adamchik <andrus@objectstyle.org> =
wrote:
>=20
> Also pretty easy - define a custom DataSourceFactory:
>=20
> builder.addModule(b -> =
b.bind(DataSourceFactory.class).to(MyImpl.class));
>=20
> Andrus
>=20
>> On Mar 8, 2016, at 3:20 AM, Hugi Thordarson <hugi@karlmenn.is> wrote:
>>=20
>> Not quite done :).
>>=20
>> I have a project that uses multiple Cayenne models connected to =
multiple databases. The connection information is currently stored in =
the models and I=E2=80=99m constructing the ServerRuntime like this:
>>=20
>> ServerRuntimeBuilder b =3D new ServerRuntimeBuilder( "Netbokhald" );
>> b.addConfig( "cayenne-core/cayenne-project.xml" );
>> b.addConfig( "cayenne-reporting/cayenne-project.xml" );
>>=20
>> How would I go about programmatically configuring separate =
DataSources for each model?
>>=20
>> Cheers,
>> - hugi
>>=20
>>=20
>>=20
>>> On 7. mar. 2016, at 21:03, Hugi Thordarson <hugi@karlmenn.is> wrote:
>>>=20
>>> Well, that was easy! Up and running in production.
>>>=20
>>> HikariConfig config =3D new HikariConfig();
>>> config.setJdbcUrl( =
"jdbc:mysql://server:3306/database?useUnicode=3Dtrue&characterEncoding=3Du=
tf8&autoReconnect=3Dtrue&connectTimeout=3D0" );
>>> config.setUsername( =E2=80=9CmyUsername" );
>>> config.setPassword( =E2=80=9CmyPassword" );
>>>=20
>>> HikariDataSource dataSource =3D new HikariDataSource( config );
>>> serverRuntimeBuilder =3D serverRuntimeBuilder.dataSource( dataSource =
);
>>>=20
>>> Thanks,
>>> - hugi
>>>=20
>>>=20
>>>> On 7. mar. 2016, at 20:51, Andrus Adamchik <andrus@objectstyle.org> =
wrote:
>>>>=20
>>>> Yep. Cayenne built-in pool is intentionally basic with a minimal =
number of features (Here we may be dealing with a bug though, and I'd =
like to fix it, but that's a separate issue). So yeah, using a third =
party DS may be a good idea. Here is an example how you can set it up:
>>>>=20
>>>> DataSource ds =3D // instantiate it via API specific to you DS =
provider
>>>>=20
>>>> ServerRuntime runtime =3D =
ServerRuntimeBuilder.builder().dataSource(ds).build();
>>>> // of course add any other things you need to add to the builder to =
get a working Cayenne stack.
>>>>=20
>>>> Andrus
>>>>=20
>>>>=20
>>>>> On Mar 7, 2016, at 12:45 PM, Hugi Thordarson <hugi@karlmenn.is> =
wrote:
>>>>>=20
>>>>> Thanks John! I=E2=80=99m going to try my hand at HikariCP.
>>>>>=20
>>>>> Are there any examples anywhere on how to configure Cayenne to use =
a third party connection pool?
>>>>>=20
>>>>> Cheers,
>>>>> - hugi
>>>>>=20
>>>>>=20
>>>>>=20
>>>>>=20
>>>>>> On 7. mar. 2016, at 20:39, John Huss <johnthuss@gmail.com> wrote:
>>>>>>=20
>>>>>> EOF can do JNDI, but that's not going to do anything to fix your =
problem.
>>>>>>=20
>>>>>> The connection pool in cayenne had some changes somewhat recently =
so it's
>>>>>> entirely possible there are bugs.
>>>>>>=20
>>>>>> In practice it turns out many people don't use the connection =
pool built-in
>>>>>> to Cayenne at all, and rather use a third-party connection pool, =
of which
>>>>>> there are several.  The cayenne one is decent, but it is very =
limited in
>>>>>> functionality and less robust due to having a smaller user base =
and being a
>>>>>> non-core feature.
>>>>>>=20
>>>>>> You can use another pool like:
>>>>>> hikari <https://github.com/brettwooldridge/HikariCP> - If I was =
starting a
>>>>>> new project I'd use this
>>>>>> tomcat-jdbc =
<https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html> -
>>>>>> This is what I currently use
>>>>>> commonds-dbcp <https://commons.apache.org/proper/commons-dbcp/>
>>>>>> c3po <http://www.mchange.com/projects/c3p0/>
>>>>>>=20
>>>>>> The main reason I turned to a third-party connection pool was to =
get
>>>>>> fair-scheduling which will provide connections to whoever has =
been waiting
>>>>>> the longest, which helps avoid unnecessary errors caused by =
serving
>>>>>> requests out of order.
>>>>>>=20
>>>>>> John
>>>>>>=20
>>>>>> On Mon, Mar 7, 2016 at 2:31 PM Michael Gentry =
<mgentry@masslight.net> wrote:
>>>>>>=20
>>>>>>> Well, I'm not sure what you are using to run your web =
application, but
>>>>>>> Tomcat, Jetty, JBoss, etc all have mechanisms to provide JNDI =
lookups of DB
>>>>>>> connection pools.  You just tell Cayenne Modeler to use JNDI =
lookup and
>>>>>>> give it the JNDI name, then configure the container to provide =
the DB
>>>>>>> connection.
>>>>>>>=20
>>>>>>> Is your WO application using EOF or Cayenne?  Been a while since =
I used WO,
>>>>>>> but I'd be stunned if EOF cannot use a JNDI lookup as well.
>>>>>>>=20
>>>>>>> mrg
>>>>>>>=20
>>>>>>>=20
>>>>>>> On Mon, Mar 7, 2016 at 3:07 PM, Hugi Thordarson =
<hugi@karlmenn.is> wrote:
>>>>>>>=20
>>>>>>>> Hi Michael,
>>>>>>>> does using JNDI change anything about the connection itself, =
isn=E2=80=99t it
>>>>>>> just
>>>>>>>> a different method of looking up connection information?
>>>>>>>>=20
>>>>>>>> But I probably can=E2=80=99t use it anyway since one of the =
apps is a WebObjects
>>>>>>>> app and doesn=E2=80=99t provide a JNDI service (at least I=E2=80=99=
ve never used it).
>>>>>>>>=20
>>>>>>>> Thanks,
>>>>>>>> - hugi
>>>>>>>>=20
>>>>>>>>=20
>>>>>>>>> On 7. mar. 2016, at 19:13, Michael Gentry =
<mgentry@masslight.net>
>>>>>>> wrote:
>>>>>>>>>=20
>>>>>>>>> Hi Hugi,
>>>>>>>>>=20
>>>>>>>>> Since this appears to be a web-based application, can you =
switch to
>>>>>>> using
>>>>>>>>> JNDI?
>>>>>>>>>=20
>>>>>>>>> mrg
>>>>>>>>>=20
>>>>>>>>>=20
>>>>>>>>> On Mon, Mar 7, 2016 at 5:46 AM, Hugi Thordarson =
<hugi@karlmenn.is>
>>>>>>>> wrote:
>>>>>>>>>=20
>>>>>>>>>> Hi all,
>>>>>>>>>> This is still happening, even after I added a =
validationQuery, our app
>>>>>>>> is
>>>>>>>>>> dying quite frequently :(.  I=E2=80=99m not quite sure how to =
debug this, is
>>>>>>>> there
>>>>>>>>>> any way for me to catch where connections are being opened in =
the code
>>>>>>>> and
>>>>>>>>>> at what location they=E2=80=99re hanging?
>>>>>>>>>>=20
>>>>>>>>>>=20
>>>>>>>>>>=20
>>>>>>>>=20
>>>>>>> =
https://www.dropbox.com/s/8jkmh6513s6wwkn/Screenshot%202016-03-07%2010.21.=
53.png?dl=3D0
>>>>>>>>>>=20
>>>>>>>>>> Cheer,
>>>>>>>>>> - hugi
>>>>>>>>>>=20
>>>>>>>>>> // Hugi Thordarson
>>>>>>>>>> // http://www.loftfar.is/
>>>>>>>>>> // s. 895-6688
>>>>>>>>>>=20
>>>>>>>>>>=20
>>>>>>>>>>=20
>>>>>>>>>>> On 29. feb. 2016, at 11:25, Andrus Adamchik =
<andrus@objectstyle.org>
>>>>>>>>>> wrote:
>>>>>>>>>>>=20
>>>>>>>>>>>=20
>>>>>>>>>>>> On Feb 29, 2016, at 2:20 PM, Hugi Thordarson =
<hugi@karlmenn.is>
>>>>>>>> wrote:
>>>>>>>>>>>>=20
>>>>>>>>>>>> What does validationQuery do?
>>>>>>>>>>>=20
>>>>>>>>>>> Periodically executes for each pooled connection, and kills
>>>>>>> connections
>>>>>>>>>> that throw during validation. So it ensures that all pooled
>>>>>>> connections
>>>>>>>> are
>>>>>>>>>> in a good state.
>>>>>>>>>>>=20
>>>>>>>>>>> Andrus
>>>>>>>>>>>=20
>>>>>>>>>>=20
>>>>>>>>>>=20
>>>>>>>>=20
>>>>>>>>=20
>>>>>>>=20
>>>>>=20
>>>>=20
>>>=20
>>=20
>=20