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

Discussion in 'GlobalEvents, Spells & CreatureEvents' started by Darkhaos, Aug 3, 2011.

  1. Darkhaos

    Darkhaos (:

    Joined:
    Apr 17, 2008
    Messages:
    1,922
    Likes Received:
    170
    Best Answers:
    0
    Here is the second version of my Anti-Dupe system that you can see here http://otland.net/f81/basic-anti-dupe-system-131898/

    This new script brings a better configuration, now it checks the tables that you want and some optimizations made.

    //TODO
    • You can configure the tables that will be checked.
    • It saves the info on a log file, declared by yourself.
    • When a duplicated item is deleted, scripts prints it on console, showing the player who has the item, item-id, count and serial of the item.
    • With the function 'generateSerial()' you can set an unique serial to every item.

    First of all, you need to paste this function at your lib/functions.lua:
    Code (Lua):
    1. function generateSerial()
    2.     local strings = "AaBbCcDdEeFfGgHhIiJjKkLlMmOoPpQqRrSsTtUuVvWwXxYyZz"
    3.     local newSerialStr, newSerialInt = "", 0
    4.     local newSerial = ""
    5.     local query, query2, query3
    6.     repeat
    7.         for k = 1, math.random(1, 10) do
    8.             local l = math.random(1, string.len(strings))
    9.             newSerialStr = newSerialStr .. string.sub(strings, l, l)
    10.         end
    11.         newSerialInt = math.random(999999)
    12.         newSerial = newSerialStr .. "-" .. newSerialInt
    13.         query = db.getResult("select * from player_items where substring(convert(attributes using latin1) from 18) = " .. db.escapeString(newSerial))
    14.         query2 = db.getResult("select * from player_depotitems where substring(convert(attributes using latin1) from 18) = " .. db.escapeString(newSerial))
    15.         query3 = db.getResult("select * from tile_items where substring(convert(attributes using latin1) from 18) = " .. db.escapeString(newSerial))
    16.     until query:getID() == -1 and query2:getID() == -1 and query3:getID() == -1
    17.     return "!" .. newSerial
    18. end
    Now, everytime that do you want to set an unique serial to an item, just use:
    Code (Lua):
    1. doItemSetAttribute(uid, "serial", generateSerial())
    This is the script that will delete items with duplicated serial when serer starts:
    Create a file of globalevents/scripts called trackitems.lua and paste:
    Code (Lua):
    1. tablesToCheck = {"player_items", "player_depotitems", "tile_items", {"player_items", {"player_depotitems", "tile_items"}}, {"player_depotitems", "tile_items"}}
    2.  
    3. function onStartup()
    4.     local text, final = "", ""
    5.     local filex = "data/logs/duplicated.txt"
    6.     local f = io.open(filex, "a+")
    7.     local count = 0
    8.     for i = 1, table.maxn(tablesToCheck) do
    9.         if type(tablesToCheck[i]) == "string" then
    10.             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)")
    11.             if query:getID() ~= -1 then
    12.                 while(true) do
    13.                     local delete = db.executeQuery("delete from " .. tablesToCheck[i] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. " and player_id = " .. query:getDataInt("player_id") .. ";")
    14.                     text = "[!] -> Deleting items with duplicated serial from '" .. tablesToCheck[i] .. "': [Player: " .. getPlayerNameByGUID(query:getDataInt("player_id")) .. ", Item: " .. query:getDataInt("itemtype") .. ", Count: " .. query:getDataInt("count") .. ", Serial: " .. query:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!")
    15.                     count = (delete and count + 1 or count)
    16.                     final = final .. (final ~= "" and "\n" or "") .. text
    17.                     print(text)
    18.                     if not query:next() then break end
    19.                 end
    20.             end
    21.         else
    22.             if type(tablesToCheck[i][2]) == "string" then
    23.                 local query = db.getResult("SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' FROM " .. tablesToCheck[i][1] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) IN (SELECT SUBSTRING(CONVERT(attributes USING latin1) FROM 18) FROM " .. tablesToCheck[i][2] .. " WHERE CONVERT(attributes USING latin1) LIKE '%serial%' GROUP BY SUBSTRING(CONVERT(attributes USING latin1) FROM 18) HAVING COUNT(*) > 0)")
    24.                 if query:getID() ~= -1 then
    25.                     while(true) do
    26.                         local query_ = db.getResult("SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' FROM " .. tablesToCheck[i][2] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) IN (SELECT SUBSTRING(CONVERT(attributes USING latin1) FROM 18) FROM " .. tablesToCheck[i][1] .. " WHERE CONVERT(attributes USING latin1) LIKE '%serial%' GROUP BY SUBSTRING(CONVERT(attributes USING latin1) FROM 18) HAVING COUNT(*) > 0)")
    27.                         local delete = db.executeQuery("delete from " .. tablesToCheck[i][1] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. ";")
    28.                         count = (delete and count + 1 or count)
    29.                         local delete2 = db.executeQuery("delete from " .. tablesToCheck[i][2] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. ";")
    30.                         count = (delete2 and count + 1 or count)
    31.                         text = "[!] -> Deleting item with duplicated serial from '" .. tablesToCheck[i][1] .. "' [Player: " .. getPlayerNameByGUID(query:getDataInt("player_id")) .. ", Item: " .. query:getDataInt("itemtype") .. ", Count: " .. query:getDataInt("count") .. ", Serial: " .. query:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!") ..
    32.                         "\n[!] -> Deleting item with duplicated serial from '" .. tablesToCheck[i][2] .. "' [Player: " .. getPlayerNameByGUID(query_:getDataInt("player_id")) .. ", Item: " .. query_:getDataInt("itemtype") .. ", Count: " .. query_:getDataInt("count") .. ", Serial: " .. query_:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!")
    33.                         final = final .. (final ~= "" and "\n" or "") .. text
    34.                         print(text)
    35.                         if not query:next() then break end
    36.                     end
    37.                 end
    38.             else
    39.                 for j = 1, #tablesToCheck[i][2] do
    40.                     local query = db.getResult("SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' FROM " .. tablesToCheck[i][1] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) IN (SELECT SUBSTRING(CONVERT(attributes USING latin1) FROM 18) FROM " .. tablesToCheck[i][2][j] .. " WHERE CONVERT(attributes USING latin1) LIKE '%serial%' GROUP BY SUBSTRING(CONVERT(attributes USING latin1) FROM 18) HAVING COUNT(*) > 0)")
    41.                     if query:getID() ~= -1 then
    42.                         while(true) do
    43.                             local query_ = db.getResult("SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' FROM " .. tablesToCheck[i][2][j] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) IN (SELECT SUBSTRING(CONVERT(attributes USING latin1) FROM 18) FROM " .. tablesToCheck[i][1] .. " WHERE CONVERT(attributes USING latin1) LIKE '%serial%' GROUP BY SUBSTRING(CONVERT(attributes USING latin1) FROM 18) HAVING COUNT(*) > 0)")
    44.                             local delete = db.executeQuery("delete from " .. tablesToCheck[i][1] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. ";")
    45.                             count = (delete and count + 1 or count)
    46.                             local delete2 = db.executeQuery("delete from " .. tablesToCheck[i][2][j] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. ";")
    47.                             count = (delete2 and count + 1 or count)
    48.                             text = "[!] -> Deleting item with duplicated serial from '" .. tablesToCheck[i][1] .. "' [Player: " .. getPlayerNameByGUID(query:getDataInt("player_id")) .. ", Item: " .. query:getDataInt("itemtype") .. ", Count: " .. query:getDataInt("count") .. ", Serial: " .. query:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!") ..
    49.                             "\n[!] -> Deleting item with duplicated serial from '" .. tablesToCheck[i][2][j] .. "' [Player: " .. getPlayerNameByGUID(query_:getDataInt("player_id")) .. ", Item: " .. query_:getDataInt("itemtype") .. ", Count: " .. query_:getDataInt("count") .. ", Serial: " .. query_:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!")
    50.                             final = final .. (final ~= "" and "\n" or "") .. text
    51.                             print(text)
    52.                             if not query:next() then break end
    53.                         end
    54.                     end
    55.                 end
    56.             end
    57.         end
    58.     end
    59.     if f ~= nil then
    60.         f:write("[" .. os.date("%d %B %Y %X ", os.time()) .. "] >> [Anti-Dupe] " .. count .. " duplicated items have been deleted from the database.\n" .. (final == "" and "[!] -> No duplicated item was found in the database" or final) .. "\n\n")
    61.         f:close()
    62.     else
    63.         print("[!] -> [Anti-Dupe] Cannot save info to file!")
    64.     end
    65.     return true
    66. end
    67.  
    Code (XML):
    1.     <globalevent name="trackitems" type="start" event="script" value="trackitems.lua"/>
    Also you can see items with serial from phpMyAdmin with this command:
    Code (SQL):
    1. SELECT * , SUBSTRING( CONVERT( attributes
    2. USING latin1 )
    3. FROM 18 ) AS  'serial'
    4. FROM player_items
    5. WHERE CONVERT( attributes
    6. USING latin1 ) LIKE  '%serial%'
    [​IMG]

    And you can see items with duplicated serials with the following commands:
    Code (SQL):
    1.  
    2. --Use this command to see duplicated items on players inventory
    3. SELECT * , SUBSTRING( CONVERT( attributes
    4. USING latin1 )
    5. FROM 18 ) AS  'duplicated serials'
    6. FROM player_items
    7. WHERE SUBSTRING( CONVERT( attributes
    8. USING latin1 )
    9. FROM 18 )
    10. IN (
    11.  
    12. SELECT SUBSTRING( CONVERT( attributes
    13. USING latin1 )
    14. FROM 18 )
    15. FROM player_items
    16. WHERE CONVERT( attributes
    17. USING latin1 ) LIKE  '%serial%'
    18. GROUP BY SUBSTRING( CONVERT( attributes
    19. USING latin1 )
    20. FROM 18 )
    21. HAVING COUNT( * ) >1
    22. )
    23.  
    24. --Use this command to see duplicated items on players inventory or depot items
    25. SELECT * , SUBSTRING( CONVERT( attributes
    26. USING latin1 )
    27. FROM 18 ) AS 'duplicated serials'
    28. FROM player_items
    29. WHERE SUBSTRING( CONVERT( attributes
    30. USING latin1 )
    31. FROM 18 )
    32. IN (
    33.  
    34. SELECT SUBSTRING( CONVERT( attributes
    35. USING latin1 )
    36. FROM 18 )
    37. FROM player_depotitems
    38. WHERE CONVERT( attributes
    39. USING latin1 ) LIKE '%serial%'
    40. GROUP BY SUBSTRING( CONVERT( attributes
    41. USING latin1 )
    42. FROM 18 )
    43. HAVING COUNT( * ) >1
    44. )
    45.  
    46. --Use this command to see duplicated items on players depots
    47. SELECT * , SUBSTRING( CONVERT( attributes
    48. USING latin1 )
    49. FROM 18 ) AS 'duplicated serials'
    50. FROM player_depotitems
    51. WHERE SUBSTRING( CONVERT( attributes
    52. USING latin1 )
    53. FROM 18 )
    54. IN (
    55.  
    56. SELECT SUBSTRING( CONVERT( attributes
    57. USING latin1 )
    58. FROM 18 )
    59. FROM player_depotitems
    60. WHERE CONVERT( attributes
    61. USING latin1 ) LIKE '%serial%'
    62. GROUP BY SUBSTRING( CONVERT( attributes
    63. USING latin1 )
    64. FROM 18 )
    65. HAVING COUNT( * ) >1
    66. )
    67.  
    68. --Use this command to see duplicated items on player_items and player_depotitems (Example of usage)
    69. --Use this command to see duplicated items on players depots
    70. SELECT * , SUBSTRING( CONVERT( attributes
    71. USING latin1 )
    72. FROM 18 ) AS 'duplicated serials'
    73. FROM player_items
    74. WHERE SUBSTRING( CONVERT( attributes
    75. USING latin1 )
    76. FROM 18 )
    77. IN (
    78.  
    79. SELECT SUBSTRING( CONVERT( attributes
    80. USING latin1 )
    81. FROM 18 )
    82. FROM player_depotitems
    83. WHERE CONVERT( attributes
    84. USING latin1 ) LIKE '%serial%'
    85. GROUP BY SUBSTRING( CONVERT( attributes
    86. USING latin1 )
    87. FROM 18 )
    88. HAVING COUNT( * ) >0
    89.  
    [​IMG]
    Note that you can change the table 'player_items' to 'player_depotitems' to get info about items on depot

    Please, comment with constructive criticism :)!
    Rep++ is appreciated!
     
    Last edited: Aug 5, 2011
    Limos and TaurenLess like this.
  2. TaurenLess

    TaurenLess New Member

    Joined:
    Jan 2, 2010
    Messages:
    76
    Likes Received:
    1
    Best Answers:
    0
    Good, where I put the function in my shop.lua ?

    Script Here:
    Code (Lua):
    1.  
    2. local SHOP_MSG_TYPE = MESSAGE_EVENT_ORANGE
    3. local SQL_interval = 30
    4.  
    5. function onThink(interval, lastExecution)
    6.     local result_plr = db.getResult("SELECT * FROM z_ots_comunication WHERE `type` = 'login';")
    7.     if(result_plr:getID() ~= -1) then
    8.         while(true) do
    9.             local id = tonumber(result_plr:getDataInt("id"))
    10.             local action = tostring(result_plr:getDataString("action"))
    11.             local delete = tonumber(result_plr:getDataInt("delete_it"))
    12.             local cid = getCreatureByName(tostring(result_plr:getDataString("name")))
    13.             if isPlayer(cid) then
    14.                 local itemtogive_id = tonumber(result_plr:getDataInt("param1"))
    15.                 local itemtogive_count = tonumber(result_plr:getDataInt("param2"))
    16.                 local container_id = tonumber(result_plr:getDataInt("param3"))
    17.                 local container_count = tonumber(result_plr:getDataInt("param4"))
    18.                 local add_item_type = tostring(result_plr:getDataString("param5"))
    19.                 local add_item_name = tostring(result_plr:getDataString("param6"))
    20.                 local received_item = 0
    21.                 local full_weight = 0
    22.                 if add_item_type == 'container' then
    23.                     container_weight = getItemWeightById(container_id, 1)
    24.                     if isItemRune(itemtogive_id) == TRUE then
    25.                         items_weight = container_count * getItemWeightById(itemtogive_id, 1)
    26.                     else
    27.                         items_weight = container_count * getItemWeightById(itemtogive_id, itemtogive_count)
    28.                     end
    29.                     full_weight = items_weight + container_weight
    30.                 else
    31.                     full_weight = getItemWeightById(itemtogive_id, itemtogive_count)
    32.                     if isItemRune(itemtogive_id) == TRUE then
    33.                         full_weight = getItemWeightById(itemtogive_id, 1)
    34.                     else
    35.                         full_weight = getItemWeightById(itemtogive_id, itemtogive_count)
    36.                     end
    37.                 end
    38.                 local free_cap = getPlayerFreeCap(cid)
    39.                 if full_weight <= free_cap then
    40.                     if add_item_type == 'container' then
    41.                         local new_container = doCreateItemEx(container_id, 1)
    42.                         local iter = 0
    43.                         while iter ~= container_count do
    44.                             doAddContainerItem(new_container, itemtogive_id, itemtogive_count)
    45.                             iter = iter + 1
    46.                         end
    47.                         received_item = doPlayerAddItemEx(cid, new_container)
    48.                     else
    49.                         local new_item = doCreateItemEx(itemtogive_id, itemtogive_count)
    50.                         doItemSetAttribute(new_item, "description", "This item was purchased at the shop by the player ".. getPlayerName(cid) ..".")
    51.                         doItemSetAttribute(new_item, "aid", getPlayerGUID(cid)+10000)
    52.                         received_item = doPlayerAddItemEx(cid, new_item)
    53.                     end
    54.                     if received_item == RETURNVALUE_NOERROR then
    55.                         doPlayerSendTextMessage(cid, SHOP_MSG_TYPE, 'You received >> '.. add_item_name ..' << from OTS shop.')
    56.                         db.executeQuery("DELETE FROM `z_ots_comunication` WHERE `id` = " .. id .. ";")
    57.                         db.executeQuery("UPDATE `z_shop_history_item` SET `trans_state`='realized', `trans_real`=" .. os.time() .. " WHERE id = " .. id .. ";")
    58.                     else
    59.                         doPlayerSendTextMessage(cid, SHOP_MSG_TYPE, '>> '.. add_item_name ..' << from OTS shop is waiting for you. Please make place for this item in your backpack/hands and wait about '.. SQL_interval ..' seconds to get it.')
    60.                     end
    61.                 else
    62.                     doPlayerSendTextMessage(cid, SHOP_MSG_TYPE, '>> '.. add_item_name ..' << from OTS shop is waiting for you. It weight is '.. full_weight ..' oz., you have only '.. free_cap ..' oz. free capacity. Put some items in depot and wait about '.. SQL_interval ..' seconds to get it.')
    63.                 end
    64.             end
    65.             if not(result_plr:next()) then
    66.                 break
    67.             end
    68.         end
    69.         result_plr:free()
    70.     end
    71.     return true
    72. end
    73.  
    I tried and NotWork:
    Code (Lua):
    1.  
    2. ...
    3. local new_item = doCreateItemEx(itemtogive_id, itemtogive_count)
    4.                         doItemSetAttribute(new_item, "description", "This item was purchased at the shop by the player ".. getPlayerName(cid) ..".")
    5.                         doItemSetAttribute(new_item, "aid", getPlayerGUID(cid)+10000)
    6.                         received_item = doPlayerAddItemEx(cid, new_item)
    7.                                                 doItemSetAttribute(uid, "serial", generateSerial())
    8. ...
    9.  
    and
    Code (Lua):
    1.  
    2.                                                 doItemSetAttribute(new_item, "serial", generateSerial())
    3.  
     
  3. Darkhaos

    Darkhaos (:

    Joined:
    Apr 17, 2008
    Messages:
    1,922
    Likes Received:
    170
    Best Answers:
    0
    It won't work if items has more than one attribute, item should have only the serial and no more attributes
     
  4. conde2

    conde2 Active Member

    Joined:
    Jun 22, 2008
    Messages:
    341
    Likes Received:
    37
    Best Answers:
    0
    Fixed the problem with connection lost?
     
  5. MxSoft

    MxSoft Leave Tibia, Live Life.

    Joined:
    Dec 22, 2009
    Messages:
    1,773
    Likes Received:
    38
    Best Answers:
    0
    Same question here :)
     
  6. Darkhaos

    Darkhaos (:

    Joined:
    Apr 17, 2008
    Messages:
    1,922
    Likes Received:
    170
    Best Answers:
    0
    I never got that problem, so i cannot fix that, you should try with this script and post here if you find problem with connection.
    Also you should post here the distro used by you and the sqlType (Remember, this script has been tested on TFS 0.3.6pl1 and works with MySql)
     
  7. TaurenLess

    TaurenLess New Member

    Joined:
    Jan 2, 2010
    Messages:
    76
    Likes Received:
    1
    Best Answers:
    0
    Work 0.4 yes...
    Rep+ ;D
     
    Last edited: Aug 4, 2011
  8. setembro

    setembro Member

    Joined:
    May 22, 2008
    Messages:
    110
    Likes Received:
    1
    Best Answers:
    0
    No have command /track??
     
  9. setembro

    setembro Member

    Joined:
    May 22, 2008
    Messages:
    110
    Likes Received:
    1
    Best Answers:
    0
    Bro, no have command /track ?? and i used command to see duplicated items on players inventory in mysql and my server down.
     
  10. setembro

    setembro Member

    Joined:
    May 22, 2008
    Messages:
    110
    Likes Received:
    1
    Best Answers:
    0
  11. Darkhaos

    Darkhaos (:

    Joined:
    Apr 17, 2008
    Messages:
    1,922
    Likes Received:
    170
    Best Answers:
    0
    No, doesn't have because i haven't time to make that talkaction. I'll add it later (Maybe)
     
  12. setembro

    setembro Member

    Joined:
    May 22, 2008
    Messages:
    110
    Likes Received:
    1
    Best Answers:
    0
  13. Lava Titan

    Lava Titan High Exp OTS Developer

    Joined:
    Jul 25, 2009
    Messages:
    1,448
    Likes Received:
    56
    Best Answers:
    0
    i used the query and now i got a problem x.x

    [​IMG]
     
  14. anotapreta

    anotapreta New Member

    Joined:
    Mar 12, 2010
    Messages:
    104
    Likes Received:
    0
    Best Answers:
    0
    So if someone duplicate items and hide inside the house will not be detected any more.

    EDIT: msql connection lost tsf0.4
     
    Last edited: Aug 12, 2011
  15. Darkhaos

    Darkhaos (:

    Joined:
    Apr 17, 2008
    Messages:
    1,922
    Likes Received:
    170
    Best Answers:
    0
    There are many variables that could be causing this error (Note: That isn't an error from my script, that's a server error, caused maybe because your pc don't have enough memory or have too many connections)

    Possible reasons for that:
    Firewall (Try closing it).
    Not enough memory (Try increasing with set global max_allowed_packet = x).
    Too much connections.

    You should try updating your mysql version.
     
    Last edited: Aug 12, 2011
  16. LucasFerraz

    LucasFerraz Systems Analyst

    Joined:
    Jun 10, 2010
    Messages:
    2,856
    Likes Received:
    85
    Best Answers:
    0
  17. Mock

    Mock Mock the bear (MTB)

    Joined:
    Jul 29, 2008
    Messages:
    619
    Likes Received:
    93
    Best Answers:
    0
    Are you crazy? Install a new mysql will not fix the problem.
    Your code idea is good... but the code still a mess. You open mysql connections but don't close.
    Code (Lua):
    1.  
    2. function generateSerial()
    3.     --[[local strings = "AaBbCcDdEeFfGgHhIiJjKkLlMmOoPpQqRrSsTtUuVvWwXxYyZz"
    4.     local newSerialStr, newSerialInt = "", 0
    5.     local newSerial = ""
    6.     local query, query2, query3
    7.     repeat
    8.         for k = 1, math.random(1, 10) do
    9.             local l = math.random(1, string.len(strings))
    10.             newSerialStr = newSerialStr .. string.sub(strings, l, l)
    11.         end
    12.         newSerialInt = math.random(999999)
    13.         newSerial = newSerialStr .. "-" .. newSerialInt]]
    14.         query = db.getResult("select * from player_items where substring(convert(attributes using latin1) from 18) = " .. db.escapeString(newSerial))
    15.         query2 = db.getResult("select * from player_depotitems where substring(convert(attributes using latin1) from 18) = " .. db.escapeString(newSerial))
    16.         query3 = db.getResult("select * from tile_items where substring(convert(attributes using latin1) from 18) = " .. db.escapeString(newSerial))
    17.     until query:getID() == -1 and query2:getID() == -1 and query3:getID() == -1
    18.     return "!" .. newSerial
    19. end
    20.  
    why you dont close the connection? ¬¬'''
    To stop this error:
    [​IMG]
    use this modified function.
    Code (Lua):
    1.  
    2. function generateSerial()
    3.     local strings = "AaBbCcDdEeFfGgHhIiJjKkLlMmOoPpQqRrSsTtUuVvWwXxYyZz"
    4.     local newSerialStr, newSerialInt = "", 0
    5.     local newSerial = ""
    6.     local query, query2, query3
    7.     repeat
    8.         for k = 1, math.random(1, 10) do
    9.             local l = math.random(1, string.len(strings))
    10.             newSerialStr = newSerialStr .. string.sub(strings, l, l)
    11.         end
    12.         newSerialInt = math.random(999999)
    13.         newSerial = newSerialStr .. "-" .. newSerialInt
    14.         query = db.getResult("select * from player_items where substring(convert(attributes using latin1) from 18) = " .. db.escapeString(newSerial))
    15.         query2 = db.getResult("select * from player_depotitems where substring(convert(attributes using latin1) from 18) = " .. db.escapeString(newSerial))
    16.         query3 = db.getResult("select * from tile_items where substring(convert(attributes using latin1) from 18) = " .. db.escapeString(newSerial))
    17.     until query:getID() == -1 and query2:getID() == -1 and query3:getID() == -1
    18.     if query:getID() ~= -1 then query:free() end
    19.         if query2:getID() ~= -1 then query2:free() end
    20.         if query3:getID() ~= -1 then query3:free() end
    21.         return "!" .. newSerial
    22. end
    23.  
    Do the same for the main code and will works.

    Code (Lua):
    1.  
    2. Lua Code:
    3.  
    4. tablesToCheck = {"player_items", "player_depotitems", "tile_items", {"player_items", {"player_depotitems", "tile_items"}}, {"player_depotitems", "tile_items"}}
    5.  
    6. function onStartup()
    7.     local text, final = "", ""
    8.     local filex = "data/logs/duplicated.txt"
    9.     local f = io.open(filex, "a+")
    10.     local count = 0
    11.     for i = 1, table.maxn(tablesToCheck) do
    12.         if type(tablesToCheck[i]) == "string" then
    13.             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)")
    14.             if query:getID() ~= -1 then
    15.                 while(true) do
    16.                     local delete = db.executeQuery("delete from " .. tablesToCheck[i] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. " and player_id = " .. query:getDataInt("player_id") .. ";")
    17.                     text = "[!] -> Deleting items with duplicated serial from '" .. tablesToCheck[i] .. "': [Player: " .. getPlayerNameByGUID(query:getDataInt("player_id")) .. ", Item: " .. query:getDataInt("itemtype") .. ", Count: " .. query:getDataInt("count") .. ", Serial: " .. query:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!")
    18.                     count = (delete and count + 1 or count)
    19.                     final = final .. (final ~= "" and "\n" or "") .. text
    20.                     print(text)
    21.                     if not query:next() then break end
    22.                 end
    23.                 query:free()
    24.             end
    25.         else
    26.             if type(tablesToCheck[i][2]) == "string" then
    27.                 local query = db.getResult("SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' FROM " .. tablesToCheck[i][1] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) IN (SELECT SUBSTRING(CONVERT(attributes USING latin1) FROM 18) FROM " .. tablesToCheck[i][2] .. " WHERE CONVERT(attributes USING latin1) LIKE '%serial%' GROUP BY SUBSTRING(CONVERT(attributes USING latin1) FROM 18) HAVING COUNT(*) > 0)")
    28.                 if query:getID() ~= -1 then
    29.                     while(true) do
    30.                         local query_ = db.getResult("SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' FROM " .. tablesToCheck[i][2] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) IN (SELECT SUBSTRING(CONVERT(attributes USING latin1) FROM 18) FROM " .. tablesToCheck[i][1] .. " WHERE CONVERT(attributes USING latin1) LIKE '%serial%' GROUP BY SUBSTRING(CONVERT(attributes USING latin1) FROM 18) HAVING COUNT(*) > 0)")
    31.                         local delete = db.executeQuery("delete from " .. tablesToCheck[i][1] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. ";")
    32.                         count = (delete and count + 1 or count)
    33.                         local delete2 = db.executeQuery("delete from " .. tablesToCheck[i][2] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. ";")
    34.                         count = (delete2 and count + 1 or count)
    35.                         text = "[!] -> Deleting item with duplicated serial from '" .. tablesToCheck[i][1] .. "' [Player: " .. getPlayerNameByGUID(query:getDataInt("player_id")) .. ", Item: " .. query:getDataInt("itemtype") .. ", Count: " .. query:getDataInt("count") .. ", Serial: " .. query:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!") ..
    36.                         "\n[!] -> Deleting item with duplicated serial from '" .. tablesToCheck[i][2] .. "' [Player: " .. getPlayerNameByGUID(query_:getDataInt("player_id")) .. ", Item: " .. query_:getDataInt("itemtype") .. ", Count: " .. query_:getDataInt("count") .. ", Serial: " .. query_:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!")
    37.                         final = final .. (final ~= "" and "\n" or "") .. text
    38.                         print(text)
    39.                         if not query:next() then query:free() break end
    40.                     end
    41.                 end
    42.             else
    43.                 for j = 1, #tablesToCheck[i][2] do
    44.                     local query = db.getResult("SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' FROM " .. tablesToCheck[i][1] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) IN (SELECT SUBSTRING(CONVERT(attributes USING latin1) FROM 18) FROM " .. tablesToCheck[i][2][j] .. " WHERE CONVERT(attributes USING latin1) LIKE '%serial%' GROUP BY SUBSTRING(CONVERT(attributes USING latin1) FROM 18) HAVING COUNT(*) > 0)")
    45.                     if query:getID() ~= -1 then
    46.                         while(true) do
    47.                             local query_ = db.getResult("SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' FROM " .. tablesToCheck[i][2][j] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) IN (SELECT SUBSTRING(CONVERT(attributes USING latin1) FROM 18) FROM " .. tablesToCheck[i][1] .. " WHERE CONVERT(attributes USING latin1) LIKE '%serial%' GROUP BY SUBSTRING(CONVERT(attributes USING latin1) FROM 18) HAVING COUNT(*) > 0)")
    48.                             local delete = db.executeQuery("delete from " .. tablesToCheck[i][1] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. ";")
    49.                             count = (delete and count + 1 or count)
    50.                             local delete2 = db.executeQuery("delete from " .. tablesToCheck[i][2][j] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. ";")
    51.                             count = (delete2 and count + 1 or count)
    52.                             text = "[!] -> Deleting item with duplicated serial from '" .. tablesToCheck[i][1] .. "' [Player: " .. getPlayerNameByGUID(query:getDataInt("player_id")) .. ", Item: " .. query:getDataInt("itemtype") .. ", Count: " .. query:getDataInt("count") .. ", Serial: " .. query:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!") ..
    53.                             "\n[!] -> Deleting item with duplicated serial from '" .. tablesToCheck[i][2][j] .. "' [Player: " .. getPlayerNameByGUID(query_:getDataInt("player_id")) .. ", Item: " .. query_:getDataInt("itemtype") .. ", Count: " .. query_:getDataInt("count") .. ", Serial: " .. query_:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!")
    54.                             final = final .. (final ~= "" and "\n" or "") .. text
    55.                             print(text)
    56.                             if not query:next() then break end
    57.                         end
    58.                         query:free()
    59.                     end
    60.                 end
    61.             end
    62.         end
    63.     end
    64.     if f ~= nil then
    65.         f:write("[" .. os.date("%d %B %Y %X ", os.time()) .. "] >> [Anti-Dupe] " .. count .. " duplicated items have been deleted from the database.\n" .. (final == "" and "[!] -> No duplicated item was found in the database" or final) .. "\n\n")
    66.         f:close()
    67.     else
    68.         print("[!] -> [Anti-Dupe] Cannot save info to file!")
    69.     end
    70.     return true
    71. end
    72.  
     
  18. Cykotitan

    Cykotitan Experienced G'

    Joined:
    Nov 4, 2008
    Messages:
    16,897
    Likes Received:
    808
    Best Answers:
    1
    this isn't LuaSQL. db.getResult doesn't open any connection, it always uses the persistent db connection of TFS
    result.free(res) doesn't close anything, but only frees the allocated resources

    just correcting you, not saying :free shouldn't be used.
     
  19. Mock

    Mock Mock the bear (MTB)

    Joined:
    Jul 29, 2008
    Messages:
    619
    Likes Received:
    93
    Best Answers:
    0
    Since tfs 0.3.4 we have to free the results because every db.getResult open a new connection, you checked if its really true about it? I already tested on 0.3.4, if you run like 500 db.getResult the mysql block the new connections and some times close the server db connection.
     
  20. Lava Titan

    Lava Titan High Exp OTS Developer

    Joined:
    Jul 25, 2009
    Messages:
    1,448
    Likes Received:
    56
    Best Answers:
    0
    well, i modified the scripts, to the ones you posted Mock, but i still have the same error x.x

    Im using tfs 0.4 btw
     

Share This Page

Loading...