Bug #18781
closed
field tablename in sys_refindex too short
0%
Description
When I'm using intermediate table in IRRE relations
I'm getting such error
caller t3lib_DB::exec_INSERTquery
ERROR Duplicate entry '77e032659be3cf41758181f06f337bd3' for key 1
lastBuiltQuery INSERT INTO sys_refindex
(
tablename,
recuid,
field,
flexpointer,
softref_key,
softref_id,
sorting,
deleted,
ref_table,
ref_uid,
ref_string,
hash
) VALUES (
'tx_sfterminals_person_attrcat_rel_attr_rel',
'26',
'attr_id',
'',
'',
'',
'0',
'0',
'tx_sfterminals_attr',
'16',
'',
'77e032659be3cf41758181f06f337bd3'
)
I've traced that to the fact that table name tx_sfterminals_person_attrcat_rel_attr_rel doesn't fit into field in sys_refindex and this cause wrong handling of ref index so duplication error occures. If the field enlarged there is no error.
(issue imported from #M8399)
Files
Updated by Steffen Kamper almost 16 years ago
I agree that 40 characters are too short, especially when using IRRE-relations. So i made a patch which increase it to 64 chars
Updated by Oliver Hader almost 16 years ago
Yepp, 64 chars look like this (which should be enough):
tx_mywhatever_firstbaseobjectsuperxx_secondbaseobjectsuperyy_rel
Updated by Vladimir Podkovanov almost 16 years ago
Oliver, really I had relation between another relation table and entity so relation table name looks like tx_mywhatever_firststuff_secondstaff_rel_thirdstuff_rel
however 64 should be enough ;)
Updated by Michael Stucki over 14 years ago
This leads to a problem as reported in #20867: The index "lookup_string" of sys_refindex consists of "ref_table" and "ref_string".
Before this change, the size of the index was (40 (ref_table) + 200 (ref_string)) * 3 (because of UTF-8) = 720 bytes.
After the change, the size is (255 (ref_table) + 200 (ref_string) ) * 3 (UTF-8) = 1365 bytes.
However, indexes must not exceed 1000 bytes, so this one is now too long, and the table can't be created any more.
Solutions to the problem:
1. Restrict index length. Works, and was used until rev. 5694. However, it does not work with DBAL apparantly. So if we come back to this solution, DBAL must be fixed in some way to cope with this.
2. Shrink the field size again. The maximum af 255 was just a wild guess I would say. The maximum which is possible without modiying the index is 133 (because (133+200)*3 = 999 bytes). Is this length also acceptable for table names?
3. Remove the index completely. Wise idea?
4. Revert this change.
For now I won't apply any changes but if nobody fixes this, I would probably choose option 4.
- michael
Updated by Rupert Germann over 14 years ago
the solution for the follow-up problem of this bug is in #0011694
(index too long on utf-8 databases)
The problem with the too short table names in branches 4_2 and 4_1 still needs to be fixed and the indizes must be changed accordingly.
Updated by Rupert Germann over 14 years ago
committed #0011694 to trunk.
So the problem is fixed in trunk since rev 6166
I keep this bug open because the fix is still missing in the older branches