Page MenuHomePhabricator
Create Task
Maniphest T108255

Enable MariaDB/MySQL's Strict Mode
Open, Stalled, LowPublic
Actions

Description

If you are looking for the full RFC, go to T112637

This task's scope is:

  • switching to MariaDB's Strict Mode, first on CI (T119371), then on production
  • Make it so that DB insert/update that would overflow would error out rather than be silently truncated
  • Generally promote better use of MariaDB by making many things fail precommit and/or fail loudly in production rather than failing quietly

Options discussed:

  • sql_mode = 'TRADITIONAL' (equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER)
  • sql_mode = ONLY_FULL_GROUP_BY

Often, our non-strict usage is a fountain of bugs and security issues.

Slide by @jcrespo that explains it: https://www.slideshare.net/jynus/query-optimization-with-mysql-57-and-mariadb-10-even-newer-tricks/43 and https://www.slideshare.net/jynus/query-optimization-with-mysql-57-and-mariadb-10-even-newer-tricks/44

Related Objects
Search...

View Standalone Graph
This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.
StatusSubtypeAssignedTask
· · ·
StalledNoneT108255 Enable MariaDB/MySQL's Strict Mode
ResolvedhasharT119370 Send deployment-db1 deployment-db2 syslog to beta cluster logstash
ResolvedKrinkleT119371 Enable MariaDB/MySQL strict mode for MW during development and Jenkins jobs
OpenNoneT121489 Field 'ar_page' doesn't have a default value
ResolvedNoneT117854 Missing updater for rc_ip field: RecentChange::save: 1406 Data too long for column 'rc_ip'
ResolvedNikerabbitT67807 Incorrect integer value: '' for column 'af_hidden'
Resolved• RobLa-WMFT112637 RFC: Increase the strictness of mediawiki SQL code and leverage database code blockers for scalability
OpenNoneT138031 Category sort keys can exceed the capacity of cl_sortkey, yet that is not correctly handled
ResolvedNikerabbitT124628 LiquidThreads does not truncate comments: Data too long for column 'th_change_comment'
ResolvedEjeggT145591 CentralNotice: Support MariaDB Strict Mode
OpenNoneT147875 SecurePoll should comply with strict mode
Resolved• MarosteguiT149819 Add primary keys to remaining Flow tables
OpenNoneT193246 JobQueueDB::doBatchPush fails with data too long for job_params when purging a template used on a few million pages
ResolvedTK-999T199323 Checkuser does not truncate data (cuc_comment, cuc_agent, cuc_actiontext) and that causes exceptions with strict mode
ResolvedNoneT220274 sql files of extension CreditsSource failing on unit tests due to default on text column
OpenBUG REPORTNoneT294775 Filter for multiple tags on Special:RecentChanges fails with 'recentchanges.rc_namespace' isn't in GROUP BY for sql mode ONLY_FULL_GROUP_BY
OpenBUG REPORTNoneT299826 action=query&list=allpages&apfilterlanglinks=withlanglinks fails with 'page.page_namespace' isn't in GROUP BY for sql mode ONLY_FULL_GROUP_BY
ResolvedNoneT304762 Add SpecialPageFatalTest to @group Database
ResolvedBUG REPORTDiesel_kapasuleT307613 Validate length of user email on Special:ChangeEmail/Special:CreateAccount to avoid database error "Data too long for column 'user_email'" on strict mode
DeclinedNoneT322080 Error 1055: 'wikidb.mw_flow_tree_revision.tree_orig_user_ip' isn't in GROUP BY
ResolvedBUG REPORTapasternakT355530 Cannot delete files with non-standard extensions on MySQL in a strict mode
· · ·

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
Platonides subscribed.Jun 29 2016, 9:23 PM
hashar added a comment.Jun 30 2016, 10:48 AM

I have myself have no idea how to enable strict mode / which part of puppet config files have to be tweaked. The beta cluster database are also still running on Precise instance and MariaDB 5.5 , we might want to migrate them to migrate them to Jessie and MariaDB 5.10 first (that is T138778)

• jcrespo added a comment.Jun 30 2016, 11:23 AM

This is not blocked by that- it only needs to be enabled on config (see my comment on the child ticket).

TerraCodes subscribed.Jul 6 2016, 5:27 PM
hashar closed subtask T119370: Send deployment-db1 deployment-db2 syslog to beta cluster logstash as Resolved.Jul 14 2016, 12:12 PM
greg moved this task from Next: Feature to Backlog on the Beta-Cluster-Infrastructure board.Aug 5 2016, 8:56 PM
Nikerabbit added a subtask: T124628: LiquidThreads does not truncate comments: Data too long for column 'th_change_comment'.Aug 24 2016, 11:24 AM
Nikerabbit closed subtask T124628: LiquidThreads does not truncate comments: Data too long for column 'th_change_comment' as Resolved.Aug 25 2016, 1:13 PM
AndyRussG added a subtask: T145591: CentralNotice: Support MariaDB Strict Mode.Sep 13 2016, 10:42 PM
• MZMcBride subscribed.Sep 13 2016, 11:24 PM
• Tgr mentioned this in T147537: Database::select with the condition [ 'somecolumn' => false ] will match unexpectedly on (almost) any string value in MySQL non-strict mode.Oct 6 2016, 1:10 AM
Nikerabbit mentioned this in T147852: MW unable to bypass MySQL strict mode by setting $wgSQLMode to blank.Oct 11 2016, 2:43 PM
Huji added a subtask: T147875: SecurePoll should comply with strict mode.Oct 11 2016, 6:15 PM
• RobLa-WMF mentioned this in T147875: SecurePoll should comply with strict mode.Oct 11 2016, 7:25 PM
• RobLa-WMF added a subscriber: Huji.Oct 11 2016, 7:31 PM

Thanks @Huji for noting the SecurePoll strict mode problems (T147875).

I'll note this task still seems to be unassigned. @hashar, is the Beta cluster running in strict mode?

hashar added a comment.Oct 12 2016, 10:54 AM

The beta cluster databases have been migrated to Jessie / MariaDB 5.10 T138778 and their config file is https://github.com/wikimedia/operations-puppet/blob/d3a05afd154/templates/mariadb/beta.my.cnf.erb

On beta I would guess it is about setting $wgSQLMode via operations/mediawiki-config , maybe that already has been done. For CI the task is T119371.

I am lurking on this task but reasonably have zero free time to lead the effort.

• jcrespo mentioned this in T146444: Improve mediawiki data redaction.Oct 18 2016, 7:23 AM
Krinkle closed subtask T117854: Missing updater for rc_ip field: RecentChange::save: 1406 Data too long for column 'rc_ip' as Resolved.Oct 26 2016, 9:45 PM
Catrope created subtask T149819: Add primary keys to remaining Flow tables.Nov 2 2016, 5:22 PM
• jcrespo added a project: Epic.Nov 7 2016, 4:03 PM
Ejegg closed subtask T145591: CentralNotice: Support MariaDB Strict Mode as Resolved.Nov 23 2016, 11:59 PM
• jcrespo mentioned this in T153899: Gerrit: Convert gerrit's db caractor encoding from utf8 to utf8mb4 to prevent truncation of astral characters.Dec 22 2016, 9:11 AM
• Marostegui closed subtask T149819: Add primary keys to remaining Flow tables as Resolved.Jan 23 2017, 3:48 PM
• jcrespo mentioned this in T162050: Implement GTID-MySQL support for mediawiki.Apr 3 2017, 3:07 PM
Krinkle subscribed.Apr 18 2017, 6:25 AM
• Phabricator_maintenance removed a subscriber: yuvipanda.Jun 7 2017, 6:48 PM
• jcrespo mentioned this in T161232: Bump MediaWiki's minimum supported MySQL Version to 5.5.8.Jun 30 2017, 12:38 PM
daniel mentioned this in T171295: Fatal error: Argument 4 passed to WikibaseQuality\ConstraintReport\Constraint::__construct() must be array.Jul 21 2017, 4:49 PM
• Marostegui mentioned this in T150949: Set barracuda InnoDB file format as the default configuration everywhere.Dec 1 2017, 10:32 AM
Krinkle unsubscribed.Dec 2 2017, 12:57 AM
• jcrespo updated the task description. (Show Details)Apr 27 2018, 9:04 AM
Nikerabbit added a subtask: T193246: JobQueueDB::doBatchPush fails with data too long for job_params when purging a template used on a few million pages.Apr 27 2018, 3:24 PM
hashar added a comment.Apr 27 2018, 8:18 PM

https://gerrit.wikimedia.org/r/#/c/429386/ would enable sql_mode = TRADITIONAL via includes/DevelopmentSettings.php which is loaded by CI. Tracked by sub task T119371.

