Goodbye to Merit History...

[botany]

All good things must come to an end.

Today marks 120 days since the merit system was introduced, and as Theymos said, old entries are going to start dropping off the file:

https://bitcointalk.org/merit.txt.xz

If you want a complete list of merit entries, you now need to keep your own historical records.

Oldest entries (120+ days) will also start disappear off of individual merit pages.

Vod - This is why we need the Bitcoin Public Information Project. 
Wouldn't it contain a full record?

[DdmrDdmr]

<...>

Until now, what I did was a full load of the complete merit.txt file, since it was self-sufficient and covered all the lifespan of the Merit System (so each load discarded the previous). I load the data files into a RDBMS (my unix/linux memory is too distant these days).

Seen what we’ve seen today, the tactics for loading the data will be as follows:
1.   Load the new merit.txt file into a table.
2.   Insert into the new table all the records from the previous load’s table that do not exist in the new table (comparing by time, msg, user_from, user_to) and with a timestamp <= min(timestamp) in new file.

That is, from the old file, only retrieve records that are outside the window timeframe of the newly received file.

The 9 registers we’ve been talking about before are in the new file (although with different msg Ids than in last week’s file), so placing the above stated timestamp condition assures that I’ll keep the latest msgId.
Having done the above, the aggregate totals are as follows (for anyone who wants to compare):

nMerit     nTx       nFrom     nTo        minDate                            maxDate                       
159.076   71.427   14.143   15.755   2018-01-24 22:12:21.000   2018-05-25 02:45:40.000

I broke it down by week and compared it to my reports of the kind. All the historical static weeks coincide in values, so the process is fine.

As I said before, what we will lose, whatever we do, is the track of the msg Ids that change for a given Tx outside the 120 day window. So for example, if a merited post gets moved or deleted after 120 days, the cumulative file has no way of knowing that since the Tx does not update in the weekly file.
The deviation should be very small and pretty much ignorable (unless anyone goes bonkers and starts to move/delete things heavily after 120 days). The deviation should only be in terms of the post msg Id (when aggregating by Forum Section for example), but should not budge on the aggregate user’s balance.

I’ve seen the timestamp again and there are cases of both double-clicks, as well as simultaneous meriting over the forum.

[DdmrDdmr]

@LoyceV, can you check my profile according your "all time data" if my merit stats are correct? Trying to troubleshoot a problem here so it will be useful to know if there is something wrong with the data dumps.

From what I have in my data you've got:

NumTx   sMeritSent
63           68

NumTx   sMeritReceived
94           184

Let's see if it is the same as LoyceV's in order to contrast.

[coinlocket$]

Luckly we can keep tracking offline but it's sad that now we can't see merit under the profile of the user.

@OP. If you wrote the title as "Goodbye to Merit" omitting the word History, this thread would has done endless visualizations! 

[TheBeardedBaby]

@LoyceV, can you check my profile according your "all time data" if my merit stats are correct? Trying to troubleshoot a problem here so it will be useful to know if there is something wrong with the data dumps.

From what I have in my data you've got:

NumTx   sMeritSent
63           68

NumTx   sMeritReceived
94           184

Let's see if it is the same as LoyceV's in order to contrast.

Thanks man. We have to see what LoyceV has as info then i need to troubleshoot the problem.

[DdmrDdmr]

<...> We have to see what LoyceV has as info then i need to troubleshoot the problem.

