Bug #88176

DBAL Unique Key containing more than 3 columns not working

Added by Gabriel Kaufmann / Typoworx NewMedia 4 months ago. Updated 13 days ago.

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

0%

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.

History

#1 Updated by Gabriel Kaufmann / Typoworx NewMedia 4 months 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 13 days 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 13 days ago

  • Assignee set to Manuel Selbach

#4 Updated by Gerrit Code Review 13 days 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

Also available in: Atom PDF