Project

General

Profile

Actions

Bug #80043

closed

Upgrade Wizard must only add PK indexes

Added by Thomas Hohn about 7 years ago. Updated over 6 years ago.

Status:
Closed
Priority:
Must have
Assignee:
Category:
Install Tool
Target version:
Start date:
2017-02-27
Due date:
% Done:

100%

Estimated time:
TYPO3 Version:
8
PHP Version:
7.0
Tags:
Complexity:
Is Regression:
No
Sprint Focus:
On Location Sprint

Description

The field "sha1" of table "sys_file" was a tinytext in 6.2 and is a CHAR in master. The key sha1 changed from "KEY sha1 (sha1(40))" to "KEY sha1 (sha1)".

In the update wizards: TYPO3 tries to first change the key and fails here because of field type not being allowed for the new key.

In the DB analyzer: The list of suggested changes offers the sha1 key update as well (preselect by default) and offers the field type change as well (not preselected). Following this "recommendation" fails here as well.

Possible solution:
Since the "Upgrade Wizard" Database analyser only create tables/columns/indexes
in must only take indexes into account that are primary keys - since other indexes
can rely on column updates which are required to be performed before the index
is created.


Related issues 1 (0 open1 closed)

Related to TYPO3 Core - Bug #80096: [BUGFIX] ConnectionMigrator resets changedColumnsClosedThomas Hohn2017-03-02

Actions
Actions #1

Updated by Gerrit Code Review about 7 years ago

  • Status changed from New to Under Review

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

Actions #2

Updated by Gerrit Code Review about 7 years ago

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

Actions #3

Updated by Thomas Hohn about 7 years ago

Bugreport:

Johannes Kasberger [2:14 PM]
I’m currently trying to run the upgrade wizards for a site running on 6.2 (using the current master) and I get the following error:
```Update failed!
SQL-ERROR: BLOB/TEXT column 'sha1' used in key specification without a key length
SQL-ERROR: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys```

The issues is:
The field "sha1" of table "sys_file" was a tinytext in 6.2 and is a CHAR in master. and the key changed from "KEY sha1 (sha1(40))" to "KEY sha1 (sha1)".
TYPO3 tries to first change the key and fails here because of wrong type. try to do the type change from tinytext to char first, then try if the key change goes through.

This is due to the fact the the Upgrade Wizard only performs creates.

Actions #4

Updated by Markus Klein about 7 years ago

  • Description updated (diff)
Actions #5

Updated by Morton Jonuschat about 7 years ago

I can't reproduce this using the following steps:

1. Install TYPO3 6.2 into a clean database
2. Install TYPO3 8.7-dev, point it at existing database
3. Go to install tool
4. Go to Upgrade Wizards
5. Execute Update database schema: Create tables and fields

No problem shows up, using a debugger to check the decision Doctrine is making the index sha1 is detected as a changed index and does not interfere with the upgrade process.

Regarding the changes in the install tool database analyzer: Thats the downside of being able to do each change as a dedicated operation. Doctrine would normally do this in a single operation without problems. In this case some knowledge by the admin is required that the field needs to be changed before the index - and it's only relevant if the field changes from TEXT/BLOB to something else.

Actions #6

Updated by Morton Jonuschat about 7 years ago

I figured out a way to recreate the situation by dropping / renaming the index sha1 between steps 2 and 5. In that case the index will be detected as an add and due to the column not matching the index requirements this error shows up.

So at this point I need to assume that the database from TYPO3 6.2 was not "in good health" to begin this.

I feel that this is not something we could or should try to fix programmatically on our own. In my opinion requiring the database to be in a good shape before an upgrade attempt - having all changes required by the currently running TYPO3 version applied - is reasonable.

I don't think that building something on top to try and work around this "bad" starting situation is feasible or scalable, especially as there's only a really slim chance of this happening:

1) An index that exists in both TYPO3 versions must be missing
2) The index definition has changed between TYPO3 versions
3) The fields used by the index currently exist or will be added
4) At least one of the fields that do exist is of type TEXT or BLOB in the current schema
5) At least one of the existing TEXT/BLOB columns was changed to a different type that does not require a prefix length value in the wanted schema
6) The index definition does not specify a prefix length for the column that changed type anymore

Only in this constellation (and only on MySQL) can this bug be triggered.

I would be inclined to reject this bugreport and abandon the patch unless there is a different way to trigger this situation, especially since the current approach doesn't seem to fix it.

Thoughts?

Actions #7

Updated by Markus Klein about 7 years ago

The definition of the table in 6.2 is:

CREATE TABLE sys_file (
    sha1 tinytext,
...
    KEY sha1 (sha1(40))
);

Whereas 8.6 has:

CREATE TABLE sys_file (
    sha1 char(40) DEFAULT '' NOT NULL,
...
    KEY sha1 (sha1)
);

Maybe it is important which DB is used and in which mode. Johannes will check that on his local machine.

The project we are upgrading has a clean DB in that regard.

Actions #8

Updated by Gerrit Code Review about 7 years ago

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

Actions #9

Updated by Gerrit Code Review about 7 years ago

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

Actions #10

Updated by Gerrit Code Review about 7 years ago

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

Actions #11

Updated by Gerrit Code Review about 7 years ago

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

Actions #12

Updated by Morton Jonuschat about 7 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100
Actions #13

Updated by Riccardo De Contardi over 6 years ago

  • Status changed from Resolved to Closed
Actions

Also available in: Atom PDF