Closed vorpal-buildbot closed 6 years ago
This does not occur on dev nor in a private message with the bot, which is kind of weird.
Issue seems to be multiple entries for new GRN cards in discordbot
cards db:
MariaDB [discordbot]> SELECT * FROM face WHERE name = 'Chemister''s Insight';
+-------+---------------------+-----------+------+-------+-----------+---------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+------------+------+------+---------+----------+---------------------+---------+
| id | name | mana_cost | cmc | power | toughness | loyalty | type | text | search_text | image_name | hand | life | starter | position | name_ascii | card_id |
+-------+---------------------+-----------+------+-------+-----------+---------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+------------+------+------+---------+----------+---------------------+---------+
| 18839 | Chemister's Insight | {3}{U} | 4 | NULL | NULL | NULL | Instant | Draw two cards.
Jump-start (You may cast this card from your graveyard by discarding a card in addition to paying its other costs. Then exile this card.) | Draw two cards.
Jump-start | grn_32 | NULL | NULL | NULL | 1 | Chemister's Insight | 18639 |
| 18841 | Chemister's Insight | {3}{U} | 4 | NULL | NULL | NULL | Instant | Draw two cards.
Jump-start (You may cast this card from your graveyard by discarding a card in addition to paying its other costs. Then exile this card.) | Draw two cards.
Jump-start | grn_32 | NULL | NULL | NULL | 1 | Chemister's Insight | 18641 |
| 18843 | Chemister's Insight | {3}{U} | 4 | NULL | NULL | NULL | Instant | Draw two cards.
Jump-start (You may cast this card from your graveyard by discarding a card in addition to paying its other costs. Then exile this card.) | Draw two cards.
Jump-start | grn_32 | NULL | NULL | NULL | 1 | Chemister's Insight | 18641 |
| 18844 | Chemister's Insight | {3}{U} | 4 | NULL | NULL | NULL | Instant | Draw two cards.
Jump-start (You may cast this card from your graveyard by discarding a card in addition to paying its other costs. Then exile this card.) | Draw two cards.
Jump-start | grn_32 | NULL | NULL | NULL | 1 | Chemister's Insight | 18641 |
| 18845 | Chemister's Insight | {3}{U} | 4 | NULL | NULL | NULL | Instant | Draw two cards.
Jump-start (You may cast this card from your graveyard by discarding a card in addition to paying its other costs. Then exile this card.) | Draw two cards.
Jump-start | grn_32 | NULL | NULL | NULL | 1 | Chemister's Insight | 18641 |
| 18847 | Chemister's Insight | {3}{U} | 4 | NULL | NULL | NULL | Instant | Draw two cards.
Jump-start (You may cast this card from your graveyard by discarding a card in addition to paying its other costs. Then exile this card.) | Draw two cards.
Jump-start | grn_32 | NULL | NULL | NULL | 1 | Chemister's Insight | 18644 |
| 18848 | Chemister's Insight | {3}{U} | 4 | NULL | NULL | NULL | Instant | Draw two cards.
Jump-start (You may cast this card from your graveyard by discarding a card in addition to paying its other costs. Then exile this card.) | Draw two cards.
Jump-start | grn_32 | NULL | NULL | NULL | 1 | Chemister's Insight | 18645 |
+-------+---------------------+-----------+------+-------+-----------+---------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+------------+------+------+---------+----------+---------------------+---------+
7 rows in set (0.06 sec)
MariaDB [discordbot]> SELECT name, COUNT(*) FROM face GROUP BY name HAVING COUNT(name) > 1;
+--------------------------------+----------+
| name | COUNT(*) |
+--------------------------------+----------+
| Brisela, Voice of Nightmares | 2 |
| Chemister's Insight | 7 |
| Chittering Host | 2 |
| Hanweir, the Writhing Township | 2 |
| Vraska, Golgari Queen Emblem | 4 |
+--------------------------------+----------+
5 rows in set (0.07 sec)
The root cause here is that we have bad data for the partner cards from battlebond coming from mtgjson. I patched this in multiverse but the bot db hasn't been rebuilt since then. I have fake updated the version of mtgjson to a prior version in the bot's card db and restarted the bot. That should do the trick.
SELECT
`u`.`id` AS `id`,
CASE
WHEN layout = 'double-faced' OR layout = 'flip' THEN
GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN face_name ELSE '' END SEPARATOR '')
WHEN layout = 'meld' THEN
GROUP_CONCAT(CASE WHEN `u`.position = 1 OR `u`.position = 2 THEN face_name ELSE '' END SEPARATOR '')
ELSE
GROUP_CONCAT(face_name SEPARATOR ' // ' )
END
AS name, GROUP_CONCAT(`u`.`mana_cost` SEPARATOR '|') AS `mana_cost`,
CASE
WHEN layout = 'split' OR layout = 'aftermath' THEN
SUM(`u`.cmc)
WHEN layout = 'meld' THEN
SUM(CASE WHEN `u`.position = 1 OR `u`.position = 2 THEN cmc ELSE 0 END)
ELSE
SUM(CASE WHEN `u`.position = 1 THEN `u`.cmc ELSE 0 END)
END
AS cmc, GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN `u`.`power` ELSE '' END SEPARATOR '') AS `power`, GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN `u`.`toughness` ELSE '' END SEPARATOR '') AS `toughness`, GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN `u`.`loyalty` ELSE '' END SEPARATOR '') AS `loyalty`,
CASE
WHEN layout = 'meld' THEN
GROUP_CONCAT(CASE WHEN `u`.position = 1 OR `u`.position = 2 THEN type ELSE '' END SEPARATOR '')
ELSE
GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN type ELSE '' END SEPARATOR '')
END
AS type, GROUP_CONCAT(`u`.`text` SEPARATOR '
-----
') AS `text`, GROUP_CONCAT(`u`.`search_text` SEPARATOR '
-----
') AS `search_text`, GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN `u`.`image_name` ELSE '' END SEPARATOR '') AS `image_name`, SUM(CASE WHEN `u`.position = 1 OR (`u`.position = 2 AND `u`.layout = 'meld') THEN `u`.`hand` ELSE NULL END) AS `hand`, SUM(CASE WHEN `u`.position = 1 OR (`u`.position = 2 AND `u`.layout = 'meld') THEN `u`.`life` ELSE NULL END) AS `life`, SUM(CASE WHEN `u`.position = 1 OR (`u`.position = 2 AND `u`.layout = 'meld') THEN `u`.`starter` ELSE NULL END) AS `starter`, SUM(CASE WHEN `u`.position = 1 OR (`u`.position = 2 AND `u`.layout = 'meld') THEN `u`.`position` ELSE NULL END) AS `position`,
CASE
WHEN layout = 'double-faced' OR layout = 'flip' THEN
GROUP_CONCAT(CASE WHEN `u`.position = 1 THEN name_ascii ELSE '' END SEPARATOR '')
WHEN layout = 'meld' THEN
GROUP_CONCAT(CASE WHEN `u`.position = 1 OR `u`.position = 2 THEN name_ascii ELSE '' END SEPARATOR '')
ELSE
GROUP_CONCAT(name_ascii SEPARATOR ' // ' )
END
AS name_ascii, SUM(CASE WHEN `u`.position = 1 OR (`u`.position = 2 AND `u`.layout = 'meld') THEN `u`.`card_id` ELSE NULL END) AS `card_id`, `u`.`layout` AS `layout`, GROUP_CONCAT(face_name SEPARATOR '|') AS names, legalities, `u`.`pd_legal` AS `pd_legal`, bugs
FROM (
SELECT c.id, c.layout, f.mana_cost, f.cmc, f.power, f.toughness, f.loyalty, f.type, f.text, f.search_text, f.image_name, f.hand, f.life, f.starter, f.position, f.name_ascii, f.card_id, f.name AS face_name,
pd_legal,
legalities
FROM
card AS c
INNER JOIN
face AS f ON c.id = f.card_id
LEFT JOIN (
SELECT
cl.card_id,
SUM(CASE WHEN cl.format_id = 21 THEN 1 ELSE 0 END) > 0 AS pd_legal,
GROUP_CONCAT(CONCAT(fo.name, ':', cl.legality)) AS legalities
FROM
card_legality AS cl
LEFT JOIN
format AS fo ON cl.format_id = fo.id
GROUP BY
cl.card_id
) AS cl ON cl.card_id = c.id
GROUP BY
f.id
ORDER BY
f.card_id, f.position
) AS u
LEFT JOIN (
SELECT
cb.card_id,
GROUP_CONCAT(CONCAT(cb.description, '|', cb.classification, '|', cb.last_confirmed, '|', cb.url, '|', cb.from_bug_blog, '|', cb.bannable) SEPARATOR '_SEPARATOR_') AS bugs
FROM
card_bug AS cb
GROUP BY
cb.card_id
) AS bugs ON u.id = bugs.card_id
WHERE u.id IN (SELECT c.id FROM card AS c INNER JOIN face AS f ON c.id = f.card_id WHERE (1 = 1))
GROUP BY u.id
***HAVING position IS NULL***
is what revealed the culprits.
Seemed to do it.
Reported on Discord by bakert#2193