Project

General

Profile

Actions
Copy link

Bug #105498

closed

Nullable table fields can not be migrated (ALTERed) to "NOT NULL" in an update from v12 to v13

Added by Benjamin Franzke 24 days ago. Updated 17 days ago.

Status:
Resolved
Priority:
Should have
Assignee:
-
Category:
Install Tool
Target version:
Candidate for patchlevel
Start date:
2024-10-28
Due date:
% Done:

100%

Estimated time:
TYPO3 Version:
13
PHP Version:
Tags:
Complexity:
Is Regression:
Sprint Focus:

Description

Example: fe_users.image
This field has been changed from TINYTEXT DEFAULT NULL (in v12) to INT UNSIGNED DEFAULT NOT NULL (in v13).

That means the following ALTER TABLE statement is generated:

ALTER TABLE `fe_users` CHANGE `image` `image` INT UNSIGNED DEFAULT 0 NOT NULL

Which fails with:

 Error: Data truncated for column 'image' at row 1

When there are columns (which was fine in v12) with null values:

select uid, pid, username, image from fe_users;
+-----+------+--------------------------+-------+
| uid | pid  | username                 | image |
+-----+------+--------------------------+-------+
|   1 |  178 | styleguide-frontend-demo | NULL  |
|   2 |  205 | styleguide-frontend-demo | NULL  |
|   3 |  771 | example_user             | NULL  |
|   4 | 1008 | admin                    | 0     |
|   5 | 1013 | admin                    | NULL  |
+-----+------+--------------------------+-------+

Desired solution:

There should be an upgrade wizard that runs prior to this database updates, that sets all values of current nullable fields to the schema default value, when the desired schema is a non-nullable field with a default value.

Actions
Copy link
 #1

Updated by Benjamin Franzke 24 days ago

  • Description updated (diff)
Actions
Copy link
 #2

Updated by Benjamin Franzke 24 days ago

  • Description updated (diff)
Actions
Copy link
 #3

Updated by Garvin Hicking 23 days ago ยท Edited

Issue also came up on slack today: https://typo3.slack.com/archives/C025BQLFA/p1730212799669139

For me (running mariadb/mysql) I could fix data truncation like:

UPDATE fe_users SET image = '0' WHERE image IS NULL;
UPDATE fe_users SET image = '0' WHERE image NOT RLIKE '^[0-9]+$'

the second part was needed because in my database I also had an entry with "image = '2'". Don't know how that got into the database even, but since it's valid tinytext, we might need an update wizard that checks anything that isn't number-ish.

Like you indicate, it would be needed for any DB compare alteration that NULL to NOT NULL; my personal "number-ish" case probably shouldn't be of concern. ( updated this sentence)

Actions
Copy link
 #4

Updated by Gerrit Code Review 22 days ago

  • Status changed from Accepted to Under Review

Patch set 1 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/86831

Actions
Copy link
 #5

Updated by Gerrit Code Review 22 days ago

Patch set 2 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/86831

Actions
Copy link
 #6

Updated by Gerrit Code Review 22 days ago

Patch set 3 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/86831

Actions
Copy link
 #7

Updated by Kevin Appelt 22 days ago

Coming from the current discussion in TYPO3 Slack and providing the list of failing changes:

NEW: `pages.field_a` SMALLINT UNSIGNED DEFAULT 0 NOT NULL
WAS: `pages.field_a` TEXT CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`

NEW: `pages.field_b` VARCHAR(255) CHARACTER SET utf8mb4 DEFAULT '' NOT NULL COLLATE `utf8mb4_unicode_ci`
WAS: `pages.field_b` TEXT CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`

NEW: `pages.field_c` VARCHAR(255) CHARACTER SET utf8mb4 DEFAULT '' NOT NULL COLLATE `utf8mb4_unicode_ci`
WAS: `pages.field_c` TEXT CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`

NEW: `sys_file_metadata.field_a` VARCHAR(255) CHARACTER SET utf8mb4 DEFAULT '' NOT NULL COLLATE `utf8mb4_unicode_ci`
WAS: `sys_file_metadata.field_a` TINYTEXT CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`

NEW: `tt_content.field_a` VARCHAR(255) CHARACTER SET utf8mb4 DEFAULT '' NOT NULL COLLATE `utf8mb4_unicode_ci`
WAS: `tt_content.field_a` TINYTEXT CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`

NEW: `tt_content.field_b` VARCHAR(255) CHARACTER SET utf8mb4 DEFAULT '' NOT NULL COLLATE `utf8mb4_unicode_ci`
WAS: `tt_content.field_b` TINYTEXT CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`

NEW: `tt_content.field_c` VARCHAR(255) CHARACTER SET utf8mb4 DEFAULT '' NOT NULL COLLATE `utf8mb4_unicode_ci`
WAS: `tt_content.field_c` TINYTEXT CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`

NEW: `tt_content.field_d` VARCHAR(255) CHARACTER SET utf8mb4 DEFAULT '' NOT NULL COLLATE `utf8mb4_unicode_ci`
WAS: `tt_content.field_d` TINYTEXT CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`

NEW: `tt_content.field_e` VARCHAR(255) CHARACTER SET utf8mb4 DEFAULT '' NOT NULL COLLATE `utf8mb4_unicode_ci`
WAS: `tt_content.field_e` TINYTEXT CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`

NEW: `tt_content.field_f` VARCHAR(255) CHARACTER SET utf8mb4 DEFAULT '' NOT NULL COLLATE `utf8mb4_unicode_ci`
WAS: `tt_content.field_f` TINYTEXT CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`

NEW: `tt_content.field_g` VARCHAR(255) CHARACTER SET utf8mb4 DEFAULT '' NOT NULL COLLATE `utf8mb4_unicode_ci`
WAS: `tt_content.field_g` VARCHAR(255) CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`

NEW: `tt_content.field_h` TEXT CHARACTER SET utf8mb4 DEFAULT '' NOT NULL COLLATE `utf8mb4_unicode_ci`
WAS: `tt_content.field_h` VARCHAR(2048) CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`

NEW: `some_table.field_a` VARCHAR(255) CHARACTER SET utf8mb4 DEFAULT '' NOT NULL COLLATE `utf8mb4_unicode_ci`
WAS: `some_table.field_a` TINYTEXT CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`

With some cleanup:

NEW: SMALLINT UNSIGNED DEFAULT 0 NOT NULL
WAS: TEXT CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`

NEW: VARCHAR(255) DEFAULT '' NOT NULL
WAS: TEXT DEFAULT NULL

NEW: VARCHAR(255) DEFAULT '' NOT NULL
WAS: TINYTEXT DEFAULT NULL

NEW: VARCHAR(255) DEFAULT '' NOT NULL
WAS: VARCHAR(255) DEFAULT NULL

NEW: TEXT DEFAULT '' NOT NULL 
WAS: VARCHAR(2048) DEFAULT NULL

And finally:

NEW: SMALLINT UNSIGNED DEFAULT 0 NOT NULL
WAS: TEXT CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci`

NEW: DEFAULT '' NOT NULL
WAS: DEFAULT NULL

So I guess the main issue is that values could be NULL (default) before which is not allowed anymore with NOT NULL where the default is now ''.
But there might be also cases where the definition changes a lot like from TEXT to SMALLINT (which in this case is caused by a totally wrong table definition before, which gets corrected with the auto creation now (in this case it is a checkbox). Maybe also the TEXT => VARCHAR(255) might lead to problems (but in this case the values should be not longer than 255 chars, so it's fine).

Actions
Copy link
 #8

Updated by Gerrit Code Review 22 days ago

Patch set 4 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/86831

Actions
Copy link
 #9

Updated by Gerrit Code Review 22 days ago

Patch set 5 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/86831

Actions
Copy link
 #10

Updated by Gerrit Code Review 22 days ago

Patch set 6 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/86831

Actions
Copy link
 #11

Updated by Gerrit Code Review 22 days ago

Patch set 7 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/86831

Actions
Copy link
 #12

Updated by Gerrit Code Review 17 days ago

Patch set 8 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/86831

Actions
Copy link
 #13

Updated by Gerrit Code Review 17 days ago

Patch set 9 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/86831

Actions
Copy link
 #14

Updated by Gerrit Code Review 17 days ago

Patch set 10 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/86831

Actions
Copy link
 #15

Updated by Gerrit Code Review 17 days ago

Patch set 1 for branch 13.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/+/86870

Actions
Copy link
 #16

Updated by Benjamin Franzke 17 days ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100
Actions
Copy link

Also available in: Atom PDF