Bug #82023: Install Tool DB Analyzer tries to change all VARCHAR fields - TYPO3 Core - TYPO3 Forge

Custom queries

12 LTS
Accessibility
Dashboard issues
Easy tasks
Extbase Issues
FAL Issues
FAL Sprint 2023
Hierarchical Issues
Issues reported for v11
JavaScript issues
JavaScript Issues (w/o Epics/Stories)
Most wanted Bugfixes
Most wanted Features
My open issues
New & Unassigned
No feedback within 90 days
Open Bugs
Open issues of 10
Page Tree Regressions after 2020-07-28
Recently modified
Regressions
Stabilization Issues
Usability or UX

Watchers (15)

Alexander Vogt
Arne Bracht
Charles Coleman
Christiane Helmchen
Denis Mir
Kevin Purrmann
Lioh Moeller
M. Martens
Marco Ziesing
Markus no-lastname-given
Sybille Peters
Tilo Baller
Tobias Schaefer
Torben Hansen
Wittkiel Gruppe

Actions

Copy link

Bug #82023

closed

Install Tool DB Analyzer tries to change all VARCHAR fields

Added by Viktor Livakivskyi over 6 years ago. Updated about 3 years ago.

Status:

Rejected

Priority:

Must have

Assignee:

Category:

Database API (Doctrine DBAL)

Target version:

Start date:

2017-08-01

Due date:

% Done:

Estimated time:

TYPO3 Version:

PHP Version:

7.1

Tags:

Complexity:

Is Regression:

Sprint Focus:

Description

Hi developers,

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.

TYPO3 8.7.4
PHP 7.1
MariaDB 10.2.7

Related issues 1 (0 open — 1 closed)

Has duplicate TYPO3 Core - Bug #83046: sys_file_collection and tt_content could not be created

Closed

2017-11-20

Actions

Issue # Delay: days Cancel

History
Notes
Property changes

Actions

Copy link

Updated by Hans Höchtl over 6 years 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.

Actions

Copy link

Updated by Aleksandar Dimitrov over 6 years 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 '' with '''' 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 and 'NULL' are very different things for any SQL server. Some aggressive string escaping maybe?

Actions

Copy link

Updated by Morton Jonuschat over 6 years 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.

Actions

Copy link

Updated by Benni Mack over 6 years ago

Status changed from On Hold to Rejected

So, the only thing TYPO3 can do, is wait for the Doctrine DBAL fix, and once this is done we can upgrade our dependency.

Actions

Copy link

Updated by Georg Ringer over 6 years ago

Has duplicate Bug #83046: sys_file_collection and tt_content could not be created added

Actions

Copy link

Updated by Georg Ringer over 6 years ago

the PR https://github.com/doctrine/dbal/pull/2825 has been merged yesterday, so lets see when a release is done

Actions

Copy link

Updated by Thomas Böhm over 5 years ago

Has this problem been solved? I just installed a fresh typo3 8.7.19 on mariadb 10.2.17 and still have this problem. Or is it solved in mariadb 10.3? Downgrading to 10.1 is not an easy option for me as there are other databases already on that server.

Actions

Copy link

Updated by Jan Kiesewetter over 5 years ago

TYPO3 8.7 has dbal 2.5.10 locked atm.
https://github.com/TYPO3/TYPO3.CMS/blob/TYPO3_8-7/composer.lock#L339-L345
and installs maximum v2.5.13.

MariaDB 10.2 support came with doctrine/dbal 2.7.0
https://github.com/doctrine/dbal/releases/tag/v2.7.0

So this is fixed for master and TYPO3 ^9.3 but not for 8.7.

Actions

Copy link

Updated by Georg Ringer over 5 years ago

Some final words: This won't be solved as doctrine dbal 2.7.0 requires php 7.1 which doesn't fit to the min. requirements of TYPO3 8.7 which got php 7.0.

Actions

Copy link

#10

Updated by Jan Kiesewetter over 5 years ago

As PHP 7.0 is EOL by 3 Dec 2018 http://php.net/supported-versions.php, maybe TYPO3 8.7 could raise requirements after that?

Actions

Copy link

#11

Updated by Thomas Oliver Moll over 5 years ago

Priority changed from Should have to Must have

Jan Kiesewetter wrote:

As PHP 7.0 is EOL by 3 Dec 2018 http://php.net/supported-versions.php, maybe TYPO3 8.7 could raise requirements after that?

This Problem is still present with PHP 7.0 dead for a month now and TYPO3 8.7 still in support for quite some time clinging to the PHP 7.0 requirement doesn't make much sense now.

Actions

Copy link

#12

Updated by Arne Bracht over 4 years ago

Hello,

I changed /vendor/doctrine/dbal with the dbal 2.7.2 from here: https://www.doctrine-project.org/projects/dbal/2.7.html within TYPO3 8.7. I think that it worked. Should it be so simple?

I hope it could be integrated in the next TYPO3 8.7.x update. Yes I know it change the PHP requirements from PHP 7.0 to PHP 7.1. But why not? PHP 7.0 is EOL by 3 Dec 2018. And so TYPO3 8.7.27 <= php 7.0 and TYPO3 8.7.28 >= PHP 7.1 should be easily to make a notice in the requirements list and update notice.

Thanks

Actions

Copy link

#13

Updated by Lioh Moeller over 4 years ago

Why has this issue been marked as 'rejected'? We still face it with current typo3 version 9.5.9 and still 8.7.x in combination with MariaDB 10.3.

Actions

Copy link

#14

Updated by Pascal Naujoks about 4 years ago

PHP Version changed from 7.1 to 7.4

Also exists with PHP Version 7.4.4 and 10.4.12-MariaDB-1:10.4.12+maria~stretch on TYPO3 8.7.31

Example of fields that want to be updated but can't:

ALTER TABLE `be_groups` CHANGE `title` `title` VARCHAR(50) DEFAULT '' NOT NULL 
Current value: title VARCHAR(50) DEFAULT '''' NOT NULL COLLATE utf8_general_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_general_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_general_ci
 ALTER TABLE `be_groups` CHANGE `description` `description` VARCHAR(2000) DEFAULT '' NOT NULL 
Current value: description VARCHAR(2000) DEFAULT '''' NOT NULL COLLATE utf8_general_ci
...

Actions

Copy link

#15

Updated by Richard no-lastname-given about 4 years ago

PHP Version changed from 7.4 to 7.2

Actions

Copy link

#16

Updated by Richard no-lastname-given about 4 years ago

PHP Version changed from 7.2 to 7.1

Actions

Copy link

#17

Updated by Richard no-lastname-given about 4 years ago

I can confirm this issue on TYPO3 CMS 8.7.32, PHP 7.2 and MariaDB 10.2.8. My client bought ELTS for security updates. Buying ELTS for security reasons and at the same time not being able to update to MariaDB 10.2 is an issue because support for MariaDB 10.1 will be dropped in October 2020.

Actions

Copy link

#18

Updated by Tim Spiekerkötter over 3 years ago

Hey everyone,
is there anything we can do in this scenario? We have this problem with a ELTS installation as well. SLES MariaDB 10.2 and PHP 7.2.
Are there any other known problems with MariaDB 10.2 and TYPO3 8.7? I am thinking about building a workaround into the install tool for this exact case, has anyone done this before?

Actions

Copy link

#19

Updated by Yurii Panchenko over 3 years ago

Tim Spiekerkötter wrote:

Hey everyone,
is there anything we can do in this scenario? We have this problem with a ELTS installation as well. SLES MariaDB 10.2 and PHP 7.2.
Are there any other known problems with MariaDB 10.2 and TYPO3 8.7? I am thinking about building a workaround into the install tool for this exact case, has anyone done this before?

I was migrating the database to typo3 v10 (php7.4, mysql 5.7). I had an error with the field:

`seo_title` varchar (255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ',
I did this and the error went away:
1) Made a new empty field
`seo_title_temp` varchar (255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ',
2) Copied information excluding NULL
UPDATE `pages` SET `seo_title_temp` = `seo_title` WHERE `seo_title` IS NOT NULL
3) Removed `seo_title`
4) Renamed `seo_title_temp` to `seo_title`

There is no more error)))

Actions

Copy link

#20

Updated by Tomas Norre Mikkelsen about 3 years ago

I'm facing this issue with:

TYPO3 8.7 ELTS
MariaDB: 10.1.x
PHP: 7.2.x

Actions

Copy link

Also available in: Atom PDF

Project

General

Profile

TYPO3 Core

Custom queries

Watchers (15)

Bug #82023

Install Tool DB Analyzer tries to change all VARCHAR fields

Updated by Hans Höchtl over 6 years ago

Updated by Aleksandar Dimitrov over 6 years ago

Updated by Morton Jonuschat over 6 years ago

Updated by Benni Mack over 6 years ago

Updated by Georg Ringer over 6 years ago

Updated by Georg Ringer over 6 years ago

Updated by Thomas Böhm over 5 years ago

Updated by Jan Kiesewetter over 5 years ago

Updated by Georg Ringer over 5 years ago

Updated by Jan Kiesewetter over 5 years ago

Updated by Thomas Oliver Moll over 5 years ago

Updated by Arne Bracht over 4 years ago

Updated by Lioh Moeller over 4 years ago

Updated by Pascal Naujoks about 4 years ago

Updated by Richard no-lastname-given about 4 years ago

Updated by Richard no-lastname-given about 4 years ago

Updated by Richard no-lastname-given about 4 years ago

Updated by Tim Spiekerkötter over 3 years ago

Updated by Yurii Panchenko over 3 years ago

Updated by Tomas Norre Mikkelsen about 3 years ago