Bug #16801

sys_refindex failed to create key

Added by Joerg Schoppet almost 13 years ago. Updated over 12 years ago.

Status:
Closed
Priority:
Should have
Category:
Install Tool
Target version:
-
Start date:
2006-12-21
Due date:
% Done:

0%

TYPO3 Version:
4.1beta1
PHP Version:
5
Tags:
Complexity:
Is Regression:
Sprint Focus:

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)

History

#1 Updated by Michael Stucki almost 13 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?

#2 Updated by Joerg Schoppet almost 13 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)

#3 Updated by Joerg Schoppet almost 13 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.

#4 Updated by Michael Stucki almost 13 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.

#5 Updated by Joerg Schoppet almost 13 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.

#6 Updated by Michael Stucki almost 13 years ago

OK, closing. For better clarification, I will slightly change the description of multiplyDBfieldSize.

Also available in: Atom PDF