0

I have a table like this:

CREATE TABLE `sessions` (
  `id` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `access` int unsigned DEFAULT NULL,
  `data` mediumtext,
  PRIMARY KEY (`id`),
  KEY `access_idx` (`access`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

In MySQL 5 that worked perfectly well when I stored a small binary object in the session.

In MySQL 8 the stored session data is cut off right where that binary object starts. So when I load the session after writing the session with the binary data, session_start() fails to deserialize the session data and immediately calls session_destroy()

We changed the data column to mediumblob and that fixes the error.

So two questions: What changed between MySQL 5 and 8, and is there a problem with using a BLOB column for session storage?

Improve this question
9
  • It seems like use of MEDIUMBLOB would work for storing that binary data correctly. But you would need to make sure your character sets are correct. When they upgraded, from 5 to 8 MySQL, seems that they added some stricter requirements for TEXT and thus truncation can happen, which you obviously don't want to happen with a binary stream.
    – easleyfixed
    Commented May 22, 2024 at 18:05
  • Question is: why did you store binary data in a text column in the first place? @easleyfixed I don't think mysql 5 had utf8mb4 character set and its utf8 implementation was only max 3 bytes long. Upgrade that to 4 byte utf8 and you can run out space.
    – Shadow
    Commented May 22, 2024 at 18:12
  • @shadow I never heard of such a thing before either, but if its possible someone will do it. But the BLOB fix is the correct way, and the answer is due to text being more strict in the newer version.
    – easleyfixed
    Commented May 22, 2024 at 18:13
  • @Shadow The binary data was in a cryptographic challenge stored in the session for convenience for comparison in the very next request.
    – tvanc
    Commented May 22, 2024 at 18:26
  • @easleyfixed if you placed that info as an answer I'd accept it
    – tvanc
    Commented May 22, 2024 at 19:47
Related questions
3212
Should I use the datetime or timestamp data type in MySQL?
1354
Retrieving the last record in each group - MySQL
1421
Which MySQL data type to use for storing boolean values
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.