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

GlobalEvent 2.0 Database Cleaner - Inactive Players + The Players Child Data (skills, items, etc)

Gesior.pl said:
@up
error fixed [priv msg]

Cleanup database, fast:
1. Shutdown server.
2. Execute in phpmyadmin (change days offline and level) - remove inactive players:
PHP: 
DELETE FROM `players` WHERE `lastlogin` < (UNIX_TIMESTAMP() - (3 * 24 * 3600)) AND `level` < 20 AND `account_id` != 1;
DELETE FROM `players` WHERE `lastlogin` < (UNIX_TIMESTAMP() - (5 * 24 * 3600)) AND `level` < 70 AND `account_id` != 1;
DELETE FROM `players` WHERE `lastlogin` < (UNIX_TIMESTAMP() - (7 * 24 * 3600)) AND `level` < 100 AND `account_id` != 1;
DELETE FROM `players` WHERE `lastlogin` < (UNIX_TIMESTAMP() - (14 * 24 * 3600)) AND `level` < 200 AND `account_id` != 1
DELETE FROM `players` WHERE `lastlogin` < (UNIX_TIMESTAMP() - (24 * 24 * 3600)) AND `account_id` != 1
< 20 lvl, 3 days
< 70 lvl, 5 days
...
offline > 24 days, delete, doesn't check level

Before you ask, it will delete items, skills, storage etc.

3. Execute in phpmyadmin - remove players with 0 players on it and 0 premium points:
PHP: 
DELETE `accounts` FROM `accounts` LEFT JOIN `players` ON `accounts`.`id`=`players`.`account_id` WHERE `players`.`account_id` IS NULL AND `accounts`.`premium_points` = 0

4. Start server, it will remove players (deleted) from houses.
Click to expand...

what was the error?
 
This globalevent works in sqlite? Because every time I test, i get an error related to the line "UNIX_TIMESTAMP."
Why is this happening?

THX. (sry for my english ;x)
 
