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

[Michael Stucki]

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/
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: bug_8399_post1.diff
Url: http://lists.netfielders.de/pipermail/typo3-team-core/attachments/20081108/3688346e/attachment.txt