JDBC with autocommit=false

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

JDBC with autocommit=false

Brian Flowers
Hi everyone,

I'm having some issues using JMeter to insert some records in a MariaDB
database with autocommit disabled (the idea being that we want to commit
every ~1000 records, not after each one).

Did some searches and couldn't find any documentation or tutorials
explaining this...I got desperate enough to ask on StackOverflow :) but
the only response so far seems to indicate that I'm configuring it
correctly:
https://stackoverflow.com/questions/63713516/jmeter-jdbc-manual-commit

I started with JMeter 3.2 connecting via the mysql connector version
5.1.27 as that's what we already had...I realize those are pretty old,
so I did try upgrading, but got the same results. Tried on JMeter 5.3
with mysql connector 8.0.21, and also with the dedicated mariadb
connector version 2.11.3 (all connectors from the Maven repository).
With auto commit true, any combination of those versions works fine.
With auto commit false, I can't get my data committed on any of them.

What I have set up right now is a thread group with one thread, than
contains a JDBC request with a single INSERT statement, using a couple
variables that it takes from a csv data set and a counter, on a constant
throughput timer, and I'm using the loop count in the thread group to
control the number of records inserted. When I have auto commit set to
true in the JDBC configuration, the records all get inserted just fine.
But when I turn auto commit off, I can't get those statements committed.
I set the JDBC request query type to "AutoCommit(false)" instead of
"Update Statement", then I added a second JDBC request on the same
configuration with request type of "Commit". In the results tree I can
see a commit statement following each insert statement with no errors,
but the records don't actually get committed in the DB. I tried adding
the commit inside the original JDBC request (just to see if that'd work)
but that gave a SQL error; I tried adding a commit post processor within
the main JDBC request, but no luck there. I tried adding a pre-processor
to open a transaction, assuming that it wasn't including the commit and
the insert on the same transaction, but no change with that. I tried
configuration transaction isolation as DEFAULT or as
TRANSACTION_SERIALIZABLE but that had no apparent effect either.

So...how do I manually commit an insert statement on a mariaDB database?
Or what else can I check to try to diagnose exactly what is going on
here? Are there any resources or documentation about exactly how to use
the autocommit setting?

Thanks!
Brian Flowers
[hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: JDBC with autocommit=false

support@ubikloadpack.com
Hello,
Can you share your test plan ?
or run in JMeter 5.X: Menu Tools > Generate Schematic Overview.
Or  screenshots  of your test plan (the Tree + Screenshots of each JDBC
Request element)
Thanks

On Thu, Sep 3, 2020 at 6:16 PM Brian Flowers <[hidden email]>
wrote:

> Hi everyone,
>
> I'm having some issues using JMeter to insert some records in a MariaDB
> database with autocommit disabled (the idea being that we want to commit
> every ~1000 records, not after each one).
>
> Did some searches and couldn't find any documentation or tutorials
> explaining this...I got desperate enough to ask on StackOverflow :) but
> the only response so far seems to indicate that I'm configuring it
> correctly:
> https://stackoverflow.com/questions/63713516/jmeter-jdbc-manual-commit
>
> I started with JMeter 3.2 connecting via the mysql connector version
> 5.1.27 as that's what we already had...I realize those are pretty old,
> so I did try upgrading, but got the same results. Tried on JMeter 5.3
> with mysql connector 8.0.21, and also with the dedicated mariadb
> connector version 2.11.3 (all connectors from the Maven repository).
> With auto commit true, any combination of those versions works fine.
> With auto commit false, I can't get my data committed on any of them.
>
> What I have set up right now is a thread group with one thread, than
> contains a JDBC request with a single INSERT statement, using a couple
> variables that it takes from a csv data set and a counter, on a constant
> throughput timer, and I'm using the loop count in the thread group to
> control the number of records inserted. When I have auto commit set to
> true in the JDBC configuration, the records all get inserted just fine.
> But when I turn auto commit off, I can't get those statements committed.
> I set the JDBC request query type to "AutoCommit(false)" instead of
> "Update Statement", then I added a second JDBC request on the same
> configuration with request type of "Commit". In the results tree I can
> see a commit statement following each insert statement with no errors,
> but the records don't actually get committed in the DB. I tried adding
> the commit inside the original JDBC request (just to see if that'd work)
> but that gave a SQL error; I tried adding a commit post processor within
> the main JDBC request, but no luck there. I tried adding a pre-processor
> to open a transaction, assuming that it wasn't including the commit and
> the insert on the same transaction, but no change with that. I tried
> configuration transaction isolation as DEFAULT or as
> TRANSACTION_SERIALIZABLE but that had no apparent effect either.
>
> So...how do I manually commit an insert statement on a mariaDB database?
> Or what else can I check to try to diagnose exactly what is going on
> here? Are there any resources or documentation about exactly how to use
> the autocommit setting?
>
> Thanks!
> Brian Flowers
> [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
> --
> <[hidden email]>
>
> Regards
> <[hidden email]>Ubik Load Pack <http://ubikloadpack.com> Team
> Follow us on Twitter <http://twitter.com/ubikloadpack>
>
>
> Cordialement
> L'équipe Ubik Load Pack <http://ubikloadpack.com>
> Suivez-nous sur Twitter <http://twitter.com/ubikloadpack>
>
>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: JDBC with autocommit=false

Brian Flowers
Schematic overview is attached

Screenshots are uploaded at imgbb:

JDBC Configuration: https://ibb.co/Nm1nbN1
Thread Group: https://ibb.co/wKRQsQG
JDBC Request: https://ibb.co/T4ng1R9
Commit: https://ibb.co/dcBVHNF
CSV: https://ibb.co/Y8TRRRZ
Timer: https://ibb.co/yXX4PgM
Counter: https://ibb.co/Xb9B47Q

Thanks!


On 9/3/20 12:40 PM, UBIK LOAD PACK Support wrote:

> Hello,
> Can you share your test plan ?
> or run in JMeter 5.X: Menu Tools > Generate Schematic Overview.
> Or  screenshots  of your test plan (the Tree + Screenshots of each JDBC
> Request element)
> Thanks
>
> On Thu, Sep 3, 2020 at 6:16 PM Brian Flowers <[hidden email]>
> wrote:
>
>> Hi everyone,
>>
>> I'm having some issues using JMeter to insert some records in a MariaDB
>> database with autocommit disabled (the idea being that we want to commit
>> every ~1000 records, not after each one).
>>
>> Did some searches and couldn't find any documentation or tutorials
>> explaining this...I got desperate enough to ask on StackOverflow :) but
>> the only response so far seems to indicate that I'm configuring it
>> correctly:
>> https://stackoverflow.com/questions/63713516/jmeter-jdbc-manual-commit
>>
>> I started with JMeter 3.2 connecting via the mysql connector version
>> 5.1.27 as that's what we already had...I realize those are pretty old,
>> so I did try upgrading, but got the same results. Tried on JMeter 5.3
>> with mysql connector 8.0.21, and also with the dedicated mariadb
>> connector version 2.11.3 (all connectors from the Maven repository).
>> With auto commit true, any combination of those versions works fine.
>> With auto commit false, I can't get my data committed on any of them.
>>
>> What I have set up right now is a thread group with one thread, than
>> contains a JDBC request with a single INSERT statement, using a couple
>> variables that it takes from a csv data set and a counter, on a constant
>> throughput timer, and I'm using the loop count in the thread group to
>> control the number of records inserted. When I have auto commit set to
>> true in the JDBC configuration, the records all get inserted just fine.
>> But when I turn auto commit off, I can't get those statements committed.
>> I set the JDBC request query type to "AutoCommit(false)" instead of
>> "Update Statement", then I added a second JDBC request on the same
>> configuration with request type of "Commit". In the results tree I can
>> see a commit statement following each insert statement with no errors,
>> but the records don't actually get committed in the DB. I tried adding
>> the commit inside the original JDBC request (just to see if that'd work)
>> but that gave a SQL error; I tried adding a commit post processor within
>> the main JDBC request, but no luck there. I tried adding a pre-processor
>> to open a transaction, assuming that it wasn't including the commit and
>> the insert on the same transaction, but no change with that. I tried
>> configuration transaction isolation as DEFAULT or as
>> TRANSACTION_SERIALIZABLE but that had no apparent effect either.
>>
>> So...how do I manually commit an insert statement on a mariaDB database?
>> Or what else can I check to try to diagnose exactly what is going on
>> here? Are there any resources or documentation about exactly how to use
>> the autocommit setting?
>>
>> Thanks!
>> Brian Flowers
>> [hidden email]
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>> --
>> <[hidden email]>
>>
>> Regards
>> <[hidden email]>Ubik Load Pack <http://ubikloadpack.com> Team
>> Follow us on Twitter <http://twitter.com/ubikloadpack>
>>
>>
>> Cordialement
>> L'équipe Ubik Load Pack <http://ubikloadpack.com>
>> Suivez-nous sur Twitter <http://twitter.com/ubikloadpack>
>>
>>
>>
>>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: JDBC with autocommit=false

Felix Schumacher
In reply to this post by Brian Flowers

Am 03.09.20 um 18:16 schrieb Brian Flowers:

> Hi everyone,
>
> I'm having some issues using JMeter to insert some records in a MariaDB
> database with autocommit disabled (the idea being that we want to commit
> every ~1000 records, not after each one).
>
> Did some searches and couldn't find any documentation or tutorials
> explaining this...I got desperate enough to ask on StackOverflow :) but
> the only response so far seems to indicate that I'm configuring it
> correctly:
> https://stackoverflow.com/questions/63713516/jmeter-jdbc-manual-commit
>
> I started with JMeter 3.2 connecting via the mysql connector version
> 5.1.27 as that's what we already had...I realize those are pretty old,
> so I did try upgrading, but got the same results. Tried on JMeter 5.3
> with mysql connector 8.0.21, and also with the dedicated mariadb
> connector version 2.11.3 (all connectors from the Maven repository).
> With auto commit true, any combination of those versions works fine.
> With auto commit false, I can't get my data committed on any of them.
>
> What I have set up right now is a thread group with one thread, than
> contains a JDBC request with a single INSERT statement, using a couple
> variables that it takes from a csv data set and a counter, on a constant
> throughput timer, and I'm using the loop count in the thread group to
> control the number of records inserted. When I have auto commit set to
> true in the JDBC configuration, the records all get inserted just fine.
> But when I turn auto commit off, I can't get those statements committed.
> I set the JDBC request query type to "AutoCommit(false)" instead of
> "Update Statement", then I added a second JDBC request on the same
> configuration with request type of "Commit". In the results tree I can
> see a commit statement following each insert statement with no errors,
> but the records don't actually get committed in the DB. I tried adding
> the commit inside the original JDBC request (just to see if that'd work)
> but that gave a SQL error; I tried adding a commit post processor within
> the main JDBC request, but no luck there. I tried adding a pre-processor
> to open a transaction, assuming that it wasn't including the commit and
> the insert on the same transaction, but no change with that. I tried
> configuration transaction isolation as DEFAULT or as
> TRANSACTION_SERIALIZABLE but that had no apparent effect either.
>
> So...how do I manually commit an insert statement on a mariaDB database?
> Or what else can I check to try to diagnose exactly what is going on
> here? Are there any resources or documentation about exactly how to use
> the autocommit setting?
I have (tried) to attach a minimal test plan, that works for me. It was
tested with a MariaDB in a docker instance, that I started with

 $ docker run --rm -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -ti
mariadb

I then added  a database named db to it with a mysql client

 $ docker exec  mydb /bin/bash -c 'echo "create database db;" | mysql
--password=my-secret-pw'

In the test plan I have one thread group with one thread. It contains a
jdbc config which has set autocommit to false and a name of db. I used a
init sql statement to create a table:

begin; create table if not exists person (id int, name text); commit;

and filled in the database connection parameters

url: jdbc:mysql://localhost:3306/db
driver class: com.mysql.jdbc.Driver
username: root
password: my-secret-pw

(Oh, and don't forget to add the driver jar somewhere JMeter can find it ;))

Now, for the logic I added loop controller named loop and placed an jdbc
sampler into it.

That sampler was named "insert data" and had set the auto commit field
set to false. Its type was prepared update statement and the query was
"insert into person values (?, ?)". Parameter values and types were
${__jm__loop__idx},${__RandomString(10,abcdefghijklmnopqrstuvwxyz,)} and
INTEGER,VARCHAR.

After the loop I verified that the transaction had all the data with a
jdbc sampler named "view data", that had the type set to select
statement and the query "select * from person".

Now to rollback (or commit), I used another jdbc sampler called "roll
back" with a query type of "Rollback".

To verify that rollback worked. I added a last jdbc sampler named "view
data (again)" with the same type and statement as "view data".

As I wanted to see all those requests and their responses, I added a
tree results view.

So, reading your message correctly, I think you want to try changing the
query type of your statements back to update/select and adding a commit
typed query every once in a while.

Felix

>
> Thanks!
> Brian Flowers
> [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

mysql-roolback.jmx (8K) Download Attachment
pmd
Reply | Threaded
Open this post in threaded view
|

Re: JDBC with autocommit=false

pmd
Hello Felix,

Thanks for answer, just 2 notes:

   - I guess rollback should be named commit (just a matter of naming)
   - More important, do we need to put a "commit"  in Query for  Query
   Type: Commit, reading the code I am not sure text is used  ?:
      -
      https://github.com/apache/jmeter/blob/master/src/protocol/jdbc/src/main/java/org/apache/jmeter/protocol/jdbc/AbstractJDBCTestElement.java#L224


Regards

On Thu, Sep 3, 2020 at 9:33 PM Felix Schumacher <
[hidden email]> wrote:

>
> Am 03.09.20 um 18:16 schrieb Brian Flowers:
> > Hi everyone,
> >
> > I'm having some issues using JMeter to insert some records in a MariaDB
> > database with autocommit disabled (the idea being that we want to commit
> > every ~1000 records, not after each one).
> >
> > Did some searches and couldn't find any documentation or tutorials
> > explaining this...I got desperate enough to ask on StackOverflow :) but
> > the only response so far seems to indicate that I'm configuring it
> > correctly:
> > https://stackoverflow.com/questions/63713516/jmeter-jdbc-manual-commit
> >
> > I started with JMeter 3.2 connecting via the mysql connector version
> > 5.1.27 as that's what we already had...I realize those are pretty old,
> > so I did try upgrading, but got the same results. Tried on JMeter 5.3
> > with mysql connector 8.0.21, and also with the dedicated mariadb
> > connector version 2.11.3 (all connectors from the Maven repository).
> > With auto commit true, any combination of those versions works fine.
> > With auto commit false, I can't get my data committed on any of them.
> >
> > What I have set up right now is a thread group with one thread, than
> > contains a JDBC request with a single INSERT statement, using a couple
> > variables that it takes from a csv data set and a counter, on a constant
> > throughput timer, and I'm using the loop count in the thread group to
> > control the number of records inserted. When I have auto commit set to
> > true in the JDBC configuration, the records all get inserted just fine.
> > But when I turn auto commit off, I can't get those statements committed.
> > I set the JDBC request query type to "AutoCommit(false)" instead of
> > "Update Statement", then I added a second JDBC request on the same
> > configuration with request type of "Commit". In the results tree I can
> > see a commit statement following each insert statement with no errors,
> > but the records don't actually get committed in the DB. I tried adding
> > the commit inside the original JDBC request (just to see if that'd work)
> > but that gave a SQL error; I tried adding a commit post processor within
> > the main JDBC request, but no luck there. I tried adding a pre-processor
> > to open a transaction, assuming that it wasn't including the commit and
> > the insert on the same transaction, but no change with that. I tried
> > configuration transaction isolation as DEFAULT or as
> > TRANSACTION_SERIALIZABLE but that had no apparent effect either.
> >
> > So...how do I manually commit an insert statement on a mariaDB database?
> > Or what else can I check to try to diagnose exactly what is going on
> > here? Are there any resources or documentation about exactly how to use
> > the autocommit setting?
>
> I have (tried) to attach a minimal test plan, that works for me. It was
> tested with a MariaDB in a docker instance, that I started with
>
>  $ docker run --rm -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -ti
> mariadb
>
> I then added  a database named db to it with a mysql client
>
>  $ docker exec  mydb /bin/bash -c 'echo "create database db;" | mysql
> --password=my-secret-pw'
>
> In the test plan I have one thread group with one thread. It contains a
> jdbc config which has set autocommit to false and a name of db. I used a
> init sql statement to create a table:
>
> begin; create table if not exists person (id int, name text); commit;
>
> and filled in the database connection parameters
>
> url: jdbc:mysql://localhost:3306/db
> driver class: com.mysql.jdbc.Driver
> username: root
> password: my-secret-pw
>
> (Oh, and don't forget to add the driver jar somewhere JMeter can find it
> ;))
>
> Now, for the logic I added loop controller named loop and placed an jdbc
> sampler into it.
>
> That sampler was named "insert data" and had set the auto commit field
> set to false. Its type was prepared update statement and the query was
> "insert into person values (?, ?)". Parameter values and types were
> ${__jm__loop__idx},${__RandomString(10,abcdefghijklmnopqrstuvwxyz,)} and
> INTEGER,VARCHAR.
>
> After the loop I verified that the transaction had all the data with a
> jdbc sampler named "view data", that had the type set to select
> statement and the query "select * from person".
>
> Now to rollback (or commit), I used another jdbc sampler called "roll
> back" with a query type of "Rollback".
>
> To verify that rollback worked. I added a last jdbc sampler named "view
> data (again)" with the same type and statement as "view data".
>
> As I wanted to see all those requests and their responses, I added a
> tree results view.
>
> So, reading your message correctly, I think you want to try changing the
> query type of your statements back to update/select and adding a commit
> typed query every once in a while.
>
> Felix
>
> >
> > Thanks!
> > Brian Flowers
> > [hidden email]
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [hidden email]
> > For additional commands, e-mail: [hidden email]
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]



--
Cordialement.
Philippe Mouawad.
Reply | Threaded
Open this post in threaded view
|

Re: JDBC with autocommit=false

Felix Schumacher

Am 03.09.20 um 22:00 schrieb Philippe Mouawad:
> Hello Felix,
>
> Thanks for answer, just 2 notes:
>
>    - I guess rollback should be named commit (just a matter of naming)
>    - More important, do we need to put a "commit"  in Query for  Query
>    Type: Commit, reading the code I am not sure text is used  ?:
>       -
>       https://github.com/apache/jmeter/blob/master/src/protocol/jdbc/src/main/java/org/apache/jmeter/protocol/jdbc/AbstractJDBCTestElement.java#L224


I am not sure, what you mean by this. In my example, I used rollback
instead of commit, as I think it shows the way the samplers work more
nicely.

The OP wanted to have commit, so my comment (... (or commit) ...) was a
placeholder to show, where the type has to be changed (and the name of
the sampler should be changed, too).

I think the documentation could be made clearer, that the special types
commit, rollback, autocommit(true) and autocommit(false) are really that
(special) and will ignore the given content of the sql statements. And
while we are at it, the sql field could be disabled when the special
types are selected.

For the OP the most important take away is probably, that
autocommit(false) switches the current connection into transaction mode,
that has to be either committed or rollbacked and that the type to use
in his scenario would have been "commit" AND that the sql statement in
the special typed samplers are ignored.

The "begin; create ...; commit" as init sql -- in my example -- was
needed, as the connections are initialized in transaction mode
(autocommit(false)) and I really wanted to create the table.

Felix


>
>
> Regards
>
> On Thu, Sep 3, 2020 at 9:33 PM Felix Schumacher <
> [hidden email]> wrote:
>
>> Am 03.09.20 um 18:16 schrieb Brian Flowers:
>>> Hi everyone,
>>>
>>> I'm having some issues using JMeter to insert some records in a MariaDB
>>> database with autocommit disabled (the idea being that we want to commit
>>> every ~1000 records, not after each one).
>>>
>>> Did some searches and couldn't find any documentation or tutorials
>>> explaining this...I got desperate enough to ask on StackOverflow :) but
>>> the only response so far seems to indicate that I'm configuring it
>>> correctly:
>>> https://stackoverflow.com/questions/63713516/jmeter-jdbc-manual-commit
>>>
>>> I started with JMeter 3.2 connecting via the mysql connector version
>>> 5.1.27 as that's what we already had...I realize those are pretty old,
>>> so I did try upgrading, but got the same results. Tried on JMeter 5.3
>>> with mysql connector 8.0.21, and also with the dedicated mariadb
>>> connector version 2.11.3 (all connectors from the Maven repository).
>>> With auto commit true, any combination of those versions works fine.
>>> With auto commit false, I can't get my data committed on any of them.
>>>
>>> What I have set up right now is a thread group with one thread, than
>>> contains a JDBC request with a single INSERT statement, using a couple
>>> variables that it takes from a csv data set and a counter, on a constant
>>> throughput timer, and I'm using the loop count in the thread group to
>>> control the number of records inserted. When I have auto commit set to
>>> true in the JDBC configuration, the records all get inserted just fine.
>>> But when I turn auto commit off, I can't get those statements committed.
>>> I set the JDBC request query type to "AutoCommit(false)" instead of
>>> "Update Statement", then I added a second JDBC request on the same
>>> configuration with request type of "Commit". In the results tree I can
>>> see a commit statement following each insert statement with no errors,
>>> but the records don't actually get committed in the DB. I tried adding
>>> the commit inside the original JDBC request (just to see if that'd work)
>>> but that gave a SQL error; I tried adding a commit post processor within
>>> the main JDBC request, but no luck there. I tried adding a pre-processor
>>> to open a transaction, assuming that it wasn't including the commit and
>>> the insert on the same transaction, but no change with that. I tried
>>> configuration transaction isolation as DEFAULT or as
>>> TRANSACTION_SERIALIZABLE but that had no apparent effect either.
>>>
>>> So...how do I manually commit an insert statement on a mariaDB database?
>>> Or what else can I check to try to diagnose exactly what is going on
>>> here? Are there any resources or documentation about exactly how to use
>>> the autocommit setting?
>> I have (tried) to attach a minimal test plan, that works for me. It was
>> tested with a MariaDB in a docker instance, that I started with
>>
>>  $ docker run --rm -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -ti
>> mariadb
>>
>> I then added  a database named db to it with a mysql client
>>
>>  $ docker exec  mydb /bin/bash -c 'echo "create database db;" | mysql
>> --password=my-secret-pw'
>>
>> In the test plan I have one thread group with one thread. It contains a
>> jdbc config which has set autocommit to false and a name of db. I used a
>> init sql statement to create a table:
>>
>> begin; create table if not exists person (id int, name text); commit;
>>
>> and filled in the database connection parameters
>>
>> url: jdbc:mysql://localhost:3306/db
>> driver class: com.mysql.jdbc.Driver
>> username: root
>> password: my-secret-pw
>>
>> (Oh, and don't forget to add the driver jar somewhere JMeter can find it
>> ;))
>>
>> Now, for the logic I added loop controller named loop and placed an jdbc
>> sampler into it.
>>
>> That sampler was named "insert data" and had set the auto commit field
>> set to false. Its type was prepared update statement and the query was
>> "insert into person values (?, ?)". Parameter values and types were
>> ${__jm__loop__idx},${__RandomString(10,abcdefghijklmnopqrstuvwxyz,)} and
>> INTEGER,VARCHAR.
>>
>> After the loop I verified that the transaction had all the data with a
>> jdbc sampler named "view data", that had the type set to select
>> statement and the query "select * from person".
>>
>> Now to rollback (or commit), I used another jdbc sampler called "roll
>> back" with a query type of "Rollback".
>>
>> To verify that rollback worked. I added a last jdbc sampler named "view
>> data (again)" with the same type and statement as "view data".
>>
>> As I wanted to see all those requests and their responses, I added a
>> tree results view.
>>
>> So, reading your message correctly, I think you want to try changing the
>> query type of your statements back to update/select and adding a commit
>> typed query every once in a while.
>>
>> Felix
>>
>>> Thanks!
>>> Brian Flowers
>>> [hidden email]
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [hidden email]
>>> For additional commands, e-mail: [hidden email]
>>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|

Re: JDBC with autocommit=false

Philippe Mouawad
On Fri, Sep 4, 2020 at 8:43 AM Felix Schumacher <
[hidden email]> wrote:

>
> Am 03.09.20 um 22:00 schrieb Philippe Mouawad:
> > Hello Felix,
> >
> > Thanks for answer, just 2 notes:
> >
> >    - I guess rollback should be named commit (just a matter of naming)
> >    - More important, do we need to put a "commit"  in Query for  Query
> >    Type: Commit, reading the code I am not sure text is used  ?:
> >       -
> >
> https://github.com/apache/jmeter/blob/master/src/protocol/jdbc/src/main/java/org/apache/jmeter/protocol/jdbc/AbstractJDBCTestElement.java#L224
>
>
> I am not sure, what you mean by this. In my example, I used rollback
> instead of commit, as I think it shows the way the samplers work more
> nicely.
>
Just that the name of the element is rollback and Query Type is commit.

>
> The OP wanted to have commit, so my comment (... (or commit) ...) was a
> placeholder to show, where the type has to be changed (and the name of
> the sampler should be changed, too).
>
ok

>
> I think the documentation could be made clearer, that the special types
> commit, rollback, autocommit(true) and autocommit(false) are really that
> (special) and will ignore the given content of the sql statements.


I agree

> And
> while we are at it, the sql field could be disabled when the special
> types are selected.
>
As it's a Generic TestBeanGU I am afraid it might not be that easy to do

>
> For the OP the most important take away is probably, that
> autocommit(false) switches the current connection into transaction mode,
> that has to be either committed or rollbacked and that the type to use
> in his scenario would have been "commit" AND that the sql statement in
> the special typed samplers are ignored.
>
> The "begin; create ...; commit" as init sql -- in my example -- was
> needed, as the connections are initialized in transaction mode
> (autocommit(false)) and I really wanted to create the table.
>

Yes very clear, I didn't say anything about that
Thanks

>
> Felix
>
>
> >
> >
> > Regards
> >
> > On Thu, Sep 3, 2020 at 9:33 PM Felix Schumacher <
> > [hidden email]> wrote:
> >
> >> Am 03.09.20 um 18:16 schrieb Brian Flowers:
> >>> Hi everyone,
> >>>
> >>> I'm having some issues using JMeter to insert some records in a MariaDB
> >>> database with autocommit disabled (the idea being that we want to
> commit
> >>> every ~1000 records, not after each one).
> >>>
> >>> Did some searches and couldn't find any documentation or tutorials
> >>> explaining this...I got desperate enough to ask on StackOverflow :) but
> >>> the only response so far seems to indicate that I'm configuring it
> >>> correctly:
> >>> https://stackoverflow.com/questions/63713516/jmeter-jdbc-manual-commit
> >>>
> >>> I started with JMeter 3.2 connecting via the mysql connector version
> >>> 5.1.27 as that's what we already had...I realize those are pretty old,
> >>> so I did try upgrading, but got the same results. Tried on JMeter 5.3
> >>> with mysql connector 8.0.21, and also with the dedicated mariadb
> >>> connector version 2.11.3 (all connectors from the Maven repository).
> >>> With auto commit true, any combination of those versions works fine.
> >>> With auto commit false, I can't get my data committed on any of them.
> >>>
> >>> What I have set up right now is a thread group with one thread, than
> >>> contains a JDBC request with a single INSERT statement, using a couple
> >>> variables that it takes from a csv data set and a counter, on a
> constant
> >>> throughput timer, and I'm using the loop count in the thread group to
> >>> control the number of records inserted. When I have auto commit set to
> >>> true in the JDBC configuration, the records all get inserted just fine.
> >>> But when I turn auto commit off, I can't get those statements
> committed.
> >>> I set the JDBC request query type to "AutoCommit(false)" instead of
> >>> "Update Statement", then I added a second JDBC request on the same
> >>> configuration with request type of "Commit". In the results tree I can
> >>> see a commit statement following each insert statement with no errors,
> >>> but the records don't actually get committed in the DB. I tried adding
> >>> the commit inside the original JDBC request (just to see if that'd
> work)
> >>> but that gave a SQL error; I tried adding a commit post processor
> within
> >>> the main JDBC request, but no luck there. I tried adding a
> pre-processor
> >>> to open a transaction, assuming that it wasn't including the commit and
> >>> the insert on the same transaction, but no change with that. I tried
> >>> configuration transaction isolation as DEFAULT or as
> >>> TRANSACTION_SERIALIZABLE but that had no apparent effect either.
> >>>
> >>> So...how do I manually commit an insert statement on a mariaDB
> database?
> >>> Or what else can I check to try to diagnose exactly what is going on
> >>> here? Are there any resources or documentation about exactly how to use
> >>> the autocommit setting?
> >> I have (tried) to attach a minimal test plan, that works for me. It was
> >> tested with a MariaDB in a docker instance, that I started with
> >>
> >>  $ docker run --rm -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -ti
> >> mariadb
> >>
> >> I then added  a database named db to it with a mysql client
> >>
> >>  $ docker exec  mydb /bin/bash -c 'echo "create database db;" | mysql
> >> --password=my-secret-pw'
> >>
> >> In the test plan I have one thread group with one thread. It contains a
> >> jdbc config which has set autocommit to false and a name of db. I used a
> >> init sql statement to create a table:
> >>
> >> begin; create table if not exists person (id int, name text); commit;
> >>
> >> and filled in the database connection parameters
> >>
> >> url: jdbc:mysql://localhost:3306/db
> >> driver class: com.mysql.jdbc.Driver
> >> username: root
> >> password: my-secret-pw
> >>
> >> (Oh, and don't forget to add the driver jar somewhere JMeter can find it
> >> ;))
> >>
> >> Now, for the logic I added loop controller named loop and placed an jdbc
> >> sampler into it.
> >>
> >> That sampler was named "insert data" and had set the auto commit field
> >> set to false. Its type was prepared update statement and the query was
> >> "insert into person values (?, ?)". Parameter values and types were
> >> ${__jm__loop__idx},${__RandomString(10,abcdefghijklmnopqrstuvwxyz,)} and
> >> INTEGER,VARCHAR.
> >>
> >> After the loop I verified that the transaction had all the data with a
> >> jdbc sampler named "view data", that had the type set to select
> >> statement and the query "select * from person".
> >>
> >> Now to rollback (or commit), I used another jdbc sampler called "roll
> >> back" with a query type of "Rollback".
> >>
> >> To verify that rollback worked. I added a last jdbc sampler named "view
> >> data (again)" with the same type and statement as "view data".
> >>
> >> As I wanted to see all those requests and their responses, I added a
> >> tree results view.
> >>
> >> So, reading your message correctly, I think you want to try changing the
> >> query type of your statements back to update/select and adding a commit
> >> typed query every once in a while.
> >>
> >> Felix
> >>
> >>> Thanks!
> >>> Brian Flowers
> >>> [hidden email]
> >>>
> >>>
> >>> ---------------------------------------------------------------------
> >>> To unsubscribe, e-mail: [hidden email]
> >>> For additional commands, e-mail: [hidden email]
> >>>
> >> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: [hidden email]
> >> For additional commands, e-mail: [hidden email]
> >
> >
>


