Re: DBCP Database Connection Pools without transactionnal NO autocommit
Just a BTW I think the two statements are not from you/DBCP but the driver
emits them when you use the setAutocommit(). In some cases drivers remember the
state and don’t emit something, in most cases they do. So a config option to
not set/reset that state on checking/checkout - if you need to minimize the
number of roundtrips - would be good for DBCP.
This would be setting defaultAutocommit to thr value you like and setting
autocommitOnReturn to false to avoid resetting it. And your code should not
call the setter, at least not without checking if it’s needed (it will depend
on the driver if this causes a select).
I think Auto commit would be great if your log statements are a single atomic
unit as it greatly reduces roundtrips and transaction time - if you still need
to make multiple inserts in different tables perhaps consider calling a stored
procedure instead?
(And also some batching, but that can become tricky if you keep the
connections for extended transactions). I am not sure how good the dbappender
from log4j is in this regard.
Gruss
Bernd
--
http://bernd.eckenfels.net
Von: Gary Gregory
Gesendet: Monday, June 6, 2022 7:22:26 PM
An: Commons Users List
Betreff: Re: DBCP Database Connection Pools without transactionnal NO autocommit
Any news?
Gary
On Thu, Jun 2, 2022 at 12:32 PM Gary Gregory wrote:
>
> BTW, if that still does not work for you, make sure your are using the latest
> version of DBCP and of your JDBC Driver. Please report which versions you are
> using. If you are still stuck, the only way we can realistically help you
> here is if you provide a PR with a reproducible test case using the H2 in
> memory database, see the existing tests for inspiration. If you cannot
> reproduce the issue, then this might be specific to your database of JDBC
> driver, in which case, we would still need a standalone application that
> reproduces the issue.
>
> Gary
>
> On Thu, Jun 2, 2022, 08:22 Gary Gregory wrote:
>>
>> It might be simpler to avoid custom Java code. Is there a reason you are not
>> using the log4j2-jdbc-dbcp2 module?
>>
>> For example
>> https://github.com/apache/logging-log4j2/blob/release-2.x/log4j-jdbc-dbcp2/src/test/resources/log4j2-jdbc-dbcp2.xml
>>
>> Gary
>>
>> On Thu, Jun 2, 2022, 08:13 Sébastien Jachym
>> wrote:
>>>
>>> I'm logging with log4j2
>>>
>>> log4j2.xml :
>>>
>>>
>>>
>>> >> method="getConnection" />
>>> ...
>>>
>>>
>>>
>>> ConnectionFactory class :
>>> public static Connection getConnection() throws SQLException {
>>> dataSource = new org.apache.commons.dbcp2.BasicDataSource();
>>> dataSource.setUp ( url, user, pass, driver )
>>>
>>> dataSource.setDefaultAutoCommit(false);
>>> dataSource.setAutoCommitOnReturn(false);
>>> dataSource.setRollbackOnReturn(false);
>>> dataSource.setValidationQuery("SELECT 1");
>>>
>>> //Connection connection = dataSource.getConnection();
>>> //connection.setAutoCommit(true); // no change, first is SET
>>> autocommit=(0|1) and log4j2 next is SET autocommit=0
>>> //return connection;
>>> return dataSource.getConnection();
>>>
>>> In code call :
>>> org.apache.logging.log4j.Logger.trace("hello")
>>>
>>> Finally calling logger,
>>> use JDBC Appender
>>> and get a connection from the BasicDataSource pool.
>>> And continu outside my code with the unwanted transactional statement.
>>> For writing log in my database.
>>>
>>> -Message d'origine-
>>> De : Gary Gregory
>>> Envoyé : jeudi 2 juin 2022 11:23
>>> À : Commons Users List
>>> Objet : Re: DBCP Database Connection Pools without transactionnal NO
>>> autocommit
>>>
>>> Something is not clear to me: are you in control of calling JDBC or are you
>>> using Log4j's JDBC Appender or something else? You description is not
>>> detailed enough for me to understand who does what.
>>>
>>> Gary
>>>
>>> On Wed, Jun 1, 2022, 19:49 Sébastien Jachym <
>>> sebastien.jac...@agri-commerce.fr> wrote:
>>>
>>> > Hello,
>>> > Thank you Gary for you reply, but it doesn’t work with configuring
>>> > BasicDataSource with :
>>> >dataSource.setDefaultAutoCommit(false);
>>> >dataSource.
Re: DBCP Database Connection Pools without transactionnal NO autocommit
Any news?
Gary
On Thu, Jun 2, 2022 at 12:32 PM Gary Gregory wrote:
>
> BTW, if that still does not work for you, make sure your are using the latest
> version of DBCP and of your JDBC Driver. Please report which versions you are
> using. If you are still stuck, the only way we can realistically help you
> here is if you provide a PR with a reproducible test case using the H2 in
> memory database, see the existing tests for inspiration. If you cannot
> reproduce the issue, then this might be specific to your database of JDBC
> driver, in which case, we would still need a standalone application that
> reproduces the issue.
>
> Gary
>
> On Thu, Jun 2, 2022, 08:22 Gary Gregory wrote:
>>
>> It might be simpler to avoid custom Java code. Is there a reason you are not
>> using the log4j2-jdbc-dbcp2 module?
>>
>> For example
>> https://github.com/apache/logging-log4j2/blob/release-2.x/log4j-jdbc-dbcp2/src/test/resources/log4j2-jdbc-dbcp2.xml
>>
>> Gary
>>
>> On Thu, Jun 2, 2022, 08:13 Sébastien Jachym
>> wrote:
>>>
>>> I'm logging with log4j2
>>>
>>> log4j2.xml :
>>>
>>>
>>>
>>> >> method="getConnection" />
>>> ...
>>>
>>>
>>>
>>> ConnectionFactory class :
>>> public static Connection getConnection() throws SQLException {
>>> dataSource = new org.apache.commons.dbcp2.BasicDataSource();
>>> dataSource.setUp ( url, user, pass, driver )
>>>
>>> dataSource.setDefaultAutoCommit(false);
>>> dataSource.setAutoCommitOnReturn(false);
>>> dataSource.setRollbackOnReturn(false);
>>> dataSource.setValidationQuery("SELECT 1");
>>>
>>> //Connection connection = dataSource.getConnection();
>>> //connection.setAutoCommit(true); // no change, first is SET
>>> autocommit=(0|1) and log4j2 next is SET autocommit=0
>>> //return connection;
>>> return dataSource.getConnection();
>>>
>>> In code call :
>>> org.apache.logging.log4j.Logger.trace("hello")
>>>
>>> Finally calling logger,
>>> use JDBC Appender
>>> and get a connection from the BasicDataSource pool.
>>> And continu outside my code with the unwanted transactional statement.
>>> For writing log in my database.
>>>
>>> -Message d'origine-
>>> De : Gary Gregory
>>> Envoyé : jeudi 2 juin 2022 11:23
>>> À : Commons Users List
>>> Objet : Re: DBCP Database Connection Pools without transactionnal NO
>>> autocommit
>>>
>>> Something is not clear to me: are you in control of calling JDBC or are you
>>> using Log4j's JDBC Appender or something else? You description is not
>>> detailed enough for me to understand who does what.
>>>
>>> Gary
>>>
>>> On Wed, Jun 1, 2022, 19:49 Sébastien Jachym <
>>> sebastien.jac...@agri-commerce.fr> wrote:
>>>
>>> > Hello,
>>> > Thank you Gary for you reply, but it doesn’t work with configuring
>>> > BasicDataSource with :
>>> >dataSource.setDefaultAutoCommit(false);
>>> >dataSource.setAutoCommitOnReturn(false);
>>> >dataSource.setRollbackOnReturn(false);
>>> >
>>> > There are changes, but always transactional with "SET autocommit" :
>>> > SELECT @@session.transaction_isolation SELECT 1 -- ValidationQuery
>>> > dans ConnectionFactory SET autocommit=0 SET SQL_SELECT_LIMIT=1 select
>>> >
>>> > date,logger,level,message,exception,pid,idUtilisateur,version,ip,marke
>>> > rSimpleName,sqlLastInsertId,sqlTimeMillis
>>> > from log4j2 where 1=0
>>> > SET SQL_SELECT_LIMIT=DEFAULT
>>> > insert into log4j2
>>> >
>>> > (date,logger,level,message,exception,pid,idUtilisateur,version,ip,mark
>>> > erSimpleName,sqlLastInsertId,sqlTimeMillis)
>>> > values (... the values ...)
>>> > commit
>>> >
>>> > Also before returning the connection to my log4j2 Async Appenders
>>> > whith ConnectionFactory, I force : connection.setAutoCommit(false);
>>> > But result is same with transactional statements :
>>> > SET autocommit=0 -- [ OR "SET autocommit=0" WITH
>>> > connection.setAutoCommit(true); ] SET autocommit
Re: DBCP Database Connection Pools without transactionnal NO autocommit
BTW, if that still does not work for you, make sure your are using the
latest version of DBCP and of your JDBC Driver. Please report which
versions you are using. If you are still stuck, the only way we can
realistically help you here is if you provide a PR with a reproducible test
case using the H2 in memory database, see the existing tests for
inspiration. If you cannot reproduce the issue, then this might be specific
to your database of JDBC driver, in which case, we would still need a
standalone application that reproduces the issue.
Gary
On Thu, Jun 2, 2022, 08:22 Gary Gregory wrote:
> It might be simpler to avoid custom Java code. Is there a reason you are
> not using the log4j2-jdbc-dbcp2 module?
>
> For example
> https://github.com/apache/logging-log4j2/blob/release-2.x/log4j-jdbc-dbcp2/src/test/resources/log4j2-jdbc-dbcp2.xml
>
> Gary
>
> On Thu, Jun 2, 2022, 08:13 Sébastien Jachym <
> sebastien.jac...@agri-commerce.fr> wrote:
>
>> I'm logging with log4j2
>>
>> log4j2.xml :
>>
>>
>>
>> > method="getConnection" />
>> ...
>>
>>
>>
>> ConnectionFactory class :
>> public static Connection getConnection() throws SQLException {
>> dataSource = new org.apache.commons.dbcp2.BasicDataSource();
>> dataSource.setUp ( url, user, pass, driver )
>>
>> dataSource.setDefaultAutoCommit(false);
>> dataSource.setAutoCommitOnReturn(false);
>> dataSource.setRollbackOnReturn(false);
>> dataSource.setValidationQuery("SELECT 1");
>>
>> //Connection connection = dataSource.getConnection();
>> //connection.setAutoCommit(true); // no change, first is SET
>> autocommit=(0|1) and log4j2 next is SET autocommit=0
>> //return connection;
>> return dataSource.getConnection();
>>
>> In code call :
>> org.apache.logging.log4j.Logger.trace("hello")
>>
>> Finally calling logger,
>> use JDBC Appender
>> and get a connection from the BasicDataSource pool.
>> And continu outside my code with the unwanted transactional statement.
>> For writing log in my database.
>>
>> -Message d'origine-
>> De : Gary Gregory
>> Envoyé : jeudi 2 juin 2022 11:23
>> À : Commons Users List
>> Objet : Re: DBCP Database Connection Pools without transactionnal NO
>> autocommit
>>
>> Something is not clear to me: are you in control of calling JDBC or are
>> you
>> using Log4j's JDBC Appender or something else? You description is not
>> detailed enough for me to understand who does what.
>>
>> Gary
>>
>> On Wed, Jun 1, 2022, 19:49 Sébastien Jachym <
>> sebastien.jac...@agri-commerce.fr> wrote:
>>
>> > Hello,
>> > Thank you Gary for you reply, but it doesn’t work with configuring
>> > BasicDataSource with :
>> >dataSource.setDefaultAutoCommit(false);
>> >dataSource.setAutoCommitOnReturn(false);
>> >dataSource.setRollbackOnReturn(false);
>> >
>> > There are changes, but always transactional with "SET autocommit" :
>> > SELECT @@session.transaction_isolation SELECT 1 -- ValidationQuery
>> > dans ConnectionFactory SET autocommit=0 SET SQL_SELECT_LIMIT=1 select
>> >
>> > date,logger,level,message,exception,pid,idUtilisateur,version,ip,marke
>> > rSimpleName,sqlLastInsertId,sqlTimeMillis
>> > from log4j2 where 1=0
>> > SET SQL_SELECT_LIMIT=DEFAULT
>> > insert into log4j2
>> >
>> > (date,logger,level,message,exception,pid,idUtilisateur,version,ip,mark
>> > erSimpleName,sqlLastInsertId,sqlTimeMillis)
>> > values (... the values ...)
>> > commit
>> >
>> > Also before returning the connection to my log4j2 Async Appenders
>> > whith ConnectionFactory, I force : connection.setAutoCommit(false);
>> > But result is same with transactional statements :
>> > SET autocommit=0 -- [ OR "SET autocommit=0" WITH
>> > connection.setAutoCommit(true); ] SET autocommit=0 SET
>> > SQL_SELECT_LIMIT=1 select
>> >
>> > date,logger,level,message,exception,pid,idUtilisateur,version,ip,marke
>> > rSimpleName,sqlLastInsertId,sqlTimeMillis
>> > from log4j2 where 1=0
>> > SET SQL_SELECT_LIMIT=DEFAULT
>> > insert into log4j2
>> >
>> > (date,logger,level,message,exception,pid,idUtilisateur,version,ip,mark
>> > erSimpleName,sqlLastInsertId,sqlTimeMillis)
>> >
Re: DBCP Database Connection Pools without transactionnal NO autocommit
It might be simpler to avoid custom Java code. Is there a reason you are
not using the log4j2-jdbc-dbcp2 module?
For example
https://github.com/apache/logging-log4j2/blob/release-2.x/log4j-jdbc-dbcp2/src/test/resources/log4j2-jdbc-dbcp2.xml
Gary
On Thu, Jun 2, 2022, 08:13 Sébastien Jachym <
sebastien.jac...@agri-commerce.fr> wrote:
> I'm logging with log4j2
>
> log4j2.xml :
>
>
>
> method="getConnection" />
> ...
>
>
>
> ConnectionFactory class :
> public static Connection getConnection() throws SQLException {
> dataSource = new org.apache.commons.dbcp2.BasicDataSource();
> dataSource.setUp ( url, user, pass, driver )
>
> dataSource.setDefaultAutoCommit(false);
> dataSource.setAutoCommitOnReturn(false);
> dataSource.setRollbackOnReturn(false);
> dataSource.setValidationQuery("SELECT 1");
>
> //Connection connection = dataSource.getConnection();
> //connection.setAutoCommit(true); // no change, first is SET
> autocommit=(0|1) and log4j2 next is SET autocommit=0
> //return connection;
> return dataSource.getConnection();
>
> In code call :
> org.apache.logging.log4j.Logger.trace("hello")
>
> Finally calling logger,
> use JDBC Appender
> and get a connection from the BasicDataSource pool.
> And continu outside my code with the unwanted transactional statement.
> For writing log in my database.
>
> -Message d'origine-
> De : Gary Gregory
> Envoyé : jeudi 2 juin 2022 11:23
> À : Commons Users List
> Objet : Re: DBCP Database Connection Pools without transactionnal NO
> autocommit
>
> Something is not clear to me: are you in control of calling JDBC or are you
> using Log4j's JDBC Appender or something else? You description is not
> detailed enough for me to understand who does what.
>
> Gary
>
> On Wed, Jun 1, 2022, 19:49 Sébastien Jachym <
> sebastien.jac...@agri-commerce.fr> wrote:
>
> > Hello,
> > Thank you Gary for you reply, but it doesn’t work with configuring
> > BasicDataSource with :
> >dataSource.setDefaultAutoCommit(false);
> >dataSource.setAutoCommitOnReturn(false);
> >dataSource.setRollbackOnReturn(false);
> >
> > There are changes, but always transactional with "SET autocommit" :
> > SELECT @@session.transaction_isolation SELECT 1 -- ValidationQuery
> > dans ConnectionFactory SET autocommit=0 SET SQL_SELECT_LIMIT=1 select
> >
> > date,logger,level,message,exception,pid,idUtilisateur,version,ip,marke
> > rSimpleName,sqlLastInsertId,sqlTimeMillis
> > from log4j2 where 1=0
> > SET SQL_SELECT_LIMIT=DEFAULT
> > insert into log4j2
> >
> > (date,logger,level,message,exception,pid,idUtilisateur,version,ip,mark
> > erSimpleName,sqlLastInsertId,sqlTimeMillis)
> > values (... the values ...)
> > commit
> >
> > Also before returning the connection to my log4j2 Async Appenders
> > whith ConnectionFactory, I force : connection.setAutoCommit(false);
> > But result is same with transactional statements :
> > SET autocommit=0 -- [ OR "SET autocommit=0" WITH
> > connection.setAutoCommit(true); ] SET autocommit=0 SET
> > SQL_SELECT_LIMIT=1 select
> >
> > date,logger,level,message,exception,pid,idUtilisateur,version,ip,marke
> > rSimpleName,sqlLastInsertId,sqlTimeMillis
> > from log4j2 where 1=0
> > SET SQL_SELECT_LIMIT=DEFAULT
> > insert into log4j2
> >
> > (date,logger,level,message,exception,pid,idUtilisateur,version,ip,mark
> > erSimpleName,sqlLastInsertId,sqlTimeMillis)
> > values ...
> > commit
> >
> > Cheers.
> >
> >
> > -Message d'origine-
> > De : Gary Gregory Envoyé : mercredi 1 juin
> > 2022 14:57 À : Commons Users List Objet :
> > Re: DBCP Database Connection Pools without transactionnal NO
> > autocommit
> >
> > Hello,
> >
> > Try calling Connection#setAutoCommit(false) once you get a Connection.
> > You'll likely want to configure your data source with
> > org.apache.commons.dbcp2.BasicDataSource.setAutoCommitOnReturn(false)
> > to avoid resetting the auto-commit flag.
> >
> > Gary
> >
> > On Tue, May 31, 2022 at 7:46 PM Sébastien Jachym
> > wrote:
> > >
> > > Hy, is a way to fully disabled transactional (autocommit) statement
> > > in DBCP ?
> > >
> > >
> > >
> > > Using Database Connection Pools with
> > > org.apache.commons.dbcp2.BasicDataSource give me
RE: DBCP Database Connection Pools without transactionnal NO autocommit
I'm logging with log4j2
log4j2.xml :
...
ConnectionFactory class :
public static Connection getConnection() throws SQLException {
dataSource = new org.apache.commons.dbcp2.BasicDataSource();
dataSource.setUp ( url, user, pass, driver )
dataSource.setDefaultAutoCommit(false);
dataSource.setAutoCommitOnReturn(false);
dataSource.setRollbackOnReturn(false);
dataSource.setValidationQuery("SELECT 1");
//Connection connection = dataSource.getConnection();
//connection.setAutoCommit(true); // no change, first is SET
autocommit=(0|1) and log4j2 next is SET autocommit=0
//return connection;
return dataSource.getConnection();
In code call :
org.apache.logging.log4j.Logger.trace("hello")
Finally calling logger,
use JDBC Appender
and get a connection from the BasicDataSource pool.
And continu outside my code with the unwanted transactional statement.
For writing log in my database.
-Message d'origine-
De : Gary Gregory
Envoyé : jeudi 2 juin 2022 11:23
À : Commons Users List
Objet : Re: DBCP Database Connection Pools without transactionnal NO
autocommit
Something is not clear to me: are you in control of calling JDBC or are you
using Log4j's JDBC Appender or something else? You description is not
detailed enough for me to understand who does what.
Gary
On Wed, Jun 1, 2022, 19:49 Sébastien Jachym <
sebastien.jac...@agri-commerce.fr> wrote:
> Hello,
> Thank you Gary for you reply, but it doesn’t work with configuring
> BasicDataSource with :
>dataSource.setDefaultAutoCommit(false);
>dataSource.setAutoCommitOnReturn(false);
>dataSource.setRollbackOnReturn(false);
>
> There are changes, but always transactional with "SET autocommit" :
> SELECT @@session.transaction_isolation SELECT 1 -- ValidationQuery
> dans ConnectionFactory SET autocommit=0 SET SQL_SELECT_LIMIT=1 select
>
> date,logger,level,message,exception,pid,idUtilisateur,version,ip,marke
> rSimpleName,sqlLastInsertId,sqlTimeMillis
> from log4j2 where 1=0
> SET SQL_SELECT_LIMIT=DEFAULT
> insert into log4j2
>
> (date,logger,level,message,exception,pid,idUtilisateur,version,ip,mark
> erSimpleName,sqlLastInsertId,sqlTimeMillis)
> values (... the values ...)
> commit
>
> Also before returning the connection to my log4j2 Async Appenders
> whith ConnectionFactory, I force : connection.setAutoCommit(false);
> But result is same with transactional statements :
> SET autocommit=0 -- [ OR "SET autocommit=0" WITH
> connection.setAutoCommit(true); ] SET autocommit=0 SET
> SQL_SELECT_LIMIT=1 select
>
> date,logger,level,message,exception,pid,idUtilisateur,version,ip,marke
> rSimpleName,sqlLastInsertId,sqlTimeMillis
> from log4j2 where 1=0
> SET SQL_SELECT_LIMIT=DEFAULT
> insert into log4j2
>
> (date,logger,level,message,exception,pid,idUtilisateur,version,ip,mark
> erSimpleName,sqlLastInsertId,sqlTimeMillis)
> values ...
> commit
>
> Cheers.
>
>
> -Message d'origine-
> De : Gary Gregory Envoyé : mercredi 1 juin
> 2022 14:57 À : Commons Users List Objet :
> Re: DBCP Database Connection Pools without transactionnal NO
> autocommit
>
> Hello,
>
> Try calling Connection#setAutoCommit(false) once you get a Connection.
> You'll likely want to configure your data source with
> org.apache.commons.dbcp2.BasicDataSource.setAutoCommitOnReturn(false)
> to avoid resetting the auto-commit flag.
>
> Gary
>
> On Tue, May 31, 2022 at 7:46 PM Sébastien Jachym
> wrote:
> >
> > Hy, is a way to fully disabled transactional (autocommit) statement
> > in DBCP ?
> >
> >
> >
> > Using Database Connection Pools with
> > org.apache.commons.dbcp2.BasicDataSource give me statements :
> >
> >
> >
> > SET autocommit=1
> >
> > SET autocommit=0
> >
> > SET SQL_SELECT_LIMIT=1
> >
> > select
> > date,logger,level,message,exception,pid,idUtilisateur,version,ip,mar
> > ke rSimpleName,sqlLastInsertId,sqlTimeMillis
> > from log4j2 where 1=0
> >
> > SET SQL_SELECT_LIMIT=DEFAULT
> >
> > insert into log4j2
> > (date,logger,level,message,exception,pid,idUtilisateur,version,ip,ma
> > rk
> > erSimpleName,sqlLastInsertId,sqlTimeMillis)
> > values
> > ('2022-06-01','timertask','TRACE','WaitInitService','','45964','123','
> > null','','','','0')
> >
> > commit
> >
> > rollback
> >
> > SET autocommit=1
> >
> >
> >
> > But it's for MySQL MyISAM table, and I didn't need transactions, I
> > whish only :
> >
> >
> >
&g
Re: DBCP Database Connection Pools without transactionnal NO autocommit
Something is not clear to me: are you in control of calling JDBC or are you
using Log4j's JDBC Appender or something else? You description is not
detailed enough for me to understand who does what.
Gary
On Wed, Jun 1, 2022, 19:49 Sébastien Jachym <
sebastien.jac...@agri-commerce.fr> wrote:
> Hello,
> Thank you Gary for you reply, but it doesn’t work with configuring
> BasicDataSource with :
>dataSource.setDefaultAutoCommit(false);
>dataSource.setAutoCommitOnReturn(false);
>dataSource.setRollbackOnReturn(false);
>
> There are changes, but always transactional with "SET autocommit" :
> SELECT @@session.transaction_isolation
> SELECT 1 -- ValidationQuery dans ConnectionFactory
> SET autocommit=0
> SET SQL_SELECT_LIMIT=1
> select
>
> date,logger,level,message,exception,pid,idUtilisateur,version,ip,markerSimpleName,sqlLastInsertId,sqlTimeMillis
> from log4j2 where 1=0
> SET SQL_SELECT_LIMIT=DEFAULT
> insert into log4j2
>
> (date,logger,level,message,exception,pid,idUtilisateur,version,ip,markerSimpleName,sqlLastInsertId,sqlTimeMillis)
> values (... the values ...)
> commit
>
> Also before returning the connection to my log4j2 Async Appenders whith
> ConnectionFactory, I force : connection.setAutoCommit(false);
> But result is same with transactional statements :
> SET autocommit=0 -- [ OR "SET autocommit=0" WITH
> connection.setAutoCommit(true); ]
> SET autocommit=0
> SET SQL_SELECT_LIMIT=1
> select
>
> date,logger,level,message,exception,pid,idUtilisateur,version,ip,markerSimpleName,sqlLastInsertId,sqlTimeMillis
> from log4j2 where 1=0
> SET SQL_SELECT_LIMIT=DEFAULT
> insert into log4j2
>
> (date,logger,level,message,exception,pid,idUtilisateur,version,ip,markerSimpleName,sqlLastInsertId,sqlTimeMillis)
> values ...
> commit
>
> Cheers.
>
>
> -Message d'origine-----
> De : Gary Gregory
> Envoyé : mercredi 1 juin 2022 14:57
> À : Commons Users List
> Objet : Re: DBCP Database Connection Pools without transactionnal NO
> autocommit
>
> Hello,
>
> Try calling Connection#setAutoCommit(false) once you get a Connection.
> You'll likely want to configure your data source with
> org.apache.commons.dbcp2.BasicDataSource.setAutoCommitOnReturn(false)
> to avoid resetting the auto-commit flag.
>
> Gary
>
> On Tue, May 31, 2022 at 7:46 PM Sébastien Jachym
> wrote:
> >
> > Hy, is a way to fully disabled transactional (autocommit) statement in
> > DBCP ?
> >
> >
> >
> > Using Database Connection Pools with
> > org.apache.commons.dbcp2.BasicDataSource give me statements :
> >
> >
> >
> > SET autocommit=1
> >
> > SET autocommit=0
> >
> > SET SQL_SELECT_LIMIT=1
> >
> > select
> > date,logger,level,message,exception,pid,idUtilisateur,version,ip,marke
> > rSimpleName,sqlLastInsertId,sqlTimeMillis
> > from log4j2 where 1=0
> >
> > SET SQL_SELECT_LIMIT=DEFAULT
> >
> > insert into log4j2
> > (date,logger,level,message,exception,pid,idUtilisateur,version,ip,mark
> > erSimpleName,sqlLastInsertId,sqlTimeMillis)
> > values
> > ('2022-06-01','timertask','TRACE','WaitInitService','','45964','123','
> > null','','','','0')
> >
> > commit
> >
> > rollback
> >
> > SET autocommit=1
> >
> >
> >
> > But it's for MySQL MyISAM table, and I didn't need transactions, I
> > whish only :
> >
> >
> >
> > select
> > date,logger,level,message,exception,pid,idUtilisateur,version,ip,marke
> > rSimpleName,sqlLastInsertId,sqlTimeMillis
> > from log4j2 where 1=0
> >
> > SET SQL_SELECT_LIMIT=DEFAULT
> >
> > insert into log4j2
> > (date,logger,level,message,exception,pid,idUtilisateur,version,ip,mark
> > erSimpleName,sqlLastInsertId,sqlTimeMillis)
> > values
> > ('2022-06-01','timertask','TRACE','WaitInitService','','45964','123','
> > null','','','','0')
> >
> >
> >
> > Thanks for you replys.
>
> -
> To unsubscribe, e-mail: user-unsubscr...@commons.apache.org
> For additional commands, e-mail: user-h...@commons.apache.org
>
> -
> To unsubscribe, e-mail: user-unsubscr...@commons.apache.org
> For additional commands, e-mail: user-h...@commons.apache.org
>
>
RE: DBCP Database Connection Pools without transactionnal NO autocommit
Hello,
Thank you Gary for you reply, but it doesn’t work with configuring
BasicDataSource with :
dataSource.setDefaultAutoCommit(false);
dataSource.setAutoCommitOnReturn(false);
dataSource.setRollbackOnReturn(false);
There are changes, but always transactional with "SET autocommit" :
SELECT @@session.transaction_isolation
SELECT 1 -- ValidationQuery dans ConnectionFactory
SET autocommit=0
SET SQL_SELECT_LIMIT=1
select
date,logger,level,message,exception,pid,idUtilisateur,version,ip,markerSimpleName,sqlLastInsertId,sqlTimeMillis
from log4j2 where 1=0
SET SQL_SELECT_LIMIT=DEFAULT
insert into log4j2
(date,logger,level,message,exception,pid,idUtilisateur,version,ip,markerSimpleName,sqlLastInsertId,sqlTimeMillis)
values (... the values ...)
commit
Also before returning the connection to my log4j2 Async Appenders whith
ConnectionFactory, I force : connection.setAutoCommit(false);
But result is same with transactional statements :
SET autocommit=0 -- [ OR "SET autocommit=0" WITH
connection.setAutoCommit(true); ]
SET autocommit=0
SET SQL_SELECT_LIMIT=1
select
date,logger,level,message,exception,pid,idUtilisateur,version,ip,markerSimpleName,sqlLastInsertId,sqlTimeMillis
from log4j2 where 1=0
SET SQL_SELECT_LIMIT=DEFAULT
insert into log4j2
(date,logger,level,message,exception,pid,idUtilisateur,version,ip,markerSimpleName,sqlLastInsertId,sqlTimeMillis)
values ...
commit
Cheers.
-Message d'origine-
De : Gary Gregory
Envoyé : mercredi 1 juin 2022 14:57
À : Commons Users List
Objet : Re: DBCP Database Connection Pools without transactionnal NO
autocommit
Hello,
Try calling Connection#setAutoCommit(false) once you get a Connection.
You'll likely want to configure your data source with
org.apache.commons.dbcp2.BasicDataSource.setAutoCommitOnReturn(false)
to avoid resetting the auto-commit flag.
Gary
On Tue, May 31, 2022 at 7:46 PM Sébastien Jachym
wrote:
>
> Hy, is a way to fully disabled transactional (autocommit) statement in
> DBCP ?
>
>
>
> Using Database Connection Pools with
> org.apache.commons.dbcp2.BasicDataSource give me statements :
>
>
>
> SET autocommit=1
>
> SET autocommit=0
>
> SET SQL_SELECT_LIMIT=1
>
> select
> date,logger,level,message,exception,pid,idUtilisateur,version,ip,marke
> rSimpleName,sqlLastInsertId,sqlTimeMillis
> from log4j2 where 1=0
>
> SET SQL_SELECT_LIMIT=DEFAULT
>
> insert into log4j2
> (date,logger,level,message,exception,pid,idUtilisateur,version,ip,mark
> erSimpleName,sqlLastInsertId,sqlTimeMillis)
> values
> ('2022-06-01','timertask','TRACE','WaitInitService','','45964','123','
> null','','','','0')
>
> commit
>
> rollback
>
> SET autocommit=1
>
>
>
> But it's for MySQL MyISAM table, and I didn't need transactions, I
> whish only :
>
>
>
> select
> date,logger,level,message,exception,pid,idUtilisateur,version,ip,marke
> rSimpleName,sqlLastInsertId,sqlTimeMillis
> from log4j2 where 1=0
>
> SET SQL_SELECT_LIMIT=DEFAULT
>
> insert into log4j2
> (date,logger,level,message,exception,pid,idUtilisateur,version,ip,mark
> erSimpleName,sqlLastInsertId,sqlTimeMillis)
> values
> ('2022-06-01','timertask','TRACE','WaitInitService','','45964','123','
> null','','','','0')
>
>
>
> Thanks for you replys.
-
To unsubscribe, e-mail: user-unsubscr...@commons.apache.org
For additional commands, e-mail: user-h...@commons.apache.org
-
To unsubscribe, e-mail: user-unsubscr...@commons.apache.org
For additional commands, e-mail: user-h...@commons.apache.org
Re: DBCP Database Connection Pools without transactionnal NO autocommit
Hello,
Try calling Connection#setAutoCommit(false) once you get a Connection.
You'll likely want to configure your data source with
org.apache.commons.dbcp2.BasicDataSource.setAutoCommitOnReturn(false)
to avoid resetting the auto-commit flag.
Gary
On Tue, May 31, 2022 at 7:46 PM Sébastien Jachym
wrote:
>
> Hy, is a way to fully disabled transactional (autocommit) statement in DBCP
> ?
>
>
>
> Using Database Connection Pools with
> org.apache.commons.dbcp2.BasicDataSource give me statements :
>
>
>
> SET autocommit=1
>
> SET autocommit=0
>
> SET SQL_SELECT_LIMIT=1
>
> select
> date,logger,level,message,exception,pid,idUtilisateur,version,ip,markerSimpleName,sqlLastInsertId,sqlTimeMillis
> from log4j2 where 1=0
>
> SET SQL_SELECT_LIMIT=DEFAULT
>
> insert into log4j2
> (date,logger,level,message,exception,pid,idUtilisateur,version,ip,markerSimpleName,sqlLastInsertId,sqlTimeMillis)
> values
> ('2022-06-01','timertask','TRACE','WaitInitService','','45964','123','null','','','','0')
>
> commit
>
> rollback
>
> SET autocommit=1
>
>
>
> But it's for MySQL MyISAM table, and I didn't need transactions, I whish
> only :
>
>
>
> select
> date,logger,level,message,exception,pid,idUtilisateur,version,ip,markerSimpleName,sqlLastInsertId,sqlTimeMillis
> from log4j2 where 1=0
>
> SET SQL_SELECT_LIMIT=DEFAULT
>
> insert into log4j2
> (date,logger,level,message,exception,pid,idUtilisateur,version,ip,markerSimpleName,sqlLastInsertId,sqlTimeMillis)
> values
> ('2022-06-01','timertask','TRACE','WaitInitService','','45964','123','null','','','','0')
>
>
>
> Thanks for you replys.
-
To unsubscribe, e-mail: user-unsubscr...@commons.apache.org
For additional commands, e-mail: user-h...@commons.apache.org
