http://forge.typo3.org/http://forge.typo3.org/themes/typo3_forge/favicon/favicon.png?17058661692006-12-21T10:40:39ZTYPO3 ForgeTYPO3 Core - Bug #16801: sys_refindex failed to create keyhttp://forge.typo3.org/issues/16801?journal_id=435062006-12-21T10:40:39ZMichael Stuckimichael.stucki@typo3.org
<ul></ul><p>Hmm. This problem was fixed in 4.0.2, I wonder why it happens again? Is it an incompatibility with MySQL 5.x?</p> TYPO3 Core - Bug #16801: sys_refindex failed to create keyhttp://forge.typo3.org/issues/16801?journal_id=435072006-12-21T10:52:45ZJoerg Schoppet
<ul></ul><p>It has to do with the mysql-version or with multiplyDBFieldSize.</p>
<p>On a system with mysql 4.1.14 and multiplyDBFieldSize not set I have the following field definitions:<br />ref_table -> <abbr title="40">VARCHAR</abbr><br />ref_string -> <abbr title="200">VARCHAR</abbr></p>
<p>On the failing system with mysql 5.0.27 and multiplyDBFieldSize set to 2:<br />ref_table -> <abbr title="80">VARCHAR</abbr><br />ref-string -> TEXT</p>
<p>Perhaps the TEXT datatype is the problem. See also Bugs 4652, 4680 (DAM)</p> TYPO3 Core - Bug #16801: sys_refindex failed to create keyhttp://forge.typo3.org/issues/16801?journal_id=435082006-12-21T11:15:33ZJoerg Schoppet
<ul></ul><p>I think I've found the problem:<br />Indexes in mysql have a max size of 1000 Bytes. If the db is utf8, one char needs three bytes</p>
<p>so the above statement will be:<br />80 + 400 = 480 * 3 = 1440 (Failure)</p>
<p>the ref_string have to be changed to e.g. 250<br />80 + 250 = 330 * 3 = 990</p>
<p>So the Statement have to be <br />ALTER TABLE sys_refindex ADD KEY lookup_string(ref_table, ref_string(250));</p>
<p>This works.</p> TYPO3 Core - Bug #16801: sys_refindex failed to create keyhttp://forge.typo3.org/issues/16801?journal_id=435092006-12-21T11:31:41ZMichael Stuckimichael.stucki@typo3.org
<ul></ul><p>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?</p>
<p>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.</p> TYPO3 Core - Bug #16801: sys_refindex failed to create keyhttp://forge.typo3.org/issues/16801?journal_id=435102006-12-21T11:44:31ZJoerg Schoppet
<ul></ul><p>Sorry for this, but I read the following in the Install tool for multiplyDBFieldSize:<br />... This is useful if you want to expand the size of fields for utf-8 ...</p>
<p>I thought I have to do it this way.</p>
<p>But ok.</p>
<p>After switching back to multiplyDBFieldSize = 1 and Compare the DB everything is ok now.</p> TYPO3 Core - Bug #16801: sys_refindex failed to create keyhttp://forge.typo3.org/issues/16801?journal_id=435112006-12-21T13:24:12ZMichael Stuckimichael.stucki@typo3.org
<ul></ul><p>OK, closing. For better clarification, I will slightly change the description of multiplyDBfieldSize.</p>