Can't get MySQL non-jta connection pool with Aries-2.4.0 and EL-2.6.2 working - Looking for (more) example(s).

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Can't get MySQL non-jta connection pool with Aries-2.4.0 and EL-2.6.2 working - Looking for (more) example(s).

Erwin Hogeweg
Hi,

Not sure if this is a question for these lists or for the EL list but I figure I start here. Feel free to redirect when you feel it doesn’t belong here.

I am trying to get a non-jta connection pool (internal connection pool) working with EL 2.6.2, Aries 2.4.0 (incl. EL adapter), dbcp2-2.1 and mySQL, but I must be missing something because I just can’t get it to work properly. Everything works just fine w/o a connection pool, so this is definitely the source of the misery.

Been struggling with this for a while now, and I am running out of ideas. I think I could use some sample code to point me in the right direction that doesn't use Blueprint? I found some of Christian’s examples, but I don’t think they are using connection pools.

Below a short summary of what I run into.

When I am using the ‘original’ MysqlDataSource...

    private DataSource createMySQLDataSource( Dictionary<String, String> dbConnProps ) {
        MysqlDataSource ds = new MysqlDataSource();
        ds.setUrl( dbConnProps.get( "jdbc_url" ) );
        ds.setUser( dbConnProps.get( "jdbc_user" ) );
        ds.setPassword( dbConnProps.get( "jdbc_password" ) );
        return ds;
    }

… everything kinda works normally. The DataSource, PersistenceProvider and EntityManagerFactory are all created and registered correctly;

g! services javax.sql.DataSource
{javax.sql.DataSource}={eclipselink.target-database=MySQL, osgi.jndi.service.name=jdbc/mynonjta, service.id=139, service.bundleid=104, service.scope=singleton}
  "Registered by bundle:" com.my.project.persistence.mysqldatasource_4.0.0.SNAPSHOT [104]

g! services javax.persistence.EntityManagerFactory
{javax.persistence.EntityManagerFactory}={osgi.unit.version=4.0.0.SNAPSHOT, osgi.unit.name=my.pu, osgi.unit.provider=org.eclipse.persistence.jpa.PersistenceProvider, service.id=142, service.bundleid=98, service.scope=singleton}
  "Registered by bundle:" com.my.project.model_4.0.0.SNAPSHOT [98]

The performance is horrible though as I don’t really seem to get a connection pool. The connection is closed after every query. On top of that I loose all network connections every few seconds with a:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Which has me puzzled for a while now.

So my next attempt was to use the org.apache.commons.dbcp2.BasicDataSource:

   private DataSource createMySQLDataSource(  Dictionary<String, String> dbConnProps ) {

    BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
...
        return basicDataSource;
    }

This fails because the following exception:

[EL Severe]: 2016-07-24 14:41:55.872--java.lang.UnsupportedOperationException: Not supported by BasicDataSource
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1552)

Which is this method:

    @Override
    public Connection getConnection(String user, String pass) throws SQLException {
        // This method isn't supported by the PoolingDataSource returned by
        // the createDataSource
        throw new UnsupportedOperationException("Not supported by BasicDataSource");
    }

So I figured I create a version with a PoolingDataSource  (following the PoolingDataSourceExample in svn): 

        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(dbConnProps.get( "jdbc_url" ), "user", "password");
        PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);
    ObjectPool<PoolableConnection> connectionPool = new GenericObjectPool<>(poolableConnectionFactory);
    poolableConnectionFactory.setPool(connectionPool);
        PoolingDataSource<PoolableConnection> dataSource = new PoolingDataSource<>(connectionPool);
        return dataSource;

But that still gives me an exception:

[EL Severe]: 2016-07-24 16:40:30.392--java.lang.UnsupportedOperationException
at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:156)

So I am kinda lost now.

This is the relevant stuff from the persistence.xml file:

<non-jta-data-source>osgi:service/javax.sql.DataSource/(osgi.jndi.service.name=jdbc/mynonjta)</non-jta-data-source>

<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://my_db_server:3306/myschema" /> 
<property name="javax.persistence.jdbc.user" value="user" /> 
<property name="javax.persistence.jdbc.password" value="password" />
<!-- Configure connection pool. -->
<property name="eclipselink.connection-pool.default.initial" value="10" /> 
<property name="eclipselink.connection-pool.default.min" value="16" /> 
<property name="eclipselink.connection-pool.default.max" value="50" /> 


Although I only see one DataSource registered it somehow feels like there is some more stuff going on behind the (EL?) scenes that I don’t have a handle on yet.

BTW... I have also created an org.apache.aries.jpa.my.pu.cfg configuration file, but when I leave the DB properties out of the persistence.xml I get bunch of ClassNotFound exceptions, so that is suspicious.

BTW2… the examples link at the bottom of this page is broken: https://commons.apache.org/proper/commons-dbcp/


Regards,

Erwin

Reply | Threaded
Open this post in threaded view
|