--
Cordialement
Philippe M.
Ubik-Ingenierie
Philippe M.
@philmdot
http://ubikloadpack.com
Reply | Threaded
Open this post in threaded view
|

Re: JDBC with autocommit=false

Felix Schumacher


Am 4. September 2020 08:50:08 MESZ schrieb Philippe Mouawad <[hidden email]>:

>On Fri, Sep 4, 2020 at 8:43 AM Felix Schumacher <
>[hidden email]> wrote:
>
>>
>> Am 03.09.20 um 22:00 schrieb Philippe Mouawad:
>> > Hello Felix,
>> >
>> > Thanks for answer, just 2 notes:
>> >
>> >    - I guess rollback should be named commit (just a matter of
>naming)
>> >    - More important, do we need to put a "commit"  in Query for
>Query
>> >    Type: Commit, reading the code I am not sure text is used  ?:
>> >       -
>> >
>>
>https://github.com/apache/jmeter/blob/master/src/protocol/jdbc/src/main/java/org/apache/jmeter/protocol/jdbc/AbstractJDBCTestElement.java#L224
>>
>>
>> I am not sure, what you mean by this. In my example, I used rollback
>> instead of commit, as I think it shows the way the samplers work more
>> nicely.
>>
>Just that the name of the element is rollback and Query Type is commit.

Hm. Maybe I changed the type to test the commit case and used that in my mail. Wasn't my intention. I wanted to show rollback. Sorry for the confusion.

>
>>
>> The OP wanted to have commit, so my comment (... (or commit) ...) was
>a
>> placeholder to show, where the type has to be changed (and the name
>of
>> the sampler should be changed, too).
>>
>ok
>
>>
>> I think the documentation could be made clearer, that the special
>types
>> commit, rollback, autocommit(true) and autocommit(false) are really
>that
>> (special) and will ignore the given content of the sql statements.
>
>
>I agree
>
>> And
>> while we are at it, the sql field could be disabled when the special
>> types are selected.
>>
>As it's a Generic TestBeanGU I am afraid it might not be that easy to
>do
>

Right

>>
>> For the OP the most important take away is probably, that
>> autocommit(false) switches the current connection into transaction
>mode,
>> that has to be either committed or rollbacked and that the type to
>use
>> in his scenario would have been "commit" AND that the sql statement
>in
>> the special typed samplers are ignored.
>>
>> The "begin; create ...; commit" as init sql -- in my example -- was
>> needed, as the connections are initialized in transaction mode
>> (autocommit(false)) and I really wanted to create the table.
>>
>
>Yes very clear, I didn't say anything about that

It wasn't clear to me, neither.

Felix

>Thanks
>
>>
>> Felix
>>
>>
>> >
>> >
>> > Regards
>> >
>> > On Thu, Sep 3, 2020 at 9:33 PM Felix Schumacher <
>> > [hidden email]> wrote:
>> >
>> >> Am 03.09.20 um 18:16 schrieb Brian Flowers:
>> >>> Hi everyone,
>> >>>
>> >>> I'm having some issues using JMeter to insert some records in a
>MariaDB
>> >>> database with autocommit disabled (the idea being that we want to
>> commit
>> >>> every ~1000 records, not after each one).
>> >>>
>> >>> Did some searches and couldn't find any documentation or
>tutorials
>> >>> explaining this...I got desperate enough to ask on StackOverflow
>:) but
>> >>> the only response so far seems to indicate that I'm configuring
>it
>> >>> correctly:
>> >>>
>https://stackoverflow.com/questions/63713516/jmeter-jdbc-manual-commit
>> >>>
>> >>> I started with JMeter 3.2 connecting via the mysql connector
>version
>> >>> 5.1.27 as that's what we already had...I realize those are pretty
>old,
>> >>> so I did try upgrading, but got the same results. Tried on JMeter
>5.3
>> >>> with mysql connector 8.0.21, and also with the dedicated mariadb
>> >>> connector version 2.11.3 (all connectors from the Maven
>repository).
>> >>> With auto commit true, any combination of those versions works
>fine.
>> >>> With auto commit false, I can't get my data committed on any of
>them.
>> >>>
>> >>> What I have set up right now is a thread group with one thread,
>than
>> >>> contains a JDBC request with a single INSERT statement, using a
>couple
>> >>> variables that it takes from a csv data set and a counter, on a
>> constant
>> >>> throughput timer, and I'm using the loop count in the thread
>group to
>> >>> control the number of records inserted. When I have auto commit
>set to
>> >>> true in the JDBC configuration, the records all get inserted just
>fine.
>> >>> But when I turn auto commit off, I can't get those statements
>> committed.
>> >>> I set the JDBC request query type to "AutoCommit(false)" instead
>of
>> >>> "Update Statement", then I added a second JDBC request on the
>same
>> >>> configuration with request type of "Commit". In the results tree
>I can
>> >>> see a commit statement following each insert statement with no
>errors,
>> >>> but the records don't actually get committed in the DB. I tried
>adding
>> >>> the commit inside the original JDBC request (just to see if
>that'd
>> work)
>> >>> but that gave a SQL error; I tried adding a commit post processor
>> within
>> >>> the main JDBC request, but no luck there. I tried adding a
>> pre-processor
>> >>> to open a transaction, assuming that it wasn't including the
>commit and
>> >>> the insert on the same transaction, but no change with that. I
>tried
>> >>> configuration transaction isolation as DEFAULT or as
>> >>> TRANSACTION_SERIALIZABLE but that had no apparent effect either.
>> >>>
>> >>> So...how do I manually commit an insert statement on a mariaDB
>> database?
>> >>> Or what else can I check to try to diagnose exactly what is going
>on
>> >>> here? Are there any resources or documentation about exactly how
>to use
>> >>> the autocommit setting?
>> >> I have (tried) to attach a minimal test plan, that works for me.
>It was
>> >> tested with a MariaDB in a docker instance, that I started with
>> >>
>> >>  $ docker run --rm -p 3306:3306 -e
>MYSQL_ROOT_PASSWORD=my-secret-pw -ti
>> >> mariadb
>> >>
>> >> I then added  a database named db to it with a mysql client
>> >>
>> >>  $ docker exec  mydb /bin/bash -c 'echo "create database db;" |
>mysql
>> >> --password=my-secret-pw'
>> >>
>> >> In the test plan I have one thread group with one thread. It
>contains a
>> >> jdbc config which has set autocommit to false and a name of db. I
>used a
>> >> init sql statement to create a table:
>> >>
>> >> begin; create table if not exists person (id int, name text);
>commit;
>> >>
>> >> and filled in the database connection parameters
>> >>
>> >> url: jdbc:mysql://localhost:3306/db
>> >> driver class: com.mysql.jdbc.Driver
>> >> username: root
>> >> password: my-secret-pw
>> >>
>> >> (Oh, and don't forget to add the driver jar somewhere JMeter can
>find it
>> >> ;))
>> >>
>> >> Now, for the logic I added loop controller named loop and placed
>an jdbc
>> >> sampler into it.
>> >>
>> >> That sampler was named "insert data" and had set the auto commit
>field
>> >> set to false. Its type was prepared update statement and the query
>was
>> >> "insert into person values (?, ?)". Parameter values and types
>were
>> >>
>${__jm__loop__idx},${__RandomString(10,abcdefghijklmnopqrstuvwxyz,)}
>and
>> >> INTEGER,VARCHAR.
>> >>
>> >> After the loop I verified that the transaction had all the data
>with a
>> >> jdbc sampler named "view data", that had the type set to select
>> >> statement and the query "select * from person".
>> >>
>> >> Now to rollback (or commit), I used another jdbc sampler called
>"roll
>> >> back" with a query type of "Rollback".
>> >>
>> >> To verify that rollback worked. I added a last jdbc sampler named
>"view
>> >> data (again)" with the same type and statement as "view data".
>> >>
>> >> As I wanted to see all those requests and their responses, I added
>a
>> >> tree results view.
>> >>
>> >> So, reading your message correctly, I think you want to try
>changing the
>> >> query type of your statements back to update/select and adding a
>commit
>> >> typed query every once in a while.
>> >>
>> >> Felix
>> >>
>> >>> Thanks!
>> >>> Brian Flowers
>> >>> [hidden email]
>> >>>
>> >>>
>> >>>
>---------------------------------------------------------------------
>> >>> To unsubscribe, e-mail: [hidden email]
>> >>> For additional commands, e-mail: [hidden email]
>> >>>
>> >>
>---------------------------------------------------------------------
>> >> To unsubscribe, e-mail: [hidden email]
>> >> For additional commands, e-mail: [hidden email]
>> >
>> >
>>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: JDBC with autocommit=false

