[trevius]

I am no SQL guru like SqlDev01, and this can probably be written better and more efficiently, but I figured I would share it anyway.

In relation to the tradeskill exploit queries SqlDev01 posted above, these queries are designed to find tradeskill combines that may potentially be exploited. There are 2 queries. They are both the same in every way except the second one actually makes item price changes (see the UPDATE near the bottom of it).

This first query basically creates some temporary tables and totals up the costs for the components, failures and success results for each recipe. It then checks if the total of the components cost is less than the total of the success results cost. It will list all of the recipes it finds that might be an issue.

There are a lot of factors into tradeskill recipe pricing such as where the components come from (are they purchased, dropped, etc), what the sellrates are for each item in the combine and result, if the result is no drop, the actual amount you pay/receive for the components and results after prices are adjusted, combine results that are components for another combine, and probably extra stuff not mentioned here.

These scripts do not account for all of that, as it would require a lot more queries and logic to make sure all factors were considered. It does take account for results being no drop. The scripts also assume the sellrate for all items is set to 1.

I ran this script and found a much larger number of results than I expected to see. It was far to many to deal with them all on a case-by-case basis, which was my original intention. So, I made the second script that actually adjusts item prices.

Note that you would want to adjust the 2 variables at the top of the scripts to specify the range of recipe_ids that you want to run the query on. If you run it for all of them and have around 10k recipes, just be aware that it will take a LONG time to complete. The SUM portion where it actually totals up the costs for components, failures, and successes is what takes the longest. It took around 30 minutes to complete on my test server. Because of this, I would recommend only trying it when your server is not running, as it will possibly impact play performance and take longer to run.

This first query should be perfectly safe to run on any server. It doesn't change any existing data, but remember it takes a long time to complete and shouldn't be done while the server is running:

-- Query Script to find all Exploitable Tradeskill Combines where the cost to create is less than the value of the combine result.
-- 
-- NOTE: This script takes a LONG time to run.  It ran for about 30 minutes on my server before completing.
-- 

-- Define the range of IDs you want to run the query for (smaller ranges will take less time)
SET @StartingRecipeID = 1;
SET @EndingRecipeID = 1000;

-- Create a temporary table that includes the prices for all items in the components, failures, and results
CREATE TEMPORARY TABLE temp_table ( 
	id INT(11) DEFAULT 0 PRIMARY KEY, 
	recipe_id INT(11) DEFAULT 0, 
	combine_value INT(11) DEFAULT 0, 
	fail_value INT(11) DEFAULT 0, 
	result_value INT(11) DEFAULT 0 
);

-- Insert all of the Entry IDs and Recipe IDs into the temp_table
INSERT INTO temp_table ( id, recipe_id ) ( SELECT id, recipe_id FROM tradeskill_recipe_entries );

-- Set the result_value for temp_table - Add 0 value if item is not tradeable
UPDATE tradeskill_recipe_entries, items, temp_table
	SET temp_table.result_value = (
		(IFNULL((SELECT items.price FROM items WHERE items.id = tradeskill_recipe_entries.item_id AND items.nodrop = 1), 0)) *
		(SELECT tradeskill_recipe_entries.successcount FROM tradeskill_recipe_entries WHERE tradeskill_recipe_entries.id = temp_table.id)/
		IF((items.itemtype = 21 AND items.maxcharges > 0), items.maxcharges, 1)
	)
	WHERE items.id = tradeskill_recipe_entries.item_id AND tradeskill_recipe_entries.id = temp_table.id AND tradeskill_recipe_entries.successcount > 0;
	
-- Set the combine_value for temp_table
UPDATE tradeskill_recipe_entries, items, temp_table
	SET temp_table.combine_value = (
		(SELECT items.price FROM items WHERE items.id = tradeskill_recipe_entries.item_id) *
		(SELECT tradeskill_recipe_entries.componentcount FROM tradeskill_recipe_entries WHERE tradeskill_recipe_entries.id = temp_table.id) /
		IF((items.itemtype = 21 AND items.maxcharges > 0), items.maxcharges, 1)
	)
	WHERE items.id = tradeskill_recipe_entries.item_id AND tradeskill_recipe_entries.id = temp_table.id AND tradeskill_recipe_entries.componentcount > 0;

-- Set the fail_value for temp_table
UPDATE tradeskill_recipe_entries, items, temp_table
	SET temp_table.fail_value = (
		(SELECT items.price FROM items WHERE items.id = tradeskill_recipe_entries.item_id) *
		(SELECT tradeskill_recipe_entries.failcount FROM tradeskill_recipe_entries WHERE tradeskill_recipe_entries.id = temp_table.id) /
		IF((items.itemtype = 21 AND items.maxcharges > 0), items.maxcharges, 1)
	)
	WHERE items.id = tradeskill_recipe_entries.item_id AND tradeskill_recipe_entries.id = temp_table.id AND tradeskill_recipe_entries.failcount > 0;

-- Create a temporary table that includes the total prices for all items in the components and results for each recipe
CREATE TEMPORARY TABLE temp_table2 ( 
	recipe_id INT(11) DEFAULT 0 PRIMARY KEY, 
	recipe_name VARCHAR(64) DEFAULT '', 
	combine_value INT(11) DEFAULT 0, 
	fail_value INT(11) DEFAULT 0, 
	result_value INT(11) DEFAULT 0 
);

-- Insert all of the unique Recipe IDs into the temp_table2
REPLACE INTO temp_table2 ( recipe_id ) ( SELECT recipe_id FROM tradeskill_recipe_entries );

-- UPDATE the name of all recipes to the temp_table2
UPDATE temp_table2, tradeskill_recipe SET temp_table2.recipe_name = 
	(
		SELECT tradeskill_recipe.name FROM tradeskill_recipe WHERE tradeskill_recipe.id = temp_table2.recipe_id LIMIT 1 
	) 
	WHERE tradeskill_recipe.id = temp_table2.recipe_id;

-- Test Query to view a sample output of temp_table
SELECT recipe_id, combine_value, fail_value, result_value FROM temp_table LIMIT 10;

-- Test Query to view a sample output of temp_table
SELECT recipe_id, recipe_name, combine_value, fail_value, result_value FROM temp_table2 LIMIT 10;

-- Uncomment the following Delete line to test changes in this query so it will run faster
DELETE FROM temp_table WHERE recipe_id <= @StartingRecipeID AND recipe_id > @EndingRecipeID;

-- UPDATE temp_table2 with the combine_value totals for each recipe in the temp_table
UPDATE temp_table2 SET temp_table2.combine_value = (
	 SELECT SUM(temp_table.combine_value)
	 FROM temp_table 
	 WHERE temp_table.recipe_id = temp_table2.recipe_id
	 GROUP BY temp_table.recipe_id
);

-- UPDATE temp_table2 with the fail_value totals for each recipe in the temp_table
UPDATE temp_table2 SET temp_table2.fail_value = (
	 SELECT SUM(temp_table.fail_value)
	 FROM temp_table 
	 WHERE temp_table.recipe_id = temp_table2.recipe_id
	 GROUP BY temp_table.recipe_id
);

-- UPDATE temp_table2 with the result_value totals for each recipe in the temp_table
UPDATE temp_table2 SET temp_table2.result_value = (
	 SELECT SUM(temp_table.result_value)
	 FROM temp_table 
	 WHERE temp_table.recipe_id = temp_table2.recipe_id
	 GROUP BY temp_table.recipe_id
);

-- Main Query to find any recipe for which the result is worth more than the combined cost of the components.
SELECT recipe_id, recipe_name, combine_value, fail_value, result_value 
	FROM temp_table2 WHERE (combine_value - fail_value) < (result_value - fail_value);
	
-- Drop the temporary tables since they are no longer needed
DROP TEMPORARY TABLE temp_table;
DROP TEMPORARY TABLE temp_table2;

