Improve database exception messages [#3609986]

Postponed on #3622582: Cleanup database exceptions

Spin off from #3399150: [PP-1] Enable dynamic queries to produce SQL with positional placeholders.

Problem/Motivation

While working on #3399150: [PP-1] Enable dynamic queries to produce SQL with positional placeholders, I got fed up by the low readability of the SQL strings dumped when exceptions occur, both in PHPUnit (or other CLI) output and in site pages.

So I spent some time trying to improve them.

Before CLI
before html

After CLI
before html

Before HTML

After HTML
before html

Proposed resolution

Introduce a dev dependency for doctrine/sql-formatter, which has a good tokenizer and CLI / HTML dumpers.
Use doctrine/sql-formatter for dumping SQL strings and symfony/var-dumper for dumping the placeholders values.
Create an utility helper class to allow formatting, self-detecting whether CLI or HTML output should be produced.
Use the class for formatting output produced by database ExceptionHandler implementations.

Remaining tasks

User interface changes

API changes

Data model changes

Release notes snippet

`doctrine/sql-formatter` Dependency Evaluation

Approaching 190Mio downloads from Packagist, >200k downloads per day
https://github.com/doctrine/sql-formatter

Maintainership of the package

Maintainership of the package: maintained by the Doctrine team; apparently actively maintained, 6 releases in the last 2 years.

Security policies of the package

Security policies of the package: unspecified, but this is a dev dependency

Expected release and support cycles

Expected release and support cycles: best effort?

Code quality

Code quality: documented, PHPUnit tests present, PHPStan max level

Other dependencies it would add, if any (the full tree, not just direct dependencies), and evaluations for those dependencies as well

None

Comment	File	Size	Author
#39	3609986-nr-bot_54w7zc0e.txt	91 bytes	needs-review-queue-bot
#35	3609986-nr-bot_b7f4_bg4.txt	1.07 KB	needs-review-queue-bot
#32	3609986-nr-bot_l8sx68c6.txt	98 bytes	needs-review-queue-bot
#27	3609986-nr-bot_xqtq4qxv.txt	98 bytes	needs-review-queue-bot
#25	3609986-nr-bot_kxks46v3.txt	91 bytes	needs-review-queue-bot
#21	3609986-nr-bot_mrd461_m.txt	98 bytes	needs-review-queue-bot
#19	3609986-nr-bot_mqtz3fq3.txt	91 bytes	needs-review-queue-bot
#17	3609986-nr-bot_0ijhgn_e.txt	91 bytes	needs-review-queue-bot
#2	html before.png	308.1 KB	mondrake
#2	html after.png	159.44 KB	mondrake
#2	cli after.png	722.37 KB	mondrake
#2	cli before.png	586.31 KB	mondrake

Issue fork drupal-3609986

Show commands

Start within a Git clone of the project using the version control instructions.

Add & fetch this issue fork’s repository

Or, if you do not have SSH keys set up on git.drupalcode.org:

Add & fetch this issue fork’s repository

3609986-improve-database-exception changes, plain diff MR !16289
Check out this branch for the first time

Check out existing branch, if you already have it locally

About issue forks

Comments

Comment #1

10 July 2026 at 19:07

mondrake created an issue. See original summary.

Comment #2

mondrake

🇮🇹

commented 10 July 2026 at 19:16

Issue summary:

View changes

Status	File	Size
new	cli before.png	586.31 KB
new	cli after.png	722.37 KB
new	html after.png	159.44 KB
new	html before.png	308.1 KB

Comment #3

mondrake

🇮🇹

commented 10 July 2026 at 19:18

Issue summary:

View changes

Comment #4

mondrake

🇮🇹

commented 10 July 2026 at 19:39

Issue summary:

View changes

Comment #5

10 July 2026 at 19:54

mondrake opened merge request !16289

Comment #6

mondrake

🇮🇹

commented 10 July 2026 at 20:05

Status:

Active

» Needs review

Comment #7

longwave

he/him

English

commented 10 July 2026 at 20:06

Can the SQL formatter actually do the placeholder replacement as well? Sometimes I want to copy and paste the statement and run it manually - and that is always painful (the quoted field names don't help here either, but that's a separate problem)

Having said that it might be better to show the placeholders separately for some cases, so not sure we always want this.

Comment #8

longwave

he/him

English

commented 10 July 2026 at 20:08

This will need to be a runtime dependency if we are formatting runtime exception messages with it.

Comment #9

mondrake

🇮🇹

commented 10 July 2026 at 21:42

There’s this https://github.com/doctrine/sql-formatter#compress-query but we’d have to do the placeholder replacements ourselves, which is tricky as it requires escaping that is Connection dependent. The library itself is db agnostic. Maybe we could do that for tests putting up a static flag to say whether a full replaced SQL string should be produced on top. My use case was different from yours, I am mainly checking for named placeholders in strings that should only have positional ones (see parent).

The MR has protection to do this only in dev (symfony/var-dumper is a dev dep, so by extension I thought to limit to dev also the sql formatting).

Comment #10

mondrake

🇮🇹

commented 10 July 2026 at 21:45

the quoted field names don't help here either

Why so? Theoretically quoting column names and other db objects should be the most accurate, no?

Comment #11

longwave

he/him

English

commented 10 July 2026 at 22:34

When I copy and paste the quoted names into an SQL client then the statement won't execute until I manually remove the quotes.

Comment #12

mondrake

🇮🇹

commented 11 July 2026 at 07:41

#11: weird.

Anyway if we get in the business of replacing the placeholders with their concrete escaped values, I think we can also get in the one of removing the quotes from the identifiers.

TBH I think #7 to #11 would be better dealt with in a follow-up: it’s additional req vs the need of showing the statement exactly as is executed by Drupal.

Comment #13

mondrake

🇮🇹

commented 11 July 2026 at 07:48

btw #3571186: mysqli - skip prepared statement to allow async query execution is exactly doing the replacement of placeholders with their values - but that’s done at execution time and is mysqli only

Comment #14

mondrake

🇮🇹

commented 11 July 2026 at 10:11

I was really puzzled by #11 so I investigated a bit.

Used a MariaDb client.

The problem is that Drupal produces ANSI standard SQL that uses double quotes to escape identifiers; at least MariaDb SQL client uses backticks by default instead. If you change " with the backtick character, the syntax passes. Alternatively, you can execute
SET sql_mode = 'ANSI,TRADITIONAL';
in the CLI before executing a Drupal statement string (which is what Drupal does in the Connection constructor, and that explains why Drupal is not failing) and double quotes will then be recognized as valid quotes for database identifiers.

Comment #15

mondrake

🇮🇹

commented 12 July 2026 at 05:48

Status:

Needs review

» Needs work

Mmm this now will call expensive dumping for every database exception, but many are caught before producing any output. Let’s try to restrict dumping to only when it’s needed.

Comment #16

mondrake

🇮🇹

commented 12 July 2026 at 18:52

Status:

Needs work

» Needs review

Done #15

Comment #17

needs-review-queue-bot commented 13 July 2026 at 08:45

Status:

Needs review

» Needs work

Status	File	Size
new	3609986-nr-bot_0ijhgn_e.txt	91 bytes

The Needs Review Queue Bot tested this issue. It no longer applies to Drupal core. Therefore, this issue status is now "Needs work".

This does not mean that the patch necessarily needs to be re-rolled or the MR rebased. Read the Issue Summary, the issue tags and the latest discussion here to determine what needs to be done.

Consult the Drupal Contributor Guide to find step-by-step guides for working with issues.

Comment #18

mondrake

🇮🇹

commented 16 July 2026 at 15:31

Status:

Needs work

» Needs review

Comment #19

needs-review-queue-bot commented 21 July 2026 at 11:06

Status:

Needs review

» Needs work

Status	File	Size
new	3609986-nr-bot_mqtz3fq3.txt	91 bytes

The Needs Review Queue Bot tested this issue. It no longer applies to Drupal core. Therefore, this issue status is now "Needs work".

This does not mean that the patch necessarily needs to be re-rolled or the MR rebased. Read the Issue Summary, the issue tags and the latest discussion here to determine what needs to be done.

Consult the Drupal Contributor Guide to find step-by-step guides for working with issues.

Comment #20

mondrake

🇮🇹

commented 21 July 2026 at 17:39

Status:

Needs work

» Needs review

Comment #21

needs-review-queue-bot commented 22 July 2026 at 11:03

Status:

Needs review

» Needs work

Status	File	Size
new	3609986-nr-bot_mrd461_m.txt	98 bytes

The Needs Review Queue Bot tested this issue. The merge request has merge conflicts and cannot be merged. Therefore, this issue status is now "Needs work".

This does not mean that the patch necessarily needs to be re-rolled or the MR rebased. Read the Issue Summary, the issue tags and the latest discussion here to determine what needs to be done.

Consult the Drupal Contributor Guide to find step-by-step guides for working with issues.

Comment #22

mondrake

🇮🇹

commented 22 July 2026 at 11:52

Status:

Needs work

» Needs review

Comment #23

mondrake

🇮🇹

commented 22 July 2026 at 15:17

Status:

Needs review

» Needs work

We may just pass the value object in SqlDumper::export() instead of the 3 args.

Comment #24

mondrake

🇮🇹

commented 24 July 2026 at 14:23

Status:

Needs work

» Needs review

Comment #25

needs-review-queue-bot commented 25 July 2026 at 10:56

Status:

Needs review

» Needs work

Status	File	Size
new	3609986-nr-bot_kxks46v3.txt	91 bytes

The Needs Review Queue Bot tested this issue. It no longer applies to Drupal core. Therefore, this issue status is now "Needs work".

This does not mean that the patch necessarily needs to be re-rolled or the MR rebased. Read the Issue Summary, the issue tags and the latest discussion here to determine what needs to be done.

Consult the Drupal Contributor Guide to find step-by-step guides for working with issues.

Comment #26

mondrake

🇮🇹

commented 25 July 2026 at 15:36

Status:

Needs work

» Needs review

rebased

Comment #27

needs-review-queue-bot commented 29 July 2026 at 10:59

Status:

Needs review

» Needs work

Status	File	Size
new	3609986-nr-bot_xqtq4qxv.txt	98 bytes

The Needs Review Queue Bot tested this issue. The merge request has merge conflicts and cannot be merged. Therefore, this issue status is now "Needs work".

This does not mean that the patch necessarily needs to be re-rolled or the MR rebased. Read the Issue Summary, the issue tags and the latest discussion here to determine what needs to be done.

Consult the Drupal Contributor Guide to find step-by-step guides for working with issues.

Comment #28

quietone commented 29 July 2026 at 11:13

I saw from the issue summary that this is adding a dependency, so it should have an evaluation added to the issue summary per Core dependency evaluation criteria. Thanks.

Comment #29

mondrake

🇮🇹

commented 29 July 2026 at 11:15

Status:

Needs work

» Needs review

rebased

Comment #30

mondrake

🇮🇹

commented 29 July 2026 at 11:28

Issue summary:

View changes

added dependency evaluation

Comment #31

daffie commented 29 July 2026 at 15:39

Issue tags:

+Needs Needs framework manager review, +Needs release manager review

According to the "Criteria for adding dependencies" there needs to be done a framework managers sign off and a release managers sign off.

Comment #32

needs-review-queue-bot commented 12 August 2026 at 10:31

Status:

Needs review

» Needs work

Status	File	Size
new	3609986-nr-bot_l8sx68c6.txt	98 bytes

The Needs Review Queue Bot tested this issue. The merge request has merge conflicts and cannot be merged. Therefore, this issue status is now "Needs work".

This does not mean that the patch necessarily needs to be re-rolled or the MR rebased. Read the Issue Summary, the issue tags and the latest discussion here to determine what needs to be done.

Consult the Drupal Contributor Guide to find step-by-step guides for working with issues.

Comment #33

quietone commented 13 August 2026 at 10:55

Issue summary:

View changes

Convert the dependency evaluation to the template used in the policy.

Comment #34

mondrake

🇮🇹

commented 23 August 2026 at 14:20

Status:

Needs work

» Needs review

rebased

Comment #35

needs-review-queue-bot commented 23 August 2026 at 14:26

Status:

Needs review

» Needs work

Status	File	Size
new	3609986-nr-bot_b7f4_bg4.txt	1.07 KB

The Needs Review Queue Bot tested this issue. It fails the Drupal core commit checks. Therefore, this issue status is now "Needs work".

This does not mean that the patch necessarily needs to be re-rolled or the MR rebased. Read the Issue Summary, the issue tags and the latest discussion here to determine what needs to be done.

Consult the Drupal Contributor Guide to find step-by-step guides for working with issues.

Comment #36

mondrake

🇮🇹

commented 23 August 2026 at 16:57

Status:

Needs work

» Needs review

Comment #37

daffie commented 3 September 2026 at 07:09

Assigned:

Unassigned

» daffie

Working on this.

Comment #38

mondrake

🇮🇹

commented 3 September 2026 at 09:20

Issue tags:

-Needs Needs framework manager review

+Needs framework manager review

Comment #39

needs-review-queue-bot commented 6 September 2026 at 10:15

Status:

Needs review

» Needs work

Status	File	Size
new	3609986-nr-bot_54w7zc0e.txt	91 bytes

The Needs Review Queue Bot tested this issue. It no longer applies to Drupal core. Therefore, this issue status is now "Needs work".

This does not mean that the patch necessarily needs to be re-rolled or the MR rebased. Read the Issue Summary, the issue tags and the latest discussion here to determine what needs to be done.

Consult the Drupal Contributor Guide to find step-by-step guides for working with issues.