Am 10. September 2020 20:06:08 MESZ schrieb Brian Flowers
<jmeter-s...@bsflowers.net>:
>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 <
>>> felix.schumac...@internetallee.de> 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 <
>>>>> 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
>> ---------------------------------------------------------------------
>> 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
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscr...@jmeter.apache.org
For additional commands, e-mail: user-h...@jmeter.apache.org
