Bug #93150
closedUpdate / change in Analyze Database Structure
Added by Sandro Barbieri over 3 years ago. Updated over 3 years ago.
100%
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
Screenshot 2020-12-22 120354.jpg (30.9 KB) Screenshot 2020-12-22 120354.jpg | Sandro Barbieri, 2020-12-22 11:04 | ||
Bildschirmfoto 2020-12-22 um 11.21.40.png (79.7 KB) Bildschirmfoto 2020-12-22 um 11.21.40.png | Ulrich Koch, 2020-12-22 11:09 | ||
typo3-10-4-12-install-problem-2020-12-29 214002.jpg (132 KB) typo3-10-4-12-install-problem-2020-12-29 214002.jpg | Sandro Barbieri, 2020-12-29 20:47 |
Updated by Oliver Bartsch over 3 years 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.
Updated by Sandro Barbieri over 3 years 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
Updated by Ulrich Koch over 3 years ago
Can't update.
Updated by Christian Kuhn over 3 years ago
- Related to Task #93131: Increase session id db field size added
Updated by Christian Kuhn over 3 years 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).
- 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.
Updated by Christian Kuhn over 3 years ago
- Status changed from Needs Feedback to New
Updated by Andreas Kießling over 3 years 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
Updated by Sandro Barbieri over 3 years ago
- File typo3-10-4-12-install-problem-2020-12-29 214002.jpg typo3-10-4-12-install-problem-2020-12-29 214002.jpg added
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
Updated by Christian Kuhn over 3 years 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.
Updated by Gerrit Code Review over 3 years 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
Updated by Gerrit Code Review over 3 years 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
Updated by Christian Kuhn over 3 years ago
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
Applied in changeset 11e16238259ffe093849c0fd2fc4d1d0b81f50c8.
Updated by Andreas Kießling over 3 years 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
- 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?
Updated by Alexander Opitz over 3 years 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
Updated by Alexander Opitz over 3 years 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.
Updated by Christian Kuhn over 3 years 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?!
Updated by Andreas Kießling over 3 years 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
Updated by Anonymous over 3 years 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.
Updated by Christian Kuhn over 3 years ago
Ingo: I disagree, see gerrit comment.
Updated by Andreas Kießling over 3 years ago
- Related to Bug #93261: Wrong detection of MariaDB/MySQL version on Debian 9 added
Updated by Gerrit Hübbers over 3 years 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?
Updated by Katja Lutz over 3 years 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
Updated by Stefan Froemken over 3 years ago
- Related to Bug #93326: Error while Auto-Update: Error: Index column size too large added
Updated by Josef Glatz over 3 years 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.