Bug #105498
open
Nullable table fields can not be migrated (ALTERed) to "NOT NULL" in an update from v12 to v13
0%
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.
Updated by
Updated by Garvin Hicking 1 day 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)
Updated by Gerrit Code Review 1 day 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
Updated by Gerrit Code Review 1 day 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
Updated by Gerrit Code Review 1 day 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
Updated by Kevin Appelt 1 day 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).
Updated by Gerrit Code Review 1 day 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
Updated by Gerrit Code Review 1 day 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
Updated by Gerrit Code Review 1 day 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
Updated by Gerrit Code Review 1 day 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