The second query below is the one that actually does the price changes. After running this, you may want to run the above query again just to see how many results it finds on the second pass. There are so many factors that it is most likely not going to resolve every possible recipe, but it should definitely lower the total output down to a more reasonable level where you can investigate them 1 by 1 as needed.

Be warned that this DOES change prices in your items table, so make sure to backup your database before running it.

I can adjust it to create a new table that will save backup prices and post another query to restore original prices if people really need it.

-- Query Script to find all Exploitable Tradeskill Combines where the cost to create is less than the value of the combine result.
-- 
-- NOTE: This script takes a LONG time to run.  It ran for about 30 minutes on my server before completing.
-- 

-- Define the range of IDs you want to run the query for (smaller ranges will take less time)
SET @StartingRecipeID = 1;
SET @EndingRecipeID = 1000;

-- Create a temporary table that includes the prices for all items in the components, failures, and results
CREATE TEMPORARY TABLE temp_table ( 
	id INT(11) DEFAULT 0 PRIMARY KEY, 
	recipe_id INT(11) DEFAULT 0, 
	combine_value INT(11) DEFAULT 0, 
	fail_value INT(11) DEFAULT 0, 
	result_value INT(11) DEFAULT 0 
);

-- Insert all of the Entry IDs and Recipe IDs into the temp_table
INSERT INTO temp_table ( id, recipe_id ) ( SELECT id, recipe_id FROM tradeskill_recipe_entries );

-- Set the result_value for temp_table - Add 0 value if item is not tradeable
UPDATE tradeskill_recipe_entries, items, temp_table
	SET temp_table.result_value = (
		(IFNULL((SELECT items.price FROM items WHERE items.id = tradeskill_recipe_entries.item_id AND items.nodrop = 1), 0)) *
		(SELECT tradeskill_recipe_entries.successcount FROM tradeskill_recipe_entries WHERE tradeskill_recipe_entries.id = temp_table.id)/
		IF((items.itemtype = 21 AND items.maxcharges > 0), items.maxcharges, 1)
	)
	WHERE items.id = tradeskill_recipe_entries.item_id AND tradeskill_recipe_entries.id = temp_table.id AND tradeskill_recipe_entries.successcount > 0;
	
-- Set the combine_value for temp_table
UPDATE tradeskill_recipe_entries, items, temp_table
	SET temp_table.combine_value = (
		(SELECT items.price FROM items WHERE items.id = tradeskill_recipe_entries.item_id) *
		(SELECT tradeskill_recipe_entries.componentcount FROM tradeskill_recipe_entries WHERE tradeskill_recipe_entries.id = temp_table.id) /
		IF((items.itemtype = 21 AND items.maxcharges > 0), items.maxcharges, 1)
	)
	WHERE items.id = tradeskill_recipe_entries.item_id AND tradeskill_recipe_entries.id = temp_table.id AND tradeskill_recipe_entries.componentcount > 0;

-- Set the fail_value for temp_table
UPDATE tradeskill_recipe_entries, items, temp_table
	SET temp_table.fail_value = (
		(SELECT items.price FROM items WHERE items.id = tradeskill_recipe_entries.item_id) *
		(SELECT tradeskill_recipe_entries.failcount FROM tradeskill_recipe_entries WHERE tradeskill_recipe_entries.id = temp_table.id) /
		IF((items.itemtype = 21 AND items.maxcharges > 0), items.maxcharges, 1)
	)
	WHERE items.id = tradeskill_recipe_entries.item_id AND tradeskill_recipe_entries.id = temp_table.id AND tradeskill_recipe_entries.failcount > 0;

-- Create a temporary table that includes the total prices for all items in the components and results for each recipe
CREATE TEMPORARY TABLE temp_table2 ( 
	recipe_id INT(11) DEFAULT 0 PRIMARY KEY, 
	recipe_name VARCHAR(64) DEFAULT '', 
	combine_value INT(11) DEFAULT 0, 
	fail_value INT(11) DEFAULT 0, 
	result_value INT(11) DEFAULT 0 
);

