Bug #93150

Update / change in Analyze Database Structure

Added by Sandro Barbieri 9 months ago. Updated 7 months ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2020-12-22
Due date:
% Done:

100%

Estimated time:
TYPO3 Version:
10
PHP Version:
7.4
Tags:
database, upgrade,
Complexity:
Is Regression:
Sprint Focus:

Description

Update from 10.4.10 to 10.4.12 in backend (core update)

then ran upgrade wizard.

then apply/do Analyze Database Structure, result:

Change fields
select/deselect all
ALTER TABLE `be_sessions` CHANGE `ses_id` `ses_id` VARCHAR DEFAULT '' NOT NULL
Current value: ses_id VARCHAR CHARACTER SET utf8mb4 DEFAULT '' NOT NULL COLLATE `utf8mb4_unicode_ci`
ALTER TABLE `fe_sessions` CHANGE `ses_id` `ses_id` VARCHAR DEFAULT '' NOT NULL
Current value: ses_id VARCHAR CHARACTER SET utf8mb4 DEFAULT '' NOT NULL COLLATE `utf8mb4_unicode_ci`


Files


Related issues

Related to TYPO3 Core - Task #93131: Increase session id db field sizeClosed2020-12-21

Actions
Related to TYPO3 Core - Bug #93261: Wrong detection of MariaDB/MySQL version on Debian 9New2021-01-11

Actions
Related to TYPO3 Core - Bug #93326: Error while Auto-Update: Error: Index column size too largeNewStefan Froemken2021-01-20

Actions
#1

Updated by Sandro Barbieri 9 months ago

  • Description updated (diff)
#2

Updated by Zenoussi no-lastname-given 9 months ago

Same here.

#3

Updated by Oliver Bartsch 9 months ago

  • Status changed from New to Needs Feedback

Hi, did you encounter any problems with peforming the database updates? This update is intended, see: #93131.

#4

Updated by Sandro Barbieri 9 months ago

This screenshot was missing, sorry for that.

The error (2x) I get is:

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

#6

Updated by Christian Kuhn 9 months ago

  • Related to Task #93131: Increase session id db field size added
#7

Updated by Christian Kuhn 9 months ago

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).

Background:
  • 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.

#8

Updated by Christian Kuhn 9 months ago

  • Status changed from Needs Feedback to New
#9

Updated by Andreas Kiessling 9 months ago

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

#10

Updated by Sandro Barbieri 9 months ago

I just tried a fresh 10.4.12 install and have the same (?) problem with the installer hanging at step 4/5 (80%) with the following error:

Database query failed!
Query: CREATE TABLE `fe_sessions` (`ses_id` VARCHAR DEFAULT '' NOT NULL, `ses_iplock` VARCHAR DEFAULT '' NOT NULL, `ses_userid` INT UNSIGNED DEFAULT 0 NOT NULL, `ses_tstamp` INT UNSIGNED DEFAULT 0 NOT NULL, `ses_data` MEDIUMBLOB DEFAULT NULL, `ses_permanent` SMALLINT UNSIGNED DEFAULT 0 NOT NULL, `ses_anonymous` SMALLINT UNSIGNED DEFAULT 0 NOT NULL, INDEX `ses_tstamp` (ses_tstamp), PRIMARY KEY) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB Error: Specified key was too long; max key length is 767 bytes

This is a bigger issue, on the same server / installation with 10.4.10 runs without problems.

--> see also attachment

#11

Updated by Christian Kuhn 9 months ago

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.

#12

Updated by Gerrit Code Review 9 months ago

  • Status changed from New to Under Review

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

#13

Updated by Gerrit Code Review 9 months ago

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

#14

Updated by Christian Kuhn 9 months ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100
#15

Updated by Andreas Kiessling 9 months ago

@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

What i did:
  • 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

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

Can you spot any error?

#16

Updated by Alexander Opitz 9 months ago

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
#17

Updated by Alexander Opitz 9 months ago

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.

#18

Updated by Christian Kuhn 9 months ago

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?!

#19

Updated by Andreas Kiessling 9 months ago

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

#20

Updated by Ingo Fabbri 9 months ago

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.

#21

Updated by Christian Kuhn 9 months ago

Ingo: I disagree, see gerrit comment.

#22

Updated by Andreas Kiessling 8 months ago

  • Related to Bug #93261: Wrong detection of MariaDB/MySQL version on Debian 9 added
#23

Updated by Gerrit Hübbers 8 months ago

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?

#24

Updated by Katja Lutz 8 months ago

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
#25

Updated by Stefan Froemken 8 months ago

  • Related to Bug #93326: Error while Auto-Update: Error: Index column size too large added
#26

Updated by Josef Glatz 7 months ago

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.

#27

Updated by Benni Mack 7 months ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF