For anyone running cncli-sync it is easy to see which stake pools have produced chain forks by running dual block producers. Here is a SQL query you can run to get a list:
First use sqlite3 to connect to your fully synced cncli database.
sqlite3 cncli.db
Issue the following SQL command:
select c1.block_number, c1.slot_number, c1.pool_id, c1.hash, c2.hash from chain as c1 join chain as c2 on c1.block_number = c2.block_number and c1.slot_number = c2.slot_number and c1.pool_id = c2.pool_id and c1.hash < c2.hash and c1.slot_number >= 59356801 group by c1.block_number, c1.slot_number, c1.pool_id, c1.hash, c2.hash;
If you just cut and paste the SQL command above, once connected to cncli.db, you will get output like:
BlockNo|SlotNo | Pool ID | First Block Hash | Second Block Hash
7172499|59432733|f84aed8cedb63195f68ec94a59b3e9b979b7eb0dd41085ae945473d7|2e62cd320d05c063c80354e00a8b10864e34647e8427142c892a095ce55eeb5d|9e06ebf46c19ce9876a8654baf76467f80be9133cc06a4840d4589d461e9c28c
7174614|59476594|c29c92f8319150962650bc8a5e24d918491e8a7b3ac43525afe76baa|07d66b41e3882e9fc7d710cc81c553b3c4370bc39496d4f97083d3a333a6f915|3f977cc3241f026ae5c46a83352902aa1581942f70bb0435aabeb2bd3d5b5f48
7179865|59583999|a631b46315b1567ef0ce2b2ad4cb63faac09f32ceba6716a7e331e5e|4ca92cd2ef864fc15bbd745cf584504e37bcfaf971c439f43df7c8235a149b20|cdeb49452c297af584dbb06c19428145b4298a9cc658990da654e3b7b584335c
7179939|59585524|a631b46315b1567ef0ce2b2ad4cb63faac09f32ceba6716a7e331e5e|18388208cfbfdbc410ee19a5edd6abf572ee2a8b97442485a4abde08217fbc04|36b63632145612d6b7afe3149ce460d80ec1ec2a71b8222eb80def6c3fbd10e6
Note:
- The query pulls data from the beginning of epoch 335 since the where clause has “c1.slot_number >= 59356801”. Obviously just change this value to whatever time frame you are interested in. You can calculate the slot number for the start of an epoch on the command line with:
epoch=335; firstslot="$(echo “(($epoch - 208) * 432000) + 4492800 + 1” | bc)"; echo “firstslot=$firstslot”
(Change the 335 value to the epoch you want)
- The query will only return chain forks that have been received by your node. Every fork does not propagate to every node because when another pool adds a block the protocol will try to select the longest chain. Your individual node may receive the added to fork first and so it will reject the second, if it has not been added to yet. (Longest chain wins rule.)
- Cut and past the Pool ID values into pooltool.io to look up the pool and click on the block number to see what pooltool.io received. Pooltool is likely to receive evidence of more forks than your individual node. I have not yet seen a fork that my node received but pooltool did not.
- The group by clause in the SQL query just removes some duplication where the cncli database stores 2 copies of the exact same block (with same hash) for some reason. (These are not forks.)
The 4 pools listed in the query output above have all produced forks by running dual block producers this epoch (335). (Only the ones received by my node.) They are:
YOYOGI POOL (YYG)
P2P Validator #3 (P2P3)
Staking4ADA.org (4ADA)
Staking4ADA.org (4ADA)
If you are the operator of one of these pools, then please stop running with dual block producers and act more professionally as a good pool operator would.
Update Following @HeptaSean comments below:
This post originally used a formula I calculated by working backwards from figures I already had and ended with constants for epoch 209 transition. However, the shelley hard fork transition was actually at start of epoch 208. The original formula did produce correct results with the different constants but it is better to have the proper constants for the shelley transition instant.
The above formula has been already amended so that it is using the actual shelley hard fork transition values as @HeptaSean pointed out. So if you are reading this post in the future the above formula and SQL query have been amended, so you can just cut and paste and it will be correct.
