Skip to content
This repository was archived by the owner on Sep 18, 2026. It is now read-only.
This repository was archived by the owner on Sep 18, 2026. It is now read-only.

Schema: Remove UNIQUE KEY room_type constraint #10

Closed

Description

@josephfusco

Our `UNIQUE KEY room_type (room(191), type)` constraint doesn't match core's pattern and prevents storing multiple updates per room.

Current:
```sql
UNIQUE KEY room_type (room(191), type)
```

Core (PR #11256):
No unique constraint. Multiple rows per room+type are normal for updates.

Why we have it:
We originally tried to store awareness (one row per room) and updates (many rows per room) in the same table. The UNIQUE constraint allows multiple NULL type values (MySQL quirk) but only one non-NULL value per room.

Why it's wrong:

  • Core stores multiple update types: `update`, `sync_step1`, `sync_step2`, `compaction`
  • Multiple compaction operations can exist for the same room
  • UNIQUE constraint would break legitimate writes

Fix: Remove the UNIQUE constraint entirely. With Presence API handling awareness separately, we don't need this MySQL NULL hack.

Core indexes:
```sql
KEY type_client_id (type,client_id),
KEY room (room,collaboration_id),
KEY room_type_date (room,type,date_gmt),
KEY date_gmt (date_gmt)
```

Reference: WordPress/wordpress-develop#11256

Activity

  1. josephfusco commented on Aug 17, 2026

    @josephfusco
    CollaboratorAuthor

    Fixed in commit. Removed UNIQUE KEY room_type constraint from schema. With composite storage (Presence API for awareness, wp_collaboration for CRDT only), this constraint is unnecessary and could prevent legitimate multi-update scenarios.

    Schema after fix:

    PRIMARY KEY (id),
    KEY room_id (room(50), id),
    KEY room_timestamp (room(50), timestamp)

    No more UNIQUE constraint. Verified with SHOW CREATE TABLE after fresh activation.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions