Possible Bug - Players unable to delete characters properly

[antiroot]

Mangos Version: 11050 - 11055

Custom Patches: AHBot, Some of my own (unrelated to logins), Old version of ACE

SD2 Version: 1953

Database Name and Version : UDB 396

OS: FreeBSD 7.2 (not sure if this is relevant, but this is why i'm using old ACE)

How it SHOULD work:

Deleting a character from game should delete it from db and memory and allow new character creation

How it DOES work:

Seemed to only delete from database, characters still appeared in console .account characters $accoutname

A tester had reached the 10 character limit, attempted to delete one, character was removed from list, upon creating new character dialog stated they had already reached the limit

restarted mangos - same error

restarted mangos+mysql - same error

used .character erase $charname - tester was able to create new character, however when trying to delete the newly created character client would hang at "deleting character" message and not proceed

updated from11050 to 11055 - same issues

used an alternate mysql conf.. my-medium.cf + transaction-isolation = READ-COMMITTED

restarted mysql+mangos - issues seem to be resolved

prior to switching to the new conf mysql was using whatever its defaults are (apparently i didn't fine tune it on this install)

I don't know if this really a bug report or more of just wanting developers to know something unexpected happened

If nobody else has run into this and its just me, please close this, as the issues are now resolved on my end

[antiroot]

Ha sorry guys, i should have read the full commit info of 11045

still had autocommits on, and it was causing record locks apparently

Still doesn't explain how i got the above issues in the order I did, but this is no longer a bug report and can be closed

[Ambal]

Still had autocommits on, and it was causing record locks apparently

Hi, antiroot. I'll very appreciate if you can explain what issues with 'autocommit' you had as well as did you actually changed 'transaction-isolation' level in config? This mode should be automatically disabled for every single connection we establish with MySQL now. We use BEGIN TRANSACTION - COMMIT instead.

'Autocommit' was enabled by-default in previous DB layer and if it took you to update your config in order to fix your issues - we need to know about that.

Cheers.

[antiroot]

I only added

transaction-isolation = READ-COMMITTED

to my mysql configuration and everything was fine afterward, at this time autocommit is still at its default value (on)

I'll try and remove the config and replicate the issues

If I'm able to successfully repeat the bug, then I'll raise logging levels and gather some information from mysql/mangos and what they are doing

Unfortunately i have most of my mangos related log files set to a low value, what i can conclude is that Char.log correctly stated new characters were created, but when the user tried to delete them this never showed in the log

[Ambal]

Thank you, antiroot.

Please, let me know if you'll be able to reproduce this issue again with default settings so I can take a look and help you. We are not interested in the fact that users have to change configs of their MySQL servers in order to keep up with the latest changes in the Core.

Cheers.

[antiroot]

ok reverted my.cnf back to original my-medium.cnf - issues came back right away

2011-01-21 10:20:07 Player: channels cleaned up!
2011-01-21 10:20:07 SQL: START TRANSACTION
2011-01-21 10:20:07 [1 ms] SQL: DELETE FROM characters WHERE guid = '223'
2011-01-21 10:20:07 [2 ms] SQL: INSERT INTO characters (guid,account,name,race,class,gender,level,xp,money,playerBytes,playerBytes2,playerFlags,map, dungeon_difficulty, position_x, position_y, position_z, orientation, taximask, online, cinematic, totaltime, leveltime, rest_bonus, logout_time, is_logout_resting, resettalents_cost, resettalents_time, trans_x, trans_y, trans_z, trans_o, transguid, extra_flags, stable_slots, at_login, zone, death_expire_time, taxi_path, arenaPoints, totalHonorPoints, todayHonorPoints, yesterdayHonorPoints, totalKills, todayKills, yesterdayKills, chosenTitle, knownCurrencies, watchedFaction, drunk, health, power1, power2, power3, power4, power5, power6, power7, specCount, activeSpec, exploredZones, equipmentCache, ammoId, knownTitles, actionBars) VALUES
(223, 5, 'Oncis', 11, 3, 0, 1, 0, 0, 100926977, 33554437, 0, 530, 0, -3961.64, -13931.2, 100.615, 2.08364, '0 0 536870912 8 0 0 0 0 0 0 0 0 0 0 ', 0, 0, 0, 0, 0, 1295626807, 0, 0, 0, 0, 0, 0, 0, 0, 2, 0, 32, 0, 0, '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 4294967295, 0, 66,100,0,0,100,0,0,0, 1, 0, '0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ', '0 0 0 0 0 0 23345 0 0 0 0 0 23344 0 23348 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 12282 0 0 0 23347 0 0 0 ',2512, '0 0 0 0 0 0 ',0)
2011-01-21 10:20:07 [3 ms] SQL: INSERT INTO character_inventory (guid,bag,slot,item,item_template) VALUES
('223', '0', '3', '5366502', '23345')
2011-01-21 10:20:07 SQL: START TRANSACTION

Theres a bit much there but, the first part of the transaction is saying delete character guid:223, but then directly after we have INSERT (guid:223)

also there is no commit before the start of the next transaction (all other transactions in log begin with start and end with commit), mysql show processlist shows nothing happening

after creating some characters the only way to delete them again is to restart mangos, however

create 10 characters, restart mangos, delete a character, create 1 new character - results in character limit message

looking at the logs when the character delete is requested a transaction is started, character is deleted from all tables then committed, then

SELECT COUNT(guid) FROM characters WHERE account = '5'

is executed, which should be 9 after deleting 1 of 10, but client must still be getting 10

restarting mangos again after deleting 1 of 10 allows a new character to be created again..

makes me think some commits are being missed and also that some queries are just in the wrong order

EDIT: othertimes there is a start transaction and then multiple nested ones below, each with a commit.. so if they are being pooled correctly i would assume they should be successful, but something is still not being committed correctly resulting in the record being locked and things just not working correctly

I do see bug reports at MySQL for similar issues with completely unrelated projects/applications and they say to upgrade mysql, however i'm running a higher version than what the bug reports say to upgrade to

[Ambal]

Antiroot, is this the SQL logs mangos is writing ("LogSQL = true")? If so - in this log we protocol all queries from multiple databases at once. So multiple START TRANSACTION records are expected there.

These SQL requests you've showed to us are from Player::SaveToDB() function - they are not connected to character account deletion. Are you removing character from mangos command line?

[antiroot]

OH very sorry ambal, i meant to hit reply, but hit report by mistake

short answer, the logs are from Server.log (mangos log), and trying to delete characters from client only

console command does delete character, and occurs in 3 insert/delete/update steps and 1 select

start -> delete petitions - > commit

start -> delete characters - > commit

select realmcharacters

start -> delete rleamcharacters, insert realmcharacters - > commit

and sorry again about the report, hopefully a mod/admin can undo my thoughtless action

[Ambal]

Oh, so this is the problem only when you are trying to delete account from the client? So you get notification about successful character deletion and when you try to create more chars - you fail because server thinks that you still have tonns of chars? And if you delete char from mangos command line you can create characters w/o problems?

Try to do this:

1) start your client and login

2) delete your character from client

3) wait for one minute before creating new character

