Bug #88176

DBAL Unique Key containing more than 3 columns not working

Added by Gabriel Kaufmann / Typoworx NewMedia over 1 year ago. Updated 10 months ago.

Status:
Closed
Priority:
Must have
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2019-04-18
Due date:
% Done:

100%

TYPO3 Version:
8
PHP Version:
7.1
Tags:
mysql dbal unique-key
Complexity:
Is Regression:
Sprint Focus:

Description

I tried to define ext_tables.sql structure and a table that has unique-key consisting of multiple fields.

This is working as expected with max. 3 fields added:

UNIQUE unique_asset (parent_table, parent_id, parent_field)

I required to add another one - which fails after testing sql-inserts

UNIQUE unique_asset (parent_table, parent_id, parent_field,parent_field_index)

TYPO3 Install-Tool reports to successfully create database-structure. After a while I noticed that I got SQL-Exceptions like this for duplicate-entries:

Duplicate entry 'tx_mytablename_domain_model_pro-114-instructions' for key 'unique_asset'

I noticed the last index-field obviously is missing there! It should have been a numeric index.
In PhpMyAdmin I've noticed the recent added index-field 'parent_field_index' is really missing/not created at all!

I've deleted and recreated the index manually on phpmyadmin and it works:

ALTER TABLE `tx_mytablename_domain_model_pro` ADD UNIQUE unique_asset (parent_table, parent_id, parent_field,parent_field_inde);

So it looks like there somewhere is a hard-coded limit either in TYPO-Core or in Doctrine/DBAL? I would at least have expected TYPO3-Install tool to throw a hint on this, but it's completly ignoring the 4th index-field and reporting success everywhere - except on duplicate-entries raising SQL-Exception obviously.


Related issues

Related to TYPO3 Core - Bug #89306: postgres Analyze Database Structure and Upgrade Wizzard fails to read indicies and primary keys Closed 2019-09-30

Associated revisions

Revision ac4e3bfa (diff)
Added by Manuel Selbach almost 1 year ago

[BUGFIX] Respect changed indexes within schema migration

With this change all indexes identified as "changed" will get proper
SQL statements to update them (e.g. DROP and ADD afterwards).

The order has also been changed to apply the index changes first, to
avoid issues on changing a field on a table, see #89169.

Releases: master, 9.5
Resolves: #88176
Related: #89169
Related: #89063
Change-Id: Ia683ff1a6ccea47cc9b3848f2db0b9f32b984626
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/61476
Tested-by: TYPO3com <>
Tested-by: Daniel Goerz <>
Tested-by: Alexander Schnitzler <>
Tested-by: Manuel Selbach <>
Reviewed-by: Daniel Goerz <>
Reviewed-by: Guido Schmechel <>
Reviewed-by: Markus Klein <>
Reviewed-by: Alexander Schnitzler <>
Reviewed-by: Manuel Selbach <>

Revision 4e9c9ac3 (diff)
Added by Manuel Selbach almost 1 year ago

[BUGFIX] Respect changed indexes within schema migration

With this change all indexes identified as "changed" will get proper
SQL statements to update them (e.g. DROP and ADD afterwards).

The order has also been changed to apply the index changes first, to
avoid issues on changing a field on a table, see #89169.

Releases: master, 9.5
Resolves: #88176
Related: #89169
Related: #89063
Change-Id: Ia683ff1a6ccea47cc9b3848f2db0b9f32b984626
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/62063
Tested-by: TYPO3com <>
Tested-by: Manuel Selbach <>
Reviewed-by: Manuel Selbach <>

History

#1 Updated by Gabriel Kaufmann / Typoworx NewMedia over 1 year ago

After some research I've noticed this ext_tables.sql variant works creating the UNIQUE-Key with all fields. Don't know why:

UNIQUE KEY unique_asset (parent_table, parent_id, parent_field, parent_field_index),

#2 Updated by Gerrit Code Review about 1 year 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/c/Packages/TYPO3.CMS/+/61476

#3 Updated by Manuel Selbach about 1 year ago

  • Assignee set to Manuel Selbach

#4 Updated by Gerrit Code Review about 1 year 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/c/Packages/TYPO3.CMS/+/61476

#5 Updated by Gerrit Code Review about 1 year ago

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

#6 Updated by Gerrit Code Review about 1 year 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/c/Packages/TYPO3.CMS/+/61476

#7 Updated by Gerrit Code Review about 1 year 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/c/Packages/TYPO3.CMS/+/61476

#8 Updated by Gerrit Code Review about 1 year 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/c/Packages/TYPO3.CMS/+/61476

#9 Updated by Manuel Selbach about 1 year ago

  • Related to Bug #89306: postgres Analyze Database Structure and Upgrade Wizzard fails to read indicies and primary keys added

#10 Updated by Gerrit Code Review almost 1 year 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/c/Packages/TYPO3.CMS/+/61476

#11 Updated by Gerrit Code Review almost 1 year ago

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

#12 Updated by Gerrit Code Review almost 1 year ago

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

#13 Updated by Gerrit Code Review almost 1 year ago

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

#14 Updated by Manuel Selbach almost 1 year ago

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

#15 Updated by Benni Mack 10 months ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF