Re: JDBC with autocommit=false

[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


Regards

On Thu, Sep 3, 2020 at 9:33 PM Felix Schumacher <
felix.schumac...@internetallee.de> 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)" 

Re: JDBC with autocommit=false

[Felix Schumacher]

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
> jmeter-s...@bsflowers.net
>
>
> -
> To unsubscribe, e-mail: user-unsubscr...@jmeter.apache.org
> For additional commands, e-mail: user-h...@jmeter.apache.org
>


  

  
  false
  true
  false
  

  
  


  
  

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 
> 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
>> jmeter-s...@bsflowers.net
>>
>>
>> -
>> To unsubscribe, e-mail: user-unsubscr...@jmeter.apache.org
>> For additional commands, e-mail: user-h...@jmeter.apache.org
>>
>> --
>> 
>>
>> Regards
>> Ubik Load Pack  Team
>> Follow us on Twitter 
>>
>>
>> Cordialement
>> L'équipe Ubik Load Pack 
>> Suivez-nous sur Twitter 
>>
>>
>>
>>


-
To unsubscribe, e-mail: user-unsubscr...@jmeter.apache.org
For additional commands, e-mail: user-h...@jmeter.apache.org

Re: JDBC with autocommit=false

[UBIK LOAD PACK Support]

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 
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
> jmeter-s...@bsflowers.net
>
>
> -
> To unsubscribe, e-mail: user-unsubscr...@jmeter.apache.org
> For additional commands, e-mail: user-h...@jmeter.apache.org
>
> --
> 
>
> Regards
> Ubik Load Pack  Team
> Follow us on Twitter 
>
>
> Cordialement
> L'équipe Ubik Load Pack 
> Suivez-nous sur Twitter 
>
>
>
>

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
jmeter-s...@bsflowers.net


-
To unsubscribe, e-mail: user-unsubscr...@jmeter.apache.org
For additional commands, e-mail: user-h...@jmeter.apache.org