Update tl_search Fehler innoDB?

Druckbare Version

11.08.2022, 09:35
tblumrich

Update tl_search Fehler innoDB?

Hallo liebe Community,

ich hab jetzt viel gelesen zu ähnlichen Problemen, leider hat mir nichts so richtig weiter geholfen:
Ich habe eine Installation von 3.5 auf 4.9 gebracht (natürlich auf einer Paralelinstallation). Das hat soweit auch gut gelappt bis zur letzten Datenbank Aktualsierung, ein SQL Befehl (CREATE irgendwas mit unique tl_search.url) ging nicht.
Offensichtlich hab ich das innodb_large_prefix = off Problem. Da das ein Kundenhosting ist, komme ich da selbst nur beschränkt ran.
Ich hab dann eine config.yml angelegt mit dem empfohlenen:

Code:

doctrine: dbal: connections: default: default_table_options: charset: utf8 collate: utf8_unicode_ci

Natürlich Caches danach erneuert. Das Problem blieb.

Contao lief dabei allerdings. Dann wollte ich einfach mal auf 4.13 gehen - da kamen dann weitere Fehler hinzu beim Aktualisieren und ich bin lieber wieder zur 4.9 zurück. Der Stand derzeit ist das folgende Einträge im Installtool gemacht werden müssen, aber Fehler liefern, auch wenn ich sie versuche einzeln anzuhaken.

Code:

Tabellen anlegen Alle auswählen CREATE TABLE tl_search ( id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid INT UNSIGNED DEFAULT 0 NOT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, title TEXT DEFAULT NULL, url VARCHAR(2048) CHARACTER SET ascii DEFAULT '' NOT NULL COLLATE `ascii_bin`, text MEDIUMTEXT DEFAULT NULL, filesize DOUBLE PRECISION DEFAULT '0' NOT NULL, checksum VARCHAR(32) DEFAULT '' NOT NULL, protected CHAR(1) DEFAULT '' NOT NULL, groups BLOB DEFAULT NULL, language VARCHAR(5) DEFAULT '' NOT NULL, UNIQUE INDEX url (url), UNIQUE INDEX checksum_pid (checksum, pid), PRIMARY KEY(id) ) DEFAULT CHARACTER SET utf8 COLLATE `utf8_unicode_ci` ENGINE = InnoDB ROW_FORMAT = DYNAMIC Spalten anlegen Alle auswählen ALTER TABLE tl_search_index ADD PRIMARY KEY (id) ALTER TABLE tl_search_index ADD language VARCHAR(5) DEFAULT '' NOT NULL ALTER TABLE tl_search_index ADD word VARCHAR(64) DEFAULT '' NOT NULL COLLATE `utf8_bin` ALTER TABLE tl_search_index ADD id INT UNSIGNED AUTO_INCREMENT NOT NULL CREATE INDEX word ON tl_search_index (word)

Beim Updateversuch entsteht folgender Fehler in der Logdatei:

Code:

