Hacktoberfest 2026: le issue che i maintainer hanno segnato per ottobre, aperte e adatte ai principianti. Sfoglia le issue Hacktoberfest

Database checks: `Select_full_join` warning is caused by the setup checks' own schema introspection

Aperta
  • Qualcosa non va?
#1,245 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
45/100
Tipo di issue
Bug
Chiarezza
Abbastanza chiara
Stato di attività
Attiva
Stack tecnologico
javascript, mariadb, php
Ambito
backend, databases, observability

Direzione di ricerca

Start by tracing the four named core setup checks and their SchemaWrapper construction, then inspect how serverinfo evaluates the Select_full_join threshold. Reproduce the reported increase by opening Administration → Overview against MariaDB with the described slow query filter. Done means Overview visits no longer cause this database check to fail; the issue lists downgrading the counter to a notice or excluding setup checks as possible approaches.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

Setup
  • Nextcloud 35.0.2, serverinfo 7.0.0
  • MariaDB 11.8.9 (Alpine package), local socket, same host
  • PHP 8.5, nginx
  • occ db:add-missing-indices, missing columns and missing primary keys all report nothing
Bug description

Overview reports "Joins without indexes" (Select_full_join) as a failing warning: 0.1378% of queries did a full join without an index (96 full joins out of 71,025 queries over 33 h; the threshold is 0.05%).

To find the queries, I set this for one hour:

SET GLOBAL log_slow_filter = 'full_join', long_query_time = 0, log_slow_verbosity = 'query_plan,explain';

Results:

  • Select_full_join went from 96 to 108, so there were 12 full joins in the hour.
  • All 12 came from one database connection within one second (10:09:00–10:09:01).
  • That second matches a single GET /index.php/settings/admin in the web server log, i.e. opening Administration → Overview.
  • The 12 are Doctrine schema introspection queries against information_schema: 3 different queries, each run 4 times:
    • TABLES joined with COLLATION_CHARACTER_SET_APPLICABILITY
    • COLUMNS joined with TABLES, with a CHECK_CONSTRAINTS subquery
    • KEY_COLUMN_USAGE joined with REFERENTIAL_CONSTRAINTS
  • The 4 runs come from the core setup checks DatabaseHasMissingColumns, DatabaseHasMissingIndices, DatabaseHasMissingPrimaryKeys and DatabasePendingBigIntConversions. Each one creates new SchemaWrapper($connection), which introspects the whole schema.

information_schema tables have no indexes, so these joins are always counted as full joins. The query plans:

PRIMARY  t     ALL  NULL  TABLE_SCHEMA  ...  138   Using where; Open_frm_only; Scanned 1 database; Using temporary; Using filesort
PRIMARY  c     ALL  NULL  TABLE_SCHEMA  ...  946   Using where; Open_frm_only; Scanned 1 database; Using join buffer (flat, BNL join)
SIMPLE   ccsa  ALL  NULL  NULL          ...  1288  Using where; Using join buffer (flat, BNL join)
SIMPLE   k     ALL  NULL  TABLE_SCHEMA  ...  315   Using where; Open_full_table; Scanned 1 database; Using temporary; Using filesort
SIMPLE   c     ALL  NULL  CONSTRAINT_SCHEMA ... 11 Using where; Open_full_table; Scanned 1 database; Using join buffer (flat, BNL join)

So every Overview visit adds 12 to Select_full_join. The 96 since the last MariaDB restart is a multiple of 12 (consistent with 8 visits). On a small instance with little query volume, a handful of visits is enough to cross 0.05%. The check fails because the admin looked at it.

Following the recommendation (find the queries, add indexes) leads nowhere: the "missing indexes" are on MariaDB's catalogue tables. Also, on MariaDB log_queries_not_using_indexes (from the recommendation) logs far more than full joins. log_slow_filter = 'full_join' with long_query_time = 0 is what isolates them.

Expected behavior

Viewing the Overview page shouldn't make a database check fail.

Suggestions
  • Downgrade Select_full_join to a notice, as #1167 did for Join_without_index_rate. It's the same kind of counter, and an admin can't act on it.
  • Or take a baseline of Select_full_join before the schema checks run and leave their contribution out.

The same introspection also adds 12 on-disk temp tables per Overview visit, which feeds Created_tmp_disk_tables; that's covered in #1224.

Related: #1155, #1166, #1167, #1224, #1231

Lingua principale
JavaScript
Stelle
133
Fork
67
Merge medio
22h 55m
PR unite (30g)
53

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di nextcloud/serverinfo

Tutte le issue di nextcloud/serverinfo

Issue simili

Altre issue su JavaScript

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.