MySQL-Fehler 1054, (angeblich!) fehlende Spalte

[Auge]

Hallo

Ich habe ein Problem mit einem Query, bei dem moniert wird, dass ich eine angeblich nicht existierende Spalte benutze. Ich habe eine MySQL-Datebank auf meinem Webspace mit der Serverversion 8.0.41 und darin folgende Tabelle.

CREATE TABLE `tbl_uploads` (
	`id` int UNSIGNED NOT NULL AUTO_INCREMENT,
	`uploader` int UNSIGNED DEFAULT NULL,
	`filename` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
	`tstamp` datetime DEFAULT NULL,
	ADD PRIMARY KEY (`id`),
	ADD KEY `fname` (`filename`) USING BTREE;
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Ich würde gerne die Definition der Spalte filename mit einem PHP-Skript mit einem per mysqli_query ausgeführten Query ändern. Zuvor möchte ich mit einer Löschabfrage sicherstellen, dass es keine zwei (oder mehr) Einträge mit identischen Werten in dieser Spalte gibt. Dazu habe ich mich belesen und habe einen Query mit Subquery für die Angabe der zu löschenden Werte gefunden und angepasst.

DELETE FROM `tbl_uploads`
WHERE `id` IN (
	SELECT `temp_id` FROM (
		SELECT `t1`.`id` AS `temp_id` FROM `tbl_uploads` as `t1`
			INNER JOIN `tbl_uploads` as `t2`
			ON  `t1`.`id` > `t2`.`id` AND `t1`.`filename` = `t2`.`filename`
	) AS c
)

Der Part SELECT temp_id FROM (…) AS c um den eigentlichen Subquery ist nötig, da sonst der Optimiser von MySQL querschlägt. Dieser Query funktioniert auf der Zieldatenbank auf meinem Webspace, wenn ich ihn per phpMyAdmin ausführe. Wenn ich ihn jedoch über das PHP-Skript ausführe, wird mir eine nicht existente Spalte t1.filename gemeldet.

Error in line 3722: 1054, Unknown column 't1.filename' in 'on clause'

Um das Problem einzugrenzen, habe ich den Query Zeichen für Zeichen auf etwaige Schreibfehler geprüft. Ich habe nix gefunden. Danach habe ich mit der Struktur der Abfrage herumexperimentiert. Das DELETE habe ich mit SELECT * ersetzt.

SELECT * FROM `tbl_uploads`
WHERE `id` IN (
	SELECT `temp_id` FROM (
		SELECT `t1`.`id` AS `temp_id` FROM `tbl_uploads` as `t1`
			INNER JOIN `tbl_uploads` as `t2`
			ON  `t1`.`id` > `t2`.`id` AND `t1`.`filename` = `t2`.`filename`
	) AS c
)

Egal, ob in der dazugehörigen Tabelle Doubletten existierten oder nicht, im phpMyAdmin funktioniert der Query und gibt bei Doubletten diese auch aus beziehungsweise löscht sie.

Gliedere ich die Bedingungen aus dem ON des JOINs in eine WHERE-Klausel aus, funktioniert der Query in phpMyAdmin sowohl mit SELECT *als auch mit DELETE immer noch, aber im Skript in beiden Versionen nach wie vor nicht. Die Fehlermeldung bezieht sich in jedem Testszenario auf t1.filename, egal ob in der 'on clause' oder in der 'where clause'.

/* Vergleich von 'filename' in ON, Vergleich der 'id' im WHERE */

SELECT * FROM `tbl_uploads`
WHERE `id` IN (
	SELECT `temp_id` FROM (
		SELECT `t1`.`id` AS `temp_id` FROM `tbl_uploads` as `t1`
			INNER JOIN `tbl_uploads` as `t2`
			ON `t1`.`filename` = `t2`.`filename`
			WHERE `t1`.`id` > `t2`.`id`
	) AS c
)

Error in line 3722: 1054, Unknown column 't1.filename' in 'on clause'

/* Vergleich der 'id' in ON, Vergleich von 'filename' im WHERE */

SELECT * FROM `tbl_uploads`
WHERE `id` IN (
	SELECT `temp_id` FROM (
		SELECT `t1`.`id` AS `temp_id` FROM `tbl_uploads` as `t1`
			INNER JOIN `tbl_uploads` as `t2`
			ON `t1`.`id` > `t2`.`id`
			WHERE `t1`.`filename` = `t2`.`filename`
	) AS c
)

Error in line 3723: 1054, Unknown column 't1.filename' in 'where clause'

Was zum Henker übersehe ich? Ich weiß nicht, ob ich einem generellen Denkfehler unterliege oder ob ich „nur“ betriebsblind eine Kleinigkeit übersehe.

„Witz“ am Rande: auf einem anderen DB-Server mit MySQL 5.7 statt 8.0 beim selben Hoster funktionierte die Abfrage auch mit dem selben PHP-Skript. Eine irgendwie zu neue Syntax dürfte also nicht der Grund für das Scheitern sein. Leider gibt die MySQL-Doku die Version, ab der eine bestimmte Syntax unterstützt wird, nur schwer oder gar nicht her, also kann ich mir dessen auch nur nicht sicher sein.

In dieser Hinsicht ist die PHP-Doku um Längen besser (Funktion bla_blubb_trallala => PHP ab 4.0.17, PHP 5.x, PHP 7.x, PHP 8.x).

Tschö, Auge

[Rolf B]

Hallo Auge,

bist Du sicher, dass Du im PHP auf die richtige DB zugreifst bzw. dass der User, mit dem PHP auf die DB zugreift, genug Rechte für die Aktion hat?

Das sieht nämlich alles problemfrei aus und ist für mich durch einen Blick auf's SQL erstmal auch nicht zu erklären.

Rolf

[Auge]

Hallo

bist Du sicher, dass Du im PHP auf die richtige DB zugreifst

bin ich …

bzw. dass der User, mit dem PHP auf die DB zugreift, genug Rechte für die Aktion hat?

… hatter, aber …

… in dieser Instanz, die ich überarbeiten will, habe ich auch die Entwicklungsarbeit für die Änderungen ausgeführt und habe dort eine Kopie der Originaltabelle tbl_uploads, nennen wir sie tbl_test_uploads, angelegt und darauf alle Schritte getestet, unter anderem die Umbenennung der Spalte filename zu pathname. Und der Name dieser Testtabelle mitsamt der geänderten Tabellenstruktur stand bis eben noch in der Konfigurationsdatei. Die Originaltabelle tbl_uploads existiert in der Datenbank natürlich ebenfalls und in ihr gibt es die Spalte filename. Aber da diese Tabelle in der Konfiguration ausgeschlossen ist, kann ich da natürlich draufschauen, wie ich will.

Wenn ich im Skript nach den Werten der Spalte filename frage, aber stattdessen in der konfigurierten Tabelle eine Spalte namens pathname existiert, ist klar, dass der Server die erfragte Spalte nicht findet.

Also tatsächlich Betriebsblindheit. Danke für deine Fragen, die mich auf den richtigen Weg geführt haben.

Das sieht nämlich alles problemfrei aus und ist für mich durch einen Blick auf's SQL erstmal auch nicht zu erklären.

Danke, dass du auch darauf geschaut hast. Den Query aus deinem anderen Posting schaue ich mir auch noch an.

Tschö, Auge

[Rolf B]

Hallo Auge,

Also tatsächlich Betriebsblindheit. Danke für deine Fragen, die mich auf den richtigen Weg geführt haben.

Jo, dumme Fragen stellen kann ich prima 🤡. Vor allem, wenn ich jeden deiner Fehler selbst schon gemacht habe 🦮😎

Rolf

[Rolf B]

Hallo Auge,

bin gespannt, was deine Prüfung auf "war's die richtige DB" ergibt…

Unabhängig davon: ich selbst hätte die Query wohl anders formuliert. Ob das dann besser ist, weiß ich natürlich auch nicht 😉. Aber vielleicht ist es lesbarer und besser verständlich.

Folgende Query liefert mir alle Filenames, die mehr als einmal verwendet werden, sowie die kleinste verwendete ID dazu:

select min(id) as lead_id, filename
from tbl_uploads
group by filename
having count(*) > 1

Wenn's einen Index auf (filename,id) gibt, ist das eine reine Indexoperation und sollte in O(n) laufen.

Das kann ich nun mit den Uploads joinen. Um in einem JOIN zu löschen, hat MySQL eine spezielle Multi-Table DELETE Syntax, bei der man hinter dem DELETE die am Join beteiligten Tabellen auflistet, in denen die gefundenen Sätze zu löschen sind. Da hier mit Aliasnamen gearbeitet wird, ist (sagt ein Stackoverflow-Treffer) hinter DELETE der Aliasname anzugeben, nicht der Tablename.

Innnerhalb der Table-Expression hinter dem Join brauchst Du meines Wissens keine Table-Aliase. Und Backticks brauchst Du eigentlich gar nicht, aber ich hab sie mal mit eingesetzt.

DELETE `t1`
FROM `tbl_uploads` AS `t1`
JOIN (
         SELECT MIN(`id`) AS `lead_id`, `filename`
         FROM `tbl_uploads`
         GROUP BY `filename`
         HAVING count(*) > 1
     ) `t2`
ON `t1`.`filename` = `t2`.`filename` 
   AND `t1`.`id` > `t2`.`lead_id`

Das geht in MySQL 5.7, sollte daher auch in MySQL 8.* oder im Datenmariechen funktionieren. Die Syntax für DELETEs aus Joins kann von SQL-Engine zu SQL-Engine wechseln oder auch gar nicht unterstützt sein.

Ausprobiert habe ich es nicht. Aber es sollte performanter und auch lesbarer sein als deine IN-Abfrage in einen Join hinein; ich würde eine O(n²) Komplexität unterstellen, bei deiner Query hingegen O(n³). Ohne den Index auf filename sind es allerdings viele Tablescans, was die Sache ausbremst. Ob der Index wirklich was bringt, kommt drauf an, wieviele Sätze du in der Table hast.

Den inneren SELECT könnte man auch als CTE (common table expression) vor die Query ziehen, aber CTEs gibt's in MYSQL 5 noch nicht und ich weiß nicht, was Du verwendest.

An Stelle von JOIN gibt es auch Syntaxen (Syntaxis?), die mit SQL Tupeln arbeiten und dann wieder den IN Operator verwenden, aber das ist (a) längst nicht in jedem SQL möglich und (b) muss hier ja der Filename auf Gleichheit und die ID auf größer (oder Ungleich) verglichen werden, weil Du ja genau eine ID beibehalten willst.

Rolf