[2022-08-11 08:29:15] request.INFO: Matched route "contao_install". {"route":"contao_install","route_parameters":{"_route":"contao_install","_scope":"backend","_token_check":true,"_controller":"Contao\\InstallationBundle\\Controller\\InstallationController::installAction"},"request_uri":"http://c4.graphischewerkstatt.de/contao/install","method":"POST"} [] [2022-08-11 08:29:15] request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: "An exception occurred while executing 'CREATE TABLE tl_search (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid INT UNSIGNED DEFAULT 0 NOT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, title TEXT DEFAULT NULL, url VARCHAR(2048) CHARACTER SET ascii DEFAULT '' NOT NULL COLLATE `ascii_bin`, text MEDIUMTEXT DEFAULT NULL, filesize DOUBLE PRECISION DEFAULT '0' NOT NULL, checksum VARCHAR(32) DEFAULT '' NOT NULL, protected CHAR(1) DEFAULT '' NOT NULL, groups BLOB DEFAULT NULL, language VARCHAR(5) DEFAULT '' NOT NULL, UNIQUE INDEX url (url), UNIQUE INDEX checksum_pid (checksum, pid), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE `utf8_unicode_ci` ENGINE = InnoDB ROW_FORMAT = DYNAMIC': SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes" at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 128 {"exception":"[object] (Doctrine\\DBAL\\Exception\\DriverException(code: 0): An exception occurred while executing 'CREATE TABLE tl_search (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid INT UNSIGNED DEFAULT 0 NOT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, title TEXT DEFAULT NULL, url VARCHAR(2048) CHARACTER SET ascii DEFAULT '' NOT NULL COLLATE `ascii_bin`, text MEDIUMTEXT DEFAULT NULL, filesize DOUBLE PRECISION DEFAULT '0' NOT NULL, checksum VARCHAR(32) DEFAULT '' NOT NULL, protected CHAR(1) DEFAULT '' NOT NULL, groups BLOB DEFAULT NULL, language VARCHAR(5) DEFAULT '' NOT NULL, UNIQUE INDEX url (url), UNIQUE INDEX checksum_pid (checksum, pid), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE `utf8_unicode_ci` ENGINE = InnoDB ROW_FORMAT = DYNAMIC':\n\nSQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:128, Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:141)"} [] [2022-08-11 08:29:15] request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\TableNotFoundException: "An exception occurred while executing 'SELECT id FROM tl_search WHERE url='http://c4.graphischewerkstatt.de/contao/install'': SQLSTATE[42S02]: Base table or view not found: 1146 Table 'gw_c4.tl_search' doesn't exist" at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 61 {"exception":"[object] (Doctrine\\DBAL\\Exception\\TableNotFoundException(code: 0): An exception occurred while executing 'SELECT id FROM tl_search WHERE url='http://c4.graphischewerkstatt.de/contao/install'':\n\nSQLSTATE[42S02]: Base table or view not found: 1146 Table 'gw_c4.tl_search' doesn't exist at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:61, Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 42S02): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'gw_c4.tl_search' doesn't exist at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: 42S02): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'gw_c4.tl_search' doesn't exist at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:141)"} []

Contao 4.9.31 läuft ansich trotzdem ohne Fehler. PHP ist auf 8.0.22 und wenn ichs in Plesk richtig sehe ist es eine MariaDB - (die besagte tl_search fehlt) PHPmyAdmin Zugang hab ich.

Die config.yml enthält weiterhin den o.g. Eintrag. Ist die Lösung am Ende doch den Hoster zu fragen ob er am MYSQL folgendes einstellt?

Code:

innodb_file_format = Barracuda innodb_file_per_table = 1 innodb_large_prefix = 1

Danke für jede Hilfe!
11.08.2022, 09:43
iainmacnab

