Page MenuHomePhabricator

Change length of userAgent column on EL tables
Closed, ResolvedPublic0 Story Points

Description

We recently wanted to deploy a change to eventlogging that would insert parsed user Agents in the user agent column on every table of eventlogging database.

This change increases the length of UA data and event_userAgent database column is limited by this setting:

https://github.com/wikimedia/eventlogging/blob/master/eventlogging/jrm.py#L79

Could we:

  1. increase this length
  2. run an alter on all tables so event_userAgent column size is increased?

Setting as priority high but what we need from @jcrespo or @Marostegui is just an answer to whether limit could be increased
we can probably manage to do alters on our end, right ? (cc @Ottomata )

The issue with column length is blocking this ticket: https://phabricator.wikimedia.org/T153207

Details

Related Gerrit Patches:
analytics/discovery-stats : masterAdding renamed tables to sql union statements
analytics/limn-flow-data : masterAdding renamed tables to sql union statements
analytics/limn-edit-data : masterAdding renamed tables to sql union statements
eventlogging : masterBumping up length of varchar columns

Event Timeline

Nuria created this task.Mar 14 2017, 6:56 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMar 14 2017, 6:56 PM
Nuria triaged this task as High priority.Mar 14 2017, 6:58 PM
Nuria updated the task description. (Show Details)
Nuria added subscribers: Marostegui, jcrespo.

Why not deploy a new schema instead? Are the already inserted user agents going to change?

Nuria updated the task description. (Show Details)Mar 14 2017, 6:59 PM
Nuria updated the task description. (Show Details)
Nuria added a comment.Mar 14 2017, 7:12 PM

Are the already inserted user agents going to change?

no

Why not deploy a new schema instead?

A new schema would not change length of column as that is hardcoded on db so we would have this same problem, other than alter would need to run in a smaller set of tables, newly created.

Changing capsule schema for all schemas is problematic as is a change that is non backwards compatible (can explain why that is)
Have in mind that there are two schemas in EL: 1) the capsule (common to all events, describes columns that are there at all times, https://meta.wikimedia.org/wiki/Schema:EventCapsule) and 2)the particular event schema. The userAgent is described on the capsule schema.

other than alter would need to run in a smaller set of tables, newly created.

For me that would be a huge win- it could be deployed in seconds, rather than weeks.

Changing capsule schema for all schemas is problematic

That seems like a limitation on how the application works, not the storage (EL is supposed to be append-only).

I will check what a "schema change of all user agent column on every table of eventlogging database" would entail, but you seem to be asking for a multi-month project changing TB of data and blocking insertion of new events, just to avoid some extra coding. Nothing against it if you take care of doing it and handling potential replication breaking, I will just warn you after I do some quick testing what to see what it would actually take in time and resources, and its dangers.

Nuria added a comment.EditedMar 14 2017, 7:55 PM

other than alter would need to run in a smaller set of tables, newly created.

Sorry, I did not explain this well-enough. A capsule change would not help little in this case but let me explain why that is: the tables are not versioned according to capsule schema but rather 'event schema' thus changing capsule in this case would involve having to issue a bunch of database statements . We would basically need to rename all the tables from, say, EventName_1234 to EventName_1234_old and have new tables be automatically created (as new records come in) as Eventname_1234. These new tables will hold a column for event_userAgent with a different length. That would work too but it seems more work on the database end that doing alters on the tables that are currently used (only those, not all of them). If that is not the case let me know.

Please let us know if you think this is still months of work, as i said we can stop system, alter tables currently used and start system again. I am not sure as to how long would alters need to run though.

Thanks for the prompt response.

Nuria raised the priority of this task from High to Needs Triage.Mar 14 2017, 10:39 PM
Nuria moved this task from Next Up to In Progress on the Analytics-Kanban board.
Nuria moved this task from In Progress to In Code Review on the Analytics-Kanban board.
Nuria added a comment.Mar 15 2017, 3:37 PM

ping @jcrespo Is the solution of renaming tables easier on the dabase end? that would work great for us too. Please let us know.

jcrespo added a comment.EditedMar 15 2017, 3:38 PM

Not sure all details of that-but yes, renaming a table is an instant operation, doing a schema change can take up to a week per table and server.

The schema changes do not have to be that bad technology-wise, but this was designed with a very constrained design where nothing would be changed after it has data. Storing JSON + virtual columns for indexing would have been a better option, which allow arbitrary content dynamically, while still allowing fast indexed search: http://mysqlserverteam.com/indexing-json-documents-via-virtual-columns/

Nuria added a comment.Mar 15 2017, 3:52 PM

Excellent, let us know what you think is a good time on your end to do this and we will take an outage accordingly. For us, the sooner the better. Ideally (i think) we might not want to do all tables at the same time as it means that all tables would need to be re-created at the same time and this sounds like it might cause problems but again, not sure, it might not matter so much. Let us know what you think is the best way to proceed. Rough set of setps (cc @Ottomata for double checking)
And, again, this apply only to active tables not to all of them.

  1. stop EL
  2. rename active tables from Schema_version to Schema_version_ 15423246 (15423246 is capsule vs)
  3. change https://github.com/wikimedia/eventlogging/blob/master/eventlogging/jrm.py#L79 for length of varchars to what? (@jcrespo to advice)
  4. restart eventlogging, new tables should be created for incoming data with new column length

change https://github.com/wikimedia/eventlogging/blob/master/eventlogging/jrm.py#L79 for length of varchars to what? (@jcrespo to advice)

FYI, the comment on this line says something about setting the length to avoid problems with index field length limits. I don't think we have any indexes on userAgent, and afaik indexes on any varchar fields in EL tables would have to be manually added (right?), and I personally don't know of any we do that for these days.

So, could we just increase this limit to something like 1024, and then never add indexes on varchar columns?

Nuria added a comment.Mar 16 2017, 3:08 PM

ping @jcrespo. Could you give us an ETA on when we could start doing these changes?

Marostegui added a comment.EditedMar 16 2017, 3:11 PM

Excellent, let us know what you think is a good time on your end to do this and we will take an outage accordingly. For us, the sooner the better. Ideally (i think) we might not want to do all tables at the same time as it means that all tables would need to be re-created at the same time and this sounds like it might cause problems but again, not sure, it might not matter so much. Let us know what you think is the best way to proceed. Rough set of setps (cc @Ottomata for double checking)
And, again, this apply only to active tables not to all of them.

  1. stop EL
  2. rename active tables from Schema_version to Schema_version_ 15423246 (15423246 is capsule vs)

Could you give us the exact list of tables that would need to be renamed?
We'd need to rename them on both, master (db1046) and slaves (db1047 and dbstore1002)

  1. change https://github.com/wikimedia/eventlogging/blob/master/eventlogging/jrm.py#L79 for length of varchars to what? (@jcrespo to advice)

That is more up to you guys than to us really, as @Ottomata mentioned, 1024 is fine by us I think.

  1. restart eventlogging, new tables should be created for incoming data with new column length

Thanks!

Nuria added a comment.Mar 16 2017, 3:14 PM

Could you give us the exact list of tables that would need to be renamed?

Yes, will compile today and post here.

Could you give us the exact list of tables that would need to be renamed?

Yes, will compile today and post here.

Great - thanks!

Nuria added a comment.Mar 16 2017, 8:34 PM

List of tables:

CentralAuth_5690875
ChangesListFilters_16174591
ChangesListFilters_16403617
CommandInvocation_15243810
ContentTranslation_11628043
ContentTranslationCTA_16017678
ContentTranslationError_11767097
ContentTranslationSuggestion_16344900
CookieBlock_16241436
EchoInteraction_15823738
EchoMail_5467650
Edit_13457736
EditConflict_8860941
EditorActivation_14208837
ExternalLinksChange_15716074
FlowReplies_10561344
GeoFeatures_12914994
GettingStartedRedirectImpression_7355552
GuidedTourButtonClick_13869649
GuidedTourExited_8690566
GuidedTourExternalLinkActivation_8690560
GuidedTourGuiderHidden_8690549
GuidedTourGuiderImpression_8694395
GuidedTourInternalLinkActivation_8690553
ImageMetricsCorsSupport_11686678
Kartographer_16132745
MediaViewer_10867062
MediaWikiPingback_15781718
MobileAppCategorizationAttempts_5359208
MobileAppLoginAttempts_5257721
MobileAppShareAttempts_5346170
MobileAppUploadAttempts_5334329
MobileOptionsTracking_14003392
MobileWebLanguageSwitcher_15302503
MobileWebMainMenuClickTracking_11568715
MobileWebSearch_12054448
MobileWebUIClickTracking_10742159
MobileWikiAppAppearanceSettings_10375462
MobileWikiAppArticleSuggestions_10590869
MobileWikiAppArticleSuggestions_11448426
MobileWikiAppArticleSuggestions_12443791
MobileWikiAppArticleSuggestions_15302212
MobileWikiAppCreateAccount_8240702
MobileWikiAppCreateAccount_9135391
MobileWikiAppDailyStats_12637385
MobileWikiAppEdit_16256587
MobileWikiAppEdit_9003125
MobileWikiAppFeed_15734713
MobileWikiAppFindInPage_14586774
MobileWikiAppInstallReferrer_12601905
MobileWikiAppIntents_15237384
MobileWikiAppLangSelect_12588733
MobileWikiAppLinkPreview_12143205
MobileWikiAppLinkPreview_14095177
MobileWikiAppLinkPreview_15730939
MobileWikiAppLogin_8234533
MobileWikiAppLogin_9135390
MobileWikiAppMediaGallery_10923135
MobileWikiAppMediaGallery_12588701
MobileWikiAppNavMenu_12732211
MobileWikiAppOnboarding_9123466
MobileWikiAppPageScroll_14591606
MobileWikiAppProtectedEditAttempt_8682497
MobileWikiAppReadingLists_15520526
MobileWikiAppSavedPages_10375480
MobileWikiAppSearch_10641988
MobileWikiAppSearch_15729321
MobileWikiAppSessions_14031591
MobileWikiAppSessions_15522505
MobileWikiAppShareAFact_11331974
MobileWikiAppShareAFact_12588711
MobileWikiAppStuffHappens_8955468
MobileWikiAppTabs_12453651
MobileWikiAppToCInteraction_10375484
MobileWikiAppToCInteraction_11014396
MobileWikiAppToCInteraction_14585319
MobileWikiAppWidgets_11312870
MobileWikiAppWiktionaryPopup_15158116
MultimediaViewerAttribution_9758179
MultimediaViewerDimensions_10014238
MultimediaViewerDuration_10427980
MultimediaViewerNetworkPerformance_15573630
MultimediaViewerVersusPageFilePerformance_7907636
NavigationTiming_15485142
NavigationTiming_16305090
NewEditorEdit_6792669
PageContentSaveComplete_5588433
PageCreation_7481635
PageDeletion_7481655
PageMove_7495717
PageRestoration_7758372
Popups_16208085
Popups_16364296
PrefUpdate_5563398
ReadingDepth_16325045
RelatedArticles_14496900
RelatedArticles_16352530
SaveTiming_15396492
ServerSideAccountCreation_5487345
TestSearchSatisfaction2_15357244
TestSearchSatisfaction2_15700292
TestSearchSatisfaction2_15922352
TestSearchSatisfaction2_16270835
TranslationRecommendationAPIRequests_16261139
TranslationRecommendationUIRequests_15484897
TranslationRecommendationUserAction_15858947
UniversalLanguageSelector_7327441
UploadWizardErrorFlowEvent_11772725
UploadWizardExceptionFlowEvent_11772722
UploadWizardFlowEvent_11772723
UploadWizardStep_11772724
UploadWizardTutorialActions_5803466
UploadWizardUploadFlowEvent_11772717
WikimediaBlogVisit_5308166
WikipediaPortal_14377354
WikipediaPortal_15890769
WikipediaZeroUsage_14574251

Nuria added a comment.Mar 16 2017, 8:35 PM

We will need to:

  • send notice to users
  • update automated scripts

Change 343122 had a related patch set uploaded (by Nuria):
[eventlogging] Bumping up length of varchar columns

https://gerrit.wikimedia.org/r/343122

Marostegui added a comment.EditedMar 17 2017, 9:22 AM

Thanks for the list of tables.
From the DBA side, this would be the only thing to execute really (assuming you just want to add the "_ 15423246" to the and as you said at: T160454#3106339) on all the hosts (db1046,db1047 and dbstore1002) once you have stopped EL, it shouldn't take more than a few seconds:

1root@db1046:/home/marostegui# for i in `cat tables_to_rename.txt`; do echo "mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table $i to ${i}_15423246;'";done
2mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table CentralAuth_5690875 to CentralAuth_5690875_15423246;'
3mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table ChangesListFilters_16174591 to ChangesListFilters_16174591_15423246;'
4mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table ChangesListFilters_16403617 to ChangesListFilters_16403617_15423246;'
5mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table CommandInvocation_15243810 to CommandInvocation_15243810_15423246;'
6mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table ContentTranslation_11628043 to ContentTranslation_11628043_15423246;'
7mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table ContentTranslationCTA_16017678 to ContentTranslationCTA_16017678_15423246;'
8mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table ContentTranslationError_11767097 to ContentTranslationError_11767097_15423246;'
9mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table ContentTranslationSuggestion_16344900 to ContentTranslationSuggestion_16344900_15423246;'
10mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table CookieBlock_16241436 to CookieBlock_16241436_15423246;'
11mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table EchoInteraction_15823738 to EchoInteraction_15823738_15423246;'
12mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table EchoMail_5467650 to EchoMail_5467650_15423246;'
13mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table Edit_13457736 to Edit_13457736_15423246;'
14mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table EditConflict_8860941 to EditConflict_8860941_15423246;'
15mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table EditorActivation_14208837 to EditorActivation_14208837_15423246;'
16mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table ExternalLinksChange_15716074 to ExternalLinksChange_15716074_15423246;'
17mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table FlowReplies_10561344 to FlowReplies_10561344_15423246;'
18mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table GeoFeatures_12914994 to GeoFeatures_12914994_15423246;'
19mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table GettingStartedRedirectImpression_7355552 to GettingStartedRedirectImpression_7355552_15423246;'
20mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table GuidedTourButtonClick_13869649 to GuidedTourButtonClick_13869649_15423246;'
21mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table GuidedTourExited_8690566 to GuidedTourExited_8690566_15423246;'
22mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table GuidedTourExternalLinkActivation_8690560 to GuidedTourExternalLinkActivation_8690560_15423246;'
23mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table GuidedTourGuiderHidden_8690549 to GuidedTourGuiderHidden_8690549_15423246;'
24mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table GuidedTourGuiderImpression_8694395 to GuidedTourGuiderImpression_8694395_15423246;'
25mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table GuidedTourInternalLinkActivation_8690553 to GuidedTourInternalLinkActivation_8690553_15423246;'
26mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table ImageMetricsCorsSupport_11686678 to ImageMetricsCorsSupport_11686678_15423246;'
27mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table Kartographer_16132745 to Kartographer_16132745_15423246;'
28mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MediaViewer_10867062 to MediaViewer_10867062_15423246;'
29mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MediaWikiPingback_15781718 to MediaWikiPingback_15781718_15423246;'
30mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileAppCategorizationAttempts_5359208 to MobileAppCategorizationAttempts_5359208_15423246;'
31mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileAppLoginAttempts_5257721 to MobileAppLoginAttempts_5257721_15423246;'
32mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileAppShareAttempts_5346170 to MobileAppShareAttempts_5346170_15423246;'
33mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileAppUploadAttempts_5334329 to MobileAppUploadAttempts_5334329_15423246;'
34mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileOptionsTracking_14003392 to MobileOptionsTracking_14003392_15423246;'
35mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWebLanguageSwitcher_15302503 to MobileWebLanguageSwitcher_15302503_15423246;'
36mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWebMainMenuClickTracking_11568715 to MobileWebMainMenuClickTracking_11568715_15423246;'
37mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWebSearch_12054448 to MobileWebSearch_12054448_15423246;'
38mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWebUIClickTracking_10742159 to MobileWebUIClickTracking_10742159_15423246;'
39mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppAppearanceSettings_10375462 to MobileWikiAppAppearanceSettings_10375462_15423246;'
40mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppArticleSuggestions_10590869 to MobileWikiAppArticleSuggestions_10590869_15423246;'
41mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppArticleSuggestions_11448426 to MobileWikiAppArticleSuggestions_11448426_15423246;'
42mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppArticleSuggestions_12443791 to MobileWikiAppArticleSuggestions_12443791_15423246;'
43mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppArticleSuggestions_15302212 to MobileWikiAppArticleSuggestions_15302212_15423246;'
44mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppCreateAccount_8240702 to MobileWikiAppCreateAccount_8240702_15423246;'
45mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppCreateAccount_9135391 to MobileWikiAppCreateAccount_9135391_15423246;'
46mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppDailyStats_12637385 to MobileWikiAppDailyStats_12637385_15423246;'
47mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppEdit_16256587 to MobileWikiAppEdit_16256587_15423246;'
48mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppEdit_9003125 to MobileWikiAppEdit_9003125_15423246;'
49mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppFeed_15734713 to MobileWikiAppFeed_15734713_15423246;'
50mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppFindInPage_14586774 to MobileWikiAppFindInPage_14586774_15423246;'
51mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppInstallReferrer_12601905 to MobileWikiAppInstallReferrer_12601905_15423246;'
52mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppIntents_15237384 to MobileWikiAppIntents_15237384_15423246;'
53mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppLangSelect_12588733 to MobileWikiAppLangSelect_12588733_15423246;'
54mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppLinkPreview_12143205 to MobileWikiAppLinkPreview_12143205_15423246;'
55mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppLinkPreview_14095177 to MobileWikiAppLinkPreview_14095177_15423246;'
56mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppLinkPreview_15730939 to MobileWikiAppLinkPreview_15730939_15423246;'
57mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppLogin_8234533 to MobileWikiAppLogin_8234533_15423246;'
58mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppLogin_9135390 to MobileWikiAppLogin_9135390_15423246;'
59mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppMediaGallery_10923135 to MobileWikiAppMediaGallery_10923135_15423246;'
60mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppMediaGallery_12588701 to MobileWikiAppMediaGallery_12588701_15423246;'
61mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppNavMenu_12732211 to MobileWikiAppNavMenu_12732211_15423246;'
62mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppOnboarding_9123466 to MobileWikiAppOnboarding_9123466_15423246;'
63mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppPageScroll_14591606 to MobileWikiAppPageScroll_14591606_15423246;'
64mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppProtectedEditAttempt_8682497 to MobileWikiAppProtectedEditAttempt_8682497_15423246;'
65mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppReadingLists_15520526 to MobileWikiAppReadingLists_15520526_15423246;'
66mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppSavedPages_10375480 to MobileWikiAppSavedPages_10375480_15423246;'
67mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppSearch_10641988 to MobileWikiAppSearch_10641988_15423246;'
68mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppSearch_15729321 to MobileWikiAppSearch_15729321_15423246;'
69mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppSessions_14031591 to MobileWikiAppSessions_14031591_15423246;'
70mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppSessions_15522505 to MobileWikiAppSessions_15522505_15423246;'
71mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppShareAFact_11331974 to MobileWikiAppShareAFact_11331974_15423246;'
72mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppShareAFact_12588711 to MobileWikiAppShareAFact_12588711_15423246;'
73mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppStuffHappens_8955468 to MobileWikiAppStuffHappens_8955468_15423246;'
74mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppTabs_12453651 to MobileWikiAppTabs_12453651_15423246;'
75mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppToCInteraction_10375484 to MobileWikiAppToCInteraction_10375484_15423246;'
76mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppToCInteraction_11014396 to MobileWikiAppToCInteraction_11014396_15423246;'
77mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppToCInteraction_14585319 to MobileWikiAppToCInteraction_14585319_15423246;'
78mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppWidgets_11312870 to MobileWikiAppWidgets_11312870_15423246;'
79mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MobileWikiAppWiktionaryPopup_15158116 to MobileWikiAppWiktionaryPopup_15158116_15423246;'
80mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MultimediaViewerAttribution_9758179 to MultimediaViewerAttribution_9758179_15423246;'
81mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MultimediaViewerDimensions_10014238 to MultimediaViewerDimensions_10014238_15423246;'
82mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MultimediaViewerDuration_10427980 to MultimediaViewerDuration_10427980_15423246;'
83mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MultimediaViewerNetworkPerformance_15573630 to MultimediaViewerNetworkPerformance_15573630_15423246;'
84mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table MultimediaViewerVersusPageFilePerformance_7907636 to MultimediaViewerVersusPageFilePerformance_7907636_15423246;'
85mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table NavigationTiming_15485142 to NavigationTiming_15485142_15423246;'
86mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table NavigationTiming_16305090 to NavigationTiming_16305090_15423246;'
87mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table NewEditorEdit_6792669 to NewEditorEdit_6792669_15423246;'
88mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table PageContentSaveComplete_5588433 to PageContentSaveComplete_5588433_15423246;'
89mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table PageCreation_7481635 to PageCreation_7481635_15423246;'
90mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table PageDeletion_7481655 to PageDeletion_7481655_15423246;'
91mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table PageMove_7495717 to PageMove_7495717_15423246;'
92mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table PageRestoration_7758372 to PageRestoration_7758372_15423246;'
93mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table Popups_16208085 to Popups_16208085_15423246;'
94mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table Popups_16364296 to Popups_16364296_15423246;'
95mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table PrefUpdate_5563398 to PrefUpdate_5563398_15423246;'
96mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table ReadingDepth_16325045 to ReadingDepth_16325045_15423246;'
97mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table RelatedArticles_14496900 to RelatedArticles_14496900_15423246;'
98mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table RelatedArticles_16352530 to RelatedArticles_16352530_15423246;'
99mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table SaveTiming_15396492 to SaveTiming_15396492_15423246;'
100mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table ServerSideAccountCreation_5487345 to ServerSideAccountCreation_5487345_15423246;'
101mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table TestSearchSatisfaction2_15357244 to TestSearchSatisfaction2_15357244_15423246;'
102mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table TestSearchSatisfaction2_15700292 to TestSearchSatisfaction2_15700292_15423246;'
103mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table TestSearchSatisfaction2_15922352 to TestSearchSatisfaction2_15922352_15423246;'
104mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table TestSearchSatisfaction2_16270835 to TestSearchSatisfaction2_16270835_15423246;'
105mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table TranslationRecommendationAPIRequests_16261139 to TranslationRecommendationAPIRequests_16261139_15423246;'
106mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table TranslationRecommendationUIRequests_15484897 to TranslationRecommendationUIRequests_15484897_15423246;'
107mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table TranslationRecommendationUserAction_15858947 to TranslationRecommendationUserAction_15858947_15423246;'
108mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table UniversalLanguageSelector_7327441 to UniversalLanguageSelector_7327441_15423246;'
109mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table UploadWizardErrorFlowEvent_11772725 to UploadWizardErrorFlowEvent_11772725_15423246;'
110mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table UploadWizardExceptionFlowEvent_11772722 to UploadWizardExceptionFlowEvent_11772722_15423246;'
111mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table UploadWizardFlowEvent_11772723 to UploadWizardFlowEvent_11772723_15423246;'
112mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table UploadWizardStep_11772724 to UploadWizardStep_11772724_15423246;'
113mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table UploadWizardTutorialActions_5803466 to UploadWizardTutorialActions_5803466_15423246;'
114mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table UploadWizardUploadFlowEvent_11772717 to UploadWizardUploadFlowEvent_11772717_15423246;'
115mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table WikimediaBlogVisit_5308166 to WikimediaBlogVisit_5308166_15423246;'
116mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table WikipediaPortal_14377354 to WikipediaPortal_14377354_15423246;'
117mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table WikipediaPortal_15890769 to WikipediaPortal_15890769_15423246;'
118mysql --skip-ssl log -e 'set session sql_log_bin=0; rename table WikipediaZeroUsage_14574251 to WikipediaZeroUsage_14574251_15423246;'

Marostegui moved this task from Triage to Next on the DBA board.Mar 17 2017, 10:01 AM
Nuria added a comment.Mar 20 2017, 4:52 PM

@Marostegui I think @otto can run script in our end, let us know if that is OK with you and we will take a small outage and run script tomorrow

jcrespo added a comment.EditedMar 20 2017, 4:55 PM

@Nuria, Marostegui is out today- if you can wait, I would wait one extra day to be sure he can make it.

Sorry folks, am I reading this correctly: All active EL tables will be renamed and replaced by the new version (with the parsed UAs)? That would be a pretty disruptive change, which at the very least should be announced beforehand to minimize confusion about unexpected results and give people time to prepare. Not afterwards. I'm sending a followup in the existing Analytics-l thread now.

Nuria added a comment.Mar 20 2017, 6:25 PM

That would be a pretty disruptive change, which at the very least should be announced beforehand to minimize confusion about unexpected results and give people time to prepare.

It will be announced before hand, we still do not know when are we going to be able to do it or who is going to do it. We will announce before it happens.

I'm sending a followup in the existing Analytics-l thread now.

There is no existing thread for this work as afar as i know.

That would be a pretty disruptive change, which at the very least should be announced beforehand to minimize confusion about unexpected results and give people time to prepare.

It will be announced before hand, we still do not know when are we going to be able to do it or who is going to do it. We will announce before it happens.

I'm sending a followup in the existing Analytics-l thread now.

Sent: https://lists.wikimedia.org/pipermail/analytics/2017-March/005795.html

There is no existing thread for this work as afar as i know.

I was referring to the thread that led to the creation of T153207 . But yeah, it would have been good to send further updates there as the work progressed, also about the decision to remove raw UAs entirely (i.e. the redefinition of T153207).

@Marostegui I think @otto can run script in our end, let us know if that is OK with you and we will take a small outage and run script tomorrow

That is fine by me. I have changed a bit the "for" loop, to include set session sql_log_bin=0 to avoid inserting this on the binlog (although it should not really matters as those hosts do not have slaves, but just in case), it is on this comment: T160454#3109219

The file tables_to_rename.txt is present on /home/marostegui on all the hosts, db1046, db1047 and dbstore1002.

Nuria added a comment.Mar 21 2017, 3:25 PM

Ok, will be sending e-mail today with the change we are going and we can plan on doing it Thursday (48 hrs notice)

Change 343122 merged by Ottomata:
[eventlogging] Bumping up length of varchar columns

https://gerrit.wikimedia.org/r/343122

Ok, will be sending e-mail today with the change we are going and we can plan on doing it Thursday (48 hrs notice)

Great, thanks! (For later reference, this announcement thread is here.)

BTW, I assume we are going to update https://meta.wikimedia.org/wiki/Schema:EventCapsule beforehand too?

Nuria added a comment.Mar 21 2017, 7:52 PM

BTW, I assume we are going to update https://meta.wikimedia.org/wiki/Schema:EventCapsule beforehand too?

We will be doing that once this work is finished, there is no need to do it before,

Change 344049 had a related patch set uploaded (by Nuria):
[analytics/discovery-stats] Adding renamed tables to sql union statements

https://gerrit.wikimedia.org/r/344049

Change 344054 had a related patch set uploaded (by Nuria):
[analytics/limn-edit-data] Adding renamed tables to sql union statements

https://gerrit.wikimedia.org/r/344054

Change 344055 had a related patch set uploaded (by Nuria):
[analytics/limn-flow-data] Adding renamed tables to sql union statements

https://gerrit.wikimedia.org/r/344055

Hi!
What's the status of this?

Happening today! :)

Mentioned in SAL (#wikimedia-operations) [2017-03-29T16:53:01Z] <marostegui> Disable puppet on db1047 and dbstore1002 for maintenance - T160454

Mentioned in SAL (#wikimedia-operations) [2017-03-29T16:55:52Z] <marostegui> Stop eventlog syncs to db1047 and dbstore1002 for maintenance - T160454

Change 344054 merged by Nuria:
[analytics/limn-edit-data@master] Adding renamed tables to sql union statements

https://gerrit.wikimedia.org/r/344054

Change 344055 merged by Nuria:
[analytics/limn-flow-data@master] Adding renamed tables to sql union statements

https://gerrit.wikimedia.org/r/344055

Change 344049 merged by Milimetric:
[analytics/discovery-stats@master] Adding renamed tables to sql union statements

https://gerrit.wikimedia.org/r/344049

The eventlogging script on db1047 is failing due to:

Thu Mar 30 06:07:49 UTC 2017 localhost ContentTranslationError_11767097, createERROR 1005 (HY000) at line 19: Can't create table `log`.`ContentTranslationError_11767097` (errno: 24 "Too many open files")

The script on dbstore1002 works fine, where the values are a lot higher:

root@DBSTORE[(none)]> select @@hostname;
+-------------+
| @@hostname  |
+-------------+
| dbstore1002 |
+-------------+
1 row in set (0.00 sec)

root@DBSTORE[(none)]> show global variables like 'open_files%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| open_files_limit | 400000 |
+------------------+--------+
1 row in set (0.00 sec)

I wasn't even able to run lsof on db1047, it was stuck (probably due to the rename yesterday).
What I have done to fix the issue:

  • Stop sync script on terbium
  • Stop all slaves
  • Stop mysql
  • Start mysql
  • Start script on terbium
  • Run puppet (and check that the scripts starts fine locally
root     27866  0.0  0.0  43824  1800 ?        S    06:21   0:00 sudo -u root /usr/local/bin/eventlogging_sync.sh
root     27870  1.2  0.0  12572  1620 ?        S    06:21   0:01 /bin/bash /usr/local/bin/eventlogging_sync.sh
  • Start all slaves

@Nuria feel free to close this ticket if you believe the scope for this task is done.
Thanks!

Mentioned in SAL (#wikimedia-operations) [2017-03-30T06:25:41Z] <marostegui> Logging backwards for the record: restart mysql on db1047 for maintenance - T160454

Should we increase open_files_limit or do you think this was a one-time issue due to the rename process?

I thought about it, but it has not happened until we did the rename thingy yesterday. So I would leave it for now.

Wow, ok. Thanks.

Nuria set the point value for this task to 0.Mar 30 2017, 2:56 PM
Nuria closed this task as Resolved.Apr 3 2017, 4:37 PM