Felix Schumacher
In reply to this post by Philippe Mouawad

Am 04.09.20 um 08:50 schrieb Philippe Mouawad:

> On Fri, Sep 4, 2020 at 8:43 AM Felix Schumacher <
> [hidden email]> wrote:
>
>> Am 03.09.20 um 22:00 schrieb Philippe Mouawad:
>>> Hello Felix,
>>>
>>> Thanks for answer, just 2 notes:
>>>
>>>    - I guess rollback should be named commit (just a matter of naming)
>>>    - More important, do we need to put a "commit"  in Query for  Query
>>>    Type: Commit, reading the code I am not sure text is used  ?:
>>>       -
>>>
>> https://github.com/apache/jmeter/blob/master/src/protocol/jdbc/src/main/java/org/apache/jmeter/protocol/jdbc/AbstractJDBCTestElement.java#L224
>>
>>
>> I am not sure, what you mean by this. In my example, I used rollback
>> instead of commit, as I think it shows the way the samplers work more
>> nicely.
>>
> Just that the name of the element is rollback and Query Type is commit.
>
>> The OP wanted to have commit, so my comment (... (or commit) ...) was a
>> placeholder to show, where the type has to be changed (and the name of
>> the sampler should be changed, too).
>>
> ok
>
>> I think the documentation could be made clearer, that the special types
>> commit, rollback, autocommit(true) and autocommit(false) are really that
>> (special) and will ignore the given content of the sql statements.
>
> I agree

Done

Felix


>
>> And
>> while we are at it, the sql field could be disabled when the special
>> types are selected.
>>
> As it's a Generic TestBeanGU I am afraid it might not be that easy to do
>
>> For the OP the most important take away is probably, that
>> autocommit(false) switches the current connection into transaction mode,
>> that has to be either committed or rollbacked and that the type to use
>> in his scenario would have been "commit" AND that the sql statement in
>> the special typed samplers are ignored.
>>
>> The "begin; create ...; commit" as init sql -- in my example -- was
>> needed, as the connections are initialized in transaction mode
>> (autocommit(false)) and I really wanted to create the table.
>>
> Yes very clear, I didn't say anything about that
> Thanks
>
>> Felix
>>
>>
>>>
>>> Regards
>>>
>>> On Thu, Sep 3, 2020 at 9:33 PM Felix Schumacher <
>>> [hidden email]> wrote:
>>>
>>>> Am 03.09.20 um 18:16 schrieb Brian Flowers:
>>>>> Hi everyone,
>>>>>
>>>>> I'm having some issues using JMeter to insert some records in a MariaDB
>>>>> database with autocommit disabled (the idea being that we want to
>> commit
>>>>> every ~1000 records, not after each one).
>>>>>
>>>>> Did some searches and couldn't find any documentation or tutorials
>>>>> explaining this...I got desperate enough to ask on StackOverflow :) but
>>>>> the only response so far seems to indicate that I'm configuring it
>>>>> correctly:
>>>>> https://stackoverflow.com/questions/63713516/jmeter-jdbc-manual-commit
>>>>>
>>>>> I started with JMeter 3.2 connecting via the mysql connector version
>>>>> 5.1.27 as that's what we already had...I realize those are pretty old,
>>>>> so I did try upgrading, but got the same results. Tried on JMeter 5.3
>>>>> with mysql connector 8.0.21, and also with the dedicated mariadb
>>>>> connector version 2.11.3 (all connectors from the Maven repository).
>>>>> With auto commit true, any combination of those versions works fine.
>>>>> With auto commit false, I can't get my data committed on any of them.
>>>>>
>>>>> What I have set up right now is a thread group with one thread, than
>>>>> contains a JDBC request with a single INSERT statement, using a couple
>>>>> variables that it takes from a csv data set and a counter, on a
>> constant
>>>>> throughput timer, and I'm using the loop count in the thread group to
>>>>> control the number of records inserted. When I have auto commit set to
>>>>> true in the JDBC configuration, the records all get inserted just fine.
>>>>> But when I turn auto commit off, I can't get those statements
>> committed.
>>>>> I set the JDBC request query type to "AutoCommit(false)" instead of
>>>>> "Update Statement", then I added a second JDBC request on the same
>>>>> configuration with request type of "Commit". In the results tree I can
>>>>> see a commit statement following each insert statement with no errors,
>>>>> but the records don't actually get committed in the DB. I tried adding
>>>>> the commit inside the original JDBC request (just to see if that'd
>> work)
>>>>> but that gave a SQL error; I tried adding a commit post processor
>> within
>>>>> the main JDBC request, but no luck there. I tried adding a
>> pre-processor
>>>>> to open a transaction, assuming that it wasn't including the commit and
>>>>> the insert on the same transaction, but no change with that. I tried
>>>>> configuration transaction isolation as DEFAULT or as
>>>>> TRANSACTION_SERIALIZABLE but that had no apparent effect either.
>>>>>
>>>>> So...how do I manually commit an insert statement on a mariaDB
>> database?
>>>>> Or what else can I check to try to diagnose exactly what is going on
>>>>> here? Are there any resources or documentation about exactly how to use
>>>>> the autocommit setting?
>>>> I have (tried) to attach a minimal test plan, that works for me. It was
>>>> tested with a MariaDB in a docker instance, that I started with
>>>>
>>>>  $ docker run --rm -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -ti
>>>> mariadb
>>>>
>>>> I then added  a database named db to it with a mysql client
>>>>
>>>>  $ docker exec  mydb /bin/bash -c 'echo "create database db;" | mysql
>>>> --password=my-secret-pw'
>>>>
>>>> In the test plan I have one thread group with one thread. It contains a
>>>> jdbc config which has set autocommit to false and a name of db. I used a
>>>> init sql statement to create a table:
>>>>
>>>> begin; create table if not exists person (id int, name text); commit;
>>>>
>>>> and filled in the database connection parameters
>>>>
>>>> url: jdbc:mysql://localhost:3306/db
>>>> driver class: com.mysql.jdbc.Driver
>>>> username: root
>>>> password: my-secret-pw
>>>>
>>>> (Oh, and don't forget to add the driver jar somewhere JMeter can find it
>>>> ;))
>>>>
>>>> Now, for the logic I added loop controller named loop and placed an jdbc
>>>> sampler into it.
>>>>
>>>> That sampler was named "insert data" and had set the auto commit field
>>>> set to false. Its type was prepared update statement and the query was
>>>> "insert into person values (?, ?)". Parameter values and types were
>>>> ${__jm__loop__idx},${__RandomString(10,abcdefghijklmnopqrstuvwxyz,)} and
>>>> INTEGER,VARCHAR.
>>>>
>>>> After the loop I verified that the transaction had all the data with a
>>>> jdbc sampler named "view data", that had the type set to select
>>>> statement and the query "select * from person".
>>>>
>>>> Now to rollback (or commit), I used another jdbc sampler called "roll
>>>> back" with a query type of "Rollback".
>>>>
>>>> To verify that rollback worked. I added a last jdbc sampler named "view
>>>> data (again)" with the same type and statement as "view data".
>>>>
>>>> As I wanted to see all those requests and their responses, I added a
>>>> tree results view.
>>>>
>>>> So, reading your message correctly, I think you want to try changing the
>>>> query type of your statements back to update/select and adding a commit
>>>> typed query every once in a while.
>>>>
>>>> Felix
>>>>
>>>>> Thanks!
>>>>> Brian Flowers
>>>>> [hidden email]
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: [hidden email]
>>>>> For additional commands, e-mail: [hidden email]
>>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: [hidden email]
>>>> For additional commands, e-mail: [hidden email]
>>>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: JDBC with autocommit=false

Brian Flowers
Sorry for the delayed response, had some issues with the DB and had to
wait for the admin to get back from vacation.

I have tried configuring as shown in the jmx plan you provided, but the
records still aren't being committed...results tree shows the insert
query triggering successfully, but the table count doesn't change even
when queried from within the same jmeter test...

On 9/5/20 4:44 AM, Felix Schumacher wrote:

> Am 04.09.20 um 08:50 schrieb Philippe Mouawad:
>> On Fri, Sep 4, 2020 at 8:43 AM Felix Schumacher <
>> [hidden email]> wrote:
>>
>>> Am 03.09.20 um 22:00 schrieb Philippe Mouawad:
>>>> Hello Felix,
>>>>
>>>> Thanks for answer, just 2 notes:
>>>>
>>>>    - I guess rollback should be named commit (just a matter of naming)
>>>>    - More important, do we need to put a "commit"  in Query for  Query
>>>>    Type: Commit, reading the code I am not sure text is used  ?:
>>>>       -
>>>>
>>> https://github.com/apache/jmeter/blob/master/src/protocol/jdbc/src/main/java/org/apache/jmeter/protocol/jdbc/AbstractJDBCTestElement.java#L224
>>>
>>>
>>> I am not sure, what you mean by this. In my example, I used rollback
>>> instead of commit, as I think it shows the way the samplers work more
>>> nicely.
>>>
>> Just that the name of the element is rollback and Query Type is commit.
>>
>>> The OP wanted to have commit, so my comment (... (or commit) ...) was a
>>> placeholder to show, where the type has to be changed (and the name of
>>> the sampler should be changed, too).
>>>
>> ok
>>
>>> I think the documentation could be made clearer, that the special types
>>> commit, rollback, autocommit(true) and autocommit(false) are really that
>>> (special) and will ignore the given content of the sql statements.
>> I agree
> Done
>
> Felix
>
>
>>> And
>>> while we are at it, the sql field could be disabled when the special
>>> types are selected.
>>>
>> As it's a Generic TestBeanGU I am afraid it might not be that easy to do
>>
>>> For the OP the most important take away is probably, that
>>> autocommit(false) switches the current connection into transaction mode,
>>> that has to be either committed or rollbacked and that the type to use
>>> in his scenario would have been "commit" AND that the sql statement in
>>> the special typed samplers are ignored.
>>>
>>> The "begin; create ...; commit" as init sql -- in my example -- was
>>> needed, as the connections are initialized in transaction mode
>>> (autocommit(false)) and I really wanted to create the table.
>>>
>> Yes very clear, I didn't say anything about that
>> Thanks
>>
>>> Felix
>>>
>>>
>>>> Regards
>>>>
>>>> On Thu, Sep 3, 2020 at 9:33 PM Felix Schumacher <
>>>> [hidden email]> wrote:
>>>>
>>>>> Am 03.09.20 um 18:16 schrieb Brian Flowers:
>>>>>> Hi everyone,
>>>>>>
>>>>>> I'm having some issues using JMeter to insert some records in a MariaDB
>>>>>> database with autocommit disabled (the idea being that we want to
>>> commit
>>>>>> every ~1000 records, not after each one).
>>>>>>
>>>>>> Did some searches and couldn't find any documentation or tutorials
>>>>>> explaining this...I got desperate enough to ask on StackOverflow :) but
>>>>>> the only response so far seems to indicate that I'm configuring it
>>>>>> correctly:
>>>>>> https://stackoverflow.com/questions/63713516/jmeter-jdbc-manual-commit
>>>>>>
>>>>>> I started with JMeter 3.2 connecting via the mysql connector version
>>>>>> 5.1.27 as that's what we already had...I realize those are pretty old,
>>>>>> so I did try upgrading, but got the same results. Tried on JMeter 5.3
>>>>>> with mysql connector 8.0.21, and also with the dedicated mariadb
>>>>>> connector version 2.11.3 (all connectors from the Maven repository).
>>>>>> With auto commit true, any combination of those versions works fine.
>>>>>> With auto commit false, I can't get my data committed on any of them.
>>>>>>
>>>>>> What I have set up right now is a thread group with one thread, than
>>>>>> contains a JDBC request with a single INSERT statement, using a couple
>>>>>> variables that it takes from a csv data set and a counter, on a
>>> constant
>>>>>> throughput timer, and I'm using the loop count in the thread group to
>>>>>> control the number of records inserted. When I have auto commit set to
>>>>>> true in the JDBC configuration, the records all get inserted just fine.
>>>>>> But when I turn auto commit off, I can't get those statements
>>> committed.
>>>>>> I set the JDBC request query type to "AutoCommit(false)" instead of
>>>>>> "Update Statement", then I added a second JDBC request on the same
>>>>>> configuration with request type of "Commit". In the results tree I can
>>>>>> see a commit statement following each insert statement with no errors,
>>>>>> but the records don't actually get committed in the DB. I tried adding
>>>>>> the commit inside the original JDBC request (just to see if that'd
>>> work)
>>>>>> but that gave a SQL error; I tried adding a commit post processor
>>> within
>>>>>> the main JDBC request, but no luck there. I tried adding a
>>> pre-processor
>>>>>> to open a transaction, assuming that it wasn't including the commit and
>>>>>> the insert on the same transaction, but no change with that. I tried
>>>>>> configuration transaction isolation as DEFAULT or as
>>>>>> TRANSACTION_SERIALIZABLE but that had no apparent effect either.
>>>>>>
>>>>>> So...how do I manually commit an insert statement on a mariaDB
>>> database?
>>>>>> Or what else can I check to try to diagnose exactly what is going on
>>>>>> here? Are there any resources or documentation about exactly how to use
>>>>>> the autocommit setting?
>>>>> I have (tried) to attach a minimal test plan, that works for me. It was
>>>>> tested with a MariaDB in a docker instance, that I started with
>>>>>
>>>>>  $ docker run --rm -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -ti
>>>>> mariadb
>>>>>
>>>>> I then added  a database named db to it with a mysql client
>>>>>
>>>>>  $ docker exec  mydb /bin/bash -c 'echo "create database db;" | mysql
>>>>> --password=my-secret-pw'
>>>>>
>>>>> In the test plan I have one thread group with one thread. It contains a
>>>>> jdbc config which has set autocommit to false and a name of db. I used a
>>>>> init sql statement to create a table:
>>>>>
>>>>> begin; create table if not exists person (id int, name text); commit;
>>>>>
>>>>> and filled in the database connection parameters
>>>>>
>>>>> url: jdbc:mysql://localhost:3306/db
>>>>> driver class: com.mysql.jdbc.Driver
>>>>> username: root
>>>>> password: my-secret-pw
>>>>>
>>>>> (Oh, and don't forget to add the driver jar somewhere JMeter can find it
>>>>> ;))
>>>>>
>>>>> Now, for the logic I added loop controller named loop and placed an jdbc
>>>>> sampler into it.
>>>>>
>>>>> That sampler was named "insert data" and had set the auto commit field
>>>>> set to false. Its type was prepared update statement and the query was
>>>>> "insert into person values (?, ?)". Parameter values and types were
>>>>> ${__jm__loop__idx},${__RandomString(10,abcdefghijklmnopqrstuvwxyz,)} and
>>>>> INTEGER,VARCHAR.
>>>>>
>>>>> After the loop I verified that the transaction had all the data with a
>>>>> jdbc sampler named "view data", that had the type set to select
>>>>> statement and the query "select * from person".
>>>>>
>>>>> Now to rollback (or commit), I used another jdbc sampler called "roll
>>>>> back" with a query type of "Rollback".
>>>>>
>>>>> To verify that rollback worked. I added a last jdbc sampler named "view
>>>>> data (again)" with the same type and statement as "view data".
>>>>>
>>>>> As I wanted to see all those requests and their responses, I added a
>>>>> tree results view.
>>>>>
>>>>> So, reading your message correctly, I think you want to try changing the
>>>>> query type of your statements back to update/select and adding a commit
>>>>> typed query every once in a while.
>>>>>
>>>>> Felix
>>>>>
>>>>>> Thanks!
>>>>>> Brian Flowers
>>>>>> [hidden email]
>>>>>>
>>>>>>
>>>>>> ---------------------------------------------------------------------
>>>>>> To unsubscribe, e-mail: [hidden email]
>>>>>> For additional commands, e-mail: [hidden email]
>>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: [hidden email]
>>>>> For additional commands, e-mail: [hidden email]
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: JDBC with autocommit=false

Felix Schumacher


Am 10. September 2020 20:06:08 MESZ schrieb Brian Flowers <[hidden email]>:
>Sorry for the delayed response, had some issues with the DB and had to
>wait for the admin to get back from vacation.
>
>I have tried configuring as shown in the jmx plan you provided, but the
>records still aren't being committed...results tree shows the insert
>query triggering successfully, but the table count doesn't change even
>when queried from within the same jmeter test...

Did you change the rollback type to commit?

Felix

>
>On 9/5/20 4:44 AM, Felix Schumacher wrote:
>> Am 04.09.20 um 08:50 schrieb Philippe Mouawad:
>>> On Fri, Sep 4, 2020 at 8:43 AM Felix Schumacher <
>>> [hidden email]> wrote:
>>>
>>>> Am 03.09.20 um 22:00 schrieb Philippe Mouawad:
>>>>> Hello Felix,
>>>>>
>>>>> Thanks for answer, just 2 notes:
>>>>>
>>>>>    - I guess rollback should be named commit (just a matter of
>naming)
>>>>>    - More important, do we need to put a "commit"  in Query for
>Query
>>>>>    Type: Commit, reading the code I am not sure text is used  ?:
>>>>>       -
>>>>>
>>>>
>https://github.com/apache/jmeter/blob/master/src/protocol/jdbc/src/main/java/org/apache/jmeter/protocol/jdbc/AbstractJDBCTestElement.java#L224
>>>>
>>>>
>>>> I am not sure, what you mean by this. In my example, I used
>rollback
>>>> instead of commit, as I think it shows the way the samplers work
>more
>>>> nicely.
>>>>
>>> Just that the name of the element is rollback and Query Type is
>commit.
>>>
>>>> The OP wanted to have commit, so my comment (... (or commit) ...)
>was a
>>>> placeholder to show, where the type has to be changed (and the name
>of
>>>> the sampler should be changed, too).
>>>>
>>> ok
>>>
>>>> I think the documentation could be made clearer, that the special
>types
>>>> commit, rollback, autocommit(true) and autocommit(false) are really
>that
>>>> (special) and will ignore the given content of the sql statements.
>>> I agree
>> Done
>>
>> Felix
>>
>>
>>>> And
>>>> while we are at it, the sql field could be disabled when the
>special
>>>> types are selected.
>>>>
>>> As it's a Generic TestBeanGU I am afraid it might not be that easy
>to do
>>>
>>>> For the OP the most important take away is probably, that
>>>> autocommit(false) switches the current connection into transaction
>mode,
>>>> that has to be either committed or rollbacked and that the type to
>use
>>>> in his scenario would have been "commit" AND that the sql statement
>in
>>>> the special typed samplers are ignored.
>>>>
>>>> The "begin; create ...; commit" as init sql -- in my example -- was
>>>> needed, as the connections are initialized in transaction mode
>>>> (autocommit(false)) and I really wanted to create the table.
>>>>
>>> Yes very clear, I didn't say anything about that
>>> Thanks
>>>
>>>> Felix
>>>>
>>>>
>>>>> Regards
>>>>>
>>>>> On Thu, Sep 3, 2020 at 9:33 PM Felix Schumacher <
>>>>> [hidden email]> wrote:
>>>>>
>>>>>> Am 03.09.20 um 18:16 schrieb Brian Flowers:
>>>>>>> Hi everyone,
>>>>>>>
>>>>>>> I'm having some issues using JMeter to insert some records in a
>MariaDB
>>>>>>> database with autocommit disabled (the idea being that we want
>to
>>>> commit
>>>>>>> every ~1000 records, not after each one).
>>>>>>>
>>>>>>> Did some searches and couldn't find any documentation or
>tutorials
>>>>>>> explaining this...I got desperate enough to ask on StackOverflow
>:) but
>>>>>>> the only response so far seems to indicate that I'm configuring
>it
>>>>>>> correctly:
>>>>>>>
>https://stackoverflow.com/questions/63713516/jmeter-jdbc-manual-commit
>>>>>>>
>>>>>>> I started with JMeter 3.2 connecting via the mysql connector
>version
>>>>>>> 5.1.27 as that's what we already had...I realize those are
>pretty old,
>>>>>>> so I did try upgrading, but got the same results. Tried on
>JMeter 5.3
>>>>>>> with mysql connector 8.0.21, and also with the dedicated mariadb
>>>>>>> connector version 2.11.3 (all connectors from the Maven
>repository).
>>>>>>> With auto commit true, any combination of those versions works
>fine.
>>>>>>> With auto commit false, I can't get my data committed on any of
>them.
>>>>>>>
>>>>>>> What I have set up right now is a thread group with one thread,
>than
>>>>>>> contains a JDBC request with a single INSERT statement, using a
>couple
>>>>>>> variables that it takes from a csv data set and a counter, on a
>>>> constant
>>>>>>> throughput timer, and I'm using the loop count in the thread
>group to
>>>>>>> control the number of records inserted. When I have auto commit
>set to
>>>>>>> true in the JDBC configuration, the records all get inserted
>just fine.
>>>>>>> But when I turn auto commit off, I can't get those statements
>>>> committed.
>>>>>>> I set the JDBC request query type to "AutoCommit(false)" instead
>of
>>>>>>> "Update Statement", then I added a second JDBC request on the
>same
>>>>>>> configuration with request type of "Commit". In the results tree
>I can
>>>>>>> see a commit statement following each insert statement with no
>errors,
>>>>>>> but the records don't actually get committed in the DB. I tried
>adding
>>>>>>> the commit inside the original JDBC request (just to see if
>that'd
>>>> work)
>>>>>>> but that gave a SQL error; I tried adding a commit post
>processor
>>>> within
>>>>>>> the main JDBC request, but no luck there. I tried adding a
>>>> pre-processor
>>>>>>> to open a transaction, assuming that it wasn't including the
>commit and
>>>>>>> the insert on the same transaction, but no change with that. I
>tried
>>>>>>> configuration transaction isolation as DEFAULT or as
>>>>>>> TRANSACTION_SERIALIZABLE but that had no apparent effect either.
>>>>>>>
>>>>>>> So...how do I manually commit an insert statement on a mariaDB
>>>> database?
>>>>>>> Or what else can I check to try to diagnose exactly what is
>going on
>>>>>>> here? Are there any resources or documentation about exactly how
>to use
>>>>>>> the autocommit setting?
>>>>>> I have (tried) to attach a minimal test plan, that works for me.
>It was
>>>>>> tested with a MariaDB in a docker instance, that I started with
>>>>>>
>>>>>>  $ docker run --rm -p 3306:3306 -e
>MYSQL_ROOT_PASSWORD=my-secret-pw -ti
>>>>>> mariadb
>>>>>>
>>>>>> I then added  a database named db to it with a mysql client
>>>>>>
>>>>>>  $ docker exec  mydb /bin/bash -c 'echo "create database db;" |
>mysql
>>>>>> --password=my-secret-pw'
>>>>>>
>>>>>> In the test plan I have one thread group with one thread. It
>contains a
>>>>>> jdbc config which has set autocommit to false and a name of db. I
>used a
>>>>>> init sql statement to create a table:
>>>>>>
>>>>>> begin; create table if not exists person (id int, name text);
>commit;
>>>>>>
>>>>>> and filled in the database connection parameters
>>>>>>
>>>>>> url: jdbc:mysql://localhost:3306/db
>>>>>> driver class: com.mysql.jdbc.Driver
>>>>>> username: root
>>>>>> password: my-secret-pw
>>>>>>
>>>>>> (Oh, and don't forget to add the driver jar somewhere JMeter can
>find it
>>>>>> ;))
>>>>>>
>>>>>> Now, for the logic I added loop controller named loop and placed
>an jdbc
>>>>>> sampler into it.
>>>>>>
>>>>>> That sampler was named "insert data" and had set the auto commit
>field
>>>>>> set to false. Its type was prepared update statement and the
>query was
>>>>>> "insert into person values (?, ?)". Parameter values and types
>were
>>>>>>
>${__jm__loop__idx},${__RandomString(10,abcdefghijklmnopqrstuvwxyz,)}
>and
>>>>>> INTEGER,VARCHAR.
>>>>>>
>>>>>> After the loop I verified that the transaction had all the data
>with a
>>>>>> jdbc sampler named "view data", that had the type set to select
>>>>>> statement and the query "select * from person".
>>>>>>
>>>>>> Now to rollback (or commit), I used another jdbc sampler called
>"roll
>>>>>> back" with a query type of "Rollback".
>>>>>>
>>>>>> To verify that rollback worked. I added a last jdbc sampler named
>"view
>>>>>> data (again)" with the same type and statement as "view data".
>>>>>>
>>>>>> As I wanted to see all those requests and their responses, I
>added a
>>>>>> tree results view.
>>>>>>
>>>>>> So, reading your message correctly, I think you want to try
>changing the
>>>>>> query type of your statements back to update/select and adding a
>commit
>>>>>> typed query every once in a while.
>>>>>>
>>>>>> Felix
>>>>>>
>>>>>>> Thanks!
>>>>>>> Brian Flowers
>>>>>>> [hidden email]
>>>>>>>
>>>>>>>
>>>>>>>
>---------------------------------------------------------------------
>>>>>>> To unsubscribe, e-mail: [hidden email]
>>>>>>> For additional commands, e-mail: [hidden email]
>>>>>>>
>>>>>>
>---------------------------------------------------------------------
>>>>>> To unsubscribe, e-mail: [hidden email]
>>>>>> For additional commands, e-mail: [hidden email]
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: [hidden email]
>For additional commands, e-mail: [hidden email]

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: JDBC with autocommit=false

