By looking at some of this data I can see that web crawler events are getting into hive but not into mysql (that would be something for us to fix).
You can see that by comparing data on 2018-09-30T20:30.
The events with app install id:
5979C16C-30DC-4552-8C67-6922E4959796
are from a crawler.

Now, I want yo clarify that both systems are not identical and one of the reasons why we want to encourage users moving to hive for analysis is because the mysql system does not scale, this, in data, might appear as some lost events as restarts of the mysql process (which have happened twice in the period you queried for) could drop a few events.
Also, a burst of traffic from a schema that will send too many requests to mysql can make the consumer get clogged up, die and drop a few events.

And last but not least, persistence to two different SQL engines and two pipelines for validation can run into issues that are particular to one MySQL engine or schema validation pipeline.

@Nuria : Do we know why those events don't get inserted into MySQL?

@JAllemandou we have code that prevents them from being so for self-reported bots: https://phabricator.wikimedia.org/T67508

Thanks for looking into this @Nuria !

In T210006#4766916, @Nuria wrote:

By looking at some of this data I can see that web crawler events are getting into hive but not into mysql (that would be something for us to fix).

Does this (i.e. that T67508 doesn't yet work for the Hive data) apply to all EL schemas? In that case it would seem a valuable addition to the documentation at https://wikitech.wikimedia.org/wiki/Analytics/Systems/EventLogging#Incompatibilities_with_the_MariaDB_setup .

@Nuria & @Tbayer, I looked into this a bit. The is_bot flag is added to the parsed user agent, so all EL events have it. But it's only used to filter out events from being inserted into mysql:

https://github.com/wikimedia/puppet/blob/dcac90f33c775dfdca2fa592adaa33d3e7c83394/modules/eventlogging/files/plugins.py#L146

I can see two possibilities:

1. we did this on purpose and we forgot
2. we made the mysql filter before EventLogging refine -> Hadoop was operational, so we forgot to apply the filter there

Does everyone think we should filter out is_bot events or keep them there and leave the filtering for query time? I don't see what value we could get out of data from bots, so I'd vote to filter them out.

we made the mysql filter before EventLogging refine -> Hadoop was operational, so we forgot to apply the filter there

Yes, this is what happened. To be fair this are only "identified" bots (there is a lot of bot data in EL not identified as such) but yes, they should be filtered out.

Hm, I thought I was onto something with the is_bot flag, because the mysql consumer filters out events when useragent.is_bot is true. But I can't back this up from the existing data, mostly because is_bot seems to always be false now (in all data we have for this schema, only 6 records have is_bot true). I reran the queries with AND not useragent.is_bot anyway, and results are almost always identical. When they're not, my guess is the mysql consumer is dropping events due to restarts (like during the April 1st outage (also see March 7th and 19th)).

We talked about this and we don't think filtering all events by is_bot would be a good idea. We mostly did that in mysql for scalability reasons, because we saw spiky activity from bots that would crash the consumer. But we don't have that problem in Hadoop. So the question is, @chelsyx: can you filter out records with is_bot true when you need? If you almost always would need to do that, maybe we can think of another aproach.

Rerunning the original queries on new data:

Hive:
SELECT Date(CONCAT(year,"-",LPAD(month,2,"0"),"-",LPAD(day,2,"0"))) as date, COUNT(*) 
FROM event.mobilewikiappdailystats
WHERE year=2019 and month > 1 and month < 5
AND revision = 17984412
GROUP BY CONCAT(year,"-",LPAD(month,2,"0"),"-",LPAD(day,2,"0"))
ORDER BY date
LIMIT 1000
;

MariaDB:
select LEFT(timestamp, 8) as date, count(*)
from MobileWikiAppDailyStats_17984412
where LEFT(timestamp, 6) > '201902'
and LEFT(timestamp, 6) < '201905'
group by LEFT(timestamp, 8)
;

Date            Hive        MariaDB
==========      =====       =======
2019-02-01      52136       52136
2019-02-02      60110       60110
2019-02-03      62252       62252
2019-02-04      52997       52997
2019-02-05      51234       51234
2019-02-06      50962       50962
2019-02-07      51165       51165
2019-02-08      52206       52206
2019-02-09      59006       59006
2019-02-10      62538       62538
2019-02-11      53448       53448
2019-02-12      50974       50974
2019-02-13      50734       50734
2019-02-14      49820       49820
2019-02-15      50891       50891
2019-02-16      58675       58675
2019-02-17      60717       60717
2019-02-18      53260       53260
2019-02-19      52155       52155
2019-02-20      50847       50847
2019-02-21      50710       50710
2019-02-22      52407       52407
2019-02-23      59151       59151
2019-02-24      61078       61078
2019-02-25      54601       54601
2019-02-26      50947       50947
2019-02-27      50488       50488
2019-02-28      50016       50016
2019-03-01      51093       51093
2019-03-02      58070       58070
2019-03-03      60225       60225
2019-03-04      52738       52738
2019-03-05      50948       50948
2019-03-06      50625       50625
2019-03-07      50358       50244
2019-03-08      52143       52143
2019-03-09      58497       58497
2019-03-10      60904       60904
2019-03-11      51534       51534
2019-03-12      50881       50881
2019-03-13      50129       50129
2019-03-14      50778       50778
2019-03-15      51118       51118
2019-03-16      57532       57532
2019-03-17      59959       59959
2019-03-18      50468       50468
2019-03-19      50230       29706
2019-03-20      50401       50401
2019-03-21      52442       52442
2019-03-22      51079       51079
2019-03-23      57270       57270
2019-03-24      59547       59547
2019-03-25      51267       51267
2019-03-26      50061       50061
2019-03-27      50062       50062
2019-03-28      49490       49490
2019-03-29      49907       49907
2019-03-30      55896       55896
2019-03-31      58502       58502
2019-04-01      49570       49110
2019-04-02      49193       49190
2019-04-03      49628       49628
2019-04-04      49486       49486
2019-04-05      50795       50795
2019-04-06      55728       55728
2019-04-07      58317       58317
2019-04-08      50281       50281

@chelsyx since you can filter the data with the is_bot tag, we're going to close this ticket unless you have any objections.

@fdans No objection from me. Thank you for looking into this issue!