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

[Rupert Germann]

FYI: committed to trunk rev 6166

greets
rupert


Rupert Germann schrieb:
> 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
>