Contao 4.13 -> Installtool -> DB aktualisieren -> statement ALTER TABLE tl_member funktioniert nicht

[jochi44]

Nach einem Update von Contao 4.12.7 auf 4.13.2 wird im Contao-Installtool folgendes Statement angezeigt: ALTER TABLE tl_member CHANGE username username VARCHAR(64) DEFAULT NULL COLLATE utf8mb4_unicode_ci

Die Betätigung des Buttons "Datenbank aktualisieren" bewirkt nichts, das ALTER TABLE-statement erscheint immer wieder. Fehlermeldung wird keine ausgegeben. Im Stacktrace des Debug-Modes hab ich nichts gefunden, am Server (/var/logs bzw. error.log) seh ich auch keine Fehlermeldungen.

Ich vermute, dass die Erweiterung terminal42/contao-mailusername damit zusammenhängen könnte.

Im Contao-Community-Forum gibt es 2 Threads, die das Problem beschreiben bzw. weiterführende Informationen liefern: https://community.contao.org/de/showthread.php?82879-Installtool-gt-DB-aktualisieren-gt-statement-ALTER-TABLE-tl_member-funktioniert-nicht https://community.contao.org/de/showthread.php?82535-Fehler-bei-Datenbank-Aktualisierung&highlight=utf8mb4_unicode_ci

[exscorp]

Kann ich bestätigen, heute zwei Installationen auf 4.13 aktualisiert und bekomme diesen "Fehler".

Habe es auch mit einer frischen Contao 4.13.2 probiert und nur "terminal42/contao-mailusername" installiert, dann erscheint dieser "Fehler".

[Anke]

Kann ich ebenfalls bestätigen - bei einem Update von 4.11 auf 4.13.2 (Contao-Neuinstallation mit Import der upzudatenden DB). Ein Blick in die Datenbank zeigt, dass die Änderung bereits durchgeführt wurde bzw. nichts zu ändern ist.

[aschempp]

@ausi any idea why this is happening?

[ausi]

I’d guess that utf8mb4_unicode_ci is already the collation of the table so the COLLATE utf8mb4_unicode_ci in the column definition is redundant and that might confuse the new Doctrine comparator.

What is the reason for the extra COLLATE here? https://github.com/terminal42/contao-mailusername/blob/10e8026e5363cd78c95cee54018fb6bf168d1dad/contao/dca/tl_member.php#L8

[Anke]

I’d guess that utf8mb4_unicode_ci is already the collation of the table

This I can confirm.

[Anke]

I have a second case, an update from 4.11.x to 4.13.2 and the install tool is producing an error again. The old database line in tl_content is:

playerColor varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT

The installtool wants to change it as follows:

ALTER TABLE tl_content CHANGE playerColor playerColor VARCHAR(6) CHARACTER SET ascii DEFAULT '' NOT NULL COLLATEascii_bin``

var/logs says:

[2022-04-25T10:57:17.241409+02: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":"https://....de/contao/install","method":"POST"} []
[2022-04-25T10:57:17.812981+02:00] request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: "An exception occurred while executing a query: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'playerColor' at row 1" at /kunden/.../vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php line 119 {"exception":"[object] (Doctrine\\DBAL\\Exception\\DriverException(code: 1406): An exception occurred while executing a query: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'playerColor' at row 1 at /.../vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php:119)\n[previous exception] [object] (Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 1406): SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'playerColor' at row 1 at /.../vendor/doctrine/dbal/src/Driver/PDO/Exception.php:30)\n[previous exception] [object] (PDOException(code: 22001): SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'playerColor' at row 1 at /.../vendor/doctrine/dbal/src/Driver/PDO/Connection.php:34)"} []

It's a MariaDB and config.yml has the following setting:

doctrine:
  dbal:
    connections:
      default:
        options:
          1002: "SET SESSION sql_mode=(SELECT CONCAT(@@sql_mode, ',TRADITIONAL'))"

[ausi]

The installtool wants to change it as follows:

ALTER TABLE tl_content CHANGE playerColor playerColor VARCHAR(6) CHARACTER SET ascii DEFAULT '' NOT NULL COLLATE `ascii_bin`

This is correct, as the column was changed in version 4.13.

An exception occurred while executing a query: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'playerColor' at row 1

Please check if there is some data in tl_content.playerColor that is longer than 6 characters. If so these values should be fixed (or reported here why they need to be longer than 6 characters).

[Anke]

There is no player on that website and never has been.

[ausi]

What are the contents of the playerColor column in the tl_content table?

[Anke]

Before the update it was a:2:{i:0;s:0:"";i:1;s:0:"";}

now shortened to a:2:{i

[fritzmg]

@Anke that's unrelated to this issue. See https://github.com/contao/contao/issues/4449

[Anke]

Okay, thanks, I posted it there. Just thought it might be the same problem and you might have a clue.

[ameotoko]

I also have this issue on two Contao 4.13.2 installations. These discussions might be relevant:

https://github.com/doctrine/dbal/issues/5265 https://github.com/doctrine/DoctrineBundle/issues/1468

Adding this config solved the issue for me:

# config/config.yml
doctrine:
    dbal:
        connections:
            default:
                default_table_options:
                    collation: utf8mb4_unicode_ci

[fritzmg]

Hm that's weird, as this should be the default anyway. But may be it's not (anymore) and/or Doctrine is unable to detect the current collation and thus thinks it needs to be changed.

[ameotoko]

default is collate, not collation

https://github.com/contao/contao/blob/4fb6ba22b4fc034199009f6c7371c3964df4f4b7/manager-bundle/src/Resources/skeleton/config/config.yml#L56

[fritzmg]

According to the issues that you have linked in https://github.com/contao/contao/issues/4560 this should already be fixed - unless the fixes weren't released yet?

[ameotoko]

To be honest, I was only able to follow some of what is happening in those threads... What I get out of this:

collate was never supposed to be supported and used in the first place, and DBAL 3.3.2 fixed that.

and

if you're configuring dbal's collate option directly, you must change the configuration to use collation instead.

[fritzmg]

Yes, it is pretty confusing. Either should work on its own, but it seems there is still a bug in doctrine where you will have to define both collate and collation, otherwise the schema diff will be wrong.

[ameotoko]

https://github.com/contao/contao/pull/4592 fixes the issue for me

[aschempp]

closing this as a Contao bug then 🙃