[devRev4] CharServer do not delete items database.

Welcome in the osRose emulator Project.

Moderators: osRose dev team, ospRose dev team, osiRose dev team, Moderators

[devRev4] CharServer do not delete items database.

Postby kismetbecomes on Fri Apr 26, 2013 6:27 pm

Hello, good day OsRose. Well its the weekend again and working with the RoseZa client/devrev4 continues.

The testers have been reporting bugs when logging in- All of them deleted a character and created a new one.

I have tested them myself and have concluded that the CharServer doesn't delete the entries/records in the items.sql corresponding to that character to be deleted in characters.sql. And because of that problem, the CharServer hangs for a duplicate entry when the new char logs in. And then something worst happens.

To detail it, the problem is triggered when someone creates a new character - and that NEW character TOOK the ID that was once used. But the problem doesn't end with the CharServer crashing, since items.sql is not cleared from the previous delete, the new character then gets ALL previous items from that ID. :? Sad.

Anyway! I'am looking forward to fixing this with your help. :D

I reviewed CharServer(main) and have found this:

  1.  
  2.  
  3. // delete/resurect character
  4. bool CCharServer::pakDeleteChar( CCharClient* thisclient, CPacket* P )
  5. {
  6.     if(!thisclient->isLoggedIn) return false;
  7.     char* name = (char*)&P->Buffer[2];
  8.  
  9.     if(!CheckEscapeMySQL(name,-1,true))
  10.     {
  11.         Log(MSG_WARNING,"A name (delete / resurrect) contains incorrect caracter (see warnings above)");
  12.         return false;
  13.     }
  14.  
  15.     MYSQL_RES *result;
  16.     MYSQL_ROW row;
  17.     result = DB->QStore("SELECT account_name FROM characters WHERE char_name='%s' LIMIT 1", name);
  18.     if(result==NULL) return false;
  19.     row = mysql_fetch_row(result);
  20.  
  21.     //LMA: no case.
  22.     //if (strcmp(row[0], thisclient->username)!=0)
  23.     if (stricmp(row[0], thisclient->username)!=0)
  24.     {
  25.         Log(MSG_HACK, "User %s tried deleting another users (%s) character.", thisclient->username, name);
  26.         DB->QFree( );
  27.         return false;
  28.     }
  29.     DB->QFree( );
  30.     short int action = GETBYTE((*P), 1 );
  31.     unsigned long int DeleteTime = 0;
  32.     switch(action)
  33.     {
  34.         case 0x00://Resurrect
  35.         {
  36.             DeleteTime = 0;
  37.             //if(!DB->QExecute(" UPDATE characters SET deletetime=0 WHERE char_name='%s'",(char*)&P->Buffer[2] ))
  38.             if(!DB->QExecute(" UPDATE characters SET deletetime=0 WHERE char_name='%s'",name))
  39.             {
  40.                 return false;
  41.             }
  42.  
  43.         }
  44.         break;
  45.         case 0x01://Delete
  46.         {
  47.             DeleteTime = GetServerTime( ) + Config.DeleteTime;
  48.             //if(!DB->QExecute(" UPDATE characters SET deletetime=%i WHERE char_name='%s'",DeleteTime, (char*)&P->Buffer[2] ))
  49.             if(!DB->QExecute(" UPDATE characters SET deletetime=%i WHERE char_name='%s'",DeleteTime,name))
  50.             {
  51.                 return false;
  52.             }
  53.  
  54.         }
  55.         break;
  56.     }
  57.     BEGINPACKET( pak, 0x714 );
  58.     if(DeleteTime > 0 )
  59.     {
  60.         ADDDWORD   ( pak, Config.DeleteTime );
  61.     }
  62.     else
  63.     {
  64.         ADDDWORD   ( pak, 0x00000000 );
  65.     }
  66.     ADDSTRING  ( pak, (char*)&P->Buffer[2] );
  67.     ADDBYTE    ( pak, 0x00 );
  68.     thisclient->SendPacket( &pak );
  69.     return true;
  70. }



Correct me if I'm wrong, should we add a command somewhere below line 45 something like:

  1. DELETE FROM list_item WHERE owner=%s


