Integrating JMeter and MySQL With Your Database

JMeter is a widely-used tool for performing API testing. It is very easy to automate APIs to send requests and receive responses using JMeter. But many of us have faced a situation when we have to store some values in a database and/or fetch records from a database and then assert the database responses. To address this problem, I have written this blog where we will learn how to integrate a DB with JMeter, fetch the records from the DB, and then assert the records fetched from the DB.

Pre-Requisites:

To start the integration of the database with JMeter, we need to have following prerequisites:

• JMeter
• MySQL DB installed on your machine
• Permission to connect to DB and run CRUD operations on DB
• Java 8

To start with, we need to have a MySQL connector on our machine to set up a proper communication channel between JMeter and MySQL. Follow these steps to do this correctly.

MySql connector download:

1. Download the latest MySQL connector driver. There are both Zip and Tar files available on this page, and we can download any of these.

2. Unzip the compressed file and copy the mysql-connector-java-5.1.xx-bin file.

3. Paste this file into the Apache JMeter lib folder, which will be at following path: ..\apache-jmeter-x.x\lib.

If you do not complete the above steps, you may see this error while running the JMeter tests: “No suitable driver found for jdbc:mysql://localhost”3306/Testing.”

Configuring the Database:

To configure the database, you must first open the JMeter batch file. Then, add a JDBC Connection Configuration using the following flow shown in the following image: Test Plan > Thread Group > Add > Config Element > JDBC Connection Configuration.

 

Then, we need to fill in the values for following fields:

• Variable name bound to Pool section:Variable Name: A variable name is mandatory in a request because it is used as a reference between JMeter and the Database connection.
• Connection Pool Configuration Section: Values are filled by default in this section. Use the default values.
• Connection Validation by Pool Section: Values are filled by default in this section. Use the default values.

Database Connection Configuration:

1. Database URL – This should be in following format:

 

2. JDBC driver class – This is the class in which the code with a particular database is implemented:

• com.mysql.jdbc.Driver – This is a static series which is used while connecting to MySQL

If the correct driver class name is not provided in the configuration, the following error may appear as we are running the JMeter tests: “No suitable driver found for jdbc:mysql://localhost:3306/Testing.”

• Username  – the name of a user who has access to the DB
• Password – the password for accessing the DB

3. Add a JDBC Request as a child element to the Thread Group. This will be used to send requests to the DB.

In the JDBC request, we will send the Query that we want to use to fetch records from the DB. For example, we could use the following code:

1.  select * from ApplicantProfile where ApplicantId = < ApplicantId > ;

Reading values from JDBC response:

Do not use the extractors, because the DB values are not extracted properly. Instead, use Beanshell Postprocessor as a child under the JDBC Request. BeanShell Postprocessor stores the DB value in Array, which can be used for assertion.

Use following code with the BeanShell PostProcessor:

1.  ArrayList result = vars.getObject("result"); 2.    3.  for (HashMap row: result) { 4.      Iterator it = row.entrySet().iterator(); 5.      while (it.hasNext()) { 6.          Map.Entry pair = (Map.Entry) it.next(); 7.          log.info(pair.getKey() + " = " + pair.getValue()); 8.   } 9.  }

JMeter Logs

Debug Sampler is another way to get the response to show as JMeter variables. Add the Debug sampler to the thread group:

Here is how the response of the debug sampler is shown:

Asserting the response of the BeanShell PostProcesser:

We will add the BeanShell Assertion as a child element to the JDBC Request element in order to assert the response of the BeanShell Postprocessor:

Enter the following code in the BeanShell assertion:

• String expected contains the value that is expected to be stored in the DB.
• String actual contains the variable name that represents the DB value.

1.  String expected = ("Roger"); 2.  String actual = ("${FirstName_1}"); 3.  if (expected.equals(actual)) { 4.      Failure = false; 5.  }  6. else { 7.      Failure = true; 8.      FailureMessage = "Difference detected, expected: " + expected + " and actual: " + actual; 9.  }

If both the string ‘expected’ and ‘actual’ match. then the result is passed; otherwise, the result will fail. We can also add a CSV file to read the expected value from there.

Troubleshooting

There are some issues that may need some troubleshooting while setting up this integration. I will address few of those here.

Issue 1

You receive the following message: “Communications link failure. The last packet sent successfully to the server was 0 milliseconds ago. The drive has not received any packets from the server.”

There are a few probable solutions for this issue:

• If your DB connection uses VPN, make sure that the VPN is connected and running.
• Make sure the firewall or anti-virus software isn’t blocking the MySQL service.
• If there is a “skip-networking” line in your MySQL config file, make it comment by adding the “#” sign at the beginning of that line.
• Uncomment the “bind-address” attribute or change it to one of the following IPs: bind-address=”127.0.0.1″ or bind-address=”0.0.0.0″

Issue 2

You receive the following message: “Access denied for user ‘ca4app’@%’ to database ‘sakila'”

There are following probable solutions for this issue:

• Make sure the Database Name, Schema Name, Username and Password are correct.

This concludes my process for integrating a database with JMeter, and then using this integration to fetch records from the database and assert that these records were accurately fetched. Please feel free to leave comments or questions.