MediaWiki のデータベースの方針

このページでは、公式の MediaWiki のデータベース方針の規範について説明します。 これは、RFC T220056 に従って TechCom の RFC プロセスを介して2019年12月に承認されました。

データベースのクエリ

• MediaWiki から SQL クエリを送信するすべての新しいコードは MariaDB/MySQL の厳格モード (RFC T112637 による) の下でいかなる警告も生成しないようにする必要があります。
  • WMF は MariaDB/MySQL の厳格モード (T108255) を有効にする予定です。これは MySQL 5.7 でとにかく既定になる予定です。それ以前は、コードに警告が出ないようにする必要があります。
  • データベースを操作するコードは、以下の MySQL SQL モードと互換性がある必要があります:
    • 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)
    • ONLY_FULL_GROUP_BY
• Database code must be compatible with older versions of databases as listed in the MediaWiki installation requirements for database server. しかし、サポートされている最新のバージョン (またはその既定値や広く推奨されている既定値) にのみ適用されるような性能向上は、サポートされていないリリースにのみ適用されるものよりも優先されるべきです。
• Non-deterministic queries and unsafe statements for binlog should be avoided as they would return/write different results in a replication environment. The latter can be detected as warnings with the text "[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT". Those include INSERT ... SELECT when using an auto_increment key, UPDATE ... LIMIT without an ORDER BY, and using non-deterministic functions like SYSDATE(). More info here .

スキーマの変更

• All new tables must have a primary key. When a candidate for primary key could not be created (for example, if all columns can be repeated), a separate auto_increment column, or other arbitrary field (depending on the case), must be added.
• Primary keys, and fields that reference them, should be unsigned, in order to increase the maximum values.
• All tables in MediaWiki core, and Wikimedia-deployed and MediaWiki-bundled extensions must be implemented using the abstract schema system, and new schema changes to them must be generated automatically.

スキーマ変更の互換性

Schema changes must provide a path for upgrading from all releases from two LTS releases before onwards (see phab:T259771).

データベースのパッチ

データベース スキーマを変更する場合は、以下の規則に従ってください:

• Update the installer – Update includes/installer and add an appropriate SQL patch file to maintenance/archives/. The naming convention, if you're adding a field, is patch-{table}-{field}.sql. If you're removing a field, it is patch-drop-{table}-{field}.sql. If you're adding a table, it is patch-{table}.sql. Look at the commit history of includes/installer/(MySQL|Postgres|SQLite)Updater.php to find examples of how it is done. If you're adding a bunch of fields to the same table, make all those changes in one query in one patch file.
• Make your schema change optional – All schema changes must go through a period of being optional. Some examples:
  • Instead of changing the format of a column, create a new column, make all writes happen to the old and new column (if it exists), and deprecate use of the old column. Check if the new column exists before blindly assuming that it does. Only eliminate support for the old column after it is clear the schema migration has completed and there's no chance that we'll need to roll back to the old version of the software. If this doesn't seem feasible, send mail to Wikitech-l asking for advice.
  • You could set your new feature to only work if a config option is set to true, and set the option to false by default. Then the commit can be safely deployed before the schema change is made. To deploy your feature to the Wikimedia cluster, file a ticket in Phabricator in the relevant project with the #schema-change tag. Once you've confirmed the change has been made, you can remove the config option to enable your feature.
  • Note that this means your schema change should be optional in code - for wikimedia deployments, it is expected that every wiki with the relevant database table(s) will have the schema change applied to them. If you need different schema for different wikis, then apply the change using an extension and creating new tables dependent on that extension.

「スキーマの変更を省略可能にする」規則は、パフォーマンスまたはロジスティクスの観点から禁止される場合があります。 ただし、そのようなスキーマの変更はそもそもまれであるため、wikitech-l メーリング リストで目立つようにして議論する必要があります。 スキーマの変更を省略可能にすることが不可能な場合でも、変更前の状態にロールバックするスクリプトを作成することが重要です。

• Search for input from a WMF Database Administrator – MediaWiki is deployed to Wikimedia websites every week, and it takes considerable planning to apply schema changes to MySQL-based sites the size of Wikipedia. 2020年1月時点 Jaime Crespo (jcrespo on LDAP, jynus on irc) and Manuel (Arostegui, marostegui) are the best people to add to database reviews. In most cases, input is just needed on the logistics of the change.
• Test your changes on Beta - in particular, it is a common mistake to change indexes and column definitions that would result in different query plans. Try to test the generated queries' plan with tools such as EXPLAIN; not doing so could mean that, when scaled to production, queries that only take 1 second locally, they pileup on production when they receive much more traffic and have larger tables.

注記

Since Version 1.36, MediaWiki only commits to supporting upgrades from two major long term support releases (LTS) ago (see phab:T259771). Upgrades from older versions of MediaWiki will have to be performed in multiple steps.