[01:24:59] dpifke: looks like xhgui does not display the year anywhere, is that right? https://performance.wikimedia.org/xhgui/run/view?id=5c49dd41bb85442a0520149d [01:26:14] Appears not. [01:26:37] I suspect the top entries are HHVM [01:28:33] .. and it's running backwards / chronologically [01:28:58] .. and without a pager limit? [01:29:42] the latter I see in beta now as well, but not the former. in beta the order is correctly desc [01:30:37] The problem is that request_ts came out in seconds when I converted beta, and milliseconds when I convert prod. Dunno if that was due to different MongoDB versions, different Python versions, or what, but I didn't notice until too late. [01:31:35] Mysql truncated the timestamps to MAX_INT on import, so new ones are appearing in reverse chonological order, after the imported ones. [01:32:11] It's an easy data fix, but we didn't ask for UPDATE privileges for the xhgui database user, thinking the data is immutable. [01:36:12] dpifke: hm.. so the schema ended up differently? [01:36:20] blob vs longblog/longtext [01:37:32] profile column needs to be longtext, that was a separate issue due to me manually running CREATE TABLE before running import. [01:37:44] ok [01:38:08] And I specified longtext in CREATE TABLE and it's showing longblob in DESCRIBE, so I think that's just a Mysql thing? [01:38:18] yeah, we prefer bytes [01:38:33] I don't know exactly where/how that is enforced, but either we do it or maria by default [01:38:44] thats fine indeed [01:39:02] Shouldn't make a difference, as I don't think JSON will already escape any non-ASCII. [01:39:10] s/don't// [01:40:39] I'm not fully getting the impact on the date trimming. [01:40:56] Does that mean all recent dates are trimmed to a past date that we surpassed in epoch? [01:42:30] All old dates (2020-08-11 or before) have request_ts = MAX_INT. [01:42:56] assuming 32bit, that's somewhere in 2038 right? [01:44:09] 01/19/2038 [01:45:17] Hnm.. but the last entry is Dec 21 2018 [01:46:03] The sort uses request_ts, the date column comes from request_date. Latter is stored as ISO string. [01:46:18] are the maria grants in puppet and/or is there a pre-existing admin user that exists to help us out [01:46:29] The info is also duplicated into request_ts_micro, which also ran out of precision on import due to the same issue. [01:46:34] we could naively re-import some/all [01:46:57] We also don't have DELETE privileges. [01:47:13] I think we probably want to ask for an admin user with UPDATE, DELETE, and ALTER TABLE. [01:47:17] yeah, but someone other than us could more easily delete/truncate without knowing much about the data [01:47:21] right [01:47:34] oh, right so the schema isn't fixed yet [01:47:40] makes sense [01:48:12] k.ormat helped me fix the type on the profile column, which solved the initial issue you helped me diagnose. [01:48:22] cool [01:50:26] Then I re-imported and still didn't catch the request_ts issue. I would have expected an error for a column out of range, instead of silently truncated values. [01:50:48] there's a mysql strict mode [01:51:00] see also php, see also, everything sucks [01:51:05] Interesting, I should turn that on. [01:51:29] I think we're "just about" to turn that on for MW by default "soon" [01:51:42] I think it's a connection-level thing, but not sure [01:51:53] I hope there's also a way to enforce it on a database or table level [01:53:05] Looks like it can be set in .my.cnf, which would have prevented the extant issue. [01:54:27] dpifke: btw, what's the eaisest way to see the difference between our last upstream HEAD and actual package? [01:55:27] Diff wmf branch and upstream branch. [01:56:09] Hm.. I see. So we merge upstream into 'wmf' upon update? [01:56:12] and master? [01:56:17] Each commit on wmf gets collapsed into a patch on master in debian/patches dir. git-buildpackage enforces that the only differences between master and upstream are in the debian subdirectory. [01:57:00] aha, so I can rely on the patch files in master to be conclusive / flattening of all that. [01:57:02] neat [01:57:32] I wasn't sure when master is modified normally vs via patch files. makes sense now [01:58:10] Yup. It's designed (by Debian) to be compatible with non-Git-based schemes for patching upstream. [01:58:56] puppet says we do have DELETE [01:59:00] not sure we shouldd, but we do [01:59:52] there isn't a generic admin user that we'd be given access to, so an xhgui-admin user of sorts seems fair (for future ref) [02:00:16] Ah, that would let me delete where request_ts = MAX_INT and re-import. I'll do that. [02:00:22] ack [02:01:08] dpifke: I'm still confused as to why those entries sort on top if they don't have the highest date. It seems all three date fields reflect the 2018 date [02:01:29] I'm guessing max-int here is the max-int in the python code, not the highest value supported in that column? [02:01:32] Yeah, I was thinking xhgui-admin, not general admin. Once it exists, we should probably move DELETE to it. [02:01:41] * Krinkle nods [02:03:05] I think sort is non-deterministic within the bad entries. `SELECT COUNT(*) FROM xhgui WHERE request_ts = 2147483647;` returns 20,030 rows. [02:03:52] Hm.. I see, so mysql just gives them to us in the inserted order, fun. [02:04:02] should the newer entries not be higher than that though? [02:04:07] the ones not imported that is [02:04:15] * Krinkle double checks [02:04:16] Possibly not even the inserted order. I think the underlying storage is free to reorder rows as it sees fit. [02:04:45] yeah, I mean that it happens to return in a seemingly chrionological order, but that's just laziness / chance [02:05:26] Current request_ts is ~1,597,370,701,. [02:06:05] So it's showing followed by . [02:06:10] hm.. ok, so maybe this is a field that isn't actually one of the three that is used in the UI [02:06:30] that would explain why these old entries all appear to have imported correctly but only sort wrongly [02:06:46] Yeah, it uses request_date, at least on the index page. [02:08:03] I definitely wouldn't have designed the schema this way. :) [02:08:44] yeah, for one I'd very likely not want to use native date fields of any kind [02:10:24] dpifke: I can roll out this one now if that doesn't intefere https://gerrit.wikimedia.org/r/c/operations/mediawiki-config/+/620142/ [02:11:18] I'm feeling pretty committed to fixing the current issues rather than rolling back. [02:11:43] And once we turn off writes to Mongo I can do a final dump of the data. [02:23:47] I see, ok, no rush on turning writes off then will wait until it's done. [02:24:10] How long did it take last time? I suppose we can finish that up tomorrow. [02:26:14] Maybe an hour or so, all told. Migration script is running now on my laptop, then I need to upload the results, then pipe them to mysql. I was having internet connection issues on Tuesday which made the middle step the longest part of the process, but seems to be fixed now. [02:29:47] It's not great that we're dumping the entire DB on every index page view, but once the sort is order fixed people can hit stop. [02:35:28] I need to take the dog for a walk and make dinner, then I'll be back on in a little while. [02:37:20] I piped the migration script through pv this time, and it says it's right around ~1,000 entries/minute.