cowballzach
commented
1 month ago Closed commonwealth-issue-bot[bot] closed 1 month ago
cowballzach
commented
1 month ago 
timolegros
commented
1 month ago From what I can see, stargate does not have any groups registered in the DB but for some reason, their topics are still linked to a non-existing group with ID 14.
Resolving the issue may be as simple as clearing the group_ids arrays of their topics with the following query:
UPDATE "Topics"
SET group_ids = '{}'
WHERE community_id = 'stargatetoken';This issue applies to 41 other communities across 95 topics.
To get the list of affect communities/topics:
SELECT
unnest_groups.topic_id,
unnest_groups.community_id
FROM (
SELECT
id AS topic_id,
community_id,
UNNEST(group_ids) AS unnest_group_id
FROM "Topics"
) AS unnest_groups
LEFT JOIN "Groups" ON unnest_groups.unnest_group_id = "Groups".id
WHERE "Groups".id IS NULL
GROUP BY unnest_groups.community_id, unnest_groups.topic_id;To get the list of non-existent group_ids:
SELECT DISTINCT unnest_group_id AS missing_group_id
FROM (
SELECT UNNEST(group_ids) AS unnest_group_id
FROM "Topics"
) AS unnest_groups
LEFT JOIN "Groups" ON unnest_groups.unnest_group_id = "Groups".id
WHERE "Groups".id IS NULL;The following query deletes all non-existing group ids from the "Topics" table. This would resolve the issue for all communities across all topics but it does not solve the root issue (could already be resolved - still investigating):
WITH missing_group_ids AS (
SELECT DISTINCT unnest_group_id AS missing_group_id
FROM (
SELECT UNNEST(group_ids) AS unnest_group_id
FROM "Topics"
) AS unnest_groups
LEFT JOIN "Groups" ON unnest_groups.unnest_group_id = "Groups".id
WHERE "Groups".id IS NULL
)
UPDATE "Topics"
SET group_ids = ARRAY(
SELECT id
FROM UNNEST(group_ids) AS id
WHERE id NOT IN (SELECT missing_group_id FROM missing_group_ids)
)
WHERE EXISTS (
SELECT 1
FROM UNNEST(group_ids) AS id
WHERE id IN (SELECT missing_group_id FROM missing_group_ids)
);Stargate said that they do not have any gating enabled within the forum. Also, they stated that no groups have been recently deleted. for added context
jnaviask
commented
1 month ago Fine by me to run it.
timolegros
commented
1 month ago Just in case here are the reversal queries:
CREATE TABLE temp_missing_group_ids (
unnest_group_id INTEGER,
topic_id INTEGER,
community_id TEXT
);
INSERT INTO temp_missing_group_ids (unnest_group_id, topic_id, community_id) VALUES
(1, 1496, 'community-trial'),
(2, 1487, 'coinflex'),
(3, 1505, 'rocket-fuel'),
(4, 1533, 'apeswapnft'),
(5, 1534, 'apeswapnft'),
(6, 2993, 'gelatonetwork'),
(4, 1530, 'apeswapnft'),
(4, 1531, 'apeswapnft'),
(4, 1532, 'apeswapnft'),
(7, 2422, 'mfers'),
(7, 2428, 'mfers'),
(7, 2423, 'mfers'),
(8, 2777, 'allianceblockdao'),
(7, 2813, 'mfers'),
(7, 2425, 'mfers'),
(8, 2785, 'allianceblockdao'),
(10, 919, 'ideamarket'),
(11, 777, 'spiritswap'),
(10, 918, 'ideamarket'),
(12, 921, 'insuretoken'),
(13, 1013, 'phonon-dao'),
(14, 1064, 'stargatetoken'),
(15, 1175, 'anata'),
(13, 1015, 'phonon-dao'),
(13, 1017, 'phonon-dao'),
(13, 1016, 'phonon-dao'),
(18, 1180, 'bitdao'),
(19, 841, 'fingerprints-dao'),
(19, 844, 'fingerprints-dao'),
(19, 842, 'fingerprints-dao'),
(19, 845, 'fingerprints-dao'),
(22, 1181, 'bitdao'),
(23, 916, 'morpheus-swap'),
(13, 998, 'phonon-dao'),
(24, 691, 'exodia'),
(25, 690, 'exodia'),
(26, 950, 'jones-dao'),
(28, 1303, 'lico-token'),
(29, 1322, 'ankr-forum'),
(30, 1320, 'ankr-forum'),
(31, 1317, 'ankr-forum'),
(2, 1433, 'coinflex'),
(14, 1389, 'stargatetoken'),
(6, 2997, 'gelatonetwork'),
(33, 3059, 'moneta'),
(34, 3113, 'down-vault-pos'),
(35, 3121, 'allianceblock-nexera-token'),
(36, 3124, 'xcult-house'),
(37, 3200, 'lithosphere'),
(37, 3201, 'lithosphere'),
(38, 3202, 'lithosphere'),
(21, 1146, 'plasma-finance'),
(35, 3294, 'allianceblock-nexera-token'),
(7, 2812, 'mfers'),
(9, 2809, 'paladin-protocol'),
(13, 1014, 'phonon-dao'),
(16, 1116, 'hummingbot-foundation'),
(17, 862, 'ftmguru'),
(20, 1145, 'plasma-finance'),
(14, 1065, 'stargatetoken'),
(32, 1324, 'passportoffreeland'),
(33, 3060, 'moneta'),
(39, 3215, 'gravidao-moloch'),
(40, 3256, 'oqdao'),
(39, 3237, 'gravidao-moloch'),
(43, 3404, 'battleflydao'),
(42, 3484, 'opnx'),
(47, 3546, 'PancakeSwap Token'),
(42, 3416, 'opnx'),
(45, 3501, 'ftw-dao'),
(43, 3451, 'battleflydao'),
(43, 3452, 'battleflydao'),
(10, 920, 'ideamarket'),
(21, 1148, 'plasma-finance'),
(50, 3486, 'opnx'),
(8, 2910, 'allianceblockdao'),
(19, 846, 'fingerprints-dao'),
(19, 843, 'fingerprints-dao'),
(20, 1151, 'plasma-finance'),
(26, 951, 'jones-dao'),
(27, 917, 'morpheus-swap'),
(15, 1173, 'anata'),
(32, 1326, 'passportoffreeland'),
(51, 3532, 'unilend-finance'),
(35, 3122, 'allianceblock-nexera-token'),
(44, 3433, 'Meta Toy City'),
(44, 3430, 'Meta Toy City'),
(44, 3432, 'Meta Toy City'),
(46, 3547, 'PancakeSwap Token'),
(48, 3548, 'PancakeSwap Token'),
(49, 3572, 'my-aave-ethereum-dai'),
(49, 3571, 'my-aave-ethereum-dai'),
(51, 3534, 'unilend-finance'),
(41, 3327, 'common'),
(255, 4596, 'layerzero');
UPDATE "Topics" t
SET group_ids = array_append(group_ids, tm.unnest_group_id)
FROM temp_missing_group_ids tm
WHERE t.id = tm.topic_id AND t.community_id = tm.community_id;Update: query was executed in production which should temporarily resolve the issue for affected communities.
Note: the issue affected a LayerZero topic so it is most likely a bug that currently exists in the codebase. This ticket should be handled (find root cause) on Monday, July 22.
timolegros
commented
1 month ago @rbennettcw @kurtisassad @Rotorsoft this bug is still open since the root cause has not been identified and only a temporary fix was applied to resolve the existing data inconsistencies (new ones may yet appear).
kurtisassad
commented
1 month ago Ok I figured out the issue. Basically the inconsistency was introduced in the remove-ropsten-chain-node migration. We remove the Groups associated withe the communities for ropsten chains, but don't remove the associated group_ids in the Topics.group_ids.
Then the new permissions logic I had coded had relied on group_ids to have valid references to the Groups table. This assumption was not valid leading to the failure.
Also all routes were analyzed to ensure that both now, and historically through the git history, we had always been updating the associated topics in a transaction when updating/deleting the associated Groups.
The next steps are to finish the permissions model refactor https://github.com/hicommonwealth/commonwealth/pull/8528. This will make it impossible to have an inconsistent state in the DB.
See the proposed full model here: https://github.com/hicommonwealth/commonwealth/issues/7904
timolegros
commented
1 month ago Ok I figured out the issue. Basically the inconsistency was introduced in the remove-ropsten-chain-node migration. We remove the Groups associated withe the communities for ropsten chains, but don't remove the associated group_ids in the Topics.group_ids.
Then the new permissions logic I had coded had relied on group_ids to have valid references to the Groups table. This assumption was not valid leading to the failure.
Also all routes were analyzed to ensure that both now, and historically through the git history, we had always been updating the associated topics in a transaction.
The next steps are to finish the permissions model refactor #8528. This will make it impossible to have an inconsistent state in the DB.
See the proposed full model here: #7904
Amazing!!! Thank you, Kurtis!
Description
User gets a "failed to post thread" message when posting under 'proposals' in Stargate Finance's community. I was able to replicate with my burner account.
Internal Reporter (Growth Team)
Mitchy (@Mitchy)
Communities Affeceted
Stargate Finance
Initial Conditions
Environment: production Encounter Date/Time: 7/19, 11am ET
Reproduction Steps
Try to post a proposal under 'proposals' in https://commonwealth.im/stargatetoken/discussions/Proposals?featured=newest
Should get a "Failed to post thread" error message
Expected Behavior
Thread should be posted?
Videos / Screenshots
Additional Information
I'm able to recreate aswell. Stargate Finance at one point may have been token gated onchain, could be something that isn't showing up in the UI.