4) try to create an account and report to us if this operation succeeded or not.

[antiroot]

well strangely it worked fine this time to create the player..

however i still can't delete a character that was created since the running mangos process was started

Pressing "Delete Character" from client, typing DELETE like it requires, and submitting just results in a hang, it is not frozen or crashed, just not processing anything about the delete.. this works fine for characters that were created before the current mangos process was started however

EDIT: still had old instance of mysql running with non-default config, but didn't delete this post since Ambal already quoted me in a later post

[antiroot]

Spoke too soon..

created total of 10 characters, restarted mangos, deleted 1 character, waited 1 minute - unable to make character

closed client, repeated above - same result (unable to create character)

[Ambal]

well strangely it worked fine this time to create the player..

So you did everything I told you in the previous post and it worked fine? If so then this only means that you get notification about character deletion BEFORE it was actually deleted from the database!

WorldSession::HandleCharDeleteOpcode( WorldPacket & recv_data )
{
....
   Player::DeleteFromDB(guid, GetAccountId()); //<= fully async character deletion!

   //we send opcode right away w/o waiting for character to be removed from DB
   WorldPacket data(SMSG_CHAR_DELETE, 1);
   data << (uint8)CHAR_DELETE_SUCCESS;
   SendPacket( &data );
}

UPDATE: ok, I'll try to reproduce this issue today with default transaction isolation settings for DB.

