• There is NO official Otland's Discord server and NO official Otland's server list. The Otland's Staff does not manage any Discord server or server list. Moderators or administrator of any Discord server or server lists have NO connection to the Otland's Staff. Do not get scammed!

GlobalEvent Anti-Dupe - Delete all items with duplicated serial from your database.

1. I made it removes all the items, because with this script you can't know which is the original and wich is the duplicated
2. Freeing queries wion't fix that (Maybe fix it for some users only).

Anyways thanks for these features that you added/modified.

It doesn't really matter which one is the duplicated item, they are all the same item. As long as one of the duplicated items exist its OK, seeing as the only real way to duplicate in TFS is by a server crash, the duplicated items will be removed upon startup.

Btw its just good form to make sure the results are freed at the end of a script :p anyway nice idea.
 
Im new in here, but i would like to know where should i implement
Lua:
doItemSetAttribute(uid, "serial", generateSerial())

my shop.lua is the next one:

Lua:
function onThink(interval, lastExecution, thinkInterval)

	local result = db.getResult("SELECT * FROM shop_history WHERE `processed` = 0;")
	
		if(result:getID() ~= -1) then
			while(true) do
				cid = getCreatureByName(tostring(result:getDataString("player")))
				product = tonumber(result:getDataInt("product"))
				itemr = db.getResult("SELECT * FROM shop_offer WHERE `id` = "..product..";")
					if isPlayer(cid) then
						local id = tonumber(itemr:getDataInt("item"))
						local tid = tonumber(result:getDataInt("id"))
						local count = tonumber(itemr:getDataInt("count"))
						local tipe = tonumber(itemr:getDataInt("type"))
						local productn = tostring(itemr:getDataString("name"))
							if isInArray({5,8},tipe) then
								if getPlayerFreeCap(cid) >= getItemWeightById(id, count) then
									if isContainer(getPlayerSlotItem(cid, 3).uid) then
										doAddContainerItem(getPlayerSlotItem(cid, 3).uid, id, count)
					if doAddContainerItem then
                                            doPlayerSendTextMessage(cid,19, "You have received >> "..productn.." << from our shop system")
											db.executeQuery("UPDATE `shop_history` SET `processed`='1' WHERE id = " .. tid .. ";")
										else
											doPlayerSendTextMessage(cid,19, "Sorry, you don't have enough space on container to receive >> "..productn.." <<")
										end
									else
										doPlayerSendTextMessage(cid,19, "Sorry, you don't have a container to receive >> "..productn.." <<")
									end
								else
									doPlayerSendTextMessage(cid,19, "Sorry, you don't have enough capacity to receive >> "..productn.." << (You need: "..getItemWeightById(id, count).." Capacity)")
								end
							elseif isInArray({6,7},tipe) then
									if tipe == 6 then
										bcap = 8
										bid = 1987
									elseif tipe == 7 then
										bcap = 20
										bid = 1988
									end
									if isItemRune(id) then
										count = 1
									end
									if getPlayerFreeCap(cid) >= (getItemWeightById(1987, 1) + getItemWeightById(id,count * bcap)) then
										local bag = doCreateItemEx(bid, 1)
											for i = 1,bcap do
												doAddContainerItem(bag, id, count)
											end
										received = doPlayerAddItemEx(getPlayerSlotItem(cid, 3).uid, bag)
										if received == RETURNVALUE_NOERROR then
											doPlayerSendTextMessage(cid,19, "You have received >> "..productn.." << from our shop system")
											db.executeQuery("UPDATE `shop_history` SET `processed`='1' WHERE id = " .. tid .. ";")
										else
											doPlayerSendTextMessage(cid,19, "Sorry, you don't have enough space to receive >> "..productn.." <<")
										end
									else
										doPlayerSendTextMessage(cid,19, "Sorry, you don't have enough capacity to receive >> "..productn.." << (You need: "..getItemWeightById(id, count).." Capacity)")
									end
							end
					end
				itemr:free()
				if not(result:next()) then
					break
				end
			end
			result:free()
		end
	return true
