Bug 80: (Lokked) MySQL Server has gone away

[BugTracker]

Bug ID       : 80 - MySQL Server has gone away 
Bug Date     : 2015/01/11 11:45:06
Assigned To  : Lokked
Priority     : Immediate

Category     : WorldServer
Sub-Category : 
Severity     : Severe (e.g. Exploitable)
Reproducible : Some of the time

Details:
This happens more often than I am comfortable with. Nothing at all appears corrupted with my internal network connections; all VMs that run VG and MySQL and the Web sites are all on the same ESX host (ie., no copper) and yet occasionally the world just suddenly experiences a fatal loss of connectivity with the MySQL server.

Details to follow in forum posting.

Originated From Forum: via BugTracker

[John Adams]

I was just online with 3 other players, literally all sitting idle chatting. Out of the blue, this happened:

[quote]11:43:20.985 E Database Error running MySQL query (2013): Lost connection to MySQL server during query
INSERT INTO `character_details` (`char_id_fk`,`title_show`,`title_prefix`,`title_suffix`) VALUES (782, 1, 600, 601)
11:43:20.985 E Database Error running MySQL query (2006): MySQL server has gone away
DELETE FROM `characters` WHERE `character_id`=782
11:43:27.583 E Database Error running MySQL query (2006): MySQL server has gone away
INSERT IGNORE INTO `character_items` (`char_id_fk`,`item_id_fk`, `bag_id`, `current_slot`, `current_equip_slot`, `durability`, `unique_id`)
VALUES (366, 1432478, 4294967295, 11, 4294967295, 100, 27);
11:43:27.583 E Database WorldDatabase::AddCharacterItem: Unable to add item 16509 unique_id 27 to table
11:43:47.111 D Chat [NewTelon] John: MySQL server just took a crap, world is going to crash. Sorry
11:43:52.773 E Database Error running MySQL query (2006): MySQL server has gone away
INSERT IGNORE INTO `character_items` (`char_id_fk`,`item_id_fk`, `bag_id`, `current_slot`, `current_equip_slot`, `durability`, `unique_id`)
VALUES (366, 1432295, 4294967295, 12, 4294967295, 100, 28);
11:43:52.773 E Database WorldDatabase::AddCharacterItem: Unable to add item 9515 unique_id 28 to table
11:43:58.341 E Database Error running MySQL query (2006): MySQL server has gone away
INSERT IGNORE INTO `character_items` (`char_id_fk`,`item_id_fk`, `bag_id`, `current_slot`, `current_equip_slot`, `durability`, `unique_id`)
VALUES (366, 1465664, 4294967295, 13, 4294967295, 100, 29);
11:43:58.341 E Database WorldDatabase::AddCharacterItem: Unable to add item 9175 unique_id 29 to table
11:44:04.222 E Database Error running MySQL query (2006): MySQL server has gone away
INSERT IGNORE INTO `character_items` (`char_id_fk`,`item_id_fk`, `bag_id`, `current_slot`, `current_equip_slot`, `durability`, `unique_id`)
VALUES (366, 1432316, 4294967295, 14, 4294967295, 100, 30);
11:44:04.222 E Database WorldDatabase::AddCharacterItem: Unable to add item 9470 unique_id 30 to table
11:44:10.195 D Chat [NewTelon] Meemers: np =)
11:44:15.530 D Chunk Camp started for character 'Black'
11:44:16.715 D Chat [NewTelon] John: brb!
11:44:17.511 E Database Error running MySQL query (2006): MySQL server has gone away
INSERT IGNORE INTO `character_items` (`char_id_fk`,`item_id_fk`, `bag_id`, `current_slot`, `current_equip_slot`, `durability`, `unique_id`)
VALUES (366, 1432477, 4294967295, 15, 4294967295, 100, 31);
11:44:20.053 E Database WorldDatabase::AddCharacterItem: Unable to add item 5781 unique_id 31 to table
11:44:20.708 E Database Error running MySQL query (2006): MySQL server has gone away
UPDATE `character_items`
SET `item_id_fk` = 1432477, `bag_id` = 4294967295, `current_slot` = 15, `current_equip_slot` = 4, `durability` = 100
WHERE char_id_fk = 366 AND unique_id = 31

11:44:20.708 E Database WorldDatabase::UpdateCharacterItem: Unable to update item unique_id=31 in table
11:44:23.906 D Chunk Camp started for character 'Meemers'[/quote]

I will do some research today on this messaging, but I want to also add this does not just happen on Items-related updates. I see it on nearly every type of routine db updates over the last few months. Fortunately, it's very rare, but we still need to teach WorldServer how to recover. Foof added a heartbeat/mysql_ping() long ago, maybe that ping needs to be more frequent and add self-healing to the connection.

I am also not sure if we're shutting down and freeing our resources properly at all, but something else to look into.