[TYPO3-core] RFC: #8399: field tablename in sys_refindex too short

Steffen Ritter info at rs-websystems.de
Sat Aug 22 13:55:32 CEST 2009


Michael Stucki schrieb:
> I need to stress this again. Currently, the table sys_refindex CANNOT be
> created on UTF-8 tables (and UTF-8 is what we want) because of this change.
> 
> See http://bugs.typo3.org/view.php?id=11694 for more information, plus
> see my detailed explanation below.
> 
> After reading through this thread, I notice that first it was proposed
> to change the field length to 64 chars until Dmitry suggested that 255
> is possible for varchar. Besides that, I find no reason for using 255.
> 
> So my suggested solution is to change _all_ fields that have been
> changed by this "fix" (see changeset attached) back to 64 characters.
> 
> That would solve #11694 and still improve #8399 which allowed only 40
> characters before.
> 
> - michael
> 
> Michael Stucki schrieb:
>> Hi Ingo & Co.
>>
>> Ingo Renner wrote:
>>> Steffen Kamper wrote:
>>>
>>>> After clarify from Dmitry i now changed all relevant fields to
>>>> varchar(255).
>>>> New patch is attached.
>>> nobrainer, +1
>> Not quite. When changing fields you should also check if the database
>> updater likes it.
>>
>> The problem here was that a field was enlarged which was part of a key:
>>
>> CREATE TABLE sys_refindex (
>>   hash varchar(32) DEFAULT '' NOT NULL,
>>   tablename varchar(255) DEFAULT '' NOT NULL,
>>   recuid int(11) DEFAULT '0' NOT NULL,
>>   [...]
>>   ref_table varchar(255) DEFAULT '' NOT NULL,
>>   ref_uid int(11) DEFAULT '0' NOT NULL,
>>   ref_string varchar(200) DEFAULT '' NOT NULL,
>>
>>   PRIMARY KEY (hash),
>>   KEY lookup_rec (tablename,recuid),
>>   KEY lookup_uid (ref_table,ref_uid),
>>   KEY lookup_string (ref_table,ref_string)
>> );
>>
>> Now updating this table will always fail, and here is the reason:
>>
>> - Indexes can only be 1000 bytes and no longer
>> - Our database content is UTF-8 as we recommend that to all users
>> - A UTF-8 field uses 3 times the string size
>> - So the index "lookup_string" is 255*3 + 200*3 = 1365 bytes long, which
>>   is > 1000
>>
>> The solution is to limit the key length, which I did as follows:
>>
>>   KEY lookup_string (ref_table(133),ref_string)
>>
>> Reason:
>> I expect that ref_string may take a better use of the full field size
>> than ref_table (see discussion before). Therefore I only limit the size
>> of ref_table to 133 characters, so the resulting key in a UTF-8 table
>> will be 133*3 + 200*3 = 999 bytes.
>>
>> I consider this a no-brainer although it took me quite some time to
>> figure this out.
>>
>> The fix is committed in revision 4434 (find the changeset attached).
>>
>> - michael
>>
> 
> 
I'm +1 to this topic... encountered this "cannot create" table ...

Also applies to new table sys_registry...

regards

Steffen


More information about the TYPO3-team-core mailing list