[02:19:09] 10Fundraising Sprint Highway to Hello World, 10Fundraising-Backlog, 10Patch-For-Review: CT-to-Civi-Phase 1 - populate the civi fields - https://phabricator.wikimedia.org/T334534 (10Eileenmcnaughton) We ran a bunch in but it is currently 'stuck' - on staging I set stuck on id: 157852067 contribu... [06:19:44] (03CR) 10CI reject: [V: 04-1] Localisation updates from https://translatewiki.net. [extensions/DonationInterface] (REL1_35) - 10https://gerrit.wikimedia.org/r/910952 (owner: 10L10n-bot) [08:43:42] (03CR) 10Lucas Werkmeister (WMDE): Use IDatabase::setSessionOptions() to set group_concat_max_len (031 comment) [extensions/CentralNotice] - 10https://gerrit.wikimedia.org/r/908572 (https://phabricator.wikimedia.org/T333688) (owner: 10Lucas Werkmeister (WMDE)) [10:47:21] (03CR) 10Nikerabbit: [V: 03+2] Localisation updates from https://translatewiki.net. [extensions/DonationInterface] (REL1_35) - 10https://gerrit.wikimedia.org/r/910952 (owner: 10L10n-bot) [11:56:33] (03CR) 10Ladsgroup: [C: 03+2] Use IDatabase::setSessionOptions() to set group_concat_max_len (031 comment) [extensions/CentralNotice] - 10https://gerrit.wikimedia.org/r/908572 (https://phabricator.wikimedia.org/T333688) (owner: 10Lucas Werkmeister (WMDE)) [11:58:44] (03Merged) 10jenkins-bot: Use IDatabase::setSessionOptions() to set group_concat_max_len [extensions/CentralNotice] - 10https://gerrit.wikimedia.org/r/908572 (https://phabricator.wikimedia.org/T333688) (owner: 10Lucas Werkmeister (WMDE)) [12:22:22] 10Fundraising Tech - Chaos Crew, 10Fundraising-Backlog, 10FR-dlocal, 10MediaWiki-extensions-DonationInterface: "Order id is duplicated" error from dlocal - https://phabricator.wikimedia.org/T334905 (10jgleeson) [14:38:01] 10Fundraising-Backlog, 10Wikimedia-Fundraising-CiviCRM: Civi - Increase limit on email sends to 2000 - https://phabricator.wikimedia.org/T335296 (10AKanji-WMF) [15:21:10] 10Fundraising-Backlog, 10Platform Team Workboards (Clinic Duty Team): add RESTBase to donate wiki - https://phabricator.wikimedia.org/T259309 (10Aklapper) RESTBase is supposed to be removed: #restbase_sunsetting . Can this task be closed as `declined`? [15:38:05] 10fundraising-tech-ops, 10DC-Ops, 10SRE, 10ops-codfw: Q3:rack/setup/install frmon2002 - https://phabricator.wikimedia.org/T334501 (10Jhancock.wm) [15:39:21] 10Fundraising Tech - Chaos Crew, 10Fundraising-Backlog: 'Senior' is not a valid option for field suffix_id', - https://phabricator.wikimedia.org/T335300 (10AnnWF) [15:44:00] 10Fundraising-Backlog, 10Platform Team Workboards (Clinic Duty Team): add RESTBase to donate wiki - https://phabricator.wikimedia.org/T259309 (10Ejegg) 05Open→03Declined OK, let's decline it. The issue we were trying to address here is just that the apps were opening certain wikis and showing broken pages.... [15:47:24] 10Fundraising Tech - Chaos Crew, 10Fundraising-Backlog: 'Senior' is not a valid option for field suffix_id', - https://phabricator.wikimedia.org/T335300 (10Ejegg) @AnnWF this can be can be added in the UI here: https://civicrm.wikimedia.org/civicrm/admin/options/individual_suffix?reset=1 [15:48:48] !log payments-wiki upgraded from 25d867dc to a6288840 [15:48:51] Logged the message at https://wikitech.wikimedia.org/wiki/Server_Admin_Log [15:51:33] 10Fundraising Tech - Chaos Crew, 10Fundraising-Backlog: 'Senior' is not a valid option for field suffix_id', - https://phabricator.wikimedia.org/T335300 (10AnnWF) Thanks Ejegg: added via UI [15:59:27] 10Fundraising Tech - Chaos Crew, 10Fundraising-Backlog, 10FR-dlocal: Recurring donation, but no subscription ID or recurring payment token found. Source: - https://phabricator.wikimedia.org/T335303 (10AnnWF) [16:06:50] (03PS1) 10Wfan: Skip associate existing recurring records for initial upi recurring [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911335 (https://phabricator.wikimedia.org/T335303) [16:24:26] (03PS4) 10Ejegg: Ingenico disentanglement: clean up doPayment [extensions/DonationInterface] - 10https://gerrit.wikimedia.org/r/908670 (https://phabricator.wikimedia.org/T260725) [16:24:28] (03PS1) 10Ejegg: Override all variants for test setup [extensions/DonationInterface] - 10https://gerrit.wikimedia.org/r/911336 [16:26:20] (03CR) 10CI reject: [V: 04-1] Ingenico disentanglement: clean up doPayment [extensions/DonationInterface] - 10https://gerrit.wikimedia.org/r/908670 (https://phabricator.wikimedia.org/T260725) (owner: 10Ejegg) [16:27:20] (03CR) 10Ejegg: "Hmm, we expect to have the contribution_recur_id set when the UPI donation gets to this function. Can you tell if it came in via the new u" [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911335 (https://phabricator.wikimedia.org/T335303) (owner: 10Wfan) [16:39:40] If we have the contribution_recur_id set then it should already skip the recurring record associate part. Hmm so the root cause for those failMails you think is the contribution_recur_id not set? [16:42:48] sorry havent looked into this yet but is this from adding paytm at the top level and we missed some recurring logic for it somewhere? [16:44:33] they all seem to be paytmwallet [16:46:28] ohhh yep it's that [16:46:31] sorry I missed a spot [16:46:54] argh [16:48:00] yeah just checked upis are still getting into contribution recur [16:48:25] OK, so the listener just decides whether to route them to the upi-donations queue by the 'IR' method in the message [16:48:46] so the paytmwallet ones should be routed there too [16:48:52] where are we making the distinction [16:48:53] ? [16:49:11] ohh processDonorReturn [16:51:50] (03PS1) 10Ejegg: Skip queue message for paytm recurring [extensions/DonationInterface] - 10https://gerrit.wikimedia.org/r/911341 (https://phabricator.wikimedia.org/T335303) [16:51:59] sorry wfan ^^^ should fix it [16:52:13] and we can just delete those queue messages from payments-wiki [16:52:25] ahhh i see [16:52:40] we just should have been treating it like upi and skipping them in the first place [16:53:20] hubris, n: thinking one has a full mental model of the payments cluster [16:54:19] Oh the paytm thanks ejegg. [16:54:48] oh, I'll just make that a strict in_array comparison [16:55:19] (03PS2) 10Ejegg: Skip queue message for paytm recurring [extensions/DonationInterface] - 10https://gerrit.wikimedia.org/r/911341 (https://phabricator.wikimedia.org/T335303) [16:59:17] (03CR) 10Wfan: [C: 03+2] "thx!" [extensions/DonationInterface] - 10https://gerrit.wikimedia.org/r/911341 (https://phabricator.wikimedia.org/T335303) (owner: 10Ejegg) [17:03:38] do we need to add paytmwallet here as well? https://phabricator.wikimedia.org/T334942 [17:04:38] wfan yep, we should. Good catch [17:06:36] 10fundraising-tech-ops, 10DC-Ops, 10SRE, 10ops-codfw: Q3:rack/setup/install frmon2002 - https://phabricator.wikimedia.org/T334501 (10Jhancock.wm) [17:11:07] (03PS2) 10Wfan: Add paytmwallet for should update contribution recurring [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911335 (https://phabricator.wikimedia.org/T334942) [17:12:33] (03CR) 10Ejegg: [C: 03+2] "Looks good, and thanks for the method rename!" [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911335 (https://phabricator.wikimedia.org/T334942) (owner: 10Wfan) [17:12:52] (03PS2) 10Ejegg: Override all global prefixes for test setup [extensions/DonationInterface] - 10https://gerrit.wikimedia.org/r/911336 [17:14:25] (03CR) 10CI reject: [V: 04-1] Override all global prefixes for test setup [extensions/DonationInterface] - 10https://gerrit.wikimedia.org/r/911336 (owner: 10Ejegg) [17:15:51] (03PS3) 10Ejegg: Override all global prefixes for test setup [extensions/DonationInterface] - 10https://gerrit.wikimedia.org/r/911336 [17:16:15] fr-tech ^^^ should help us get more consistent results for DonationInterface tests locally [17:16:34] well, it's a start - we could use that new function in a few more places [17:17:35] (03Merged) 10jenkins-bot: Skip queue message for paytm recurring [extensions/DonationInterface] - 10https://gerrit.wikimedia.org/r/911341 (https://phabricator.wikimedia.org/T335303) (owner: 10Ejegg) [17:17:58] wfan I can deploy the payments-wiki fix if you want to deploy the crm one [17:18:47] Ok 😊 [17:18:54] (03PS1) 10Ejegg: Merge branch 'master' into deployment [extensions/DonationInterface] (deployment) - 10https://gerrit.wikimedia.org/r/911344 [17:23:22] will do after docker build finish and then crm merged [17:26:50] (03PS1) 10Wfan: Merge branch 'master' into deployment [wikimedia/fundraising/crm] (deployment) - 10https://gerrit.wikimedia.org/r/911345 [17:27:18] (03CR) 10Wfan: [C: 03+2] Merge branch 'master' into deployment [wikimedia/fundraising/crm] (deployment) - 10https://gerrit.wikimedia.org/r/911345 (owner: 10Wfan) [17:28:04] (03Merged) 10jenkins-bot: Merge branch 'master' into deployment [wikimedia/fundraising/crm] (deployment) - 10https://gerrit.wikimedia.org/r/911345 (owner: 10Wfan) [17:29:07] 10Fundraising Sprint Highway to Hello World, 10Fundraising-Backlog: CT-to-Civi-Phase 1 - expose in the UI - https://phabricator.wikimedia.org/T334540 (10Damilare) This looks cool, I should have checked the dashboard page on production. Adding it to the individual contribution makes sense also. I looked into t... [17:41:59] (03CR) 10Wfan: [C: 03+2] Merge branch 'master' into deployment [extensions/DonationInterface] (deployment) - 10https://gerrit.wikimedia.org/r/911344 (owner: 10Ejegg) [17:51:53] !log payments-wiki upgraded from a6288840 to 2a4c450d [17:51:57] Logged the message at https://wikitech.wikimedia.org/wiki/Server_Admin_Log [17:56:00] thanks, wfan [17:57:38] np, I just deleted those queue messages from damage table also [18:35:40] fr-tech I have one last patch for that new ID Ingenico wants us to start sending by May 1st: https://gerrit.wikimedia.org/r/910577 [18:36:07] turns out I couldn't actually backfill from the logs like I thought [18:36:15] but we can look up those IDs via an API call [18:37:11] so ^^^ is an API action to do it [18:37:42] you can test in dev but unfortunately the IDs are all blank in Ingenico pre-prod [18:38:23] (03CR) 10Ejegg: "To run this:" [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/910577 (https://phabricator.wikimedia.org/T331754) (owner: 10Ejegg) [18:39:01] checking ejegg [18:39:06] thanks jgleeson ! [18:40:09] that API4 explorer's example code generation was super helpful in making that patch [18:40:44] oh right, I wanted to make sure that blank space actually got written to the record [18:41:21] ah yeah, I confirmed that Thursday [18:43:00] yeah I love the api explorer [18:43:15] super handy [18:48:07] (03PS2) 10Wfan: Check recurring if have cancel date before charge [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/910615 (https://phabricator.wikimedia.org/T335152) [18:52:30] ejegg: to test this, can I just create an ingenico recurring donation and run the relevant consumers [18:52:48] the MAX: key has me interested [18:57:20] Also ejegg. should I be seeing the name field on the ingenico form locally? [18:57:24] it's a required field [18:57:59] https://phabricator.wikimedia.org/F36962137 [19:10:52] jgleeson: so yep, you should just be able to create the recurring donation and run the consumers [19:11:24] the MAX thing is pretty weird, but yeah, that's how it addresses the aggregated field from the joined contribution table [19:11:53] I thought we got rid of the name field since they need to collect it now [19:12:23] ah, yeah, right, looking at your screenshot, that's how the Ingenico form is now - the name is down in the card section [19:12:30] we took it out of the top section [19:17:20] ah cool thanks [19:22:04] ejegg: i can't see $batch as an arg [19:22:16] jgleeson: it's kind of magic [19:22:22] do AbstractActions wire it up? [19:22:24] ah [19:22:35] The @method notation creates it [19:22:38] yep [19:23:07] hmm I guess I only have one so it's no biggie but I was wondering why I couldn't see it as an option on the api explorer [19:23:18] ah interesting [19:23:27] https://wmff.localhost:32353/civicrm/api4#/explorer/ContributionRecur/fillSchemeId [19:23:30] it might need a cvapi system.flush to show up there [19:23:37] oh huh [19:23:47] ok, i'm checking locally [19:26:00] Huh, the cancelInactives one looks very similarly written, and its 'Days' param shows up: https://wmff.localhost:32353/civicrm/api4#/explorer/ContributionRecur/cancelInactives [19:26:29] Ah, maybe I need phpdoc for the protected property? [19:26:43] huh [19:26:55] magic has its downsides [19:27:51] aha, yep, I'll push up a version with that phpdoc [19:28:16] (03PS6) 10Ejegg: Fill scheme ID for ingenico recurrings [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/910577 (https://phabricator.wikimedia.org/T331754) [19:28:34] ^^^ and then a system.flush got it to show up in the UI for me jgleeson [19:29:32] checkin [19:41:51] (03PS2) 10Eileen: Add server route for contribution tracking [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/910620 [19:49:25] 10Fundraising Tech - Chaos Crew, 10Fundraising-Backlog, 10FR-dlocal: dlocal recurring issue: contribution_id is not being written to the contribution_tracking record following CiviCRM import - https://phabricator.wikimedia.org/T334942 (10AKanji-WMF) [19:49:27] 10Fundraising Tech - Chaos Crew, 10Fundraising-Backlog, 10FR-dlocal, 10Patch-For-Review: Recurring donation, but no subscription ID or recurring payment token found. Source: - https://phabricator.wikimedia.org/T335303 (10AKanji-WMF) 05Open→03Resolved a:03AKanji-WMF [19:49:58] 10Fundraising Tech - Chaos Crew, 10Fundraising-Backlog: 'Senior' is not a valid option for field suffix_id', - https://phabricator.wikimedia.org/T335300 (10AKanji-WMF) 05Open→03Resolved a:03AKanji-WMF [19:58:21] (03PS1) 10Eileen: Add handling for invalid amounts in contribution_tracking [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911361 (https://phabricator.wikimedia.org/T334534) [20:06:35] 10Fundraising-Backlog, 10Wikimedia-Fundraising-CiviCRM: Clean up old TY CiviMail records - https://phabricator.wikimedia.org/T335070 (10AKanji-WMF) Associated with {T335071} [20:07:19] (03PS2) 10Eileen: Add handling for invalid amounts in contribution_tracking [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911361 (https://phabricator.wikimedia.org/T334534) [20:09:17] 10Fundraising-Backlog: Civi\Api4\Service\Spec\Provider\MappingCreationSpecProvider' not found in CachedCiviContainer - https://phabricator.wikimedia.org/T334966 (10AKanji-WMF) 05Open→03Resolved a:03AKanji-WMF Closing as we believe this only happened as the caches were being cleared [20:11:32] (03PS3) 10Eileen: Add handling for invalid amounts in contribution_tracking [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911361 (https://phabricator.wikimedia.org/T334534) [20:21:59] ejegg: so I'm getting errors when locally calling $provider->getPaymentStatus( $id ); [20:22:20] UNKNOWN_PAYMENT_ID [20:22:52] also, $result[$id] looks like it gets lost as it's declared within the loop, unless more magic? [20:23:15] oh actually I see it passed in [20:23:38] jgleeson: yep yep, that's the way to return an API result in v4 [20:24:08] so UNKNOWN_PAYMENT_ID is with the same payment you just ran in payments-wiki and consumed to Civi? [20:24:34] let me check again that I didn't get that locally [20:25:04] I wonder if there's also a smashpig update I'm missing [20:25:21] I don' [20:25:23] t think so [20:25:33] this is using a SmashPig function we wrote a while back [20:25:39] ah [20:25:42] (and never made a normalized result class for) [20:26:59] 10Fundraising-Backlog: Paypal Recurring trnx does not have payment_token_id for future charge - https://phabricator.wikimedia.org/T334902 (10Ejegg) PayPal recurring transactions are scheduled on PayPal's side rather than charged by us, which is why they can be annual rather than monthly. However, it's not clear... [20:27:04] I'll run through the test steps again here [20:27:38] https://world.preprod.api-ingenico.com/v1/6570/payments/000000657010000013910000100001 [20:27:44] is that the expected API? [20:27:50] endpoint [20:28:01] yep, preprod [20:28:56] - Upgrading wikimedia/smash-pig (v0.8.4.4 => v0.8.4.13): Extracting archive [20:29:14] heh, that's good to do [20:29:18] I think that's fixed the api call [20:29:38] huh, that getPaymentStatus call is an oldie [20:30:03] or... did I do something to it just last wrrk? [20:30:43] hmm looks like that new field isn't available. is this the non-on-test thing? [20:31:04] right, it's not coming back in the responses on preprod [20:31:16] so we should at least see the script stuff that field with '' [20:31:24] so as to move on to the next record [20:31:40] I figure after we run through them all I can go back and delete those useless placeholders [20:31:56] oh right is it NULL now [20:32:20] yep, or just has no row in that custom field table [20:34:50] ok I can see it updating with the empty string [20:35:02] I guess if that field exists we should be good [20:35:14] (03PS1) 10Wfan: Add currency and country match from di [extensions/DonationInterface] - 10https://gerrit.wikimedia.org/r/911386 (https://phabricator.wikimedia.org/T334967) [20:35:20] I just caught the comment about the placeholder [20:35:53] I wonder if you can alias the '->addSelect('MAX(contribution.contribution_extra.gateway_txn_id)')' call [20:36:37] heh yeah, that's a long one [20:36:41] let me see [20:37:21] it's not a blocker [20:37:35] (03CR) 10CI reject: [V: 04-1] Add currency and country match from di [extensions/DonationInterface] - 10https://gerrit.wikimedia.org/r/911386 (https://phabricator.wikimedia.org/T334967) (owner: 10Wfan) [20:37:42] I was clicking through the SelectParamTrait to see [20:38:07] it looks like I can [20:38:44] (03PS2) 10Wfan: Add currency and country match from di [extensions/DonationInterface] - 10https://gerrit.wikimedia.org/r/911386 (https://phabricator.wikimedia.org/T334967) [20:39:01] (03PS7) 10Ejegg: Fill scheme ID for ingenico recurrings [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/910577 (https://phabricator.wikimedia.org/T331754) [20:41:18] (03CR) 10Ejegg: "See currencies.yaml in astropay_gateway/config for a different way to do this" [extensions/DonationInterface] - 10https://gerrit.wikimedia.org/r/911386 (https://phabricator.wikimedia.org/T334967) (owner: 10Wfan) [20:42:35] oh ok I just realised that UNKNOWN_PAYMENT_ID result was not related to the recurring I created just now for testing. looks like an earlier one [20:42:43] (03CR) 10Ejegg: "Ahh, you would also need to copy the getCurrencies override from astropay.adapter.php to make that work, but it would also activate the cu" [extensions/DonationInterface] - 10https://gerrit.wikimedia.org/r/911386 (https://phabricator.wikimedia.org/T334967) (owner: 10Wfan) [20:43:23] jgleeson: oh interesting, do they purge old txns frequently in preprod I wonder? [20:43:32] I see, since we only support certain countries, no needs to list all the supported currencies from dlocal will do~ [20:43:45] wfan I guess so [20:44:31] ahh, we could also just use NationalCurrencies in the getCurrencies override [20:44:37] wfan actually I like that better [20:44:49] so we leave the full list of the ones they support in the currencies.yaml [20:45:19] oh ok~ in case we open other countries for dlocal right [20:45:30] but we create a getCurrencies override that takes the selected country into account, looks up its national currency, and then returns it if it is in the list from yaml [20:46:04] yep yep wfan, trying to think what will make it most easy to expand in the future [20:46:31] (03CR) 10Jgleeson: [C: 03+2] "Working well for me after testing locally. As discussed on IRC, we don't get the scheme ID back when testing with proprod, but we are succ" [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/910577 (https://phabricator.wikimedia.org/T331754) (owner: 10Ejegg) [20:46:40] thanks jgleeson ! [20:46:47] I'm eager to try that out on prod [20:46:54] np ty [20:55:43] (03PS3) 10Wfan: Add currency and country match from di [extensions/DonationInterface] - 10https://gerrit.wikimedia.org/r/911386 (https://phabricator.wikimedia.org/T334967) [20:57:30] (03CR) 10CI reject: [V: 04-1] Add currency and country match from di [extensions/DonationInterface] - 10https://gerrit.wikimedia.org/r/911386 (https://phabricator.wikimedia.org/T334967) (owner: 10Wfan) [20:58:30] (03Merged) 10jenkins-bot: Fill scheme ID for ingenico recurrings [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/910577 (https://phabricator.wikimedia.org/T331754) (owner: 10Ejegg) [20:59:33] (03PS4) 10Wfan: Add currency and country match from di [extensions/DonationInterface] - 10https://gerrit.wikimedia.org/r/911386 (https://phabricator.wikimedia.org/T334967) [21:00:33] (03PS1) 10Eileen: Limit appeal to 64 char [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911364 (https://phabricator.wikimedia.org/T334534) [21:04:04] (03PS1) 10Eileen: SImplify query [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911365 (https://phabricator.wikimedia.org/T334534) [21:05:19] (03PS2) 10Eileen: SImplify query [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911365 (https://phabricator.wikimedia.org/T334534) [21:08:08] (03Abandoned) 10Eileen: SImplify query [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911365 (https://phabricator.wikimedia.org/T334534) (owner: 10Eileen) [21:08:40] ejegg: I hit a couple of places where the populate stalled - https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/crm/+/911364/1 & the one before it [21:09:07] yep, i'm just trying to understand that first patch [21:09:15] the parent patch [21:09:27] the string is 'USD invalid' [21:09:40] ohh [21:09:48] so the regex is to make sure it is number-possible-dot-possible-number [21:10:05] I grabbed it from https://sebhastian.com/mysql-isnumeric/ [21:10:49] but if you're checking whether REGEXP_REPLACE(t.form_amount, '.* ([0-9]+\.?[0-9]*)$','\\\1') = t.form_amount, it seems like those are only the same when there is nothing outside of the capture [21:11:35] won't that regex match the whole form_amount, then replace it with just the captured numeric bit? [21:12:35] i tried it wtih asdasd 113311d and it came out with 113311d [21:13:08] not sure why it didn't respect that $ end anchor [21:15:01] ah i needed to escape the . again [21:15:54] i'm playing around in CLI mysql so I had to take one of the \\\\ s off from before the 1 backreference [21:16:33] but i needed to add an extra \\ before the . to make it not match the trailing letter [21:18:52] let's see if this works [21:18:53] select id, form_amount, IF(REGEXP_REPLACE(t.form_amount, '.* ([0-9]+\\.?[0-9]*).*$','\\\1') = REGEXP_REPLACE(t.form_amount, '.* ([^ ]+).*$', '\\\1'), REGEXP_REPLACE(t.form_amount, '.* ([0-9]+\\.?[0-9]*).*$','\\\1'), NULL) AS amount from contribution_tracking t order by id desc limit 100; [21:19:08] eileen: so that seems to work for me on the command line [21:19:20] it compares the results of two different regex replaces [21:19:27] one with just number capturing [21:19:33] and the other with anything not a space [21:20:02] and then flips around the results of the IF [21:20:09] so the NULL comes last, when they're not equal [21:20:45] I think all of those backslash strings need one more backslash when written in the PHP though [21:32:58] ejegg: oh ok - i worked for me on live but maybe it missed the dot - do you want to push up what works? [21:36:50] (03PS1) 10Ejegg: Update to drupal 7.97 [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911408 [21:38:10] (03PS1) 10Ejegg: Re-apply WMF patches [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911409 [21:39:14] speaking of missing dots ^^^ [21:40:33] not that we're very likely vunlerable [21:40:44] 'Drupal 7 sites on Linux web servers are vulnerable with certain file directory structures, or if a vulnerable contributed or custom file access module is installed.' [21:42:16] (03PS1) 10Eileen: Fix Matching Gifts import template to right date format [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911410 [21:42:56] oh ! [21:43:13] Can I get a quick +2 on this - https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/crm/+/911410 - it's just a config setting I got wrong [21:43:41] (03CR) 10Eileen: [C: 03+2] Update to drupal 7.97 [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911408 (owner: 10Ejegg) [21:43:43] (03PS2) 10Ejegg: Fix Matching Gifts import template to right date format [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911410 (owner: 10Eileen) [21:43:51] (03CR) 10Eileen: [C: 03+2] Re-apply WMF patches [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911409 (owner: 10Ejegg) [21:43:56] (03CR) 10Ejegg: [C: 03+2] "I have faith in the '4'" [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911410 (owner: 10Eileen) [21:44:14] lol [21:44:27] may the '4's be with you? [21:45:02] (03CR) 10Ejegg: [V: 03+2] Update to drupal 7.97 [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911408 (owner: 10Ejegg) [21:45:12] (03CR) 10Ejegg: [V: 03+2] Re-apply WMF patches [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911409 (owner: 10Ejegg) [21:45:34] manually submitted those [21:45:42] to avoid the top one failing CI [21:45:53] cool [21:46:26] I meant staging above [21:46:34] ah ok [21:47:07] right, let me try another version [21:47:29] i'll introduce some oddballs to my local db [21:47:53] (03PS2) 10Eileen: Limit appeal to 64 char [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911364 (https://phabricator.wikimedia.org/T334534) [21:48:28] ejegg: ok - I just added slashes to where makes sense to me https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/crm/+/911364/1..2/drupal/sites/all/modules/wmf_civicrm/scripts/civicrm_populate_contribution_tracking.drush.inc [21:49:50] hmm, those IF args still look backwards [21:50:15] so you're returning NULL when the = test is true [21:51:06] oh... [21:52:22] (03PS4) 10Eileen: Add handling for invalid amounts in contribution_tracking [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911361 (https://phabricator.wikimedia.org/T334534) [21:52:39] I was so focussed on tracking down which ones were failing & getting no db error [21:53:01] hmm no I WAS right I think - the test is 'it is unchanged' [21:53:22] ohhh [21:53:24] (03PS5) 10Eileen: Add handling for invalid amounts in contribution_tracking [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911361 (https://phabricator.wikimedia.org/T334534) [21:53:31] (switched back) [21:54:39] sorry, i guess i just found the edge case with one trailing non-number [21:55:24] and imagined you were trying to do it a less efficient way [21:55:43] with an extra regexp_replace since that was the first thing that occurred to me [21:55:56] ok, trying the latest version locally [21:55:59] cool [21:58:04] (03PS1) 10Eileen: Merge branch 'master' of ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/crm into deployment [wikimedia/fundraising/crm] (deployment) - 10https://gerrit.wikimedia.org/r/911411 [21:58:32] (03CR) 10Eileen: [C: 03+2] Merge branch 'master' of ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/crm into deployment [wikimedia/fundraising/crm] (deployment) - 10https://gerrit.wikimedia.org/r/911411 (owner: 10Eileen) [22:00:34] !log civicrm upgraded from 3466c2d3 to c17c8db2 [22:00:37] Logged the message at https://wikitech.wikimedia.org/wiki/Server_Admin_Log [22:01:21] oh weird. Illegal parameter data types longblob and row for operation '=' [22:01:45] (when I run the drush locally) [22:01:54] lemme try it without that patch [22:02:18] oh wait, that was with my alt version [22:02:30] switching to your latest version [22:04:11] yep, yours ran, checking what it did with my mutants [22:05:11] oh and i see the typo in the alt version now [22:06:58] oh wait, it didn't hit my mutants - i was altering rows that were skipped because they were transferred on a past run [22:07:10] now that I run your version again it's unhappy with one of my weirdos [22:07:17] let's see [22:08:17] oh Data truncated for column 'amount' at row 1] [22:08:35] hmm what's my amount column, maybe i need a schema update [22:09:27] still let 234a through, I wonder why [22:19:04] ok, looks like 4 is the magic number again [22:19:21] eileen: want to try with 4 backslashes before those dots? [22:19:52] ejegg: ok! [22:21:21] hmm so change the 2 to 4? The thing is where I have 4 - it is supposed to wind up as 2 so 2 should be 4 [22:23:26] yeah, in PHP it should be 4 [22:23:33] which winds up as 2 in the SQL [22:23:40] and that's what works for me [22:23:50] i tried in SQL then in PHP [22:24:17] and with 4 in php the script happily NULLifies things with numbers and a trailing letter [22:25:29] in most language regexes it seems like you should only have to escape the . with a single ` [22:25:33] err a single \ [22:25:45] hmm [22:25:46] so [22:25:48] SET @string = 'USD 123.89'; [22:25:48] SELECT IF(REGEXP_REPLACE(@string, '.* ([0-9]+\.?[0-9]*)$','\\1') = @string, [22:25:48] NULL, [22:25:48] REGEXP_REPLACE(@string, '.* ([0-9]+\.?[0-9]*)$','\\1')); [22:25:52] but I guess mysql must unescape and pass through to something else [22:25:59] is the sql I'm testing [22:26:20] & that works for USD 123.89 and USD 123 and and 123a [22:26:31] oh weird [22:27:01] trying again locally [22:27:42] it seems to still work with the extra one [22:27:58] the slash says that the . is not a special character [22:28:28] for me I get BRL 234a => 234a with one backslash [22:28:36] and BRL 234a => NULL with two [22:28:55] you're testing in staging or on local docker db server? [22:29:45] (with two slashes the well-formatted rows pass through correctly for me: USD 10.00 => 10.00) [22:31:26] I'm so confused now, the dots with a single slash in the other regexes are certainly matching just dots [22:32:14] oh hey, [.] also works [22:32:34] and looks like a robot belly button [22:35:00] oh i see [22:35:09] your regexp rejects 12.89a [22:35:14] but allows 1289a [22:36:48] (at least on my matchine) [22:37:04] I just tried copying your sql straight from irc [22:37:18] anyway, i'mma try on staging and see if I get different results [22:38:55] well i'll be darned, the result is different [22:39:50] versions are only slightly different [22:39:56] perhaps it's a setting? [22:41:13] I do see this: Note: Because MariaDB uses the C escape syntax in strings (for example, "\n" to represent the newline character), you must double any "\" that you use in your REGEXP strings. [22:42:54] huh, neither local nor prod have default_regex_flags set to anything [22:43:42] ejegg: hmm - well to be fair we have only seen 'USD invalid' and USD 123.89 [22:43:56] ok, i just wish i understood [22:49:09] sorry, i AM getting the same results [22:49:37] SET @string='USD 123a'; SELECT IF(REGEXP_REPLACE(@string, '.* ([0-9]+\.?[0-9]*)$','\\1') = @string, NULL, REGEXP_REPLACE(@string, '.* ([0-9]+\.?[0-9]*)$','\\1')) as result; [22:49:41] | result | [22:49:41] +--------+ [22:49:41] | 123a | [22:49:47] on both staging and locally [22:50:24] but right, this isn't for all future data, that'll be parsed in php, and we'll de-concatenated the message fields at the source [22:51:32] now i'm curious how the other dot-split is working! [22:52:02] oh i see it's all the not-a-dot ranges [22:52:14] ([^\.]*) [22:52:21] hah, so I think that \ is unneeded [22:53:54] (03CR) 10Ejegg: [C: 03+2] "Works for all the cases found in our db!" [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911361 (https://phabricator.wikimedia.org/T334534) (owner: 10Eileen) [22:54:07] (03PS6) 10Ejegg: Add handling for invalid amounts in contribution_tracking [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911361 (https://phabricator.wikimedia.org/T334534) (owner: 10Eileen) [22:54:18] (03PS3) 10Ejegg: Limit appeal to 64 char [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911364 (https://phabricator.wikimedia.org/T334534) (owner: 10Eileen) [22:54:28] (03CR) 10Ejegg: [C: 03+2] Limit appeal to 64 char [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911364 (https://phabricator.wikimedia.org/T334534) (owner: 10Eileen) [22:59:59] (03PS1) 10Eileen: Remove broken ajax/js call in shoreditch theme [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911415 [23:06:31] (03Merged) 10jenkins-bot: Add handling for invalid amounts in contribution_tracking [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911361 (https://phabricator.wikimedia.org/T334534) (owner: 10Eileen) [23:06:41] (03Merged) 10jenkins-bot: Limit appeal to 64 char [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911364 (https://phabricator.wikimedia.org/T334534) (owner: 10Eileen) [23:08:06] (03PS1) 10Eileen: Merge branch 'master' of ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/crm into deployment [wikimedia/fundraising/crm] (deployment) - 10https://gerrit.wikimedia.org/r/911417 [23:08:16] (03CR) 10Eileen: [C: 03+2] Merge branch 'master' of ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/crm into deployment [wikimedia/fundraising/crm] (deployment) - 10https://gerrit.wikimedia.org/r/911417 (owner: 10Eileen) [23:08:38] (03PS1) 10Ejegg: Pedantic backslash-dot fixes [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911418 [23:09:08] (03Merged) 10jenkins-bot: Merge branch 'master' of ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/crm into deployment [wikimedia/fundraising/crm] (deployment) - 10https://gerrit.wikimedia.org/r/911417 (owner: 10Eileen) [23:14:30] (03CR) 10Eileen: [C: 03+2] "Well it still seems to work on staging!" [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911418 (owner: 10Ejegg) [23:15:18] !log civicrm upgraded from c17c8db2 to 26150ed4 [23:15:21] Logged the message at https://wikitech.wikimedia.org/wiki/Server_Admin_Log [23:19:13] well those are going in again now - but the query is slowing as there are more rows so I need to look at that [23:26:52] (03Merged) 10jenkins-bot: Pedantic backslash-dot fixes [wikimedia/fundraising/crm] - 10https://gerrit.wikimedia.org/r/911418 (owner: 10Ejegg)