Import The YourBOX MySQL Catalog Into PostgreSQL

When To Use

Use this runbook the first time YourBOX deploys against PostgreSQL on a given environment. The 2020 MySQL dump (yourbox.sql) is the source. The runbook imports rows once, reconciles identifiers, then leaves PostgreSQL as the source of truth.

The runbook is idempotent. A second execution detects the already imported rows and exits with a zero changeset.

Preconditions

The PostgreSQL target is reachable through DB_DSN.
The Doctrine migrations have run to the latest revision.
The MySQL source dump is available at infra/migration/yourbox.sql.gz.
The application is in maintenance mode for the duration of the import.

Procedure

Confirm the migration revision.
Terminal window
```
php app/bin/console doctrine:migrations:current --no-debug
```
Expected result: prints the latest migration version with the comment add-storage-keys-and-tags-jsonb.

Stage the source dump.

gunzip --keep --to-stdout infra/migration/yourbox.sql.gz \
  | docker exec -i yourbox-mysql-import \
      mysql -uroot -proot YourBOX_legacy

Expected result: the MySQL container reports Query OK for every INSERT block. The container only exists for the import window.

Run the importer command.
Terminal window
```
php app/bin/console yourbox:migration:import-from-mysql \
  --mysql-dsn=mysql://root:root@127.0.0.1:3306/YourBOX_legacy \
  --batch-size=500 \
  --no-debug
```
Expected result: the command logs four phases (users, files, comments, votes) with the row count moved per batch and ends with Migration succeeded with N inserts, M skipped (already present).
Reconcile the storage keys.
Terminal window
```
php app/bin/console yourbox:migration:reconcile-storage-keys --dry-run
```
Expected result: the dry run reports the count of files rows whose URL_preview and file_url were absolute paths under the 2020 layout. The reconciliation maps each row to the new videos/YYYY/MM/<ulid>/source.<ext> form using the existing object key index in MinIO or Azure Blob.

Re run without --dry-run once the counts look correct:
Terminal window
```
php app/bin/console yourbox:migration:reconcile-storage-keys
```
Validate the foreign keys.
Terminal window
```
php app/bin/console yourbox:migration:validate-foreign-keys
```
Expected result: the command reports zero orphan rows for comments.video_id, votes.ref_id and files.uploader_id.
Reindex Meilisearch.
Terminal window
```
php app/bin/console catalog:search:reindex --no-debug
```
Expected result: the command reports the document count copied from PostgreSQL to Meilisearch. The count matches the published video count from PostgreSQL within one document (the in flight upload).

Exit maintenance mode.

kubectl -n yourbox annotate ingress yourbox \
  maintenance=disabled --overwrite
kubectl -n yourbox rollout restart deployment/yourbox

Expected result: traffic resumes and GET /health returns 200.

Run the post import smoke test.
Terminal window
```
bash tests/smoke/post-import-smoke.sh
```
Expected result: the catalog browse, the search and the playback of a sampled video succeed.

Validation

SELECT count(*) FROM users matches the row count in the MySQL dump.
SELECT count(*) FROM votes WHERE user_id IS NULL returns 0.
The Meilisearch index document count matches PostgreSQL.
kubectl -n yourbox logs deployment/yourbox --since=10m | grep ERROR returns no results.

If This Does Not Work

If the importer halts midway, re run with the same arguments. The importer skips already imported rows by primary key. Inspect the audit log table for the import action records to identify the last batch.

If Meilisearch reindex stalls, drop the index and restart it:

php app/bin/console catalog:search:reset
php app/bin/console catalog:search:reindex

Escalate to the platform on call when:

The importer reports orphan rows that the validate step cannot reconcile.
A storage key resolution fails for more than 1 percent of the files.
The maintenance window exceeds the announced duration.