Bug #17480
closed
TABLE sys_refindex definition not compatible with mysql < 4.1.2
0%
Description
The following table definition fails when creating Typo3 tables on old mysql versions which don't support more than 255 characters in key indexes :
CREATE TABLE sys_refindex (
hash varchar(64) NOT NULL default '',
tablename varchar(80) NOT NULL default '',
recuid int(11) NOT NULL default '0',
field varchar(80) NOT NULL default '',
flexpointer text NOT NULL,
softref_key varchar(60) NOT NULL default '',
softref_id varchar(80) NOT NULL default '',
sorting int(11) NOT NULL default '0',
deleted tinyint(1) NOT NULL default '0',
ref_table varchar(80) NOT NULL default '',
ref_uid int(11) NOT NULL default '0',
ref_string text NOT NULL,
PRIMARY KEY (hash),
KEY lookup_rec (tablename,recuid),
KEY lookup_uid (ref_table,ref_uid),
KEY lookup_string (ref_table,ref_string(400))
);
The last line should be replaced by :
KEY lookup_string (ref_table,ref_string(255))
and the table definition gets accepted by mysql, at least on my server (version 3.23.58)
BLOB and TEXT columns can be indexed, but only by using an index prefix
length. That is, only a leading part of uniform length of a BLOB or
TEXT can be used for the index. In MySQL 4.1.2, the prefix can be up to
1000 characters; in earlier versions, the limit is 255 characters.
Read this page for more information:
http://dev.mysql.com/doc/mysql/en/indexes.html
(issue imported from #M5987)
Updated by Georg Kühnberger over 16 years ago
Experienced the same with mysql 4.0.24 and T3 4.1.2;
temp. solution was to leave out the
KEY lookup_string (ref_table,ref_string(600))
and manually create the table via phpmyadmin;
Details see below.
TYPO3 - Version: 4.1.2
====================================================
mysql> STATUS;
--------------
mysql Ver 14.12 Distrib 5.0.32, for pc-linux-gnu (i386) using readline 4.3
Server version: 4.0.24_Debian-10sarge1
Protocol version: 10
Client characterset: latin1
Server characterset: latin1
TCP port: 3306
====================================================
- Installtool did not create TABLE sys_refindex and did not report any error, except that it wants to create the table;
- Trying to create the table with the original SQL-Statement (phpmyadmin or shell) resulted in the mysql-error:
#14743 - Incorrect sub part key. The used key part isn't a string, the used length is longer than the key part or the table handler doesn't support unique sub keys
- leaving out the
KEY lookup_string (ref_table,ref_string(600))
allowed the manual creation of the table.
ORIGINAL SQL TABLE-CREATION
---
CREATE TABLE sys_refindex (
hash varchar(96) NOT NULL default '',
tablename varchar(120) NOT NULL default '',
recuid int(11) NOT NULL default '0',
field varchar(120) NOT NULL default '',
flexpointer text NOT NULL,
softref_key varchar(90) NOT NULL default '',
softref_id varchar(120) NOT NULL default '',
sorting int(11) NOT NULL default '0',
deleted tinyint(1) NOT NULL default '0',
ref_table varchar(120) NOT NULL default '',
ref_uid int(11) NOT NULL default '0',
ref_string text NOT NULL,
PRIMARY KEY (hash),
KEY lookup_rec (tablename,recuid),
KEY lookup_uid (ref_table,ref_uid),
KEY lookup_string (ref_table,ref_string(600))
);
MODIFIED SQL TABLE-CREATION
---
CREATE TABLE sys_refindex (
hash varchar(96) NOT NULL default '',
tablename varchar(120) NOT NULL default '',
recuid int(11) NOT NULL default '0',
field varchar(120) NOT NULL default '',
flexpointer text NOT NULL,
softref_key varchar(90) NOT NULL default '',
softref_id varchar(120) NOT NULL default '',
sorting int(11) NOT NULL default '0',
deleted tinyint(1) NOT NULL default '0',
ref_table varchar(120) NOT NULL default '',
ref_uid int(11) NOT NULL default '0',
ref_string text NOT NULL,
PRIMARY KEY (hash),
KEY lookup_rec (tablename,recuid),
KEY lookup_uid (ref_table,ref_uid)
);
Updated by Michael Stucki over 16 years ago
Hmm. For now I would say that this is not worth to be fixed. The error occurs only if multiplyDBfieldSize is used, and we are going to get rid off this anyway. So consider upgrading your MySQL server and make it ready for UTF-8, so that you don't need the multiplyDBfieldSize feature any more.
Alternatively you can just copy&edit the query and fix it accordingly.
- michael
Updated by Georg Kühnberger over 16 years ago
michael, thanks for the comment.
general ACK; suggest to close the bug (closed/wont be solved), with workaround mentioned.
btw: "upgrading MySQL" in this case (as too often) unfortunately was no option (inflexible 3rd party-hoster).
- g
Updated by Christian Kuhn about 15 years ago
Resolved, won't fix:
- Fix / upgrade mysql
- $GLOBALS['TYPO3_CONF_VARS']['SYS']['multiplyDBfieldSize'] is deprecated
More reading:
- Search for "multiplyDBfieldSize" in this bugtracker (with view status "any")
- http://wiki.typo3.org/index.php/UTF-8_support
- http://lists.netfielders.de/pipermail/typo3-team-core/2006-March/003739.html
Updated by Michael Stucki about 15 years ago
Reading this, I'll need to precise it. The bug was actually fixed by me some time ago. See Trunk@1709.
- michael
Updated by