When testing your APIs, web service, or other system parts, you might need to record or retrieve data from a database. The purpose of this interaction is to check the correct record of specific data in the DB or to prepare test data for the tests by adding specific records to the database. Show This article will show you how to test a MySQL connection by using Apache JMeter™ in order to check, update, and add entries to your database. Based on these examples, each tester can then perform the appropriate interaction with the database, during their further testing. In this blog post, we will use JMeter 3.2, the database MySQL 5.7.18 Community Edition (free and installed on your PC), and Java8. 📕 Related Resource: Learn more about How to Test BLOB from MySQLHow to Test MySQL Connection With JMeterThere are two steps to testing MySQL connection with JMeter: Part 1 - Configuring the Database on JMeterBefore you start working with a database by using JMeter, you need to do the following:
Installing the Connector/J 5.1.42 Driver for DB InteractionTo interact with the database it is necessary to: 1. Download the Connector/J 5.1.42 driver. 2. Enter the downloaded archive. 3. Copy the mysql-connector-java-5.1.42-bin.jar to ..\apache-jmeter-3.2\lib folder Note: the driver Connector/J 5.1.42 is used only for connection to the MySQL database. Database Connection Configuration4. Boot JMeter and add the JDBC Connection Configuration element. Right click on Thread Group -> Add -> Config Element -> JDBC Connection Configuration The JDBC Connection Configuration is used to configure JMeter connections to the database. 5. Fill in the Variable Name field. The value of this field is used to associate a specific connection configuration (JDBC Connection Configuration) to the database and a specific request (JDBC Request) sent by JMeter. If the field isn’t filled, the request won’t be sent and the JMeter Console would show the following message: java.lang.IllegalArgumentException: Variable Name must not be empty for element:JDBC Connection Configuration. This can be seen in the screenshot below: 6. Configure the JDBC Connection Configuration. Anyone can use the following configuration by changing the value of localhost and the database schema:
The rest of the fields can remain as set by default in JMeter. Part 2 - Sending a Request to the DatabaseSending a Data Request7. Add a Thread Group element After adding an element, leave all the parameters filled by default (as JMeter itself provides). 8. Add a JDBC Request as a child element to the Thread Group. The request will allow sending requests to the DB. 9. Configure the JDBC Request
For example: Let’s say we have two JDBC Requests, which are under the same element of the Thread Group. For JDBC Request №1 and JDBC Request № 2, the value of the Result variable name = A field is set. When JDBC Request №1 returns a response from the database, all fields and field values will be assigned to the A variable. After receiving a response from JDBC Request № 2, all the fields and field values will be assigned again to the A variable, which already contains the values from the JDBC Request № 1. Thus, the data received by JDBC Request № 1 will not be stored in the А variable. The А variable will store the response only from the JDBC Request № 2. In order for the response from JDBC Request №1 and JDBC Request № 2 to be stored in variables and not overwritten, the Result variable name field must be specified as unique (For example, for JDBC Request №1, the value of the Result variable name = A field, and for the JDBC Request № 1 Result variable name = B)
10. Add a View Results Tree Listener and run the test. The item View Results Tree Listener allows you to see the sent requests and received responses as shown in the picture below. Note: Database requests can be built according to SQL language syntax, which supports a particular database. JMeter does not impose any restrictions on its design. Asserting the DB ResponseIn most of the cases when working with a DB, data receiving is complete with respect to the given conditions. If the data corresponding to the request exists in the database, we will get the results. But what happens when the DB doesn’t have the data we were testing? In such a case, we will get an empty response, but JMeter won’t report it as an error. It will look like this: To check if the response was correct, we can take the next steps: 10. Add the BeanShell Assertion element as a child element to the JDBC Request element: 11. Add the code as shown in the picture: Example: if(vars.getObject("Request_1").size()!=0){log.info("!!!!!!!!!!!!!! The response is not empty !!!!!!!!!!!!!!!!!!!");}else{FailureMessage="!!!!!!!!!!!! The response is empty !!!!!!!!!!!!!";Failure=true;prev.setStopThread(true);}
Now, when getting an empty response, all further acts or requests in the Thread Group will be stopped and the correct error message will appear: Sometimes there are cases when the connection to the database is temporarily unavailable, the IP address has been changed, or some other reasons have arisen, as a result of which there was no response from the database. As a result, in the above code, errors occur during execution, although there is no error data when there is a connection to the database. To exclude such moments, you can add a connection test to the database and, in the absence of this connection, correctly display the error message. Example:
if(ResponseCode.equals("200")){if(vars.getObject("Request_1").size()!=0){log.info("!!!!!!!!!!!!!! The response is not empty !!!!!!!!!!!!!!!!!!!");}else{FailureMessage="!!!!!!!!!!!! The response is empty !!!!!!!!!!!!!";Failure=true;prev.setStopThread(true);}}else{FailureMessage="!!!!!!!!!!!! No connection to the database !!!!!!!!!!!!!";Failure=true;prev.setStopThread(true);}
That’s it! You now know how to create and assert a basic DB configuration with JMeter. Stay tuned for part two, where we will learn how to perform advanced manipulations on variables and on responses, as well as how to perform advanced query types. START TESTING NOW This blog was originally published on August 15, 2017, and has since been updated for accuracy and relevance. How to check MySQL connection status?The active or total connection can be known with the help of threads_connected variable. The variable tells about the number of currently open connections. mysql> show status where `variable_name` = 'Threads_connected'; Here is the output.
How do I know if my MySQL database is connected?To test the connection to your database, run the telnet hostname port on your Looker server. For example, if you are running MySQL on the default port and your database name is mydb, the command would be telnet mydb 3306 . If the connection is working, you will see something similar to this: Trying 10.10.
How to check total connections in MySQL?SHOW STATUS WHERE `variable_name` = 'Threads_connected'; This will show you all the open connections.
How to check MySQL connection string?Driver={mySQL};Server=myServerAddress;Port=3306;Option=131072;Stmt=;Database=myDataBase;User=myUsername;Password=myPassword; The driver defaults to port value 3306, if not specified in the connection string, as 3306 is the default port for MySQL.
|