query on two DBs and realmd doubt

[karlos]

Hi all. I'm trying to make a patch to delete accounts not used for a period of time. I have the accounts part done but have a problem with characters without account associated. Is possible to do this query directly in mangos code?

select guid from characters.characters where account not in (select id from realmd.account);

of course, characters and realmd are the names I've got in my confs files , but I want take it from mangos.conf and not hardcode then in the core.

If this is not possible, I suppose I must pass two querys through loginDatabase.PQuery() and charactersDatabase.PQuery() but I don't Know how to process the first result into the second function.

My doubt with realmd main function is about this querys

    //set expired bans to inactive
   loginDatabase.Execute("UPDATE account_banned SET active = 0 WHERE unbandate<=UNIX_TIMESTAMP() AND unbandate<>bandate");
   loginDatabase.Execute("DELETE FROM ip_banned WHERE unbandate<=UNIX_TIMESTAMP() AND unbandate<>bandate");

Are this executed only at start or they run every ping check? if not , is possible to include in the ping loop?

I must say i'm not programmer and i trying to learn c++ through mangos project and reading (a lot of... ) manuals as I did to learn about mysql or linux or git .It's my first approach to the code but I use Mangos for more than 3 years.

Thanks in advance, I post patch here when its run correctly

[mrhooha]

MiniManager for mangos can do all of this and more. http://mangos.osh.nu/forums/index.php?showtopic=5 Try this link.

[karlos]

yes I know, but I'm trying to do in the code to learn

[DasBlub]

you can't select over two databases as it isn't guaranteed that they're running on the same host / the same mysql daemon and that the db-user has enough rights on both db's.

[karlos]

thank you DasBlub, this is the problem.

And then how to pass the result of first query

select id from account;

into the second query ?

select guid from characters where account not in (result of the first query);

is there any function in mangos that converts the result into a list with commas as second query needs? or needs a bucle for convert the result to a string that I can pass as parameter

please be patient with me, I'm trying to understand how mangos work with MySQL

[karlos]

well, I finish the patch:

diff --git a/src/game/ObjectMgr.cpp b/src/game/ObjectMgr.cpp
index b887961..d62cf43 100644
--- a/src/game/ObjectMgr.cpp
+++ b/src/game/ObjectMgr.cpp
@@ -6299,6 +6299,71 @@ uint32 ObjectMgr::GeneratePetNumber()
    return ++m_hiPetNumber;
}

+// Delete characters not asociated to any account
+void ObjectMgr::DeleteOrphanCharacters()
+{
+    // List of accounts present in the login database
+    QueryResult *result_acc = loginDatabase.PQuery("SELECT id FROM account");
+    if(result_acc)
+    {
+        std::ostringstream char_sql;
+        int count = result_acc->GetRowCount() - 1;
+
+        // Build query to detect characters without account
+        char_sql << "SELECT guid FROM characters WHERE account NOT IN (";
+
+        for ( int i = 0; i <= count; i++ )
+        {
+            Field *fields = result_acc->Fetch();
+            uint32 accid = fields[0].GetUInt32();
+            char_sql << accid;
+            if (i == count)
+                {
+                    char_sql << ")";
+                }
+                else
+                {
+                    char_sql << ",";
+                    result_acc->NextRow();
+                }
+        }
+
+        // free result
+        delete result_acc;
+
+        // List of characters without account
+        QueryResult *result_char = CharacterDatabase.PQuery("%s", char_sql.str().c_str() );
+        if (result_char)
+        {
+            barGoLink bar( result_char->GetRowCount() );
+            do
+            {
+                bar.step();
+
+                Field *fields = result_char->Fetch();
+                uint32 guidlo = fields[0].GetUInt32();
+                uint64 guid = MAKE_NEW_GUID(guidlo, 0, HIGHGUID_PLAYER);
+
+                // kick if player currently
+                ObjectAccessor::KickPlayer(guid);
+                Player::DeleteFromDB(guid, 0, false);       // no need to update realm characters
+            } while (result_char->NextRow());
+
+            sLog.outString();
+            sLog.outString("%d Characters deleted due to expired accounts", result_char->GetRowCount() );
+            sLog.outString();
+
+            // free result
+            delete result_char;
+        }
+        else
+        {
+        sLog.outString("There's no orphan characters to be deleted");
+        sLog.outString();
+        }
+    }
+}
+
void ObjectMgr::LoadCorpses()
{
    uint32 count = 0;
diff --git a/src/game/ObjectMgr.h b/src/game/ObjectMgr.h
index 4442f56..0f3e58d 100644
--- a/src/game/ObjectMgr.h
+++ b/src/game/ObjectMgr.h
@@ -549,6 +549,8 @@ class ObjectMgr
            return NULL;
        }

