Originally posted by [b]theultramage[/b]
http://www.eathena.ws/board/index.php?autocom=bugtracker&showbug=3304
When deleting a char, the query that's supposed to erase unhatched pets from inventory/cart items.
CODE
if( SQL_ERROR == Sql_Query(sql_handle, "DELETE FROM `%s` WHERE pet_id IN "
"(SELECT card1|card2<<2 FROM `%s` WHERE char_id = '%d' AND card0 = -256"
" UNION"
" SELECT card1|card2<<2 FROM `%s` WHERE char_id = '%d' AND card0 = -256)",
pet_db, inventory_db, char_id, cart_db, char_id) )
Sql_ShowDebug(sql_handle);
has two problems - it runs slow as hell because MySQL5's IN() operation does not use indexes, and it doesn't work because the << 2 bit shift should have been << 16. If the pet id counter gets above 65535, it may even lead to data loss.
Proposed solution:
CODE
if( SQL_ERROR == Sql_Query(sql_handle, "DELETE FROM `%s` USING `%s` JOIN `%s` ON `pet_id` = `card1`|`card2`<<16 WHERE `%s`.char_id = '%d' AND card0 = -256", pet_db, pet_db, inventory_db, inventory_db, char_id)
Sql_ShowDebug(sql_handle);
if( SQL_ERROR == Sql_Query(sql_handle, "DELETE FROM `%s` USING `%s` JOIN `%s` ON `pet_id` = `card1`|`card2`<<16 WHERE `%s`.char_id = '%d' AND card0 = -256", pet_db, pet_db, cart_db, cart_db, char_id)
Sql_ShowDebug(sql_handle);
I'm not sure what to do about all the stale data that's already there... hopefully it won't cause performance problems.
This post has been edited by theultramage: Jun 28 2009, 08:43 AM