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 > 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 > > > > > -- Cordialement Philippe M. Ubik-Ingenierie
