Creating new tables
Appearance
Follow the steps below to create a new database table. If you want to make changes to the existing database schema, use this guide instead.
Preparation
- Create a Phabricator task to track the table creation.
- Design the table schema, following the database policy and database coding conventions.
- Create a Gerrit patch with the database JSON and generated SQL commands to create the new table and link it to the task.
- Get the patch reviewed (+1):
- Ask a developer knowledgeable in areas of database schema to review the patch (a list is coming, for now just ask around to find one).
- Tag the task with the #DBA tag for visibility. A DBA may provide feedback on the patch at this point.
- Request DBA signoff (requests are processed in order, with no specific SLA, if you are working against a deadline contact the DBA manager to negotiate):
- Add the following information to the task:
- Should this table be replicated to wiki replicas (does it not contain private data)?
- Will you be doing cross-joins with the wiki metadata?
- Size of the table (number of rows expected).
- Expected growth per year (number of rows).
- Expected amount of queries, both writes and reads (per minute, per hour...per day, any of those are ok).
- Examples of queries that will be using the table.
- The release plan for the feature (are there specific wikis you'd like to test first etc).
- Move the task to the Triage column on the DBA workboard.
- Add the following information to the task:
- If the table is for an extension used on multiple wikis: modify createExtensionTables.php to include extension and tables you need to use.
- If the table is for an extension and needs to be created on all new wikis (or all new Wikipedias/Wiktionaries/etc): modify addWiki.php to include the extension and tables you need.
- Once the above steps are done, proceed to deployment.
Deployment
- If you have production deployment permissions, proceed to the next step. If you don't have deployment permissions and this is a one off deployment (you don't regularly deploy to production), locate a person with deployment permissions and ask them for help. If you can't find any, creating a task tagged with wikimedia-maintenance-script-run and wikimedia-site-requests will likely attract someone who can help you.
- Make sure the patch which introduces the tables got merged, and confirm files were deployed to production
- Log on to a mwmaint host (mwmaint1002 or mwmaint2001)
- Check if the file is present in the location matching the following example:
- If :
- the sql file is in
Extension:Foo
in pathsql/new_table.sql
- the MW version deployed in production is
1.36-wmf.21
- the sql file is in
- The location will be:
/srv/mediawiki/php-1.36-wmf.21/extensions/Foo/sql/new_table.sql
- If :
- Schedule table creation at a time that doesn't conflict with the deployment schedule
- Create the table:
- Log on to a mwmaint host (mwmaint1002 or mwmaint2001, depending on the active cluster)
- Run one of the following:
- To add a core table to a single wiki:
mwscript sql.php --wiki=wikidbname maintenance/patches/patchname.sql
- To add a core table to all wikis:
foreachwiki sql.php maintenance/patches/patchname.sql
- To create tables required when adding an extension (assuming you already deployed the extension code and that the table is included in createExtensionTables.php script):
mwscript extensions/WikimediaMaintenance/createExtensionTables.php --wiki=wikidbname extensionname
- You can run the script on all wikis in a dblist using
foreachwikiindblist name-of-dblist.dblist extensions/WikimediaMaintenance/createExtensionTables.php extensionname
- See also: https://www.mediawiki.org/wiki/Manual:Developing_extensions#Adding_database_tables
- You can run the script on all wikis in a dblist using
- To add a core table to a single wiki:
Future-proofing
- If your new table is going to be needed in all wikis, add it to
addWiki.php
inWikimediaMaintenance
extension so that new wikis are created with your table
This page is a part of the SRE Data Persistence technical documentation
(go here for a list of all our pages)