Seit 3 Tage kampf ich durch hunderte von Beiträge und Installationsanleitungen. 4.13 auf PHP 8.1 ist mehrmals kläglich gescheitert - sogar unsere Hostring-Techniker kommen nicht weiter. Nun versuchen wir 4.9 auf 7.4.30 zu installieren - Fehler wie oben:
[2022-08-11 08:20:08] request.INFO: Matched route "contao_install". {"route":"contao_install","route_parameters":{"_ro ute":"contao_install","_scope":"backend","_token_c heck":true,"_controller":"Contao\\InstallationBund le\\Controller\\InstallationController::installAct ion"},"request_uri":"https://hhv.eu-mart.de/contao/install","method":"POST"} []
[2022-08-11 08:20:08] request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: "An exception occurred while executing 'CREATE TABLE tl_search (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid INT UNSIGNED DEFAULT 0 NOT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, title TEXT DEFAULT NULL, url VARCHAR(2048) CHARACTER SET ascii DEFAULT '' NOT NULL COLLATE `ascii_bin`, text MEDIUMTEXT DEFAULT NULL, filesize DOUBLE PRECISION DEFAULT '0' NOT NULL, checksum VARCHAR(32) DEFAULT '' NOT NULL, protected CHAR(1) DEFAULT '' NOT NULL, groups BLOB DEFAULT NULL, language VARCHAR(5) DEFAULT '' NOT NULL, UNIQUE INDEX url (url), UNIQUE INDEX checksum_pid (checksum, pid), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE `utf8_unicode_ci` ENGINE = InnoDB ROW_FORMAT = DYNAMIC': SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes" at /var/www/vhosts/hhv.eu-mart.de/httpdocs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 128 {"exception":"[object] (Doctrine\\DBAL\\Exception\\DriverException(code: 0): An exception occurred while executing 'CREATE TABLE tl_search (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid INT UNSIGNED DEFAULT 0 NOT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, title TEXT DEFAULT NULL, url VARCHAR(2048) CHARACTER SET ascii DEFAULT '' NOT NULL COLLATE `ascii_bin`, text MEDIUMTEXT DEFAULT NULL, filesize DOUBLE PRECISION DEFAULT '0' NOT NULL, checksum VARCHAR(32) DEFAULT '' NOT NULL, protected CHAR(1) DEFAULT '' NOT NULL, groups BLOB DEFAULT NULL, language VARCHAR(5) DEFAULT '' NOT NULL, UNIQUE INDEX url (url), UNIQUE INDEX checksum_pid (checksum, pid), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE `utf8_unicode_ci` ENGINE = InnoDB ROW_FORMAT = DYNAMIC':\n\nSQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes at /var/www/vhosts/hhv.eu-mart.de/httpdocs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:128, Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes at /var/www/vhosts/hhv.eu-mart.de/httpdocs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes at /var/www/vhosts/hhv.eu-mart.de/httpdocs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:141)"} []
[2022-08-11 08:20:09] request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\TableNotFoundException: "An exception occurred while executing 'SELECT id FROM tl_search WHERE url='https://hhv.eu-mart.de/contao/install'': SQLSTATE[42S02]: Base table or view not found: 1146 Table 'hhv_db1.tl_search' doesn't exist" at /var/www/vhosts/hhv.eu-mart.de/httpdocs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 61 {"exception":"[object] (Doctrine\\DBAL\\Exception\\TableNotFoundException (code: 0): An exception occurred while executing 'SELECT id FROM tl_search WHERE url='https://hhv.eu-mart.de/contao/install'':\n\nSQLSTATE[42S02]: Base table or view not found: 1146 Table 'hhv_db1.tl_search' doesn't exist at /var/www/vhosts/hhv.eu-mart.de/httpdocs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:61, Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 42S02): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'hhv_db1.tl_search' doesn't exist at /var/www/vhosts/hhv.eu-mart.de/httpdocs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: 42S02): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'hhv_db1.tl_search' doesn't exist at /var/www/vhosts/hhv.eu-mart.de/httpdocs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:141)"} []

Danke für jede Hilfe!
11.08.2022, 09:45
Spooky

Das wird in zukünftigen Contao Versionen behoben sein, siehe https://github.com/contao/contao/pull/4628

Workaround: https://github.com/contao/contao/pul...ent-1122098735
11.08.2022, 09:48
iainmacnab

Der Pfad contao/dca/ existiert nicht!
11.08.2022, 09:52
Spooky

Zitat:

Zitat von iainmacnab

Der Pfad contao/dca/ existiert nicht!

Alles was nicht existiert musst du immer selbst anlegen.
11.08.2022, 10:05
tblumrich

Das heißt das hier als Workaround anlegen? Gilt das auch für meine Problematik?

Code:

// contao/dca/tl_search.php $GLOBALS['TL_DCA']['tl_search']['fields']['url']['sql'] = "varchar(767) COLLATE ascii_bin NOT NULL default ''";

Hab ich gemacht und Prod-Cache gelöscht. Leider weiterhin mit Fehler.
11.08.2022, 10:09
iainmacnab

Hier auch (trotz danke für deine ewige Muhe Spooky!)

DriverException

Doctrine\DBAL\Exception\DriverException:
An exception occurred while executing 'CREATE TABLE tl_search (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid INT UNSIGNED DEFAULT 0 NOT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, title TEXT DEFAULT NULL, url VARCHAR(2048) CHARACTER SET ascii DEFAULT '' NOT NULL COLLATE `ascii_bin`, text MEDIUMTEXT DEFAULT NULL, filesize DOUBLE PRECISION DEFAULT '0' NOT NULL, checksum VARCHAR(32) DEFAULT '' NOT NULL, protected CHAR(1) DEFAULT '' NOT NULL, groups BLOB DEFAULT NULL, language VARCHAR(5) DEFAULT '' NOT NULL, UNIQUE INDEX url (url), UNIQUE INDEX checksum_pid (checksum, pid), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE `utf8_unicode_ci` ENGINE = InnoDB ROW_FORMAT = DYNAMIC':

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

at vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:128
at Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException()
(vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:182)
at Doctrine\DBAL\DBALException::wrapException()
(vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:159)
at Doctrine\DBAL\DBALException::driverExceptionDuring Query()
(vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:2226)
at Doctrine\DBAL\Connection->handleExceptionDuringQuery()
(vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1451)
at Doctrine\DBAL\Connection->query()
(vendor/contao/installation-bundle/src/Database/Installer.php:99)
at Contao\InstallationBundle\Database\Installer->execCommand()
(vendor/contao/installation-bundle/src/Controller/InstallationController.php:399)
at Contao\InstallationBundle\Controller\InstallationC ontroller->adjustDatabaseTables()
(vendor/contao/installation-bundle/src/Controller/InstallationController.php:104)
at Contao\InstallationBundle\Controller\InstallationC ontroller->installAction()
(vendor/symfony/http-kernel/HttpKernel.php:158)
at Symfony\Component\HttpKernel\HttpKernel->handleRaw()
(vendor/symfony/http-kernel/HttpKernel.php:80)
at Symfony\Component\HttpKernel\HttpKernel->handle()
(vendor/symfony/http-kernel/Kernel.php:201)
at Symfony\Component\HttpKernel\Kernel->handle()
(web/index.php:31)
11.08.2022, 10:13
Spooky
Zitat:

Zitat von tblumrich

Das heißt das hier als Workaround anlegen? Gilt das auch für meine Problematik?

Code:

// contao/dca/tl_search.php $GLOBALS['TL_DCA']['tl_search']['fields']['url']['sql'] = "varchar(767) COLLATE ascii_bin NOT NULL default ''";

Hab ich gemacht und Prod-Cache gelöscht. Leider weiterhin mit Fehler.

Du musst:
1. Die Datei anlegen, inkl. Inhalt (<?php nicht vergessen).
2. Danach den Symfony Application Cache neu aufbauen.
3. Danach die Datenbank aktualisieren (bspw. über contao:migrate oder das Install Tool).
11.08.2022, 10:13
tblumrich

Ich meine mich dunkel zu erinnern... Müssen die DCA Dateien mit "<?php " beginnen?
11.08.2022, 10:14
Spooky

Zitat:

Zitat von tblumrich

Ich meine mich dunkel zu erinnern... Müssen die DCA Dateien mit "<?php " beginnen?

PHP Dateien beginnen immer mit <?php - sonst wird der Inhalt von PHP nicht verarbeitet.
11.08.2022, 10:23
tblumrich

Okay.
Bei mir bleiben nach der Aktion 2 Sachen stehen:

Code:

ALTER TABLE tl_search_index ADD PRIMARY KEY (id) ALTER TABLE tl_search_index ADD id INT UNSIGNED AUTO_INCREMENT NOT NULL

Fehler:

Code:

[2022-08-11 09:20:55] request.INFO: Matched route "contao_install". {"route":"contao_install","route_parameters":{"_route":"contao_install","_scope":"backend","_token_check":true,"_controller":"Contao\\InstallationBundle\\Controller\\InstallationController::installAction"},"request_uri":"http://c4.graphischewerkstatt.de/contao/install","method":"POST"} [] [2022-08-11 09:20:55] request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: "An exception occurred while executing 'ALTER TABLE tl_search_index ADD PRIMARY KEY (id)': SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'id' doesn't exist in table" at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 128 {"exception":"[object] (Doctrine\\DBAL\\Exception\\DriverException(code: 0): An exception occurred while executing 'ALTER TABLE tl_search_index ADD PRIMARY KEY (id)':\n\nSQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'id' doesn't exist in table at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:128, Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'id' doesn't exist in table at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'id' doesn't exist in table at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:141)"} []
11.08.2022, 10:32
Spooky

Die Tabelle scheint bei dir kaputt zu sein. Du kannst sie einfach manuell löschen und dann wieder vom Install Tool bzw. contao:migrate neu anlegen lassen. Führe danach auch ein TRUNCATE tl_search; aus. Danach musst du den Suchindex neu aufbauen.
11.08.2022, 10:41
tblumrich

Okay, für mich heißt das:

1. Tabelle tl_search in PHPmyAdmin löschen
2. Install-Tool aufrufen z.B. über den CM
3. TRUNCATE tl_search; In PHPmyAdmin als SQL Befehl?
4. Suchindex im Contao Backend erneuern?