end
Thanks in advance =)
 
Last edited:
i execute query sql ERROR ! #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--Use this command to see duplicated items on players inventory or depot items ' at line 22
 
If I just use

this:
Lua:
function generateSerial()
	local strings = "AaBbCcDdEeFfGgHhIiJjKkLlMmOoPpQqRrSsTtUuVvWwXxYyZz"
	local newSerialStr, newSerialInt = "", 0
	local newSerial = ""
	local query, query2, query3
	repeat
		for k = 1, math.random(1, 10) do
			local l = math.random(1, string.len(strings))
			newSerialStr = newSerialStr .. string.sub(strings, l, l)
		end
		newSerialInt = math.random(999999)
		newSerial = newSerialStr .. "-" .. newSerialInt
		query = db.getResult("select * from player_items where substring(convert(attributes using latin1) from 18) = " .. db.escapeString(newSerial))
		query2 = db.getResult("select * from player_depotitems where substring(convert(attributes using latin1) from 18) = " .. db.escapeString(newSerial))
		query3 = db.getResult("select * from tile_items where substring(convert(attributes using latin1) from 18) = " .. db.escapeString(newSerial))
	until query:getID() == -1 and query2:getID() == -1 and query3:getID() == -1
	return "!" .. newSerial
end

and this:
Lua:
doItemSetAttribute(uid, "serial", generateSerial())

Will I have problem?



----------
I tried to slove the problem using some coroutine functions to delay all the connections at the same time in the DB
So try this and post here if worked and REP++ please

Thanks to colandus for the function

Lua:
sleep = coroutine.yield
 
function doSleep(co)
	if coroutine.status(co) ~= 'dead' then
		local _, delay = coroutine.resume(co)
		addEvent(doSleep, delay, co)
	end
end
 
function enableSleep(f)
	if type(f) == 'function' then
		local co = coroutine.create(f)
		doSleep(co)
	end
end

tablesToCheck = {"player_items", "player_depotitems"}
 
function onStartup()
	local text, final = "", ""
	local filex = "data/logs/duplicated.txt"
	local f = io.open(filex, "a+")
	for i = 1, table.maxn(tablesToCheck) do
		local query = db.getResult("SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' FROM " .. tablesToCheck[i] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) IN (SELECT SUBSTRING(CONVERT(attributes USING latin1) FROM 18) FROM " .. tablesToCheck[i] .. " WHERE CONVERT(attributes USING latin1) LIKE '%serial%' GROUP BY SUBSTRING(CONVERT(attributes USING latin1) FROM 18) HAVING COUNT(*) > 1)")
		local duplicated = {}
		if query:getID() ~= -1 then
		enableSleep(function()
			while(true) do
				text = "[!] -> Deleting item with duplicated serial: [Player: " .. getPlayerNameByGUID(query:getDataInt("player_id")) .. ", Item: " .. query:getDataInt("itemtype") .. ", Count: " .. query:getDataInt("count") .. ", Serial: " .. query:getDataString("track") .."]... "
				local delete = db.executeQuery("delete from " .. tablesToCheck[i] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. ";")
				if delete then
					text = text .. "Success!"
				else
					text = text .. "Failed!"
				end
				final = final .. (final ~= "" and "\n" or "") .. text
				print(text)
				sleep(50)
				if not query:next() then break end
			end
			if f ~= nil then
				f:write(os.date() .. "\n" .. final .. "\n\n")
				f:close()
			else
				print("[!] -> Cannot save info to file!")
			end
		end)
		
		else
			print("[!] -> Cannot get info, table is empty, there are not items with duplicated serial or you have a problem on the query syntax: " .. tablesToCheck[i] .. ".")
		end
	end
	return true
end
 
Last edited:
If I just use

this:
Lua:
function generateSerial()
	local strings = "AaBbCcDdEeFfGgHhIiJjKkLlMmOoPpQqRrSsTtUuVvWwXxYyZz"
	local newSerialStr, newSerialInt = "", 0
	local newSerial = ""
	local query, query2, query3
	repeat
		for k = 1, math.random(1, 10) do
			local l = math.random(1, string.len(strings))
			newSerialStr = newSerialStr .. string.sub(strings, l, l)
		end
		newSerialInt = math.random(999999)
		newSerial = newSerialStr .. "-" .. newSerialInt
		query = db.getResult("select * from player_items where substring(convert(attributes using latin1) from 18) = " .. db.escapeString(newSerial))
		query2 = db.getResult("select * from player_depotitems where substring(convert(attributes using latin1) from 18) = " .. db.escapeString(newSerial))
		query3 = db.getResult("select * from tile_items where substring(convert(attributes using latin1) from 18) = " .. db.escapeString(newSerial))
	until query:getID() == -1 and query2:getID() == -1 and query3:getID() == -1
	return "!" .. newSerial
end

and this:
Lua:
doItemSetAttribute(uid, "serial", generateSerial())

Will I have problem?



----------

No, @about conde2's script, the problem with database lost connection occurs when database has a lot of items to check (A LOT OF ITEM, 1000+, 5000+, etc) So, a way to fixt is make the script checks only certain items, like this one: http://code.google.com/p/antidupe/source/browse/trunk/ad.lua
 
I'm trying to do it using trigger/procedure
@LucasFerraz
I'm still waiting for you to answer me about the anti-clone script you sold me with problems, not giving to compile, showing some errors. I look forward to returning. thank you


@Darkhaos
Where I put this?
Code:
doItemSetAttribute(uid, "serial", generateSerial())
 
Can someone tell me how to dismantle this script on mysql? as im fearing that this script is making my server getting massive lag spikes, not sure if that is the reason but im quite suspicious it is.
 
work for sqlite?

Code:
[09/07/2014 13:04:48] OTSYS_SQLITE3_PREPARE(): SQLITE ERROR: near "using": syntax error (select * from player_items where substring(convert(attributes using latin1) from 18) = 'BmTSB-297586')
[09/07/2014 13:04:48] OTSYS_SQLITE3_PREPARE(): SQLITE ERROR: near "using": syntax error (select * from player_depotitems where substring(convert(attributes using latin1) from 18) = 'BmTSB-297586')
[09/07/2014 13:04:48] OTSYS_SQLITE3_PREPARE(): SQLITE ERROR: near "using": syntax error (select * from tile_items where substring(convert(attributes using latin1) from 18) = 'BmTSB-297586')
 
Sorry for offtop, my MySQL error fix:
Code:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 19

Code:
SELECT*, SUBSTRING(CONVERT( attributes
USING latin1 )FROM18)AS'duplicated serials'FROM player_itemsWHERE SUBSTRING(CONVERT( attributesUSING latin1 )FROM18)IN(

SELECT SUBSTRING(CONVERT( attributesUSING latin1 )FROM18)FROM player_depotitemsWHERECONVERT( attributesUSING latin1 )LIKE'%serial%'GROUPBY SUBSTRING(CONVERT( attributesUSING latin1 )FROM18)HAVING COUNT(*)>0
)
 
This error is always being told that the id item it has in the photo disappears from the game .. I wanted to find a way to revert and delete the questions that run my database. It's possible?

LPAVU0j.png
 
I need help.
i cant execute because this error:

Code:
Error execute query: near "USING": syntax error
<simple query executor>


Code:
SELECT * , SUBSTRING( CONVERT( attributes
USING latin1 )
FROM 18 ) AS  'serial'
FROM player_items
WHERE CONVERT( attributes
USING latin1 ) LIKE  '%serial%'
 
Back
Top