Help Clean

Discussion in 'Support' started by God Of Pain, Apr 16, 2018.

  1. God Of Pain

    God Of Pain Member

    Joined:
    Aug 3, 2017
    Messages:
    471
    Likes Received:
    12
    Best Answers:
    8
    I tried to use this script but got one error, I using tfs 1.2 and gesior acc

    error:

    [Error - mysql_real_query] Query: DELETE FROM `accounts` as ACCS WHERE `id` > 20 AND `creation` <= 1518984775 AND (SELECT COUNT(*) from `players` WHERE `account_id` = `ACCS`.`id`) > 0

    Message: 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 'as ACCS WHERE `id` > 20 AND `creation` <= 1518984775 AND (SELECT COUNT(*) from `' at line 1

    Code (Lua):
    1. ---
    2. --- Generated by EmmyLua(https://github.com/EmmyLua)
    3. --- Created by leu.
    4. --- DateTime: 04/04/18 18:42
    5. ---
    6. --[[ Clean Database by Cjaker | Refactor and SQL Optimizations by Leu ]]--
    7. local inactiveMonths = 5 --> Quantos meses o player ficou inativo
    8. local createdMonths = 2 --> Quantos meses a conta foi criada e não possui character criado.
    9. local protectedAccIdEnd = 20 --ignorar accounts com id <= 20
    10. local function clearInactivePlayers()
    11.     local inactiveTimestamp = os.time() - (86400 * (inactiveMonths*30))
    12.     local totalClear=0
    13.     local fromClause = "`players` WHERE `account_id` > ".. protectedAccIdEnd .." AND lastlogin <= "..inactiveTimestamp
    14.     local resultId = db.storeQuery("SELECT COUNT(*) as num_inativos FROM "..fromClause)
    15.     if resultId ~= false then
    16.         totalClear = result.getDataInt(resultId, 'num_inativos')
    17.         result.free(resultId)
    18.         if totalClear > 0 then
    19.             db.asyncQuery("DELETE FROM "..fromClause)
    20.         end
    21.     end
    22.     return totalClear
    23. end
    24. local function clearEmptyAccounts()
    25.     local totalClear = 0
    26.     local createdTimestamp = os.time() - (86400 * (createdMonths*30))
    27.     local fromClause = "`accounts` as ACCS WHERE `id` > ".. protectedAccIdEnd .." AND `creation` <= "..createdTimestamp.." AND (SELECT COUNT(*) from `players` WHERE `account_id` = `ACCS`.`id`) > 0"
    28.     local resultId = db.storeQuery("SELECT COUNT(*) as num_inativas FROM "..fromClause)
    29.     if resultId~= false then
    30.         totalClear = result.getDataInt(resultId,'num_inativas')
    31.         result.free(resultId)
    32.         if totalClear > 0 then
    33.             db.asyncQuery("DELETE FROM "..fromClause)
    34.         end
    35.     end
    36.     return totalClear
    37. end
    38. function onStartup()
    39.     print('>> ' ..clearInactivePlayers().. " players inativos deletados.")
    40.     print('>> ' ..clearEmptyAccounts().. " contas vazias deletadas.")
    41. end
     
    Last edited: Apr 22, 2018
  2. Best Answer:
    Post #9 by slawkens, Apr 22, 2018
  3. slawkens

    slawkens Well-Known Member

    Joined:
    Aug 27, 2007
    Messages:
    2,723
    Likes Received:
    317
    Best Answers:
    46
    Change
    Code (Text):
    1.  
    2. `accounts` ACCS
    3.  
    To
    Code (Text):
    1.  
    2. `accounts` as ACCS
    3.  
     
  4. God Of Pain

    God Of Pain Member

    Joined:
    Aug 3, 2017
    Messages:
    471
    Likes Received:
    12
    Best Answers:
    8
    thanks!
    another error:

    [Error - mysql_real_query] Query: DELETE FROM `accounts` as ACCS WHERE `id` > 20 AND `creation` <= 1518771614 AND (SELECT COUNT(*) from `players` WHERE `account_id` = ACCS.`id`) > 0
    Message: 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 'as ACCS WHERE `id` > 20 AND `creation` <= 1518771614 AND (SELECT COUNT(*) from `' at line 1
     
  5. Sarah Wesker

    Sarah Wesker S.E ©

    Joined:
    Mar 16, 2017
    Messages:
    279
    Likes Received:
    122
    Best Answers:
    20
    Code (Text):
    1. (SELECT COUNT(*) from `players` WHERE `account_id` = `ACCS`.`id`)
    ACCS is `ACCS`.`id`
     
  6. God Of Pain

    God Of Pain Member

    Joined:
    Aug 3, 2017
    Messages:
    471
    Likes Received:
    12
    Best Answers:
    8
    this?:
    Code (Lua):
    1. local fromClause = "`accounts` as ACCS WHERE `id` > ".. protectedAccIdEnd .." AND `creation` <= "..createdTimestamp.." AND (SELECT COUNT(*) from `players` WHERE `account_id` = `ACCS`.`id`) > 0"
     
  7. God Of Pain

    God Of Pain Member

    Joined:
    Aug 3, 2017
    Messages:
    471
    Likes Received:
    12
    Best Answers:
    8
  8. God Of Pain

    God Of Pain Member

    Joined:
    Aug 3, 2017
    Messages:
    471
    Likes Received:
    12
    Best Answers:
    8
    actually error:

    Code (Lua):
    1. [Error - mysql_real_query] Query: DELETE FROM `accounts` as ACCS WHERE `id` > 20 AND `creation` <= 1518984775 AND (SELECT COUNT(*) from `players` WHERE `account_id` = `ACCS`.`id`) > 0
    2. Message: 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 'as ACCS WHERE `id` > 20 AND `creation` <= 1518984775 AND (SELECT COUNT(*) from `' at line 1
    Edited the main thread with actually code.
     
  9. God Of Pain

    God Of Pain Member

    Joined:
    Aug 3, 2017
    Messages:
    471
    Likes Received:
    12
    Best Answers:
    8
  10. slawkens

    slawkens Well-Known Member

    Joined:
    Aug 27, 2007
    Messages:
    2,723
    Likes Received:
    317
    Best Answers:
    46
    Best Answer
    Try this:
    Code (Text):
    1.  
    2. local fromClause = "`accounts` WHERE `id` > ".. protectedAccIdEnd .." AND `creation` <= "..createdTimestamp.." AND (SELECT COUNT(*) from `players` WHERE `account_id` = `accounts`.`id`) > 0"
    3.  
     
    God Of Pain likes this.
  11. God Of Pain

    God Of Pain Member

    Joined:
    Aug 3, 2017
    Messages:
    471
    Likes Received:
    12
    Best Answers:
    8
    hey its worked thanks !!
     
    Last edited: Apr 23, 2018

Share This Page

Loading...