Bug #16801
closedsys_refindex failed to create key
0%
Description
Following environment:
mysql 5-0.27
[SYS][multiplyDBFieldSize] = 2
TYPO3 4.1.0beta2
The following statement can not be executed:
ALTER TABLE sys_refindex ADD KEY lookup_string (ref_table,ref_string(400));
Error Statement:
#14729 - Specified key was too long; max key length is 1000 bytes
(issue imported from #M4684)
Updated by Michael Stucki almost 18 years ago
Hmm. This problem was fixed in 4.0.2, I wonder why it happens again? Is it an incompatibility with MySQL 5.x?
Updated by Joerg Schoppet almost 18 years ago
It has to do with the mysql-version or with multiplyDBFieldSize.
On a system with mysql 4.1.14 and multiplyDBFieldSize not set I have the following field definitions:
ref_table -> VARCHAR
ref_string -> VARCHAR
On the failing system with mysql 5.0.27 and multiplyDBFieldSize set to 2:
ref_table -> VARCHAR
ref-string -> TEXT
Perhaps the TEXT datatype is the problem. See also Bugs 4652, 4680 (DAM)
Updated by Joerg Schoppet almost 18 years ago
I think I've found the problem:
Indexes in mysql have a max size of 1000 Bytes. If the db is utf8, one char needs three bytes
so the above statement will be:
80 + 400 = 480 * 3 = 1440 (Failure)
the ref_string have to be changed to e.g. 250
80 + 250 = 330 * 3 = 990
So the Statement have to be
ALTER TABLE sys_refindex ADD KEY lookup_string(ref_table, ref_string(250));
This works.
Updated by Michael Stucki almost 18 years ago
I think there is nothing we can change about this. TYPO3 doesn't know that your database is UTF-8 encoded, and it sends a valid key length of 480 bytes to the database. So how could it know that MySQL multiplies this again?
But besides of this: Why do you use multiplyDBfieldSize when you already use UTF-8 for your database? It is not needed! Have a look at the description of it.
Updated by Joerg Schoppet almost 18 years ago
Sorry for this, but I read the following in the Install tool for multiplyDBFieldSize:
... This is useful if you want to expand the size of fields for utf-8 ...
I thought I have to do it this way.
But ok.
After switching back to multiplyDBFieldSize = 1 and Compare the DB everything is ok now.
Updated by Michael Stucki almost 18 years ago
OK, closing. For better clarification, I will slightly change the description of multiplyDBfieldSize.