Skip to content
V
For developers
Architecture, conventions, the core and security
Getting started / Database check

Database check#

A set of queries to run when deploying the portal onto an existing database — typically when an older installation is upgraded to a new version of the system. They reveal schema defects that otherwise surface months later as inexplicably duplicated or blank text.

Everything here is read-only SELECTs. Nothing is modified, so it is safe to run against production.

Script: sql/2026-09-03_audit_texts_tables_primary_keys.sql

Why this is checked#

Every *_texts translation table is supposed to have a composite primary key:

PRIMARY KEY (`<entity>_id`, `cms_system_languages_language_id`)

The key is not cosmetic — it is the only thing preventing one entity from holding two different translations in the same language. Where it is missing, three problems compound:

What is missing Consequence
primary key the database accepts duplicate rows
enforced identity Doctrine maps a composite identity the database does not hold — nothing guarantees which row is read
unique index ON DUPLICATE KEY UPDATE has nothing to collide with and silently inserts another row instead of updating

The last point is the insidious one: seeds are written to be idempotent, so they get re-run — and every run adds more duplicates.

validateClass will not catch this

SchemaValidator::validateClass compares columns and types, not indexes. A missing primary key therefore passes every automated check. That is why this manual audit exists.

When to run it#

  • Before deploying a new version onto an older database — so you know what you are inheriting.
  • After any schema change that created or altered *_texts tables.
  • When investigating reports like "the site shows different text than the admin" or "the translation reverted to the old value after saving".

1. Which tables lack the language in their primary key#

SELECT t.TABLE_NAME AS table_name,
       COALESCE(GROUP_CONCAT(k.COLUMN_NAME ORDER BY k.SEQ_IN_INDEX),
                '(no PK)') AS primary_key
  FROM information_schema.TABLES t
  LEFT JOIN information_schema.STATISTICS k
         ON k.TABLE_SCHEMA = t.TABLE_SCHEMA
        AND k.TABLE_NAME   = t.TABLE_NAME
        AND k.INDEX_NAME   = 'PRIMARY'
 WHERE t.TABLE_SCHEMA = DATABASE()
   AND t.TABLE_NAME LIKE '%\_texts'
   AND t.TABLE_NAME NOT LIKE 'bak\_%'
   AND t.TABLE_NAME NOT LIKE 'tmp\_%'
 GROUP BY t.TABLE_NAME
HAVING primary_key NOT LIKE '%cms_system_languages_language_id%'
 ORDER BY t.TABLE_NAME;

A healthy database returns 0 rows.

Backup and temporary tables (bak_, tmp_) are excluded deliberately — copies are not supposed to carry the key and would only clutter the output.

2. Duplicate translations#

This cannot be done in a single query: every table names its FK column differently. So this query generates the queries for you — copy the output and run it.

SELECT CONCAT(
         'SELECT ''', t.TABLE_NAME, ''' AS table_name, `', c.COLUMN_NAME,
         '` AS entity, cms_system_languages_language_id AS lang, ',
         'COUNT(*) AS cnt FROM `', t.TABLE_NAME,
         '` GROUP BY 1,2,3 HAVING cnt > 1;'
       ) AS run_this
  FROM information_schema.TABLES t
  JOIN information_schema.COLUMNS c
         ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
        AND c.TABLE_NAME   = t.TABLE_NAME
        AND c.COLUMN_NAME LIKE '%\_id'
        AND c.COLUMN_NAME <> 'cms_system_languages_language_id'
  LEFT JOIN information_schema.STATISTICS k
         ON k.TABLE_SCHEMA = t.TABLE_SCHEMA
        AND k.TABLE_NAME   = t.TABLE_NAME
        AND k.INDEX_NAME   = 'PRIMARY'
 WHERE t.TABLE_SCHEMA = DATABASE()
   AND t.TABLE_NAME LIKE '%\_texts'
   AND t.TABLE_NAME NOT LIKE 'bak\_%'
 GROUP BY t.TABLE_NAME, c.COLUMN_NAME
HAVING COALESCE(GROUP_CONCAT(k.COLUMN_NAME), '')
       NOT LIKE '%cms_system_languages_language_id%'
 ORDER BY t.TABLE_NAME;

3. Language completeness#

Another generator — it counts rows per language for every translation table.

SELECT CONCAT(
         'SELECT ''', TABLE_NAME, ''' AS table_name, ',
         'SUM(cms_system_languages_language_id=1) AS cs, ',
         'SUM(cms_system_languages_language_id=2) AS en, ',
         'SUM(cms_system_languages_language_id=3) AS sk ',
         'FROM `', TABLE_NAME, '`'
       ) AS run_this
  FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA = DATABASE()
   AND COLUMN_NAME  = 'cms_system_languages_language_id'
   AND TABLE_NAME LIKE '%\_texts'
   AND TABLE_NAME NOT LIKE 'bak\_%'
 ORDER BY TABLE_NAME;

The order of the steps is not arbitrary

Clean up duplicates (step 2) first, only then count missing translations (step 3). A table with duplicates looks as if a language were missing — in reality it has another language too many.

A real example from the 2026-09-03 review: notification types reported 12/12/9, which looks like three missing Slovak translations. After removing duplicates it came out 9/9/9 — none were missing, Czech and English were simply sitting there four times each.

Reading the step 3 results#

Unequal counts are not a defect by themselves. Distinguish two kinds of table:

Kind Tables Inequality means
system registries privileges, privilege groups, notification types, user groups, routes a hole — the admin shows blank where text should be
editorial content cms_system_image_texts, cms_system_file_texts, cms_mod_blog_intro_texts normal — nobody translated that image caption

What to do with a finding#

  1. Back up the affected tables. ALTER TABLE cannot be rolled back in a transaction.
  2. Clean up the duplicates. They are usually either byte-identical copies (keep one) or an empty inactive row next to a filled one (delete the empty one).
  3. Add the primary key.
  4. Verify that ON DUPLICATE KEY UPDATE now really updates — run the same seed twice, the row count must not change.

Both halves are modelled in sql/2026-09-03_04_texts_tables_missing_primary_keys.sql (the fix) and sql/2026-09-03_96_..._rollback.sql (restore from backups).

The duplicate that cannot be resolved automatically

When one entity holds two different non-empty values in the same language, that is not a technical decision — somebody has to say which one is correct.

In the 2026-09-03 review, route 13 (expressExecute) had two English values while route 12 (execute) had no English at all, despite having Czech and Slovak. The text had landed there by mistake and belonged to route 12 — moving it removed the duplicate and the missing translation in one step. There was no way to tell without looking at the neighbouring records.