Am 04.09.20 um 08:50 schrieb Philippe Mouawad: > On Fri, Sep 4, 2020 at 8:43 AM Felix Schumacher < > [email protected]> 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 < >>> [email protected]> 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 >>>>> [email protected] >>>>> >>>>> >>>>> --------------------------------------------------------------------- >>>>> To unsubscribe, e-mail: [email protected] >>>>> For additional commands, e-mail: [email protected] >>>>> >>>> --------------------------------------------------------------------- >>>> To unsubscribe, e-mail: [email protected] >>>> For additional commands, e-mail: [email protected] >>> > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
