Review main Merged

86831[BUGFIX] Add update wizard to prevent truncation error in DB upgrade

Benjamin Franzke CR +2 V +2 Hard +359 −0 updated 11/04/24, 18:34

Commit message

Table fields previously set to be nullable can not be migrated
(ALTERed) to "NOT NULL" if any row contains a NULL value in such
a field (which is likely for nullable fields).

A migration wizard is added that sets fields to their (future) default
value (if available) if the current value of a field is NULL
and the column is about to be changed from NULL(able) to NOT NULL.

Review

  • Code review CR +2 Done
    • Benjamin Franzke +2
    • Garvin Hicking +1
    • Stefan Bürk +1
  • Verified V +2 Done
    • Benjamin Franzke +2
    • Garvin Hicking +1
    • Stefan Bürk +1
    • core-ci +1 CI

Issues 2

Files 8

Review log 3242

Show 1017 earlier messages
  1. Benjamin Franzke uploaded patch set 1 10/29/24, 21:17

  2. core-ci voted on patch set 1 V -1 10/29/24, 21:22 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/68808

  3. Benjamin Franzke uploaded patch set 2 V -1 10/29/24, 21:39

  4. core-ci voted on patch set 2 V +1 10/29/24, 21:45 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/68809

  5. Benjamin Franzke uploaded patch set 3 V +1 10/29/24, 22:59

  6. core-ci voted on patch set 3 V +1 10/29/24, 23:05 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/68810

  7. Kevin Appelt commented on patch set 3 V +1 10/30/24, 05:06

    Can confirm that all the issues (I mentioned them in the resolved issue) are gone after running the UpgradeWizard)
  8. Benjamin Franzke uploaded patch set 4 V +1 10/30/24, 05:51

  9. core-ci voted on patch set 4 V +1 10/30/24, 05:57 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/68823

  10. Benjamin Franzke uploaded patch set 5 V +1 10/30/24, 06:04

  11. core-ci voted on patch set 5 V +1 10/30/24, 06:09 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/68824

  12. Benjamin Franzke uploaded patch set 6 V +1 10/30/24, 06:57

  13. core-ci voted on patch set 6 V +1 10/30/24, 07:02 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/68825

  14. Garvin Hicking uploaded patch set 7 V +1 10/30/24, 08:55

    Relate to TCA changelog and minor wording
  15. core-ci voted on patch set 7 V +1 10/30/24, 09:01 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/68831

  16. Garvin Hicking commented on patch set 7 V +1 10/30/24, 09:44

    Works for me. I am unsure though if Stefan Bürk had something on "his map". Two things, probably unrelated but need to make a note somewhere (resolving it to not block things): * My MySQL 8.0.36 ddev setup, using utf8mb4 charset and utf8mb4_unicode_ci collation has an old dataset base with utf8mb3 tables+fields (it was on mariadb before, where utf8=utf8mb3). With that data, the DB compare tool is caught in a loop and is never able to alter fields from utf8mb3 to utf8mb4. I believe I saw an issue for this but couldn't find it. * Due to this legacy data I also have some fe_user.image records where image='' (empty string, not NULL). The wizard doesn't cover this scenario, but I'm not sure it should.
  17. Benjamin Franzke commented on patch set 7 10/30/24, 12:09

    Reply

    The utf8 mb3 vs mb4 problem could probably be solved by https://review.typo3.org/c/Packages/TYPO3.CMS/+/86725 if you're not using `defaultTableOptions`, but I assume you have `defaultTableOptions` enabled, so that the update wizard tries hard to update mb3 to mb4? In any case, that's indeed a separate topic then, but worth an own forge issue. (The change I linked above essentially leaves the current charset as-is if `defaultTableOptions` is not configured – which is good and makes step-by-step transitions possible. Forcing UTF8mb4 for everyone would be nice, but we have a pile of BUGS to fix beforehand, like https://forge.typo3.org/issues/105515) > Due to this legacy data I also have some fe_user.image records where image='' (empty string, not NULL). The wizard doesn't cover this scenario, but I'm not sure it should. Invalid data is imho another TASK, because we can not assume that it's "OK" to migrate invalid data to a default value, while it's fine to migrate the old default to the new default. Therefore I see that as a different topic.
  1. Garvin Hicking commented on patch set 7 10/30/24, 12:46

    Reply

    Oh right, now I know why I thought this was fixed already. I did try that patch and it worked out in the past, but it's not merged yet. Thanks. Now I better see what defaulTableOptions does in that case, and indeed I had not set it explicitly. Setting it to utf8mb4 does resolve the circularities, too. Agreed on the rest, thanks for putting work into it. If we can leave this dangling for Stefan to give some input after his vacation, I'd appreciate it. :-)
  2. Benjamin Franzke commented on patch set 7 10/30/24, 13:16

    Reply

    fine by me
  3. Kevin Appelt voted on patch set 7 V +1 11/01/24, 19:33

  4. Stefan Bürk commented on patch set 7 CR -1 11/04/24, 09:53

    Reviewing ...
  5. Stefan Bürk commented on patch set 7 11/04/24, 11:09

    Reply

    On it ...

    NullToDefaultUpdateWizard.php:80

    getModifiedColumns() is deprecated by Doctrine DBAL https://github.com/doctrine/dbal/pull/6280

    NullToDefaultUpdateWizard.php:94

    This should be `$tableName` and not `$fieldName`. Retrieving for fieldname will always retrieve the default connection.
  6. Stefan Bürk commented on patch set 7 11/04/24, 11:12

    NullToDefaultUpdateWizard.php:145

    Why building raw query here and not using API for it ?
  7. Benjamin Franzke uploaded patch set 8 CR -1 V +1 11/04/24, 12:11

  8. Benjamin Franzke commented on patch set 8 11/04/24, 12:11

    NullToDefaultUpdateWizard.php:80 Reply

    oops, fixed!

    NullToDefaultUpdateWizard.php:94 Reply

    lol, thx. Done.

    NullToDefaultUpdateWizard.php:145 Reply

    Because we overwrote Connection::update to apply escaping to all values so I couln't use `column IS NULL` (`IS` because `column = null` would not work). Or will doctrine impliclty build a IS query in that case (I actually did not test that).
  9. Benjamin Franzke commented on patch set 8 11/04/24, 12:12

    NullToDefaultUpdateWizard.php:80 Reply

    Done
  10. Benjamin Franzke uploaded patch set 9 11/04/24, 12:14

  11. Benjamin Franzke commented on patch set 9 CR -1 11/04/24, 12:18

    NullToDefaultUpdateWizard.php:145 Reply

    ok, seems to work with: ``` $connection->update( $tableName, [$fieldName => $defaultValue], [$fieldName => null] ); ``` (from reading the code and having `IS NULL` vs `= null` in mind I assumed it will not – will check and update afterwards)
  12. core-ci voted on patch set 8 V +1 11/04/24, 12:18 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/69031

  13. core-ci voted on patch set 9 V +1 11/04/24, 12:21 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/69032

  14. Benjamin Franzke uploaded patch set 10 CR -1 V +1 11/04/24, 12:25

  15. Benjamin Franzke commented on patch set 10 11/04/24, 12:25

    NullToDefaultUpdateWizard.php:145 Reply

  16. Stefan Bürk commented on patch set 10 11/04/24, 12:28

    Reply

    > On it ... Did you read that ? I worked on it, just did not pushed the remarks yet because playing around with further (edge) cases from a feeling point of view ?. Will now wait and eventualy merge the changes into a later changeset and push it later.

    NullToDefaultUpdateWizard.php:81

    Not sure, if we should add the filter condition from the depreacted getModified() method here: ``` if (!($modifiedColumn->countChangedProperties() > ($modifiedColumn->hasNameChanged() ? 1 : 0))) { continue; } ``` What do you think ?
  17. Stefan Bürk commented on patch set 10 11/04/24, 12:28

    NullToDefaultUpdateWizard.php:81 Reply

    getModifiedColumns() method, sorry.
  18. core-ci voted on patch set 10 V +1 11/04/24, 12:32 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/69033

  19. Benjamin Franzke commented on patch set 10 11/04/24, 12:34

    NullToDefaultUpdateWizard.php:81 Reply

    I thought about it as well while changing the method name, but since the code takes new/old columns into account we do not need this: It uses the "old"column object to detect the field name and takes the default value from the new field in the update process That means: Once we'd possibly be able to generate RENAME operations, this would still work correctly, so we do not need to (and must not) exclude name changes (as the same update preparation is needed as well, in case name and null constraints change)
  20. Stefan Bürk commented on patch set 10 11/04/24, 12:35

    NullToDefaultUpdateWizard.php:81 Reply

    Acknowledged
  21. Benjamin Franzke commented on patch set 10 11/04/24, 12:37

    Reply

    I understood that you are still "Reviewing" (possibly because it is marked as a reply). Sorry, I really didn't get that, otherwise I wouldn't have pushed…
  22. Stefan Bürk commented on patch set 10 CR +1 V +1 11/04/24, 16:32

    Reply

    > Works for me. I am unsure though if Stefan Bürk had something on "his map". Yes, there was/is a plan but more for the future as it requires some refactorings before, which are not really backport compatible. Having a upgrade wizard for now is absolutly fine, specially under the point of beeing backported to 13.4.

    Reply

    Going to make that more clearer in the future. My bad.
  23. Garvin Hicking voted on patch set 10 CR +1 V +1 11/04/24, 17:35

  24. Benjamin Franzke commented on patch set 10 CR +2 V +2 11/04/24, 18:34

    Reply

    sorry again and thanks for the thorough review!
  25. Benjamin Franzke merged patch set 11 11/04/24, 18:34