Re: Can't get MySQL non-jta connection pool with Aries-2.4.0 and EL-2.6.2 working - Looking for (more) example(s).

Timothy Ward
Hi,

Have you considered using Aries Transaction Control? It's typically much simpler to configure than the raw JDBC service, and it definitely gives you connection pooling (again, without extra moving parts).

Best Regards,

Tim Ward

Sent from my iPhone

On 24 Jul 2016, at 21:51, Erwin Hogeweg <[hidden email]> wrote:

Hi,

Not sure if this is a question for these lists or for the EL list but I figure I start here. Feel free to redirect when you feel it doesn’t belong here.

I am trying to get a non-jta connection pool (internal connection pool) working with EL 2.6.2, Aries 2.4.0 (incl. EL adapter), dbcp2-2.1 and mySQL, but I must be missing something because I just can’t get it to work properly. Everything works just fine w/o a connection pool, so this is definitely the source of the misery.

Been struggling with this for a while now, and I am running out of ideas. I think I could use some sample code to point me in the right direction that doesn't use Blueprint? I found some of Christian’s examples, but I don’t think they are using connection pools.

Below a short summary of what I run into.

When I am using the ‘original’ MysqlDataSource...

    private DataSource createMySQLDataSource( Dictionary<String, String> dbConnProps ) {
        MysqlDataSource ds = new MysqlDataSource();
        ds.setUrl( dbConnProps.get( "jdbc_url" ) );
        ds.setUser( dbConnProps.get( "jdbc_user" ) );
        ds.setPassword( dbConnProps.get( "jdbc_password" ) );
        return ds;
    }

… everything kinda works normally. The DataSource, PersistenceProvider and EntityManagerFactory are all created and registered correctly;

g! services javax.sql.DataSource
{javax.sql.DataSource}={eclipselink.target-database=MySQL, osgi.jndi.service.name=jdbc/mynonjta, service.id=139, service.bundleid=104, service.scope=singleton}
  "Registered by bundle:" com.my.project.persistence.mysqldatasource_4.0.0.SNAPSHOT [104]

g! services javax.persistence.EntityManagerFactory
{javax.persistence.EntityManagerFactory}={osgi.unit.version=4.0.0.SNAPSHOT, osgi.unit.name=my.pu, osgi.unit.provider=org.eclipse.persistence.jpa.PersistenceProvider, service.id=142, service.bundleid=98, service.scope=singleton}
  "Registered by bundle:" com.my.project.model_4.0.0.SNAPSHOT [98]

The performance is horrible though as I don’t really seem to get a connection pool. The connection is closed after every query. On top of that I loose all network connections every few seconds with a:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Which has me puzzled for a while now.

So my next attempt was to use the org.apache.commons.dbcp2.BasicDataSource:

   private DataSource createMySQLDataSource(  Dictionary<String, String> dbConnProps ) {

    BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
...
        return basicDataSource;
    }

This fails because the following exception:

[EL Severe]: 2016-07-24 14:41:55.872--java.lang.UnsupportedOperationException: Not supported by BasicDataSource
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1552)

Which is this method:

    @Override
    public Connection getConnection(String user, String pass) throws SQLException {
        // This method isn't supported by the PoolingDataSource returned by
        // the createDataSource
        throw new UnsupportedOperationException("Not supported by BasicDataSource");
    }

So I figured I create a version with a PoolingDataSource  (following the PoolingDataSourceExample in svn): 

        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(dbConnProps.get( "jdbc_url" ), "user", "password");
        PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);
    ObjectPool<PoolableConnection> connectionPool = new GenericObjectPool<>(poolableConnectionFactory);
    poolableConnectionFactory.setPool(connectionPool);
        PoolingDataSource<PoolableConnection> dataSource = new PoolingDataSource<>(connectionPool);
        return dataSource;

But that still gives me an exception:

[EL Severe]: 2016-07-24 16:40:30.392--java.lang.UnsupportedOperationException
at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:156)

So I am kinda lost now.

This is the relevant stuff from the persistence.xml file:

<non-jta-data-source>osgi:service/javax.sql.DataSource/(osgi.jndi.service.name=jdbc/mynonjta)</non-jta-data-source>

<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://my_db_server:3306/myschema" /> 
<property name="javax.persistence.jdbc.user" value="user" /> 
<property name="javax.persistence.jdbc.password" value="password" />
<!-- Configure connection pool. -->
<property name="eclipselink.connection-pool.default.initial" value="10" /> 
<property name="eclipselink.connection-pool.default.min" value="16" /> 
<property name="eclipselink.connection-pool.default.max" value="50" /> 


Although I only see one DataSource registered it somehow feels like there is some more stuff going on behind the (EL?) scenes that I don’t have a handle on yet.

BTW... I have also created an org.apache.aries.jpa.my.pu.cfg configuration file, but when I leave the DB properties out of the persistence.xml I get bunch of ClassNotFound exceptions, so that is suspicious.

