MigrationFromOTRS: explain why some columns are shortened to 190 chars

[bschmalhofer]

In Kernel/System/MigrateFromOTRS/CloneDB/Driver/Base.pm there is the following dubious code:

            COLUMNVALUES:
            for my $ColumnCounter ( 1 .. $#OTRSColumns ) {
                my $Column = $OTRSColumns[$ColumnCounter];

                # Check if we need to cut the string, cause utf8mb4 only needs 191 chars.
                if ( $ShortenColumn{$Column} ) {
                    if ( $Row[$ColumnCounter] && length( $Row[$ColumnCounter] ) > 190 ) {
                        $Row[$ColumnCounter] = substr( $Row[$ColumnCounter], 0, 190 );
                    }
                }
            }

It is not obvious where the constant value of 190 characters is coming from. The suspicion is that either this value should be depend on the actual database table. At least it should be properly explained.

[svenoe]

The explanation is this, if I understand correctly: "# Check if we need to cut the string, cause utf8mb4 only needs 191 chars." (Though "needs" is not the right word.)

I forgot for which column types this exactly applies and when, but as we switched from utf8 to utf8mb4, the maximum size in some places has shrunken down from 255 to 191 (is it only CHAR?).

[bschmalhofer]

@StefanRother-OTOBO explained where the 190 chars come from:

Bei utf8mb4 belegt jedes Zeichen eines Varchar Feldes 4 bytes. Innodb hat ein Limit von 767 bytes für Keys, welches nicht überschritten werden darf.
Wir müssen also die Länge der Felder username und email auf 767 / 4 Zeichen verringern, was nach Rundung 191 Zeichen entspricht.

This explanation should be added as code comment as well. See also https://dev.mysql.com/doc/refman/5.6/en/innodb-limits.html and https://dev.mysql.com/doc/refman/5.7/en/innodb-limits.html

[bschmalhofer]

The code comments have been enhanced. Closing this issue.