//edit
tl_search_index löschen wahrscheinlich?
11.08.2022, 10:49
Spooky

tl_search_index löschen, ja.
11.08.2022, 11:04
iainmacnab

Irgendwie hat es geklappt - danke sehr!
11.08.2022, 11:23
tblumrich

Ja, wunderbar, bei mir jetzt auch. Dickes Danke spooky!

Dann versuche ich direkt nochmal auf 4.13 zu gehen. :)
11.08.2022, 11:59
tblumrich

Zitat:

Zitat von tblumrich

Dann versuche ich direkt nochmal auf 4.13 zu gehen. :)

Leider crasht hier auch das DB Update mit sehr vielen Einträgen. Fehler im Log:

Code:

[2022-08-11T10:53:30.756541+00:00] request.INFO: Matched route "contao_install". {"route":"contao_install","route_parameters":{"_route":"contao_install","_scope":"backend","_token_check":true,"_controller":"Contao\\InstallationBundle\\Controller\\InstallationController::installAction"},"request_uri":"http://c4.graphischewerkstatt.de/contao/install","method":"POST"} [] [2022-08-11T10:53:31.195468+00:00] request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: "An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'utf8'" at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php line 119 {"exception":"[object] (Doctrine\\DBAL\\Exception\\DriverException(code: 1253): An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'utf8' at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php:119)\n[previous exception] [object] (Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 1253): SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'utf8' at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/src/Driver/PDO/Exception.php:28)\n[previous exception] [object] (PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'utf8' at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/src/Driver/PDO/Connection.php:33)"} [] [2022-08-11T10:53:44.417530+00:00] request.INFO: Matched route "contao_install". {"route":"contao_install","route_parameters":{"_route":"contao_install","_scope":"backend","_token_check":true,"_controller":"Contao\\InstallationBundle\\Controller\\InstallationController::installAction"},"request_uri":"http://c4.graphischewerkstatt.de/contao/install","method":"POST"} [] [2022-08-11T10:53:44.894066+00:00] request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: "An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'utf8'" at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php line 119 {"exception":"[object] (Doctrine\\DBAL\\Exception\\DriverException(code: 1253): An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'utf8' at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php:119)\n[previous exception] [object] (Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 1253): SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'utf8' at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/src/Driver/PDO/Exception.php:28)\n[previous exception] [object] (PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'utf8' at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/src/Driver/PDO/Connection.php:33)"} []

Hat da noch jemand einen Rat?

/edit

Jetzt (wieder Downgrade nach 4.9) crasht mein Installtool schon beim Aufruf, nicht erst beim DB update ;)

Code:

[2022-08-11 11:15:08] request.INFO: Matched route "contao_install". {"route":"contao_install","route_parameters":{"_route":"contao_install","_scope":"backend","_token_check":true,"_controller":"Contao\\InstallationBundle\\Controller\\InstallationController::installAction"},"request_uri":"http://c4.graphischewerkstatt.de/contao/install","method":"GET"} [] [2022-08-11 11:15:08] request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: "An exception occurred while executing 'CREATE UNIQUE INDEX pid_email ON tl_newsletter_recipients (pid, email)': SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes" at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 128 {"exception":"[object] (Doctrine\\DBAL\\Exception\\DriverException(code: 0): An exception occurred while executing 'CREATE UNIQUE INDEX pid_email ON tl_newsletter_recipients (pid, email)':\n\nSQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:128, Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:141)"} []
11.08.2022, 12:34
Spooky

Verwende

Code:

doctrine: dbal: connections: default: default_table_options: charset: utf8 collate: utf8_unicode_ci collation: utf8_unicode_ci
11.08.2022, 13:15
tblumrich

Hab ich gemacht (und Cache erneuert), leider weiterhin folgender Fehler beim Aufruf des Install-Tools.

Logeintrag:

Code:

[2022-08-11 12:13:39] request.INFO: Matched route "contao_install". {"route":"contao_install","route_parameters":{"_route":"contao_install","_scope":"backend","_token_check":true,"_controller":"Contao\\InstallationBundle\\Controller\\InstallationController::installAction"},"request_uri":"http://c4.graphischewerkstatt.de/contao/install","method":"GET"} [] [2022-08-11 12:13:39] request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: "An exception occurred while executing 'CREATE UNIQUE INDEX pid_email ON tl_newsletter_recipients (pid, email)': SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes" at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 128 {"exception":"[object] (Doctrine\\DBAL\\Exception\\DriverException(code: 0): An exception occurred while executing 'CREATE UNIQUE INDEX pid_email ON tl_newsletter_recipients (pid, email)':\n\nSQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:128, Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes at /var/www/vhosts/graphischewerkstatt.de/graphischewerkstatt2022/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:141)"} []
11.08.2022, 13:31
Spooky

Ich glaube du solltest dich um ein besseres Hosting mit moderner MySQL Umgebung umsehen ;)
11.08.2022, 13:44
tblumrich

Zitat:

Zitat von Spooky

Ich glaube du solltest dich um ein besseres Hosting mit moderner MySQL Umgebung umsehen ;)

Ohje, das wollte ich nicht hören, das ist leider ein Kundenhosting mit beschränktem Zugriff, sagen wir mal es ist kompliziert. Ich könnte ja auch damit leben, wenn ich es wenigstens ohne Fehler auf 4.9 hätte - so wie vorhin, wenn ich nicht das 4.13 versucht hätte...

Gibts keinen Kniff, den ich jetzt noch versuchen könnte? :o
11.08.2022, 13:46
Spooky

Verwendet die betroffene Instanz Contao Newsletter?
11.08.2022, 13:48
tblumrich

Nein, wird nicht benutzt
11.08.2022, 13:49
Spooky

Dann einfach deinstallieren.
11.08.2022, 14:02
tblumrich

Hab ich gemacht... er crasht trotzdem weiter mit selbem Fehler. Wie gesagt, er tut das schon beim Aufruf vom Installtool, das verwirrt mich. Hab ich das Intsalltool kautt gemacht? Zur Ansicht der aktualsierenden Tabellen komme ich garnicht.
11.08.2022, 14:03
Spooky

Nutze den Debug-Modus und poste den Stack Trace.
11.08.2022, 14:18
tblumrich

Sind 3 beim Aufruf

Code:

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'CREATE UNIQUE INDEX pid_email ON tl_newsletter_recipients (pid, email)': SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes at vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:128 at Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException() (vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:182) at Doctrine\DBAL\DBALException::wrapException() (vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:159) at Doctrine\DBAL\DBALException::driverExceptionDuringQuery() (vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:2226) at Doctrine\DBAL\Connection->handleExceptionDuringQuery() (vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1451) at Doctrine\DBAL\Connection->query() (vendor/contao/core-bundle/src/Migration/Version404/Version447Update.php:133) at Contao\CoreBundle\Migration\Version404\Version447Update->run() (vendor/contao/core-bundle/src/Migration/MigrationCollection.php:58) at Contao\CoreBundle\Migration\MigrationCollection->run() (vendor/contao/installation-bundle/src/InstallTool.php:471) at Contao\InstallationBundle\InstallTool->runMigrations() (vendor/contao/installation-bundle/src/Controller/InstallationController.php:368) at Contao\InstallationBundle\Controller\InstallationController->runDatabaseUpdates() (vendor/contao/installation-bundle/src/Controller/InstallationController.php:100) at Contao\InstallationBundle\Controller\InstallationController->installAction() (vendor/symfony/http-kernel/HttpKernel.php:158) at Symfony\Component\HttpKernel\HttpKernel->handleRaw() (vendor/symfony/http-kernel/HttpKernel.php:80) at Symfony\Component\HttpKernel\HttpKernel->handle() (vendor/symfony/http-kernel/Kernel.php:201) at Symfony\Component\HttpKernel\Kernel->handle() (web/index.php:31)

Code:

Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes at vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18 at Doctrine\DBAL\Driver\PDO\Exception::new() (vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:143) at Doctrine\DBAL\Driver\PDOConnection->doQuery() (vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOQueryImplementation.php:24) at Doctrine\DBAL\Driver\PDOConnection->query() (vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1449) at Doctrine\DBAL\Connection->query() (vendor/contao/core-bundle/src/Migration/Version404/Version447Update.php:133) at Contao\CoreBundle\Migration\Version404\Version447Update->run() (vendor/contao/core-bundle/src/Migration/MigrationCollection.php:58) at Contao\CoreBundle\Migration\MigrationCollection->run() (vendor/contao/installation-bundle/src/InstallTool.php:471) at Contao\InstallationBundle\InstallTool->runMigrations() (vendor/contao/installation-bundle/src/Controller/InstallationController.php:368) at Contao\InstallationBundle\Controller\InstallationController->runDatabaseUpdates() (vendor/contao/installation-bundle/src/Controller/InstallationController.php:100) at Contao\InstallationBundle\Controller\InstallationController->installAction() (vendor/symfony/http-kernel/HttpKernel.php:158) at Symfony\Component\HttpKernel\HttpKernel->handleRaw() (vendor/symfony/http-kernel/HttpKernel.php:80) at Symfony\Component\HttpKernel\HttpKernel->handle() (vendor/symfony/http-kernel/Kernel.php:201) at Symfony\Component\HttpKernel\Kernel->handle() (web/index.php:31)

Code:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes at vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:141 at PDO->query() (vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:141) at Doctrine\DBAL\Driver\PDOConnection->doQuery() (vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOQueryImplementation.php:24) at Doctrine\DBAL\Driver\PDOConnection->query() (vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1449) at Doctrine\DBAL\Connection->query() (vendor/contao/core-bundle/src/Migration/Version404/Version447Update.php:133) at Contao\CoreBundle\Migration\Version404\Version447Update->run() (vendor/contao/core-bundle/src/Migration/MigrationCollection.php:58) at Contao\CoreBundle\Migration\MigrationCollection->run() (vendor/contao/installation-bundle/src/InstallTool.php:471) at Contao\InstallationBundle\InstallTool->runMigrations() (vendor/contao/installation-bundle/src/Controller/InstallationController.php:368) at Contao\InstallationBundle\Controller\InstallationController->runDatabaseUpdates() (vendor/contao/installation-bundle/src/Controller/InstallationController.php:100) at Contao\InstallationBundle\Controller\InstallationController->installAction() (vendor/symfony/http-kernel/HttpKernel.php:158) at Symfony\Component\HttpKernel\HttpKernel->handleRaw() (vendor/symfony/http-kernel/HttpKernel.php:80) at Symfony\Component\HttpKernel\HttpKernel->handle() (vendor/symfony/http-kernel/Kernel.php:201) at Symfony\Component\HttpKernel\Kernel->handle() (web/index.php:31)
11.08.2022, 14:19
Spooky

Lösche die tl_newsletter_recipients Tabelle manuell.
11.08.2022, 14:25
tblumrich

Jetzt kam ich wieder rein, leider beim Update der DB Tabellen:

Code:

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'ALTER TABLE tl_search_index ADD PRIMARY KEY (id)': SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'id' doesn't exist in table at vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:128 at Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException() (vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:182) at Doctrine\DBAL\DBALException::wrapException() (vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:159) at Doctrine\DBAL\DBALException::driverExceptionDuringQuery() (vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:2226) at Doctrine\DBAL\Connection->handleExceptionDuringQuery() (vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1451) at Doctrine\DBAL\Connection->query() (vendor/contao/installation-bundle/src/Database/Installer.php:99) at Contao\InstallationBundle\Database\Installer->execCommand() (vendor/contao/installation-bundle/src/Controller/InstallationController.php:399) at Contao\InstallationBundle\Controller\InstallationController->adjustDatabaseTables() (vendor/contao/installation-bundle/src/Controller/InstallationController.php:104) at Contao\InstallationBundle\Controller\InstallationController->installAction() (vendor/symfony/http-kernel/HttpKernel.php:158) at Symfony\Component\HttpKernel\HttpKernel->handleRaw() (vendor/symfony/http-kernel/HttpKernel.php:80) at Symfony\Component\HttpKernel\HttpKernel->handle() (vendor/symfony/http-kernel/Kernel.php:201) at Symfony\Component\HttpKernel\Kernel->handle() (web/index.php:31)

