[ Resolvido ]Query Deletar Lastday Accounts Que Não Logam Mais.

[cs007 13]

Bom pessoal tenho um Otserv a bastante tempo On porem a database está começando a ficar pesada d+ sem necessidado pois muito dos players nem logam mais. Eu tenho um Query para deletar os players que não e o bastante se alguém souber outra maneira de aliviar a database ajuda ai... Eu tentei deletar as contas que não logam da seguinte forma.

 

Delete FROM `accounts` WHERE `lastday` < UNIX_TIMESTAMP() - 10*24*60*60

 

Que apagaria as contas que não logasse com 10 dias, Porem meu lastday conta como se as contas logassem todos os dias ai não tem como eu deletar. Alguém pode ajudar? Obrigado.

 

EDIT

 

 

 

 

 

Por causa dos DDoS a Xtibia fica mais Off que On e por estarem ocupados com o problema fica dificil responder as duvidas. Aguardando resposta! valeu

Editado Fevereiro 27, 2012 por cs007

[smarf 3]

Olha nao sei se vai ajudar mais se ja ta muito pesada a melhor forma e voce ir deletando guild que nao sao usadas , e player com nomes muito grande que nao logao mais ok ?

Fica a dica ai , acho que vai ajudar um pouco.

[cs007 13]

Essa não e a solução eu tenho o script para apagar os players. Meu problema e que no Mysql o Lastday não deleta as contas porque e como se todas as contas logassem todos os dias...

 

Se alguém souber a solução será bem vinda

[cs007 13]

Mas acho que vai ser bom para mim e o

UPDATE `houses` SET `owner` = '0' WHERE `houses`.`owner` NOT IN ( SELECT `players`.`id` FROM `players` WHERE `players`.`id` = `houses`.`owner` );

Bom mesmo e se tivesse como deletar as contas :S Se alguém sabe o pq que o meu lastday não esta certo me avisa aew.

 

Valeu

[cs007 13]

ninguém? affs concerteza alguém usa esse sistema e não quer repassar vacilo isso

[MatheusEnjoy 69]

Tente reformular o titulo do tópico, talvez assim alguém possa te ajudar.

São mais de 8 mil erros no PhpMyAdmin, se colocar o titulo deletar last login seria mais facil, pense em algo criativo.

[cs007 13]

Obrigado pela dica

[Vodkart 1515]

já existe,

 

 

 

globalevents.xml:

<globalevent name="dbcleaner" type="startup" event="script" value="dbcleaner.lua"/>

 

Add globalevents/scripts

 

dbcleaner.lua