+        void DeleteOrphanCharacters();
+
        void LoadGuilds();
        void LoadArenaTeams();
        void LoadGroups();
diff --git a/src/game/World.cpp b/src/game/World.cpp
index 7516899..a416178 100644
--- a/src/game/World.cpp
+++ b/src/game/World.cpp
@@ -892,6 +892,15 @@ void World::SetInitialWorldSettings()
    uint32 realm_zone = getConfig(CONFIG_UINT32_REALM_ZONE);
    loginDatabase.PExecute("UPDATE realmlist SET icon = %u, timezone = %u WHERE id = '%d'", server_type, realm_zone, realmID);

+    ///- Delete characters not asociated to any account
+    uint8 orphan = sConfig.GetIntDefault( "AccountExpire.DeleteOrphanCharacters", 0 );
+    if (orphan != 0)
+    {
+        sLog.outString();
+        sLog.outString("Removing characters not asociated to any accounts...");
+        sObjectMgr.DeleteOrphanCharacters();
+    }
+
    ///- Remove the bones after a restart
    CharacterDatabase.PExecute("DELETE FROM corpse WHERE corpse_type = '0'");

diff --git a/src/mangosd/mangosd.conf.dist.in b/src/mangosd/mangosd.conf.dist.in
index 8cfc258..8a56dbd 100644
--- a/src/mangosd/mangosd.conf.dist.in
+++ b/src/mangosd/mangosd.conf.dist.in
@@ -159,6 +159,11 @@ BindIP = "0.0.0.0"
#        Default: 1 (permit addon channel)
#                 0 (do not permit addon channel)
#
+#    AccountExpire.DeleteOrphanCharacters
+#        Delete characters not asociated to any account at server start
+#        Default: 0 (false)
+#                 1 (true)
+#
###################################################################################################################

UseProcessors = 0
@@ -182,6 +187,7 @@ TargetPosRecalculateRange = 1.5
UpdateUptimeInterval = 10
MaxCoreStuckTime = 0
AddonChannel = 1
+AccountExpire.DeleteOrphanCharacters = 0

###################################################################################################################
# SERVER LOGGING
diff --git a/src/realmd/Main.cpp b/src/realmd/Main.cpp
index 84f9ec0..0915810 100644
--- a/src/realmd/Main.cpp
+++ b/src/realmd/Main.cpp
@@ -209,11 +209,6 @@ extern int main(int argc, char **argv)
        return 1;
    }

-    // cleanup query
-    //set expired bans to inactive
-    loginDatabase.Execute("UPDATE account_banned SET active = 0 WHERE unbandate<=UNIX_TIMESTAMP() AND unbandate<>bandate");
-    loginDatabase.Execute("DELETE FROM ip_banned WHERE unbandate<=UNIX_TIMESTAMP() AND unbandate<>bandate");
-
    h.Add(&authListenSocket);

    ///- Catch termination signals
