Download the PHP package lolli/dbdoctor without Composer

TYPO3 DB doctor

• Mission
• Installation via composer
• Usage
  • Preparation
  • Agentic doctor
  • Human interface
  • Interactive mode
  • Exit values
  • Options
  • Postprocessing
• Strategy
• Impact on Frontend rendering
• Limits
• Versions
• Further hints
• FAQ
  • Will the functionality be made available in a backend GUI?
  • Can I manipulate health checks?
  • Can I add my own health checks?
  • Can I replace a health check of dbdoctor?
  • Does dbdoctor fix the number of relations stored in inline and MM fields?
  • Why does dbdoctor not use the TcaSchema API of the core?
  • This project is heavily driven by agentic coding. Can I trust it?
• Tagging and releasing

Mission

The mission of this extension is to find database inconsistencies that may have been introduced in a living TYPO3 instance over time, and to fix them.

As an example, when a page tree is deleted by an editor, it sometimes happens that most pages are properly set to deleted, but some pages are missed, or a content element on one page is not deleted. This leads to orphan pages or content elements in the database.

There can be many reasons to end up with invalid database state like the above: TYPO3 in general has no referential integrity constraints on database tables, inconsistencies can be triggered by a dying PHP process, a lost DB connection, a core bug, a buggy extension, a broken deployment, and more. Long-lived active instances that were upgraded through multiple major core versions tend to end up with something that isn't quite right anymore.

Such inconsistencies can lead to further issues. For instance, if a page is copied that has an orphaned localized record, the system tends to mess up localizations of the copied page, too. Editors then stumble and TYPO3 agencies have to do time-consuming debugging sessions to find out what went wrong.

This extension provides a CLI command that tries to find various such inconsistencies and gives admins options to fix them.

I'm not aware of other open extensions that try to achieve the same in a similar systematic way. The core lowlevel extension comes with a few commands that try to find and clean up some fishy DB state cases, but dbdoctor solves far more things. This extension is not a substitute for lowlevel commands. It will grow over time.

Installation via composer

The extension currently supports TYPO3 v13 and TYPO3 v14. It can be installed as non-dev dependency (not adding --dev to composer require): It has no impact on a live instance (except dependency injection definitions) as long as it is not actively executed via CLI. There are no TER releases after 2.2.0.

Usage

Preparation

The nature of the CLI command is to perform destructive database operations on your instance. As such, a few things should be kept in mind:

• [!!!] 💣 Create a fresh database backup dump before and after using the CLI interface. Ensure the recovery strategy actually works: Both the extension and the user can potentially get something wrong. We are dealing with low level database stuff here after all, so things can potentially go south rather quickly. See the "Further hints" section below, too.

• [!!!] Make sure the TYPO3 "Database analyzer" is happy and needs no new or changed columns or tables. An early check verifies missing tables and columns, but it is still a good idea to double-check before running dbdoctor.

• [!!!] There should not be any pending core upgrade wizards. dbdoctor currently does not check up-front if all upgrade wizards have been executed.

Agentic doctor

Judging findings on a large instance is the laborious part of using dbdoctor. A coding agent can do much of it: Documentation/AgentGuide.md tells agents how to run dbdoctor read-only with the JSON report, how to go through the report in a few steps, and how to judge findings, for instance whether fixing them changes the frontend. The agent ends with a verdict per check: harmless, needs a look, or the check seems wrong.

One prompt to an agent with shell access does it all, in the project root of the instance:

With dbdoctor installed already, the prompt starts with "Read …".

To make this permanent for a project, add a line to its AGENTS.md or CLAUDE.md:

Notes:

• Use a local copy of the instance, for instance a ddev project with a recent database dump: The agent queries the database, and the report contains record data. The guide has agents write it to var/ and remove it when done.
• The guide keeps the agent read-only: It does not run execute mode or change records, fixing stays your decision. It uses uids and TCA control fields only, and asks before reading labels like titles with --tier labels.
• The JSON report is experimental, so is the guide. Experiences with it are welcome as issues.

Human interface

Note dbdoctor is "runtime static" with TCA and database schema: Both are not expected to change meanwhile. When you are looking at single changes and decide to change TCA, then clear all caches and abort dbdoctor (press "a" in interactive mode) to start again. Failing to do so may lead to dbdoctor writing harmful changes to the database, depending on what you did with TCA.