------------------------------------------------------------------------------*
----- [[> Automated Database Cleanup 1.1 Structure //By Cybermaster <]] ------|
-------------- [[> System 2.0 Revamped by Teh Maverick <3 <]] ----------------|
------------- [[>  Removal of empty accounts by darkaos  <]] ---------------|
--------------- [[> Function getDBPlayersCount() by Elf <]] ------------------|
------------------------------------------------------------------------------|
------------------------------------------------------------------------------|
--- ~!READ THIS!~ ------------------------------------------------------------|
--- Be sure to back up your database and test this on your server first, -----|
--- I(Teh Maverick) cannot guarantee it will work the same for every core. ---|
--- It is very easy to test, with the log file and values that are printed ---|
-----------------------------------Enjoy!-------------------------------------|
------------------------------------------------------------------------------*

function countRowsWhereInTable(table, field, condition)
local result = db.getResult("SELECT COUNT(" .. field .. ") as count FROM " .. table .. " WHERE " .. field .. " = '" .. condition .. "';")
local tmp = result:getDataInt("count")
result:free()
return tmp
end

function getDBPlayersCount()
local result = db.getResult("SELECT COUNT(id) as count FROM `players`;")
local tmp = result:getDataInt("count")
result:free()
return tmp
end

function getDBAccountsCount()
local result = db.getResult("SELECT COUNT(id) as count FROM `accounts`;")
local tmp = result:getDataInt("count")
result:free()
return tmp
end

function onStartup()
local DB_BEFORE = {players = getDBPlayersCount(), accounts = getDBAccountsCount()}
local result,result1, ii, numPlayersToDelete, numAccountsDeleted, tmp = 0, 0, 0, 0, 0
local pid, aid = {}, {}
local dropCount = {players={},accounts={}}

local config = {
deleteAccountWithNoPlayers = true,
cleanChildTables = true,
printResult = true,
saveResultToFile = true,
logFileName = 'db_cleanup.txt'
}

--In each table, players with below specified level, and days of inactivity will be deleted from db on server startup
local cleanup = {
[1] = {level = 11, time = 5 * 24 * 60 * 60},
[2] = {level = 20, time = 15 * 24 * 60 * 60},
[3] = {level = 50, time = 30 * 24 * 60 * 60},
[4] = {level = 100, time = 60 * 24 * 60 * 60},
[5] = {level = 130, time = 90 * 24 * 60 * 60}
}

local childAttributeTables = {
players = {
[1] = {table = "`player_viplist`", idField = "`player_id`"},
[2] = {table = "`player_storage`", idField = "`player_id`"},
[3] = {table = "`player_spells`", idField = "`player_id`"},
[4] = {table = "`player_skills`", idField = "`player_id`"},
[5] = {table = "`player_namelocks`", idField = "`player_id`"},
[6] = {table = "`player_items`", idField = "`player_id`"},
[7] = {table = "`player_depotitems`", idField = "`player_id`"},
[8] = {table = "`houses`", idField = "`owner`"},
[9] = {table = "`house_auctions`", idField = "`player_id`"},
[10] = {table = "`players`", idField = "`id`"} -- Keep this as the last item in the array
--Note: `houses` and `bans` are in the DB triggers for TFS so don't worry about them.
--Also I did not want to put killers, or deaths on here because that is historic data,
--do so at your own risk.
},
accounts = {
[1] = {table = "`accounts`", idField = "`id`"},
[2] = {table = "`account_viplist`", idField = "`account_id`"}
}
}

--Clean up all the players and player data
for i = 1, #cleanup do
result = db.getResult("SELECT `id`,`name`,`account_id` FROM `players` WHERE `level` < ".. cleanup[i].level .." AND `name` NOT IN('Account Manager', 'Sorcerer Sample', 'Druid Sample', 'Paladin Sample', 'Knight Sample', 'Rook Sample') AND `group_id` < 2 AND `lastlogin` < UNIX_TIMESTAMP() - ".. cleanup[i].time .." AND `lastlogin` > 0;")
if(result:getID() ~= -1) then
ii = 1
repeat
pid[ii] = result:getDataInt("id") -- list the players id into an array
aid[ii] = result:getDataInt("account_id") -- list the account id of each player being removed into an array
ii = ii + 1
until not(result:next())
result:free()
end
numPlayersToDelete = ii - 1

--Drop players and their child table attribute data such as skills, items, etc.
for j = 1, numPlayersToDelete do

if(config.cleanChildTables) then
for k = 1, #childAttributeTables.players do
if childAttributeTables.players[k].table == "houses" then
house = getHouseByPlayerGUID(pid[j])
if house ~= 0 or house ~= nil then
doCleanHouse(house)
doUpdateHouseAuctions()
end
else
dropCount.players[k] = ((dropCount.players[k] or 0) + countRowsWhereInTable(childAttributeTables.players[k].table, childAttributeTables.players[k].idField, pid[j]))
db.executeQuery("DELETE FROM " .. childAttributeTables.players[k].table .. " WHERE " .. childAttributeTables.players[k].idField .. " = '" .. pid[j] .. "';")
end
end
else
db.executeQuery("DELETE FROM `players` WHERE `id` = '" .. pid[j] .. "';")
end
end
end

--Drop all the accounts that have 0 players linked to them (at the moment its only checking from the list of players removed)
if config.deleteAccountWithNoPlayers then
--This part was scripted by Darkhaos, modified/fixed by Teh Maverick --[[
for acc = 1, #aid do
result1 = db.getResult("SELECT `id` FROM `accounts` WHERE `id` = '" .. aid[acc] .. "';")
if result1:getID() ~= -1 then -- check to make sure the account exists
result1:free()
for i = 1, #childAttributeTables.accounts do
--Make sure there are no other players on the account
result1 = db.getResult("SELECT COUNT(id) as count FROM `players` WHERE `account_id` = '" .. aid[acc] .. "';")
tmp = result1:getDataInt("count")
if(tmp <= 0) then
--Remove accounts
dropCount.accounts[i] = ((dropCount.accounts[i] or 0) + countRowsWhereInTable(childAttributeTables.accounts[i].table, childAttributeTables.accounts[i].idField, aid[acc]))
db.executeQuery("DELETE FROM " .. childAttributeTables.accounts[i].table .. " WHERE " .. childAttributeTables.accounts[i].idField .. " = '" .. aid[acc] .. "';")
end
end
end
end
end
--]]

--Print and Save results (configurable)
local DB_NOW = {players = DB_BEFORE.players - getDBPlayersCount(), accounts = DB_BEFORE.accounts - getDBAccountsCount()}
if DB_NOW.players > 0 or DB_NOW.accounts > 0 then
local text = ">> [DBCLEANUP] " .. DB_NOW.players .. " inactive players" .. (config.deleteAccountWithNoPlayers and " and " .. DB_NOW.accounts .. " empty accounts" or "") .. " have been deleted from the database."

--Write to console
if config.printResult then
print("")
print(text)
if config.cleanChildTables then
--Write player info
for i = 1,#dropCount.players do
print("[!] --> Dropped: " .. dropCount.players[i] .. " from " .. childAttributeTables.players[i].table .. " table")
end
--Write account info
if config.deleteAccountWithNoPlayers then
for i = 1,#dropCount.accounts do
print("[!] --> Dropped: " .. dropCount.accounts[i] .. " from " .. childAttributeTables.accounts[i].table .. " table")
end
end
print("")
end
end

--Write to file
if config.saveResultToFile then

local file = io.open("data/logs/"..config.logFileName, "a")
file:write("[" .. os.date("%d %B %Y %X ", os.time()) .. "] " .. text .. "\n")

if config.cleanChildTables then
--Write player info
for i = 1, #dropCount.players do
file:write("[!] --> Dropped: " .. dropCount.players[i] .. " from " .. childAttributeTables.players[i].table .. " table\n")
end
--Write account info
if config.deleteAccountWithNoPlayers then
for i = 1, #dropCount.accounts do
file:write("[!] --> Dropped: " .. dropCount.accounts[i] .. " from " .. childAttributeTables.accounts[i].table .. " table\n")
end
end
file:write("\n")
end
file:close()
end
end
return true
end

 

 

 

ai o exemplo do log:

 

 

[26 April 2011 13:55:22 ] >> [DBCLEANUP] 1143 inactive players and 784 empty accounts have been deleted from the database.

[!] --> Dropped: 0 from `player_viplist` table

[!] --> Dropped: 860 from `player_storage` table

[!] --> Dropped: 0 from `player_spells` table

[!] --> Dropped: 991 from `player_skills` table

[!] --> Dropped: 0 from `player_namelocks` table

[!] --> Dropped: 389 from `player_items` table

[!] --> Dropped: 627 from `player_depotitems` table

[!] --> Dropped: 1143 from `players` table

[!] --> Dropped: 784 from `accounts` table

[!] --> Dropped: 153 from `account_viplist` table

 

Aqui está o link para o tópico original: http://otland.net/f82/2-0-database-cleaner-inactive-players-players-child-data-skills-items-etc-127320/

Editado Fevereiro 27, 2012 por Vodkart

[MatheusEnjoy 69]

Viu como solucionar mais rapido o problema?

Mudando o titulo huahuaha

 

Posso mover para sanada?

[cs007 13]

@MatheusEnjoy

 

Sim veio...kkkkkkkkkkkkk valeu pela dica

 

@Vodkart

 

Veio só me explica uma coisa. Ele vai deleter as accs que não logam? Como funciona ele todo dia faz o check? valeu pela ajuda REP +

 

Tem como eu ligar o comando sem ter que desligar o Ot? Valeu

Editado Março 1, 2012 por cs007

[MatheusEnjoy 69]

Tópico resolvido.

[Piabeta Kun 359]

mais nao foi movido para a sessao correta Reportando!

[HisashiitYamaguti 108]

Tópico movido para a área correta e fechado.

Ajude o fórum ficar limpo e seguro reportando, mas não se esqueça que a experiência e o divertimento vem em primeiro lugar .