This script do not clean up house for me? it clean up players and that but not house and i get this error spammed
[1:48:10.421] mysql_real_query(): INSERT INTO `tiles` (`id`, `world_id`, `house_
id`, `x`, `y`, `z`) VALUES (494, 0, 1289, 1026, 924, 7) - MYSQL ERROR: Cannot ad
d or update a child row: a foreign key constraint fails (`tibia`.`tiles`, CONSTR
AINT `tiles_ibfk_1` FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses` (`i
d`, `world_id`) ON DELETE CASCADE) (1452)
Click to expand...
 
Duvbo said:
This script do not clean up house for me? it clean up players and that but not house and i get this error spammed
Click to expand...

make sure you don't have the house tables in the config. The script will take care of the houses internally rather than directly from the database.

I have been using this script on 0.3.7 revs for months and had no issues.
 
Just updated it fixing a bug that was removing players that were just created on startup.

Please consider updating if you use this and have not already fixed this! :D

in this query:
Code: 
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 ..";")

add to the end before the ; this:
Code: 
[B][COLOR="#008000"]AND `lastlogin` > 0[/COLOR][/B]

Code: 
------------------------------------------------------------------------------*
----- [[> 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 .." [B][COLOR="#008000"]AND `lastlogin` > 0[/COLOR][/B];")
		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
 
Hey need a little help this and the script 1.1 is showing the same error here:

[18:34:25.942] [Error - GlobalEvent Interface]
[18:34:25.942] data/globalevents/scripts/playerclean.lua:eek:nStartup
[18:34:25.942] Description:
[18:34:25.942] data/globalevents/scripts/playerclean.lua:123: attempt to concatenate field 'time' (a nil value)
[18:34:25.942] stack traceback:
[18:34:25.942] data/globalevents/scripts/playerclean.lua:123: in function <data/globalevents/scripts/playerclean.lua:76>
Click to expand...

Using Ubuntu 12.04.5 LTS
I will attach my .lua script because i make a comment in script 1.1, the file contain the 2 scripts, to help with lines :)
Amazing script, so useful!
 

Attachments

@chiitus
Change Line 123
Code: 
result = db.getResult("SELECT `id`,`name`,`account_id` FROM `players` WHERE `level` < ".. cleanup.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.time .." AND `lastlogin` > 0;")

To
Code: 
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;")
 
@HeberPcL
I did it and this error appears:

[8:19:59.252] [Error - GlobalEvent Interface]
[8:19:59.252] data/globalevents/scripts/playerclean.lua:eek:nStartup
[8:19:59.252] Description:
[8:19:59.252] data/globalevents/scripts/playerclean.lua:56: attempt to concatenate local 'field' (a nil value)
[8:19:59.252] stack traceback:
[8:19:59.252] data/globalevents/scripts/playerclean.lua:56: in function 'countRowsWhereInTable'
[8:19:59.252] data/globalevents/scripts/playerclean.lua:171: in function <data/globalevents/scripts/playerclean.lua:76>
Click to expand...

EDIT: delete only player, not account.
 
Last edited:
@chiitus
Change Line 171
Code: 
db.executeQuery("DELETE FROM " .. childAttributeTables.accounts.table .. " WHERE " .. childAttributeTables.accounts.idField .. " = '" .. aid[acc] .. "';")

To
Code: 
db.executeQuery("DELETE FROM " .. childAttributeTables.accounts[i].table .. " WHERE " .. childAttributeTables.accounts[i].idField .. " = '" .. aid[acc] .. "';")
 
Holy shit, i'm so idiot, now i can see the error, the fucking same stupid error like before :D

EDIT: I tried and:

[13:13:09.025] [Error - GlobalEvent Interface]
[13:13:09.025] data/globalevents/scripts/playerclean.lua:eek:nStartup
[13:13:09.025] Description:
[13:13:09.025] data/globalevents/scripts/playerclean.lua:56: attempt to concatenate local 'field' (a nil value)
[13:13:09.025] stack traceback:
[13:13:09.025] data/globalevents/scripts/playerclean.lua:56: in function 'countRowsWhereInTable'
[13:13:09.025] data/globalevents/scripts/playerclean.lua:171: in function <data/globalevents/scripts/playerclean.lua:76>
Click to expand...
 
Last edited:
@chiitus
Change:
Code: 
dropCount.accounts = ((dropCount.accounts or 0) + countRowsWhereInTable(childAttributeTables.accounts.table, childAttributeTables.accounts.idField, aid[acc]))

To:
Code: 
dropCount.accounts = ((dropCount.accounts or 0) + countRowsWhereInTable(childAttributeTables.accounts[i].table, childAttributeTables.accounts[i].idField, aid[acc]))
 
Thanks!
I tried and:

[14:52:06.590] [Error - GlobalEvent Interface]
[14:52:06.590] data/globalevents/scripts/playerclean.lua:eek:nStartup
[14:52:06.590] Description:
[14:52:06.590] data/globalevents/scripts/playerclean.lua:171: attempt to perform arithmetic on a table value
[14:52:06.590] stack traceback:
[14:52:06.590] data/globalevents/scripts/playerclean.lua:171: in function <data/globalevents/scripts/playerclean.lua:76>
Click to expand...

I think the script is not using the table configs correctly! But i can't see the error.
 
Code: 
dropCount.accounts[i] = ((dropCount.accounts[i] or 0) + countRowsWhereInTable(childAttributeTables.accounts[i].table, childAttributeTables.accounts[i].idField, aid[acc]))

Here you can find the correct lines
http://otland.net/threads/2-0-datab...a-skills-items-etc.127320/page-4#post-1461489

All the [ i ] where removed because of the missing Lua tags, in that post he used code tags, so the [ i ] are still added there.
 
Limos said:
Code: 
dropCount.accounts[i] = ((dropCount.accounts[i] or 0) + countRowsWhereInTable(childAttributeTables.accounts[i].table, childAttributeTables.accounts[i].idField, aid[acc]))

Here you can find the correct lines
http://otland.net/threads/2-0-datab...a-skills-items-etc.127320/page-4#post-1461489

All the [ i ] where removed because of the missing Lua tags, in that post he used code tags, so the [ i ] are still added there.
Click to expand...

Thanks!!
I will try and post the result.

EDIT: Really thanks, this script is so usefull!! I tried with player already own a house and no problem when it got deleted. Perfect script!
Thanks for the fix! You and the script owner.

EDIT2: just a observation, when the server go ups and delete some accounts, you got some erros trying to /save, erros from houses saves like i said in first EDIT. But after you close and open again the TFS, you got no erros anymore. Someone think this can cause some troubles in server?
 
Last edited:

Similar threads

Gesior.pl
GlobalEvent [TFS 1.x] Power Gamers and Online Time for Gesior2012/MyAAC
Replies
1
Views
127
netbe
N
potinho
CreatureEvent [OTX 2] Global Drop Item
Replies
1
Views
432
Forkz
Forkz
WikiArk21
  • Question
Error MySql
Replies
0
Views
229
WikiArk21
WikiArk21
darkangel1
  • Question
Lua Count items in chest [HELP]
Replies
10
Views
534
Mateus Robeerto
Mateus Robeerto
alejandro762
  • Question
Bag allowing certain items , similar (Gold Pouch)
Replies
1
Views
330
alejandro762
alejandro762
Czorcik
  • Question
Highscore reborn system in acc maker
Replies
0
Views
267
Czorcik
Czorcik
Back
Top