:lol: (Laughing so hard why I came up with that.. I don't know how to do this I'm really sorry. Just giving an idea, I don't really know how to begin it anyway.)

OR IS THIS A PACKET ISSUE? :(
RoseZa v437 - HueRose Test Project -- http://hueroseonline.no-ip.biz
kismetbecomes
Rackie
Rackie
 
Posts: 299
Joined: Mon Feb 06, 2012 12:41 am

Re: [devRev4] CharServer do not delete items database.

Postby Circa on Fri Apr 26, 2013 9:02 pm

doubt it's a packet issue, because if the character ingame is actually deleted/removed then its good client side, but the problem is related to the server, I'm not much of database/SQL guy, hopefully PY can help or someone else.
Circa
Clown
Clown
 
Posts: 404
Joined: Sun Aug 23, 2009 5:52 am
Location: CA

Re: [devRev4] CharServer do not delete items database.

Postby PurpleYouko on Mon Apr 29, 2013 7:02 pm

The problem is that characters are not immediately deleted. You have a certain length of time ( usually about 24 hours) where you can change your mind and resurrect your character.
This means that the actual deletion doesn't take place in the code that you posted. It just sets the timer.
The actual deletion takes place in function pakGetCharacters in charpackets.cpp
  1.  
  2. bool CCharServer::pakGetCharacters( CCharClient* thisclient, CPacket* P )
  3. {
  4.     if (!thisclient->isLoggedIn) return false;
  5.     if(!DB->QExecute( "DELETE FROM characters WHERE deletetime>0 AND deletetime<=%i",GetServerTime() ))
  6.         return false;
  7.     MYSQL_RES *result;
  8.     MYSQL_ROW row;


you are absolutely correct that it is missing a query to remove the items owned by that character
Where at the moment it calls the deletion query within the conditional it doesn't leave room for also deleting the other stuff.
What we need to do is to rewrite the conditional then expand it a little to include queries to delete items, skills and possibly some other stuff too.
Trouble is after running the first query there's nothing left to select in order to delete the rest of the stuff.
We probably need to break the whole lot out into a new boolean function and return true or false depending on whether we perform the deletion or not.

Unfortunately I don't have the time to write up a new function this week. I'm crazy busy at work with a visitor from out of the country.
Need to lookup information on NARose items, skills, quests?
Now featuring a newly completed skill tree for all classes
Formatting fixed for different resolutions
Image

"A Gazelle is nothing but a giraffe plotted logarithmicaly"
User avatar
PurpleYouko
Rose Guru
Rose Guru
 
Posts: 4733
Joined: Fri Aug 10, 2007 2:05 pm

Re: [devRev4] CharServer do not delete items database.

Postby kismetbecomes on Mon Apr 29, 2013 8:17 pm

I don't have any idea about adding a new boolean function, but, I might get things around by adding MYSQL delete syntax. That, I cannot promise, I' am new to Php and MySQL as much as I' am new with C++. But, I will try to come up with something and see if I can work something out.

I would still wait for a few weeks until you are not so busy PY, if that's okay. I trust your programming more. :lol:
Thank you.
RoseZa v437 - HueRose Test Project -- http://hueroseonline.no-ip.biz
kismetbecomes
Rackie
Rackie
 
Posts: 299
Joined: Mon Feb 06, 2012 12:41 am

Re: [devRev4] CharServer do not delete items database.

Postby kismetbecomes on Tue May 07, 2013 8:23 pm

Hello Py, I was thinking, is it possible to added a new line after

DELETE FROM characters WHERE deletetime>0 AND deletetime<=%i",GetServerTime()

something like:

DELETE FROM items WHERE owner = thisclient


I hope you got my point. :lol: basically its just another MySQL command.
RoseZa v437 - HueRose Test Project -- http://hueroseonline.no-ip.biz
kismetbecomes
Rackie
Rackie
 
Posts: 299
Joined: Mon Feb 06, 2012 12:41 am

Re: [devRev4] CharServer do not delete items database.

Postby PurpleYouko on Wed May 08, 2013 2:48 pm

yeah something like that.
But......

It would need to be done on the OWNER's character id number in the database rather than on thisclient which is a structure containing everything pertaining to the player.
This ID number is not easily available in the present context.
the whole thing needs to be broken out of the present code so that we can get the details

presently it is called like this
  1. if(!DB->QExecute( "DELETE FROM characters WHERE deletetime>0 AND deletetime<=%i",GetServerTime() ))
  2.         return false;

it runs the query every time and only returns false if there is an error.
It should also be noted that this query will remove EVERY character that has been deleted by it's owner, not just this player's character. ALL of them, no matter who owns them.
The next problem is that the character no longer exists so we have no way to find the correct id to delete his items.

What we need to do is replace the initial query with one that returns all the characters that have been deleted then use their character ids to loop through the list of deletions and perform a series of DELETE queries on the items database.

I'm thinking it should look something like this
  1. bool CCharServer::pakGetCharacters( CCharClient* thisclient, CPacket* P )
  2. {
  3.     if (!thisclient->isLoggedIn) return false;
  4.     //if(!DB->QExecute( "DELETE FROM characters WHERE deletetime>0 AND deletetime<=%i",GetServerTime() ))
  5.     //    return false;
  6.  
  7.     MYSQL_RES *result;
  8.     MYSQL_ROW row;
  9.     result = DB->QStore("SELECT id FROM characters WHERE deletetime>0 AND deletetime<=%i",GetServerTime() );
  10.     if(result==NULL) return false;
  11.     while (row = mysql_fetch_row(result))
  12.     {
  13.         UINT ThisID = atoi(row[0]);
  14.         DB->QExecute( "DELETE FROM items WHERE owner = %i",ThisID);
  15.         DB->QExecute( "DELETE FROM characters WHERE id = %i",ThisID);
  16.     }
  17.     DB->QFree( );
  18.  
  19.     CItem items[20];
  20.     unsigned int charnum=0;
  21.     CCharacter chars[8];

I commented out the original query and added a few new ones.
Note that I had to shift it all down a little so that it is after the definitions or *result and row since I needed to use them.

This code compiles fine and it should run and do what you need but I don't have the capability of testing it right now.
Need to lookup information on NARose items, skills, quests?
Now featuring a newly completed skill tree for all classes
Formatting fixed for different resolutions
Image

"A Gazelle is nothing but a giraffe plotted logarithmicaly"
User avatar
PurpleYouko
Rose Guru
Rose Guru
 
Posts: 4733
Joined: Fri Aug 10, 2007 2:05 pm

Re: [devRev4] CharServer do not delete items database.

Postby kismetbecomes on Wed May 08, 2013 4:56 pm

I get stucked in downloading avatar list after deleting a character and relogging.
(Set the delete time in the conf to : 60secs)


EDIT: All accounts get stucked. Wonder why is that. :)
RoseZa v437 - HueRose Test Project -- http://hueroseonline.no-ip.biz
kismetbecomes
Rackie
Rackie
 
Posts: 299
Joined: Mon Feb 06, 2012 12:41 am

Re: [devRev4] CharServer do not delete items database.

Postby PurpleYouko on Wed May 08, 2013 7:03 pm

LOL

I think I know exactly why :lol:

I put in a standard check to make sure that result is not NULL
  1. if(result==NULL) return false;

It's probably returning false if there are no rows found in the query.
It really shouldn't be though. An empty set should not be equivalent of NULL

anyway try commenting that line out and see what happens
Need to lookup information on NARose items, skills, quests?
Now featuring a newly completed skill tree for all classes
Formatting fixed for different resolutions
Image

"A Gazelle is nothing but a giraffe plotted logarithmicaly"
User avatar
PurpleYouko
Rose Guru
Rose Guru
 
Posts: 4733
Joined: Fri Aug 10, 2007 2:05 pm

Re: [devRev4] CharServer do not delete items database.

Postby kismetbecomes on Wed May 08, 2013 7:33 pm

Still stuck. :(
RoseZa v437 - HueRose Test Project -- http://hueroseonline.no-ip.biz
kismetbecomes
Rackie
Rackie
 
Posts: 299
Joined: Mon Feb 06, 2012 12:41 am

Re: [devRev4] CharServer do not delete items database.

Postby PurpleYouko on Wed May 08, 2013 7:39 pm

aw crap.

I will have to think about that for a while. Right now I have no idea :?
Need to lookup information on NARose items, skills, quests?
Now featuring a newly completed skill tree for all classes
Formatting fixed for different resolutions
Image

"A Gazelle is nothing but a giraffe plotted logarithmicaly"
User avatar
PurpleYouko
Rose Guru
Rose Guru
 
Posts: 4733
Joined: Fri Aug 10, 2007 2:05 pm

Next

Return to Support - OsRose Emulator

Who is online

Users browsing this forum: No registered users and 12 guests

cron