@@ -275,8 +270,37 @@ extern int main(int argc, char **argv)
        if( (++loopCounter) == numLoops )
        {
            loopCounter = 0;
-            sLog.outDetail("Ping MySQL to keep connection alive");
-            delete loginDatabase.Query("SELECT 1 FROM realmlist LIMIT 1");
+            //sLog.outDetail("Ping MySQL to keep connection alive");
+            //delete loginDatabase.Query("SELECT 1 FROM realmlist LIMIT 1");
+            sLog.outDetail("Cleanup accounts...");
+            // cleanup query
+            // clean outdated accounts
+            uint8 acc_age = sConfig.GetIntDefault( "AccountExpire.DaysOld", 0 );
+            if (acc_age != 0)
+            {
+                uint8 acc_creation = sConfig.GetIntDefault( "AccountExpire.JoindateDiff", 7 );
+                sLog.outDetail("Deleting accounts not used for %d days and created more than %d days ago", acc_age, acc_creation );
+
+                QueryResult *acc_del =loginDatabase.PQuery( "SELECT count(*) FROM account WHERE gmlevel = 0 AND joindate < DATE_SUB(CURDATE(),INTERVAL %d DAY) AND last_login < DATE_SUB(CURDATE(),INTERVAL '%d' DAY)", acc_creation, acc_age );
+
+                loginDatabase.PQuery( "DELETE FROM account WHERE gmlevel = 0 AND joindate < DATE_SUB(CURDATE(),INTERVAL %d DAY) AND last_login < DATE_SUB(CURDATE(),INTERVAL '%d' DAY)", acc_creation, acc_age );
+                loginDatabase.Execute( "DELETE FROM account_banned WHERE id NOT IN (SELECT id FROM account)" );
+                loginDatabase.Execute( "DELETE FROM realmcharacters WHERE acctid NOT IN (SELECT id FROM account)" );
+
+                Field *fields = acc_del->Fetch();
+                uint32 acc_count = fields[0].GetUInt32();
+                sLog.outDetail("Deleted %d accounts", acc_count );
+                delete acc_del;
+            }
+            else
+            {
+                sLog.outDetail("Old account cleanup deactivated");
+            }
+            //set expired bans to inactive
+            loginDatabase.Execute("UPDATE account_banned SET active = 0 WHERE unbandate<=UNIX_TIMESTAMP() AND unbandate<>bandate");
+            loginDatabase.Execute("DELETE FROM ip_banned WHERE unbandate<=UNIX_TIMESTAMP() AND unbandate<>bandate");
+            sLog.outDetail("Cleanup done...");
+
        }
#ifdef WIN32
        if (m_ServiceStatus == 0) stopEvent = true;
diff --git a/src/realmd/realmd.conf.dist.in b/src/realmd/realmd.conf.dist.in
index 217cb15..95d9959 100644
--- a/src/realmd/realmd.conf.dist.in
+++ b/src/realmd/realmd.conf.dist.in
@@ -94,6 +94,15 @@ ConfVersion=2007062001
#        Default: 0 (Ban IP)
#                 1 (Ban Account)
#
+#    AccountExpireTime
+#        Delete accounts no used for a period of time in days
+#        Default: 0 (no delete)
+#
+#    AccountExpire.JoindateDiff
+#        Number of days to leave recently created accounts
+#        in the database before can be deleted
+#        Default: 7
+#
###################################################################################################################

LoginDatabaseInfo = "127.0.0.1;3306;root;mangos;realmd"
@@ -114,3 +123,5 @@ RealmsStateUpdateDelay = 20
WrongPass.MaxCount = 0
WrongPass.BanTime = 600
WrongPass.BanType = 0
+AccountExpireTime = 0
+AccountExpire.JoindateDiff = 7
\\ No newline at end of file
diff --git a/src/shared/Database/Database.h b/src/shared/Database/Database.h
index a6dc98d..8ab96e8 100644
--- a/src/shared/Database/Database.h
+++ b/src/shared/Database/Database.h
@@ -30,7 +30,7 @@ class SqlQueryHolder;
typedef UNORDERED_MAP<ACE_Based::Thread* , SqlTransaction*> TransactionQueues;
typedef UNORDERED_MAP<ACE_Based::Thread* , SqlResultQueue*> QueryQueues;

-#define MAX_QUERY_LEN   32*1024
+#define MAX_QUERY_LEN   64*1024

class MANGOS_DLL_SPEC Database
{

but I have a doubt. Is there any problem with this?

-#define MAX_QUERY_LEN   32*1024
+#define MAX_QUERY_LEN   64*1024

in a db very populated like mine this limit is too short for the second query. How do this limit affect to the rest of code?

it works very well in debian 64 but I don't know in other systems. Tested without players online

this patch is for rev 9441.

[Metatron]

/edit: ~please ignore my post~ :cool:

[cyrex]

Wow excellent idea. I normally clean my DB of old characters every 60 days. But this a nice idea to control the amount of players and guid that actually play.