Brian Flowers
Yes...I am opening the connection with autocommit false, then in the
thread group I am doing:

1) JDBC request with no query, of type 'AutoCommit(false)'

2) JDBC request of type 'select' taking the count(*) of the table

3) JDBC request to of type 'prepared update statement' with my insert
query (I did move the variables to the parameters value and set their
types in order to try prepared update instead of regular
update...neither type of update seems to change anything though)

4) JDBC request of type 'Commit' with no query given

5) JDBC request of type 'select' taking the count(*) of the table again

In the results tree I see the count, I see the insert running without
exceptions with the correct parameters and types logged, I see the
commit say it is successfully triggered, and then I see the same count
returned again, and the same count and same data when querying from the
database directly.

Regards,
Brian Flowers
[hidden email]


On 9/10/20 2:22 PM, Felix Schumacher wrote:

>
> Am 10. September 2020 20:06:08 MESZ schrieb Brian Flowers <[hidden email]>:
>> Sorry for the delayed response, had some issues with the DB and had to
>> wait for the admin to get back from vacation.
>>
>> I have tried configuring as shown in the jmx plan you provided, but the
>> records still aren't being committed...results tree shows the insert
>> query triggering successfully, but the table count doesn't change even
>> when queried from within the same jmeter test...
> Did you change the rollback type to commit?
>
> Felix
>
>> On 9/5/20 4:44 AM, Felix Schumacher wrote:
>>> Am 04.09.20 um 08:50 schrieb Philippe Mouawad:
>>>> On Fri, Sep 4, 2020 at 8:43 AM Felix Schumacher <
>>>> [hidden email]> wrote:
>>>>
>>>>> Am 03.09.20 um 22:00 schrieb Philippe Mouawad:
>>>>>> Hello Felix,
>>>>>>
>>>>>> Thanks for answer, just 2 notes:
>>>>>>
>>>>>>    - I guess rollback should be named commit (just a matter of
>> naming)
>>>>>>    - More important, do we need to put a "commit"  in Query for
>> Query
>>>>>>    Type: Commit, reading the code I am not sure text is used  ?:
>>>>>>       -
>>>>>>
>> https://github.com/apache/jmeter/blob/master/src/protocol/jdbc/src/main/java/org/apache/jmeter/protocol/jdbc/AbstractJDBCTestElement.java#L224
>>>>>
>>>>> I am not sure, what you mean by this. In my example, I used
>> rollback
>>>>> instead of commit, as I think it shows the way the samplers work
>> more
>>>>> nicely.
>>>>>
>>>> Just that the name of the element is rollback and Query Type is
>> commit.
>>>>> The OP wanted to have commit, so my comment (... (or commit) ...)
>> was a
>>>>> placeholder to show, where the type has to be changed (and the name
>> of
>>>>> the sampler should be changed, too).
>>>>>
>>>> ok
>>>>
>>>>> I think the documentation could be made clearer, that the special
>> types
>>>>> commit, rollback, autocommit(true) and autocommit(false) are really
>> that
>>>>> (special) and will ignore the given content of the sql statements.
>>>> I agree
>>> Done
>>>
>>> Felix
>>>
>>>
>>>>> And
>>>>> while we are at it, the sql field could be disabled when the
>> special
>>>>> types are selected.
>>>>>
>>>> As it's a Generic TestBeanGU I am afraid it might not be that easy
>> to do
>>>>> For the OP the most important take away is probably, that
>>>>> autocommit(false) switches the current connection into transaction
>> mode,
>>>>> that has to be either committed or rollbacked and that the type to
>> use
>>>>> in his scenario would have been "commit" AND that the sql statement
>> in
>>>>> the special typed samplers are ignored.
>>>>>
>>>>> The "begin; create ...; commit" as init sql -- in my example -- was
>>>>> needed, as the connections are initialized in transaction mode
>>>>> (autocommit(false)) and I really wanted to create the table.
>>>>>
>>>> Yes very clear, I didn't say anything about that
>>>> Thanks
>>>>
>>>>> Felix
>>>>>
>>>>>
>>>>>> Regards
>>>>>>
>>>>>> On Thu, Sep 3, 2020 at 9:33 PM Felix Schumacher <
>>>>>> [hidden email]> wrote:
>>>>>>
>>>>>>> Am 03.09.20 um 18:16 schrieb Brian Flowers:
>>>>>>>> Hi everyone,
>>>>>>>>
>>>>>>>> I'm having some issues using JMeter to insert some records in a
>> MariaDB
>>>>>>>> database with autocommit disabled (the idea being that we want
>> to
>>>>> commit
>>>>>>>> every ~1000 records, not after each one).
>>>>>>>>
>>>>>>>> Did some searches and couldn't find any documentation or
>> tutorials
>>>>>>>> explaining this...I got desperate enough to ask on StackOverflow
>> :) but
>>>>>>>> the only response so far seems to indicate that I'm configuring
>> it
>>>>>>>> correctly:
>>>>>>>>
>> https://stackoverflow.com/questions/63713516/jmeter-jdbc-manual-commit
>>>>>>>> I started with JMeter 3.2 connecting via the mysql connector
>> version
>>>>>>>> 5.1.27 as that's what we already had...I realize those are
>> pretty old,
>>>>>>>> so I did try upgrading, but got the same results. Tried on
>> JMeter 5.3
>>>>>>>> with mysql connector 8.0.21, and also with the dedicated mariadb
>>>>>>>> connector version 2.11.3 (all connectors from the Maven
>> repository).
>>>>>>>> With auto commit true, any combination of those versions works
>> fine.
>>>>>>>> With auto commit false, I can't get my data committed on any of
>> them.
>>>>>>>> What I have set up right now is a thread group with one thread,
>> than
>>>>>>>> contains a JDBC request with a single INSERT statement, using a
>> couple
>>>>>>>> variables that it takes from a csv data set and a counter, on a
>>>>> constant
>>>>>>>> throughput timer, and I'm using the loop count in the thread
>> group to
>>>>>>>> control the number of records inserted. When I have auto commit
>> set to
>>>>>>>> true in the JDBC configuration, the records all get inserted
>> just fine.
>>>>>>>> But when I turn auto commit off, I can't get those statements
>>>>> committed.
>>>>>>>> I set the JDBC request query type to "AutoCommit(false)" instead
>> of
>>>>>>>> "Update Statement", then I added a second JDBC request on the
>> same
>>>>>>>> configuration with request type of "Commit". In the results tree
>> I can
>>>>>>>> see a commit statement following each insert statement with no
>> errors,
>>>>>>>> but the records don't actually get committed in the DB. I tried
>> adding
>>>>>>>> the commit inside the original JDBC request (just to see if
>> that'd
>>>>> work)
>>>>>>>> but that gave a SQL error; I tried adding a commit post
>> processor
>>>>> within
>>>>>>>> the main JDBC request, but no luck there. I tried adding a
>>>>> pre-processor
>>>>>>>> to open a transaction, assuming that it wasn't including the
>> commit and
>>>>>>>> the insert on the same transaction, but no change with that. I
>> tried
>>>>>>>> configuration transaction isolation as DEFAULT or as
>>>>>>>> TRANSACTION_SERIALIZABLE but that had no apparent effect either.
>>>>>>>>
>>>>>>>> So...how do I manually commit an insert statement on a mariaDB
>>>>> database?
>>>>>>>> Or what else can I check to try to diagnose exactly what is
>> going on
>>>>>>>> here? Are there any resources or documentation about exactly how
>> to use
>>>>>>>> the autocommit setting?
>>>>>>> I have (tried) to attach a minimal test plan, that works for me.
>> It was
>>>>>>> tested with a MariaDB in a docker instance, that I started with
>>>>>>>
>>>>>>>  $ docker run --rm -p 3306:3306 -e
>> MYSQL_ROOT_PASSWORD=my-secret-pw -ti
>>>>>>> mariadb
>>>>>>>
>>>>>>> I then added  a database named db to it with a mysql client
>>>>>>>
>>>>>>>  $ docker exec  mydb /bin/bash -c 'echo "create database db;" |
>> mysql
>>>>>>> --password=my-secret-pw'
>>>>>>>
>>>>>>> In the test plan I have one thread group with one thread. It
>> contains a
>>>>>>> jdbc config which has set autocommit to false and a name of db. I
>> used a
>>>>>>> init sql statement to create a table:
>>>>>>>
>>>>>>> begin; create table if not exists person (id int, name text);
>> commit;
>>>>>>> and filled in the database connection parameters
>>>>>>>
>>>>>>> url: jdbc:mysql://localhost:3306/db
>>>>>>> driver class: com.mysql.jdbc.Driver
>>>>>>> username: root
>>>>>>> password: my-secret-pw
>>>>>>>
>>>>>>> (Oh, and don't forget to add the driver jar somewhere JMeter can
>> find it
>>>>>>> ;))
>>>>>>>
>>>>>>> Now, for the logic I added loop controller named loop and placed
>> an jdbc
>>>>>>> sampler into it.
>>>>>>>
>>>>>>> That sampler was named "insert data" and had set the auto commit
>> field
>>>>>>> set to false. Its type was prepared update statement and the
>> query was
>>>>>>> "insert into person values (?, ?)". Parameter values and types
>> were
>> ${__jm__loop__idx},${__RandomString(10,abcdefghijklmnopqrstuvwxyz,)}
>> and
>>>>>>> INTEGER,VARCHAR.
>>>>>>>
>>>>>>> After the loop I verified that the transaction had all the data
>> with a
>>>>>>> jdbc sampler named "view data", that had the type set to select
>>>>>>> statement and the query "select * from person".
>>>>>>>
>>>>>>> Now to rollback (or commit), I used another jdbc sampler called
>> "roll
>>>>>>> back" with a query type of "Rollback".
>>>>>>>
>>>>>>> To verify that rollback worked. I added a last jdbc sampler named
>> "view
>>>>>>> data (again)" with the same type and statement as "view data".
>>>>>>>
>>>>>>> As I wanted to see all those requests and their responses, I
>> added a
>>>>>>> tree results view.
>>>>>>>
>>>>>>> So, reading your message correctly, I think you want to try
>> changing the
>>>>>>> query type of your statements back to update/select and adding a
>> commit
>>>>>>> typed query every once in a while.
>>>>>>>
>>>>>>> Felix
>>>>>>>
>>>>>>>> Thanks!
>>>>>>>> Brian Flowers
>>>>>>>> [hidden email]
>>>>>>>>
>>>>>>>>
>>>>>>>>
>> ---------------------------------------------------------------------
>>>>>>>> To unsubscribe, e-mail: [hidden email]
>>>>>>>> For additional commands, e-mail: [hidden email]
>>>>>>>>
>> ---------------------------------------------------------------------
>>>>>>> To unsubscribe, e-mail: [hidden email]
>>>>>>> For additional commands, e-mail: [hidden email]
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [hidden email]
>>> For additional commands, e-mail: [hidden email]
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: JDBC with autocommit=false

Felix Schumacher

Am 10.09.20 um 20:42 schrieb Brian Flowers:

> Yes...I am opening the connection with autocommit false, then in the
> thread group I am doing:
>
> 1) JDBC request with no query, of type 'AutoCommit(false)'
>
> 2) JDBC request of type 'select' taking the count(*) of the table
>
> 3) JDBC request to of type 'prepared update statement' with my insert
> query (I did move the variables to the parameters value and set their
> types in order to try prepared update instead of regular
> update...neither type of update seems to change anything though)
>
> 4) JDBC request of type 'Commit' with no query given
>
> 5) JDBC request of type 'select' taking the count(*) of the table again
>
> In the results tree I see the count, I see the insert running without
> exceptions with the correct parameters and types logged, I see the
> commit say it is successfully triggered, and then I see the same count
> returned again, and the same count and same data when querying from the
> database directly.

There are a few questions left (there always are)

Have you tried a simple test case first?

Which driver did you use? (In your first post you mentioned quite a few.
In my experiment I used mysql-connector-java-8.0.21.jar (which you
mentioned, too)).

Are there errors logged on the database side?

Did you try to commit "earlier"? (if you originally committed every 1000
requests, try committing after 100)

Are there any log messages in jmeter.log?

You might want to try to use an intercepting driver like p6spy to log
the statements JMeter issues on the connection, or take a network dump
and have a look at the packets with wireshark or something similar.

If you can post a minimal test plan, that you think should work and
doesn't work for you, that would probably be helpful, too.

Felix

>
> Regards,
> Brian Flowers
> [hidden email]
>
>
> On 9/10/20 2:22 PM, Felix Schumacher wrote:
>> Am 10. September 2020 20:06:08 MESZ schrieb Brian Flowers <[hidden email]>:
>>> Sorry for the delayed response, had some issues with the DB and had to
>>> wait for the admin to get back from vacation.
>>>
>>> I have tried configuring as shown in the jmx plan you provided, but the
>>> records still aren't being committed...results tree shows the insert
>>> query triggering successfully, but the table count doesn't change even
>>> when queried from within the same jmeter test...
>> Did you change the rollback type to commit?
>>
>> Felix
>>
>>> On 9/5/20 4:44 AM, Felix Schumacher wrote:
>>>> Am 04.09.20 um 08:50 schrieb Philippe Mouawad:
>>>>> On Fri, Sep 4, 2020 at 8:43 AM Felix Schumacher <
>>>>> [hidden email]> wrote:
>>>>>
>>>>>> Am 03.09.20 um 22:00 schrieb Philippe Mouawad:
>>>>>>> Hello Felix,
>>>>>>>
>>>>>>> Thanks for answer, just 2 notes:
>>>>>>>
>>>>>>>    - I guess rollback should be named commit (just a matter of
>>> naming)
>>>>>>>    - More important, do we need to put a "commit"  in Query for
>>> Query
>>>>>>>    Type: Commit, reading the code I am not sure text is used  ?:
>>>>>>>       -
>>>>>>>
>>> https://github.com/apache/jmeter/blob/master/src/protocol/jdbc/src/main/java/org/apache/jmeter/protocol/jdbc/AbstractJDBCTestElement.java#L224
>>>>>> I am not sure, what you mean by this. In my example, I used
>>> rollback
>>>>>> instead of commit, as I think it shows the way the samplers work
>>> more
>>>>>> nicely.
>>>>>>
>>>>> Just that the name of the element is rollback and Query Type is
>>> commit.
>>>>>> The OP wanted to have commit, so my comment (... (or commit) ...)
>>> was a
>>>>>> placeholder to show, where the type has to be changed (and the name
>>> of
>>>>>> the sampler should be changed, too).
>>>>>>
>>>>> ok
>>>>>
>>>>>> I think the documentation could be made clearer, that the special
>>> types
>>>>>> commit, rollback, autocommit(true) and autocommit(false) are really
>>> that
>>>>>> (special) and will ignore the given content of the sql statements.
>>>>> I agree
>>>> Done
>>>>
>>>> Felix
>>>>
>>>>
>>>>>> And
>>>>>> while we are at it, the sql field could be disabled when the
>>> special
>>>>>> types are selected.
>>>>>>
>>>>> As it's a Generic TestBeanGU I am afraid it might not be that easy
>>> to do
>>>>>> For the OP the most important take away is probably, that
>>>>>> autocommit(false) switches the current connection into transaction
>>> mode,
>>>>>> that has to be either committed or rollbacked and that the type to
>>> use
>>>>>> in his scenario would have been "commit" AND that the sql statement
>>> in
>>>>>> the special typed samplers are ignored.
>>>>>>
>>>>>> The "begin; create ...; commit" as init sql -- in my example -- was
>>>>>> needed, as the connections are initialized in transaction mode
>>>>>> (autocommit(false)) and I really wanted to create the table.
>>>>>>
>>>>> Yes very clear, I didn't say anything about that
>>>>> Thanks
>>>>>
>>>>>> Felix
>>>>>>
>>>>>>
>>>>>>> Regards
>>>>>>>
>>>>>>> On Thu, Sep 3, 2020 at 9:33 PM Felix Schumacher <
>>>>>>> [hidden email]> wrote:
>>>>>>>
>>>>>>>> Am 03.09.20 um 18:16 schrieb Brian Flowers:
>>>>>>>>> Hi everyone,
>>>>>>>>>
>>>>>>>>> I'm having some issues using JMeter to insert some records in a
>>> MariaDB
>>>>>>>>> database with autocommit disabled (the idea being that we want
>>> to
>>>>>> commit
>>>>>>>>> every ~1000 records, not after each one).
>>>>>>>>>
>>>>>>>>> Did some searches and couldn't find any documentation or
>>> tutorials
>>>>>>>>> explaining this...I got desperate enough to ask on StackOverflow
>>> :) but
>>>>>>>>> the only response so far seems to indicate that I'm configuring
>>> it
>>>>>>>>> correctly:
>>>>>>>>>
>>> https://stackoverflow.com/questions/63713516/jmeter-jdbc-manual-commit
>>>>>>>>> I started with JMeter 3.2 connecting via the mysql connector
>>> version
>>>>>>>>> 5.1.27 as that's what we already had...I realize those are
>>> pretty old,
>>>>>>>>> so I did try upgrading, but got the same results. Tried on
>>> JMeter 5.3
>>>>>>>>> with mysql connector 8.0.21, and also with the dedicated mariadb
>>>>>>>>> connector version 2.11.3 (all connectors from the Maven
>>> repository).
>>>>>>>>> With auto commit true, any combination of those versions works
>>> fine.
>>>>>>>>> With auto commit false, I can't get my data committed on any of
>>> them.
>>>>>>>>> What I have set up right now is a thread group with one thread,
>>> than
>>>>>>>>> contains a JDBC request with a single INSERT statement, using a
>>> couple
>>>>>>>>> variables that it takes from a csv data set and a counter, on a
>>>>>> constant
>>>>>>>>> throughput timer, and I'm using the loop count in the thread
>>> group to
>>>>>>>>> control the number of records inserted. When I have auto commit
>>> set to
>>>>>>>>> true in the JDBC configuration, the records all get inserted
>>> just fine.
>>>>>>>>> But when I turn auto commit off, I can't get those statements
>>>>>> committed.
>>>>>>>>> I set the JDBC request query type to "AutoCommit(false)" instead
>>> of
>>>>>>>>> "Update Statement", then I added a second JDBC request on the
>>> same
>>>>>>>>> configuration with request type of "Commit". In the results tree
>>> I can
>>>>>>>>> see a commit statement following each insert statement with no
>>> errors,
>>>>>>>>> but the records don't actually get committed in the DB. I tried
>>> adding
>>>>>>>>> the commit inside the original JDBC request (just to see if
>>> that'd
>>>>>> work)
>>>>>>>>> but that gave a SQL error; I tried adding a commit post
>>> processor
>>>>>> within
>>>>>>>>> the main JDBC request, but no luck there. I tried adding a
>>>>>> pre-processor
>>>>>>>>> to open a transaction, assuming that it wasn't including the
>>> commit and
>>>>>>>>> the insert on the same transaction, but no change with that. I
>>> tried
>>>>>>>>> configuration transaction isolation as DEFAULT or as
>>>>>>>>> TRANSACTION_SERIALIZABLE but that had no apparent effect either.
>>>>>>>>>
>>>>>>>>> So...how do I manually commit an insert statement on a mariaDB
>>>>>> database?
>>>>>>>>> Or what else can I check to try to diagnose exactly what is
>>> going on
>>>>>>>>> here? Are there any resources or documentation about exactly how
>>> to use
>>>>>>>>> the autocommit setting?
>>>>>>>> I have (tried) to attach a minimal test plan, that works for me.
>>> It was
>>>>>>>> tested with a MariaDB in a docker instance, that I started with
>>>>>>>>
>>>>>>>>  $ docker run --rm -p 3306:3306 -e
>>> MYSQL_ROOT_PASSWORD=my-secret-pw -ti
>>>>>>>> mariadb
>>>>>>>>
>>>>>>>> I then added  a database named db to it with a mysql client
>>>>>>>>
>>>>>>>>  $ docker exec  mydb /bin/bash -c 'echo "create database db;" |
>>> mysql
>>>>>>>> --password=my-secret-pw'
>>>>>>>>
>>>>>>>> In the test plan I have one thread group with one thread. It
>>> contains a
>>>>>>>> jdbc config which has set autocommit to false and a name of db. I
>>> used a
>>>>>>>> init sql statement to create a table:
>>>>>>>>
>>>>>>>> begin; create table if not exists person (id int, name text);
>>> commit;
>>>>>>>> and filled in the database connection parameters
>>>>>>>>
>>>>>>>> url: jdbc:mysql://localhost:3306/db
>>>>>>>> driver class: com.mysql.jdbc.Driver
>>>>>>>> username: root
>>>>>>>> password: my-secret-pw
>>>>>>>>
>>>>>>>> (Oh, and don't forget to add the driver jar somewhere JMeter can
>>> find it
>>>>>>>> ;))
>>>>>>>>
>>>>>>>> Now, for the logic I added loop controller named loop and placed
>>> an jdbc
>>>>>>>> sampler into it.
>>>>>>>>
>>>>>>>> That sampler was named "insert data" and had set the auto commit
>>> field
>>>>>>>> set to false. Its type was prepared update statement and the
>>> query was
>>>>>>>> "insert into person values (?, ?)". Parameter values and types
>>> were
>>> ${__jm__loop__idx},${__RandomString(10,abcdefghijklmnopqrstuvwxyz,)}
>>> and
>>>>>>>> INTEGER,VARCHAR.
>>>>>>>>
>>>>>>>> After the loop I verified that the transaction had all the data
>>> with a
>>>>>>>> jdbc sampler named "view data", that had the type set to select
>>>>>>>> statement and the query "select * from person".
>>>>>>>>
>>>>>>>> Now to rollback (or commit), I used another jdbc sampler called
>>> "roll
>>>>>>>> back" with a query type of "Rollback".
>>>>>>>>
>>>>>>>> To verify that rollback worked. I added a last jdbc sampler named
>>> "view
>>>>>>>> data (again)" with the same type and statement as "view data".
>>>>>>>>
>>>>>>>> As I wanted to see all those requests and their responses, I
>>> added a
>>>>>>>> tree results view.
>>>>>>>>
>>>>>>>> So, reading your message correctly, I think you want to try
>>> changing the
>>>>>>>> query type of your statements back to update/select and adding a
>>> commit
>>>>>>>> typed query every once in a while.
>>>>>>>>
>>>>>>>> Felix
>>>>>>>>
>>>>>>>>> Thanks!
>>>>>>>>> Brian Flowers
>>>>>>>>> [hidden email]
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>> ---------------------------------------------------------------------
>>>>>>>>> To unsubscribe, e-mail: [hidden email]
>>>>>>>>> For additional commands, e-mail: [hidden email]
>>>>>>>>>
>>> ---------------------------------------------------------------------
>>>>>>>> To unsubscribe, e-mail: [hidden email]
>>>>>>>> For additional commands, e-mail: [hidden email]
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: [hidden email]
>>>> For additional commands, e-mail: [hidden email]
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [hidden email]
>>> For additional commands, e-mail: [hidden email]
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: JDBC with autocommit=false

Brian Flowers
Well...it's another case where I'm not exactly sure what changed but
I've gotten it working now...

Since you asked about which specific version I was running I decided to
try with all the various jmeter/connector combinations again. Still no
luck with either mysql connector on either jmeter version...but this
time the mariadb 2.6.2 connector worked. Pretty sure I had tested that
already and it wasn't working then, but I can't seem to reproduce that
now so maybe I missed something obvious last time. Or maybe the DBAs
fixed something on their end, I don't have much access to that side at
the moment.

Either way...thank you all very much for taking the time to walk this
newbie through all of this :)


On 9/10/20 3:34 PM, Felix Schumacher wrote:

