• 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!

Help Clean

Fortera Global

Intermediate OT User
Joined
Nov 20, 2015
Messages
1,180
Solutions
2
Reaction score
117
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

Lua:
---
--- Generated by EmmyLua(https://github.com/EmmyLua)
--- Created by leu.
--- DateTime: 04/04/18 18:42
---
--[[ Clean Database by Cjaker | Refactor and SQL Optimizations by Leu ]]--
local inactiveMonths = 5 --> Quantos meses o player ficou inativo
local createdMonths = 2 --> Quantos meses a conta foi criada e não possui character criado.
local protectedAccIdEnd = 20 --ignorar accounts com id <= 20
local function clearInactivePlayers()
    local inactiveTimestamp = os.time() - (86400 * (inactiveMonths*30))
    local totalClear=0
    local fromClause = "`players` WHERE `account_id` > ".. protectedAccIdEnd .." AND lastlogin <= "..inactiveTimestamp
    local resultId = db.storeQuery("SELECT COUNT(*) as num_inativos FROM "..fromClause)
    if resultId ~= false then
        totalClear = result.getDataInt(resultId, 'num_inativos')
        result.free(resultId)
        if totalClear > 0 then
            db.asyncQuery("DELETE FROM "..fromClause)
        end
    end
    return totalClear
end
local function clearEmptyAccounts()
    local totalClear = 0
    local createdTimestamp = os.time() - (86400 * (createdMonths*30))
    local fromClause = "`accounts` as ACCS WHERE `id` > ".. protectedAccIdEnd .." AND `creation` <= "..createdTimestamp.." AND (SELECT COUNT(*) from `players` WHERE `account_id` = `ACCS`.`id`) > 0"
    local resultId = db.storeQuery("SELECT COUNT(*) as num_inativas FROM "..fromClause)
    if resultId~= false then
        totalClear = result.getDataInt(resultId,'num_inativas')
        result.free(resultId)
        if totalClear > 0 then
            db.asyncQuery("DELETE FROM "..fromClause)
        end
    end
    return totalClear
end
function onStartup()
    print('>> ' ..clearInactivePlayers().. " players inativos deletados.")
    print('>> ' ..clearEmptyAccounts().. " contas vazias deletadas.")
end
 
Last edited by a moderator:
Solution
Try this:
Code:
local fromClause = "`accounts` WHERE `id` > ".. protectedAccIdEnd .." AND `creation` <= "..createdTimestamp.." AND (SELECT COUNT(*) from `players` WHERE `account_id` = `accounts`.`id`) > 0"
Change
Code:
`accounts` ACCS
To
Code:
`accounts` as ACCS

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
 
Code:
(SELECT COUNT(*) from `players` WHERE `account_id` = `ACCS`.`id`)
ACCS is `ACCS`.`id`

this?:
Lua:
local fromClause = "`accounts` as ACCS WHERE `id` > ".. protectedAccIdEnd .." AND `creation` <= "..createdTimestamp.." AND (SELECT COUNT(*) from `players` WHERE `account_id` = `ACCS`.`id`) > 0"
 
actually error:

Lua:
[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

Edited the main thread with actually code.
 
Try this:
Code:
local fromClause = "`accounts` WHERE `id` > ".. protectedAccIdEnd .." AND `creation` <= "..createdTimestamp.." AND (SELECT COUNT(*) from `players` WHERE `account_id` = `accounts`.`id`) > 0"
 
Solution
Try this:
Code:
local fromClause = "`accounts` WHERE `id` > ".. protectedAccIdEnd .." AND `creation` <= "..createdTimestamp.." AND (SELECT COUNT(*) from `players` WHERE `account_id` = `accounts`.`id`) > 0"

hey its worked thanks !!
 
Last edited by a moderator:
Back
Top