BTW2… the examples link at the bottom of this page is broken: https://commons.apache.org/proper/commons-dbcp/


Regards,

Erwin

Reply | Threaded
Open this post in threaded view
|

Re: Can't get MySQL non-jta connection pool with Aries-2.4.0 and EL-2.6.2 working - Looking for (more) example(s).

Erwin Hogeweg
Hi Tim,

> Have you considered using Aries Transaction Control?
I definitely have, that was going to be my next step once I got this working. I just can’t stand that I can’t figure this out. It is not rocket science IMHO ;-)

> It's typically much simpler to configure than the raw JDBC service, and it definitely gives you connection pooling (again, without extra moving parts).
Thanks. I might bite the bullet and skip ahead to Aries Transactions. Would have been nice to have a working platform as baseline.

Erwin

>
> Best Regards,
>
> Tim Ward
>
> Sent from my iPhone
>
> On 24 Jul 2016, at 21:51, Erwin Hogeweg <[hidden email]<mailto:[hidden email]>> wrote:
>
> Hi,
>
> Not sure if this is a question for these lists or for the EL list but I figure I start here. Feel free to redirect when you feel it doesn’t belong here.
>
> I am trying to get a non-jta connection pool (internal connection pool) working with EL 2.6.2, Aries 2.4.0 (incl. EL adapter), dbcp2-2.1 and mySQL, but I must be missing something because I just can’t get it to work properly. Everything works just fine w/o a connection pool, so this is definitely the source of the misery.
>
> Been struggling with this for a while now, and I am running out of ideas. I think I could use some sample code to point me in the right direction that doesn't use Blueprint? I found some of Christian’s examples, but I don’t think they are using connection pools.
>
> Below a short summary of what I run into.
>
> When I am using the ‘original’ MysqlDataSource...
>
>    private DataSource createMySQLDataSource( Dictionary<String, String> dbConnProps ) {
>        MysqlDataSource ds = new MysqlDataSource();
>        ds.setUrl( dbConnProps.get( "jdbc_url" ) );
>        ds.setUser( dbConnProps.get( "jdbc_user" ) );
>        ds.setPassword( dbConnProps.get( "jdbc_password" ) );
>        return ds;
>    }
>
> … everything kinda works normally. The DataSource, PersistenceProvider and EntityManagerFactory are all created and registered correctly;
>
> g! services javax.sql.DataSource
> {javax.sql.DataSource}={eclipselink.target-database=MySQL, osgi.jndi.service.name=jdbc/mynonjta, service.id=139, service.bundleid=104, service.scope=singleton}
>  "Registered by bundle:" com.my.project.persistence.mysqldatasource_4.0.0.SNAPSHOT [104]
>
> g! services javax.persistence.EntityManagerFactory
> {javax.persistence.EntityManagerFactory}={osgi.unit.version=4.0.0.SNAPSHOT, osgi.unit.name=my.pu, osgi.unit.provider=org.eclipse.persistence.jpa.PersistenceProvider, service.id=142, service.bundleid=98, service.scope=singleton}
>  "Registered by bundle:" com.my.project.model_4.0.0.SNAPSHOT [98]
>
> The performance is horrible though as I don’t really seem to get a connection pool. The connection is closed after every query. On top of that I loose all network connections every few seconds with a:
>
> com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
>
> Which has me puzzled for a while now.
>
> So my next attempt was to use the org.apache.commons.dbcp2.BasicDataSource:
>
>   private DataSource createMySQLDataSource(  Dictionary<String, String> dbConnProps ) {
>
>    BasicDataSource basicDataSource = new BasicDataSource();
>        basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
> ...
>        return basicDataSource;
>    }
>
> This fails because the following exception:
>
> [EL Severe]: 2016-07-24 14:41:55.872--java.lang.UnsupportedOperationException: Not supported by BasicDataSource
> at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1552)
>
> Which is this method:
>
>    @Override
>    public Connection getConnection(String user, String pass) throws SQLException {
>        // This method isn't supported by the PoolingDataSource returned by
>        // the createDataSource
>        throw new UnsupportedOperationException("Not supported by BasicDataSource");
>    }
>
> So I figured I create a version with a PoolingDataSource  (following the PoolingDataSourceExample in svn):
>
>        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(dbConnProps.get( "jdbc_url" ), "user", "password");
>        PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);
>    ObjectPool<PoolableConnection> connectionPool = new GenericObjectPool<>(poolableConnectionFactory);
>    poolableConnectionFactory.setPool(connectionPool);
>        PoolingDataSource<PoolableConnection> dataSource = new PoolingDataSource<>(connectionPool);
>        return dataSource;
>
> But that still gives me an exception:
>
> [EL Severe]: 2016-07-24 16:40:30.392--java.lang.UnsupportedOperationException
> at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:156)
>
> So I am kinda lost now.
>
> This is the relevant stuff from the persistence.xml file:
>
> <non-jta-data-source>osgi:service/javax.sql.DataSource/(osgi.jndi.service.name=jdbc/mynonjta)</non-jta-data-source>
>
> <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
> <property name="javax.persistence.jdbc.url" value="jdbc:mysql://my_db_server:3306/myschema" />
> <property name="javax.persistence.jdbc.user" value="user" />
> <property name="javax.persistence.jdbc.password" value="password" />
> <!-- Configure connection pool. -->
> <property name="eclipselink.connection-pool.default.initial" value="10" />
> <property name="eclipselink.connection-pool.default.min" value="16" />
> <property name="eclipselink.connection-pool.default.max" value="50" />
>
>
> Although I only see one DataSource registered it somehow feels like there is some more stuff going on behind the (EL?) scenes that I don’t have a handle on yet.
>
> BTW... I have also created an org.apache.aries.jpa.my.pu.cfg configuration file, but when I leave the DB properties out of the persistence.xml I get bunch of ClassNotFound exceptions, so that is suspicious.
>
> BTW2… the examples link at the bottom of this page is broken: https://commons.apache.org/proper/commons-dbcp/
>
>
> Regards,
>
> Erwin
>

