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)" 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
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscr...@jmeter.apache.org
> For additional commands, e-mail: user-h...@jmeter.apache.org
--
Cordialement.
Philippe Mouawad.
