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

[Michael Stucki]

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
> 


-- 
Use a newsreader! Check out
http://typo3.org/community/mailing-lists/use-a-news-reader/