hashar updated the task description. (Show Details)Apr 27 2018, 8:37 PM
Krinkle moved this task from Untriaged to Rdbms library on the MediaWiki-libs-Rdbms board.Jul 28 2018, 11:44 PM
Nikerabbit added a subtask: T199323: Checkuser does not truncate data (cuc_comment, cuc_agent, cuc_actiontext) and that causes exceptions with strict mode.Aug 1 2018, 3:06 PM
• Marostegui updated the task description. (Show Details)Jan 9 2019, 7:17 PM
• Marostegui moved this task from Triage to Backlog on the DBA board.Jan 23 2019, 6:39 AM
Krinkle mentioned this in T220056: MediaWiki database policy and/or guidelines (2019).Apr 3 2019, 11:54 PM
Umherirrender added a subtask: T220274: sql files of extension CreditsSource failing on unit tests due to default on text column.Apr 6 2019, 7:31 PM
Umherirrender closed subtask T220274: sql files of extension CreditsSource failing on unit tests due to default on text column as Resolved.Apr 11 2019, 3:41 PM
• Lucas_Werkmeister_WMDE mentioned this in T224523: Wire up DatabasePropertyTermStore in WikibaseRepo.May 29 2019, 4:53 PM
• Lucas_Werkmeister_WMDE mentioned this in T225805: DatabaseTermIdsAcquirer fails on terms longer than 255 bytes.Jun 14 2019, 12:34 PM
Krinkle moved this task from Rdbms library to Rdbms: MySQL support on the MediaWiki-libs-Rdbms board.Jul 23 2019, 5:39 PM
Restricted Application added a project: Platform Engineering. · View Herald TranscriptJul 23 2019, 5:39 PM
Krinkle removed projects: Platform Engineering, OKR-Work.Jul 23 2019, 5:39 PM
• jcrespo mentioned this in T240405: WikiPage::updateCategoryCounts causing replication lag due to long-running writes on commonswiki.Dec 11 2019, 3:15 PM
Krinkle closed subtask T119371: Enable MariaDB/MySQL strict mode for MW during development and Jenkins jobs as Resolved.Mar 20 2020, 5:48 PM
Reedy mentioned this in T260633: BotPasswords doesn't validate length of resultant bp_grants JSON.Aug 18 2020, 12:45 AM
Reedy mentioned this in T260631: BotPasswords doesn't validate length of resultant bp_restrictions JSON.
Reedy closed subtask T199323: Checkuser does not truncate data (cuc_comment, cuc_agent, cuc_actiontext) and that causes exceptions with strict mode as Resolved.Aug 18 2020, 12:53 AM
Aklapper removed subscribers: Anomie, • RobLa-WMF.Oct 16 2020, 5:02 PM
Ammarpad mentioned this in T270032: Find a solution for cl_timestamp.Dec 31 2020, 12:59 PM
Krinkle added a project: Sustainability (Incident Followup).Mar 8 2021, 9:19 PM
Reedy mentioned this in T277379: OAuth doesn't validate length of oarc_grants and oarc_oauth2_allowed_grants JSON.Mar 13 2021, 5:06 PM
Reedy mentioned this in T277388: OAuth doesn't validate length of oarc_version (CVE-2021-31555).Mar 13 2021, 6:07 PM
LSobanski subscribed.Mar 25 2021, 11:06 AM
hashar removed a project: Continuous-Integration-Infrastructure.May 27 2021, 11:04 AM

For CI that got done by setting TRADITIONAL in DevelopmentSettings.php https://gerrit.wikimedia.org/r/c/mediawiki/core/ /429386/4/includes/DevelopmentSettings.php

That was the purpose of T119371

Umherirrender added a subtask: T294775: Filter for multiple tags on Special:RecentChanges fails with 'recentchanges.rc_namespace' isn't in GROUP BY for sql mode ONLY_FULL_GROUP_BY.Nov 1 2021, 3:54 PM
Restricted Application added a project: Performance-Team. · View Herald TranscriptNov 1 2021, 3:54 PM
hashar unsubscribed.Nov 8 2021, 3:05 PM
• dpifke edited projects, added Performance-Team (Radar); removed Performance-Team.Nov 8 2021, 7:59 PM
• dpifke moved this task from Limbo to Watching on the Performance-Team (Radar) board.
Krinkle added a comment.EditedNov 8 2021, 8:22 PM

