Hacktoberfest 2026: los issues que los mantenedores marcaron para octubre, abiertos y aptos para principiantes. Explorar issues de Hacktoberfest

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

Abierto
  • ¿Algo no está bien?
#1,245 0 comentarios 0 reacciones 0 asignados Ver en GitHub

Los mantenedores suelen responder en 1 día

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
4/5
Tiempo estimado
3-5 días
Aptitud para principiantes
45/100
Tipo de issue
Error
Claridad
Bastante claro
Estado de actividad
Activo
Stack tecnológico
javascript, mariadb, php
Área
backend, databases, observability

Línea de trabajo

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.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

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

Lenguaje dominante
JavaScript
Estrellas
133
Forks
67
Merge medio
22 h 11 min
PR fusionados (30 d)
55

Preparar el entorno

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de nextcloud/serverinfo

Todos los issues de nextcloud/serverinfo

Issues similares

Más issues de JavaScript

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.