[TYPO3-dev] Compare in 4.3 fails

[David Bruchmann]

----- Ursprüngliche Nachricht -----
Von:        Steffen Ritter <info at rs-websystems.de>
Gesendet:   Samstag, 22. August 2009 09:46:31
An:         typo3-dev at lists.netfielders.de
CC:
Betreff:    [TYPO3-dev]  Compare in 4.3 fails
> Hey folks,
> install tool wants to make the following two changes in database which 
> fail all the time because of to long indexes.
> " Specified key was too long; max key length is 1000 bytes"
> 
> What I'm wrong about?
> 
> 
> 
> ALTER TABLE sys_refindex ADD KEY lookup_string (ref_table,ref_string);
> 
> CREATE TABLE sys_registry (
> uid int(11) unsigned NOT NULL auto_increment,
> entry_namespace varchar(128) NOT NULL default '',
> entry_key varchar(255) NOT NULL default '',
> entry_value blob,
> PRIMARY KEY (uid),
> UNIQUE entry_identifier (entry_namespace,entry_key)
> );
> 

Hy Steffen,

I never found something about that problem but I suppose that it occurs 
when the table is setup as utf-8 or another multibyte charset.
UTF-8 also in mySQL is defined as variable byte-length-charset so 
assuming that each character uses 2 bytes the required space for your 
key would be 2x383 Byte plus 2 (because varchar adds 1 byte) = 768 byte.
UNIQUE allows maximum 1000 byte - so it would be enough.
But varchars can need more space for example 3 or 4 byte. In this case 
the key would be cut.
I tested with values bigger than 255 bytes (255 needs already 256 
bytes), i.e. entry_key + uid = minimum 267 bytes - that was accepted. So 
the security-multiplier for the chars must be less than 4 but bigger than 2.

Besst regards,
David