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

[Michael Stucki]

By the way: For more info about the problem, look here:

- http://bugs.mysql.com/bug.php?id=4541
- http://dev.mysql.com/doc/refman/5.0/en/create-table.html (search for
  col_name)

Cheers, 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/