Ok, we'll wait for that and move on from there. If you need the TXs it's easy to get them, but I'm not sure you'll want them posted on the thread (for no particular reason, just because it's kind of like disecting you on the thread ... ).

[atliens99]

Terrible history should be permanent, a lot of merit abusers are going to get away with evil doings.

[digaran]

Terrible history should be permanent, a lot of merit abusers are going to get away with evil doings.

Some of the merit sources are also using their sMerits source for personal use, such as meriting anybody who agrees with them in their posts. evil doings are every where man. 

[bitmover]

All good things must come to an end.

Today marks 120 days since the merit system was introduced, and as Theymos said, old entries are going to start dropping off the file:

https://bitcointalk.org/merit.txt.xz

If you want a complete list of merit entries, you now need to keep your own historical records.

Oldest entries (120+ days) will also start disappear off of individual merit pages.

This is sad.

Merit could be like the blockchain, all merit transactions recorded forever 

[apoorvlathey]

Why was there a 120 day limit put on it?

theymos had already stated somewhere that it was to avoid the problems that would arise due to pagination on merit pages as merit transactions of each user increases.

is there a way around to make archive.is able to capture my merit page?
it seems that merit page can only be viewed by registered users only, any reason for this?
It would be great if theymos can change so viewing the page does not require login

Yeah, archive.is would not work because merit pages are not available to non-logged-in users. It was because Merit history of users doesn't serve any purpose for the public so it can't be viewed.

Although as wanted to use archive.is to backup your stats, you could instead download the Merit dumps file occasionally here :  https://bitcointalk.org/merit.txt.xz

[LoyceV]

@LoyceV, can you check my profile according your "all time data" if my merit stats are correct? Trying to troubleshoot a problem here so it will be useful to know if there is something wrong with the data dumps.

With last Friday's data:
Earned:

Code:

    87. 184 Merit received by iasenko (#1291828) from 67 unique users in 94 transactions

Sent:

Code:

   434. 68 Merit sent by iasenko (#1291828) to 55 unique users in 63 transactions

What are you're trying to troubleshoot? Assuming you started with 10 Merit airdrop, this looks okay to me.

Until now, what I did was a full load of the complete merit.txt file, since it was self-sufficient and covered all the lifespan of the Merit System (so each load discarded the previous).

I tried to prepare for the 120 days limit. Well, that didn't work

That is, from the old file, only retrieve records that are outside the window timeframe of the newly received file.

This is what I will do. It's only a bit more work.

[TheBeardedBaby]

@LoyceV, can you check my profile according your "all time data" if my merit stats are correct? Trying to troubleshoot a problem here so it will be useful to know if there is something wrong with the data dumps.

With last Friday's data:
Earned:

Code:

    87. 184 Merit received by iasenko (#1291828) from 67 unique users in 94 transactions

Sent:

Code:

   434. 68 Merit sent by iasenko (#1291828) to 55 unique users in 63 transactions

What are you're trying to troubleshoot? Assuming you started with 10 Merit airdrop, this looks okay to me..

Yeah seems fine to me too. My BPIP stats are wrong, so I'm trying to find why. That's why I was asking, thanks.

[r1s2g3]

<...>

Until now, what I did was a full load of the complete merit.txt file, since it was self-sufficient and covered all the lifespan of the Merit System (so each load discarded the previous). I load the data files into a RDBMS (my unix/linux memory is too distant these days).

Seen what we’ve seen today, the tactics for loading the data will be as follows:
1.   Load the new merit.txt file into a table.
2.   Insert into the new table all the records from the previous load’s table that do not exist in the new table (comparing by time, msg, user_from, user_to) and with a timestamp <= min(timestamp) in new file.

That is, from the old file, only retrieve records that are outside the window timeframe of the newly received file.

The 9 registers we’ve been talking about before are in the new file (although with different msg Ids than in last week’s file), so placing the above stated timestamp condition assures that I’ll keep the latest msgId.
Having done the above, the aggregate totals are as follows (for anyone who wants to compare):

nMerit     nTx       nFrom     nTo        minDate                            maxDate                       
159.076   71.427   14.143   15.755   2018-01-24 22:12:21.000   2018-05-25 02:45:40.000

I broke it down by week and compared it to my reports of the kind. All the historical static weeks coincide in values, so the process is fine.

As I said before, what we will lose, whatever we do, is the track of the msg Ids that change for a given Tx outside the 120 day window. So for example, if a merited post gets moved or deleted after 120 days, the cumulative file has no way of knowing that since the Tx does not update in the weekly file.
The deviation should be very small and pretty much ignorable (unless anyone goes bonkers and starts to move/delete things heavily after 120 days). The deviation should only be in terms of the post msg Id (when aggregating by Forum Section for example), but should not budge on the aggregate user’s balance.

I’ve seen the timestamp again and there are cases of both double-clicks, as well as simultaneous meriting over the forum.

I am putting my solution in image, because cloudfare is not allowing me to write sql's command in post.

https://imgur.com/a/Mvxc1El

[LoyceV]

I am putting my solution in image, because cloudfare is not allowing me to write sql's command in post.

Please report this to theymos, so he can fix it.

The weird thing I've found are a set of 9 Txs that were in last week's file and not on this weeks, belonging to the common period:

time         amount   msg                           user_from    user_to   converted_date
1526466697   1   45212.msg37354230       1192397   209286    2018-05-16 10:31:37.000
1526427398   1   3297659.msg37378125   452769     1168027   2018-05-15 23:36:38.000
1526417463   1   1747305.msg37390368   1160524   1012481   2018-05-15 20:51:03.000
1526412210   1   3297659.msg37378125   1627661   1168027   2018-05-15 19:23:30.000
1526403535   2   2671650.msg37344086   234915     501852    2018-05-15 16:58:55.000
1526391452   2   2671650.msg37344086   1765178   501852    2018-05-15 13:37:32.000
1523884328   2   3315165.msg34758186   974425     668651    2018-04-16 13:12:08.000
1518181690   1   2669342.msg29300364   1141229   1495332   2018-02-09 13:08:10.000
1518181563   1   2669342.msg29300364   1141229   1495332   2018-02-09 13:06:03.000

I've seen this happen before, but now it is a (very minor) issue, since we have to merge files.
The above 9 TXs should be in both files. I tried to check some of the messages and they are probably deleted messages. Nevertheless, as with all Merit.txt files, the TX should remain in the file.

When I compare my previous result to my new result, I have 143 lines less.
The previous result double counted all instances where the message ID changed for the next week. My new list only adds the new entries to the previous total.
I'd like to compare data later on, it would be nice to confirm having exactly the same totals. I didn't expect the difference to be this large.

For reference, these lines are now gone:

Code:

1526466697	1	4097266.msg37354230	1192397	209286
1526427398	1	4075132.msg37378125	452769	1168027
1526417463	1	3979143.msg37390368	1160524	1012481
1526412210	1	4075132.msg37378125	1627661	1168027
1526403535	2	4032536.msg37344086	234915	501852
1526391452	2	4032536.msg37344086	1765178	501852
1525796577	1	2761992.msg36678194	124876	2000417
1525390464	10	2056317.msg36248592	918135	1955858
1525304619	1	3327310.msg36155084	452769	2038983
1525301702	5	2056317.msg36155617	918135	2081207
1525301696	5	2056317.msg36155617	918135	2081207
1525258130	1	1535816.msg36073121	140827	538922
1525107464	1	3441376.msg35970037	520313	787736
1524932636	1	2040221.msg35808459	494856	841833
1524832078	3	3134042.msg35714971	1914752	1916685
1524041162	1	1887079.msg34839877	357681	1339582
1524017659	1	2499481.msg34966256	452769	1790929
1523884328	2	4169555.msg34758186	974425	668651
1523634105	1	3169721.msg34456631	353637	1739100
1523576770	2	2892980.msg32725072	1575639	1575667
1523294274	1	2818398.msg33199264	382413	1068464
1523231013	1	1690035.msg34180795	156438	1339582
1523034791	1	1493601.msg34045254	234771	2003834
1523019675	1	1493601.msg34045254	198552	2003834
1523003825	1	2751956.msg34056542	1246188	2008113
1522899653	1	2312197.msg33753489	488253	1554619
1522890015	1	753252.msg33952617	198573	129726
1522871493	1	2385260.msg33946733	728512	1843615
1522860334	20	1208781.msg33932142	87190	1770901
1522860292	20	1208781.msg33935169	87190	2003863
1522780544	2	1758123.msg33827771	879468	388424
1522776627	2	3237470.msg33721737	507856	978309
1522752871	1	3241014.msg33812479	125583	1807652
1522676504	5	3241014.msg33760264	125583	1819966
1522602264	10	178336.msg33692910	97278	537170
1522599886	1	178336.msg33692910	26273	537170
1522546989	1	3231748.msg33638889	398552	1876950
1522469644	2	2811491.msg33513937	997839	1979059
1522372644	1	2040221.msg33486549	494856	841833
1522264461	1	3198068.msg33378650	1095186	396977
1522007982	1	3194607.msg33126769	1075907	1429434
1521896600	1	2770033.msg33054965	805294	1224612
1521759827	1	2391800.msg32904216	290351	1130867
1521704290	1	3006322.msg31240542	1665212	1881180
1521695184	2	2598751.msg31848678	719624	799502
1521656313	1	3160725.msg32779348	385013	1843281
1521586843	1	1006631.msg32789449	639728	855468
1521586205	1	1006631.msg32789449	1097516	855468
1521584858	1	1006631.msg32783135	855468	1097516
1521477490	1	2677548.msg32665012	1215537	1339582
1521471114	1	3141615.msg32662368	989041	1648221
1521239666	1	2803973.msg32332223	1436157	1702217
1521129325	2	3130158.msg32381100	143958	1056258
1521066413	2	3090355.msg32091786	1436157	1702217
1521046709	1	3122807.msg32309318	1266946	1758817
1520940049	1	1006631.msg32196506	1117066	408013
1520930553	5	1435813.msg32185752	516361	1259826
1520926717	2	3042663.msg31418158	1400491	1607649
1520885771	1	1085285.msg32168022	912011	353017
1520876682	2	3055616.msg31538730	234771	1831676
1520816841	1	3055943.msg32092938	349097	1786340
1520795324	1	2619577.msg31446500	1430861	1436157
1520699407	50	957976.msg31968216	64700	1339582
1520690956	10	785257.msg31826500	151629	1339582
1520640300	2	2912695.msg31880812	1430861	1436157
1520630540	1	2958707.msg31932752	942940	974008
1520613033	2	1394689.msg31867722	919837	485285
1520609280	1	2963745.msg31845846	1104013	1602816
1520601498	1	3092321.msg31913756	929703	1244401
1520520657	2	2814237.msg28838536	986108	940323
1520459335	1	3042333.msg31506712	1243309	1430861
1520456620	2	3031289.msg31508094	1111290	1430861
1520443109	10	1987280.msg31459300	320785	1889502
1520443010	1	3081595.msg31788815	98986	62955
1520384240	1	2767700.msg31056270	998627	1743938
1520320033	2	2090197.msg31657284	1111017	939786
1520264601	1	2752467.msg31635405	18321	1339582
1520155742	1	233997.msg31450319	48481	300872
1520138349	1	3061206.msg31524692	18321	1659648
1520036601	3	2599900.msg31218277	1111290	1430861
1520012509	1	3040681.msg31426616	112564	1577303
1519895629	1	3006322.msg31240542	698159	1881180
1519852024	1	1957064.msg30805140	399581	318891
1519821741	1	1989756.msg31182466	1025775	1339582
1519749618	1	2215995.msg25894108	32881	1218175
1519534693	2	2946384.msg30379597	1144255	1173155
1519518632	1	2638162.msg30986492	1062275	1250048
1519471159	1	2967940.msg30576746	655931	1436157
1519407574	1	469640.msg30915755	220766	1006453
1519351882	3	12156.msg30759042	1050181	1051469
1519235862	1	2638162.msg30725631	390287	1250048
1519160458	4	707083.msg30203889	1108203	1779611
1519114810	1	2962774.msg30661313	17501	392914
1519066009	1	2068554.msg30623178	188198	1339582
1519062564	4	2620982.msg30565943	1166578	1685440
1519061662	1	2068554.msg30623178	898713	1339582
1518775208	1	2759550.msg30302759	1034472	1177053
1518760239	1	2917461.msg30313194	1532483	1669483
1518745325	3	2874691.msg30272455	1050181	1051469
1518716528	1	2917461.msg30313194	180477	1669483
1518701194	3	825087.msg28544154	1135347	1294231
1518654253	1	1914900.msg30105142	1045734	1005130
1518641599	2	2905039.msg30133084	1034472	1178583
1518628231	2	2947973.msg30287619	119133	895962
1518244717	5	2909748.msg29932129	940323	1165921
1518244704	5	2909748.msg29932129	940323	1165921
1518181690	1	4029745.msg29300364	1141229	1495332
1518181563	1	4029745.msg29300364	1141229	1495332
1517754099	2	2835524.msg29427454	359844	1575667
1517618827	7	2707408.msg29436008	231477	952659
1517592441	2	2770534.msg28683170	1139385	1294231
1517582765	1	2868750.msg29458851	1030125	333242
1517580067	1	178336.msg29448548	88912	923118
1517473831	1	2110838.msg27211117	146984	1464627
1517462500	1	2638162.msg28871113	1235536	1250048
1517438712	1	2818350.msg29343430	408246	1777628
1517302875	6	1798901.msg29212240	260842	841833
1517299389	1	2818398.msg28943513	1023582	1439159
1517279363	1	2727916.msg29136753	1202550	1500233
1517222430	1	2842436.msg29157491	1281314	261696
1517177012	15	2785613.msg29128819	400366	973996
1517081005	1	2638162.msg29054281	1236492	1250048
1517015450	1	2402425.msg29005838	1191801	1731689
1516917750	1	2820113.msg28926157	720498	1423193
1516917403	1	2820113.msg28926157	926831	1423193
1516917066	5	2820113.msg28926157	364103	1423193
1516916536	1	2770033.msg28734828	1153113	889535
1516899225	1	2280600.msg26281816	662293	81995
1516897644	1	1867474.msg26547716	662293	81995
1516897317	1	756166.msg27064691	662293	81995
1516895463	1	1076426.msg28899508	662293	81995
1516889113	4	2136837.msg28879610	1009479	1300566
1516889078	1	2136837.msg28879610	1084695	1300566
1516888875	1	2136837.msg28879610	1083654	1300566
1516888781	1	2136837.msg28879610	1083697	1300566
1516888586	1	2136837.msg28879610	1086472	1300566
1516887903	1	2136837.msg28879610	964288	1300566
1516886066	1	2020163.msg27487786	1243108	1217555
1516883990	5	1958402.msg25526570	896524	1063960
1516883249	1	1904415.msg26382054	1304759	1217555
1516881815	1	2619577.msg28822776	1217555	1243108
1516871945	5	2667581.msg27954690	1148701	1669358
1516854512	1	903836.msg28866831	1341270	1739265
1516853934	2	2040221.msg28709335	494856	841833

Checking a few of them indeed shows that I counted those lines twice.

[DdmrDdmr]

<...>

Not quite.. The union will not resolve the situation where by a post changes it's message Id on two distinct files (like the 9 cases I stated). The result will be having the same post's merit counted twice for the user (once for the old msgId and once for the new one). That's why we need to play around with the window timeframes of each of the files being integrated.

You’re right about the SQL..  I've tried with various queries and some are postable while others are not. I tried both with and without the "code" header and "/code" footer with the same result.

About a month ago I managed to post SQL without any trouble (https://bitcointalk.org/index.php?topic=3380469.msg35500202#msg35500202). I can still edit and preview that post, so the Cloudfare tries to avoid sql injection but does not seem cover all SQL cases.

[The Sceptical Chymist]

In general I think the forum would be fine even if the window for recording merit transactions was even smaller, since it's so much of a gray area as to whether abusers are going to get tagged.  Much of the tagging that actually has been done was done pretty efficiently (I think) and there have even been several threads dedicated to catching abusers.  So I'm not mourning the loss of that data. 

If it was standard to red-trust these people, that would be a different story--but this is another example of how Theymos has endorsed basically a hands-off approach.

[jeromix]

Which means merits could be send again to the other account of a high rank order to his new made account to rank up easily. I have been observing those few accounts with low rank and gets high merit. And most of them are just from someone that is generous enough giving the merits and which mostly likely we could think that it is his/her second account. And the problem is that i could not see a good post for it to be given by merits. So sad that only those who are earlier who join in forum benefited well on the merit system.

[shilpyh]

This system will be helpful for the people who farm their accounts and also who sell merit. If it stays at-least 1 year then many a farming accounts as well as merit sellers/abusers will be caught. As a result there will be proper use of merit. And then merit abusers will not show dare to abuse their merit.

[Wasii]

This system will be helpful for the people who farm their accounts and also who sell merit. If it stays at-least 1 year then many a farming accounts as well as merit sellers/abusers will be caught. As a result there will be proper use of merit. And then merit abusers will not show dare to abuse their merit.

any link to thread for giveaways merits

[DdmrDdmr]

<...>
any link to thread for giveaways merits

There are no give-away merit threads. There are those that, within a certain range of rules placed by the OP, facilitate users to post their candidate posts to that effect. Before you can even have a chance to get merits you need to browse the forum, use the search function, and invest time into understanding what may be merited and what never will.
I for one will not direct you at this stage to the threads I mentioned, since right now both you and the thread owner would be wasting your time.

By the way, the topic here is the loss of merit history older than 120 days. Your question has nothing to do with the topic and does not build on it. Wrong move. Better to look in the (beg)giner’s section to start with. Meta is not the appropiate starting point.