however i still can't delete a character that was created since the running mangos process was started

Need more information about this issue: what is the workflow to reproduce this issue?

[kero99]

Hmmmm rare, i dont have this issue O_o, mysql config problem?

[antiroot]

its a default config using my-medium.cf

currently mysql 5.1.48, going to update to 5.1.54 to see if its a mysql bug and not mangos (hopefully this is the case, will report back after update)

notes about server in case some how relevant

freebsd 7.2 32bit, single core, 2gb ram

mangos runs within a freebsd jail, mysql is installed in the same jail

Edit: had same issue before using my-medium.cf config, using just mysql defaults and no my.cnf file

[Undergarun]

Hmmmm rare, i dont have this issue O_o, mysql config problem?

We have transaction-isolation = READ-COMMITTED in our my.cnf!!!

[antiroot]

ok updated mysql to newest version in freebsd ports tree

both issues still present with default configs, so not a mysql bug (at least not one that has been reported and fixed if it is)

max character dialogue occurs after these steps

1) login, create 10 characters

2) logout/close client

3) restart mangos

4) login, delete 1 characters

5) create 1 character - max limit reached message here

newly created characters can't be deleted

1) login to account with less than 10 characters

2) create character

3) delete character (even after waiting 1 minute or more) - "Deleting Character" message hangs and never deletes character

[antiroot]

correct i added "transaction-isolation = READ-COMMITTED" before making this report and it was working.

however transaction-isolation = READ-COMMITTED is not a default mysql config, so in order to run mangos it requires you to reconfigure mysql in order for it to work

Please, let me know if you'll be able to reproduce this issue again with default settings so I can take a look and help you. We are not interested in the fact that users have to change configs of their MySQL servers in order to keep up with the latest changes in the Core.

From what Ambal said, i continued with this report because it does not work correctly for me with Default mysql confs, and requires reconfigure of non-mangos configurations

[Undergarun]

correct i added "transaction-isolation = READ-COMMITTED" before making this report and it was working.

however transaction-isolation = READ-COMMITTED is not a default mysql config, so in order to run mangos it requires you to reconfigure mysql in order for it to work

Please, let me know if you'll be able to reproduce this issue again with default settings so I can take a look and help you. We are not interested in the fact that users have to change configs of their MySQL servers in order to keep up with the latest changes in the Core.

From what Ambal said, i continued with this report because it does not work correctly for me with Default mysql confs, and requires reconfigure of non-mangos configurations

Of course, go ahead with the tests and feedback ;-)

[Ambal]

The real cause of your problems is disabled by me 'autocommit' MySQL mode. Fix submitted in [11061]. UPDATE URGENTLY!!!

P.S. this feature behaves so strangely that now I become to be scared by MySQL... W/o it turned on you should wrap even SELECTs into transactions to be sure that they will really get any results to you.

Cheers.

[Diablox]

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.

From http://dev.mysql.com/doc/refman/5.1/en/commit.html

I don't see a reason to turn autocommit off.

@antiroot

Do you use CharDelete.Method = 1? Just curios.

[Ambal]

I don't see a reason to turn autocommit off.

Well, thing is that MySQL 'autocommit' mode turns ANY sort of DB request into single transaction if you do not specifically create transaction by START TRANSACTION request. So for SELECTs it was stated as overkill BUT noone from MySQL team said that SELECTs will return you empty resultsets if you will not wrap your SELECT into transaction - this happens with REPEATABLE_READ (default isolation mode for InnoDB)!!! With READ_COMMITTED it returns data but we should better keep this option enabled for our safety.

[antiroot]

Thank soo much Ambal, works as expected now with default mysql configs.. I need to spend some time working with innodb and transactions, I really didn't have enough experience to quickly track down what was happening, now if this were myisam my years of experience with that would have been helpful.

At any rate i believe this thread can be closed since the latest commit Ambal pushed resolve the problem