Reply | Threaded
Open this post in threaded view
|

Re: Can't get MySQL non-jta connection pool with Aries-2.4.0 and EL-2.6.2 working - Looking for (more) example(s).

Christian Schneider
In reply to this post by Erwin Hogeweg
Hi Erwin my examples all use Connection pools and are even XA enabled. The trick is to use pax- jdbc for this.

See
https://ops4j1.jira.com/wiki/display/PAXJDBC/Pooling+and+XA+support+for+DataSourceFactory
https://ops4j1.jira.com/wiki/display/PAXJDBC/Create+DataSource+from+config

You just have to install pax-jdbc-mysql, pax-jdbc-config, pax-jdbc-pool-dbcp2, transaction

Then you create a config like described in the docs and you will get a DataSource with pooling and XA support.

Christian

On 24.07.2016 22:50, Erwin Hogeweg wrote:
Hi,

Not sure if this is a question for these lists or for the EL list but I figure I start here. Feel free to redirect when you feel it doesn’t belong here.

I am trying to get a non-jta connection pool (internal connection pool) working with EL 2.6.2, Aries 2.4.0 (incl. EL adapter), dbcp2-2.1 and mySQL, but I must be missing something because I just can’t get it to work properly. Everything works just fine w/o a connection pool, so this is definitely the source of the misery.

Been struggling with this for a while now, and I am running out of ideas. I think I could use some sample code to point me in the right direction that doesn't use Blueprint? I found some of Christian’s examples, but I don’t think they are using connection pools.

Below a short summary of what I run into.

When I am using the ‘original’ MysqlDataSource...

    private DataSource createMySQLDataSource( Dictionary<String, String> dbConnProps ) {
        MysqlDataSource ds = new MysqlDataSource();
        ds.setUrl( dbConnProps.get( "jdbc_url" ) );
        ds.setUser( dbConnProps.get( "jdbc_user" ) );
        ds.setPassword( dbConnProps.get( "jdbc_password" ) );
        return ds;
    }

… everything kinda works normally. The DataSource, PersistenceProvider and EntityManagerFactory are all created and registered correctly;

g! services javax.sql.DataSource
{javax.sql.DataSource}={eclipselink.target-database=MySQL, osgi.jndi.service.name=jdbc/mynonjta, service.id=139, service.bundleid=104, service.scope=singleton}
  "Registered by bundle:" com.my.project.persistence.mysqldatasource_4.0.0.SNAPSHOT [104]

g! services javax.persistence.EntityManagerFactory
{javax.persistence.EntityManagerFactory}={osgi.unit.version=4.0.0.SNAPSHOT, osgi.unit.name=my.pu, osgi.unit.provider=org.eclipse.persistence.jpa.PersistenceProvider, service.id=142, service.bundleid=98, service.scope=singleton}
  "Registered by bundle:" com.my.project.model_4.0.0.SNAPSHOT [98]

The performance is horrible though as I don’t really seem to get a connection pool. The connection is closed after every query. On top of that I loose all network connections every few seconds with a:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Which has me puzzled for a while now.

So my next attempt was to use the org.apache.commons.dbcp2.BasicDataSource:

   private DataSource createMySQLDataSource(  Dictionary<String, String> dbConnProps ) {

    BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
...
        return basicDataSource;
    }

This fails because the following exception:

[EL Severe]: 2016-07-24 14:41:55.872--java.lang.UnsupportedOperationException: Not supported by BasicDataSource
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1552)

Which is this method:

    @Override
    public Connection getConnection(String user, String pass) throws SQLException {
        // This method isn't supported by the PoolingDataSource returned by
        // the createDataSource
        throw new UnsupportedOperationException("Not supported by BasicDataSource");
    }

So I figured I create a version with a PoolingDataSource  (following the PoolingDataSourceExample in svn): 

        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(dbConnProps.get( "jdbc_url" ), "user", "password");
        PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);
    ObjectPool<PoolableConnection> connectionPool = new GenericObjectPool<>(poolableConnectionFactory);
    poolableConnectionFactory.setPool(connectionPool);
        PoolingDataSource<PoolableConnection> dataSource = new PoolingDataSource<>(connectionPool);
        return dataSource;

But that still gives me an exception:

[EL Severe]: 2016-07-24 16:40:30.392--java.lang.UnsupportedOperationException
at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:156)

So I am kinda lost now.

This is the relevant stuff from the persistence.xml file:

<non-jta-data-source>osgi:service/javax.sql.DataSource/(osgi.jndi.service.name=jdbc/mynonjta)</non-jta-data-source>

<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://my_db_server:3306/myschema" /> 
<property name="javax.persistence.jdbc.user" value="user" /> 
<property name="javax.persistence.jdbc.password" value="password" />
<!-- Configure connection pool. -->
<property name="eclipselink.connection-pool.default.initial" value="10" /> 
<property name="eclipselink.connection-pool.default.min" value="16" /> 
<property name="eclipselink.connection-pool.default.max" value="50" /> 


Although I only see one DataSource registered it somehow feels like there is some more stuff going on behind the (EL?) scenes that I don’t have a handle on yet.

BTW... I have also created an org.apache.aries.jpa.my.pu.cfg configuration file, but when I leave the DB properties out of the persistence.xml I get bunch of ClassNotFound exceptions, so that is suspicious.

BTW2… the examples link at the bottom of this page is broken: https://commons.apache.org/proper/commons-dbcp/


Regards,

Erwin



-- 
Christian Schneider
http://www.liquid-reality.de

Open Source Architect
http://www.talend.com
Reply | Threaded
Open this post in threaded view
|

Re: Can't get MySQL non-jta connection pool with Aries-2.4.0 and EL-2.6.2 working - Looking for (more) example(s).

Erwin Hogeweg
Thanks Christian,

I’ll give that another try.

Erwin

On Jul 25, 2016, at 10:26, Christian Schneider <[hidden email]> wrote:

Hi Erwin my examples all use Connection pools and are even XA enabled. The trick is to use pax- jdbc for this.

See
https://ops4j1.jira.com/wiki/display/PAXJDBC/Pooling+and+XA+support+for+DataSourceFactory
https://ops4j1.jira.com/wiki/display/PAXJDBC/Create+DataSource+from+config

You just have to install pax-jdbc-mysql, pax-jdbc-config, pax-jdbc-pool-dbcp2, transaction

Then you create a config like described in the docs and you will get a DataSource with pooling and XA support.

Christian

On 24.07.2016 22:50, Erwin Hogeweg wrote:
Hi,

Not sure if this is a question for these lists or for the EL list but I figure I start here. Feel free to redirect when you feel it doesn’t belong here.

I am trying to get a non-jta connection pool (internal connection pool) working with EL 2.6.2, Aries 2.4.0 (incl. EL adapter), dbcp2-2.1 and mySQL, but I must be missing something because I just can’t get it to work properly. Everything works just fine w/o a connection pool, so this is definitely the source of the misery.

Been struggling with this for a while now, and I am running out of ideas. I think I could use some sample code to point me in the right direction that doesn't use Blueprint? I found some of Christian’s examples, but I don’t think they are using connection pools.

Below a short summary of what I run into.

When I am using the ‘original’ MysqlDataSource...

    private DataSource createMySQLDataSource( Dictionary<String, String> dbConnProps ) {
        MysqlDataSource ds = new MysqlDataSource();
        ds.setUrl( dbConnProps.get( "jdbc_url" ) );
        ds.setUser( dbConnProps.get( "jdbc_user" ) );
        ds.setPassword( dbConnProps.get( "jdbc_password" ) );
        return ds;
    }

… everything kinda works normally. The DataSource, PersistenceProvider and EntityManagerFactory are all created and registered correctly;

g! services javax.sql.DataSource
{javax.sql.DataSource}={eclipselink.target-database=MySQL, osgi.jndi.service.name=jdbc/mynonjta, service.id=139, service.bundleid=104, service.scope=singleton}
  "Registered by bundle:" com.my.project.persistence.mysqldatasource_4.0.0.SNAPSHOT [104]

g! services javax.persistence.EntityManagerFactory
{javax.persistence.EntityManagerFactory}={osgi.unit.version=4.0.0.SNAPSHOT, osgi.unit.name=my.pu, osgi.unit.provider=org.eclipse.persistence.jpa.PersistenceProvider, service.id=142, service.bundleid=98, service.scope=singleton}
  "Registered by bundle:" com.my.project.model_4.0.0.SNAPSHOT [98]

The performance is horrible though as I don’t really seem to get a connection pool. The connection is closed after every query. On top of that I loose all network connections every few seconds with a:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Which has me puzzled for a while now.

So my next attempt was to use the org.apache.commons.dbcp2.BasicDataSource:

   private DataSource createMySQLDataSource(  Dictionary<String, String> dbConnProps ) {

    BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
...
        return basicDataSource;
    }

This fails because the following exception:

[EL Severe]: 2016-07-24 14:41:55.872--java.lang.UnsupportedOperationException: Not supported by BasicDataSource
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1552)

Which is this method:

    @Override
    public Connection getConnection(String user, String pass) throws SQLException {
        // This method isn't supported by the PoolingDataSource returned by
        // the createDataSource
        throw new UnsupportedOperationException("Not supported by BasicDataSource");
    }

So I figured I create a version with a PoolingDataSource  (following the PoolingDataSourceExample in svn): 

        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(dbConnProps.get( "jdbc_url" ), "user", "password");
        PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);
    ObjectPool<PoolableConnection> connectionPool = new GenericObjectPool<>(poolableConnectionFactory);
    poolableConnectionFactory.setPool(connectionPool);
        PoolingDataSource<PoolableConnection> dataSource = new PoolingDataSource<>(connectionPool);
        return dataSource;

But that still gives me an exception:

[EL Severe]: 2016-07-24 16:40:30.392--java.lang.UnsupportedOperationException
at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:156)

So I am kinda lost now.

This is the relevant stuff from the persistence.xml file:

<non-jta-data-source>osgi:service/javax.sql.DataSource/(osgi.jndi.service.name=jdbc/mynonjta)</non-jta-data-source>

<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://my_db_server:3306/myschema" /> 
<property name="javax.persistence.jdbc.user" value="user" /> 
<property name="javax.persistence.jdbc.password" value="password" />
<!-- Configure connection pool. -->
<property name="eclipselink.connection-pool.default.initial" value="10" /> 
<property name="eclipselink.connection-pool.default.min" value="16" /> 
<property name="eclipselink.connection-pool.default.max" value="50" /> 


Although I only see one DataSource registered it somehow feels like there is some more stuff going on behind the (EL?) scenes that I don’t have a handle on yet.

BTW... I have also created an org.apache.aries.jpa.my.pu.cfg configuration file, but when I leave the DB properties out of the persistence.xml I get bunch of ClassNotFound exceptions, so that is suspicious.

BTW2… the examples link at the bottom of this page is broken: https://commons.apache.org/proper/commons-dbcp/


Regards,

Erwin



-- 
Christian Schneider
http://www.liquid-reality.de

Open Source Architect
http://www.talend.com

Reply | Threaded
Open this post in threaded view
|

Re: Can't get MySQL non-jta connection pool with Aries-2.4.0 and EL-2.6.2 working - Looking for (more) example(s).

Timothy Ward
In reply to this post by Erwin Hogeweg
Hi Erwin,

Sent from my iPhone

> On 25 Jul 2016, at 15:18, Erwin Hogeweg <[hidden email]> wrote:
>
> Hi Tim,
>
>> Have you considered using Aries Transaction Control?
> I definitely have, that was going to be my next step once I got this working. I just can’t stand that I can’t figure this out. It is not rocket science IMHO ;-)

Actually, it sort of is. The number of different services involved gets large very quickly! This is one of the reasons that the OSGi RFC for Transaction Control exists.

>
>> It's typically much simpler to configure than the raw JDBC service, and it definitely gives you connection pooling (again, without extra moving parts).
> Thanks. I might bite the bullet and skip ahead to Aries Transactions. Would have been nice to have a working platform as baseline.

Transaction Control still needs you to use JPA persistence bundles (i.e. Meta-Persistence) but it simplifies everything else a lot. You can configure your EntityManager with just a few config admin properties.

Tim

