[TYPO3-core] RFC #11694: Table sys_refindex / KEY lookup_string not UTF-8 compatible

[Rupert Germann]

Hi,

this is a SVN patch request.

Type: Bugfix (follow-up)

Bugtracker references:
http://bugs.typo3.org/view.php?id=11694

Branches: Trunk

Problem:
In t3lib/stddb/tables.sql the key definition was changed from 
(ref_table(133),ref_string) to (ref_table,ref_string).

This leads to an invalid key for databases running in UTF8 mode. Cause 
of this is that keys in MySQL are defined on character lengths but the 
internal limits are counted in bytes. As MySQL is limited to 1000 bytes 
per Index the above definition causes a Problem since the Index is 1365 
Bytes (MySQL 5.x, using 3 Bytes per character) or 1820 bytes (MySQL 6.0 
using 4 bytes per character).

This bug shows up in the install tool where the index cannot be 
updated/created.

Steps To Reproduce:
Create a MySQL Database in UTF-8 collaction/character_set and run TYPO3 
Install tool to create tables

Solution:
remove table "ref_table" from KEY "lookup_string"

Info:
writing the explain output of all sys_refindex related queries to devlog 
showed that the key "lookup_string" without "ref_table" had the same 
influence on the amount of selected rows as with "ref_table".

here's the snippet that I used to test.
insert this to function exec_SELECTquery() in t3lib_db:
...
if (strpos($from_table, 'sys_ref') !== FALSE) {
	$expRes = $this->sql_query('EXPLAIN ' . $query);
	$explain = $this->sql_fetch_assoc($expRes);
	t3lib_div::devLog('refIndex test', __FUNCTION__ , 1, array(
		'trace' => t3lib_div::debug_trail(), 'query' => $query, 'EXPLAIN' => 
$explain));
}
....

4_2 and 4_1 have the same index definition but the field ref_table is 
varchar(40), so no problem in this branches.




greets
rupert