Review main Merged

67336[BUGFIX] Reduce ses_id field length

Christian Kuhn CR +2 V +2 Easy +2 −2 updated 01/20/21, 11:28

Commit message

The patch for #93131 increased the db size of field ses_id
in tables fe_sessions and be_sessions from 32 to 255 chars.
The field is the tables primary key.
With mysql/mariadb, if the table is created on an older db
engine version that creates innodb tables with COMPACT
row format using utf8_mb4 charset, the key length now
exceeds the COMPACT maximum index length restrition of 767
bytes: 4*255+1 = 1021 bytes
Since we don't strictly need a varchar(255), the patch
reduces the field to 190, which does not collide with
maximum key length restrictions: 4*190+1 = 761 bytes

Review

  • Code review CR +2 Done
    • Benni Mack +2
    • Marc von Schalscha +1
    • Oliver Hader +1
  • Verified V +2 Done
    • Benni Mack +2
    • Marc von Schalscha +1
    • TYPO3com +1 CI

Issues 2

Files 2

Review log 1011

  1. Christian Kuhn uploaded patch set 1 01/04/21, 15:27

  2. TYPO3com voted on patch set 1 V +1 01/04/21, 15:37 Completed build in 09m 51s on Mon, 4 Jan, 03:37 PM Test Summary: 32546 passed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-8602

  3. Benni Mack voted on patch set 1 V +2 CR +2 01/04/21, 15:43

  4. Benni Mack merged patch set 2 01/04/21, 15:44

  5. Ingo Fabbri commented on patch set 2 01/07/21, 11:26

    You need to store a sha256 AFAIK - so please use VARCHAR(64) instead of 190. It matters.
  6. Christian Kuhn commented on patch set 2 01/07/21, 19:09

    Reply

    Thanks for the comment. I disagree, though: varchar(64) and varchar(190) consume the exact same storage amount if only 64 chars are used in practice. This has no storage impact and a performance impact only if the engine is a memory table (which it isn't), or if used as temporary table (it isn't either since ses_id is primary key and there are no relevant queries that create temp tables here). In general, I agree fields shouldn't be bigger than they need to be. However, as outlined with the initial patch https://review.typo3.org/c/Packages/TYPO3.CMS/+/67218, this very field gave us quite a nasty chicken-egg issue when we needed it to be bigger in a security release. To circumvent this possible issue in the future, it's now bigger than it strictly needs to be. It does not harm in practice, anyway.
  7. Ingo Fabbri commented on patch set 2 01/07/21, 19:49

    Reply

    First of all: 191 may be the maximum possible length using utfmb4 (4bytes each char). So kind of gross using 190. But never mind that. It matters because people tend to step into this trap. It happened at sys_redirect, where someone changed the column-size to VARCHAR(2048) - "its so variable". Kachooooom - index too large. The mindset "cpu and memory are cheap in 2020" turn off any conscientiousness and make people deal carelessly with such things. So when everywhere VARCHAR(191) is used, then nobody will question. Copy&paste and done. It will spread and you then get into trouble when you like to add combined index (aka multicolumn index). Then you need to limit the column-index-size "artificially" to avoid another index-too-large issue. So IMHO the KISS rule is: use the proper size when you exactly know what will be stored in there. Of course CHAR has no benefits over VARCHAR in modern MySQL/MariaDB/.. - but still it makes all more "readable" and make people "question things". "255"/"191" or hey - why not just TEXT - in times of SSD it really doesn't matter anymore that TEXT is stored separately (just kidding, but wanted to make a cheap point here). This are just my 2 cents to the topic. I am not an DB-administrator nor do I have any DB-expert-level experience. I just do not like running into these kind of "index size exceeded" issues every few months when upgrading TYPO3 websites. This is my sole motivation to write here and draw attention to this topic.
  8. Christian Kuhn commented on patch set 2 01/07/21, 22:25

    Reply

    Thanks for your addition. I still don't really get your point: There is no storage requirement difference between a varchar(64) and a varchar(190) if row field content is 64 chars (for now) in size. It does not consume different ssd capacity, nor cpu cycles. With ses_id, we went with 190 for a reason: It prevents chicken-egg "all sessions broken" issues in case we need to switch to some hash that requires even more length - the varchar(32) situation gave us a significant issue for the security patch level releases and we had to reduce collision resilience due to this. It's now fixed, we learned from that and gave us some space to not face this issue again. All that without technical drawback. Of course you can agree or disagree on that reasoning, though. In general it's not that we extend every field to whatever max-length is possible and this very case has a solid reason. So core is everything but a bad practice here: We changed the size in a short timeframe twice to accommodate all needs.
  9. Oliver Hader voted on patch set 2 CR +1 01/11/21, 12:53

  10. Oliver Hader commented on patch set 2 01/11/21, 13:21

    Just wanted to drop `VARBINARY` in case indexes really become a problem. The session ID field stores the hexadecimal representation of a (currently) 256 bit word. To really avoid mb4utf8 spanning, `VARBINARY` would the preferred solution.
  11. Marc von Schalscha commented on patch set 2 V +1 CR +1 01/20/21, 11:28

    Just ran into that issue trying to make a new installation of TYPO3 10.4.12 with MySQL 5.6.48. Applying the patch solved the problem as expected.