The interface looks like this:

A run with TYPO3 v14: Healthy checks are a single "OK" line, a check that finds something shows its records and asks what to do.

The main command is a chain of single checks. They are done one by one. Affected record details can be shown on a per-page and a per-record basis to give a quick overview. The interface allows deleting or updating affected records, depending on the type of the check.

Interactive mode

When dbdoctor finds something to fix in (default) interactive mode, execution stops and waits for user input:

• e - EXECUTE suggested changes!
• s - SIMULATE suggested changes, no execution
• a - ABORT now
• r - RELOAD this check
• p - SHOW records by page
• d - SHOW record details
• ? - HELP

s, p, d and e work on the records found when the check ran: e executes exactly the changes s shows. Before executing, e runs the check again: When records changed meanwhile, it shows the new findings instead of executing. r runs the check again at any time.

Exit values

Exit values are bit masks: Integer 3 means: "Changes needed or done" AND "User abort"

• 0 - No changes needed or done
• 1 - Changes needed or done
• 2 - User abort
• 4 - Error occurred

Options

The CLI command can be executed with a couple of options. The default mode is "interactive", prompting for user input after each failed check.

• Help overview:

  Left to the reader to find out what is done here :-P

• Interactive mode: --mode interactive or -m interactive or option not given:

  The default mode: Perform checks one-by-one and have an interactive interface to see affected record details, show pages with affected records, simulate performed queries, reload check and ultimately execute queries.

• Check mode: --mode check or -m check:

  Run all checks but don't perform any DB changes. Returns 0 (zero) if all checks are fine and non-zero if something was found by any check. Useful to verify a fix run was complete, and as cron job to see if any check "goes red" over time after everything has been fixed once. Some checks rely on earlier checks having fixed their findings: Since check mode fixes nothing, such a check is skipped with a warning when it runs into an unfixed finding of an earlier check.

• Execute mode: --mode execute or -m execute:

  Blindly execute without further questions! This will execute all update and delete queries dbdoctor suggests! This is a potentially destructive auto-operation if you trust the command, which you shouldn't ;-) Did you create a DB backup before? Note the -f option is mandatory with this mode: You must log executed queries to a dump file that does not exist yet, to at least give you the theoretical option to debug issues after dbdoctor destroyed your database. The -f option should thus have some date or similar in it, to make it unique.

• Log executed queries to file: --file or -f:

  Log all data changing queries to a file. The argument must be an absolute file name. Never put such a file into the public web folder of your instance. Option -f is available in "interactive" and mandatory in "execute" mode. Executed data changing queries are not only displayed, but also logged to a file. This can be useful if the command has been executed on a staging system using a current live database image: The queries can be reviewed again and then executed on a live instance using something like mysql my_database < file.sql or similar for other DBMS.

• Verbose output: --verbose or -v:

  Checks without affected records are a single "OK" line by default, details are shown for checks that found something only. With -v, all checks show their title, actions and description.

