sql error query

Discussion in 'Support' started by God Of Pain, Apr 16, 2018 at 3:44 PM.

  1. God Of Pain

    God Of Pain Member

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

    error:
    Code (Text):
    1. [Error - mysql_real_query] Query: DELETE FROM `accounts` ACCS WHERE `id` > 20 AND `creation` <= 1518685214 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 'ACCS WHERE `id` > 20 AND `creation` <= 1518685214 AND (SELECT COUNT(*) from `pla' at line 1

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

    slawkens Well-Known Member

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

    God Of Pain Member

    Joined:
    Aug 3, 2017
    Messages:
    301
    Likes Received:
    5
    Best Answers:
    5
    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
     
  4. Sarah Wesker

    Sarah Wesker S.E ©

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

    God Of Pain Member

    Joined:
    Aug 3, 2017
    Messages:
    301
    Likes Received:
    5
    Best Answers:
    5
    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"
     

Share This Page

Loading...