-- Insert all of the unique Recipe IDs into the temp_table2
REPLACE INTO temp_table2 ( recipe_id ) ( SELECT recipe_id FROM tradeskill_recipe_entries );

-- UPDATE the name of all recipes to the temp_table2
UPDATE temp_table2, tradeskill_recipe SET temp_table2.recipe_name = 
	(
		SELECT tradeskill_recipe.name FROM tradeskill_recipe WHERE tradeskill_recipe.id = temp_table2.recipe_id LIMIT 1 
	) 
	WHERE tradeskill_recipe.id = temp_table2.recipe_id;

-- Test Query to view a sample output of temp_table
SELECT recipe_id, combine_value, fail_value, result_value FROM temp_table LIMIT 10;

-- Test Query to view a sample output of temp_table
SELECT recipe_id, recipe_name, combine_value, fail_value, result_value FROM temp_table2 LIMIT 10;

-- Uncomment the following Delete line to test changes in this query so it will run faster
DELETE FROM temp_table WHERE recipe_id <= @StartingRecipeID AND recipe_id > @EndingRecipeID;

-- UPDATE temp_table2 with the combine_value totals for each recipe in the temp_table
UPDATE temp_table2 SET temp_table2.combine_value = (
	 SELECT SUM(temp_table.combine_value)
	 FROM temp_table 
	 WHERE temp_table.recipe_id = temp_table2.recipe_id
	 GROUP BY temp_table.recipe_id
);

-- UPDATE temp_table2 with the fail_value totals for each recipe in the temp_table
UPDATE temp_table2 SET temp_table2.fail_value = (
	 SELECT SUM(temp_table.fail_value)
	 FROM temp_table 
	 WHERE temp_table.recipe_id = temp_table2.recipe_id
	 GROUP BY temp_table.recipe_id
);

-- UPDATE temp_table2 with the result_value totals for each recipe in the temp_table
UPDATE temp_table2 SET temp_table2.result_value = (
	 SELECT SUM(temp_table.result_value)
	 FROM temp_table 
	 WHERE temp_table.recipe_id = temp_table2.recipe_id
	 GROUP BY temp_table.recipe_id
);

-- Main Query to find any recipe for which the result is worth more than the combined cost of the components.
SELECT recipe_id, recipe_name, combine_value, fail_value, result_value 
	FROM temp_table2 WHERE (combine_value - fail_value) < (result_value - fail_value);

UPDATE items, temp_table2, tradeskill_recipe_entries 
	SET items.price = ((temp_table2.combine_value - temp_table2.fail_value) / IF((items.itemtype = 21 AND items.maxcharges = tradeskill_recipe_entries.successcount), 1, tradeskill_recipe_entries.successcount)) 
	WHERE temp_table2.recipe_id = tradeskill_recipe_entries.recipe_id 
	AND items.nodrop = 1 
	AND (temp_table2.combine_value - temp_table2.fail_value) < (temp_table2.result_value - temp_table2.fail_value)
	AND items.price > ((temp_table2.combine_value - temp_table2.fail_value) / tradeskill_recipe_entries.successcount) 
	AND items.id = tradeskill_recipe_entries.item_id 
	AND tradeskill_recipe_entries.successcount > 0 
	AND ISNULL((SELECT tre_table.failcount FROM tradeskill_recipe_entries AS tre_table WHERE tre_table.failcount > 0 AND tre_table.item_id = items.id AND tre_table.recipe_id = temp_table2.recipe_id)) 
	AND tradeskill_recipe_entries.recipe_id >= @StartingRecipeID 
	AND tradeskill_recipe_entries.recipe_id < @EndingRecipeID;
	
-- Drop the temporary tables since they are no longer needed
DROP TEMPORARY TABLE temp_table;
DROP TEMPORARY TABLE temp_table2;

If anyone has any issues running this or any suggestions, let me know and I will try to help out.