Code:

Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'id' doesn't exist in table at vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18 at Doctrine\DBAL\Driver\PDO\Exception::new() (vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:143) at Doctrine\DBAL\Driver\PDOConnection->doQuery() (vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOQueryImplementation.php:24) at Doctrine\DBAL\Driver\PDOConnection->query() (vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1449) at Doctrine\DBAL\Connection->query() (vendor/contao/installation-bundle/src/Database/Installer.php:99) at Contao\InstallationBundle\Database\Installer->execCommand() (vendor/contao/installation-bundle/src/Controller/InstallationController.php:399) at Contao\InstallationBundle\Controller\InstallationController->adjustDatabaseTables() (vendor/contao/installation-bundle/src/Controller/InstallationController.php:104) at Contao\InstallationBundle\Controller\InstallationController->installAction() (vendor/symfony/http-kernel/HttpKernel.php:158) at Symfony\Component\HttpKernel\HttpKernel->handleRaw() (vendor/symfony/http-kernel/HttpKernel.php:80) at Symfony\Component\HttpKernel\HttpKernel->handle() (vendor/symfony/http-kernel/Kernel.php:201) at Symfony\Component\HttpKernel\Kernel->handle() (web/index.php:31)

Code:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'id' doesn't exist in table at vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:141 at PDO->query() (vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:141) at Doctrine\DBAL\Driver\PDOConnection->doQuery() (vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOQueryImplementation.php:24) at Doctrine\DBAL\Driver\PDOConnection->query() (vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1449) at Doctrine\DBAL\Connection->query() (vendor/contao/installation-bundle/src/Database/Installer.php:99) at Contao\InstallationBundle\Database\Installer->execCommand() (vendor/contao/installation-bundle/src/Controller/InstallationController.php:399) at Contao\InstallationBundle\Controller\InstallationController->adjustDatabaseTables() (vendor/contao/installation-bundle/src/Controller/InstallationController.php:104) at Contao\InstallationBundle\Controller\InstallationController->installAction() (vendor/symfony/http-kernel/HttpKernel.php:158) at Symfony\Component\HttpKernel\HttpKernel->handleRaw() (vendor/symfony/http-kernel/HttpKernel.php:80) at Symfony\Component\HttpKernel\HttpKernel->handle() (vendor/symfony/http-kernel/Kernel.php:201) at Symfony\Component\HttpKernel\Kernel->handle() (web/index.php:31)
11.08.2022, 14:34
Spooky

Lösche tl_search_index manuell.
11.08.2022, 14:43
tblumrich

Yeah, spooky, wie immer DANKE! Bin wieder "sauber" auf der 4.9.13

2 Fragen noch

1. Update auf 4.13 lass ich in dieser Server/DB-umgebung dann lieber?
2. Das Installtool möchte das ich den strict Modus über die config.yml aktiviere. Ich hab da ja schon was drin stehen wegen o.g. Problem. Schreib ich das dann einfach dazu, das es so ist?

Code:

doctrine: dbal: connections: default: default_table_options: charset: utf8 collate: utf8_unicode_ci collation: utf8_unicode_ci doctrine: dbal: connections: default: options: 1002: "SET SESSION sql_mode=(SELECT CONCAT(@@sql_mode, ',TRADITIONAL'))"
11.08.2022, 14:53
Spooky

Zitat:

Zitat von tblumrich

Yeah, spooky, wie immer DANKE! Bin wieder "sauber" auf der 4.9.13

Ich dachte du wolltest auf 4.13? Meine Hinweise waren eigentlich dafür gedacht ;)
11.08.2022, 15:05
tblumrich

Im Prinzip ja, aber bei so vielen Fehlern hab ichs mit der Angst zu tun bekommen und wollte schnell downgraden (hatte ich oben in irgendeinem Zwischensatz geschrieben).

Vielleicht mach ich mir noch eine Kopie der jetzigen Instanz und probiers nochmal?

Bei der Frage zum config.yml Eintrag?
11.08.2022, 15:06
Spooky

Das mit dem Strict Mode kannst du erst mal ignorieren. Das Contao 4.13 Update kannst du nochmal versuchen, ja.

Btw. in Zukunft solltest du einfach dein Datenbank Backup einspielen, wenn du downgradest. Datenbank Downgrades werden nämlich ansonsten nicht unterstützt.
12.08.2022, 11:38
tblumrich

Zitat:

Zitat von Spooky

Das mit dem Strict Mode kannst du erst mal ignorieren. Das Contao 4.13 Update kannst du nochmal versuchen, ja.

Jetzt hat das Upgrade auf die 4.13 doch tatsächlich auf Anhieb geklappt... :D Danke nochmal für die Geduld!
(und für den Hinweis mit dem Downgrade...)