You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Chris Wolf <cw...@gmail.com> on 2013/01/31 17:51:22 UTC
Running out of JDBC connections with openjpa-2.1.0 + Oracle-11g
If I process a small number of records, everything works, however when
I try to process a "real-world" number of records, I get an
"ORA-12519". At first, I thought it was an Oracle issue and after
searching around and getting hits on "solutions" involving increasing
Oracle sessions and processes (there are at least 125 configured), I
was still getting "ORA-12519". I then tailed the TNS listener log and
saw that everytime my OpenJPA process ran, it would consume all the
JDBC connections as if it was using connection pooling with some high
min-connections setting.
In fact, as the stack trace shows, it's only using
"SimpleDriveDataSource", which I thought didn't pool connections and I
don't have the DBCP jar on my classpath, so why is OpenJPA opening all
these JDBC connections?
Thanks,
Chris
183 marketdata INFO [main] openjpa.Runtime - Starting OpenJPA 2.2.1
214 marketdata INFO [main] openjpa.jdbc.JDBC - Using dictionary
class "org.apache.openjpa.jdbc.sql.OracleDictionary".
Exception in thread "main" <openjpa-2.2.1-r422266:1396819 fatal store
error> org.apache.openjpa.persistence.RollbackException: Listener
refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found
at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:594)
at ms.algo.adapt.test.BeanIODemo.saveToDB(BeanIODemo.java:153)
at ms.algo.adapt.test.BeanIODemo.beanIOTest(BeanIODemo.java:127)
at ms.algo.adapt.test.BeanIODemo.main(BeanIODemo.java:50)
Caused by: <openjpa-2.2.1-r422266:1396819 fatal general error>
org.apache.openjpa.persistence.PersistenceException: Listener refused
the connection with the following error:
ORA-12519, TNS:no appropriate service handler found
at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4958)
at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4918)
at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:971)
at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getConnection(JDBCStoreManager.java:240)
at org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.getConnection(AbstractJDBCSeq.java:163)
at org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:217)
at org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:201)
at org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
at org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
at org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:778)
at org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493)
at org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469)
at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:762)
at org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135)
at org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:600)
at org.apache.openjpa.kernel.SingleFieldManager.preFlushPC(SingleFieldManager.java:803)
at org.apache.openjpa.kernel.SingleFieldManager.preFlushPCs(SingleFieldManager.java:762)
at org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:664)
at org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:589)
at org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:505)
at org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:3028)
at org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:44)
at org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1042)
at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2114)
at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2074)
at org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1992)
at org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
at org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1516)
at org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:933)
at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:570)
... 3 more
Caused by: java.sql.SQLException: Listener refused the connection with
the following error:
ORA-12519, TNS:no appropriate service handler found
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:517)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:557)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:233)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:29)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:556)
at org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getSimpleConnection(SimpleDriverDataSource.java:84)
at org.apache.openjpa.jdbc.schema.AutoDriverDataSource.getConnection(AutoDriverDataSource.java:39)
at org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:76)
at org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
at org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:93)
at org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
at org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:304)
at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:982)
at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:967)
... 31 more
Caused by: oracle.net.ns.NetException: Listener refused the connection
with the following error:
ORA-12519, TNS:no appropriate service handler found
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:457)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1625)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:365)
... 44 more
Re: Running out of JDBC connections with openjpa-2.1.0 + Oracle-11g
Posted by Chris Wolf <cw...@gmail.com>.
Kevin,
Sorry I missed your reply. Let me try to get something together.
-Chris
On Mon, Feb 4, 2013 at 5:47 PM, Kevin Sutter <kw...@gmail.com> wrote:
> Correct, James. OpenJPA does use non-transactional datasources for
> accessing the database during id generation processing (if it's
> necessary). If a <non-jta-data-source> is defined, that will be used.
> Otherwise, the openjpa.Connection2* properties will be used to access via
> an additional connection.
>
> Back to Chris' issue... I'm glad to hear that the use of DBCP is resolving
> your issue -- or, at least, is providing a suitable workaround. And, as I
> mentioned earlier, the use of a connection pooling mechanism is good
> overall.
>
> OpenJPA does use DBCP by default. Maybe we are missing a scenario with id
> generation. If you could describe the specific scenario (better yet with a
> junit test case), then maybe we can reproduce the issue and get it resolved.
>
> Thanks,
> Kevin
>
> On Fri, Feb 1, 2013 at 11:13 AM, James Carman
> <jc...@carmanconsulting.com>wrote:
>
>> Are these the non-transactional connections that OpenJPA is using to find
>> the id values for the entities? I think that's how it works.
>>
>> On Feb 1, 2013, at 12:05 PM, Chris Wolf <cw...@gmail.com> wrote:
>>
>> > Kevin,
>> >
>> > I finally got past the issue of running out of connections - I simply
>> > configured connections pooling via DBCP and the problem went away. I
>> > still consider this a "work-around" because if I have a single
>> > process/thread persisting entities, I would expect each step of the
>> > way to complete in a synchronous fashion and be able to use just one
>> > connection to do that.
>> >
>> > Before I tried configuring connection pooling, I was trying to isolate
>> > the code in OpenJPA that was using up all the connections and it
>> > appears to be occurring in the OpenJPA sequence generator code, in or
>> > about:
>> >
>> > org.apache.openjpa.kernel.StateManagerImpl.assignObjectId()
>> > org.apache.openjpa.kernel.JDBCStoreManager.assignObjectId()
>> > org.apache.openjpa.kernel.AbstractJDBCSeq.next()
>> > org.apache.openjpa.kernel.NativeJDBCSeq.nextInternal()
>> >
>> > My entity's annotations that invoke this are:
>> >
>> > @Id
>> > @SequenceGenerator(name="MARKET_DATA_MARKETDATAID_GENERATOR",
>> > sequenceName="MARKET_DATA_ID_SEQ")
>> > @GeneratedValue(strategy=GenerationType.SEQUENCE,
>> > generator="MARKET_DATA_MARKETDATAID_GENERATOR")
>> > @Column(name="MARKET_DATA_ID", unique=true, nullable=false,
>> precision=19)
>> > public long getMarketDataId() {
>> > return this.marketDataId;
>> > }
>> >
>> >
>> > For now, I will just hope connection pooling will be sufficient to
>> > solve this issue, but I am wondering if your unit tests are running
>> > with pooling or not? If they are, you may be missing a problem.
>> >
>> > Thanks,
>> >
>> > Chris
>> >
>> >
>> >
>> > On Thu, Jan 31, 2013 at 5:59 PM, Chris Wolf <cw...@gmail.com>
>> wrote:
>> >> Kevin,
>> >>
>> >> Thanks again, I'll look into the logging thing later - you're right
>> >> that I'm more focused on just getting persistence to work and so I'm
>> >> using the debugger. For now, it's just a JSE app, so no JTA yet (but
>> >> that's what's targeted).
>> >>
>> >> So in addition to the programmatic transaction management via Spring
>> >> JpaTransactionManager, ( which
>> >> suppressed the out-of-connections issue but silently didn't write to
>> >> the DB), I tried a third approach -
>> >> Spring-managed transactions via Spring's TransactionTemplate and
>> >> callbacks, but that only got me back to where
>> >> I started, which is the connections running out again, plus hideous
>> >> code, as you can see, below.
>> >>
>> >>
>> >> Ok, I'll keep hammering away at it. Thanks,
>> >>
>> >> -Chris
>> >>
>> >>
>> >> static void saveToDB(final List<MdBaseData> data) throws Exception {
>> >> EntityManagerFactory emf = Persistence
>> >>
>> .createEntityManagerFactory("marketdata");
>> >>
>> >> final JpaTemplate jpaTempl = new JpaTemplate(emf);
>> >> jpaTempl.afterPropertiesSet();
>> >>
>> >> JpaTransactionManager jpaTxMgr = new
>> JpaTransactionManager(emf);
>> >> jpaTxMgr.afterPropertiesSet();
>> >>
>> >> final TransactionTemplate txTmpl = new
>> TransactionTemplate(jpaTxMgr);
>> >> txTmpl.afterPropertiesSet();
>> >>
>> >> TransactionStrategy txStrategy = new
>> TransactionStrategy() {
>> >> @SuppressWarnings("deprecation")
>> >> public Object execute(final JpaCallback<?>
>> callback) {
>> >> return txTmpl.execute(new
>> TransactionCallback<Object>() {
>> >> public Object
>> doInTransaction(TransactionStatus status) {
>> >> return
>> jpaTempl.execute(new JpaCallback<Object>() {
>> >> public Object
>> doInJpa(EntityManager entityManager)
>> >>
>> throws PersistenceException {
>> >> return
>> callback.doInJpa(entityManager);
>> >> }
>> >> });
>> >> }
>> >> });
>> >> }
>> >> };
>> >>
>> >> txStrategy.execute(new JpaCallback<Object>() {
>> >> @Override
>> >> public Object doInJpa(EntityManager em) throws
>> PersistenceException {
>> >> for (MdBaseData bd : data) {
>> >> em.persist(bd);
>> >> }
>> >> return null; // writing to db, not
>> reading...
>> >> }
>> >> });
>> >> }
>> >>
>> >> On Thu, Jan 31, 2013 at 5:19 PM, Kevin Sutter <kw...@gmail.com>
>> wrote:
>> >>>> Not certain what you meant by "join EM to the transaction".
>> >>> If an EM is created without knowledge of a containing JTA transaction,
>> then
>> >>> the EM and the JTA transaction won't automatically be linked. Thus,
>> if you
>> >>> start the transaction after the EM was created, you can use the
>> >>> EM.joinTransaction() method to get the two hooked up. This is, of
>> course,
>> >>> assuming that the Spring-initiated transaction is a JTA transaction.
>> If
>> >>> it's not, then you'll have to reference the Spring documentation to
>> figure
>> >>> out how the EM and transaction are linked.
>> >>>
>> >>>> BTW, I don't know if you noticed my previous post about logging, but I
>> >>> tried setting openjpa.Log = slf4j
>> >>> Yes, I did notice. But, since you seemed to have been making
>> progress, I
>> >>> figured you had figured something out... I'm not a slf4j expert, but
>> I do
>> >>> know other users have had success with integrating openjpa with slf4j.
>> I
>> >>> would guess it's an issue with the properties you are setting and how
>> they
>> >>> get mapped to OpenJPA's properties. If you are setting properties like
>> >>> this:
>> >>>
>> >>> log4j.category.openjpa.jdbc.JDBC=TRACE
>> >>>
>> >>> .. OpenJPA wouldn't know what to do with this. I would try setting the
>> >>> properties for OpenJPA's logging in the same format for the
>> persistence.xml:
>> >>>
>> >>> <property name="openjpa.Log" value="openjpa.jdbc.JDBC=TRACE"/>
>> >>>
>> >>> At least that's what I would expect. Somehow the configuration would
>> still
>> >>> have to map to properties that OpenJPA knows about. Otherwise, our
>> tracing
>> >>> code wouldn't know when or what to trace. I know there's some mapping
>> code
>> >>> like this for Log4J and WebSphere and maybe others. But, the
>> combination
>> >>> of slf4j and log4j might be throwing things off... Just an idea.
>> >>>
>> >>> Kevin
>> >>>
>> >>>
>> >>> On Thu, Jan 31, 2013 at 3:43 PM, Chris Wolf <cw...@gmail.com>
>> wrote:
>> >>>
>> >>>> On Thu, Jan 31, 2013 at 4:30 PM, Kevin Sutter <kw...@gmail.com>
>> wrote:
>> >>>>> So, which do you want? Lots of connections, or data getting to the
>> >>>>> database? :-)
>> >>>>
>> >>>> Preferably the later. ;)
>> >>>>
>> >>>>>
>> >>>>> Try starting the spring transaction before creating the EM. Either
>> that,
>> >>>>> or join the EM to the transaction.
>> >>>>
>> >>>> I tried the former - it didn't change the outcome. Not certain what
>> >>>> you meant by "join EM to the transaction".
>> >>>>
>> >>>>
>> >>>>
>> >>>> BTW, I don't know if you noticed my previous post about logging, but I
>> >>>> tried setting openjpa.Log = slf4j
>> >>>> and that didn't work - no errors, just no addtional output.
>> >>>>
>> >>>> Thanks for helping so far...
>> >>>>
>> >>>>>
>> >>>>> Kevin
>> >>>>>
>> >>>>> On Thu, Jan 31, 2013 at 3:26 PM, Chris Wolf <cw...@gmail.com>
>> >>>> wrote:
>> >>>>>
>> >>>>>> Kevin,
>> >>>>>>
>> >>>>>> Someone on the users@apache.camel.org mailing list suggested that I
>> >>>>>> let Springframework manage the transactions, so I changed my code to
>> >>>>>> look like:
>> >>>>>>
>> >>>>>> The apparent connection "leak" went away, and no exceptions are
>> >>>>>> thrown, and I only see one connection being opened, as expected -
>> >>>>>> however, nothing goes into the database.
>> >>>>>>
>> >>>>>> static void saveToDB(List<MdBaseData> data) throws Exception {
>> >>>>>> EntityManagerFactory emf = Persistence
>> >>>>>>
>> >>>> .createEntityManagerFactory("marketdata");
>> >>>>>> JpaTransactionManager jpaTxMgr = new
>> >>>>>> JpaTransactionManager(emf);
>> >>>>>>
>> >>>>>>
>> >>>>>> EntityManager em =
>> >>>>>>
>> >>>>>> jpaTxMgr.getEntityManagerFactory().createEntityManager();
>> >>>>>> TransactionStatus txStatus =
>> >>>>>> jpaTxMgr.getTransaction(new
>> >>>>>> DefaultTransactionDefinition());
>> >>>>>> try {
>> >>>>>> //em.getTransaction().begin();
>> >>>>>> for (MdBaseData bd : data) {
>> >>>>>> em.persist(bd);
>> >>>>>> }
>> >>>>>> //em.getTransaction().commit();
>> >>>>>> jpaTxMgr.commit(txStatus);
>> >>>>>> } catch (Exception e) {
>> >>>>>> jpaTxMgr.rollback(txStatus);
>> >>>>>> e.printStackTrace();
>> >>>>>> } finally {
>> >>>>>> em.close();
>> >>>>>> }
>> >>>>>> }
>> >>>>>>
>> >>>>>> On Thu, Jan 31, 2013 at 3:49 PM, Chris Wolf <cw...@gmail.com>
>> >>>> wrote:
>> >>>>>>> Even more specifically, I drilled down into the guts of
>> OpenJPA-2.1,
>> >>>>>>> into the call to
>> >>>>>>> EntityManager.getTransaction().begin();
>> >>>>>>>
>> >>>>>>> The place were all the connections are opened until exhausted is
>> in:
>> >>>>>>>
>> >>>>>>> org.apache.openjpa.kernel.BrokerImpl.flush(int reason)
>> >>>>>>>
>> >>>>>>> There is a loop where the connections are all being opened:
>> >>>>>>>
>> >>>>>>> Collection mobjs = null;
>> >>>>>>> _flags |= FLAG_PRESTORING;
>> >>>>>>> try {
>> >>>>>>> if (flush) {
>> >>>>>>> // call pre store on all currently transactional
>> objs
>> >>>>>>> for (Iterator itr = transactional.iterator();
>> >>>>>>> itr.hasNext();) //<=== EATING UP ALL CONNECTIONS
>> >>>>>>> ((StateManagerImpl)
>> >>>> itr.next()).beforeFlush(reason,
>> >>>>>> _call);
>> >>>>>>>
>> >>>>>>>
>> >>>>>>> Let me know if there's anything else I can to do help solve this
>> >>>> issue.
>> >>>>>>>
>> >>>>>>> Thanks,
>> >>>>>>>
>> >>>>>>> -Chris
>> >>>>>>>
>> >>>>>>> On Thu, Jan 31, 2013 at 3:18 PM, Chris Wolf <cw...@gmail.com>
>> >>>>>> wrote:
>> >>>>>>>> Kevin,
>> >>>>>>>>
>> >>>>>>>> I isolated where all the connections are opened at once - upon
>> >>>> calling
>> >>>>>>>> entityManager.getTransaction().commit();
>> >>>>>>>>
>> >>>>>>>> I assume this is correct from all the exampled I've seen so far...
>> >>>>>>>>
>> >>>>>>>> EntityManager entityManager =
>> >>>>>>>> entityManagerFactory.createEntityManager(); // here is where just
>> one
>> >>>>>>>> connection is opened, as expected. (not using connection pool
>> yet)
>> >>>>>>>> entityManager.getTransaction().begin();
>> >>>>>>>> for (MdBaseData bd : data) {
>> >>>>>>>> em.persist(bd);
>> >>>>>>>> }
>> >>>>>>>> entityManager.getTransaction().commit(); // here is where all the
>> >>>>>>>> connections are opened *****
>> >>>>>>>> entityManager.close();
>> >>>>>>>>
>> >>>>>>>> Note that the entity has a M2M to child entities (about 100 per
>> >>>>>>>> MdBaseData) via a link table.
>> >>>>>>>>
>> >>>>>>>> Thanks for any ideas,
>> >>>>>>>>
>> >>>>>>>> -Chris
>> >>>>>>>>
>> >>>>>>>> On Thu, Jan 31, 2013 at 1:56 PM, Kevin Sutter <kwsutter@gmail.com
>> >
>> >>>>>> wrote:
>> >>>>>>>>> Hi Chris,
>> >>>>>>>>> Good to know your plans. Thanks.
>> >>>>>>>>>
>> >>>>>>>>> You can get almost all of the JDBC connection access via the JDBC
>> >>>>>> channel
>> >>>>>>>>> in our logging framework [1]. Unfortunately, I looked at the
>> code
>> >>>> and
>> >>>>>> the
>> >>>>>>>>> constructors don't seem to have a log entry... :-( But, you do
>> get
>> >>>>>> all of
>> >>>>>>>>> the closes, commits, rollbacks, etc -- all of the normal
>> operations
>> >>>>>> through
>> >>>>>>>>> a Connection object. So, I'd start with that.
>> >>>>>>>>>
>> >>>>>>>>> Updating the Ctor for additional logging would be very easy to
>> do.
>> >>>> I
>> >>>>>> would
>> >>>>>>>>> probably do it in the LoggingConnectionDecorator, like you
>> thought.
>> >>>>>> If you
>> >>>>>>>>> have issues with building a version of OpenJPA for your testing,
>> >>>> ping
>> >>>>>> me
>> >>>>>>>>> back with the version of OpenJPA that you are using and maybe I
>> can
>> >>>>>> find
>> >>>>>>>>> time to do a quick update. But, try the JDBC log channel first
>> and
>> >>>>>> see if
>> >>>>>>>>> that gives you enough information for your specific scenario.
>> >>>>>>>>>
>> >>>>>>>>> Thanks,
>> >>>>>>>>> Kevin
>> >>>>>>>>>
>> >>>>>>>>> [1]
>> >>>>>>>>>
>> >>>>>>
>> >>>>
>> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_logging_channels
>> >>>>>>>>>
>> >>>>>>>>> On Thu, Jan 31, 2013 at 12:02 PM, Chris Wolf <
>> cwolf.algo@gmail.com>
>> >>>>>> wrote:
>> >>>>>>>>>
>> >>>>>>>>>> I am fully aware that pooling is the way to go, but I want to
>> just
>> >>>>>>>>>> prove out the simple case first. Also the final deployment will
>> >>>> be in
>> >>>>>>>>>> a JEE container, so it will be doing the pooling.
>> >>>>>>>>>>
>> >>>>>>>>>> I really wish there was a logging setting to trace
>> acquire/release
>> >>>> of
>> >>>>>>>>>> JDBC Connections - I looked at the source for (I forget now,
>> >>>> something
>> >>>>>>>>>> like JDBCDatStore) it had logging but not of acquire/release of
>> >>>>>>>>>> Connections.
>> >>>>>>>>>>
>> >>>>>>>>>> What is this LoggingConnectionDecorator? would that help me log
>> >>>>>>>>>> connection activity? If so, how is it configured?
>> >>>>>>>>>>
>> >>>>>>>>>> Thanks,
>> >>>>>>>>>>
>> >>>>>>>>>> -Chris
>> >>>>>>>>>>
>> >>>>>>>>>> On Thu, Jan 31, 2013 at 12:19 PM, Kevin Sutter <
>> kwsutter@gmail.com
>> >>>>>
>> >>>>>> wrote:
>> >>>>>>>>>>> Hi Chris,
>> >>>>>>>>>>> Normally, OpenJPA will only request a connection "on demand"
>> [1].
>> >>>>>> As
>> >>>>>>>>>> each
>> >>>>>>>>>>> database access is requested, a connection is obtained, but
>> then
>> >>>>>> it's
>> >>>>>>>>>>> released when we're done with it. Unless there is some
>> >>>> processing
>> >>>>>> or
>> >>>>>>>>>>> configuration that is telling OpenJPA to hang onto the
>> >>>>>> connection... If
>> >>>>>>>>>>> nothing jumps out at you, I would suggest tracing (maybe both
>> >>>>>> OpenJPA and
>> >>>>>>>>>>> database) to see why all of the connections are getting
>> requested
>> >>>>>> and
>> >>>>>>>>>>> nothing is getting closed.
>> >>>>>>>>>>>
>> >>>>>>>>>>> As an aside, I would highly recommend the use of some type of
>> >>>>>> connection
>> >>>>>>>>>>> pooling. Overall, you will get much better performance if
>> >>>>>> connections
>> >>>>>>>>>> can
>> >>>>>>>>>>> be re-used instead of constantly dropping and re-creating
>> >>>>>> connections.
>> >>>>>>>>>>> Whether you use DBCP or Oracle pooling or some application
>> >>>> server's
>> >>>>>>>>>>> connection pooling mechanism, it doesn't really matter. But, I
>> >>>>>> would
>> >>>>>>>>>>> suggest using some connection pooling.
>> >>>>>>>>>>>
>> >>>>>>>>>>> Good luck,
>> >>>>>>>>>>> Kevin
>> >>>>>>>>>>>
>> >>>>>>>>>>> [1]
>> >>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_dbsetup_retain
>> >>>>>>>>>>>
>> >>>>>>>>>>> On Thu, Jan 31, 2013 at 10:51 AM, Chris Wolf <
>> >>>> cwolf.algo@gmail.com>
>> >>>>>>>>>> wrote:
>> >>>>>>>>>>>
>> >>>>>>>>>>>> If I process a small number of records, everything works,
>> >>>> however
>> >>>>>> when
>> >>>>>>>>>>>> I try to process a "real-world" number of records, I get an
>> >>>>>>>>>>>> "ORA-12519". At first, I thought it was an Oracle issue and
>> >>>> after
>> >>>>>>>>>>>> searching around and getting hits on "solutions" involving
>> >>>>>> increasing
>> >>>>>>>>>>>> Oracle sessions and processes (there are at least 125
>> >>>> configured),
>> >>>>>> I
>> >>>>>>>>>>>> was still getting "ORA-12519". I then tailed the TNS listener
>> >>>> log
>> >>>>>> and
>> >>>>>>>>>>>> saw that everytime my OpenJPA process ran, it would consume
>> all
>> >>>> the
>> >>>>>>>>>>>> JDBC connections as if it was using connection pooling with
>> some
>> >>>>>> high
>> >>>>>>>>>>>> min-connections setting.
>> >>>>>>>>>>>>
>> >>>>>>>>>>>> In fact, as the stack trace shows, it's only using
>> >>>>>>>>>>>> "SimpleDriveDataSource", which I thought didn't pool
>> connections
>> >>>>>> and I
>> >>>>>>>>>>>> don't have the DBCP jar on my classpath, so why is OpenJPA
>> >>>> opening
>> >>>>>> all
>> >>>>>>>>>>>> these JDBC connections?
>> >>>>>>>>>>>>
>> >>>>>>>>>>>>
>> >>>>>>>>>>>> Thanks,
>> >>>>>>>>>>>>
>> >>>>>>>>>>>>
>> >>>>>>>>>>>> Chris
>> >>>>>>>>>>>>
>> >>>>>>>>>>>>
>> >>>>>>>>>>>> 183 marketdata INFO [main] openjpa.Runtime - Starting
>> >>>> OpenJPA
>> >>>>>> 2.2.1
>> >>>>>>>>>>>> 214 marketdata INFO [main] openjpa.jdbc.JDBC - Using
>> >>>> dictionary
>> >>>>>>>>>>>> class "org.apache.openjpa.jdbc.sql.OracleDictionary".
>> >>>>>>>>>>>> Exception in thread "main" <openjpa-2.2.1-r422266:1396819
>> fatal
>> >>>>>> store
>> >>>>>>>>>>>> error> org.apache.openjpa.persistence.RollbackException:
>> >>>> Listener
>> >>>>>>>>>>>> refused the connection with the following error:
>> >>>>>>>>>>>> ORA-12519, TNS:no appropriate service handler found
>> >>>>>>>>>>>>
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:594)
>> >>>>>>>>>>>> at
>> >>>>>> ms.algo.adapt.test.BeanIODemo.saveToDB(BeanIODemo.java:153)
>> >>>>>>>>>>>> at
>> >>>>>> ms.algo.adapt.test.BeanIODemo.beanIOTest(BeanIODemo.java:127)
>> >>>>>>>>>>>> at
>> >>>> ms.algo.adapt.test.BeanIODemo.main(BeanIODemo.java:50)
>> >>>>>>>>>>>> Caused by: <openjpa-2.2.1-r422266:1396819 fatal general error>
>> >>>>>>>>>>>> org.apache.openjpa.persistence.PersistenceException: Listener
>> >>>>>> refused
>> >>>>>>>>>>>> the connection with the following error:
>> >>>>>>>>>>>> ORA-12519, TNS:no appropriate service handler found
>> >>>>>>>>>>>>
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>
>> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4958)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4918)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:971)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getConnection(JDBCStoreManager.java:240)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.getConnection(AbstractJDBCSeq.java:163)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:217)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:201)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>
>> org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:778)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>
>> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>
>> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:762)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:600)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPC(SingleFieldManager.java:803)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPCs(SingleFieldManager.java:762)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:664)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:589)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:505)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:3028)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>> org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:44)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1042)
>> >>>>>>>>>>>> at
>> >>>>>>>>>> org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2114)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>> org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2074)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1992)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>> org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1516)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:933)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:570)
>> >>>>>>>>>>>> ... 3 more
>> >>>>>>>>>>>> Caused by: java.sql.SQLException: Listener refused the
>> >>>> connection
>> >>>>>> with
>> >>>>>>>>>>>> the following error:
>> >>>>>>>>>>>> ORA-12519, TNS:no appropriate service handler found
>> >>>>>>>>>>>>
>> >>>>>>>>>>>> at
>> >>>>>>>>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:517)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:557)
>> >>>>>>>>>>>> at
>> >>>>>>>>>> oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:233)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:29)
>> >>>>>>>>>>>> at
>> >>>>>>>>>> oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:556)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getSimpleConnection(SimpleDriverDataSource.java:84)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.jdbc.schema.AutoDriverDataSource.getConnection(AutoDriverDataSource.java:39)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:76)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:93)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:304)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:982)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:967)
>> >>>>>>>>>>>> ... 31 more
>> >>>>>>>>>>>> Caused by: oracle.net.ns.NetException: Listener refused the
>> >>>>>> connection
>> >>>>>>>>>>>> with the following error:
>> >>>>>>>>>>>> ORA-12519, TNS:no appropriate service handler found
>> >>>>>>>>>>>>
>> >>>>>>>>>>>> at
>> oracle.net.ns.NSProtocol.connect(NSProtocol.java:457)
>> >>>>>>>>>>>> at
>> >>>>>>>>>>>>
>> >>>> oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1625)
>> >>>>>>>>>>>> at
>> >>>>>>>>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:365)
>> >>>>>>>>>>>> ... 44 more
>> >>>>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>
>> >>>>
>>
>>
Re: Running out of JDBC connections with openjpa-2.1.0 + Oracle-11g
Posted by Kevin Sutter <kw...@gmail.com>.
Correct, James. OpenJPA does use non-transactional datasources for
accessing the database during id generation processing (if it's
necessary). If a <non-jta-data-source> is defined, that will be used.
Otherwise, the openjpa.Connection2* properties will be used to access via
an additional connection.
Back to Chris' issue... I'm glad to hear that the use of DBCP is resolving
your issue -- or, at least, is providing a suitable workaround. And, as I
mentioned earlier, the use of a connection pooling mechanism is good
overall.
OpenJPA does use DBCP by default. Maybe we are missing a scenario with id
generation. If you could describe the specific scenario (better yet with a
junit test case), then maybe we can reproduce the issue and get it resolved.
Thanks,
Kevin
On Fri, Feb 1, 2013 at 11:13 AM, James Carman
<jc...@carmanconsulting.com>wrote:
> Are these the non-transactional connections that OpenJPA is using to find
> the id values for the entities? I think that's how it works.
>
> On Feb 1, 2013, at 12:05 PM, Chris Wolf <cw...@gmail.com> wrote:
>
> > Kevin,
> >
> > I finally got past the issue of running out of connections - I simply
> > configured connections pooling via DBCP and the problem went away. I
> > still consider this a "work-around" because if I have a single
> > process/thread persisting entities, I would expect each step of the
> > way to complete in a synchronous fashion and be able to use just one
> > connection to do that.
> >
> > Before I tried configuring connection pooling, I was trying to isolate
> > the code in OpenJPA that was using up all the connections and it
> > appears to be occurring in the OpenJPA sequence generator code, in or
> > about:
> >
> > org.apache.openjpa.kernel.StateManagerImpl.assignObjectId()
> > org.apache.openjpa.kernel.JDBCStoreManager.assignObjectId()
> > org.apache.openjpa.kernel.AbstractJDBCSeq.next()
> > org.apache.openjpa.kernel.NativeJDBCSeq.nextInternal()
> >
> > My entity's annotations that invoke this are:
> >
> > @Id
> > @SequenceGenerator(name="MARKET_DATA_MARKETDATAID_GENERATOR",
> > sequenceName="MARKET_DATA_ID_SEQ")
> > @GeneratedValue(strategy=GenerationType.SEQUENCE,
> > generator="MARKET_DATA_MARKETDATAID_GENERATOR")
> > @Column(name="MARKET_DATA_ID", unique=true, nullable=false,
> precision=19)
> > public long getMarketDataId() {
> > return this.marketDataId;
> > }
> >
> >
> > For now, I will just hope connection pooling will be sufficient to
> > solve this issue, but I am wondering if your unit tests are running
> > with pooling or not? If they are, you may be missing a problem.
> >
> > Thanks,
> >
> > Chris
> >
> >
> >
> > On Thu, Jan 31, 2013 at 5:59 PM, Chris Wolf <cw...@gmail.com>
> wrote:
> >> Kevin,
> >>
> >> Thanks again, I'll look into the logging thing later - you're right
> >> that I'm more focused on just getting persistence to work and so I'm
> >> using the debugger. For now, it's just a JSE app, so no JTA yet (but
> >> that's what's targeted).
> >>
> >> So in addition to the programmatic transaction management via Spring
> >> JpaTransactionManager, ( which
> >> suppressed the out-of-connections issue but silently didn't write to
> >> the DB), I tried a third approach -
> >> Spring-managed transactions via Spring's TransactionTemplate and
> >> callbacks, but that only got me back to where
> >> I started, which is the connections running out again, plus hideous
> >> code, as you can see, below.
> >>
> >>
> >> Ok, I'll keep hammering away at it. Thanks,
> >>
> >> -Chris
> >>
> >>
> >> static void saveToDB(final List<MdBaseData> data) throws Exception {
> >> EntityManagerFactory emf = Persistence
> >>
> .createEntityManagerFactory("marketdata");
> >>
> >> final JpaTemplate jpaTempl = new JpaTemplate(emf);
> >> jpaTempl.afterPropertiesSet();
> >>
> >> JpaTransactionManager jpaTxMgr = new
> JpaTransactionManager(emf);
> >> jpaTxMgr.afterPropertiesSet();
> >>
> >> final TransactionTemplate txTmpl = new
> TransactionTemplate(jpaTxMgr);
> >> txTmpl.afterPropertiesSet();
> >>
> >> TransactionStrategy txStrategy = new
> TransactionStrategy() {
> >> @SuppressWarnings("deprecation")
> >> public Object execute(final JpaCallback<?>
> callback) {
> >> return txTmpl.execute(new
> TransactionCallback<Object>() {
> >> public Object
> doInTransaction(TransactionStatus status) {
> >> return
> jpaTempl.execute(new JpaCallback<Object>() {
> >> public Object
> doInJpa(EntityManager entityManager)
> >>
> throws PersistenceException {
> >> return
> callback.doInJpa(entityManager);
> >> }
> >> });
> >> }
> >> });
> >> }
> >> };
> >>
> >> txStrategy.execute(new JpaCallback<Object>() {
> >> @Override
> >> public Object doInJpa(EntityManager em) throws
> PersistenceException {
> >> for (MdBaseData bd : data) {
> >> em.persist(bd);
> >> }
> >> return null; // writing to db, not
> reading...
> >> }
> >> });
> >> }
> >>
> >> On Thu, Jan 31, 2013 at 5:19 PM, Kevin Sutter <kw...@gmail.com>
> wrote:
> >>>> Not certain what you meant by "join EM to the transaction".
> >>> If an EM is created without knowledge of a containing JTA transaction,
> then
> >>> the EM and the JTA transaction won't automatically be linked. Thus,
> if you
> >>> start the transaction after the EM was created, you can use the
> >>> EM.joinTransaction() method to get the two hooked up. This is, of
> course,
> >>> assuming that the Spring-initiated transaction is a JTA transaction.
> If
> >>> it's not, then you'll have to reference the Spring documentation to
> figure
> >>> out how the EM and transaction are linked.
> >>>
> >>>> BTW, I don't know if you noticed my previous post about logging, but I
> >>> tried setting openjpa.Log = slf4j
> >>> Yes, I did notice. But, since you seemed to have been making
> progress, I
> >>> figured you had figured something out... I'm not a slf4j expert, but
> I do
> >>> know other users have had success with integrating openjpa with slf4j.
> I
> >>> would guess it's an issue with the properties you are setting and how
> they
> >>> get mapped to OpenJPA's properties. If you are setting properties like
> >>> this:
> >>>
> >>> log4j.category.openjpa.jdbc.JDBC=TRACE
> >>>
> >>> .. OpenJPA wouldn't know what to do with this. I would try setting the
> >>> properties for OpenJPA's logging in the same format for the
> persistence.xml:
> >>>
> >>> <property name="openjpa.Log" value="openjpa.jdbc.JDBC=TRACE"/>
> >>>
> >>> At least that's what I would expect. Somehow the configuration would
> still
> >>> have to map to properties that OpenJPA knows about. Otherwise, our
> tracing
> >>> code wouldn't know when or what to trace. I know there's some mapping
> code
> >>> like this for Log4J and WebSphere and maybe others. But, the
> combination
> >>> of slf4j and log4j might be throwing things off... Just an idea.
> >>>
> >>> Kevin
> >>>
> >>>
> >>> On Thu, Jan 31, 2013 at 3:43 PM, Chris Wolf <cw...@gmail.com>
> wrote:
> >>>
> >>>> On Thu, Jan 31, 2013 at 4:30 PM, Kevin Sutter <kw...@gmail.com>
> wrote:
> >>>>> So, which do you want? Lots of connections, or data getting to the
> >>>>> database? :-)
> >>>>
> >>>> Preferably the later. ;)
> >>>>
> >>>>>
> >>>>> Try starting the spring transaction before creating the EM. Either
> that,
> >>>>> or join the EM to the transaction.
> >>>>
> >>>> I tried the former - it didn't change the outcome. Not certain what
> >>>> you meant by "join EM to the transaction".
> >>>>
> >>>>
> >>>>
> >>>> BTW, I don't know if you noticed my previous post about logging, but I
> >>>> tried setting openjpa.Log = slf4j
> >>>> and that didn't work - no errors, just no addtional output.
> >>>>
> >>>> Thanks for helping so far...
> >>>>
> >>>>>
> >>>>> Kevin
> >>>>>
> >>>>> On Thu, Jan 31, 2013 at 3:26 PM, Chris Wolf <cw...@gmail.com>
> >>>> wrote:
> >>>>>
> >>>>>> Kevin,
> >>>>>>
> >>>>>> Someone on the users@apache.camel.org mailing list suggested that I
> >>>>>> let Springframework manage the transactions, so I changed my code to
> >>>>>> look like:
> >>>>>>
> >>>>>> The apparent connection "leak" went away, and no exceptions are
> >>>>>> thrown, and I only see one connection being opened, as expected -
> >>>>>> however, nothing goes into the database.
> >>>>>>
> >>>>>> static void saveToDB(List<MdBaseData> data) throws Exception {
> >>>>>> EntityManagerFactory emf = Persistence
> >>>>>>
> >>>> .createEntityManagerFactory("marketdata");
> >>>>>> JpaTransactionManager jpaTxMgr = new
> >>>>>> JpaTransactionManager(emf);
> >>>>>>
> >>>>>>
> >>>>>> EntityManager em =
> >>>>>>
> >>>>>> jpaTxMgr.getEntityManagerFactory().createEntityManager();
> >>>>>> TransactionStatus txStatus =
> >>>>>> jpaTxMgr.getTransaction(new
> >>>>>> DefaultTransactionDefinition());
> >>>>>> try {
> >>>>>> //em.getTransaction().begin();
> >>>>>> for (MdBaseData bd : data) {
> >>>>>> em.persist(bd);
> >>>>>> }
> >>>>>> //em.getTransaction().commit();
> >>>>>> jpaTxMgr.commit(txStatus);
> >>>>>> } catch (Exception e) {
> >>>>>> jpaTxMgr.rollback(txStatus);
> >>>>>> e.printStackTrace();
> >>>>>> } finally {
> >>>>>> em.close();
> >>>>>> }
> >>>>>> }
> >>>>>>
> >>>>>> On Thu, Jan 31, 2013 at 3:49 PM, Chris Wolf <cw...@gmail.com>
> >>>> wrote:
> >>>>>>> Even more specifically, I drilled down into the guts of
> OpenJPA-2.1,
> >>>>>>> into the call to
> >>>>>>> EntityManager.getTransaction().begin();
> >>>>>>>
> >>>>>>> The place were all the connections are opened until exhausted is
> in:
> >>>>>>>
> >>>>>>> org.apache.openjpa.kernel.BrokerImpl.flush(int reason)
> >>>>>>>
> >>>>>>> There is a loop where the connections are all being opened:
> >>>>>>>
> >>>>>>> Collection mobjs = null;
> >>>>>>> _flags |= FLAG_PRESTORING;
> >>>>>>> try {
> >>>>>>> if (flush) {
> >>>>>>> // call pre store on all currently transactional
> objs
> >>>>>>> for (Iterator itr = transactional.iterator();
> >>>>>>> itr.hasNext();) //<=== EATING UP ALL CONNECTIONS
> >>>>>>> ((StateManagerImpl)
> >>>> itr.next()).beforeFlush(reason,
> >>>>>> _call);
> >>>>>>>
> >>>>>>>
> >>>>>>> Let me know if there's anything else I can to do help solve this
> >>>> issue.
> >>>>>>>
> >>>>>>> Thanks,
> >>>>>>>
> >>>>>>> -Chris
> >>>>>>>
> >>>>>>> On Thu, Jan 31, 2013 at 3:18 PM, Chris Wolf <cw...@gmail.com>
> >>>>>> wrote:
> >>>>>>>> Kevin,
> >>>>>>>>
> >>>>>>>> I isolated where all the connections are opened at once - upon
> >>>> calling
> >>>>>>>> entityManager.getTransaction().commit();
> >>>>>>>>
> >>>>>>>> I assume this is correct from all the exampled I've seen so far...
> >>>>>>>>
> >>>>>>>> EntityManager entityManager =
> >>>>>>>> entityManagerFactory.createEntityManager(); // here is where just
> one
> >>>>>>>> connection is opened, as expected. (not using connection pool
> yet)
> >>>>>>>> entityManager.getTransaction().begin();
> >>>>>>>> for (MdBaseData bd : data) {
> >>>>>>>> em.persist(bd);
> >>>>>>>> }
> >>>>>>>> entityManager.getTransaction().commit(); // here is where all the
> >>>>>>>> connections are opened *****
> >>>>>>>> entityManager.close();
> >>>>>>>>
> >>>>>>>> Note that the entity has a M2M to child entities (about 100 per
> >>>>>>>> MdBaseData) via a link table.
> >>>>>>>>
> >>>>>>>> Thanks for any ideas,
> >>>>>>>>
> >>>>>>>> -Chris
> >>>>>>>>
> >>>>>>>> On Thu, Jan 31, 2013 at 1:56 PM, Kevin Sutter <kwsutter@gmail.com
> >
> >>>>>> wrote:
> >>>>>>>>> Hi Chris,
> >>>>>>>>> Good to know your plans. Thanks.
> >>>>>>>>>
> >>>>>>>>> You can get almost all of the JDBC connection access via the JDBC
> >>>>>> channel
> >>>>>>>>> in our logging framework [1]. Unfortunately, I looked at the
> code
> >>>> and
> >>>>>> the
> >>>>>>>>> constructors don't seem to have a log entry... :-( But, you do
> get
> >>>>>> all of
> >>>>>>>>> the closes, commits, rollbacks, etc -- all of the normal
> operations
> >>>>>> through
> >>>>>>>>> a Connection object. So, I'd start with that.
> >>>>>>>>>
> >>>>>>>>> Updating the Ctor for additional logging would be very easy to
> do.
> >>>> I
> >>>>>> would
> >>>>>>>>> probably do it in the LoggingConnectionDecorator, like you
> thought.
> >>>>>> If you
> >>>>>>>>> have issues with building a version of OpenJPA for your testing,
> >>>> ping
> >>>>>> me
> >>>>>>>>> back with the version of OpenJPA that you are using and maybe I
> can
> >>>>>> find
> >>>>>>>>> time to do a quick update. But, try the JDBC log channel first
> and
> >>>>>> see if
> >>>>>>>>> that gives you enough information for your specific scenario.
> >>>>>>>>>
> >>>>>>>>> Thanks,
> >>>>>>>>> Kevin
> >>>>>>>>>
> >>>>>>>>> [1]
> >>>>>>>>>
> >>>>>>
> >>>>
> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_logging_channels
> >>>>>>>>>
> >>>>>>>>> On Thu, Jan 31, 2013 at 12:02 PM, Chris Wolf <
> cwolf.algo@gmail.com>
> >>>>>> wrote:
> >>>>>>>>>
> >>>>>>>>>> I am fully aware that pooling is the way to go, but I want to
> just
> >>>>>>>>>> prove out the simple case first. Also the final deployment will
> >>>> be in
> >>>>>>>>>> a JEE container, so it will be doing the pooling.
> >>>>>>>>>>
> >>>>>>>>>> I really wish there was a logging setting to trace
> acquire/release
> >>>> of
> >>>>>>>>>> JDBC Connections - I looked at the source for (I forget now,
> >>>> something
> >>>>>>>>>> like JDBCDatStore) it had logging but not of acquire/release of
> >>>>>>>>>> Connections.
> >>>>>>>>>>
> >>>>>>>>>> What is this LoggingConnectionDecorator? would that help me log
> >>>>>>>>>> connection activity? If so, how is it configured?
> >>>>>>>>>>
> >>>>>>>>>> Thanks,
> >>>>>>>>>>
> >>>>>>>>>> -Chris
> >>>>>>>>>>
> >>>>>>>>>> On Thu, Jan 31, 2013 at 12:19 PM, Kevin Sutter <
> kwsutter@gmail.com
> >>>>>
> >>>>>> wrote:
> >>>>>>>>>>> Hi Chris,
> >>>>>>>>>>> Normally, OpenJPA will only request a connection "on demand"
> [1].
> >>>>>> As
> >>>>>>>>>> each
> >>>>>>>>>>> database access is requested, a connection is obtained, but
> then
> >>>>>> it's
> >>>>>>>>>>> released when we're done with it. Unless there is some
> >>>> processing
> >>>>>> or
> >>>>>>>>>>> configuration that is telling OpenJPA to hang onto the
> >>>>>> connection... If
> >>>>>>>>>>> nothing jumps out at you, I would suggest tracing (maybe both
> >>>>>> OpenJPA and
> >>>>>>>>>>> database) to see why all of the connections are getting
> requested
> >>>>>> and
> >>>>>>>>>>> nothing is getting closed.
> >>>>>>>>>>>
> >>>>>>>>>>> As an aside, I would highly recommend the use of some type of
> >>>>>> connection
> >>>>>>>>>>> pooling. Overall, you will get much better performance if
> >>>>>> connections
> >>>>>>>>>> can
> >>>>>>>>>>> be re-used instead of constantly dropping and re-creating
> >>>>>> connections.
> >>>>>>>>>>> Whether you use DBCP or Oracle pooling or some application
> >>>> server's
> >>>>>>>>>>> connection pooling mechanism, it doesn't really matter. But, I
> >>>>>> would
> >>>>>>>>>>> suggest using some connection pooling.
> >>>>>>>>>>>
> >>>>>>>>>>> Good luck,
> >>>>>>>>>>> Kevin
> >>>>>>>>>>>
> >>>>>>>>>>> [1]
> >>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_dbsetup_retain
> >>>>>>>>>>>
> >>>>>>>>>>> On Thu, Jan 31, 2013 at 10:51 AM, Chris Wolf <
> >>>> cwolf.algo@gmail.com>
> >>>>>>>>>> wrote:
> >>>>>>>>>>>
> >>>>>>>>>>>> If I process a small number of records, everything works,
> >>>> however
> >>>>>> when
> >>>>>>>>>>>> I try to process a "real-world" number of records, I get an
> >>>>>>>>>>>> "ORA-12519". At first, I thought it was an Oracle issue and
> >>>> after
> >>>>>>>>>>>> searching around and getting hits on "solutions" involving
> >>>>>> increasing
> >>>>>>>>>>>> Oracle sessions and processes (there are at least 125
> >>>> configured),
> >>>>>> I
> >>>>>>>>>>>> was still getting "ORA-12519". I then tailed the TNS listener
> >>>> log
> >>>>>> and
> >>>>>>>>>>>> saw that everytime my OpenJPA process ran, it would consume
> all
> >>>> the
> >>>>>>>>>>>> JDBC connections as if it was using connection pooling with
> some
> >>>>>> high
> >>>>>>>>>>>> min-connections setting.
> >>>>>>>>>>>>
> >>>>>>>>>>>> In fact, as the stack trace shows, it's only using
> >>>>>>>>>>>> "SimpleDriveDataSource", which I thought didn't pool
> connections
> >>>>>> and I
> >>>>>>>>>>>> don't have the DBCP jar on my classpath, so why is OpenJPA
> >>>> opening
> >>>>>> all
> >>>>>>>>>>>> these JDBC connections?
> >>>>>>>>>>>>
> >>>>>>>>>>>>
> >>>>>>>>>>>> Thanks,
> >>>>>>>>>>>>
> >>>>>>>>>>>>
> >>>>>>>>>>>> Chris
> >>>>>>>>>>>>
> >>>>>>>>>>>>
> >>>>>>>>>>>> 183 marketdata INFO [main] openjpa.Runtime - Starting
> >>>> OpenJPA
> >>>>>> 2.2.1
> >>>>>>>>>>>> 214 marketdata INFO [main] openjpa.jdbc.JDBC - Using
> >>>> dictionary
> >>>>>>>>>>>> class "org.apache.openjpa.jdbc.sql.OracleDictionary".
> >>>>>>>>>>>> Exception in thread "main" <openjpa-2.2.1-r422266:1396819
> fatal
> >>>>>> store
> >>>>>>>>>>>> error> org.apache.openjpa.persistence.RollbackException:
> >>>> Listener
> >>>>>>>>>>>> refused the connection with the following error:
> >>>>>>>>>>>> ORA-12519, TNS:no appropriate service handler found
> >>>>>>>>>>>>
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:594)
> >>>>>>>>>>>> at
> >>>>>> ms.algo.adapt.test.BeanIODemo.saveToDB(BeanIODemo.java:153)
> >>>>>>>>>>>> at
> >>>>>> ms.algo.adapt.test.BeanIODemo.beanIOTest(BeanIODemo.java:127)
> >>>>>>>>>>>> at
> >>>> ms.algo.adapt.test.BeanIODemo.main(BeanIODemo.java:50)
> >>>>>>>>>>>> Caused by: <openjpa-2.2.1-r422266:1396819 fatal general error>
> >>>>>>>>>>>> org.apache.openjpa.persistence.PersistenceException: Listener
> >>>>>> refused
> >>>>>>>>>>>> the connection with the following error:
> >>>>>>>>>>>> ORA-12519, TNS:no appropriate service handler found
> >>>>>>>>>>>>
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>
> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4958)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4918)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:971)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getConnection(JDBCStoreManager.java:240)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.getConnection(AbstractJDBCSeq.java:163)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:217)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:201)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>
> org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:778)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>
> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>
> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:762)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:600)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.kernel.SingleFieldManager.preFlushPC(SingleFieldManager.java:803)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.kernel.SingleFieldManager.preFlushPCs(SingleFieldManager.java:762)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:664)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:589)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:505)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:3028)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>> org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:44)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1042)
> >>>>>>>>>>>> at
> >>>>>>>>>> org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2114)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>> org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2074)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1992)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>> org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1516)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:933)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:570)
> >>>>>>>>>>>> ... 3 more
> >>>>>>>>>>>> Caused by: java.sql.SQLException: Listener refused the
> >>>> connection
> >>>>>> with
> >>>>>>>>>>>> the following error:
> >>>>>>>>>>>> ORA-12519, TNS:no appropriate service handler found
> >>>>>>>>>>>>
> >>>>>>>>>>>> at
> >>>>>>>>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:517)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:557)
> >>>>>>>>>>>> at
> >>>>>>>>>> oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:233)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:29)
> >>>>>>>>>>>> at
> >>>>>>>>>> oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:556)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getSimpleConnection(SimpleDriverDataSource.java:84)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.jdbc.schema.AutoDriverDataSource.getConnection(AutoDriverDataSource.java:39)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:76)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:93)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:304)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:982)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:967)
> >>>>>>>>>>>> ... 31 more
> >>>>>>>>>>>> Caused by: oracle.net.ns.NetException: Listener refused the
> >>>>>> connection
> >>>>>>>>>>>> with the following error:
> >>>>>>>>>>>> ORA-12519, TNS:no appropriate service handler found
> >>>>>>>>>>>>
> >>>>>>>>>>>> at
> oracle.net.ns.NSProtocol.connect(NSProtocol.java:457)
> >>>>>>>>>>>> at
> >>>>>>>>>>>>
> >>>> oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1625)
> >>>>>>>>>>>> at
> >>>>>>>>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:365)
> >>>>>>>>>>>> ... 44 more
> >>>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>
> >>>>
>
>
Re: Running out of JDBC connections with openjpa-2.1.0 + Oracle-11g
Posted by James Carman <jc...@carmanconsulting.com>.
Are these the non-transactional connections that OpenJPA is using to find the id values for the entities? I think that's how it works.
On Feb 1, 2013, at 12:05 PM, Chris Wolf <cw...@gmail.com> wrote:
> Kevin,
>
> I finally got past the issue of running out of connections - I simply
> configured connections pooling via DBCP and the problem went away. I
> still consider this a "work-around" because if I have a single
> process/thread persisting entities, I would expect each step of the
> way to complete in a synchronous fashion and be able to use just one
> connection to do that.
>
> Before I tried configuring connection pooling, I was trying to isolate
> the code in OpenJPA that was using up all the connections and it
> appears to be occurring in the OpenJPA sequence generator code, in or
> about:
>
> org.apache.openjpa.kernel.StateManagerImpl.assignObjectId()
> org.apache.openjpa.kernel.JDBCStoreManager.assignObjectId()
> org.apache.openjpa.kernel.AbstractJDBCSeq.next()
> org.apache.openjpa.kernel.NativeJDBCSeq.nextInternal()
>
> My entity's annotations that invoke this are:
>
> @Id
> @SequenceGenerator(name="MARKET_DATA_MARKETDATAID_GENERATOR",
> sequenceName="MARKET_DATA_ID_SEQ")
> @GeneratedValue(strategy=GenerationType.SEQUENCE,
> generator="MARKET_DATA_MARKETDATAID_GENERATOR")
> @Column(name="MARKET_DATA_ID", unique=true, nullable=false, precision=19)
> public long getMarketDataId() {
> return this.marketDataId;
> }
>
>
> For now, I will just hope connection pooling will be sufficient to
> solve this issue, but I am wondering if your unit tests are running
> with pooling or not? If they are, you may be missing a problem.
>
> Thanks,
>
> Chris
>
>
>
> On Thu, Jan 31, 2013 at 5:59 PM, Chris Wolf <cw...@gmail.com> wrote:
>> Kevin,
>>
>> Thanks again, I'll look into the logging thing later - you're right
>> that I'm more focused on just getting persistence to work and so I'm
>> using the debugger. For now, it's just a JSE app, so no JTA yet (but
>> that's what's targeted).
>>
>> So in addition to the programmatic transaction management via Spring
>> JpaTransactionManager, ( which
>> suppressed the out-of-connections issue but silently didn't write to
>> the DB), I tried a third approach -
>> Spring-managed transactions via Spring's TransactionTemplate and
>> callbacks, but that only got me back to where
>> I started, which is the connections running out again, plus hideous
>> code, as you can see, below.
>>
>>
>> Ok, I'll keep hammering away at it. Thanks,
>>
>> -Chris
>>
>>
>> static void saveToDB(final List<MdBaseData> data) throws Exception {
>> EntityManagerFactory emf = Persistence
>> .createEntityManagerFactory("marketdata");
>>
>> final JpaTemplate jpaTempl = new JpaTemplate(emf);
>> jpaTempl.afterPropertiesSet();
>>
>> JpaTransactionManager jpaTxMgr = new JpaTransactionManager(emf);
>> jpaTxMgr.afterPropertiesSet();
>>
>> final TransactionTemplate txTmpl = new TransactionTemplate(jpaTxMgr);
>> txTmpl.afterPropertiesSet();
>>
>> TransactionStrategy txStrategy = new TransactionStrategy() {
>> @SuppressWarnings("deprecation")
>> public Object execute(final JpaCallback<?> callback) {
>> return txTmpl.execute(new TransactionCallback<Object>() {
>> public Object doInTransaction(TransactionStatus status) {
>> return jpaTempl.execute(new JpaCallback<Object>() {
>> public Object doInJpa(EntityManager entityManager)
>> throws PersistenceException {
>> return callback.doInJpa(entityManager);
>> }
>> });
>> }
>> });
>> }
>> };
>>
>> txStrategy.execute(new JpaCallback<Object>() {
>> @Override
>> public Object doInJpa(EntityManager em) throws PersistenceException {
>> for (MdBaseData bd : data) {
>> em.persist(bd);
>> }
>> return null; // writing to db, not reading...
>> }
>> });
>> }
>>
>> On Thu, Jan 31, 2013 at 5:19 PM, Kevin Sutter <kw...@gmail.com> wrote:
>>>> Not certain what you meant by "join EM to the transaction".
>>> If an EM is created without knowledge of a containing JTA transaction, then
>>> the EM and the JTA transaction won't automatically be linked. Thus, if you
>>> start the transaction after the EM was created, you can use the
>>> EM.joinTransaction() method to get the two hooked up. This is, of course,
>>> assuming that the Spring-initiated transaction is a JTA transaction. If
>>> it's not, then you'll have to reference the Spring documentation to figure
>>> out how the EM and transaction are linked.
>>>
>>>> BTW, I don't know if you noticed my previous post about logging, but I
>>> tried setting openjpa.Log = slf4j
>>> Yes, I did notice. But, since you seemed to have been making progress, I
>>> figured you had figured something out... I'm not a slf4j expert, but I do
>>> know other users have had success with integrating openjpa with slf4j. I
>>> would guess it's an issue with the properties you are setting and how they
>>> get mapped to OpenJPA's properties. If you are setting properties like
>>> this:
>>>
>>> log4j.category.openjpa.jdbc.JDBC=TRACE
>>>
>>> .. OpenJPA wouldn't know what to do with this. I would try setting the
>>> properties for OpenJPA's logging in the same format for the persistence.xml:
>>>
>>> <property name="openjpa.Log" value="openjpa.jdbc.JDBC=TRACE"/>
>>>
>>> At least that's what I would expect. Somehow the configuration would still
>>> have to map to properties that OpenJPA knows about. Otherwise, our tracing
>>> code wouldn't know when or what to trace. I know there's some mapping code
>>> like this for Log4J and WebSphere and maybe others. But, the combination
>>> of slf4j and log4j might be throwing things off... Just an idea.
>>>
>>> Kevin
>>>
>>>
>>> On Thu, Jan 31, 2013 at 3:43 PM, Chris Wolf <cw...@gmail.com> wrote:
>>>
>>>> On Thu, Jan 31, 2013 at 4:30 PM, Kevin Sutter <kw...@gmail.com> wrote:
>>>>> So, which do you want? Lots of connections, or data getting to the
>>>>> database? :-)
>>>>
>>>> Preferably the later. ;)
>>>>
>>>>>
>>>>> Try starting the spring transaction before creating the EM. Either that,
>>>>> or join the EM to the transaction.
>>>>
>>>> I tried the former - it didn't change the outcome. Not certain what
>>>> you meant by "join EM to the transaction".
>>>>
>>>>
>>>>
>>>> BTW, I don't know if you noticed my previous post about logging, but I
>>>> tried setting openjpa.Log = slf4j
>>>> and that didn't work - no errors, just no addtional output.
>>>>
>>>> Thanks for helping so far...
>>>>
>>>>>
>>>>> Kevin
>>>>>
>>>>> On Thu, Jan 31, 2013 at 3:26 PM, Chris Wolf <cw...@gmail.com>
>>>> wrote:
>>>>>
>>>>>> Kevin,
>>>>>>
>>>>>> Someone on the users@apache.camel.org mailing list suggested that I
>>>>>> let Springframework manage the transactions, so I changed my code to
>>>>>> look like:
>>>>>>
>>>>>> The apparent connection "leak" went away, and no exceptions are
>>>>>> thrown, and I only see one connection being opened, as expected -
>>>>>> however, nothing goes into the database.
>>>>>>
>>>>>> static void saveToDB(List<MdBaseData> data) throws Exception {
>>>>>> EntityManagerFactory emf = Persistence
>>>>>>
>>>> .createEntityManagerFactory("marketdata");
>>>>>> JpaTransactionManager jpaTxMgr = new
>>>>>> JpaTransactionManager(emf);
>>>>>>
>>>>>>
>>>>>> EntityManager em =
>>>>>>
>>>>>> jpaTxMgr.getEntityManagerFactory().createEntityManager();
>>>>>> TransactionStatus txStatus =
>>>>>> jpaTxMgr.getTransaction(new
>>>>>> DefaultTransactionDefinition());
>>>>>> try {
>>>>>> //em.getTransaction().begin();
>>>>>> for (MdBaseData bd : data) {
>>>>>> em.persist(bd);
>>>>>> }
>>>>>> //em.getTransaction().commit();
>>>>>> jpaTxMgr.commit(txStatus);
>>>>>> } catch (Exception e) {
>>>>>> jpaTxMgr.rollback(txStatus);
>>>>>> e.printStackTrace();
>>>>>> } finally {
>>>>>> em.close();
>>>>>> }
>>>>>> }
>>>>>>
>>>>>> On Thu, Jan 31, 2013 at 3:49 PM, Chris Wolf <cw...@gmail.com>
>>>> wrote:
>>>>>>> Even more specifically, I drilled down into the guts of OpenJPA-2.1,
>>>>>>> into the call to
>>>>>>> EntityManager.getTransaction().begin();
>>>>>>>
>>>>>>> The place were all the connections are opened until exhausted is in:
>>>>>>>
>>>>>>> org.apache.openjpa.kernel.BrokerImpl.flush(int reason)
>>>>>>>
>>>>>>> There is a loop where the connections are all being opened:
>>>>>>>
>>>>>>> Collection mobjs = null;
>>>>>>> _flags |= FLAG_PRESTORING;
>>>>>>> try {
>>>>>>> if (flush) {
>>>>>>> // call pre store on all currently transactional objs
>>>>>>> for (Iterator itr = transactional.iterator();
>>>>>>> itr.hasNext();) //<=== EATING UP ALL CONNECTIONS
>>>>>>> ((StateManagerImpl)
>>>> itr.next()).beforeFlush(reason,
>>>>>> _call);
>>>>>>>
>>>>>>>
>>>>>>> Let me know if there's anything else I can to do help solve this
>>>> issue.
>>>>>>>
>>>>>>> Thanks,
>>>>>>>
>>>>>>> -Chris
>>>>>>>
>>>>>>> On Thu, Jan 31, 2013 at 3:18 PM, Chris Wolf <cw...@gmail.com>
>>>>>> wrote:
>>>>>>>> Kevin,
>>>>>>>>
>>>>>>>> I isolated where all the connections are opened at once - upon
>>>> calling
>>>>>>>> entityManager.getTransaction().commit();
>>>>>>>>
>>>>>>>> I assume this is correct from all the exampled I've seen so far...
>>>>>>>>
>>>>>>>> EntityManager entityManager =
>>>>>>>> entityManagerFactory.createEntityManager(); // here is where just one
>>>>>>>> connection is opened, as expected. (not using connection pool yet)
>>>>>>>> entityManager.getTransaction().begin();
>>>>>>>> for (MdBaseData bd : data) {
>>>>>>>> em.persist(bd);
>>>>>>>> }
>>>>>>>> entityManager.getTransaction().commit(); // here is where all the
>>>>>>>> connections are opened *****
>>>>>>>> entityManager.close();
>>>>>>>>
>>>>>>>> Note that the entity has a M2M to child entities (about 100 per
>>>>>>>> MdBaseData) via a link table.
>>>>>>>>
>>>>>>>> Thanks for any ideas,
>>>>>>>>
>>>>>>>> -Chris
>>>>>>>>
>>>>>>>> On Thu, Jan 31, 2013 at 1:56 PM, Kevin Sutter <kw...@gmail.com>
>>>>>> wrote:
>>>>>>>>> Hi Chris,
>>>>>>>>> Good to know your plans. Thanks.
>>>>>>>>>
>>>>>>>>> You can get almost all of the JDBC connection access via the JDBC
>>>>>> channel
>>>>>>>>> in our logging framework [1]. Unfortunately, I looked at the code
>>>> and
>>>>>> the
>>>>>>>>> constructors don't seem to have a log entry... :-( But, you do get
>>>>>> all of
>>>>>>>>> the closes, commits, rollbacks, etc -- all of the normal operations
>>>>>> through
>>>>>>>>> a Connection object. So, I'd start with that.
>>>>>>>>>
>>>>>>>>> Updating the Ctor for additional logging would be very easy to do.
>>>> I
>>>>>> would
>>>>>>>>> probably do it in the LoggingConnectionDecorator, like you thought.
>>>>>> If you
>>>>>>>>> have issues with building a version of OpenJPA for your testing,
>>>> ping
>>>>>> me
>>>>>>>>> back with the version of OpenJPA that you are using and maybe I can
>>>>>> find
>>>>>>>>> time to do a quick update. But, try the JDBC log channel first and
>>>>>> see if
>>>>>>>>> that gives you enough information for your specific scenario.
>>>>>>>>>
>>>>>>>>> Thanks,
>>>>>>>>> Kevin
>>>>>>>>>
>>>>>>>>> [1]
>>>>>>>>>
>>>>>>
>>>> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_logging_channels
>>>>>>>>>
>>>>>>>>> On Thu, Jan 31, 2013 at 12:02 PM, Chris Wolf <cw...@gmail.com>
>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>> I am fully aware that pooling is the way to go, but I want to just
>>>>>>>>>> prove out the simple case first. Also the final deployment will
>>>> be in
>>>>>>>>>> a JEE container, so it will be doing the pooling.
>>>>>>>>>>
>>>>>>>>>> I really wish there was a logging setting to trace acquire/release
>>>> of
>>>>>>>>>> JDBC Connections - I looked at the source for (I forget now,
>>>> something
>>>>>>>>>> like JDBCDatStore) it had logging but not of acquire/release of
>>>>>>>>>> Connections.
>>>>>>>>>>
>>>>>>>>>> What is this LoggingConnectionDecorator? would that help me log
>>>>>>>>>> connection activity? If so, how is it configured?
>>>>>>>>>>
>>>>>>>>>> Thanks,
>>>>>>>>>>
>>>>>>>>>> -Chris
>>>>>>>>>>
>>>>>>>>>> On Thu, Jan 31, 2013 at 12:19 PM, Kevin Sutter <kwsutter@gmail.com
>>>>>
>>>>>> wrote:
>>>>>>>>>>> Hi Chris,
>>>>>>>>>>> Normally, OpenJPA will only request a connection "on demand" [1].
>>>>>> As
>>>>>>>>>> each
>>>>>>>>>>> database access is requested, a connection is obtained, but then
>>>>>> it's
>>>>>>>>>>> released when we're done with it. Unless there is some
>>>> processing
>>>>>> or
>>>>>>>>>>> configuration that is telling OpenJPA to hang onto the
>>>>>> connection... If
>>>>>>>>>>> nothing jumps out at you, I would suggest tracing (maybe both
>>>>>> OpenJPA and
>>>>>>>>>>> database) to see why all of the connections are getting requested
>>>>>> and
>>>>>>>>>>> nothing is getting closed.
>>>>>>>>>>>
>>>>>>>>>>> As an aside, I would highly recommend the use of some type of
>>>>>> connection
>>>>>>>>>>> pooling. Overall, you will get much better performance if
>>>>>> connections
>>>>>>>>>> can
>>>>>>>>>>> be re-used instead of constantly dropping and re-creating
>>>>>> connections.
>>>>>>>>>>> Whether you use DBCP or Oracle pooling or some application
>>>> server's
>>>>>>>>>>> connection pooling mechanism, it doesn't really matter. But, I
>>>>>> would
>>>>>>>>>>> suggest using some connection pooling.
>>>>>>>>>>>
>>>>>>>>>>> Good luck,
>>>>>>>>>>> Kevin
>>>>>>>>>>>
>>>>>>>>>>> [1]
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_dbsetup_retain
>>>>>>>>>>>
>>>>>>>>>>> On Thu, Jan 31, 2013 at 10:51 AM, Chris Wolf <
>>>> cwolf.algo@gmail.com>
>>>>>>>>>> wrote:
>>>>>>>>>>>
>>>>>>>>>>>> If I process a small number of records, everything works,
>>>> however
>>>>>> when
>>>>>>>>>>>> I try to process a "real-world" number of records, I get an
>>>>>>>>>>>> "ORA-12519". At first, I thought it was an Oracle issue and
>>>> after
>>>>>>>>>>>> searching around and getting hits on "solutions" involving
>>>>>> increasing
>>>>>>>>>>>> Oracle sessions and processes (there are at least 125
>>>> configured),
>>>>>> I
>>>>>>>>>>>> was still getting "ORA-12519". I then tailed the TNS listener
>>>> log
>>>>>> and
>>>>>>>>>>>> saw that everytime my OpenJPA process ran, it would consume all
>>>> the
>>>>>>>>>>>> JDBC connections as if it was using connection pooling with some
>>>>>> high
>>>>>>>>>>>> min-connections setting.
>>>>>>>>>>>>
>>>>>>>>>>>> In fact, as the stack trace shows, it's only using
>>>>>>>>>>>> "SimpleDriveDataSource", which I thought didn't pool connections
>>>>>> and I
>>>>>>>>>>>> don't have the DBCP jar on my classpath, so why is OpenJPA
>>>> opening
>>>>>> all
>>>>>>>>>>>> these JDBC connections?
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> Thanks,
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> Chris
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> 183 marketdata INFO [main] openjpa.Runtime - Starting
>>>> OpenJPA
>>>>>> 2.2.1
>>>>>>>>>>>> 214 marketdata INFO [main] openjpa.jdbc.JDBC - Using
>>>> dictionary
>>>>>>>>>>>> class "org.apache.openjpa.jdbc.sql.OracleDictionary".
>>>>>>>>>>>> Exception in thread "main" <openjpa-2.2.1-r422266:1396819 fatal
>>>>>> store
>>>>>>>>>>>> error> org.apache.openjpa.persistence.RollbackException:
>>>> Listener
>>>>>>>>>>>> refused the connection with the following error:
>>>>>>>>>>>> ORA-12519, TNS:no appropriate service handler found
>>>>>>>>>>>>
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:594)
>>>>>>>>>>>> at
>>>>>> ms.algo.adapt.test.BeanIODemo.saveToDB(BeanIODemo.java:153)
>>>>>>>>>>>> at
>>>>>> ms.algo.adapt.test.BeanIODemo.beanIOTest(BeanIODemo.java:127)
>>>>>>>>>>>> at
>>>> ms.algo.adapt.test.BeanIODemo.main(BeanIODemo.java:50)
>>>>>>>>>>>> Caused by: <openjpa-2.2.1-r422266:1396819 fatal general error>
>>>>>>>>>>>> org.apache.openjpa.persistence.PersistenceException: Listener
>>>>>> refused
>>>>>>>>>>>> the connection with the following error:
>>>>>>>>>>>> ORA-12519, TNS:no appropriate service handler found
>>>>>>>>>>>>
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4958)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4918)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:971)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getConnection(JDBCStoreManager.java:240)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.getConnection(AbstractJDBCSeq.java:163)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:217)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:201)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>> org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:778)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:762)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:600)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPC(SingleFieldManager.java:803)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPCs(SingleFieldManager.java:762)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:664)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:589)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:505)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:3028)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>> org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:44)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1042)
>>>>>>>>>>>> at
>>>>>>>>>> org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2114)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>> org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2074)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1992)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>> org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1516)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:933)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:570)
>>>>>>>>>>>> ... 3 more
>>>>>>>>>>>> Caused by: java.sql.SQLException: Listener refused the
>>>> connection
>>>>>> with
>>>>>>>>>>>> the following error:
>>>>>>>>>>>> ORA-12519, TNS:no appropriate service handler found
>>>>>>>>>>>>
>>>>>>>>>>>> at
>>>>>>>>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:517)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:557)
>>>>>>>>>>>> at
>>>>>>>>>> oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:233)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:29)
>>>>>>>>>>>> at
>>>>>>>>>> oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:556)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getSimpleConnection(SimpleDriverDataSource.java:84)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.jdbc.schema.AutoDriverDataSource.getConnection(AutoDriverDataSource.java:39)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:76)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:93)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:304)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:982)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:967)
>>>>>>>>>>>> ... 31 more
>>>>>>>>>>>> Caused by: oracle.net.ns.NetException: Listener refused the
>>>>>> connection
>>>>>>>>>>>> with the following error:
>>>>>>>>>>>> ORA-12519, TNS:no appropriate service handler found
>>>>>>>>>>>>
>>>>>>>>>>>> at oracle.net.ns.NSProtocol.connect(NSProtocol.java:457)
>>>>>>>>>>>> at
>>>>>>>>>>>>
>>>> oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1625)
>>>>>>>>>>>> at
>>>>>>>>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:365)
>>>>>>>>>>>> ... 44 more
>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>>
Re: Running out of JDBC connections with openjpa-2.1.0 + Oracle-11g
Posted by Chris Wolf <cw...@gmail.com>.
Kevin,
I finally got past the issue of running out of connections - I simply
configured connections pooling via DBCP and the problem went away. I
still consider this a "work-around" because if I have a single
process/thread persisting entities, I would expect each step of the
way to complete in a synchronous fashion and be able to use just one
connection to do that.
Before I tried configuring connection pooling, I was trying to isolate
the code in OpenJPA that was using up all the connections and it
appears to be occurring in the OpenJPA sequence generator code, in or
about:
org.apache.openjpa.kernel.StateManagerImpl.assignObjectId()
org.apache.openjpa.kernel.JDBCStoreManager.assignObjectId()
org.apache.openjpa.kernel.AbstractJDBCSeq.next()
org.apache.openjpa.kernel.NativeJDBCSeq.nextInternal()
My entity's annotations that invoke this are:
@Id
@SequenceGenerator(name="MARKET_DATA_MARKETDATAID_GENERATOR",
sequenceName="MARKET_DATA_ID_SEQ")
@GeneratedValue(strategy=GenerationType.SEQUENCE,
generator="MARKET_DATA_MARKETDATAID_GENERATOR")
@Column(name="MARKET_DATA_ID", unique=true, nullable=false, precision=19)
public long getMarketDataId() {
return this.marketDataId;
}
For now, I will just hope connection pooling will be sufficient to
solve this issue, but I am wondering if your unit tests are running
with pooling or not? If they are, you may be missing a problem.
Thanks,
Chris
On Thu, Jan 31, 2013 at 5:59 PM, Chris Wolf <cw...@gmail.com> wrote:
> Kevin,
>
> Thanks again, I'll look into the logging thing later - you're right
> that I'm more focused on just getting persistence to work and so I'm
> using the debugger. For now, it's just a JSE app, so no JTA yet (but
> that's what's targeted).
>
> So in addition to the programmatic transaction management via Spring
> JpaTransactionManager, ( which
> suppressed the out-of-connections issue but silently didn't write to
> the DB), I tried a third approach -
> Spring-managed transactions via Spring's TransactionTemplate and
> callbacks, but that only got me back to where
> I started, which is the connections running out again, plus hideous
> code, as you can see, below.
>
>
> Ok, I'll keep hammering away at it. Thanks,
>
> -Chris
>
>
> static void saveToDB(final List<MdBaseData> data) throws Exception {
> EntityManagerFactory emf = Persistence
> .createEntityManagerFactory("marketdata");
>
> final JpaTemplate jpaTempl = new JpaTemplate(emf);
> jpaTempl.afterPropertiesSet();
>
> JpaTransactionManager jpaTxMgr = new JpaTransactionManager(emf);
> jpaTxMgr.afterPropertiesSet();
>
> final TransactionTemplate txTmpl = new TransactionTemplate(jpaTxMgr);
> txTmpl.afterPropertiesSet();
>
> TransactionStrategy txStrategy = new TransactionStrategy() {
> @SuppressWarnings("deprecation")
> public Object execute(final JpaCallback<?> callback) {
> return txTmpl.execute(new TransactionCallback<Object>() {
> public Object doInTransaction(TransactionStatus status) {
> return jpaTempl.execute(new JpaCallback<Object>() {
> public Object doInJpa(EntityManager entityManager)
> throws PersistenceException {
> return callback.doInJpa(entityManager);
> }
> });
> }
> });
> }
> };
>
> txStrategy.execute(new JpaCallback<Object>() {
> @Override
> public Object doInJpa(EntityManager em) throws PersistenceException {
> for (MdBaseData bd : data) {
> em.persist(bd);
> }
> return null; // writing to db, not reading...
> }
> });
> }
>
> On Thu, Jan 31, 2013 at 5:19 PM, Kevin Sutter <kw...@gmail.com> wrote:
>>> Not certain what you meant by "join EM to the transaction".
>> If an EM is created without knowledge of a containing JTA transaction, then
>> the EM and the JTA transaction won't automatically be linked. Thus, if you
>> start the transaction after the EM was created, you can use the
>> EM.joinTransaction() method to get the two hooked up. This is, of course,
>> assuming that the Spring-initiated transaction is a JTA transaction. If
>> it's not, then you'll have to reference the Spring documentation to figure
>> out how the EM and transaction are linked.
>>
>>> BTW, I don't know if you noticed my previous post about logging, but I
>> tried setting openjpa.Log = slf4j
>> Yes, I did notice. But, since you seemed to have been making progress, I
>> figured you had figured something out... I'm not a slf4j expert, but I do
>> know other users have had success with integrating openjpa with slf4j. I
>> would guess it's an issue with the properties you are setting and how they
>> get mapped to OpenJPA's properties. If you are setting properties like
>> this:
>>
>> log4j.category.openjpa.jdbc.JDBC=TRACE
>>
>> .. OpenJPA wouldn't know what to do with this. I would try setting the
>> properties for OpenJPA's logging in the same format for the persistence.xml:
>>
>> <property name="openjpa.Log" value="openjpa.jdbc.JDBC=TRACE"/>
>>
>> At least that's what I would expect. Somehow the configuration would still
>> have to map to properties that OpenJPA knows about. Otherwise, our tracing
>> code wouldn't know when or what to trace. I know there's some mapping code
>> like this for Log4J and WebSphere and maybe others. But, the combination
>> of slf4j and log4j might be throwing things off... Just an idea.
>>
>> Kevin
>>
>>
>> On Thu, Jan 31, 2013 at 3:43 PM, Chris Wolf <cw...@gmail.com> wrote:
>>
>>> On Thu, Jan 31, 2013 at 4:30 PM, Kevin Sutter <kw...@gmail.com> wrote:
>>> > So, which do you want? Lots of connections, or data getting to the
>>> > database? :-)
>>>
>>> Preferably the later. ;)
>>>
>>> >
>>> > Try starting the spring transaction before creating the EM. Either that,
>>> > or join the EM to the transaction.
>>>
>>> I tried the former - it didn't change the outcome. Not certain what
>>> you meant by "join EM to the transaction".
>>>
>>>
>>>
>>> BTW, I don't know if you noticed my previous post about logging, but I
>>> tried setting openjpa.Log = slf4j
>>> and that didn't work - no errors, just no addtional output.
>>>
>>> Thanks for helping so far...
>>>
>>> >
>>> > Kevin
>>> >
>>> > On Thu, Jan 31, 2013 at 3:26 PM, Chris Wolf <cw...@gmail.com>
>>> wrote:
>>> >
>>> >> Kevin,
>>> >>
>>> >> Someone on the users@apache.camel.org mailing list suggested that I
>>> >> let Springframework manage the transactions, so I changed my code to
>>> >> look like:
>>> >>
>>> >> The apparent connection "leak" went away, and no exceptions are
>>> >> thrown, and I only see one connection being opened, as expected -
>>> >> however, nothing goes into the database.
>>> >>
>>> >> static void saveToDB(List<MdBaseData> data) throws Exception {
>>> >> EntityManagerFactory emf = Persistence
>>> >>
>>> .createEntityManagerFactory("marketdata");
>>> >> JpaTransactionManager jpaTxMgr = new
>>> >> JpaTransactionManager(emf);
>>> >>
>>> >>
>>> >> EntityManager em =
>>> >>
>>> >> jpaTxMgr.getEntityManagerFactory().createEntityManager();
>>> >> TransactionStatus txStatus =
>>> >> jpaTxMgr.getTransaction(new
>>> >> DefaultTransactionDefinition());
>>> >> try {
>>> >> //em.getTransaction().begin();
>>> >> for (MdBaseData bd : data) {
>>> >> em.persist(bd);
>>> >> }
>>> >> //em.getTransaction().commit();
>>> >> jpaTxMgr.commit(txStatus);
>>> >> } catch (Exception e) {
>>> >> jpaTxMgr.rollback(txStatus);
>>> >> e.printStackTrace();
>>> >> } finally {
>>> >> em.close();
>>> >> }
>>> >> }
>>> >>
>>> >> On Thu, Jan 31, 2013 at 3:49 PM, Chris Wolf <cw...@gmail.com>
>>> wrote:
>>> >> > Even more specifically, I drilled down into the guts of OpenJPA-2.1,
>>> >> > into the call to
>>> >> > EntityManager.getTransaction().begin();
>>> >> >
>>> >> > The place were all the connections are opened until exhausted is in:
>>> >> >
>>> >> > org.apache.openjpa.kernel.BrokerImpl.flush(int reason)
>>> >> >
>>> >> > There is a loop where the connections are all being opened:
>>> >> >
>>> >> > Collection mobjs = null;
>>> >> > _flags |= FLAG_PRESTORING;
>>> >> > try {
>>> >> > if (flush) {
>>> >> > // call pre store on all currently transactional objs
>>> >> > for (Iterator itr = transactional.iterator();
>>> >> > itr.hasNext();) //<=== EATING UP ALL CONNECTIONS
>>> >> > ((StateManagerImpl)
>>> itr.next()).beforeFlush(reason,
>>> >> _call);
>>> >> >
>>> >> >
>>> >> > Let me know if there's anything else I can to do help solve this
>>> issue.
>>> >> >
>>> >> > Thanks,
>>> >> >
>>> >> > -Chris
>>> >> >
>>> >> > On Thu, Jan 31, 2013 at 3:18 PM, Chris Wolf <cw...@gmail.com>
>>> >> wrote:
>>> >> >> Kevin,
>>> >> >>
>>> >> >> I isolated where all the connections are opened at once - upon
>>> calling
>>> >> >> entityManager.getTransaction().commit();
>>> >> >>
>>> >> >> I assume this is correct from all the exampled I've seen so far...
>>> >> >>
>>> >> >> EntityManager entityManager =
>>> >> >> entityManagerFactory.createEntityManager(); // here is where just one
>>> >> >> connection is opened, as expected. (not using connection pool yet)
>>> >> >> entityManager.getTransaction().begin();
>>> >> >> for (MdBaseData bd : data) {
>>> >> >> em.persist(bd);
>>> >> >> }
>>> >> >> entityManager.getTransaction().commit(); // here is where all the
>>> >> >> connections are opened *****
>>> >> >> entityManager.close();
>>> >> >>
>>> >> >> Note that the entity has a M2M to child entities (about 100 per
>>> >> >> MdBaseData) via a link table.
>>> >> >>
>>> >> >> Thanks for any ideas,
>>> >> >>
>>> >> >> -Chris
>>> >> >>
>>> >> >> On Thu, Jan 31, 2013 at 1:56 PM, Kevin Sutter <kw...@gmail.com>
>>> >> wrote:
>>> >> >>> Hi Chris,
>>> >> >>> Good to know your plans. Thanks.
>>> >> >>>
>>> >> >>> You can get almost all of the JDBC connection access via the JDBC
>>> >> channel
>>> >> >>> in our logging framework [1]. Unfortunately, I looked at the code
>>> and
>>> >> the
>>> >> >>> constructors don't seem to have a log entry... :-( But, you do get
>>> >> all of
>>> >> >>> the closes, commits, rollbacks, etc -- all of the normal operations
>>> >> through
>>> >> >>> a Connection object. So, I'd start with that.
>>> >> >>>
>>> >> >>> Updating the Ctor for additional logging would be very easy to do.
>>> I
>>> >> would
>>> >> >>> probably do it in the LoggingConnectionDecorator, like you thought.
>>> >> If you
>>> >> >>> have issues with building a version of OpenJPA for your testing,
>>> ping
>>> >> me
>>> >> >>> back with the version of OpenJPA that you are using and maybe I can
>>> >> find
>>> >> >>> time to do a quick update. But, try the JDBC log channel first and
>>> >> see if
>>> >> >>> that gives you enough information for your specific scenario.
>>> >> >>>
>>> >> >>> Thanks,
>>> >> >>> Kevin
>>> >> >>>
>>> >> >>> [1]
>>> >> >>>
>>> >>
>>> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_logging_channels
>>> >> >>>
>>> >> >>> On Thu, Jan 31, 2013 at 12:02 PM, Chris Wolf <cw...@gmail.com>
>>> >> wrote:
>>> >> >>>
>>> >> >>>> I am fully aware that pooling is the way to go, but I want to just
>>> >> >>>> prove out the simple case first. Also the final deployment will
>>> be in
>>> >> >>>> a JEE container, so it will be doing the pooling.
>>> >> >>>>
>>> >> >>>> I really wish there was a logging setting to trace acquire/release
>>> of
>>> >> >>>> JDBC Connections - I looked at the source for (I forget now,
>>> something
>>> >> >>>> like JDBCDatStore) it had logging but not of acquire/release of
>>> >> >>>> Connections.
>>> >> >>>>
>>> >> >>>> What is this LoggingConnectionDecorator? would that help me log
>>> >> >>>> connection activity? If so, how is it configured?
>>> >> >>>>
>>> >> >>>> Thanks,
>>> >> >>>>
>>> >> >>>> -Chris
>>> >> >>>>
>>> >> >>>> On Thu, Jan 31, 2013 at 12:19 PM, Kevin Sutter <kwsutter@gmail.com
>>> >
>>> >> wrote:
>>> >> >>>> > Hi Chris,
>>> >> >>>> > Normally, OpenJPA will only request a connection "on demand" [1].
>>> >> As
>>> >> >>>> each
>>> >> >>>> > database access is requested, a connection is obtained, but then
>>> >> it's
>>> >> >>>> > released when we're done with it. Unless there is some
>>> processing
>>> >> or
>>> >> >>>> > configuration that is telling OpenJPA to hang onto the
>>> >> connection... If
>>> >> >>>> > nothing jumps out at you, I would suggest tracing (maybe both
>>> >> OpenJPA and
>>> >> >>>> > database) to see why all of the connections are getting requested
>>> >> and
>>> >> >>>> > nothing is getting closed.
>>> >> >>>> >
>>> >> >>>> > As an aside, I would highly recommend the use of some type of
>>> >> connection
>>> >> >>>> > pooling. Overall, you will get much better performance if
>>> >> connections
>>> >> >>>> can
>>> >> >>>> > be re-used instead of constantly dropping and re-creating
>>> >> connections.
>>> >> >>>> > Whether you use DBCP or Oracle pooling or some application
>>> server's
>>> >> >>>> > connection pooling mechanism, it doesn't really matter. But, I
>>> >> would
>>> >> >>>> > suggest using some connection pooling.
>>> >> >>>> >
>>> >> >>>> > Good luck,
>>> >> >>>> > Kevin
>>> >> >>>> >
>>> >> >>>> > [1]
>>> >> >>>> >
>>> >> >>>>
>>> >>
>>> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_dbsetup_retain
>>> >> >>>> >
>>> >> >>>> > On Thu, Jan 31, 2013 at 10:51 AM, Chris Wolf <
>>> cwolf.algo@gmail.com>
>>> >> >>>> wrote:
>>> >> >>>> >
>>> >> >>>> >> If I process a small number of records, everything works,
>>> however
>>> >> when
>>> >> >>>> >> I try to process a "real-world" number of records, I get an
>>> >> >>>> >> "ORA-12519". At first, I thought it was an Oracle issue and
>>> after
>>> >> >>>> >> searching around and getting hits on "solutions" involving
>>> >> increasing
>>> >> >>>> >> Oracle sessions and processes (there are at least 125
>>> configured),
>>> >> I
>>> >> >>>> >> was still getting "ORA-12519". I then tailed the TNS listener
>>> log
>>> >> and
>>> >> >>>> >> saw that everytime my OpenJPA process ran, it would consume all
>>> the
>>> >> >>>> >> JDBC connections as if it was using connection pooling with some
>>> >> high
>>> >> >>>> >> min-connections setting.
>>> >> >>>> >>
>>> >> >>>> >> In fact, as the stack trace shows, it's only using
>>> >> >>>> >> "SimpleDriveDataSource", which I thought didn't pool connections
>>> >> and I
>>> >> >>>> >> don't have the DBCP jar on my classpath, so why is OpenJPA
>>> opening
>>> >> all
>>> >> >>>> >> these JDBC connections?
>>> >> >>>> >>
>>> >> >>>> >>
>>> >> >>>> >> Thanks,
>>> >> >>>> >>
>>> >> >>>> >>
>>> >> >>>> >> Chris
>>> >> >>>> >>
>>> >> >>>> >>
>>> >> >>>> >> 183 marketdata INFO [main] openjpa.Runtime - Starting
>>> OpenJPA
>>> >> 2.2.1
>>> >> >>>> >> 214 marketdata INFO [main] openjpa.jdbc.JDBC - Using
>>> dictionary
>>> >> >>>> >> class "org.apache.openjpa.jdbc.sql.OracleDictionary".
>>> >> >>>> >> Exception in thread "main" <openjpa-2.2.1-r422266:1396819 fatal
>>> >> store
>>> >> >>>> >> error> org.apache.openjpa.persistence.RollbackException:
>>> Listener
>>> >> >>>> >> refused the connection with the following error:
>>> >> >>>> >> ORA-12519, TNS:no appropriate service handler found
>>> >> >>>> >>
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:594)
>>> >> >>>> >> at
>>> >> ms.algo.adapt.test.BeanIODemo.saveToDB(BeanIODemo.java:153)
>>> >> >>>> >> at
>>> >> ms.algo.adapt.test.BeanIODemo.beanIOTest(BeanIODemo.java:127)
>>> >> >>>> >> at
>>> ms.algo.adapt.test.BeanIODemo.main(BeanIODemo.java:50)
>>> >> >>>> >> Caused by: <openjpa-2.2.1-r422266:1396819 fatal general error>
>>> >> >>>> >> org.apache.openjpa.persistence.PersistenceException: Listener
>>> >> refused
>>> >> >>>> >> the connection with the following error:
>>> >> >>>> >> ORA-12519, TNS:no appropriate service handler found
>>> >> >>>> >>
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4958)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4918)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:971)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getConnection(JDBCStoreManager.java:240)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.getConnection(AbstractJDBCSeq.java:163)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:217)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:201)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:778)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:762)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:600)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPC(SingleFieldManager.java:803)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPCs(SingleFieldManager.java:762)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:664)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:589)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:505)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:3028)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:44)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1042)
>>> >> >>>> >> at
>>> >> >>>> org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2114)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2074)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1992)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1516)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:933)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:570)
>>> >> >>>> >> ... 3 more
>>> >> >>>> >> Caused by: java.sql.SQLException: Listener refused the
>>> connection
>>> >> with
>>> >> >>>> >> the following error:
>>> >> >>>> >> ORA-12519, TNS:no appropriate service handler found
>>> >> >>>> >>
>>> >> >>>> >> at
>>> >> >>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:517)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:557)
>>> >> >>>> >> at
>>> >> >>>> oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:233)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:29)
>>> >> >>>> >> at
>>> >> >>>> oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:556)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getSimpleConnection(SimpleDriverDataSource.java:84)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.jdbc.schema.AutoDriverDataSource.getConnection(AutoDriverDataSource.java:39)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:76)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:93)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:304)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:982)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:967)
>>> >> >>>> >> ... 31 more
>>> >> >>>> >> Caused by: oracle.net.ns.NetException: Listener refused the
>>> >> connection
>>> >> >>>> >> with the following error:
>>> >> >>>> >> ORA-12519, TNS:no appropriate service handler found
>>> >> >>>> >>
>>> >> >>>> >> at oracle.net.ns.NSProtocol.connect(NSProtocol.java:457)
>>> >> >>>> >> at
>>> >> >>>> >>
>>> oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1625)
>>> >> >>>> >> at
>>> >> >>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:365)
>>> >> >>>> >> ... 44 more
>>> >> >>>> >>
>>> >> >>>>
>>> >>
>>>
Re: Running out of JDBC connections with openjpa-2.1.0 + Oracle-11g
Posted by Chris Wolf <cw...@gmail.com>.
Kevin,
Thanks again, I'll look into the logging thing later - you're right
that I'm more focused on just getting persistence to work and so I'm
using the debugger. For now, it's just a JSE app, so no JTA yet (but
that's what's targeted).
So in addition to the programmatic transaction management via Spring
JpaTransactionManager, ( which
suppressed the out-of-connections issue but silently didn't write to
the DB), I tried a third approach -
Spring-managed transactions via Spring's TransactionTemplate and
callbacks, but that only got me back to where
I started, which is the connections running out again, plus hideous
code, as you can see, below.
Ok, I'll keep hammering away at it. Thanks,
-Chris
static void saveToDB(final List<MdBaseData> data) throws Exception {
EntityManagerFactory emf = Persistence
.createEntityManagerFactory("marketdata");
final JpaTemplate jpaTempl = new JpaTemplate(emf);
jpaTempl.afterPropertiesSet();
JpaTransactionManager jpaTxMgr = new JpaTransactionManager(emf);
jpaTxMgr.afterPropertiesSet();
final TransactionTemplate txTmpl = new TransactionTemplate(jpaTxMgr);
txTmpl.afterPropertiesSet();
TransactionStrategy txStrategy = new TransactionStrategy() {
@SuppressWarnings("deprecation")
public Object execute(final JpaCallback<?> callback) {
return txTmpl.execute(new TransactionCallback<Object>() {
public Object doInTransaction(TransactionStatus status) {
return jpaTempl.execute(new JpaCallback<Object>() {
public Object doInJpa(EntityManager entityManager)
throws PersistenceException {
return callback.doInJpa(entityManager);
}
});
}
});
}
};
txStrategy.execute(new JpaCallback<Object>() {
@Override
public Object doInJpa(EntityManager em) throws PersistenceException {
for (MdBaseData bd : data) {
em.persist(bd);
}
return null; // writing to db, not reading...
}
});
}
On Thu, Jan 31, 2013 at 5:19 PM, Kevin Sutter <kw...@gmail.com> wrote:
>> Not certain what you meant by "join EM to the transaction".
> If an EM is created without knowledge of a containing JTA transaction, then
> the EM and the JTA transaction won't automatically be linked. Thus, if you
> start the transaction after the EM was created, you can use the
> EM.joinTransaction() method to get the two hooked up. This is, of course,
> assuming that the Spring-initiated transaction is a JTA transaction. If
> it's not, then you'll have to reference the Spring documentation to figure
> out how the EM and transaction are linked.
>
>> BTW, I don't know if you noticed my previous post about logging, but I
> tried setting openjpa.Log = slf4j
> Yes, I did notice. But, since you seemed to have been making progress, I
> figured you had figured something out... I'm not a slf4j expert, but I do
> know other users have had success with integrating openjpa with slf4j. I
> would guess it's an issue with the properties you are setting and how they
> get mapped to OpenJPA's properties. If you are setting properties like
> this:
>
> log4j.category.openjpa.jdbc.JDBC=TRACE
>
> .. OpenJPA wouldn't know what to do with this. I would try setting the
> properties for OpenJPA's logging in the same format for the persistence.xml:
>
> <property name="openjpa.Log" value="openjpa.jdbc.JDBC=TRACE"/>
>
> At least that's what I would expect. Somehow the configuration would still
> have to map to properties that OpenJPA knows about. Otherwise, our tracing
> code wouldn't know when or what to trace. I know there's some mapping code
> like this for Log4J and WebSphere and maybe others. But, the combination
> of slf4j and log4j might be throwing things off... Just an idea.
>
> Kevin
>
>
> On Thu, Jan 31, 2013 at 3:43 PM, Chris Wolf <cw...@gmail.com> wrote:
>
>> On Thu, Jan 31, 2013 at 4:30 PM, Kevin Sutter <kw...@gmail.com> wrote:
>> > So, which do you want? Lots of connections, or data getting to the
>> > database? :-)
>>
>> Preferably the later. ;)
>>
>> >
>> > Try starting the spring transaction before creating the EM. Either that,
>> > or join the EM to the transaction.
>>
>> I tried the former - it didn't change the outcome. Not certain what
>> you meant by "join EM to the transaction".
>>
>>
>>
>> BTW, I don't know if you noticed my previous post about logging, but I
>> tried setting openjpa.Log = slf4j
>> and that didn't work - no errors, just no addtional output.
>>
>> Thanks for helping so far...
>>
>> >
>> > Kevin
>> >
>> > On Thu, Jan 31, 2013 at 3:26 PM, Chris Wolf <cw...@gmail.com>
>> wrote:
>> >
>> >> Kevin,
>> >>
>> >> Someone on the users@apache.camel.org mailing list suggested that I
>> >> let Springframework manage the transactions, so I changed my code to
>> >> look like:
>> >>
>> >> The apparent connection "leak" went away, and no exceptions are
>> >> thrown, and I only see one connection being opened, as expected -
>> >> however, nothing goes into the database.
>> >>
>> >> static void saveToDB(List<MdBaseData> data) throws Exception {
>> >> EntityManagerFactory emf = Persistence
>> >>
>> .createEntityManagerFactory("marketdata");
>> >> JpaTransactionManager jpaTxMgr = new
>> >> JpaTransactionManager(emf);
>> >>
>> >>
>> >> EntityManager em =
>> >>
>> >> jpaTxMgr.getEntityManagerFactory().createEntityManager();
>> >> TransactionStatus txStatus =
>> >> jpaTxMgr.getTransaction(new
>> >> DefaultTransactionDefinition());
>> >> try {
>> >> //em.getTransaction().begin();
>> >> for (MdBaseData bd : data) {
>> >> em.persist(bd);
>> >> }
>> >> //em.getTransaction().commit();
>> >> jpaTxMgr.commit(txStatus);
>> >> } catch (Exception e) {
>> >> jpaTxMgr.rollback(txStatus);
>> >> e.printStackTrace();
>> >> } finally {
>> >> em.close();
>> >> }
>> >> }
>> >>
>> >> On Thu, Jan 31, 2013 at 3:49 PM, Chris Wolf <cw...@gmail.com>
>> wrote:
>> >> > Even more specifically, I drilled down into the guts of OpenJPA-2.1,
>> >> > into the call to
>> >> > EntityManager.getTransaction().begin();
>> >> >
>> >> > The place were all the connections are opened until exhausted is in:
>> >> >
>> >> > org.apache.openjpa.kernel.BrokerImpl.flush(int reason)
>> >> >
>> >> > There is a loop where the connections are all being opened:
>> >> >
>> >> > Collection mobjs = null;
>> >> > _flags |= FLAG_PRESTORING;
>> >> > try {
>> >> > if (flush) {
>> >> > // call pre store on all currently transactional objs
>> >> > for (Iterator itr = transactional.iterator();
>> >> > itr.hasNext();) //<=== EATING UP ALL CONNECTIONS
>> >> > ((StateManagerImpl)
>> itr.next()).beforeFlush(reason,
>> >> _call);
>> >> >
>> >> >
>> >> > Let me know if there's anything else I can to do help solve this
>> issue.
>> >> >
>> >> > Thanks,
>> >> >
>> >> > -Chris
>> >> >
>> >> > On Thu, Jan 31, 2013 at 3:18 PM, Chris Wolf <cw...@gmail.com>
>> >> wrote:
>> >> >> Kevin,
>> >> >>
>> >> >> I isolated where all the connections are opened at once - upon
>> calling
>> >> >> entityManager.getTransaction().commit();
>> >> >>
>> >> >> I assume this is correct from all the exampled I've seen so far...
>> >> >>
>> >> >> EntityManager entityManager =
>> >> >> entityManagerFactory.createEntityManager(); // here is where just one
>> >> >> connection is opened, as expected. (not using connection pool yet)
>> >> >> entityManager.getTransaction().begin();
>> >> >> for (MdBaseData bd : data) {
>> >> >> em.persist(bd);
>> >> >> }
>> >> >> entityManager.getTransaction().commit(); // here is where all the
>> >> >> connections are opened *****
>> >> >> entityManager.close();
>> >> >>
>> >> >> Note that the entity has a M2M to child entities (about 100 per
>> >> >> MdBaseData) via a link table.
>> >> >>
>> >> >> Thanks for any ideas,
>> >> >>
>> >> >> -Chris
>> >> >>
>> >> >> On Thu, Jan 31, 2013 at 1:56 PM, Kevin Sutter <kw...@gmail.com>
>> >> wrote:
>> >> >>> Hi Chris,
>> >> >>> Good to know your plans. Thanks.
>> >> >>>
>> >> >>> You can get almost all of the JDBC connection access via the JDBC
>> >> channel
>> >> >>> in our logging framework [1]. Unfortunately, I looked at the code
>> and
>> >> the
>> >> >>> constructors don't seem to have a log entry... :-( But, you do get
>> >> all of
>> >> >>> the closes, commits, rollbacks, etc -- all of the normal operations
>> >> through
>> >> >>> a Connection object. So, I'd start with that.
>> >> >>>
>> >> >>> Updating the Ctor for additional logging would be very easy to do.
>> I
>> >> would
>> >> >>> probably do it in the LoggingConnectionDecorator, like you thought.
>> >> If you
>> >> >>> have issues with building a version of OpenJPA for your testing,
>> ping
>> >> me
>> >> >>> back with the version of OpenJPA that you are using and maybe I can
>> >> find
>> >> >>> time to do a quick update. But, try the JDBC log channel first and
>> >> see if
>> >> >>> that gives you enough information for your specific scenario.
>> >> >>>
>> >> >>> Thanks,
>> >> >>> Kevin
>> >> >>>
>> >> >>> [1]
>> >> >>>
>> >>
>> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_logging_channels
>> >> >>>
>> >> >>> On Thu, Jan 31, 2013 at 12:02 PM, Chris Wolf <cw...@gmail.com>
>> >> wrote:
>> >> >>>
>> >> >>>> I am fully aware that pooling is the way to go, but I want to just
>> >> >>>> prove out the simple case first. Also the final deployment will
>> be in
>> >> >>>> a JEE container, so it will be doing the pooling.
>> >> >>>>
>> >> >>>> I really wish there was a logging setting to trace acquire/release
>> of
>> >> >>>> JDBC Connections - I looked at the source for (I forget now,
>> something
>> >> >>>> like JDBCDatStore) it had logging but not of acquire/release of
>> >> >>>> Connections.
>> >> >>>>
>> >> >>>> What is this LoggingConnectionDecorator? would that help me log
>> >> >>>> connection activity? If so, how is it configured?
>> >> >>>>
>> >> >>>> Thanks,
>> >> >>>>
>> >> >>>> -Chris
>> >> >>>>
>> >> >>>> On Thu, Jan 31, 2013 at 12:19 PM, Kevin Sutter <kwsutter@gmail.com
>> >
>> >> wrote:
>> >> >>>> > Hi Chris,
>> >> >>>> > Normally, OpenJPA will only request a connection "on demand" [1].
>> >> As
>> >> >>>> each
>> >> >>>> > database access is requested, a connection is obtained, but then
>> >> it's
>> >> >>>> > released when we're done with it. Unless there is some
>> processing
>> >> or
>> >> >>>> > configuration that is telling OpenJPA to hang onto the
>> >> connection... If
>> >> >>>> > nothing jumps out at you, I would suggest tracing (maybe both
>> >> OpenJPA and
>> >> >>>> > database) to see why all of the connections are getting requested
>> >> and
>> >> >>>> > nothing is getting closed.
>> >> >>>> >
>> >> >>>> > As an aside, I would highly recommend the use of some type of
>> >> connection
>> >> >>>> > pooling. Overall, you will get much better performance if
>> >> connections
>> >> >>>> can
>> >> >>>> > be re-used instead of constantly dropping and re-creating
>> >> connections.
>> >> >>>> > Whether you use DBCP or Oracle pooling or some application
>> server's
>> >> >>>> > connection pooling mechanism, it doesn't really matter. But, I
>> >> would
>> >> >>>> > suggest using some connection pooling.
>> >> >>>> >
>> >> >>>> > Good luck,
>> >> >>>> > Kevin
>> >> >>>> >
>> >> >>>> > [1]
>> >> >>>> >
>> >> >>>>
>> >>
>> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_dbsetup_retain
>> >> >>>> >
>> >> >>>> > On Thu, Jan 31, 2013 at 10:51 AM, Chris Wolf <
>> cwolf.algo@gmail.com>
>> >> >>>> wrote:
>> >> >>>> >
>> >> >>>> >> If I process a small number of records, everything works,
>> however
>> >> when
>> >> >>>> >> I try to process a "real-world" number of records, I get an
>> >> >>>> >> "ORA-12519". At first, I thought it was an Oracle issue and
>> after
>> >> >>>> >> searching around and getting hits on "solutions" involving
>> >> increasing
>> >> >>>> >> Oracle sessions and processes (there are at least 125
>> configured),
>> >> I
>> >> >>>> >> was still getting "ORA-12519". I then tailed the TNS listener
>> log
>> >> and
>> >> >>>> >> saw that everytime my OpenJPA process ran, it would consume all
>> the
>> >> >>>> >> JDBC connections as if it was using connection pooling with some
>> >> high
>> >> >>>> >> min-connections setting.
>> >> >>>> >>
>> >> >>>> >> In fact, as the stack trace shows, it's only using
>> >> >>>> >> "SimpleDriveDataSource", which I thought didn't pool connections
>> >> and I
>> >> >>>> >> don't have the DBCP jar on my classpath, so why is OpenJPA
>> opening
>> >> all
>> >> >>>> >> these JDBC connections?
>> >> >>>> >>
>> >> >>>> >>
>> >> >>>> >> Thanks,
>> >> >>>> >>
>> >> >>>> >>
>> >> >>>> >> Chris
>> >> >>>> >>
>> >> >>>> >>
>> >> >>>> >> 183 marketdata INFO [main] openjpa.Runtime - Starting
>> OpenJPA
>> >> 2.2.1
>> >> >>>> >> 214 marketdata INFO [main] openjpa.jdbc.JDBC - Using
>> dictionary
>> >> >>>> >> class "org.apache.openjpa.jdbc.sql.OracleDictionary".
>> >> >>>> >> Exception in thread "main" <openjpa-2.2.1-r422266:1396819 fatal
>> >> store
>> >> >>>> >> error> org.apache.openjpa.persistence.RollbackException:
>> Listener
>> >> >>>> >> refused the connection with the following error:
>> >> >>>> >> ORA-12519, TNS:no appropriate service handler found
>> >> >>>> >>
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:594)
>> >> >>>> >> at
>> >> ms.algo.adapt.test.BeanIODemo.saveToDB(BeanIODemo.java:153)
>> >> >>>> >> at
>> >> ms.algo.adapt.test.BeanIODemo.beanIOTest(BeanIODemo.java:127)
>> >> >>>> >> at
>> ms.algo.adapt.test.BeanIODemo.main(BeanIODemo.java:50)
>> >> >>>> >> Caused by: <openjpa-2.2.1-r422266:1396819 fatal general error>
>> >> >>>> >> org.apache.openjpa.persistence.PersistenceException: Listener
>> >> refused
>> >> >>>> >> the connection with the following error:
>> >> >>>> >> ORA-12519, TNS:no appropriate service handler found
>> >> >>>> >>
>> >> >>>> >> at
>> >> >>>> >>
>> >> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4958)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4918)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:971)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getConnection(JDBCStoreManager.java:240)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.getConnection(AbstractJDBCSeq.java:163)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:217)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:201)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
>> >> >>>> >> at
>> >> >>>> >>
>> >> org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:778)
>> >> >>>> >> at
>> >> >>>> >>
>> >> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493)
>> >> >>>> >> at
>> >> >>>> >>
>> >> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:762)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:600)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPC(SingleFieldManager.java:803)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPCs(SingleFieldManager.java:762)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:664)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:589)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:505)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:3028)
>> >> >>>> >> at
>> >> >>>> >>
>> org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:44)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1042)
>> >> >>>> >> at
>> >> >>>> org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2114)
>> >> >>>> >> at
>> >> >>>> >>
>> >> org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2074)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1992)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
>> >> >>>> >> at
>> >> >>>> >>
>> org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1516)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:933)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:570)
>> >> >>>> >> ... 3 more
>> >> >>>> >> Caused by: java.sql.SQLException: Listener refused the
>> connection
>> >> with
>> >> >>>> >> the following error:
>> >> >>>> >> ORA-12519, TNS:no appropriate service handler found
>> >> >>>> >>
>> >> >>>> >> at
>> >> >>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:517)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:557)
>> >> >>>> >> at
>> >> >>>> oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:233)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:29)
>> >> >>>> >> at
>> >> >>>> oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:556)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getSimpleConnection(SimpleDriverDataSource.java:84)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.schema.AutoDriverDataSource.getConnection(AutoDriverDataSource.java:39)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:76)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:93)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:304)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:982)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:967)
>> >> >>>> >> ... 31 more
>> >> >>>> >> Caused by: oracle.net.ns.NetException: Listener refused the
>> >> connection
>> >> >>>> >> with the following error:
>> >> >>>> >> ORA-12519, TNS:no appropriate service handler found
>> >> >>>> >>
>> >> >>>> >> at oracle.net.ns.NSProtocol.connect(NSProtocol.java:457)
>> >> >>>> >> at
>> >> >>>> >>
>> oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1625)
>> >> >>>> >> at
>> >> >>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:365)
>> >> >>>> >> ... 44 more
>> >> >>>> >>
>> >> >>>>
>> >>
>>
Re: Running out of JDBC connections with openjpa-2.1.0 + Oracle-11g
Posted by Chris Wolf <cw...@gmail.com>.
On Thu, Jan 31, 2013 at 5:19 PM, Kevin Sutter <kw...@gmail.com> wrote:
>> Not certain what you meant by "join EM to the transaction".
> If an EM is created without knowledge of a containing JTA transaction, then
> the EM and the JTA transaction won't automatically be linked. Thus, if you
> start the transaction after the EM was created, you can use the
> EM.joinTransaction() method to get the two hooked up. This is, of course,
> assuming that the Spring-initiated transaction is a JTA transaction. If
> it's not, then you'll have to reference the Spring documentation to figure
> out how the EM and transaction are linked.
>
>> BTW, I don't know if you noticed my previous post about logging, but I
> tried setting openjpa.Log = slf4j
> Yes, I did notice. But, since you seemed to have been making progress, I
> figured you had figured something out... I'm not a slf4j expert, but I do
> know other users have had success with integrating openjpa with slf4j. I
> would guess it's an issue with the properties you are setting and how they
> get mapped to OpenJPA's properties. If you are setting properties like
> this:
>
> log4j.category.openjpa.jdbc.JDBC=TRACE
>
> .. OpenJPA wouldn't know what to do with this. I would try setting the
> properties for OpenJPA's logging in the same format for the persistence.xml:
Kevin, according to the OpenJPA documentation, they have several
LogFactoryAdapter implementations out-of-the-box, including log4j,
slf4j and commons logging:
http://openjpa.apache.org/builds/2.2.1/apache-openjpa/docs/ref_guide_logging_log4j.html
http://openjpa.apache.org/builds/2.2.1/apache-openjpa/docs/ref_guide_logging_slf4j.html
http://openjpa.apache.org/builds/2.2.1/apache-openjpa/docs/ref_guide_logging_commons.html
> <property name="openjpa.Log" value="openjpa.jdbc.JDBC=TRACE"/>
This setting works (the line, above) but it's too verbose and I prefer
the more fine-grained logging settings that log4j and/or slf4j
provides.
>
> At least that's what I would expect. Somehow the configuration would still
> have to map to properties that OpenJPA knows about. Otherwise, our tracing
> code wouldn't know when or what to trace. I know there's some mapping code
> like this for Log4J and WebSphere and maybe others.
Right, in the package:
org.apache.openjpa.lib.log
There are the LogFactoryAdapter implmentations that do the mapping. I
just wanted to
know how to configure my OpenJPA env to actually use one of these.
> But, the combination
> of slf4j and log4j might be throwing things off... Just an idea.
>
> Kevin
>
>
> On Thu, Jan 31, 2013 at 3:43 PM, Chris Wolf <cw...@gmail.com> wrote:
>
>> On Thu, Jan 31, 2013 at 4:30 PM, Kevin Sutter <kw...@gmail.com> wrote:
>> > So, which do you want? Lots of connections, or data getting to the
>> > database? :-)
>>
>> Preferably the later. ;)
>>
>> >
>> > Try starting the spring transaction before creating the EM. Either that,
>> > or join the EM to the transaction.
>>
>> I tried the former - it didn't change the outcome. Not certain what
>> you meant by "join EM to the transaction".
>>
>>
>>
>> BTW, I don't know if you noticed my previous post about logging, but I
>> tried setting openjpa.Log = slf4j
>> and that didn't work - no errors, just no addtional output.
>>
>> Thanks for helping so far...
>>
>> >
>> > Kevin
>> >
>> > On Thu, Jan 31, 2013 at 3:26 PM, Chris Wolf <cw...@gmail.com>
>> wrote:
>> >
>> >> Kevin,
>> >>
>> >> Someone on the users@apache.camel.org mailing list suggested that I
>> >> let Springframework manage the transactions, so I changed my code to
>> >> look like:
>> >>
>> >> The apparent connection "leak" went away, and no exceptions are
>> >> thrown, and I only see one connection being opened, as expected -
>> >> however, nothing goes into the database.
>> >>
>> >> static void saveToDB(List<MdBaseData> data) throws Exception {
>> >> EntityManagerFactory emf = Persistence
>> >>
>> .createEntityManagerFactory("marketdata");
>> >> JpaTransactionManager jpaTxMgr = new
>> >> JpaTransactionManager(emf);
>> >>
>> >>
>> >> EntityManager em =
>> >>
>> >> jpaTxMgr.getEntityManagerFactory().createEntityManager();
>> >> TransactionStatus txStatus =
>> >> jpaTxMgr.getTransaction(new
>> >> DefaultTransactionDefinition());
>> >> try {
>> >> //em.getTransaction().begin();
>> >> for (MdBaseData bd : data) {
>> >> em.persist(bd);
>> >> }
>> >> //em.getTransaction().commit();
>> >> jpaTxMgr.commit(txStatus);
>> >> } catch (Exception e) {
>> >> jpaTxMgr.rollback(txStatus);
>> >> e.printStackTrace();
>> >> } finally {
>> >> em.close();
>> >> }
>> >> }
>> >>
>> >> On Thu, Jan 31, 2013 at 3:49 PM, Chris Wolf <cw...@gmail.com>
>> wrote:
>> >> > Even more specifically, I drilled down into the guts of OpenJPA-2.1,
>> >> > into the call to
>> >> > EntityManager.getTransaction().begin();
>> >> >
>> >> > The place were all the connections are opened until exhausted is in:
>> >> >
>> >> > org.apache.openjpa.kernel.BrokerImpl.flush(int reason)
>> >> >
>> >> > There is a loop where the connections are all being opened:
>> >> >
>> >> > Collection mobjs = null;
>> >> > _flags |= FLAG_PRESTORING;
>> >> > try {
>> >> > if (flush) {
>> >> > // call pre store on all currently transactional objs
>> >> > for (Iterator itr = transactional.iterator();
>> >> > itr.hasNext();) //<=== EATING UP ALL CONNECTIONS
>> >> > ((StateManagerImpl)
>> itr.next()).beforeFlush(reason,
>> >> _call);
>> >> >
>> >> >
>> >> > Let me know if there's anything else I can to do help solve this
>> issue.
>> >> >
>> >> > Thanks,
>> >> >
>> >> > -Chris
>> >> >
>> >> > On Thu, Jan 31, 2013 at 3:18 PM, Chris Wolf <cw...@gmail.com>
>> >> wrote:
>> >> >> Kevin,
>> >> >>
>> >> >> I isolated where all the connections are opened at once - upon
>> calling
>> >> >> entityManager.getTransaction().commit();
>> >> >>
>> >> >> I assume this is correct from all the exampled I've seen so far...
>> >> >>
>> >> >> EntityManager entityManager =
>> >> >> entityManagerFactory.createEntityManager(); // here is where just one
>> >> >> connection is opened, as expected. (not using connection pool yet)
>> >> >> entityManager.getTransaction().begin();
>> >> >> for (MdBaseData bd : data) {
>> >> >> em.persist(bd);
>> >> >> }
>> >> >> entityManager.getTransaction().commit(); // here is where all the
>> >> >> connections are opened *****
>> >> >> entityManager.close();
>> >> >>
>> >> >> Note that the entity has a M2M to child entities (about 100 per
>> >> >> MdBaseData) via a link table.
>> >> >>
>> >> >> Thanks for any ideas,
>> >> >>
>> >> >> -Chris
>> >> >>
>> >> >> On Thu, Jan 31, 2013 at 1:56 PM, Kevin Sutter <kw...@gmail.com>
>> >> wrote:
>> >> >>> Hi Chris,
>> >> >>> Good to know your plans. Thanks.
>> >> >>>
>> >> >>> You can get almost all of the JDBC connection access via the JDBC
>> >> channel
>> >> >>> in our logging framework [1]. Unfortunately, I looked at the code
>> and
>> >> the
>> >> >>> constructors don't seem to have a log entry... :-( But, you do get
>> >> all of
>> >> >>> the closes, commits, rollbacks, etc -- all of the normal operations
>> >> through
>> >> >>> a Connection object. So, I'd start with that.
>> >> >>>
>> >> >>> Updating the Ctor for additional logging would be very easy to do.
>> I
>> >> would
>> >> >>> probably do it in the LoggingConnectionDecorator, like you thought.
>> >> If you
>> >> >>> have issues with building a version of OpenJPA for your testing,
>> ping
>> >> me
>> >> >>> back with the version of OpenJPA that you are using and maybe I can
>> >> find
>> >> >>> time to do a quick update. But, try the JDBC log channel first and
>> >> see if
>> >> >>> that gives you enough information for your specific scenario.
>> >> >>>
>> >> >>> Thanks,
>> >> >>> Kevin
>> >> >>>
>> >> >>> [1]
>> >> >>>
>> >>
>> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_logging_channels
>> >> >>>
>> >> >>> On Thu, Jan 31, 2013 at 12:02 PM, Chris Wolf <cw...@gmail.com>
>> >> wrote:
>> >> >>>
>> >> >>>> I am fully aware that pooling is the way to go, but I want to just
>> >> >>>> prove out the simple case first. Also the final deployment will
>> be in
>> >> >>>> a JEE container, so it will be doing the pooling.
>> >> >>>>
>> >> >>>> I really wish there was a logging setting to trace acquire/release
>> of
>> >> >>>> JDBC Connections - I looked at the source for (I forget now,
>> something
>> >> >>>> like JDBCDatStore) it had logging but not of acquire/release of
>> >> >>>> Connections.
>> >> >>>>
>> >> >>>> What is this LoggingConnectionDecorator? would that help me log
>> >> >>>> connection activity? If so, how is it configured?
>> >> >>>>
>> >> >>>> Thanks,
>> >> >>>>
>> >> >>>> -Chris
>> >> >>>>
>> >> >>>> On Thu, Jan 31, 2013 at 12:19 PM, Kevin Sutter <kwsutter@gmail.com
>> >
>> >> wrote:
>> >> >>>> > Hi Chris,
>> >> >>>> > Normally, OpenJPA will only request a connection "on demand" [1].
>> >> As
>> >> >>>> each
>> >> >>>> > database access is requested, a connection is obtained, but then
>> >> it's
>> >> >>>> > released when we're done with it. Unless there is some
>> processing
>> >> or
>> >> >>>> > configuration that is telling OpenJPA to hang onto the
>> >> connection... If
>> >> >>>> > nothing jumps out at you, I would suggest tracing (maybe both
>> >> OpenJPA and
>> >> >>>> > database) to see why all of the connections are getting requested
>> >> and
>> >> >>>> > nothing is getting closed.
>> >> >>>> >
>> >> >>>> > As an aside, I would highly recommend the use of some type of
>> >> connection
>> >> >>>> > pooling. Overall, you will get much better performance if
>> >> connections
>> >> >>>> can
>> >> >>>> > be re-used instead of constantly dropping and re-creating
>> >> connections.
>> >> >>>> > Whether you use DBCP or Oracle pooling or some application
>> server's
>> >> >>>> > connection pooling mechanism, it doesn't really matter. But, I
>> >> would
>> >> >>>> > suggest using some connection pooling.
>> >> >>>> >
>> >> >>>> > Good luck,
>> >> >>>> > Kevin
>> >> >>>> >
>> >> >>>> > [1]
>> >> >>>> >
>> >> >>>>
>> >>
>> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_dbsetup_retain
>> >> >>>> >
>> >> >>>> > On Thu, Jan 31, 2013 at 10:51 AM, Chris Wolf <
>> cwolf.algo@gmail.com>
>> >> >>>> wrote:
>> >> >>>> >
>> >> >>>> >> If I process a small number of records, everything works,
>> however
>> >> when
>> >> >>>> >> I try to process a "real-world" number of records, I get an
>> >> >>>> >> "ORA-12519". At first, I thought it was an Oracle issue and
>> after
>> >> >>>> >> searching around and getting hits on "solutions" involving
>> >> increasing
>> >> >>>> >> Oracle sessions and processes (there are at least 125
>> configured),
>> >> I
>> >> >>>> >> was still getting "ORA-12519". I then tailed the TNS listener
>> log
>> >> and
>> >> >>>> >> saw that everytime my OpenJPA process ran, it would consume all
>> the
>> >> >>>> >> JDBC connections as if it was using connection pooling with some
>> >> high
>> >> >>>> >> min-connections setting.
>> >> >>>> >>
>> >> >>>> >> In fact, as the stack trace shows, it's only using
>> >> >>>> >> "SimpleDriveDataSource", which I thought didn't pool connections
>> >> and I
>> >> >>>> >> don't have the DBCP jar on my classpath, so why is OpenJPA
>> opening
>> >> all
>> >> >>>> >> these JDBC connections?
>> >> >>>> >>
>> >> >>>> >>
>> >> >>>> >> Thanks,
>> >> >>>> >>
>> >> >>>> >>
>> >> >>>> >> Chris
>> >> >>>> >>
>> >> >>>> >>
>> >> >>>> >> 183 marketdata INFO [main] openjpa.Runtime - Starting
>> OpenJPA
>> >> 2.2.1
>> >> >>>> >> 214 marketdata INFO [main] openjpa.jdbc.JDBC - Using
>> dictionary
>> >> >>>> >> class "org.apache.openjpa.jdbc.sql.OracleDictionary".
>> >> >>>> >> Exception in thread "main" <openjpa-2.2.1-r422266:1396819 fatal
>> >> store
>> >> >>>> >> error> org.apache.openjpa.persistence.RollbackException:
>> Listener
>> >> >>>> >> refused the connection with the following error:
>> >> >>>> >> ORA-12519, TNS:no appropriate service handler found
>> >> >>>> >>
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:594)
>> >> >>>> >> at
>> >> ms.algo.adapt.test.BeanIODemo.saveToDB(BeanIODemo.java:153)
>> >> >>>> >> at
>> >> ms.algo.adapt.test.BeanIODemo.beanIOTest(BeanIODemo.java:127)
>> >> >>>> >> at
>> ms.algo.adapt.test.BeanIODemo.main(BeanIODemo.java:50)
>> >> >>>> >> Caused by: <openjpa-2.2.1-r422266:1396819 fatal general error>
>> >> >>>> >> org.apache.openjpa.persistence.PersistenceException: Listener
>> >> refused
>> >> >>>> >> the connection with the following error:
>> >> >>>> >> ORA-12519, TNS:no appropriate service handler found
>> >> >>>> >>
>> >> >>>> >> at
>> >> >>>> >>
>> >> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4958)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4918)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:971)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getConnection(JDBCStoreManager.java:240)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.getConnection(AbstractJDBCSeq.java:163)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:217)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:201)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
>> >> >>>> >> at
>> >> >>>> >>
>> >> org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:778)
>> >> >>>> >> at
>> >> >>>> >>
>> >> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493)
>> >> >>>> >> at
>> >> >>>> >>
>> >> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:762)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:600)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPC(SingleFieldManager.java:803)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPCs(SingleFieldManager.java:762)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:664)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:589)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:505)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:3028)
>> >> >>>> >> at
>> >> >>>> >>
>> org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:44)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1042)
>> >> >>>> >> at
>> >> >>>> org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2114)
>> >> >>>> >> at
>> >> >>>> >>
>> >> org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2074)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1992)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
>> >> >>>> >> at
>> >> >>>> >>
>> org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1516)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:933)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:570)
>> >> >>>> >> ... 3 more
>> >> >>>> >> Caused by: java.sql.SQLException: Listener refused the
>> connection
>> >> with
>> >> >>>> >> the following error:
>> >> >>>> >> ORA-12519, TNS:no appropriate service handler found
>> >> >>>> >>
>> >> >>>> >> at
>> >> >>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:517)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:557)
>> >> >>>> >> at
>> >> >>>> oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:233)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:29)
>> >> >>>> >> at
>> >> >>>> oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:556)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getSimpleConnection(SimpleDriverDataSource.java:84)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.schema.AutoDriverDataSource.getConnection(AutoDriverDataSource.java:39)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:76)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:93)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:304)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:982)
>> >> >>>> >> at
>> >> >>>> >>
>> >> >>>>
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:967)
>> >> >>>> >> ... 31 more
>> >> >>>> >> Caused by: oracle.net.ns.NetException: Listener refused the
>> >> connection
>> >> >>>> >> with the following error:
>> >> >>>> >> ORA-12519, TNS:no appropriate service handler found
>> >> >>>> >>
>> >> >>>> >> at oracle.net.ns.NSProtocol.connect(NSProtocol.java:457)
>> >> >>>> >> at
>> >> >>>> >>
>> oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1625)
>> >> >>>> >> at
>> >> >>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:365)
>> >> >>>> >> ... 44 more
>> >> >>>> >>
>> >> >>>>
>> >>
>>
Re: Running out of JDBC connections with openjpa-2.1.0 + Oracle-11g
Posted by Kevin Sutter <kw...@gmail.com>.
> Not certain what you meant by "join EM to the transaction".
If an EM is created without knowledge of a containing JTA transaction, then
the EM and the JTA transaction won't automatically be linked. Thus, if you
start the transaction after the EM was created, you can use the
EM.joinTransaction() method to get the two hooked up. This is, of course,
assuming that the Spring-initiated transaction is a JTA transaction. If
it's not, then you'll have to reference the Spring documentation to figure
out how the EM and transaction are linked.
> BTW, I don't know if you noticed my previous post about logging, but I
tried setting openjpa.Log = slf4j
Yes, I did notice. But, since you seemed to have been making progress, I
figured you had figured something out... I'm not a slf4j expert, but I do
know other users have had success with integrating openjpa with slf4j. I
would guess it's an issue with the properties you are setting and how they
get mapped to OpenJPA's properties. If you are setting properties like
this:
log4j.category.openjpa.jdbc.JDBC=TRACE
.. OpenJPA wouldn't know what to do with this. I would try setting the
properties for OpenJPA's logging in the same format for the persistence.xml:
<property name="openjpa.Log" value="openjpa.jdbc.JDBC=TRACE"/>
At least that's what I would expect. Somehow the configuration would still
have to map to properties that OpenJPA knows about. Otherwise, our tracing
code wouldn't know when or what to trace. I know there's some mapping code
like this for Log4J and WebSphere and maybe others. But, the combination
of slf4j and log4j might be throwing things off... Just an idea.
Kevin
On Thu, Jan 31, 2013 at 3:43 PM, Chris Wolf <cw...@gmail.com> wrote:
> On Thu, Jan 31, 2013 at 4:30 PM, Kevin Sutter <kw...@gmail.com> wrote:
> > So, which do you want? Lots of connections, or data getting to the
> > database? :-)
>
> Preferably the later. ;)
>
> >
> > Try starting the spring transaction before creating the EM. Either that,
> > or join the EM to the transaction.
>
> I tried the former - it didn't change the outcome. Not certain what
> you meant by "join EM to the transaction".
>
>
>
> BTW, I don't know if you noticed my previous post about logging, but I
> tried setting openjpa.Log = slf4j
> and that didn't work - no errors, just no addtional output.
>
> Thanks for helping so far...
>
> >
> > Kevin
> >
> > On Thu, Jan 31, 2013 at 3:26 PM, Chris Wolf <cw...@gmail.com>
> wrote:
> >
> >> Kevin,
> >>
> >> Someone on the users@apache.camel.org mailing list suggested that I
> >> let Springframework manage the transactions, so I changed my code to
> >> look like:
> >>
> >> The apparent connection "leak" went away, and no exceptions are
> >> thrown, and I only see one connection being opened, as expected -
> >> however, nothing goes into the database.
> >>
> >> static void saveToDB(List<MdBaseData> data) throws Exception {
> >> EntityManagerFactory emf = Persistence
> >>
> .createEntityManagerFactory("marketdata");
> >> JpaTransactionManager jpaTxMgr = new
> >> JpaTransactionManager(emf);
> >>
> >>
> >> EntityManager em =
> >>
> >> jpaTxMgr.getEntityManagerFactory().createEntityManager();
> >> TransactionStatus txStatus =
> >> jpaTxMgr.getTransaction(new
> >> DefaultTransactionDefinition());
> >> try {
> >> //em.getTransaction().begin();
> >> for (MdBaseData bd : data) {
> >> em.persist(bd);
> >> }
> >> //em.getTransaction().commit();
> >> jpaTxMgr.commit(txStatus);
> >> } catch (Exception e) {
> >> jpaTxMgr.rollback(txStatus);
> >> e.printStackTrace();
> >> } finally {
> >> em.close();
> >> }
> >> }
> >>
> >> On Thu, Jan 31, 2013 at 3:49 PM, Chris Wolf <cw...@gmail.com>
> wrote:
> >> > Even more specifically, I drilled down into the guts of OpenJPA-2.1,
> >> > into the call to
> >> > EntityManager.getTransaction().begin();
> >> >
> >> > The place were all the connections are opened until exhausted is in:
> >> >
> >> > org.apache.openjpa.kernel.BrokerImpl.flush(int reason)
> >> >
> >> > There is a loop where the connections are all being opened:
> >> >
> >> > Collection mobjs = null;
> >> > _flags |= FLAG_PRESTORING;
> >> > try {
> >> > if (flush) {
> >> > // call pre store on all currently transactional objs
> >> > for (Iterator itr = transactional.iterator();
> >> > itr.hasNext();) //<=== EATING UP ALL CONNECTIONS
> >> > ((StateManagerImpl)
> itr.next()).beforeFlush(reason,
> >> _call);
> >> >
> >> >
> >> > Let me know if there's anything else I can to do help solve this
> issue.
> >> >
> >> > Thanks,
> >> >
> >> > -Chris
> >> >
> >> > On Thu, Jan 31, 2013 at 3:18 PM, Chris Wolf <cw...@gmail.com>
> >> wrote:
> >> >> Kevin,
> >> >>
> >> >> I isolated where all the connections are opened at once - upon
> calling
> >> >> entityManager.getTransaction().commit();
> >> >>
> >> >> I assume this is correct from all the exampled I've seen so far...
> >> >>
> >> >> EntityManager entityManager =
> >> >> entityManagerFactory.createEntityManager(); // here is where just one
> >> >> connection is opened, as expected. (not using connection pool yet)
> >> >> entityManager.getTransaction().begin();
> >> >> for (MdBaseData bd : data) {
> >> >> em.persist(bd);
> >> >> }
> >> >> entityManager.getTransaction().commit(); // here is where all the
> >> >> connections are opened *****
> >> >> entityManager.close();
> >> >>
> >> >> Note that the entity has a M2M to child entities (about 100 per
> >> >> MdBaseData) via a link table.
> >> >>
> >> >> Thanks for any ideas,
> >> >>
> >> >> -Chris
> >> >>
> >> >> On Thu, Jan 31, 2013 at 1:56 PM, Kevin Sutter <kw...@gmail.com>
> >> wrote:
> >> >>> Hi Chris,
> >> >>> Good to know your plans. Thanks.
> >> >>>
> >> >>> You can get almost all of the JDBC connection access via the JDBC
> >> channel
> >> >>> in our logging framework [1]. Unfortunately, I looked at the code
> and
> >> the
> >> >>> constructors don't seem to have a log entry... :-( But, you do get
> >> all of
> >> >>> the closes, commits, rollbacks, etc -- all of the normal operations
> >> through
> >> >>> a Connection object. So, I'd start with that.
> >> >>>
> >> >>> Updating the Ctor for additional logging would be very easy to do.
> I
> >> would
> >> >>> probably do it in the LoggingConnectionDecorator, like you thought.
> >> If you
> >> >>> have issues with building a version of OpenJPA for your testing,
> ping
> >> me
> >> >>> back with the version of OpenJPA that you are using and maybe I can
> >> find
> >> >>> time to do a quick update. But, try the JDBC log channel first and
> >> see if
> >> >>> that gives you enough information for your specific scenario.
> >> >>>
> >> >>> Thanks,
> >> >>> Kevin
> >> >>>
> >> >>> [1]
> >> >>>
> >>
> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_logging_channels
> >> >>>
> >> >>> On Thu, Jan 31, 2013 at 12:02 PM, Chris Wolf <cw...@gmail.com>
> >> wrote:
> >> >>>
> >> >>>> I am fully aware that pooling is the way to go, but I want to just
> >> >>>> prove out the simple case first. Also the final deployment will
> be in
> >> >>>> a JEE container, so it will be doing the pooling.
> >> >>>>
> >> >>>> I really wish there was a logging setting to trace acquire/release
> of
> >> >>>> JDBC Connections - I looked at the source for (I forget now,
> something
> >> >>>> like JDBCDatStore) it had logging but not of acquire/release of
> >> >>>> Connections.
> >> >>>>
> >> >>>> What is this LoggingConnectionDecorator? would that help me log
> >> >>>> connection activity? If so, how is it configured?
> >> >>>>
> >> >>>> Thanks,
> >> >>>>
> >> >>>> -Chris
> >> >>>>
> >> >>>> On Thu, Jan 31, 2013 at 12:19 PM, Kevin Sutter <kwsutter@gmail.com
> >
> >> wrote:
> >> >>>> > Hi Chris,
> >> >>>> > Normally, OpenJPA will only request a connection "on demand" [1].
> >> As
> >> >>>> each
> >> >>>> > database access is requested, a connection is obtained, but then
> >> it's
> >> >>>> > released when we're done with it. Unless there is some
> processing
> >> or
> >> >>>> > configuration that is telling OpenJPA to hang onto the
> >> connection... If
> >> >>>> > nothing jumps out at you, I would suggest tracing (maybe both
> >> OpenJPA and
> >> >>>> > database) to see why all of the connections are getting requested
> >> and
> >> >>>> > nothing is getting closed.
> >> >>>> >
> >> >>>> > As an aside, I would highly recommend the use of some type of
> >> connection
> >> >>>> > pooling. Overall, you will get much better performance if
> >> connections
> >> >>>> can
> >> >>>> > be re-used instead of constantly dropping and re-creating
> >> connections.
> >> >>>> > Whether you use DBCP or Oracle pooling or some application
> server's
> >> >>>> > connection pooling mechanism, it doesn't really matter. But, I
> >> would
> >> >>>> > suggest using some connection pooling.
> >> >>>> >
> >> >>>> > Good luck,
> >> >>>> > Kevin
> >> >>>> >
> >> >>>> > [1]
> >> >>>> >
> >> >>>>
> >>
> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_dbsetup_retain
> >> >>>> >
> >> >>>> > On Thu, Jan 31, 2013 at 10:51 AM, Chris Wolf <
> cwolf.algo@gmail.com>
> >> >>>> wrote:
> >> >>>> >
> >> >>>> >> If I process a small number of records, everything works,
> however
> >> when
> >> >>>> >> I try to process a "real-world" number of records, I get an
> >> >>>> >> "ORA-12519". At first, I thought it was an Oracle issue and
> after
> >> >>>> >> searching around and getting hits on "solutions" involving
> >> increasing
> >> >>>> >> Oracle sessions and processes (there are at least 125
> configured),
> >> I
> >> >>>> >> was still getting "ORA-12519". I then tailed the TNS listener
> log
> >> and
> >> >>>> >> saw that everytime my OpenJPA process ran, it would consume all
> the
> >> >>>> >> JDBC connections as if it was using connection pooling with some
> >> high
> >> >>>> >> min-connections setting.
> >> >>>> >>
> >> >>>> >> In fact, as the stack trace shows, it's only using
> >> >>>> >> "SimpleDriveDataSource", which I thought didn't pool connections
> >> and I
> >> >>>> >> don't have the DBCP jar on my classpath, so why is OpenJPA
> opening
> >> all
> >> >>>> >> these JDBC connections?
> >> >>>> >>
> >> >>>> >>
> >> >>>> >> Thanks,
> >> >>>> >>
> >> >>>> >>
> >> >>>> >> Chris
> >> >>>> >>
> >> >>>> >>
> >> >>>> >> 183 marketdata INFO [main] openjpa.Runtime - Starting
> OpenJPA
> >> 2.2.1
> >> >>>> >> 214 marketdata INFO [main] openjpa.jdbc.JDBC - Using
> dictionary
> >> >>>> >> class "org.apache.openjpa.jdbc.sql.OracleDictionary".
> >> >>>> >> Exception in thread "main" <openjpa-2.2.1-r422266:1396819 fatal
> >> store
> >> >>>> >> error> org.apache.openjpa.persistence.RollbackException:
> Listener
> >> >>>> >> refused the connection with the following error:
> >> >>>> >> ORA-12519, TNS:no appropriate service handler found
> >> >>>> >>
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:594)
> >> >>>> >> at
> >> ms.algo.adapt.test.BeanIODemo.saveToDB(BeanIODemo.java:153)
> >> >>>> >> at
> >> ms.algo.adapt.test.BeanIODemo.beanIOTest(BeanIODemo.java:127)
> >> >>>> >> at
> ms.algo.adapt.test.BeanIODemo.main(BeanIODemo.java:50)
> >> >>>> >> Caused by: <openjpa-2.2.1-r422266:1396819 fatal general error>
> >> >>>> >> org.apache.openjpa.persistence.PersistenceException: Listener
> >> refused
> >> >>>> >> the connection with the following error:
> >> >>>> >> ORA-12519, TNS:no appropriate service handler found
> >> >>>> >>
> >> >>>> >> at
> >> >>>> >>
> >> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4958)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4918)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:971)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getConnection(JDBCStoreManager.java:240)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.getConnection(AbstractJDBCSeq.java:163)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:217)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:201)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
> >> >>>> >> at
> >> >>>> >>
> >> org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:778)
> >> >>>> >> at
> >> >>>> >>
> >> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493)
> >> >>>> >> at
> >> >>>> >>
> >> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:762)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:600)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.kernel.SingleFieldManager.preFlushPC(SingleFieldManager.java:803)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.kernel.SingleFieldManager.preFlushPCs(SingleFieldManager.java:762)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:664)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:589)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:505)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:3028)
> >> >>>> >> at
> >> >>>> >>
> org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:44)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1042)
> >> >>>> >> at
> >> >>>> org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2114)
> >> >>>> >> at
> >> >>>> >>
> >> org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2074)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1992)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
> >> >>>> >> at
> >> >>>> >>
> org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1516)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:933)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:570)
> >> >>>> >> ... 3 more
> >> >>>> >> Caused by: java.sql.SQLException: Listener refused the
> connection
> >> with
> >> >>>> >> the following error:
> >> >>>> >> ORA-12519, TNS:no appropriate service handler found
> >> >>>> >>
> >> >>>> >> at
> >> >>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:517)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:557)
> >> >>>> >> at
> >> >>>> oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:233)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:29)
> >> >>>> >> at
> >> >>>> oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:556)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getSimpleConnection(SimpleDriverDataSource.java:84)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.jdbc.schema.AutoDriverDataSource.getConnection(AutoDriverDataSource.java:39)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:76)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:93)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:304)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:982)
> >> >>>> >> at
> >> >>>> >>
> >> >>>>
> >>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:967)
> >> >>>> >> ... 31 more
> >> >>>> >> Caused by: oracle.net.ns.NetException: Listener refused the
> >> connection
> >> >>>> >> with the following error:
> >> >>>> >> ORA-12519, TNS:no appropriate service handler found
> >> >>>> >>
> >> >>>> >> at oracle.net.ns.NSProtocol.connect(NSProtocol.java:457)
> >> >>>> >> at
> >> >>>> >>
> oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1625)
> >> >>>> >> at
> >> >>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:365)
> >> >>>> >> ... 44 more
> >> >>>> >>
> >> >>>>
> >>
>
Re: Running out of JDBC connections with openjpa-2.1.0 + Oracle-11g
Posted by Chris Wolf <cw...@gmail.com>.
On Thu, Jan 31, 2013 at 4:30 PM, Kevin Sutter <kw...@gmail.com> wrote:
> So, which do you want? Lots of connections, or data getting to the
> database? :-)
Preferably the later. ;)
>
> Try starting the spring transaction before creating the EM. Either that,
> or join the EM to the transaction.
I tried the former - it didn't change the outcome. Not certain what
you meant by "join EM to the transaction".
BTW, I don't know if you noticed my previous post about logging, but I
tried setting openjpa.Log = slf4j
and that didn't work - no errors, just no addtional output.
Thanks for helping so far...
>
> Kevin
>
> On Thu, Jan 31, 2013 at 3:26 PM, Chris Wolf <cw...@gmail.com> wrote:
>
>> Kevin,
>>
>> Someone on the users@apache.camel.org mailing list suggested that I
>> let Springframework manage the transactions, so I changed my code to
>> look like:
>>
>> The apparent connection "leak" went away, and no exceptions are
>> thrown, and I only see one connection being opened, as expected -
>> however, nothing goes into the database.
>>
>> static void saveToDB(List<MdBaseData> data) throws Exception {
>> EntityManagerFactory emf = Persistence
>> .createEntityManagerFactory("marketdata");
>> JpaTransactionManager jpaTxMgr = new
>> JpaTransactionManager(emf);
>>
>>
>> EntityManager em =
>>
>> jpaTxMgr.getEntityManagerFactory().createEntityManager();
>> TransactionStatus txStatus =
>> jpaTxMgr.getTransaction(new
>> DefaultTransactionDefinition());
>> try {
>> //em.getTransaction().begin();
>> for (MdBaseData bd : data) {
>> em.persist(bd);
>> }
>> //em.getTransaction().commit();
>> jpaTxMgr.commit(txStatus);
>> } catch (Exception e) {
>> jpaTxMgr.rollback(txStatus);
>> e.printStackTrace();
>> } finally {
>> em.close();
>> }
>> }
>>
>> On Thu, Jan 31, 2013 at 3:49 PM, Chris Wolf <cw...@gmail.com> wrote:
>> > Even more specifically, I drilled down into the guts of OpenJPA-2.1,
>> > into the call to
>> > EntityManager.getTransaction().begin();
>> >
>> > The place were all the connections are opened until exhausted is in:
>> >
>> > org.apache.openjpa.kernel.BrokerImpl.flush(int reason)
>> >
>> > There is a loop where the connections are all being opened:
>> >
>> > Collection mobjs = null;
>> > _flags |= FLAG_PRESTORING;
>> > try {
>> > if (flush) {
>> > // call pre store on all currently transactional objs
>> > for (Iterator itr = transactional.iterator();
>> > itr.hasNext();) //<=== EATING UP ALL CONNECTIONS
>> > ((StateManagerImpl) itr.next()).beforeFlush(reason,
>> _call);
>> >
>> >
>> > Let me know if there's anything else I can to do help solve this issue.
>> >
>> > Thanks,
>> >
>> > -Chris
>> >
>> > On Thu, Jan 31, 2013 at 3:18 PM, Chris Wolf <cw...@gmail.com>
>> wrote:
>> >> Kevin,
>> >>
>> >> I isolated where all the connections are opened at once - upon calling
>> >> entityManager.getTransaction().commit();
>> >>
>> >> I assume this is correct from all the exampled I've seen so far...
>> >>
>> >> EntityManager entityManager =
>> >> entityManagerFactory.createEntityManager(); // here is where just one
>> >> connection is opened, as expected. (not using connection pool yet)
>> >> entityManager.getTransaction().begin();
>> >> for (MdBaseData bd : data) {
>> >> em.persist(bd);
>> >> }
>> >> entityManager.getTransaction().commit(); // here is where all the
>> >> connections are opened *****
>> >> entityManager.close();
>> >>
>> >> Note that the entity has a M2M to child entities (about 100 per
>> >> MdBaseData) via a link table.
>> >>
>> >> Thanks for any ideas,
>> >>
>> >> -Chris
>> >>
>> >> On Thu, Jan 31, 2013 at 1:56 PM, Kevin Sutter <kw...@gmail.com>
>> wrote:
>> >>> Hi Chris,
>> >>> Good to know your plans. Thanks.
>> >>>
>> >>> You can get almost all of the JDBC connection access via the JDBC
>> channel
>> >>> in our logging framework [1]. Unfortunately, I looked at the code and
>> the
>> >>> constructors don't seem to have a log entry... :-( But, you do get
>> all of
>> >>> the closes, commits, rollbacks, etc -- all of the normal operations
>> through
>> >>> a Connection object. So, I'd start with that.
>> >>>
>> >>> Updating the Ctor for additional logging would be very easy to do. I
>> would
>> >>> probably do it in the LoggingConnectionDecorator, like you thought.
>> If you
>> >>> have issues with building a version of OpenJPA for your testing, ping
>> me
>> >>> back with the version of OpenJPA that you are using and maybe I can
>> find
>> >>> time to do a quick update. But, try the JDBC log channel first and
>> see if
>> >>> that gives you enough information for your specific scenario.
>> >>>
>> >>> Thanks,
>> >>> Kevin
>> >>>
>> >>> [1]
>> >>>
>> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_logging_channels
>> >>>
>> >>> On Thu, Jan 31, 2013 at 12:02 PM, Chris Wolf <cw...@gmail.com>
>> wrote:
>> >>>
>> >>>> I am fully aware that pooling is the way to go, but I want to just
>> >>>> prove out the simple case first. Also the final deployment will be in
>> >>>> a JEE container, so it will be doing the pooling.
>> >>>>
>> >>>> I really wish there was a logging setting to trace acquire/release of
>> >>>> JDBC Connections - I looked at the source for (I forget now, something
>> >>>> like JDBCDatStore) it had logging but not of acquire/release of
>> >>>> Connections.
>> >>>>
>> >>>> What is this LoggingConnectionDecorator? would that help me log
>> >>>> connection activity? If so, how is it configured?
>> >>>>
>> >>>> Thanks,
>> >>>>
>> >>>> -Chris
>> >>>>
>> >>>> On Thu, Jan 31, 2013 at 12:19 PM, Kevin Sutter <kw...@gmail.com>
>> wrote:
>> >>>> > Hi Chris,
>> >>>> > Normally, OpenJPA will only request a connection "on demand" [1].
>> As
>> >>>> each
>> >>>> > database access is requested, a connection is obtained, but then
>> it's
>> >>>> > released when we're done with it. Unless there is some processing
>> or
>> >>>> > configuration that is telling OpenJPA to hang onto the
>> connection... If
>> >>>> > nothing jumps out at you, I would suggest tracing (maybe both
>> OpenJPA and
>> >>>> > database) to see why all of the connections are getting requested
>> and
>> >>>> > nothing is getting closed.
>> >>>> >
>> >>>> > As an aside, I would highly recommend the use of some type of
>> connection
>> >>>> > pooling. Overall, you will get much better performance if
>> connections
>> >>>> can
>> >>>> > be re-used instead of constantly dropping and re-creating
>> connections.
>> >>>> > Whether you use DBCP or Oracle pooling or some application server's
>> >>>> > connection pooling mechanism, it doesn't really matter. But, I
>> would
>> >>>> > suggest using some connection pooling.
>> >>>> >
>> >>>> > Good luck,
>> >>>> > Kevin
>> >>>> >
>> >>>> > [1]
>> >>>> >
>> >>>>
>> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_dbsetup_retain
>> >>>> >
>> >>>> > On Thu, Jan 31, 2013 at 10:51 AM, Chris Wolf <cw...@gmail.com>
>> >>>> wrote:
>> >>>> >
>> >>>> >> If I process a small number of records, everything works, however
>> when
>> >>>> >> I try to process a "real-world" number of records, I get an
>> >>>> >> "ORA-12519". At first, I thought it was an Oracle issue and after
>> >>>> >> searching around and getting hits on "solutions" involving
>> increasing
>> >>>> >> Oracle sessions and processes (there are at least 125 configured),
>> I
>> >>>> >> was still getting "ORA-12519". I then tailed the TNS listener log
>> and
>> >>>> >> saw that everytime my OpenJPA process ran, it would consume all the
>> >>>> >> JDBC connections as if it was using connection pooling with some
>> high
>> >>>> >> min-connections setting.
>> >>>> >>
>> >>>> >> In fact, as the stack trace shows, it's only using
>> >>>> >> "SimpleDriveDataSource", which I thought didn't pool connections
>> and I
>> >>>> >> don't have the DBCP jar on my classpath, so why is OpenJPA opening
>> all
>> >>>> >> these JDBC connections?
>> >>>> >>
>> >>>> >>
>> >>>> >> Thanks,
>> >>>> >>
>> >>>> >>
>> >>>> >> Chris
>> >>>> >>
>> >>>> >>
>> >>>> >> 183 marketdata INFO [main] openjpa.Runtime - Starting OpenJPA
>> 2.2.1
>> >>>> >> 214 marketdata INFO [main] openjpa.jdbc.JDBC - Using dictionary
>> >>>> >> class "org.apache.openjpa.jdbc.sql.OracleDictionary".
>> >>>> >> Exception in thread "main" <openjpa-2.2.1-r422266:1396819 fatal
>> store
>> >>>> >> error> org.apache.openjpa.persistence.RollbackException: Listener
>> >>>> >> refused the connection with the following error:
>> >>>> >> ORA-12519, TNS:no appropriate service handler found
>> >>>> >>
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:594)
>> >>>> >> at
>> ms.algo.adapt.test.BeanIODemo.saveToDB(BeanIODemo.java:153)
>> >>>> >> at
>> ms.algo.adapt.test.BeanIODemo.beanIOTest(BeanIODemo.java:127)
>> >>>> >> at ms.algo.adapt.test.BeanIODemo.main(BeanIODemo.java:50)
>> >>>> >> Caused by: <openjpa-2.2.1-r422266:1396819 fatal general error>
>> >>>> >> org.apache.openjpa.persistence.PersistenceException: Listener
>> refused
>> >>>> >> the connection with the following error:
>> >>>> >> ORA-12519, TNS:no appropriate service handler found
>> >>>> >>
>> >>>> >> at
>> >>>> >>
>> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4958)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4918)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:971)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getConnection(JDBCStoreManager.java:240)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.getConnection(AbstractJDBCSeq.java:163)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:217)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:201)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
>> >>>> >> at
>> >>>> >>
>> org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:778)
>> >>>> >> at
>> >>>> >>
>> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493)
>> >>>> >> at
>> >>>> >>
>> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:762)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:600)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPC(SingleFieldManager.java:803)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPCs(SingleFieldManager.java:762)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:664)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:589)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:505)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:3028)
>> >>>> >> at
>> >>>> >> org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:44)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1042)
>> >>>> >> at
>> >>>> org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2114)
>> >>>> >> at
>> >>>> >>
>> org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2074)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1992)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
>> >>>> >> at
>> >>>> >> org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1516)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:933)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:570)
>> >>>> >> ... 3 more
>> >>>> >> Caused by: java.sql.SQLException: Listener refused the connection
>> with
>> >>>> >> the following error:
>> >>>> >> ORA-12519, TNS:no appropriate service handler found
>> >>>> >>
>> >>>> >> at
>> >>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:517)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:557)
>> >>>> >> at
>> >>>> oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:233)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:29)
>> >>>> >> at
>> >>>> oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:556)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getSimpleConnection(SimpleDriverDataSource.java:84)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.jdbc.schema.AutoDriverDataSource.getConnection(AutoDriverDataSource.java:39)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:76)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:93)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:304)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:982)
>> >>>> >> at
>> >>>> >>
>> >>>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:967)
>> >>>> >> ... 31 more
>> >>>> >> Caused by: oracle.net.ns.NetException: Listener refused the
>> connection
>> >>>> >> with the following error:
>> >>>> >> ORA-12519, TNS:no appropriate service handler found
>> >>>> >>
>> >>>> >> at oracle.net.ns.NSProtocol.connect(NSProtocol.java:457)
>> >>>> >> at
>> >>>> >> oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1625)
>> >>>> >> at
>> >>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:365)
>> >>>> >> ... 44 more
>> >>>> >>
>> >>>>
>>
Re: Running out of JDBC connections with openjpa-2.1.0 + Oracle-11g
Posted by Kevin Sutter <kw...@gmail.com>.
So, which do you want? Lots of connections, or data getting to the
database? :-)
Try starting the spring transaction before creating the EM. Either that,
or join the EM to the transaction.
Kevin
On Thu, Jan 31, 2013 at 3:26 PM, Chris Wolf <cw...@gmail.com> wrote:
> Kevin,
>
> Someone on the users@apache.camel.org mailing list suggested that I
> let Springframework manage the transactions, so I changed my code to
> look like:
>
> The apparent connection "leak" went away, and no exceptions are
> thrown, and I only see one connection being opened, as expected -
> however, nothing goes into the database.
>
> static void saveToDB(List<MdBaseData> data) throws Exception {
> EntityManagerFactory emf = Persistence
> .createEntityManagerFactory("marketdata");
> JpaTransactionManager jpaTxMgr = new
> JpaTransactionManager(emf);
>
>
> EntityManager em =
>
> jpaTxMgr.getEntityManagerFactory().createEntityManager();
> TransactionStatus txStatus =
> jpaTxMgr.getTransaction(new
> DefaultTransactionDefinition());
> try {
> //em.getTransaction().begin();
> for (MdBaseData bd : data) {
> em.persist(bd);
> }
> //em.getTransaction().commit();
> jpaTxMgr.commit(txStatus);
> } catch (Exception e) {
> jpaTxMgr.rollback(txStatus);
> e.printStackTrace();
> } finally {
> em.close();
> }
> }
>
> On Thu, Jan 31, 2013 at 3:49 PM, Chris Wolf <cw...@gmail.com> wrote:
> > Even more specifically, I drilled down into the guts of OpenJPA-2.1,
> > into the call to
> > EntityManager.getTransaction().begin();
> >
> > The place were all the connections are opened until exhausted is in:
> >
> > org.apache.openjpa.kernel.BrokerImpl.flush(int reason)
> >
> > There is a loop where the connections are all being opened:
> >
> > Collection mobjs = null;
> > _flags |= FLAG_PRESTORING;
> > try {
> > if (flush) {
> > // call pre store on all currently transactional objs
> > for (Iterator itr = transactional.iterator();
> > itr.hasNext();) //<=== EATING UP ALL CONNECTIONS
> > ((StateManagerImpl) itr.next()).beforeFlush(reason,
> _call);
> >
> >
> > Let me know if there's anything else I can to do help solve this issue.
> >
> > Thanks,
> >
> > -Chris
> >
> > On Thu, Jan 31, 2013 at 3:18 PM, Chris Wolf <cw...@gmail.com>
> wrote:
> >> Kevin,
> >>
> >> I isolated where all the connections are opened at once - upon calling
> >> entityManager.getTransaction().commit();
> >>
> >> I assume this is correct from all the exampled I've seen so far...
> >>
> >> EntityManager entityManager =
> >> entityManagerFactory.createEntityManager(); // here is where just one
> >> connection is opened, as expected. (not using connection pool yet)
> >> entityManager.getTransaction().begin();
> >> for (MdBaseData bd : data) {
> >> em.persist(bd);
> >> }
> >> entityManager.getTransaction().commit(); // here is where all the
> >> connections are opened *****
> >> entityManager.close();
> >>
> >> Note that the entity has a M2M to child entities (about 100 per
> >> MdBaseData) via a link table.
> >>
> >> Thanks for any ideas,
> >>
> >> -Chris
> >>
> >> On Thu, Jan 31, 2013 at 1:56 PM, Kevin Sutter <kw...@gmail.com>
> wrote:
> >>> Hi Chris,
> >>> Good to know your plans. Thanks.
> >>>
> >>> You can get almost all of the JDBC connection access via the JDBC
> channel
> >>> in our logging framework [1]. Unfortunately, I looked at the code and
> the
> >>> constructors don't seem to have a log entry... :-( But, you do get
> all of
> >>> the closes, commits, rollbacks, etc -- all of the normal operations
> through
> >>> a Connection object. So, I'd start with that.
> >>>
> >>> Updating the Ctor for additional logging would be very easy to do. I
> would
> >>> probably do it in the LoggingConnectionDecorator, like you thought.
> If you
> >>> have issues with building a version of OpenJPA for your testing, ping
> me
> >>> back with the version of OpenJPA that you are using and maybe I can
> find
> >>> time to do a quick update. But, try the JDBC log channel first and
> see if
> >>> that gives you enough information for your specific scenario.
> >>>
> >>> Thanks,
> >>> Kevin
> >>>
> >>> [1]
> >>>
> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_logging_channels
> >>>
> >>> On Thu, Jan 31, 2013 at 12:02 PM, Chris Wolf <cw...@gmail.com>
> wrote:
> >>>
> >>>> I am fully aware that pooling is the way to go, but I want to just
> >>>> prove out the simple case first. Also the final deployment will be in
> >>>> a JEE container, so it will be doing the pooling.
> >>>>
> >>>> I really wish there was a logging setting to trace acquire/release of
> >>>> JDBC Connections - I looked at the source for (I forget now, something
> >>>> like JDBCDatStore) it had logging but not of acquire/release of
> >>>> Connections.
> >>>>
> >>>> What is this LoggingConnectionDecorator? would that help me log
> >>>> connection activity? If so, how is it configured?
> >>>>
> >>>> Thanks,
> >>>>
> >>>> -Chris
> >>>>
> >>>> On Thu, Jan 31, 2013 at 12:19 PM, Kevin Sutter <kw...@gmail.com>
> wrote:
> >>>> > Hi Chris,
> >>>> > Normally, OpenJPA will only request a connection "on demand" [1].
> As
> >>>> each
> >>>> > database access is requested, a connection is obtained, but then
> it's
> >>>> > released when we're done with it. Unless there is some processing
> or
> >>>> > configuration that is telling OpenJPA to hang onto the
> connection... If
> >>>> > nothing jumps out at you, I would suggest tracing (maybe both
> OpenJPA and
> >>>> > database) to see why all of the connections are getting requested
> and
> >>>> > nothing is getting closed.
> >>>> >
> >>>> > As an aside, I would highly recommend the use of some type of
> connection
> >>>> > pooling. Overall, you will get much better performance if
> connections
> >>>> can
> >>>> > be re-used instead of constantly dropping and re-creating
> connections.
> >>>> > Whether you use DBCP or Oracle pooling or some application server's
> >>>> > connection pooling mechanism, it doesn't really matter. But, I
> would
> >>>> > suggest using some connection pooling.
> >>>> >
> >>>> > Good luck,
> >>>> > Kevin
> >>>> >
> >>>> > [1]
> >>>> >
> >>>>
> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_dbsetup_retain
> >>>> >
> >>>> > On Thu, Jan 31, 2013 at 10:51 AM, Chris Wolf <cw...@gmail.com>
> >>>> wrote:
> >>>> >
> >>>> >> If I process a small number of records, everything works, however
> when
> >>>> >> I try to process a "real-world" number of records, I get an
> >>>> >> "ORA-12519". At first, I thought it was an Oracle issue and after
> >>>> >> searching around and getting hits on "solutions" involving
> increasing
> >>>> >> Oracle sessions and processes (there are at least 125 configured),
> I
> >>>> >> was still getting "ORA-12519". I then tailed the TNS listener log
> and
> >>>> >> saw that everytime my OpenJPA process ran, it would consume all the
> >>>> >> JDBC connections as if it was using connection pooling with some
> high
> >>>> >> min-connections setting.
> >>>> >>
> >>>> >> In fact, as the stack trace shows, it's only using
> >>>> >> "SimpleDriveDataSource", which I thought didn't pool connections
> and I
> >>>> >> don't have the DBCP jar on my classpath, so why is OpenJPA opening
> all
> >>>> >> these JDBC connections?
> >>>> >>
> >>>> >>
> >>>> >> Thanks,
> >>>> >>
> >>>> >>
> >>>> >> Chris
> >>>> >>
> >>>> >>
> >>>> >> 183 marketdata INFO [main] openjpa.Runtime - Starting OpenJPA
> 2.2.1
> >>>> >> 214 marketdata INFO [main] openjpa.jdbc.JDBC - Using dictionary
> >>>> >> class "org.apache.openjpa.jdbc.sql.OracleDictionary".
> >>>> >> Exception in thread "main" <openjpa-2.2.1-r422266:1396819 fatal
> store
> >>>> >> error> org.apache.openjpa.persistence.RollbackException: Listener
> >>>> >> refused the connection with the following error:
> >>>> >> ORA-12519, TNS:no appropriate service handler found
> >>>> >>
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:594)
> >>>> >> at
> ms.algo.adapt.test.BeanIODemo.saveToDB(BeanIODemo.java:153)
> >>>> >> at
> ms.algo.adapt.test.BeanIODemo.beanIOTest(BeanIODemo.java:127)
> >>>> >> at ms.algo.adapt.test.BeanIODemo.main(BeanIODemo.java:50)
> >>>> >> Caused by: <openjpa-2.2.1-r422266:1396819 fatal general error>
> >>>> >> org.apache.openjpa.persistence.PersistenceException: Listener
> refused
> >>>> >> the connection with the following error:
> >>>> >> ORA-12519, TNS:no appropriate service handler found
> >>>> >>
> >>>> >> at
> >>>> >>
> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4958)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4918)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:971)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getConnection(JDBCStoreManager.java:240)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.getConnection(AbstractJDBCSeq.java:163)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:217)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:201)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
> >>>> >> at
> >>>> >>
> org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:778)
> >>>> >> at
> >>>> >>
> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493)
> >>>> >> at
> >>>> >>
> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:762)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:600)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.kernel.SingleFieldManager.preFlushPC(SingleFieldManager.java:803)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.kernel.SingleFieldManager.preFlushPCs(SingleFieldManager.java:762)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:664)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:589)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:505)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:3028)
> >>>> >> at
> >>>> >> org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:44)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1042)
> >>>> >> at
> >>>> org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2114)
> >>>> >> at
> >>>> >>
> org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2074)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1992)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
> >>>> >> at
> >>>> >> org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1516)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:933)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:570)
> >>>> >> ... 3 more
> >>>> >> Caused by: java.sql.SQLException: Listener refused the connection
> with
> >>>> >> the following error:
> >>>> >> ORA-12519, TNS:no appropriate service handler found
> >>>> >>
> >>>> >> at
> >>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:517)
> >>>> >> at
> >>>> >>
> >>>>
> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:557)
> >>>> >> at
> >>>> oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:233)
> >>>> >> at
> >>>> >>
> >>>>
> oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:29)
> >>>> >> at
> >>>> oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:556)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getSimpleConnection(SimpleDriverDataSource.java:84)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.jdbc.schema.AutoDriverDataSource.getConnection(AutoDriverDataSource.java:39)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:76)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:93)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:304)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:982)
> >>>> >> at
> >>>> >>
> >>>>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:967)
> >>>> >> ... 31 more
> >>>> >> Caused by: oracle.net.ns.NetException: Listener refused the
> connection
> >>>> >> with the following error:
> >>>> >> ORA-12519, TNS:no appropriate service handler found
> >>>> >>
> >>>> >> at oracle.net.ns.NSProtocol.connect(NSProtocol.java:457)
> >>>> >> at
> >>>> >> oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1625)
> >>>> >> at
> >>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:365)
> >>>> >> ... 44 more
> >>>> >>
> >>>>
>
Re: Running out of JDBC connections with openjpa-2.1.0 + Oracle-11g
Posted by Chris Wolf <cw...@gmail.com>.
Kevin,
Someone on the users@apache.camel.org mailing list suggested that I
let Springframework manage the transactions, so I changed my code to
look like:
The apparent connection "leak" went away, and no exceptions are
thrown, and I only see one connection being opened, as expected -
however, nothing goes into the database.
static void saveToDB(List<MdBaseData> data) throws Exception {
EntityManagerFactory emf = Persistence
.createEntityManagerFactory("marketdata");
JpaTransactionManager jpaTxMgr = new JpaTransactionManager(emf);
EntityManager em =
jpaTxMgr.getEntityManagerFactory().createEntityManager();
TransactionStatus txStatus =
jpaTxMgr.getTransaction(new DefaultTransactionDefinition());
try {
//em.getTransaction().begin();
for (MdBaseData bd : data) {
em.persist(bd);
}
//em.getTransaction().commit();
jpaTxMgr.commit(txStatus);
} catch (Exception e) {
jpaTxMgr.rollback(txStatus);
e.printStackTrace();
} finally {
em.close();
}
}
On Thu, Jan 31, 2013 at 3:49 PM, Chris Wolf <cw...@gmail.com> wrote:
> Even more specifically, I drilled down into the guts of OpenJPA-2.1,
> into the call to
> EntityManager.getTransaction().begin();
>
> The place were all the connections are opened until exhausted is in:
>
> org.apache.openjpa.kernel.BrokerImpl.flush(int reason)
>
> There is a loop where the connections are all being opened:
>
> Collection mobjs = null;
> _flags |= FLAG_PRESTORING;
> try {
> if (flush) {
> // call pre store on all currently transactional objs
> for (Iterator itr = transactional.iterator();
> itr.hasNext();) //<=== EATING UP ALL CONNECTIONS
> ((StateManagerImpl) itr.next()).beforeFlush(reason, _call);
>
>
> Let me know if there's anything else I can to do help solve this issue.
>
> Thanks,
>
> -Chris
>
> On Thu, Jan 31, 2013 at 3:18 PM, Chris Wolf <cw...@gmail.com> wrote:
>> Kevin,
>>
>> I isolated where all the connections are opened at once - upon calling
>> entityManager.getTransaction().commit();
>>
>> I assume this is correct from all the exampled I've seen so far...
>>
>> EntityManager entityManager =
>> entityManagerFactory.createEntityManager(); // here is where just one
>> connection is opened, as expected. (not using connection pool yet)
>> entityManager.getTransaction().begin();
>> for (MdBaseData bd : data) {
>> em.persist(bd);
>> }
>> entityManager.getTransaction().commit(); // here is where all the
>> connections are opened *****
>> entityManager.close();
>>
>> Note that the entity has a M2M to child entities (about 100 per
>> MdBaseData) via a link table.
>>
>> Thanks for any ideas,
>>
>> -Chris
>>
>> On Thu, Jan 31, 2013 at 1:56 PM, Kevin Sutter <kw...@gmail.com> wrote:
>>> Hi Chris,
>>> Good to know your plans. Thanks.
>>>
>>> You can get almost all of the JDBC connection access via the JDBC channel
>>> in our logging framework [1]. Unfortunately, I looked at the code and the
>>> constructors don't seem to have a log entry... :-( But, you do get all of
>>> the closes, commits, rollbacks, etc -- all of the normal operations through
>>> a Connection object. So, I'd start with that.
>>>
>>> Updating the Ctor for additional logging would be very easy to do. I would
>>> probably do it in the LoggingConnectionDecorator, like you thought. If you
>>> have issues with building a version of OpenJPA for your testing, ping me
>>> back with the version of OpenJPA that you are using and maybe I can find
>>> time to do a quick update. But, try the JDBC log channel first and see if
>>> that gives you enough information for your specific scenario.
>>>
>>> Thanks,
>>> Kevin
>>>
>>> [1]
>>> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_logging_channels
>>>
>>> On Thu, Jan 31, 2013 at 12:02 PM, Chris Wolf <cw...@gmail.com> wrote:
>>>
>>>> I am fully aware that pooling is the way to go, but I want to just
>>>> prove out the simple case first. Also the final deployment will be in
>>>> a JEE container, so it will be doing the pooling.
>>>>
>>>> I really wish there was a logging setting to trace acquire/release of
>>>> JDBC Connections - I looked at the source for (I forget now, something
>>>> like JDBCDatStore) it had logging but not of acquire/release of
>>>> Connections.
>>>>
>>>> What is this LoggingConnectionDecorator? would that help me log
>>>> connection activity? If so, how is it configured?
>>>>
>>>> Thanks,
>>>>
>>>> -Chris
>>>>
>>>> On Thu, Jan 31, 2013 at 12:19 PM, Kevin Sutter <kw...@gmail.com> wrote:
>>>> > Hi Chris,
>>>> > Normally, OpenJPA will only request a connection "on demand" [1]. As
>>>> each
>>>> > database access is requested, a connection is obtained, but then it's
>>>> > released when we're done with it. Unless there is some processing or
>>>> > configuration that is telling OpenJPA to hang onto the connection... If
>>>> > nothing jumps out at you, I would suggest tracing (maybe both OpenJPA and
>>>> > database) to see why all of the connections are getting requested and
>>>> > nothing is getting closed.
>>>> >
>>>> > As an aside, I would highly recommend the use of some type of connection
>>>> > pooling. Overall, you will get much better performance if connections
>>>> can
>>>> > be re-used instead of constantly dropping and re-creating connections.
>>>> > Whether you use DBCP or Oracle pooling or some application server's
>>>> > connection pooling mechanism, it doesn't really matter. But, I would
>>>> > suggest using some connection pooling.
>>>> >
>>>> > Good luck,
>>>> > Kevin
>>>> >
>>>> > [1]
>>>> >
>>>> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_dbsetup_retain
>>>> >
>>>> > On Thu, Jan 31, 2013 at 10:51 AM, Chris Wolf <cw...@gmail.com>
>>>> wrote:
>>>> >
>>>> >> If I process a small number of records, everything works, however when
>>>> >> I try to process a "real-world" number of records, I get an
>>>> >> "ORA-12519". At first, I thought it was an Oracle issue and after
>>>> >> searching around and getting hits on "solutions" involving increasing
>>>> >> Oracle sessions and processes (there are at least 125 configured), I
>>>> >> was still getting "ORA-12519". I then tailed the TNS listener log and
>>>> >> saw that everytime my OpenJPA process ran, it would consume all the
>>>> >> JDBC connections as if it was using connection pooling with some high
>>>> >> min-connections setting.
>>>> >>
>>>> >> In fact, as the stack trace shows, it's only using
>>>> >> "SimpleDriveDataSource", which I thought didn't pool connections and I
>>>> >> don't have the DBCP jar on my classpath, so why is OpenJPA opening all
>>>> >> these JDBC connections?
>>>> >>
>>>> >>
>>>> >> Thanks,
>>>> >>
>>>> >>
>>>> >> Chris
>>>> >>
>>>> >>
>>>> >> 183 marketdata INFO [main] openjpa.Runtime - Starting OpenJPA 2.2.1
>>>> >> 214 marketdata INFO [main] openjpa.jdbc.JDBC - Using dictionary
>>>> >> class "org.apache.openjpa.jdbc.sql.OracleDictionary".
>>>> >> Exception in thread "main" <openjpa-2.2.1-r422266:1396819 fatal store
>>>> >> error> org.apache.openjpa.persistence.RollbackException: Listener
>>>> >> refused the connection with the following error:
>>>> >> ORA-12519, TNS:no appropriate service handler found
>>>> >>
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:594)
>>>> >> at ms.algo.adapt.test.BeanIODemo.saveToDB(BeanIODemo.java:153)
>>>> >> at ms.algo.adapt.test.BeanIODemo.beanIOTest(BeanIODemo.java:127)
>>>> >> at ms.algo.adapt.test.BeanIODemo.main(BeanIODemo.java:50)
>>>> >> Caused by: <openjpa-2.2.1-r422266:1396819 fatal general error>
>>>> >> org.apache.openjpa.persistence.PersistenceException: Listener refused
>>>> >> the connection with the following error:
>>>> >> ORA-12519, TNS:no appropriate service handler found
>>>> >>
>>>> >> at
>>>> >> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4958)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4918)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:971)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getConnection(JDBCStoreManager.java:240)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.getConnection(AbstractJDBCSeq.java:163)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:217)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:201)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
>>>> >> at
>>>> >> org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:778)
>>>> >> at
>>>> >> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493)
>>>> >> at
>>>> >> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:762)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:600)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPC(SingleFieldManager.java:803)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPCs(SingleFieldManager.java:762)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:664)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:589)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:505)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:3028)
>>>> >> at
>>>> >> org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:44)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1042)
>>>> >> at
>>>> org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2114)
>>>> >> at
>>>> >> org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2074)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1992)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
>>>> >> at
>>>> >> org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1516)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:933)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:570)
>>>> >> ... 3 more
>>>> >> Caused by: java.sql.SQLException: Listener refused the connection with
>>>> >> the following error:
>>>> >> ORA-12519, TNS:no appropriate service handler found
>>>> >>
>>>> >> at
>>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:517)
>>>> >> at
>>>> >>
>>>> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:557)
>>>> >> at
>>>> oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:233)
>>>> >> at
>>>> >>
>>>> oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:29)
>>>> >> at
>>>> oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:556)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getSimpleConnection(SimpleDriverDataSource.java:84)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.jdbc.schema.AutoDriverDataSource.getConnection(AutoDriverDataSource.java:39)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:76)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:93)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:304)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:982)
>>>> >> at
>>>> >>
>>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:967)
>>>> >> ... 31 more
>>>> >> Caused by: oracle.net.ns.NetException: Listener refused the connection
>>>> >> with the following error:
>>>> >> ORA-12519, TNS:no appropriate service handler found
>>>> >>
>>>> >> at oracle.net.ns.NSProtocol.connect(NSProtocol.java:457)
>>>> >> at
>>>> >> oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1625)
>>>> >> at
>>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:365)
>>>> >> ... 44 more
>>>> >>
>>>>
Re: Running out of JDBC connections with openjpa-2.1.0 + Oracle-11g
Posted by Chris Wolf <cw...@gmail.com>.
Even more specifically, I drilled down into the guts of OpenJPA-2.1,
into the call to
EntityManager.getTransaction().begin();
The place were all the connections are opened until exhausted is in:
org.apache.openjpa.kernel.BrokerImpl.flush(int reason)
There is a loop where the connections are all being opened:
Collection mobjs = null;
_flags |= FLAG_PRESTORING;
try {
if (flush) {
// call pre store on all currently transactional objs
for (Iterator itr = transactional.iterator();
itr.hasNext();) //<=== EATING UP ALL CONNECTIONS
((StateManagerImpl) itr.next()).beforeFlush(reason, _call);
Let me know if there's anything else I can to do help solve this issue.
Thanks,
-Chris
On Thu, Jan 31, 2013 at 3:18 PM, Chris Wolf <cw...@gmail.com> wrote:
> Kevin,
>
> I isolated where all the connections are opened at once - upon calling
> entityManager.getTransaction().commit();
>
> I assume this is correct from all the exampled I've seen so far...
>
> EntityManager entityManager =
> entityManagerFactory.createEntityManager(); // here is where just one
> connection is opened, as expected. (not using connection pool yet)
> entityManager.getTransaction().begin();
> for (MdBaseData bd : data) {
> em.persist(bd);
> }
> entityManager.getTransaction().commit(); // here is where all the
> connections are opened *****
> entityManager.close();
>
> Note that the entity has a M2M to child entities (about 100 per
> MdBaseData) via a link table.
>
> Thanks for any ideas,
>
> -Chris
>
> On Thu, Jan 31, 2013 at 1:56 PM, Kevin Sutter <kw...@gmail.com> wrote:
>> Hi Chris,
>> Good to know your plans. Thanks.
>>
>> You can get almost all of the JDBC connection access via the JDBC channel
>> in our logging framework [1]. Unfortunately, I looked at the code and the
>> constructors don't seem to have a log entry... :-( But, you do get all of
>> the closes, commits, rollbacks, etc -- all of the normal operations through
>> a Connection object. So, I'd start with that.
>>
>> Updating the Ctor for additional logging would be very easy to do. I would
>> probably do it in the LoggingConnectionDecorator, like you thought. If you
>> have issues with building a version of OpenJPA for your testing, ping me
>> back with the version of OpenJPA that you are using and maybe I can find
>> time to do a quick update. But, try the JDBC log channel first and see if
>> that gives you enough information for your specific scenario.
>>
>> Thanks,
>> Kevin
>>
>> [1]
>> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_logging_channels
>>
>> On Thu, Jan 31, 2013 at 12:02 PM, Chris Wolf <cw...@gmail.com> wrote:
>>
>>> I am fully aware that pooling is the way to go, but I want to just
>>> prove out the simple case first. Also the final deployment will be in
>>> a JEE container, so it will be doing the pooling.
>>>
>>> I really wish there was a logging setting to trace acquire/release of
>>> JDBC Connections - I looked at the source for (I forget now, something
>>> like JDBCDatStore) it had logging but not of acquire/release of
>>> Connections.
>>>
>>> What is this LoggingConnectionDecorator? would that help me log
>>> connection activity? If so, how is it configured?
>>>
>>> Thanks,
>>>
>>> -Chris
>>>
>>> On Thu, Jan 31, 2013 at 12:19 PM, Kevin Sutter <kw...@gmail.com> wrote:
>>> > Hi Chris,
>>> > Normally, OpenJPA will only request a connection "on demand" [1]. As
>>> each
>>> > database access is requested, a connection is obtained, but then it's
>>> > released when we're done with it. Unless there is some processing or
>>> > configuration that is telling OpenJPA to hang onto the connection... If
>>> > nothing jumps out at you, I would suggest tracing (maybe both OpenJPA and
>>> > database) to see why all of the connections are getting requested and
>>> > nothing is getting closed.
>>> >
>>> > As an aside, I would highly recommend the use of some type of connection
>>> > pooling. Overall, you will get much better performance if connections
>>> can
>>> > be re-used instead of constantly dropping and re-creating connections.
>>> > Whether you use DBCP or Oracle pooling or some application server's
>>> > connection pooling mechanism, it doesn't really matter. But, I would
>>> > suggest using some connection pooling.
>>> >
>>> > Good luck,
>>> > Kevin
>>> >
>>> > [1]
>>> >
>>> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_dbsetup_retain
>>> >
>>> > On Thu, Jan 31, 2013 at 10:51 AM, Chris Wolf <cw...@gmail.com>
>>> wrote:
>>> >
>>> >> If I process a small number of records, everything works, however when
>>> >> I try to process a "real-world" number of records, I get an
>>> >> "ORA-12519". At first, I thought it was an Oracle issue and after
>>> >> searching around and getting hits on "solutions" involving increasing
>>> >> Oracle sessions and processes (there are at least 125 configured), I
>>> >> was still getting "ORA-12519". I then tailed the TNS listener log and
>>> >> saw that everytime my OpenJPA process ran, it would consume all the
>>> >> JDBC connections as if it was using connection pooling with some high
>>> >> min-connections setting.
>>> >>
>>> >> In fact, as the stack trace shows, it's only using
>>> >> "SimpleDriveDataSource", which I thought didn't pool connections and I
>>> >> don't have the DBCP jar on my classpath, so why is OpenJPA opening all
>>> >> these JDBC connections?
>>> >>
>>> >>
>>> >> Thanks,
>>> >>
>>> >>
>>> >> Chris
>>> >>
>>> >>
>>> >> 183 marketdata INFO [main] openjpa.Runtime - Starting OpenJPA 2.2.1
>>> >> 214 marketdata INFO [main] openjpa.jdbc.JDBC - Using dictionary
>>> >> class "org.apache.openjpa.jdbc.sql.OracleDictionary".
>>> >> Exception in thread "main" <openjpa-2.2.1-r422266:1396819 fatal store
>>> >> error> org.apache.openjpa.persistence.RollbackException: Listener
>>> >> refused the connection with the following error:
>>> >> ORA-12519, TNS:no appropriate service handler found
>>> >>
>>> >> at
>>> >>
>>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:594)
>>> >> at ms.algo.adapt.test.BeanIODemo.saveToDB(BeanIODemo.java:153)
>>> >> at ms.algo.adapt.test.BeanIODemo.beanIOTest(BeanIODemo.java:127)
>>> >> at ms.algo.adapt.test.BeanIODemo.main(BeanIODemo.java:50)
>>> >> Caused by: <openjpa-2.2.1-r422266:1396819 fatal general error>
>>> >> org.apache.openjpa.persistence.PersistenceException: Listener refused
>>> >> the connection with the following error:
>>> >> ORA-12519, TNS:no appropriate service handler found
>>> >>
>>> >> at
>>> >> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4958)
>>> >> at
>>> >>
>>> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4918)
>>> >> at
>>> >>
>>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
>>> >> at
>>> >>
>>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
>>> >> at
>>> >>
>>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
>>> >> at
>>> >>
>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:971)
>>> >> at
>>> >>
>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getConnection(JDBCStoreManager.java:240)
>>> >> at
>>> >>
>>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.getConnection(AbstractJDBCSeq.java:163)
>>> >> at
>>> >>
>>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:217)
>>> >> at
>>> >>
>>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:201)
>>> >> at
>>> >>
>>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
>>> >> at
>>> >> org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
>>> >> at
>>> >>
>>> org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
>>> >> at
>>> >>
>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:778)
>>> >> at
>>> >> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493)
>>> >> at
>>> >> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469)
>>> >> at
>>> >>
>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:762)
>>> >> at
>>> >>
>>> org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135)
>>> >> at
>>> >>
>>> org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:600)
>>> >> at
>>> >>
>>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPC(SingleFieldManager.java:803)
>>> >> at
>>> >>
>>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPCs(SingleFieldManager.java:762)
>>> >> at
>>> >>
>>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:664)
>>> >> at
>>> >>
>>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:589)
>>> >> at
>>> >>
>>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:505)
>>> >> at
>>> >>
>>> org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:3028)
>>> >> at
>>> >> org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:44)
>>> >> at
>>> >>
>>> org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1042)
>>> >> at
>>> org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2114)
>>> >> at
>>> >> org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2074)
>>> >> at
>>> >>
>>> org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1992)
>>> >> at
>>> >>
>>> org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
>>> >> at
>>> >> org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1516)
>>> >> at
>>> >>
>>> org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:933)
>>> >> at
>>> >>
>>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:570)
>>> >> ... 3 more
>>> >> Caused by: java.sql.SQLException: Listener refused the connection with
>>> >> the following error:
>>> >> ORA-12519, TNS:no appropriate service handler found
>>> >>
>>> >> at
>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:517)
>>> >> at
>>> >>
>>> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:557)
>>> >> at
>>> oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:233)
>>> >> at
>>> >>
>>> oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:29)
>>> >> at
>>> oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:556)
>>> >> at
>>> >>
>>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getSimpleConnection(SimpleDriverDataSource.java:84)
>>> >> at
>>> >>
>>> org.apache.openjpa.jdbc.schema.AutoDriverDataSource.getConnection(AutoDriverDataSource.java:39)
>>> >> at
>>> >>
>>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:76)
>>> >> at
>>> >>
>>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>>> >> at
>>> >>
>>> org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:93)
>>> >> at
>>> >>
>>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>>> >> at
>>> >>
>>> org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:304)
>>> >> at
>>> >>
>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:982)
>>> >> at
>>> >>
>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:967)
>>> >> ... 31 more
>>> >> Caused by: oracle.net.ns.NetException: Listener refused the connection
>>> >> with the following error:
>>> >> ORA-12519, TNS:no appropriate service handler found
>>> >>
>>> >> at oracle.net.ns.NSProtocol.connect(NSProtocol.java:457)
>>> >> at
>>> >> oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1625)
>>> >> at
>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:365)
>>> >> ... 44 more
>>> >>
>>>
Re: Running out of JDBC connections with openjpa-2.1.0 + Oracle-11g
Posted by Chris Wolf <cw...@gmail.com>.
Kevin,
I isolated where all the connections are opened at once - upon calling
entityManager.getTransaction().commit();
I assume this is correct from all the exampled I've seen so far...
EntityManager entityManager =
entityManagerFactory.createEntityManager(); // here is where just one
connection is opened, as expected. (not using connection pool yet)
entityManager.getTransaction().begin();
for (MdBaseData bd : data) {
em.persist(bd);
}
entityManager.getTransaction().commit(); // here is where all the
connections are opened *****
entityManager.close();
Note that the entity has a M2M to child entities (about 100 per
MdBaseData) via a link table.
Thanks for any ideas,
-Chris
On Thu, Jan 31, 2013 at 1:56 PM, Kevin Sutter <kw...@gmail.com> wrote:
> Hi Chris,
> Good to know your plans. Thanks.
>
> You can get almost all of the JDBC connection access via the JDBC channel
> in our logging framework [1]. Unfortunately, I looked at the code and the
> constructors don't seem to have a log entry... :-( But, you do get all of
> the closes, commits, rollbacks, etc -- all of the normal operations through
> a Connection object. So, I'd start with that.
>
> Updating the Ctor for additional logging would be very easy to do. I would
> probably do it in the LoggingConnectionDecorator, like you thought. If you
> have issues with building a version of OpenJPA for your testing, ping me
> back with the version of OpenJPA that you are using and maybe I can find
> time to do a quick update. But, try the JDBC log channel first and see if
> that gives you enough information for your specific scenario.
>
> Thanks,
> Kevin
>
> [1]
> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_logging_channels
>
> On Thu, Jan 31, 2013 at 12:02 PM, Chris Wolf <cw...@gmail.com> wrote:
>
>> I am fully aware that pooling is the way to go, but I want to just
>> prove out the simple case first. Also the final deployment will be in
>> a JEE container, so it will be doing the pooling.
>>
>> I really wish there was a logging setting to trace acquire/release of
>> JDBC Connections - I looked at the source for (I forget now, something
>> like JDBCDatStore) it had logging but not of acquire/release of
>> Connections.
>>
>> What is this LoggingConnectionDecorator? would that help me log
>> connection activity? If so, how is it configured?
>>
>> Thanks,
>>
>> -Chris
>>
>> On Thu, Jan 31, 2013 at 12:19 PM, Kevin Sutter <kw...@gmail.com> wrote:
>> > Hi Chris,
>> > Normally, OpenJPA will only request a connection "on demand" [1]. As
>> each
>> > database access is requested, a connection is obtained, but then it's
>> > released when we're done with it. Unless there is some processing or
>> > configuration that is telling OpenJPA to hang onto the connection... If
>> > nothing jumps out at you, I would suggest tracing (maybe both OpenJPA and
>> > database) to see why all of the connections are getting requested and
>> > nothing is getting closed.
>> >
>> > As an aside, I would highly recommend the use of some type of connection
>> > pooling. Overall, you will get much better performance if connections
>> can
>> > be re-used instead of constantly dropping and re-creating connections.
>> > Whether you use DBCP or Oracle pooling or some application server's
>> > connection pooling mechanism, it doesn't really matter. But, I would
>> > suggest using some connection pooling.
>> >
>> > Good luck,
>> > Kevin
>> >
>> > [1]
>> >
>> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_dbsetup_retain
>> >
>> > On Thu, Jan 31, 2013 at 10:51 AM, Chris Wolf <cw...@gmail.com>
>> wrote:
>> >
>> >> If I process a small number of records, everything works, however when
>> >> I try to process a "real-world" number of records, I get an
>> >> "ORA-12519". At first, I thought it was an Oracle issue and after
>> >> searching around and getting hits on "solutions" involving increasing
>> >> Oracle sessions and processes (there are at least 125 configured), I
>> >> was still getting "ORA-12519". I then tailed the TNS listener log and
>> >> saw that everytime my OpenJPA process ran, it would consume all the
>> >> JDBC connections as if it was using connection pooling with some high
>> >> min-connections setting.
>> >>
>> >> In fact, as the stack trace shows, it's only using
>> >> "SimpleDriveDataSource", which I thought didn't pool connections and I
>> >> don't have the DBCP jar on my classpath, so why is OpenJPA opening all
>> >> these JDBC connections?
>> >>
>> >>
>> >> Thanks,
>> >>
>> >>
>> >> Chris
>> >>
>> >>
>> >> 183 marketdata INFO [main] openjpa.Runtime - Starting OpenJPA 2.2.1
>> >> 214 marketdata INFO [main] openjpa.jdbc.JDBC - Using dictionary
>> >> class "org.apache.openjpa.jdbc.sql.OracleDictionary".
>> >> Exception in thread "main" <openjpa-2.2.1-r422266:1396819 fatal store
>> >> error> org.apache.openjpa.persistence.RollbackException: Listener
>> >> refused the connection with the following error:
>> >> ORA-12519, TNS:no appropriate service handler found
>> >>
>> >> at
>> >>
>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:594)
>> >> at ms.algo.adapt.test.BeanIODemo.saveToDB(BeanIODemo.java:153)
>> >> at ms.algo.adapt.test.BeanIODemo.beanIOTest(BeanIODemo.java:127)
>> >> at ms.algo.adapt.test.BeanIODemo.main(BeanIODemo.java:50)
>> >> Caused by: <openjpa-2.2.1-r422266:1396819 fatal general error>
>> >> org.apache.openjpa.persistence.PersistenceException: Listener refused
>> >> the connection with the following error:
>> >> ORA-12519, TNS:no appropriate service handler found
>> >>
>> >> at
>> >> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4958)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4918)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:971)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getConnection(JDBCStoreManager.java:240)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.getConnection(AbstractJDBCSeq.java:163)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:217)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:201)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
>> >> at
>> >> org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
>> >> at
>> >>
>> org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:778)
>> >> at
>> >> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493)
>> >> at
>> >> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:762)
>> >> at
>> >>
>> org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135)
>> >> at
>> >>
>> org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:600)
>> >> at
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPC(SingleFieldManager.java:803)
>> >> at
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPCs(SingleFieldManager.java:762)
>> >> at
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:664)
>> >> at
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:589)
>> >> at
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:505)
>> >> at
>> >>
>> org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:3028)
>> >> at
>> >> org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:44)
>> >> at
>> >>
>> org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1042)
>> >> at
>> org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2114)
>> >> at
>> >> org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2074)
>> >> at
>> >>
>> org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1992)
>> >> at
>> >>
>> org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
>> >> at
>> >> org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1516)
>> >> at
>> >>
>> org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:933)
>> >> at
>> >>
>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:570)
>> >> ... 3 more
>> >> Caused by: java.sql.SQLException: Listener refused the connection with
>> >> the following error:
>> >> ORA-12519, TNS:no appropriate service handler found
>> >>
>> >> at
>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:517)
>> >> at
>> >>
>> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:557)
>> >> at
>> oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:233)
>> >> at
>> >>
>> oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:29)
>> >> at
>> oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:556)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getSimpleConnection(SimpleDriverDataSource.java:84)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.schema.AutoDriverDataSource.getConnection(AutoDriverDataSource.java:39)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:76)
>> >> at
>> >>
>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>> >> at
>> >>
>> org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:93)
>> >> at
>> >>
>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:304)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:982)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:967)
>> >> ... 31 more
>> >> Caused by: oracle.net.ns.NetException: Listener refused the connection
>> >> with the following error:
>> >> ORA-12519, TNS:no appropriate service handler found
>> >>
>> >> at oracle.net.ns.NSProtocol.connect(NSProtocol.java:457)
>> >> at
>> >> oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1625)
>> >> at
>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:365)
>> >> ... 44 more
>> >>
>>
Re: Running out of JDBC connections with openjpa-2.1.0 + Oracle-11g
Posted by Chris Wolf <cw...@gmail.com>.
Kevin,
I'm not having any luck getting any of that to work. The rest of the
code in my project is using slf4j with a log4j backend, so I added:
<property name="openjpa.Log" value="slf4j"/>
...to my persistence.xml, and...
log4j.category.openjpa.Tool=INFO
log4j.category.openjpa.Runtime=INFO
log4j.category.openjpa.Remote=WARN
log4j.category.openjpa.DataCache=WARN
log4j.category.openjpa.MetaData=WARN
log4j.category.openjpa.Enhance=WARN
log4j.category.openjpa.Query=WARN
log4j.category.openjpa.jdbc.SQL=WARN
log4j.category.openjpa.jdbc.SQLDiag=WARN
log4j.category.openjpa.jdbc.JDBC=TRACE
log4j.category.openjpa.jdbc.Schema=WARN
...to my already existing log4j.properties, which is on the classpath.
These jars are on the classpath:
slf4j-api-1.6.6.jar
slf4j-log4j12-1.6.6.jar
log4j-1.2.17.jar
The only output I see is the usual:
[main] openjpa.Runtime INFO OpenJPA dynamically loaded
the class enhancer. Any classes that were not enhanced at build time
will be enhanced when they are loaded by the JVM.
[main] openjpa.Runtime INFO Starting OpenJPA 2.2.1
[main] openjpa.jdbc.JDBC INFO Using dictionary class
"org.apache.openjpa.jdbc.sql.OracleDictionary".
Any ideas?
Thanks,
-Chris
On Thu, Jan 31, 2013 at 1:56 PM, Kevin Sutter <kw...@gmail.com> wrote:
> Hi Chris,
> Good to know your plans. Thanks.
>
> You can get almost all of the JDBC connection access via the JDBC channel
> in our logging framework [1]. Unfortunately, I looked at the code and the
> constructors don't seem to have a log entry... :-( But, you do get all of
> the closes, commits, rollbacks, etc -- all of the normal operations through
> a Connection object. So, I'd start with that.
>
> Updating the Ctor for additional logging would be very easy to do. I would
> probably do it in the LoggingConnectionDecorator, like you thought. If you
> have issues with building a version of OpenJPA for your testing, ping me
> back with the version of OpenJPA that you are using and maybe I can find
> time to do a quick update. But, try the JDBC log channel first and see if
> that gives you enough information for your specific scenario.
>
> Thanks,
> Kevin
>
> [1]
> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_logging_channels
>
> On Thu, Jan 31, 2013 at 12:02 PM, Chris Wolf <cw...@gmail.com> wrote:
>
>> I am fully aware that pooling is the way to go, but I want to just
>> prove out the simple case first. Also the final deployment will be in
>> a JEE container, so it will be doing the pooling.
>>
>> I really wish there was a logging setting to trace acquire/release of
>> JDBC Connections - I looked at the source for (I forget now, something
>> like JDBCDatStore) it had logging but not of acquire/release of
>> Connections.
>>
>> What is this LoggingConnectionDecorator? would that help me log
>> connection activity? If so, how is it configured?
>>
>> Thanks,
>>
>> -Chris
>>
>> On Thu, Jan 31, 2013 at 12:19 PM, Kevin Sutter <kw...@gmail.com> wrote:
>> > Hi Chris,
>> > Normally, OpenJPA will only request a connection "on demand" [1]. As
>> each
>> > database access is requested, a connection is obtained, but then it's
>> > released when we're done with it. Unless there is some processing or
>> > configuration that is telling OpenJPA to hang onto the connection... If
>> > nothing jumps out at you, I would suggest tracing (maybe both OpenJPA and
>> > database) to see why all of the connections are getting requested and
>> > nothing is getting closed.
>> >
>> > As an aside, I would highly recommend the use of some type of connection
>> > pooling. Overall, you will get much better performance if connections
>> can
>> > be re-used instead of constantly dropping and re-creating connections.
>> > Whether you use DBCP or Oracle pooling or some application server's
>> > connection pooling mechanism, it doesn't really matter. But, I would
>> > suggest using some connection pooling.
>> >
>> > Good luck,
>> > Kevin
>> >
>> > [1]
>> >
>> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_dbsetup_retain
>> >
>> > On Thu, Jan 31, 2013 at 10:51 AM, Chris Wolf <cw...@gmail.com>
>> wrote:
>> >
>> >> If I process a small number of records, everything works, however when
>> >> I try to process a "real-world" number of records, I get an
>> >> "ORA-12519". At first, I thought it was an Oracle issue and after
>> >> searching around and getting hits on "solutions" involving increasing
>> >> Oracle sessions and processes (there are at least 125 configured), I
>> >> was still getting "ORA-12519". I then tailed the TNS listener log and
>> >> saw that everytime my OpenJPA process ran, it would consume all the
>> >> JDBC connections as if it was using connection pooling with some high
>> >> min-connections setting.
>> >>
>> >> In fact, as the stack trace shows, it's only using
>> >> "SimpleDriveDataSource", which I thought didn't pool connections and I
>> >> don't have the DBCP jar on my classpath, so why is OpenJPA opening all
>> >> these JDBC connections?
>> >>
>> >>
>> >> Thanks,
>> >>
>> >>
>> >> Chris
>> >>
>> >>
>> >> 183 marketdata INFO [main] openjpa.Runtime - Starting OpenJPA 2.2.1
>> >> 214 marketdata INFO [main] openjpa.jdbc.JDBC - Using dictionary
>> >> class "org.apache.openjpa.jdbc.sql.OracleDictionary".
>> >> Exception in thread "main" <openjpa-2.2.1-r422266:1396819 fatal store
>> >> error> org.apache.openjpa.persistence.RollbackException: Listener
>> >> refused the connection with the following error:
>> >> ORA-12519, TNS:no appropriate service handler found
>> >>
>> >> at
>> >>
>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:594)
>> >> at ms.algo.adapt.test.BeanIODemo.saveToDB(BeanIODemo.java:153)
>> >> at ms.algo.adapt.test.BeanIODemo.beanIOTest(BeanIODemo.java:127)
>> >> at ms.algo.adapt.test.BeanIODemo.main(BeanIODemo.java:50)
>> >> Caused by: <openjpa-2.2.1-r422266:1396819 fatal general error>
>> >> org.apache.openjpa.persistence.PersistenceException: Listener refused
>> >> the connection with the following error:
>> >> ORA-12519, TNS:no appropriate service handler found
>> >>
>> >> at
>> >> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4958)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4918)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:971)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getConnection(JDBCStoreManager.java:240)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.getConnection(AbstractJDBCSeq.java:163)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:217)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:201)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
>> >> at
>> >> org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
>> >> at
>> >>
>> org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:778)
>> >> at
>> >> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493)
>> >> at
>> >> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:762)
>> >> at
>> >>
>> org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135)
>> >> at
>> >>
>> org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:600)
>> >> at
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPC(SingleFieldManager.java:803)
>> >> at
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPCs(SingleFieldManager.java:762)
>> >> at
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:664)
>> >> at
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:589)
>> >> at
>> >>
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:505)
>> >> at
>> >>
>> org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:3028)
>> >> at
>> >> org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:44)
>> >> at
>> >>
>> org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1042)
>> >> at
>> org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2114)
>> >> at
>> >> org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2074)
>> >> at
>> >>
>> org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1992)
>> >> at
>> >>
>> org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
>> >> at
>> >> org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1516)
>> >> at
>> >>
>> org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:933)
>> >> at
>> >>
>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:570)
>> >> ... 3 more
>> >> Caused by: java.sql.SQLException: Listener refused the connection with
>> >> the following error:
>> >> ORA-12519, TNS:no appropriate service handler found
>> >>
>> >> at
>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:517)
>> >> at
>> >>
>> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:557)
>> >> at
>> oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:233)
>> >> at
>> >>
>> oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:29)
>> >> at
>> oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:556)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getSimpleConnection(SimpleDriverDataSource.java:84)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.schema.AutoDriverDataSource.getConnection(AutoDriverDataSource.java:39)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:76)
>> >> at
>> >>
>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>> >> at
>> >>
>> org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:93)
>> >> at
>> >>
>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:304)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:982)
>> >> at
>> >>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:967)
>> >> ... 31 more
>> >> Caused by: oracle.net.ns.NetException: Listener refused the connection
>> >> with the following error:
>> >> ORA-12519, TNS:no appropriate service handler found
>> >>
>> >> at oracle.net.ns.NSProtocol.connect(NSProtocol.java:457)
>> >> at
>> >> oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1625)
>> >> at
>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:365)
>> >> ... 44 more
>> >>
>>
Re: Running out of JDBC connections with openjpa-2.1.0 + Oracle-11g
Posted by Kevin Sutter <kw...@gmail.com>.
Hi Chris,
Good to know your plans. Thanks.
You can get almost all of the JDBC connection access via the JDBC channel
in our logging framework [1]. Unfortunately, I looked at the code and the
constructors don't seem to have a log entry... :-( But, you do get all of
the closes, commits, rollbacks, etc -- all of the normal operations through
a Connection object. So, I'd start with that.
Updating the Ctor for additional logging would be very easy to do. I would
probably do it in the LoggingConnectionDecorator, like you thought. If you
have issues with building a version of OpenJPA for your testing, ping me
back with the version of OpenJPA that you are using and maybe I can find
time to do a quick update. But, try the JDBC log channel first and see if
that gives you enough information for your specific scenario.
Thanks,
Kevin
[1]
http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_logging_channels
On Thu, Jan 31, 2013 at 12:02 PM, Chris Wolf <cw...@gmail.com> wrote:
> I am fully aware that pooling is the way to go, but I want to just
> prove out the simple case first. Also the final deployment will be in
> a JEE container, so it will be doing the pooling.
>
> I really wish there was a logging setting to trace acquire/release of
> JDBC Connections - I looked at the source for (I forget now, something
> like JDBCDatStore) it had logging but not of acquire/release of
> Connections.
>
> What is this LoggingConnectionDecorator? would that help me log
> connection activity? If so, how is it configured?
>
> Thanks,
>
> -Chris
>
> On Thu, Jan 31, 2013 at 12:19 PM, Kevin Sutter <kw...@gmail.com> wrote:
> > Hi Chris,
> > Normally, OpenJPA will only request a connection "on demand" [1]. As
> each
> > database access is requested, a connection is obtained, but then it's
> > released when we're done with it. Unless there is some processing or
> > configuration that is telling OpenJPA to hang onto the connection... If
> > nothing jumps out at you, I would suggest tracing (maybe both OpenJPA and
> > database) to see why all of the connections are getting requested and
> > nothing is getting closed.
> >
> > As an aside, I would highly recommend the use of some type of connection
> > pooling. Overall, you will get much better performance if connections
> can
> > be re-used instead of constantly dropping and re-creating connections.
> > Whether you use DBCP or Oracle pooling or some application server's
> > connection pooling mechanism, it doesn't really matter. But, I would
> > suggest using some connection pooling.
> >
> > Good luck,
> > Kevin
> >
> > [1]
> >
> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_dbsetup_retain
> >
> > On Thu, Jan 31, 2013 at 10:51 AM, Chris Wolf <cw...@gmail.com>
> wrote:
> >
> >> If I process a small number of records, everything works, however when
> >> I try to process a "real-world" number of records, I get an
> >> "ORA-12519". At first, I thought it was an Oracle issue and after
> >> searching around and getting hits on "solutions" involving increasing
> >> Oracle sessions and processes (there are at least 125 configured), I
> >> was still getting "ORA-12519". I then tailed the TNS listener log and
> >> saw that everytime my OpenJPA process ran, it would consume all the
> >> JDBC connections as if it was using connection pooling with some high
> >> min-connections setting.
> >>
> >> In fact, as the stack trace shows, it's only using
> >> "SimpleDriveDataSource", which I thought didn't pool connections and I
> >> don't have the DBCP jar on my classpath, so why is OpenJPA opening all
> >> these JDBC connections?
> >>
> >>
> >> Thanks,
> >>
> >>
> >> Chris
> >>
> >>
> >> 183 marketdata INFO [main] openjpa.Runtime - Starting OpenJPA 2.2.1
> >> 214 marketdata INFO [main] openjpa.jdbc.JDBC - Using dictionary
> >> class "org.apache.openjpa.jdbc.sql.OracleDictionary".
> >> Exception in thread "main" <openjpa-2.2.1-r422266:1396819 fatal store
> >> error> org.apache.openjpa.persistence.RollbackException: Listener
> >> refused the connection with the following error:
> >> ORA-12519, TNS:no appropriate service handler found
> >>
> >> at
> >>
> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:594)
> >> at ms.algo.adapt.test.BeanIODemo.saveToDB(BeanIODemo.java:153)
> >> at ms.algo.adapt.test.BeanIODemo.beanIOTest(BeanIODemo.java:127)
> >> at ms.algo.adapt.test.BeanIODemo.main(BeanIODemo.java:50)
> >> Caused by: <openjpa-2.2.1-r422266:1396819 fatal general error>
> >> org.apache.openjpa.persistence.PersistenceException: Listener refused
> >> the connection with the following error:
> >> ORA-12519, TNS:no appropriate service handler found
> >>
> >> at
> >> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4958)
> >> at
> >>
> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4918)
> >> at
> >>
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
> >> at
> >>
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
> >> at
> >>
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
> >> at
> >>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:971)
> >> at
> >>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getConnection(JDBCStoreManager.java:240)
> >> at
> >>
> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.getConnection(AbstractJDBCSeq.java:163)
> >> at
> >>
> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:217)
> >> at
> >>
> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:201)
> >> at
> >>
> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
> >> at
> >> org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
> >> at
> >>
> org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
> >> at
> >>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:778)
> >> at
> >> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493)
> >> at
> >> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469)
> >> at
> >>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:762)
> >> at
> >>
> org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135)
> >> at
> >>
> org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:600)
> >> at
> >>
> org.apache.openjpa.kernel.SingleFieldManager.preFlushPC(SingleFieldManager.java:803)
> >> at
> >>
> org.apache.openjpa.kernel.SingleFieldManager.preFlushPCs(SingleFieldManager.java:762)
> >> at
> >>
> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:664)
> >> at
> >>
> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:589)
> >> at
> >>
> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:505)
> >> at
> >>
> org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:3028)
> >> at
> >> org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:44)
> >> at
> >>
> org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1042)
> >> at
> org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2114)
> >> at
> >> org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2074)
> >> at
> >>
> org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1992)
> >> at
> >>
> org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
> >> at
> >> org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1516)
> >> at
> >>
> org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:933)
> >> at
> >>
> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:570)
> >> ... 3 more
> >> Caused by: java.sql.SQLException: Listener refused the connection with
> >> the following error:
> >> ORA-12519, TNS:no appropriate service handler found
> >>
> >> at
> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:517)
> >> at
> >>
> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:557)
> >> at
> oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:233)
> >> at
> >>
> oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:29)
> >> at
> oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:556)
> >> at
> >>
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getSimpleConnection(SimpleDriverDataSource.java:84)
> >> at
> >>
> org.apache.openjpa.jdbc.schema.AutoDriverDataSource.getConnection(AutoDriverDataSource.java:39)
> >> at
> >>
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:76)
> >> at
> >>
> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
> >> at
> >>
> org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:93)
> >> at
> >>
> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
> >> at
> >>
> org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:304)
> >> at
> >>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:982)
> >> at
> >>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:967)
> >> ... 31 more
> >> Caused by: oracle.net.ns.NetException: Listener refused the connection
> >> with the following error:
> >> ORA-12519, TNS:no appropriate service handler found
> >>
> >> at oracle.net.ns.NSProtocol.connect(NSProtocol.java:457)
> >> at
> >> oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1625)
> >> at
> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:365)
> >> ... 44 more
> >>
>
Re: Running out of JDBC connections with openjpa-2.1.0 + Oracle-11g
Posted by Chris Wolf <cw...@gmail.com>.
I am fully aware that pooling is the way to go, but I want to just
prove out the simple case first. Also the final deployment will be in
a JEE container, so it will be doing the pooling.
I really wish there was a logging setting to trace acquire/release of
JDBC Connections - I looked at the source for (I forget now, something
like JDBCDatStore) it had logging but not of acquire/release of
Connections.
What is this LoggingConnectionDecorator? would that help me log
connection activity? If so, how is it configured?
Thanks,
-Chris
On Thu, Jan 31, 2013 at 12:19 PM, Kevin Sutter <kw...@gmail.com> wrote:
> Hi Chris,
> Normally, OpenJPA will only request a connection "on demand" [1]. As each
> database access is requested, a connection is obtained, but then it's
> released when we're done with it. Unless there is some processing or
> configuration that is telling OpenJPA to hang onto the connection... If
> nothing jumps out at you, I would suggest tracing (maybe both OpenJPA and
> database) to see why all of the connections are getting requested and
> nothing is getting closed.
>
> As an aside, I would highly recommend the use of some type of connection
> pooling. Overall, you will get much better performance if connections can
> be re-used instead of constantly dropping and re-creating connections.
> Whether you use DBCP or Oracle pooling or some application server's
> connection pooling mechanism, it doesn't really matter. But, I would
> suggest using some connection pooling.
>
> Good luck,
> Kevin
>
> [1]
> http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_dbsetup_retain
>
> On Thu, Jan 31, 2013 at 10:51 AM, Chris Wolf <cw...@gmail.com> wrote:
>
>> If I process a small number of records, everything works, however when
>> I try to process a "real-world" number of records, I get an
>> "ORA-12519". At first, I thought it was an Oracle issue and after
>> searching around and getting hits on "solutions" involving increasing
>> Oracle sessions and processes (there are at least 125 configured), I
>> was still getting "ORA-12519". I then tailed the TNS listener log and
>> saw that everytime my OpenJPA process ran, it would consume all the
>> JDBC connections as if it was using connection pooling with some high
>> min-connections setting.
>>
>> In fact, as the stack trace shows, it's only using
>> "SimpleDriveDataSource", which I thought didn't pool connections and I
>> don't have the DBCP jar on my classpath, so why is OpenJPA opening all
>> these JDBC connections?
>>
>>
>> Thanks,
>>
>>
>> Chris
>>
>>
>> 183 marketdata INFO [main] openjpa.Runtime - Starting OpenJPA 2.2.1
>> 214 marketdata INFO [main] openjpa.jdbc.JDBC - Using dictionary
>> class "org.apache.openjpa.jdbc.sql.OracleDictionary".
>> Exception in thread "main" <openjpa-2.2.1-r422266:1396819 fatal store
>> error> org.apache.openjpa.persistence.RollbackException: Listener
>> refused the connection with the following error:
>> ORA-12519, TNS:no appropriate service handler found
>>
>> at
>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:594)
>> at ms.algo.adapt.test.BeanIODemo.saveToDB(BeanIODemo.java:153)
>> at ms.algo.adapt.test.BeanIODemo.beanIOTest(BeanIODemo.java:127)
>> at ms.algo.adapt.test.BeanIODemo.main(BeanIODemo.java:50)
>> Caused by: <openjpa-2.2.1-r422266:1396819 fatal general error>
>> org.apache.openjpa.persistence.PersistenceException: Listener refused
>> the connection with the following error:
>> ORA-12519, TNS:no appropriate service handler found
>>
>> at
>> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4958)
>> at
>> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4918)
>> at
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
>> at
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
>> at
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
>> at
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:971)
>> at
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getConnection(JDBCStoreManager.java:240)
>> at
>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.getConnection(AbstractJDBCSeq.java:163)
>> at
>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:217)
>> at
>> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:201)
>> at
>> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
>> at
>> org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
>> at
>> org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
>> at
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:778)
>> at
>> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493)
>> at
>> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469)
>> at
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:762)
>> at
>> org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135)
>> at
>> org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:600)
>> at
>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPC(SingleFieldManager.java:803)
>> at
>> org.apache.openjpa.kernel.SingleFieldManager.preFlushPCs(SingleFieldManager.java:762)
>> at
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:664)
>> at
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:589)
>> at
>> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:505)
>> at
>> org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:3028)
>> at
>> org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:44)
>> at
>> org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1042)
>> at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2114)
>> at
>> org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2074)
>> at
>> org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1992)
>> at
>> org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
>> at
>> org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1516)
>> at
>> org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:933)
>> at
>> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:570)
>> ... 3 more
>> Caused by: java.sql.SQLException: Listener refused the connection with
>> the following error:
>> ORA-12519, TNS:no appropriate service handler found
>>
>> at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:517)
>> at
>> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:557)
>> at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:233)
>> at
>> oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:29)
>> at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:556)
>> at
>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getSimpleConnection(SimpleDriverDataSource.java:84)
>> at
>> org.apache.openjpa.jdbc.schema.AutoDriverDataSource.getConnection(AutoDriverDataSource.java:39)
>> at
>> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:76)
>> at
>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>> at
>> org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:93)
>> at
>> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
>> at
>> org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:304)
>> at
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:982)
>> at
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:967)
>> ... 31 more
>> Caused by: oracle.net.ns.NetException: Listener refused the connection
>> with the following error:
>> ORA-12519, TNS:no appropriate service handler found
>>
>> at oracle.net.ns.NSProtocol.connect(NSProtocol.java:457)
>> at
>> oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1625)
>> at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:365)
>> ... 44 more
>>
Re: Running out of JDBC connections with openjpa-2.1.0 + Oracle-11g
Posted by Kevin Sutter <kw...@gmail.com>.
Hi Chris,
Normally, OpenJPA will only request a connection "on demand" [1]. As each
database access is requested, a connection is obtained, but then it's
released when we're done with it. Unless there is some processing or
configuration that is telling OpenJPA to hang onto the connection... If
nothing jumps out at you, I would suggest tracing (maybe both OpenJPA and
database) to see why all of the connections are getting requested and
nothing is getting closed.
As an aside, I would highly recommend the use of some type of connection
pooling. Overall, you will get much better performance if connections can
be re-used instead of constantly dropping and re-creating connections.
Whether you use DBCP or Oracle pooling or some application server's
connection pooling mechanism, it doesn't really matter. But, I would
suggest using some connection pooling.
Good luck,
Kevin
[1]
http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_dbsetup_retain
On Thu, Jan 31, 2013 at 10:51 AM, Chris Wolf <cw...@gmail.com> wrote:
> If I process a small number of records, everything works, however when
> I try to process a "real-world" number of records, I get an
> "ORA-12519". At first, I thought it was an Oracle issue and after
> searching around and getting hits on "solutions" involving increasing
> Oracle sessions and processes (there are at least 125 configured), I
> was still getting "ORA-12519". I then tailed the TNS listener log and
> saw that everytime my OpenJPA process ran, it would consume all the
> JDBC connections as if it was using connection pooling with some high
> min-connections setting.
>
> In fact, as the stack trace shows, it's only using
> "SimpleDriveDataSource", which I thought didn't pool connections and I
> don't have the DBCP jar on my classpath, so why is OpenJPA opening all
> these JDBC connections?
>
>
> Thanks,
>
>
> Chris
>
>
> 183 marketdata INFO [main] openjpa.Runtime - Starting OpenJPA 2.2.1
> 214 marketdata INFO [main] openjpa.jdbc.JDBC - Using dictionary
> class "org.apache.openjpa.jdbc.sql.OracleDictionary".
> Exception in thread "main" <openjpa-2.2.1-r422266:1396819 fatal store
> error> org.apache.openjpa.persistence.RollbackException: Listener
> refused the connection with the following error:
> ORA-12519, TNS:no appropriate service handler found
>
> at
> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:594)
> at ms.algo.adapt.test.BeanIODemo.saveToDB(BeanIODemo.java:153)
> at ms.algo.adapt.test.BeanIODemo.beanIOTest(BeanIODemo.java:127)
> at ms.algo.adapt.test.BeanIODemo.main(BeanIODemo.java:50)
> Caused by: <openjpa-2.2.1-r422266:1396819 fatal general error>
> org.apache.openjpa.persistence.PersistenceException: Listener refused
> the connection with the following error:
> ORA-12519, TNS:no appropriate service handler found
>
> at
> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4958)
> at
> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4918)
> at
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
> at
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
> at
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
> at
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:971)
> at
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getConnection(JDBCStoreManager.java:240)
> at
> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.getConnection(AbstractJDBCSeq.java:163)
> at
> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:217)
> at
> org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:201)
> at
> org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
> at
> org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
> at
> org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
> at
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:778)
> at
> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493)
> at
> org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469)
> at
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:762)
> at
> org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135)
> at
> org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:600)
> at
> org.apache.openjpa.kernel.SingleFieldManager.preFlushPC(SingleFieldManager.java:803)
> at
> org.apache.openjpa.kernel.SingleFieldManager.preFlushPCs(SingleFieldManager.java:762)
> at
> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:664)
> at
> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:589)
> at
> org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:505)
> at
> org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:3028)
> at
> org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:44)
> at
> org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1042)
> at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2114)
> at
> org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2074)
> at
> org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1992)
> at
> org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
> at
> org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1516)
> at
> org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:933)
> at
> org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:570)
> ... 3 more
> Caused by: java.sql.SQLException: Listener refused the connection with
> the following error:
> ORA-12519, TNS:no appropriate service handler found
>
> at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:517)
> at
> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:557)
> at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:233)
> at
> oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:29)
> at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:556)
> at
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getSimpleConnection(SimpleDriverDataSource.java:84)
> at
> org.apache.openjpa.jdbc.schema.AutoDriverDataSource.getConnection(AutoDriverDataSource.java:39)
> at
> org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:76)
> at
> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
> at
> org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:93)
> at
> org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
> at
> org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:304)
> at
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:982)
> at
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:967)
> ... 31 more
> Caused by: oracle.net.ns.NetException: Listener refused the connection
> with the following error:
> ORA-12519, TNS:no appropriate service handler found
>
> at oracle.net.ns.NSProtocol.connect(NSProtocol.java:457)
> at
> oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1625)
> at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:365)
> ... 44 more
>