Well...it's another case where I'm not exactly sure what changed but I've gotten it working now...
Since you asked about which specific version I was running I decided to try with all the various jmeter/connector combinations again. Still no luck with either mysql connector on either jmeter version...but this time the mariadb 2.6.2 connector worked. Pretty sure I had tested that already and it wasn't working then, but I can't seem to reproduce that now so maybe I missed something obvious last time. Or maybe the DBAs fixed something on their end, I don't have much access to that side at the moment. Either way...thank you all very much for taking the time to walk this newbie through all of this :) On 9/10/20 3:34 PM, Felix Schumacher wrote: > Am 10.09.20 um 20:42 schrieb Brian Flowers: >> Yes...I am opening the connection with autocommit false, then in the >> thread group I am doing: >> >> 1) JDBC request with no query, of type 'AutoCommit(false)' >> >> 2) JDBC request of type 'select' taking the count(*) of the table >> >> 3) JDBC request to of type 'prepared update statement' with my insert >> query (I did move the variables to the parameters value and set their >> types in order to try prepared update instead of regular >> update...neither type of update seems to change anything though) >> >> 4) JDBC request of type 'Commit' with no query given >> >> 5) JDBC request of type 'select' taking the count(*) of the table again >> >> In the results tree I see the count, I see the insert running without >> exceptions with the correct parameters and types logged, I see the >> commit say it is successfully triggered, and then I see the same count >> returned again, and the same count and same data when querying from the >> database directly. > There are a few questions left (there always are) > > Have you tried a simple test case first? > > Which driver did you use? (In your first post you mentioned quite a few. > In my experiment I used mysql-connector-java-8.0.21.jar (which you > mentioned, too)). > > Are there errors logged on the database side? > > Did you try to commit "earlier"? (if you originally committed every 1000 > requests, try committing after 100) > > Are there any log messages in jmeter.log? > > You might want to try to use an intercepting driver like p6spy to log > the statements JMeter issues on the connection, or take a network dump > and have a look at the packets with wireshark or something similar. > > If you can post a minimal test plan, that you think should work and > doesn't work for you, that would probably be helpful, too. > > Felix > >> Regards, >> Brian Flowers >> [email protected] >> >> >> On 9/10/20 2:22 PM, Felix Schumacher wrote: >>> Am 10. September 2020 20:06:08 MESZ schrieb Brian Flowers >>> <[email protected]>: >>>> 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 < >>>>>> [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] >>>> --------------------------------------------------------------------- >>>> 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] >> > --------------------------------------------------------------------- > 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]