>
> Erwin
>
>>
>> Best Regards,
>>
>> Tim Ward
>>
>> Sent from my iPhone
>>
>> On 24 Jul 2016, at 21:51, Erwin Hogeweg <[hidden email]<mailto:[hidden email]>> wrote:
>>
>> Hi,
>>
>> Not sure if this is a question for these lists or for the EL list but I figure I start here. Feel free to redirect when you feel it doesn’t belong here.
>>
>> I am trying to get a non-jta connection pool (internal connection pool) working with EL 2.6.2, Aries 2.4.0 (incl. EL adapter), dbcp2-2.1 and mySQL, but I must be missing something because I just can’t get it to work properly. Everything works just fine w/o a connection pool, so this is definitely the source of the misery.
>>
>> Been struggling with this for a while now, and I am running out of ideas. I think I could use some sample code to point me in the right direction that doesn't use Blueprint? I found some of Christian’s examples, but I don’t think they are using connection pools.
>>
>> Below a short summary of what I run into.
>>
>> When I am using the ‘original’ MysqlDataSource...
>>
>>   private DataSource createMySQLDataSource( Dictionary<String, String> dbConnProps ) {
>>       MysqlDataSource ds = new MysqlDataSource();
>>       ds.setUrl( dbConnProps.get( "jdbc_url" ) );
>>       ds.setUser( dbConnProps.get( "jdbc_user" ) );
>>       ds.setPassword( dbConnProps.get( "jdbc_password" ) );
>>       return ds;
>>   }
>>
>> … everything kinda works normally. The DataSource, PersistenceProvider and EntityManagerFactory are all created and registered correctly;
>>
>> g! services javax.sql.DataSource
>> {javax.sql.DataSource}={eclipselink.target-database=MySQL, osgi.jndi.service.name=jdbc/mynonjta, service.id=139, service.bundleid=104, service.scope=singleton}
>> "Registered by bundle:" com.my.project.persistence.mysqldatasource_4.0.0.SNAPSHOT [104]
>>
>> g! services javax.persistence.EntityManagerFactory
>> {javax.persistence.EntityManagerFactory}={osgi.unit.version=4.0.0.SNAPSHOT, osgi.unit.name=my.pu, osgi.unit.provider=org.eclipse.persistence.jpa.PersistenceProvider, service.id=142, service.bundleid=98, service.scope=singleton}
>> "Registered by bundle:" com.my.project.model_4.0.0.SNAPSHOT [98]
>>
>> The performance is horrible though as I don’t really seem to get a connection pool. The connection is closed after every query. On top of that I loose all network connections every few seconds with a:
>>
>> com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
>>
>> Which has me puzzled for a while now.
>>
>> So my next attempt was to use the org.apache.commons.dbcp2.BasicDataSource:
>>
>>  private DataSource createMySQLDataSource(  Dictionary<String, String> dbConnProps ) {
>>
>>   BasicDataSource basicDataSource = new BasicDataSource();
>>       basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
>> ...
>>       return basicDataSource;
>>   }
>>
>> This fails because the following exception:
>>
>> [EL Severe]: 2016-07-24 14:41:55.872--java.lang.UnsupportedOperationException: Not supported by BasicDataSource
>> at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1552)
>>
>> Which is this method:
>>
>>   @Override
>>   public Connection getConnection(String user, String pass) throws SQLException {
>>       // This method isn't supported by the PoolingDataSource returned by
>>       // the createDataSource
>>       throw new UnsupportedOperationException("Not supported by BasicDataSource");
>>   }
>>
>> So I figured I create a version with a PoolingDataSource  (following the PoolingDataSourceExample in svn):
>>
>>       ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(dbConnProps.get( "jdbc_url" ), "user", "password");
>>       PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);
>>   ObjectPool<PoolableConnection> connectionPool = new GenericObjectPool<>(poolableConnectionFactory);
>>   poolableConnectionFactory.setPool(connectionPool);
>>       PoolingDataSource<PoolableConnection> dataSource = new PoolingDataSource<>(connectionPool);
>>       return dataSource;
>>
>> But that still gives me an exception:
>>
>> [EL Severe]: 2016-07-24 16:40:30.392--java.lang.UnsupportedOperationException
>> at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:156)
>>
>> So I am kinda lost now.
>>
>> This is the relevant stuff from the persistence.xml file:
>>
>> <non-jta-data-source>osgi:service/javax.sql.DataSource/(osgi.jndi.service.name=jdbc/mynonjta)</non-jta-data-source>
>>
>> <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
>> <property name="javax.persistence.jdbc.url" value="jdbc:mysql://my_db_server:3306/myschema" />
>> <property name="javax.persistence.jdbc.user" value="user" />
>> <property name="javax.persistence.jdbc.password" value="password" />
>> <!-- Configure connection pool. -->
>> <property name="eclipselink.connection-pool.default.initial" value="10" />
>> <property name="eclipselink.connection-pool.default.min" value="16" />
>> <property name="eclipselink.connection-pool.default.max" value="50" />
>>
>>
>> Although I only see one DataSource registered it somehow feels like there is some more stuff going on behind the (EL?) scenes that I don’t have a handle on yet.
>>
>> BTW... I have also created an org.apache.aries.jpa.my.pu.cfg configuration file, but when I leave the DB properties out of the persistence.xml I get bunch of ClassNotFound exceptions, so that is suspicious.
>>
>> BTW2… the examples link at the bottom of this page is broken: https://commons.apache.org/proper/commons-dbcp/
>>
>>
>> Regards,
>>
>> Erwin
>
Reply | Threaded
Open this post in threaded view
|

Re: Can't get MySQL non-jta connection pool ... - SOLVED

Erwin Hogeweg
In reply to this post by Christian Schneider

With a little help from the master (CS) I now have the following config and screaming fast DB with connection pool.

osgi.jdbc.driver.class=com.mysql.jdbc.Driver-pool
url=jdbc:mysql://localhost:3306/mydatabase
dataSourceName=jdbc/mynonjta
user=user
password=password
databaseName=mydatabase

Next stop Transaction Control.

Erwin


