> Rewritten 2026-10-26: the original diagnosis covered only dropped re-created replies (effect A). Reading Step 7 against shed14's data shows a second, silent effect (B): replies attached to the parent's **deleted** row. The cause is the same, so both are in scope here. ## Problem and intended result Massive sync handles posts that are deleted and re-created within one batch out of chain order (`hive/db/sql_scripts/massive_sync.sql`, Steps 3-5 and Step 7): - **Steps 3-5** insert every first creation in the batch **before** Step 7 replays any delete. - **Step 7a** applies every "simple" delete (a pair that is not re-created later in the batch) as one set-based UPDATE. - **Step 7b** replays the delete -> re-create cycles in a loop `ORDER BY (author || '/' || permlink), s.id`: each pair's own ops are in chain order, but the pairs are processed **alphabetically**, not interleaved by `s.id`. - A comment's parent is resolved in `process_hive_post_operation` (`hive/db/sql_scripts/hive_post_operations.sql`) as the parent pair's row with `counter_deleted = 0` **at replay time**. So a reply's parent is resolved against whatever state the replay order happens to leave, not the parent's state at the reply's own op. Two observable results: **A. Re-created replies are dropped.** When a reply is re-created (Step 7b) at a point where its parent was live on chain, but hivemind has already marked that parent deleted (by Step 7a, or by an alphabetically earlier pair in 7b), the parent lookup finds no row and the `INSERT ... SELECT` inserts nothing. No error, no log. Measured on a 1.28.8 node synced from scratch: **1,593 comments on 52 days** (2016-08 to 2021); posts are never affected and hivemind never has *more* comments than the chain. **B. Replies are attached to the parent's deleted row.** When a reply is created after its parent was deleted **and re-created** earlier in the same batch, hivemind resolves the parent while the parent's **old** row is still live (the reply is a first creation in Steps 3-5, or its pair sorts before the parent's in 7b). The parent's replayed delete then marks that row deleted, and the re-create inserts a new row, so the reply ends up with `parent_id` (and `root_id`, `depth`, community and category inherited from it) pointing at a deleted row. hived rejects deleting a comment that has replies, so on a correct node **no live reply can have a deleted parent row**. On shed14 (hivemind massive-synced from scratch, now at head 111.1M) there are **376** such replies: every one was created after its parent's live row, and within 1,000 blocks of the parent's delete, i.e. in the same batch. By year: 2016: 2, 2017: 28, 2018: 66, 2019: 92, 2020: 142, 2021: 31, 2022: 10, 2023: 1, 2025: 4. Such replies are missing from their post's discussion, from its `children` count, and from community attribution. Example: | reply | created | parent | parent's old row: created / deleted | parent's live row created | |---|---:|---|---:|---:| | `@worldmappin/wmp1762622520388` | 101,008,704 | `@ulisg/a-beautiful-tour-to-discover-the-cristo-rey-eng-esp` | 101,008,494 / 101,008,518 | 101,008,699 | | `@enlace/resp-una-manana-de-compras-a-morning-of-shopping-esp-eng` | 99,889,791 | `@stefany0720/una-manana-de-compras-a-morning-of-shopping-esp-eng` | 99,886,765 / 99,889,122 | 99,889,160 | | `@hivebuzz/notify-1751798107` | 97,404,692 | `@nevenaix/crete-greece` | 97,404,252 / 97,404,264 | 97,404,471 | (The first example is a plain first creation, `ulisg` sorts before `worldmappin`: Steps 3-5 alone produce B.) Detection query (B): ```sql SELECT count(*) FROM hivemind_app.hive_posts c JOIN hivemind_app.hive_posts p ON p.id = c.parent_id WHERE c.counter_deleted = 0 AND c.depth > 0 AND p.counter_deleted > 0; ``` **Intended result:** massive sync resolves each comment's parent as of that comment's own op in chain order, exactly as live (block-by-block) sync does: a reply is never dropped while its parent was live on chain, and is always attached to the parent row that was live at the reply's op. The set-based character of massive sync stays (Step 7a exists because a per-row loop over ~783K deletes was too slow); do not replace it with a per-op loop over the whole batch. ### Ground truth (effect A) hived's `post_count` counts every creation the chain accepted. For the two accounts behind the largest gaps: | account | hived `post_count` | creations replayed from HAF ops (posts + comments) | hivemind `hive_posts` rows (posts + comments) | |---|---:|---:|---:| | crystalan | 19,950 | 19,950 (10,577 + 9,373) | 19,250 (10,577 + **8,673**) | | crystalliu | 45,025 | 45,025 (28,189 + 16,836) | 44,510 (28,189 + **16,321**) | The replayed creation counts come from haf_stats, which re-implements hived's create-vs-edit and delete decisions op by op. ### Example (effect A) `@crystalan` on 2021-04-14 (blocks 53,004,499–53,033,255): hivemind is 14 comments short for that day. The dropped replies sit in chains in which every link is itself in a delete/re-create loop: - `qri82s` → `qri7xk` → `qri78j` → `qri75w`; - `qrk2l9` and `qrk36e` → `crystal-an-qrk2az` (the parent was deleted 9 times in the window). `qrk2l9`, for example, has 6 creations in chain order: ``` 1@53020568 17@53020586 1@53020714 17@53020735 1@53021065 17@53021068 1@53021069 17@53021071 1@53021071 17@53021076 1@53021076 17@53021090 ``` hivemind has 2 rows for it, `block_num_created` 53020568 and 53021065. ### Every affected day (effect A) Comments created (chain) minus hivemind `hive_posts` rows with `depth > 0`, grouped by the day of `block_num_created`: | day | missing comments | |---|---:| | 2016-08-10 | 1 | | 2018-01-19 | 1 | | 2018-01-22 | 1 | | 2018-01-23 | 2 | | 2018-09-27 | 2 | | 2019-06-30 | 3 | | 2019-07-04 | 4 | | 2019-08-10 | 7 | | 2019-08-11 | 4 | | 2019-08-23 | 1 | | 2020-01-15 | 107 | | 2020-01-16 | 1 | | 2020-01-17 | 12 | | 2020-01-18 | 78 | | 2020-01-21 | 69 | | 2020-01-29 | 66 | | 2020-01-30 | 111 | | 2020-02-01 | 10 | | 2020-02-02 | 12 | | 2020-02-07 | 10 | | 2020-02-09 | 10 | | 2020-02-17 | 6 | | 2020-02-20 | 35 | | 2020-02-27 | 5 | | 2020-04-27 | 18 | | 2021-02-02 | 65 | | 2021-02-03 | 182 | | 2021-02-11 | 3 | | 2021-02-16 | 32 | | 2021-02-17 | 56 | | 2021-04-03 | 22 | | 2021-04-07 | 65 | | 2021-04-08 | 2 | | 2021-04-09 | 45 | | 2021-04-13 | 50 | | 2021-04-14 | 14 | | 2021-04-15 | 7 | | 2021-04-16 | 6 | | 2021-04-17 | 73 | | 2021-04-18 | 5 | | 2021-04-20 | 21 | | 2021-04-23 | 17 | | 2021-04-28 | 3 | | 2021-05-03 | 17 | | 2021-05-12 | 15 | | 2021-07-03 | 57 | | 2021-07-12 | 160 | | 2021-07-18 | 11 | | 2021-07-19 | 5 | | 2021-07-20 | 74 | | 2021-07-23 | 8 | | 2021-07-31 | 2 | No day in 2022–2026 is affected, and no day anywhere has a post difference. ## Acceptance criteria - A massive-sync test over a crafted batch (mock block data under `mock_data/block_data/comment_op/`, or an SQL-level test that drives the massive-sync function over staged ops) covers: (1) a reply re-created while its parent is live, where the parent is later deleted in the same batch (simple delete, and a cycle pair sorting before the reply's); (2) a reply first-created after its parent's delete -> re-create in the same batch; (3) a reply in a cycle whose parent pair sorts after it. Afterwards, every reply the chain created exists in `hive_posts`, and every live reply's `parent_id` is the parent row live at its op (so the detection query above returns 0). The test fails on the current `massive_sync.sql` and passes after the fix. - Live (non-massive) sync results for those ops are unchanged, and the massive-sync and live-sync results for the crafted batch are identical. - Massive sync of the existing CI range takes no more than 10% longer than before (state the before/after timings in the commit message). ## Integration points - `hive/db/sql_scripts/massive_sync.sql` -- Steps 3-5 parent resolution for comments whose parent pair has a delete in the batch, Step 7a/7b ordering, Step 7c/7d follow-ups that key on the re-created rows. - `hive/db/sql_scripts/hive_post_operations.sql` -- `process_hive_post_operation`'s parent lookup, if the fix resolves the parent as of the op rather than `counter_deleted = 0`. - `mock_data/block_data/comment_op/` (or the SQL test harness) -- the crafted delete/re-create batch. Existing nodes need a hivemind resync to repair already-stored data; that is an operator step, not part of this change. Found while building a production cross-check between haf_stats and hivemind post/comment counts (hive/haf_stats#63). <!-- aidev-issue-metadata {"author":{"kind":"human","name":"operator"},"authored_at":"2026-10-26T22:41:15.797833+00:00","content_digest":"sha256:4c19b9bb341d4d0d2115e31f3f9c23bf711738e1cad0b844683c9379b431e382","labels":[{"accepted":true,"authority":"executive","label":"bug","proposer":{"kind":"human","name":"operator"},"rationale":"declared by the human producer as a draft field","reason":"applied"},{"accepted":true,"authority":"executive","label":"todo","proposer":{"kind":"human","name":"operator"},"rationale":"declared by the human producer as a draft field","reason":"applied"}],"relations":[],"version":1} -->