Install Tool DB Analyzer tries to change all VARCHAR fields
I'm experiencing an issue with DB Migrations in Install Tool - the analyzer complains every time about all
varchar fields. E.g.:
ALTER TABLE `be_groups` CHANGE `title` `title` VARCHAR(50) DEFAULT '' NOT NULL Current value: title VARCHAR(50) DEFAULT '''' NOT NULL COLLATE utf8_unicode_ci ALTER TABLE `be_groups` CHANGE `allowed_languages` `allowed_languages` VARCHAR(255) DEFAULT '' NOT NULL Current value: allowed_languages VARCHAR(255) DEFAULT '''' NOT NULL COLLATE utf8_unicode_ci ALTER TABLE `be_groups` CHANGE `pagetypes_select` `pagetypes_select` VARCHAR(255) DEFAULT '' NOT NULL Current value: pagetypes_select VARCHAR(255) DEFAULT '''' NOT NULL COLLATE utf8_unicode_ci ...
What I've also noticed: the reported DEFAULT value in DB consists of 4 quotes:
'''', while proposed one contains two:
Altering table doesn't change anything, and I'm quite unsure how to fix this.
#1 Updated by Hans Höchtl 6 months ago
Same here, I posted it on Stackoverflow: [[https://stackoverflow.com/questions/45737730/typo3-lists-all-tables-in-db-compare-because-of-collate]].
I think it could be the collation, not the quotes.
#2 Updated by Aleksandar Dimitrov 6 months ago
I was able to reproduce this with 8.7.4 and 8.7.3, and mariadb 10.2.x and 5.x, on clean installs via the Install tool.
My guess is that a strict string comparison of
'''' causes the problem.
I think the serialisation of the 'original' column definition is buggy. Note the following case:
ALTER TABLE `tt_content` CHANGE `table_caption` `table_caption` VARCHAR(255) DEFAULT NULL Current value: table_caption VARCHAR(255) DEFAULT 'NULL' COLLATE utf8_unicode_ci
'NULL' is in quotes here, and most definitely should not be, as
'NULL' are very different things for any SQL server. Some aggressive string escaping maybe?
#3 Updated by Morton Jonuschat 5 months ago
- Status changed from New to On Hold
MariaDB implemented a change to the Information Schema COLUMNS table which changed the output format compared to the original (Oracle) MySQL: https://jira.mariadb.org/browse/MDEV-13132
There is ongoing work in the upstream Doctrine DBAL repository on how to fix this: https://github.com/doctrine/dbal/pull/2825
At this time TYPO3 is not compatible with MariaDB >= 10.2.7 and given that the fix is happening deeply in the upstream Doctrine DBAL stack there is little we can do to work around it at this point in time.