You just have to install pax-jdbc-mysql, pax-jdbc-config, pax-jdbc-pool-dbcp2, transaction

Then you create a config like described in the docs and you will get a DataSource with pooling and XA support.

Christian

On 24.07.2016 22:50, Erwin Hogeweg wrote:
Hi,

Not sure if this is a question for these lists or for the EL list but I figure I start here. Feel free to redirect when you feel it doesn’t belong here.

I am trying to get a non-jta connection pool (internal connection pool) working with EL 2.6.2, Aries 2.4.0 (incl. EL adapter), dbcp2-2.1 and mySQL, but I must be missing something because I just can’t get it to work properly. Everything works just fine w/o a connection pool, so this is definitely the source of the misery.

Been struggling with this for a while now, and I am running out of ideas. I think I could use some sample code to point me in the right direction that doesn't use Blueprint? I found some of Christian’s examples, but I don’t think they are using connection pools.

Below a short summary of what I run into.

When I am using the ‘original’ MysqlDataSource...

    private DataSource createMySQLDataSource( Dictionary<String, String> dbConnProps ) {
        MysqlDataSource ds = new MysqlDataSource();
        ds.setUrl( dbConnProps.get( "jdbc_url" ) );
        ds.setUser( dbConnProps.get( "jdbc_user" ) );
        ds.setPassword( dbConnProps.get( "jdbc_password" ) );
        return ds;
    }

… everything kinda works normally. The DataSource, PersistenceProvider and EntityManagerFactory are all created and registered correctly;

g! services javax.sql.DataSource
{javax.sql.DataSource}={eclipselink.target-database=MySQL, osgi.jndi.service.name=jdbc/mynonjta, service.id=139, service.bundleid=104, service.scope=singleton}
  "Registered by bundle:" com.my.project.persistence.mysqldatasource_4.0.0.SNAPSHOT [104]

g! services javax.persistence.EntityManagerFactory
{javax.persistence.EntityManagerFactory}={osgi.unit.version=4.0.0.SNAPSHOT, osgi.unit.name=my.pu, osgi.unit.provider=org.eclipse.persistence.jpa.PersistenceProvider, service.id=142, service.bundleid=98, service.scope=singleton}
  "Registered by bundle:" com.my.project.model_4.0.0.SNAPSHOT [98]

The performance is horrible though as I don’t really seem to get a connection pool. The connection is closed after every query. On top of that I loose all network connections every few seconds with a:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Which has me puzzled for a while now.

So my next attempt was to use the org.apache.commons.dbcp2.BasicDataSource:

   private DataSource createMySQLDataSource(  Dictionary<String, String> dbConnProps ) {

    BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
...
        return basicDataSource;
    }

This fails because the following exception:

[EL Severe]: 2016-07-24 14:41:55.872--java.lang.UnsupportedOperationException: Not supported by BasicDataSource
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1552)

Which is this method:

    @Override
    public Connection getConnection(String user, String pass) throws SQLException {
        // This method isn't supported by the PoolingDataSource returned by
        // the createDataSource
        throw new UnsupportedOperationException("Not supported by BasicDataSource");
    }

So I figured I create a version with a PoolingDataSource  (following the PoolingDataSourceExample in svn): 

        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(dbConnProps.get( "jdbc_url" ), "user", "password");
        PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);
    ObjectPool<PoolableConnection> connectionPool = new GenericObjectPool<>(poolableConnectionFactory);
    poolableConnectionFactory.setPool(connectionPool);
        PoolingDataSource<PoolableConnection> dataSource = new PoolingDataSource<>(connectionPool);
        return dataSource;

But that still gives me an exception:

[EL Severe]: 2016-07-24 16:40:30.392--java.lang.UnsupportedOperationException
at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:156)

So I am kinda lost now.

This is the relevant stuff from the persistence.xml file:

<non-jta-data-source>osgi:service/javax.sql.DataSource/(osgi.jndi.service.name=jdbc/mynonjta)</non-jta-data-source>

<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://my_db_server:3306/myschema" /> 
<property name="javax.persistence.jdbc.user" value="user" /> 
<property name="javax.persistence.jdbc.password" value="password" />
<!-- Configure connection pool. -->
<property name="eclipselink.connection-pool.default.initial" value="10" /> 
<property name="eclipselink.connection-pool.default.min" value="16" /> 
<property name="eclipselink.connection-pool.default.max" value="50" /> 


Although I only see one DataSource registered it somehow feels like there is some more stuff going on behind the (EL?) scenes that I don’t have a handle on yet.

BTW... I have also created an org.apache.aries.jpa.my.pu.cfg configuration file, but when I leave the DB properties out of the persistence.xml I get bunch of ClassNotFound exceptions, so that is suspicious.

BTW2… the examples link at the bottom of this page is broken: https://commons.apache.org/proper/commons-dbcp/


Regards,

Erwin



-- 
Christian Schneider
http://www.liquid-reality.de

Open Source Architect
http://www.talend.com