> Am 10.09.20 um 20:42 schrieb Brian Flowers:
>> Yes...I am opening the connection with autocommit false, then in the
>> thread group I am doing:
>>
>> 1) JDBC request with no query, of type 'AutoCommit(false)'
>>
>> 2) JDBC request of type 'select' taking the count(*) of the table
>>
>> 3) JDBC request to of type 'prepared update statement' with my insert
>> query (I did move the variables to the parameters value and set their
>> types in order to try prepared update instead of regular
>> update...neither type of update seems to change anything though)
>>
>> 4) JDBC request of type 'Commit' with no query given
>>
>> 5) JDBC request of type 'select' taking the count(*) of the table again
>>
>> In the results tree I see the count, I see the insert running without
>> exceptions with the correct parameters and types logged, I see the
>> commit say it is successfully triggered, and then I see the same count
>> returned again, and the same count and same data when querying from the
>> database directly.
> There are a few questions left (there always are)
>
> Have you tried a simple test case first?
>
> Which driver did you use? (In your first post you mentioned quite a few.
> In my experiment I used mysql-connector-java-8.0.21.jar (which you
> mentioned, too)).
>
> Are there errors logged on the database side?
>
> Did you try to commit "earlier"? (if you originally committed every 1000
> requests, try committing after 100)
>
> Are there any log messages in jmeter.log?
>
> You might want to try to use an intercepting driver like p6spy to log
> the statements JMeter issues on the connection, or take a network dump
> and have a look at the packets with wireshark or something similar.
>
> If you can post a minimal test plan, that you think should work and
> doesn't work for you, that would probably be helpful, too.
>
> Felix
>
>> Regards,
>> Brian Flowers
>> [hidden email]
>>
>>
>> On 9/10/20 2:22 PM, Felix Schumacher wrote:
>>> Am 10. September 2020 20:06:08 MESZ schrieb Brian Flowers <[hidden email]>:
>>>> Sorry for the delayed response, had some issues with the DB and had to
>>>> wait for the admin to get back from vacation.
>>>>
>>>> I have tried configuring as shown in the jmx plan you provided, but the
>>>> records still aren't being committed...results tree shows the insert
>>>> query triggering successfully, but the table count doesn't change even
>>>> when queried from within the same jmeter test...
>>> Did you change the rollback type to commit?
>>>
>>> Felix
>>>
>>>> On 9/5/20 4:44 AM, Felix Schumacher wrote:
>>>>> Am 04.09.20 um 08:50 schrieb Philippe Mouawad:
>>>>>> On Fri, Sep 4, 2020 at 8:43 AM Felix Schumacher <
>>>>>> [hidden email]> wrote:
>>>>>>
>>>>>>> Am 03.09.20 um 22:00 schrieb Philippe Mouawad:
>>>>>>>> Hello Felix,
>>>>>>>>
>>>>>>>> Thanks for answer, just 2 notes:
>>>>>>>>
>>>>>>>>    - I guess rollback should be named commit (just a matter of
>>>> naming)
>>>>>>>>    - More important, do we need to put a "commit"  in Query for
>>>> Query
>>>>>>>>    Type: Commit, reading the code I am not sure text is used  ?:
>>>>>>>>       -
>>>>>>>>
>>>> https://github.com/apache/jmeter/blob/master/src/protocol/jdbc/src/main/java/org/apache/jmeter/protocol/jdbc/AbstractJDBCTestElement.java#L224
>>>>>>> I am not sure, what you mean by this. In my example, I used
>>>> rollback
>>>>>>> instead of commit, as I think it shows the way the samplers work
>>>> more
>>>>>>> nicely.
>>>>>>>
>>>>>> Just that the name of the element is rollback and Query Type is
>>>> commit.
>>>>>>> The OP wanted to have commit, so my comment (... (or commit) ...)
>>>> was a
>>>>>>> placeholder to show, where the type has to be changed (and the name
>>>> of
>>>>>>> the sampler should be changed, too).
>>>>>>>
>>>>>> ok
>>>>>>
>>>>>>> I think the documentation could be made clearer, that the special
>>>> types
>>>>>>> commit, rollback, autocommit(true) and autocommit(false) are really
>>>> that
>>>>>>> (special) and will ignore the given content of the sql statements.
>>>>>> I agree
>>>>> Done
>>>>>
>>>>> Felix
>>>>>
>>>>>
>>>>>>> And
>>>>>>> while we are at it, the sql field could be disabled when the
>>>> special
>>>>>>> types are selected.
>>>>>>>
>>>>>> As it's a Generic TestBeanGU I am afraid it might not be that easy
>>>> to do
>>>>>>> For the OP the most important take away is probably, that
>>>>>>> autocommit(false) switches the current connection into transaction
>>>> mode,
>>>>>>> that has to be either committed or rollbacked and that the type to
>>>> use
>>>>>>> in his scenario would have been "commit" AND that the sql statement
>>>> in
>>>>>>> the special typed samplers are ignored.
>>>>>>>
>>>>>>> The "begin; create ...; commit" as init sql -- in my example -- was
>>>>>>> needed, as the connections are initialized in transaction mode
>>>>>>> (autocommit(false)) and I really wanted to create the table.
>>>>>>>
>>>>>> Yes very clear, I didn't say anything about that
>>>>>> Thanks
>>>>>>
>>>>>>> Felix
>>>>>>>
>>>>>>>
>>>>>>>> Regards
>>>>>>>>
>>>>>>>> On Thu, Sep 3, 2020 at 9:33 PM Felix Schumacher <
>>>>>>>> [hidden email]> wrote:
>>>>>>>>
>>>>>>>>> Am 03.09.20 um 18:16 schrieb Brian Flowers:
>>>>>>>>>> Hi everyone,
>>>>>>>>>>
>>>>>>>>>> I'm having some issues using JMeter to insert some records in a
>>>> MariaDB
>>>>>>>>>> database with autocommit disabled (the idea being that we want
>>>> to
>>>>>>> commit
>>>>>>>>>> every ~1000 records, not after each one).
>>>>>>>>>>
>>>>>>>>>> Did some searches and couldn't find any documentation or
>>>> tutorials
>>>>>>>>>> explaining this...I got desperate enough to ask on StackOverflow
>>>> :) but
>>>>>>>>>> the only response so far seems to indicate that I'm configuring
>>>> it
>>>>>>>>>> correctly:
>>>>>>>>>>
>>>> https://stackoverflow.com/questions/63713516/jmeter-jdbc-manual-commit
>>>>>>>>>> I started with JMeter 3.2 connecting via the mysql connector
>>>> version
>>>>>>>>>> 5.1.27 as that's what we already had...I realize those are
>>>> pretty old,
>>>>>>>>>> so I did try upgrading, but got the same results. Tried on
>>>> JMeter 5.3
>>>>>>>>>> with mysql connector 8.0.21, and also with the dedicated mariadb
>>>>>>>>>> connector version 2.11.3 (all connectors from the Maven
>>>> repository).
>>>>>>>>>> With auto commit true, any combination of those versions works
>>>> fine.
>>>>>>>>>> With auto commit false, I can't get my data committed on any of
>>>> them.
>>>>>>>>>> What I have set up right now is a thread group with one thread,
>>>> than
>>>>>>>>>> contains a JDBC request with a single INSERT statement, using a
>>>> couple
>>>>>>>>>> variables that it takes from a csv data set and a counter, on a
>>>>>>> constant
>>>>>>>>>> throughput timer, and I'm using the loop count in the thread
>>>> group to
>>>>>>>>>> control the number of records inserted. When I have auto commit
>>>> set to
>>>>>>>>>> true in the JDBC configuration, the records all get inserted
>>>> just fine.
>>>>>>>>>> But when I turn auto commit off, I can't get those statements
>>>>>>> committed.
>>>>>>>>>> I set the JDBC request query type to "AutoCommit(false)" instead
>>>> of
>>>>>>>>>> "Update Statement", then I added a second JDBC request on the
>>>> same
>>>>>>>>>> configuration with request type of "Commit". In the results tree
>>>> I can
>>>>>>>>>> see a commit statement following each insert statement with no
>>>> errors,
>>>>>>>>>> but the records don't actually get committed in the DB. I tried
>>>> adding
>>>>>>>>>> the commit inside the original JDBC request (just to see if
>>>> that'd
>>>>>>> work)
>>>>>>>>>> but that gave a SQL error; I tried adding a commit post
>>>> processor
>>>>>>> within
>>>>>>>>>> the main JDBC request, but no luck there. I tried adding a
>>>>>>> pre-processor
>>>>>>>>>> to open a transaction, assuming that it wasn't including the
>>>> commit and
>>>>>>>>>> the insert on the same transaction, but no change with that. I
>>>> tried
>>>>>>>>>> configuration transaction isolation as DEFAULT or as
>>>>>>>>>> TRANSACTION_SERIALIZABLE but that had no apparent effect either.
>>>>>>>>>>
>>>>>>>>>> So...how do I manually commit an insert statement on a mariaDB
>>>>>>> database?
>>>>>>>>>> Or what else can I check to try to diagnose exactly what is
>>>> going on
>>>>>>>>>> here? Are there any resources or documentation about exactly how
>>>> to use
>>>>>>>>>> the autocommit setting?
>>>>>>>>> I have (tried) to attach a minimal test plan, that works for me.
>>>> It was
>>>>>>>>> tested with a MariaDB in a docker instance, that I started with
>>>>>>>>>
>>>>>>>>>  $ docker run --rm -p 3306:3306 -e
>>>> MYSQL_ROOT_PASSWORD=my-secret-pw -ti
>>>>>>>>> mariadb
>>>>>>>>>
>>>>>>>>> I then added  a database named db to it with a mysql client
>>>>>>>>>
>>>>>>>>>  $ docker exec  mydb /bin/bash -c 'echo "create database db;" |
>>>> mysql
>>>>>>>>> --password=my-secret-pw'
>>>>>>>>>
>>>>>>>>> In the test plan I have one thread group with one thread. It
>>>> contains a
>>>>>>>>> jdbc config which has set autocommit to false and a name of db. I
>>>> used a
>>>>>>>>> init sql statement to create a table:
>>>>>>>>>
>>>>>>>>> begin; create table if not exists person (id int, name text);
>>>> commit;
>>>>>>>>> and filled in the database connection parameters
>>>>>>>>>
>>>>>>>>> url: jdbc:mysql://localhost:3306/db
>>>>>>>>> driver class: com.mysql.jdbc.Driver
>>>>>>>>> username: root
>>>>>>>>> password: my-secret-pw
>>>>>>>>>
>>>>>>>>> (Oh, and don't forget to add the driver jar somewhere JMeter can
>>>> find it
>>>>>>>>> ;))
>>>>>>>>>
>>>>>>>>> Now, for the logic I added loop controller named loop and placed
>>>> an jdbc
>>>>>>>>> sampler into it.
>>>>>>>>>
>>>>>>>>> That sampler was named "insert data" and had set the auto commit
>>>> field
>>>>>>>>> set to false. Its type was prepared update statement and the
>>>> query was
>>>>>>>>> "insert into person values (?, ?)". Parameter values and types
>>>> were
>>>> ${__jm__loop__idx},${__RandomString(10,abcdefghijklmnopqrstuvwxyz,)}
>>>> and
>>>>>>>>> INTEGER,VARCHAR.
>>>>>>>>>
>>>>>>>>> After the loop I verified that the transaction had all the data
>>>> with a
>>>>>>>>> jdbc sampler named "view data", that had the type set to select
>>>>>>>>> statement and the query "select * from person".
>>>>>>>>>
>>>>>>>>> Now to rollback (or commit), I used another jdbc sampler called
>>>> "roll
>>>>>>>>> back" with a query type of "Rollback".
>>>>>>>>>
>>>>>>>>> To verify that rollback worked. I added a last jdbc sampler named
>>>> "view
>>>>>>>>> data (again)" with the same type and statement as "view data".
>>>>>>>>>
>>>>>>>>> As I wanted to see all those requests and their responses, I
>>>> added a
>>>>>>>>> tree results view.
>>>>>>>>>
>>>>>>>>> So, reading your message correctly, I think you want to try
>>>> changing the
>>>>>>>>> query type of your statements back to update/select and adding a
>>>> commit
>>>>>>>>> typed query every once in a while.
>>>>>>>>>
>>>>>>>>> Felix
>>>>>>>>>
>>>>>>>>>> Thanks!
>>>>>>>>>> Brian Flowers
>>>>>>>>>> [hidden email]
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>> ---------------------------------------------------------------------
>>>>>>>>>> To unsubscribe, e-mail: [hidden email]
>>>>>>>>>> For additional commands, e-mail: [hidden email]
>>>>>>>>>>
>>>> ---------------------------------------------------------------------
>>>>>>>>> To unsubscribe, e-mail: [hidden email]
>>>>>>>>> For additional commands, e-mail: [hidden email]
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: [hidden email]
>>>>> For additional commands, e-mail: [hidden email]
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: [hidden email]
>>>> For additional commands, e-mail: [hidden email]
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [hidden email]
>>> For additional commands, e-mail: [hidden email]
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]