The need from Wikimedia-Rdbms was to add the option and any additional support. I believe that part is done. (Ideally that would've been a subtask, but alas, better next time.)

Umherirrender added a subtask: T299826: action=query&list=allpages&apfilterlanglinks=withlanglinks fails with 'page.page_namespace' isn't in GROUP BY for sql mode ONLY_FULL_GROUP_BY.Jan 22 2022, 12:09 PM
taavi added a subtask: T304759: GlobalUsersPager should work with ONLY_FULL_GROUP_BY SQL mode.Mar 26 2022, 9:29 AM
taavi removed a subtask: T304759: GlobalUsersPager should work with ONLY_FULL_GROUP_BY SQL mode.Mar 26 2022, 1:42 PM
taavi added a subtask: T304762: Add SpecialPageFatalTest to @group Database.
Umherirrender added a subtask: T307613: Validate length of user email on Special:ChangeEmail/Special:CreateAccount to avoid database error "Data too long for column 'user_email'" on strict mode.May 4 2022, 6:40 PM
Diesel_kapasule changed the status of subtask T307613: Validate length of user email on Special:ChangeEmail/Special:CreateAccount to avoid database error "Data too long for column 'user_email'" on strict mode from Open to In Progress.May 13 2022, 10:00 AM
Diesel_kapasule closed subtask T307613: Validate length of user email on Special:ChangeEmail/Special:CreateAccount to avoid database error "Data too long for column 'user_email'" on strict mode as Resolved.Jul 1 2022, 10:18 AM
Reedy mentioned this in T313491: 'cr_status' isn't in GROUP BY.Jul 21 2022, 12:25 PM
Daimona subscribed.Sep 3 2022, 1:35 PM
Joe changed the task status from Open to Stalled.Mar 20 2023, 11:17 AM
Joe lowered the priority of this task from Medium to Low.
Joe edited projects, added SRE-Sprint-Week-Sustainability-March2023; removed Sustainability (Incident Followup).
Joe subscribed.

I fail to see how this task is related to incident followup, or sustainability. So removing the tag. Adding the sprint week tag as I was triaging this task for the SRE sprint week.

Additionally, given the low activity:

  • Set as stalled
  • Lower priority.
Joe moved this task from Backlog to Done on the SRE-Sprint-Week-Sustainability-March2023 board.Mar 20 2023, 11:19 AM

Additionally: strict mode is on by default since mariadb 10.2, this has nothing to do with production usage at this point. Maybe another task should be opened?

• jcrespo added a comment.Mar 20 2023, 11:25 AM
In T108255#8709276, @Joe wrote:

I fail to see how this task is related to incident followup, or sustainability. So removing the tag. Adding the sprint week tag as I was triaging this task for the SRE sprint week.

I want to remember this was detected as a mitigation to a MW incident/bug in which, if the flag was enabled on testing, the issue would have been caught earlier. But I don't remember the details.

In T108255#8709284, @Joe wrote:

Additionally: strict mode is on by default since mariadb 10.2, this has nothing to do with production usage at this point. Maybe another task should be opened?

I believe ONLY_FULL_GROUP_BY is not enabled, which was part of the above scope. Strict is technically only STRICT_TRANS_TABLES.

Reedy added a subtask: T322080: Error 1055: 'wikidb.mw_flow_tree_revision.tree_orig_user_ip' isn't in GROUP BY.Jun 26 2023, 4:51 PM
Addshore unsubscribed.Jun 27 2023, 12:33 PM
LSobanski unsubscribed.Jul 14 2023, 8:33 AM
Krinkle removed a project: Performance-Team (Radar).Aug 7 2023, 1:46 AM
Krinkle removed subscribers: • MZMcBride, gerritbot.
Pppery closed subtask T304762: Add SpecialPageFatalTest to @group Database as Resolved.Dec 20 2023, 7:47 PM
Reedy added a subtask: T355530: Cannot delete files with non-standard extensions on MySQL in a strict mode.Jan 25 2024, 1:16 PM
TK-999 closed subtask T355530: Cannot delete files with non-standard extensions on MySQL in a strict mode as Resolved.Feb 6 2024, 3:51 PM
• jcrespo mentioned this in T359176: Long-titled archived files can get its path metadata truncated due to not having enough storage space, leading to orphan, non accesible files (was: Two files on commons have invalid UTF-8 characters in path metadata).Mar 5 2024, 3:14 PM
Samwalton9-WMF closed subtask T322080: Error 1055: 'wikidb.mw_flow_tree_revision.tree_orig_user_ip' isn't in GROUP BY as Declined.May 5 2024, 9:31 AM
Novem_Linguae subscribed.May 28 2024, 1:27 AM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits