JMeter MySQL Datastore
One of the requirement is to implement to provide Unique data across all the users running, once iteration is done data should be restored so that the other users can use the same data again. At any instance of time the same data should not be used by multiple users.
A solution is already available HTTP Simple Table Server, but the problem is by default if data fetch is set to unique, data is deleted from the STS queue. In combination with Error Handling we could achieve Unique reusable data by pushing used data irrespective of success or error back to Queue in STS. To utilize STS we need to load CSV data files to STS and used them during the test.
However, I needed a centralized data store to manage data rather than multiple data files, it is tedious to manage data files as the number for business transactions increase and huge datasets. So I utilized relational database MySQL to manage data, JDBC Sampler in combination with Error Handling to access and release used data.
I have created a Solution with MySQL stored produced which can cater Unique and Sequential data to JMeter, you can add more stored procedure to access data as per your requirement. To Implement it there are multiple Stages as specified below.
- Migrate existing CSV data files to MySQL table
- Add additional columns required to table
- Add MySQL stored procedures for Unique, Sequential data
- Rest data before test
- Configure JMeter to utilize data from MySQL
Migrate existing CSV data files to MySQL table: Migrate all the CSV data files to separate tables.
Below are image showing steps to import data into MySQL using MySQL Workbench.
Add additional columns required to table
Replace database name and Table name to the appropriate and execute the below for all the migrated tables in above step, this will add columns used by Stored Procedures to access data.
use <Database>;
ALTER TABLE <Database>.<Table-name> ADD COLUMN (
d_id INT auto_increment,
d_count INT NOT NULL,
d_state varchar(1) NOT NULL DEFAULT 'A',
d_datetime DATETIME,
d_accessed VARCHAR(255),
PRIMARY KEY (d_id),
INDEX (d_count)
);
Add MySQL Stored procedures
To access data from MySQL I have used the stored procedures, below are some commonly used requirements for accessing data
- Sequential Data
- Unique Data
Sequential Data
There are few use cases where we can use reuse the data across users at the same time, accessing data sequentially is one way where we use all the data unless required to reuse. I have created a stored procedure to do the similar thing.
Stored procedure access data from table with lowest "d_count", which shows number of times data is utilized and "d_state" is A. On every occurrence when data is used "d_datetime", "d_accessed" and "d_state" will be updated showing last used date, last accessed.
DELIMITER $$
CREATE PROCEDURE <Database>.<Table-name>_seq(IN accessedby VARCHAR(255))
BEGIN
SELECT @id:=d_id,@count:=d_count FROM <Database>.<Table-name> WHERE d_state='A' AND d_count=(SELECT min(d_count) FROM <Database>.<Table-name> FOR SHARE SKIP LOCKED) LIMIT 1 FOR UPDATE SKIP LOCKED;
UPDATE <Database>.<Table-name> SET d_count=@count+1,d_datetime=SYSDATE(),d_accessed=accessedby WHERE d_id=@id and d_state='A';
SELECT * FROM <Database>.<Table-name> WHERE d_id=@id and d_state='A';
END$$
DELIMITER ;
Unique Data
In few use cases we have used data uniquely and cannot be used until the user has completed his actions, for example login username and password can not be used by multiple users at same point of time which would trigger errors as the user will be logged out because other user logged In at same time.
To achieve unique data we lock the data on access and release on completion of user actions.
In below stored procedure we lock the data that is being accessed and it cannot be used by other users until released. Column "d_state" will be changed to L to avoid the other users using the same data. In case if multiple users try to access data at the same time, row locked by first user will be skipped for second user and will go ahead with remaining, FOR UPDATE SKIP LOCKED makes sure that the row is not accessible until the stored procedure is complete. If there are no unique data available when user is trying to access UniqueDataNotFound will be returned.
DELIMITER $$
CREATE PROCEDURE <Database>.<Table-name>_unique(IN accessedby VARCHAR(255))
BEGIN
set @id=null;
SELECT @id:=d_id,@count:=d_count FROM <Database>.<Table-name> WHERE d_state='A' and d_count=(SELECT min(d_count) FROM <Database>.<Table-name> FOR SHARE SKIP LOCKED) LIMIT 1 FOR UPDATE SKIP LOCKED;
IF (@id IS NOT NULL) then
UPDATE <Database>.<Table-name> SET d_count=@count+1,d_datetime=SYSDATE(),d_state='L',d_accessed=accessedby WHERE d_id=@id and d_state='A';
SELECT * FROM <Database>.<Table-name> WHERE d_id=@id and d_state='L';
else
Select "UniqueDataNotFound";
end if;
END $$
Once the user action are completed, data row should be released, below stored procedure will do the job. Column "d_state" will be changed to A to allow other users to access data.
DELIMITER $$
CREATE PROCEDURE <Database>.<Table-name>_unique_clear(IN accessedby VARCHAR(255),IN id VARCHAR(255))
BEGIN
UPDATE <Database>.<Table-name> SET d_datetime=SYSDATE(),d_state='A',d_accessed=accessedby WHERE d_id=CAST(id AS UNSIGNED) and d_state='L';
SELECT * FROM <Database>.<Table-name> WHERE d_id=CAST(id AS UNSIGNED) and d_state='A';
END$$
DELIMITER ;
You can use combination of Sequential and Unique data access as per your requirement.
In case if data is not available UniqueDataNotFound is returned, to check that first variable from results needs to be checked, so we use a assertion to validate like below.
In above image "price" is the first variable returned from table and in below $price_1 is used check the data, if data is not found current iteration is stopped and next iteration is started.
Note: Data from MySQL can be one or more rows so we have to provide the row number, however stored procedure will return only one row so you can use variablename_1.
Reset data before test
Add all the tables in sequence and execute the query to reset all the data. Below is the sample.
SET SQL_SAFE_UPDATES = 0;
Update <Database>.<Table-name1> set d_state='A',d_count=0 ;
Update <Database>.<Table-name2> set d_state='A',d_count=0 ;
Update <Database>.<Table-name3> set d_state='A',d_count=0 ;
SET SQL_SAFE_UPDATES = 1;
Sample script, csv data file and stored procedure template are available in GitHub.