• Structured output (experimental): --format json, with check mode only:

  A read-only report as JSON on stdout or, with --output, as files in a directory, for tools and agents: run data, the tables found, and per check its status, counts, facets and findings. Ok and skipped checks have their status only, the description of a check comes with its findings. A finding carries its key (record, record field or MM rows), the record with TCA control fields like deleted, language and workspace under names that are the same for all tables, the reason and the changes dbdoctor would do, with old and new values. Counts per table come with the affected rows and their share of the table: A check flagging more than half of a table, and more than 100 rows, marks it suspicious, which rather hints to a mismatch of check and TCA than to broken data. Groups, pages with their rootline and translations, and site language fallbacks help judging findings, see Documentation/AgentGuide.md. The exit code is the same as in check mode.

  The economical way through a large report is --output: An index, then one check, then samples of its findings, read from files. On stdout, --scope all --limit 0 lists all checks with counts, facets and groups, but no findings and no tables. --check then shows the findings of one check, --table and --uid, --pid or --subtree narrow them down further. Further options:

  • --scope until-first-finding (default): Checks after the first one that is not ok are not run, they rely on the findings being fixed. --scope all runs all checks and marks results after the first not ok check as preliminary.
  • --check <class>: Report only this check, repeatable. Earlier checks still run.
  • --table <table>, --uid <uid> (with --table), --pid <page>, --subtree <page>: Report only findings of this table, record, page, or page and its sub pages. Counts and facets are of the filtered findings, counts.total has the number of all findings. Checks with findings get filterMatches: Checks with no finding matching the filter are still listed, they are not ok, but only with their counts.
  • --limit <n>: Findings per check, default 100, 0 for none (summary), all for all. Counts, facets and groups are always complete. Facets count findings per operation, reason code, record state, visibility, language, age, site root page and write pattern.
  • --output <directory>: Write the report as files into a new directory instead of stdout, for reports too large to read at once: index.json with the run data and one entry per check, per check with findings a check file with counts, facets and groups, and a findings file with all its findings as JSON Lines, one finding per line, and tables.jsonl and pages.jsonl with one table or page per line. Findings are written while the checks run and not kept in memory. --limit is all unless given. A relative directory is relative to the current directory. The files contain record data: Do not write them into a public folder, and remove them when done.
  • --compact: JSON without whitespace, about half the size. Recommended for tools and agents.
  • --context basic (default): The report has the rootlines of the pages of findings, each finding the language parent and translation source of its record. --context none leaves them out.
  • --tier structure (default): uids, pids, TCA control fields, timestamps and written fields. --tier labels adds TCA label fields and site identifiers, never for tables with personal data like be_users and fe_users. Labels only identify records: Tags are stripped and they are cut to 80 characters, as declared in run.options.labels.

  The report is experimental: While its schemaVersion is 0.x, its structure may change with any release, there is no compatibility promise yet. Checks, their names and their order are no API in any case.

  For agents reading the report, see Agentic doctor.

Postprocessing

• [!!!] Run the reference index updater after this command has finished! It is very likely it will update something. A clean reference index becomes more and more important with newer core versions. The CLI command to do this: bin/typo3 referenceindex:update.

Strategy

The strategy of this command is to check for single things one-at-a-time and to fix them before going to the next check. Updates and deletes of not-ok records are done with low-level database queries directly, not using the DataHandler.

Single checks are carefully crafted and functionally tested and the order in which they are executed is important.

A single run does not guarantee a clean database: Fixing something in a later check can create new findings for an earlier check, for instance when a deleted record leaves sys_file_reference rows pointing to nothing. Run dbdoctor again until a run finds nothing, -m check returns 0 (zero) then.

Single checks rather try to avoid memory consumption and assumed state at the cost of more queries being executed. Queries are often performed as prepared statements to re-use them often in a single check. Statements are properly closed when a single check has finished, effectively using the PHP garbage collection. All-in-all, this command should perform relatively well even with big-sized instances, but it will hammer the database a lot.

Impact on Frontend rendering

When a health check finds something fishy, dbdoctor allows only one hard coded solution to deal with it. The user is not asked for a solution, they either accept the proposed UPDATE or DELETE database changes, or need to abort and take care manually (and then restart).

Implementing a per-record question/answer feature in dbdoctor is not feasible since this would add an orthogonal vector of complexity to the system which would quickly render it unmaintainable: Single checks are designed to work on top of each other, dbdoctor needs to establish a "chain of correctness" to do its job.

There are usually three options for a specific "fix":

• Hard delete the offending record
• Set the record deleted=1 for soft-delete aware tables
• Update the record to something "more correct"

In rare cases, a record that belongs to another one is missing, and dbdoctor inserts it, for instance the default language sys_file_metadata record of a sys_file record.

The general strategy is to create as little damage as possible from a TYPO3 Frontend rendering point of view.

For example, when there are two localizations for a default language record in a specific language, dbdoctor detects this as invalid and suggests setting one of them to deleted=1. Of the two records, it tries to set the one to deleted that is typically not rendered in Frontend.

This general strategy isn't always as simple as with the above example, though: Since the TYPO3 Frontend rendering is so flexible, the actual rendered record sometimes depends on specific Frontend rendering details dbdoctor can't know. In those cases, dbdoctor tries to guess the least amount of damage. This may not always fit real life cases. The only solution to deal with this is to look at single record change suggestions individually. The interactive options p, d and s hopefully help to classify single suggested changes.

Limits

Even though this low level tool tries to be very careful and checks lots of details before suggesting a change, there are still some limits and assumptions: For example, the "delete" column of soft-delete aware TCA tables is assumed to be an integer column, and not a text or varchar or similar. The correct schema of this column is usually created by the core as long as there is no explicit definition of it in a ext_tables.sql file. However, if an extension gets this wrong and defines such a field in some broken way, dbdoctor may cause damage by suggesting deletes or updates of all rows.

There are further assumptions: For instance, dbdoctor assumes some TCA settings the core provides for standard tables (especially pages, tt_content and sys_file_reference) are not changed by extensions. For example, those tables are assumed to be both soft-delete aware and workspace aware, the corresponding fields are queried by dbdoctor on such tables, and dbdoctor will fail if an extension tampered with the corresponding TCA ctrl settings.

There are further scenarios dbdoctor cannot deal with: For example, let's say some extension declares a table soft-delete-aware by having a TCA entry ['ctrl']['delete'] = 'deleted', and you have some rows that are deleted=1. Later, that TCA table is set to be no longer soft-delete-aware by removing the ['ctrl']['delete'] declaration. The core database analyzer will then suggest first renaming the deleted column to zzz_deleted_deleted, and will then allow removing the column. Doing this will effectively push all previously deleted records "live", if you failed to remove all affected deleted=1 records beforehand. There are similar scenarios when TCA tables are changed to be no longer workspace-aware, but you still have workspace related records in the table, or when TCA tables are no longer "starttime" / "endtime" aware while there are still timed records in the table.

dbdoctor always works on the current TCA state. It never knows if some TCA table has been defined "soft-delete-aware" before, and if this has been changed later. When you push records live by removing the "deleted" column, by removing the "workspaces" extension, workspaces related columns, or timing related fields, this can end up in a broken state dbdoctor is not able to fix. Instead, it will tend to find additional database relations that are broken, and will suggest changes that make the situation worse than before. Also, dbdoctor never looks at potentially existing zzz_deleted columns - those do not exist from dbdoctor's point of view since they depend on some "before" TCA state that cannot be reconstructed. State created by scenarios like the above is not repairable and needs manual reconstruction. Good luck.

All in all, TCA and ext_tables.sql of extensions should be in good shape before working with dbdoctor, and changes suggested by health checks should always be checked manually before committing them to the database. Also, never forget to back up the database to prepare for a disaster recovery that may become necessary. Do not accept dbdoctor suggestions blindly!

Versions

• 0.x: TYPO3 v11 & v12
• 1.x: TYPO3 v12 & v13
• 2.x: TYPO3 v13 & v14

dbdoctor supports the PHP versions and database systems of the TYPO3 core versions it supports.

Further hints

I highly encourage admins to back up databases when working with dbdoctor. Some basic rules regarding SQL dumps must not be forgotten when doing this:

• When dumping an existing MySQL / MariaDB database before and after executing the CLI command, it can be helpful to turn off the "extended inserts" option: mysqldump by default merges multiple INSERT statements into one call for efficiency and speed. This is both quicker to dump and to import, and consumes less disk space.

  However, when looking for single DB changes, it is much more convenient to turn this off and have one line for each inserted row. The output of tools like diff is then far easier to read when searching for something that may have gone wrong. Example shell commands:

  It's always possible to deviate from this when you know what you're doing, though. In practice, it might be a good idea to create two dumps: One with --skip-extended-insert and one without. A disaster recovery is much quicker when loading from a file that has no "one row per line", but to debug, it's much easier to diff dumps that were based on skipped extended inserts.

• When dumping databases, it is a crucial security measure to never put such dumps into a public directory accessible by a web server or some third party server user. Violating this basic rule is a common source of data leaks in the wild! There is no excuse to get this wrong. It is also a good idea to put SQL files in a place that is rotated into backups to allow debugging later in case issues only pop up after a while. To follow GDPR rules, those files should still be removed at some point!

• When dumping databases, it is often a good idea to gzip .sql files: This typically reduces file size by a factor of around eight. Let's save some precious server disk and backup size! It's also possible to directly 'pipe' to gzip when dumping. Either do that, or remember to gzip stuff before logging out of a system.

FAQ

Will the functionality be made available in a backend GUI?

No. CLI is the only sane way for this kind of thing.

Can I manipulate health checks?

Not via the CLI: It has no options to skip or configure checks, every check always fixes things the same way. Options would multiply the possible system states and render maintenance of this already complex project impossible. Programmatically, ModifyHealthClassListEvent can remove or reorder checks, for instance to skip checks tagged as risky, and disables replaces a check, see below. Both work, but you are on your own: Read the event class comment first.

Can I add my own health checks?

Technically yes, but you are on your own. Health checks are collected via dependency injection: Every autoconfigured service implementing HealthCheckInterface is tagged lolli.dbdoctor.health automatically and becomes part of the chain, also when it lives in a different extension. Its position is determined by the before and after tag attributes in Services.yaml, referencing identifiers of other checks. Neither the identifiers, nor the order, nor HealthCheckInterface are API: dbdoctor adds, renames, reorders or removes checks at any time. A check without before and after ends up at some position nobody chose. If before and after contradict each other, for instance after dbdoctor reordered checks, building the dependency injection container fails with an exception naming the cycle. Do not additionally add such a check using ModifyHealthClassListEvent, it would run twice.

Can I replace a health check of dbdoctor?

Same as above: Yes, but you are on your own. Tag your check with disables, the identifier of the check it replaces:

The replaced check is not executed, and your check runs at its position in the chain, no before or after needed. A tag replaces exactly one check. To replace multiple checks, add one tag with its own identifier per replaced check. Identifiers in disables that do not exist are ignored silently: When dbdoctor renames or removes a check you replaced, the dbdoctor check may run again, and yours ends up at some position nobody chose. Verify your replacements after each dbdoctor update.

Does dbdoctor fix the number of relations stored in inline and MM fields?

No. The field of a record with inline children (TCA foreign_field) or MM relations (TCA MM) stores the number of relations, for instance tt_content.image or sys_category.items. The relations themselves live in the child or MM table, and the core reads them from there, not from this count. The count is not reliable: For instance, sys_category.items is not updated when categories are assigned from the other side of the relation, like the categories field of a content element, and discarding workspace changes removes MM rows without touching counts. dbdoctor ignores these count fields: It does not check them, and checks that remove child records or MM rows do not update them.

Why does dbdoctor not use the TcaSchema API of the core?

On purpose. TcaHelper and a few checks mimic parts of TcaSchema, which exists since TYPO3 v13. But dbdoctor changes and deletes records, and which tables and fields a check touches must follow rules dbdoctor defines and tests itself. TcaSchema is an interpretation layer of the core on top of the TCA array, and it may change its semantics in any core release, including bugfix releases, without dbdoctor noticing. The TCA array is the more stable contract: Extensions write it, the core migrates outdated syntax before dbdoctor reads it. Also, the relation related checks depend on details like foreign_table_field, MM_opposite_field or MM_match_fields that TcaSchema does not model, so dbdoctor would read the TCA array anyway.

This project is heavily driven by agentic coding. Can I trust it?

No. But probably more than before. That "No" holds for any tool that changes your data, and it is why this README elaborates on backups, replay and recovery so much: If dbdoctor destroyed something and you have neither a backup nor an audit trail of the executed queries, there is no mercy. See "Preparation" above. And yes, large parts of the code are written with the help of agents nowadays. When I picked up this project again in 2026, there were many loose ends, missing features and various architectural flaws. I was able to fix a lot of them within a few days, and I am confident the result is far better than what I could have achieved without agents. This works because of the harness around it: Every check has a functional test with fixtures of the broken and the fixed state, CI runs all of them on all supported PHP versions with mysql, mariadb, postgres and sqlite, and changes are tested against several real-life instances. I look at the details and discuss options at length. And most importantly: I know the problem domain. I worked on the database details of the TYPO3 core for years and refactored major parts of it. Simply put: You can trust dbdoctor more than anyone who starts an agent with "Rewrite DataHandler" in 2026.

Tagging and releasing

packagist.org is enabled via the usual GitHub hook. Each tag gets a GitHub release, too: Create it in the GitHub web interface from the tag, using "Generate release notes".

Example: