wikimedia-database git:20261003.928fb9aA

Immutable. This exact content never changes and is served at /api/v1/blob/b371770db34d18da.

---
name: wikimedia-database
description: Execute SQL queries against Wikipedia database replicas (enwiki, wikidata, commonswiki) via an SSH tunnel to Toolforge, with connection management and data handling guardrails
license: MIT
compatibility: opencode
depends_on: [wikimedia-toolforge]
skill_discovery_hints:
  - keywords: ["SQL", "database", "replica", "Toolforge database", "enwiki_p", "MySQL"]
  - keywords: ["SSH tunnel", "pymysql", "query Wikipedia", "schema", "page table", "revision table"]
last_verified: 2026-10-03
---

> ⚠️ **User-Agent required:** While this skill primarily uses SQL via SSH tunnel, any direct API calls (e.g., for verification via `curl` or `requests`) must include a descriptive `User-Agent` header. See the **[wikimedia-api-access](../wikimedia-api-access/SKILL.md)** skill for the correct format.

Enables the agent to execute dynamic SQL queries against Wikimedia production replicas (e.g., `enwiki`, `enwiki_p`, `wikidata`, `commonswiki`) by leveraging a local SSH tunnel and specific environment variables for multi-layer authentication.

> ⚠️ **Commons links tables moved to a separate cluster (`x4`) on 2026-09-08.** The links
> tables on `commonswiki` (`categorylinks`, `pagelinks`, `templatelinks`, `imagelinks`,
> `globalimagelinks`, `linktarget`, `externallinks`, `iwlinks`, `langlinks`,
> `existencelinks`, `collation`) are no longer on the core cluster. Connect to
> `links.commonswiki.analytics.db.svc.wikimedia.cloud` for those tables, and **join them to
> `page` / `redirect` / `revision` in application code — cross-cluster JOINs are not
> possible**. The old copies on the core cluster are no longer updated, so any query still
> hitting them silently returns stale data. Table/column names are unchanged, and `page` +
> `redirect` remain available on both clusters. Since **2026-09-14** the `x4` section is exposed on the Wiki
> Replicas, so the links hostnames return fresh data again (the s4 copies are no longer reachable from the
> replicas). Details:
> [News/2026 Commons links tables database split](https://wikitech.wikimedia.org/wiki/News/2026_Commons_links_tables_database_split)
> and `references/schema-replicas.md` (hostname mapping + code-side join pattern).

## **Prerequisites**

* **Authentication:** The following variables must be set in the `.env` file:
* `TOOLFORGE_USER`: The shell/LDAP username for SSH (e.g., `janesmith`).
* `TOOLFORGE_SQL_USER`: The replica database username (e.g., `u1234`).
* `TOOLFORGE_SQL_PASSWORD`: The replica database password.
* `TOOLFORGE_DB_PORT`: The local port for the SSH tunnel (default: `3307`). Set if you need a different port to avoid conflicts with a local MySQL/MariaDB instance.

* **SSH Config:** The user must have their SSH keys added to the `ssh-agent` to allow non-interactive connections.

## **Standard Operating Procedure (SOP)**

### **1. Connection Management**

Before executing any SQL, the agent must ensure the tunnel is active.

* **Check:** Attempt a connection to `127.0.0.1:${TOOLFORGE_DB_PORT:-3307}`.
* **Auto-Establish:** If the port is closed, the agent should attempt to spawn a background SSH process:
`ssh -L ${TOOLFORGE_DB_PORT:-3307}:enwiki.analytics.db.svc.wikimedia.cloud:3306 ${TOOLFORGE_USER}@login.toolforge.org -N`
* **Persistence (Recommended):** For long-running data generation, use `autossh` instead of plain
  `ssh`. Autossh monitors the connection and re-establishes it if the tunnel drops, which happens
  frequently during multi-hour sessions. Install with `brew install autossh` (macOS) or
  `apt install autossh` (Linux), then use:
  `autossh -M 0 -o "ServerAliveInterval 30" -o "ServerAliveCountMax 3" -L ${TOOLFORGE_DB_PORT:-3307}:enwiki.analytics.db.svc.wikimedia.cloud:3306 ${TOOLFORGE_USER}@login.toolforge.org -N -v`
* **Escalation:** If the tunnel fails to open, provide the user with the command above to run manually.

### **2. Implementation Pattern (Python)**

The agent should use `pymysql` to interact with the replicas.

```python
import os
import socket
import subprocess
import pymysql
from dotenv import load_dotenv

load_dotenv()

def get_db_connection(db_name='enwiki_p'):
    host = '127.0.0.1'
    port = int(os.getenv('TOOLFORGE_DB_PORT', '3307'))
    
    # Check if tunnel is open
    with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as s:
        s.settimeout(1)
        if s.connect_ex((host, port)) != 0:
            user = os.getenv("TOOLFORGE_USER")
            # Spawn persistent background tunnel
            cmd = ["ssh", "-L", f"{port}:enwiki.analytics.db.svc.wikimedia.cloud:3306", f"{user}@login.toolforge.org", "-N"]
            subprocess.Popen(cmd, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
            
    return pymysql.connect(
        host=host,
        port=port,
        user=os.getenv("TOOLFORGE_SQL_USER"),
        password=os.getenv("TOOLFORGE_SQL_PASSWORD"),
        database=db_name,
        cursorclass=pymysql.cursors.DictCursor,
        charset='utf8mb4'
    )

def query_wiki(sql, db='enwiki_p'):
    conn = get_db_connection(db)
    try:
        with conn.cursor() as cursor:
            cursor.execute(sql)
            return cursor.fetchall()
    finally:
        conn.close()

```

### **3. Data Handling Guardrails**

* **Read-Only:** Only generate `SELECT` statements.
* **Namespace Filtering:** Always consider `page_namespace` (e.g., `0` for Main/Article space).
* **Binary Decoding:** Many strings (titles, user names) are stored as `Varbinary`. If the output looks like `b'Title'`, the agent must apply `.decode('utf-8')`.
* **Safety Limits:** Every exploratory query **must** include a `LIMIT` (suggested default: 10-50).
* **Database Naming:** Project names must end in `_p` (e.g., `wikidatawiki_p`, `commonswiki_p`).

### **MySQL 9+ compatibility**

If you get `ERROR 2059: Authentication plugin 'mysql_native_password' cannot be loaded`
when using the `mysql` CLI, this is because **MySQL 9.x removed the
`mysql_native_password` plugin entirely** from the client — both as a loadable
`.so` and as a compiled-in implementation. Neither `--default-auth` nor any
other flag can work around this.

**The fix is to use `pymysql`** (pure Python). It implements the MySQL protocol
natively and handles `mysql_native_password` authentication without any
external plugins:

```bash
pip install pymysql python-dotenv
```

The `query.sh` script will automatically use `pymysql` if available, falling
back to the `mysql` CLI only if Python dependencies are missing.

## **Example Use Cases**

* **Prompt:** "What are the 5 oldest pages in the 'Draft' namespace?"
* **Action:** Execute `SELECT page_title, page_touched FROM page WHERE page_namespace = 118 ORDER BY page_id ASC LIMIT 5;`


* **Prompt:** "Count revisions by 'ExampleUser' on enwiki."
* **Action:** Execute `SELECT COUNT(*) FROM revision_userindex JOIN actor ON rev_actor = actor_id WHERE actor_name = 'ExampleUser';`

* **Prompt:** "Search edit summaries mentioning 'revert' by a specific user."
* **Action:** Execute constrained SQL — the `comment` table has no full-text index, so queries MUST include both `actor_name` and `rev_timestamp` filters to avoid timeouts. See `references/edit-summary-search.md` for the full guide to edit-summary search across all approaches (SQL, Sigma tool, XML dumps, EventStreams).

---

## **Tooling**

This skill includes helper scripts, reference docs, and templates:

### 🔧 Tunnel Management (`scripts/setup-tunnel.sh`)

Establishes an SSH tunnel to Toolforge. Auto-detects if tunnel is already
active, prefers `autossh` for auto-reconnecting, and verifies the connection.

```bash
./scripts/setup-tunnel.sh [db_host] [local_port]
```

**Requires:** `TOOLFORGE_USER` environment variable and SSH key loaded in agent.

### 🔧 Query Runner (`scripts/query.sh`)

Run a SQL query against a replica and see results in the terminal.

```bash
./scripts/query.sh "SELECT page_title, page_len FROM page LIMIT 10" [database]
```

Includes safety guardrails: only SELECT allowed, missing env vars detected.

### 🔧 Tunnel Closer (`scripts/close-tunnel.sh`)

Cleanly tear down the SSH tunnel.

```bash
./scripts/close-tunnel.sh [local_port]
```

### 📚 Schema Reference (`references/schema-replicas.md`)

Full reference of Wikimedia replica database tables (page, revision, actor,
page_props, categorylinks, pagelinks) with column descriptions and common
queries.

### 📚 Edit Summary Search (`references/edit-summary-search.md`)

Comprehensive guide to searching Wikipedia edit summaries. Covers the `comment`
table indexing gap, constrained SQL patterns, the Σ (Sigma) Toolforge tool, XML
dump processing, and EventStreams filtering. Load this when the task involves
finding edits by their summary text.

### 📚 Connection Guide (`references/connection-guide.md`)

Step-by-step guide for setting up Toolforge access, getting credentials,
and troubleshooting common issues.

### 🧩 Environment Template (`assets/.env.example`)

```bash
cp assets/.env.example .env
# Edit .env with your credentials
```

### 🧩 Sample SQL Queries (`assets/sample-queries.sql`)

50+ pre-built SQL queries organized by category (page info, stats, categories,
pageviews, Wikidata, revisions, links, cross-refs, user activity).

---

## Cross-References

| Related Skill | Why |
|--------------|-----|
| **[wikimedia-toolforge](../wikimedia-toolforge/SKILL.md)** | SSH tunnel setup and Toolforge access |
| **[wikimedia-page-assessment](../wikimedia-page-assessment/SKILL.md)** | PageAssessments queries use the same database connection pattern |
| **[wikimedia-pageviews](../wikimedia-pageviews/SKILL.md)** | Pageview SQL queries via `page_props` table |
| **[wikimedia-api-access](../wikimedia-api-access/SKILL.md)** | User-Agent format for any follow-up API calls |