Mysql column type breaking change on PdoSessionHandler

[tseho]

Symfony version(s) affected: 4.4

Description

I think there is a BC break in #33169. On mysql, the lifetime column has been for the last few years a MEDIUMINT column.

The new version with time() + $maxlifetime instead of $maxlifetime is higher than the MEDIUMINT mysql limit.

When upgrading from symfony 4.3 to 4.4 with an existing database, we have the error SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'sess_lifetime' at row 1

I saw the creation of the session table has been fixed 5 days ago in #34410 but that does not fix existing databases.

Is it normal we need to create manually a sql migration for a breaking database schema change, which is not really mentioned in UPGRADE-4.4.md ?

[s-duval]

Exactly already mentioned that bug in #34479 and was prematurely closed. That's a pretty major issue as sessions are commonly used in applications and the fact that a DB migration is required in order to work

[tseho]

I did miss your issue when posting mine because it was already closed.

[nicolas-grekas]

Can we issue an ALTER TABLE when an exception with code 1264 is thrown? That'd be the best isn't it?
PR welcome.

[s-duval]

@nicolas-grekas Indeed I think that's the only way to fix this issue to keep backward compatibility between 4.3 and 4.4

[tseho]

@nicolas-grekas I did a quick POC about it last night, it's really straightforward but I'm conflicted about it.
I think it's a bad idea to run ALTER TABLE on any production database when the error occurs. It will mess with existing database migrations.
Meanwhile, I proposed #34582 for at least updating the UPGRADE-4.4.md.

[nicolas-grekas]

I did a quick POC about it last night, it's really straightforward but I'm conflicted about it.

up to submitting what you've done so far? then we can discuss about the impact on the PR?

[tseho]

sure, I will submit it tonight.

[mvanduijker]

Might be good to add to the changelog that this change will invalidate all your current sessions.
Before upgrading and after doing the migration of the sess_lifetime column to unsigned int, you probably have to run a query like (not tested):

UPDATE sessions SET sess_lifetime = sess_time + sess_lifetime;