Deletes items with duplicate inventory_id on a player's inventory. This script won't delete permanent items.

delete_item_duplicates.sql

DROP PROCEDURE IF EXISTS delete_duplicates;

delimiter |
CREATE PROCEDURE delete_duplicates()
BEGIN
    DECLARE done INT;

    DECLARE playerid INT;
    DECLARE inventoryid INT;
    DECLARE countnum INT;

    DECLARE curs CURSOR FOR
        select
            player_id, inventory_id, count(*)
        from
            items
        group by
            player_id, inventory_id
        having count(*) > 1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN curs;

    SET done = 0;
    REPEAT
        FETCH curs INTO playerid,inventoryid,countnum;
        delete from
            items
        where
            player_id = playerid and inventory_id = inventoryid
            and expiration != 9201999; -- comment this line to include permanent items in the task
    UNTIL done END REPEAT;

    CLOSE curs;
END|

delimiter ;

CALL delete_duplicates;
DROP PROCEDURE IF EXISTS delete_duplicates;

-- check if there are still duplicate items after this actions
-- if there are still duplicate items it may be a permanent one
select player_id, inventory_id, count(*) from items group by player_id, inventory_id having count(*) > 1;