TYPO3 Core

Same here.

Dang. This was my patch. I did not foresee the index max length restriction with older innodb row formats :(

The easiest solution for now:
Ignore this proposed field size change. It does not harm in v10 to keep a length of varchar(32).

• With utf8mb4 charset, each char can consume up to 4 bytes. A varchar(255) then ends up with "4*255 + 1" byte -> 1021 bytes
• mysql/mariadb innodb tables with COMPACT row format (exists since mysql 5.0), the maximum index length is 767 bytes, so less than a varchar(255) with utf8mb4 needs. This row format has been default for new tables until mysql 5.7.
• mysql/mariadb innodb tables with DYNAMIC row format (exists since mysql 5.5), the maximium index length is 3072 bytes, a varchar(255) with utf8mb4 thus easily fits. This row format has been default for new tables since mysql 5.7.

TYPO3 v10 has a minimum mysql version requirement of 5.5. So DYNAMIC row format should exist. However, there has never been a core change to actively migrate existing tables from COMPACT to DYNAMIC row format.

So, apart from the 'ignore issue' solution above, another option is to switch the two tables to DYNAMIC row format:

# Find sessions tables and show their row format
SELECT NAME, ROW_FORMAT FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE '%be_sessions' OR NAME LIKE '%fe_sessions';

# Switch a single table to DYNAMIC row format
# ALTER TABLE <database>.<table> ROW_FORMAT=DYNAMIC;
# example:
ALTER TABLE coremaster.be_sessions ROW_FORMAT=DYNAMIC;

# Check again if row format has been switched for this table:
# SELECT NAME, ROW_FORMAT FROM information_schema.INNODB_SYS_TABLES WHERE NAME = '<database>/<table>';
# example:
SELECT NAME, ROW_FORMAT FROM information_schema.INNODB_SYS_TABLES WHERE NAME = 'coremaster/be_sessions';

After that, the proposed database change can be applied.

For core, I'm not yet fully decided on how we'll fix this: For v10, we'll probably decrease the field size specification again, so it fits into 767 bytes with utf8mb4. For master / v11 we may add an upgrade wizard to enforce DYNAMIC storage format once and for all, since this simply leads to less trouble.

Switching to DYNAMIC row format somehow did not work out for me.
My database version is MySQL 5.5.5-10.1.47-MariaDB-0+deb9u1 - i'll skip the database:updateschema for now, but would be great to have this fixed / field size reduced soon

Andreas: Could you go investigate a bit deeper? I tried the above commands locally and it worked - switching back and forth between the row formats. I'd be interested if I missed something in my analysis, since it's the base of a possible v11 patch with an upgrade wizard to simply switch all tables to DYNAMIC. It would be great to know if that will work out on the lower end of database engines, too. Note my statements above are only for table be_sessions, it has to be done for fe_sessions, too.

Sandro: Yeah, I feared the installer could be affected, too. I'll now create a patch for v10 & master to reduce the field length a bit again to get rid of that issue with next patch level release.

Patch set 1 for branch 10.4 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/67300

@Christian: i can try, maybe my notes are of help

Server-Specs:
Debian 9.13
PHP: 7.2.34
MariaDB: 10.1.47-MariaDB-0+deb9u1

• executed the ALTER TABLE statements for the be_sessions table
• checked the SHOW CREATE TABLE results
  

  
  CREATE TABLE `be_sessions` (
    `ses_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
    `ses_iplock` varchar(39) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
    `ses_userid` int(10) unsigned NOT NULL DEFAULT '0',
    `ses_tstamp` int(10) unsigned NOT NULL DEFAULT '0',
    `ses_data` longblob,
    `ses_backuserid` int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`ses_id`),
    KEY `ses_tstamp` (`ses_tstamp`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC
  

• went to the db anaylzer and executed the update for the be_sessions table
  

  ALTER TABLE `be_sessions` CHANGE `ses_id` `ses_id` VARCHAR(255) DEFAULT '' NOT NULL
  Current value: ses_id VARCHAR(32) CHARACTER SET utf8mb4 DEFAULT '' NOT NULL COLLATE `utf8mb4_unicode_ci` 
  

Result from the flash message:

Database update failed
Error: Specified key was too long; max key length is 767 bytes

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Can you spot any error?

Notice to last statement from Andreas:

• Using varchar(190) works
• His notice was a retest of the DYNAMIC vs STATIC ROW_Format
• It seams not working with dynamic

Selftest on my host with 10.3.25-MariaDB-0ubuntu1-log - Ubuntu 20.10, the switch to dynamic works. But the yet slected solution should be fair enough.

Andreas: Thanks for re-testing this!

I guess, your mariadb 10.1.x simple does not execute the switch to DYNAMIC sucessfully. Probably, if you run this check after trying to set it to DYNAMIC, you'll see it's still COMPACT:

# adapt db / table name
SELECT NAME, ROW_FORMAT FROM information_schema.INNODB_SYS_TABLES WHERE NAME = 'coremaster/be_sessions';

The mariadb docs state. https://mariadb.com/kb/en/innodb-dynamic-row-format/

The DYNAMIC row format is only supported by the Barracuda file format. As a side effect, in MariaDB 10.1 and before, the DYNAMIC row format is only supported if the InnoDB file format is Barracuda. Therefore, the innodb_file_format system variable must be set to Barracuda to use these row formats in those versions.

The default file format in mariadb 10.1 is Antelope, so that's probably the reason the switch to DYNAMIC does not work for you. Can you confirm?

get.typo3.org states the minimum mariadb for core v10 is actually 10.2. For v10, I'd keep the solution as it is now (reduced field length to 190), for master we could still think about having an upgrade wizard that sets row format to DYNAMIC once and for an, and actually require on mariadb 10.2 this way?!

Ouch, i totally missed the requirements for MariaDB 10.2 in 10.4

SELECT NAME, ROW_FORMAT FROM information_schema.INNODB_SYS_TABLES WHERE NAME = 'coremaster/be_sessions';

just returns an empty set... i adjusted the db name, no worries ;)
Thanks for spotting the issue! I'll ask my hoster if we can upgrade.

TBH, the MariaDB versions are getting pretty frustrating. Getting multiple PHP versions running on Debian/Ubuntu ist pretty easy with the sury packages, but AFAIK you can't easily run multiple versions of MySQL / MariaDB? That might create issues when having to run different T3 version on the same server since e.g. v8 wants MariaDB 10.0 or 10.1, v10 wants 10.2 or 10.3. MySQL seems compatible with ~5.7

No, please do not make it `VARCHAR (190)`. It is not a good idea to act like this:

Strictly, a field size of 64 characters would be enough
for sha256, we however raise to 255 to never run into
this chicken-egg issue again - just in case.

You better set it to the size you expect - at max. So when sha256 is 64 digits - then use 64 chars.

Ingo: I disagree, see gerrit comment.

Side question: how come a minor release tinkers with database schemas at all? Shouldn't database schema changes be considered breaking changes and therefore should only be done in major releases?

Lil update on this from my side (just for the sake of documentation), for me this doesn't work even with a MariaDB 10.3 o.O

This database migration is failing for me as well with the bottom error:

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

• MariaDB Version: MySQL 5.5.5- 10.3.27 -MariaDB-0+deb10u1-log
• Row Format of be_sessions: dynamic

Katja Lutz wrote in #note-24:

Lil update on this from my side (just for the sake of documentation), for me this doesn't work even with a MariaDB 10.3 o.O

This database migration is failing for me as well with the bottom error:
[...]

• MariaDB Version: MySQL 5.5.5- 10.3.27 -MariaDB-0+deb10u1-log
• Row Format of be_sessions: dynamic

I get also the same problem when using MariaDB 10.3. Can confirm.