TYPO3 Core

What DMBS are you using? The field tag is a VARCHAR thus creating an index with a length of 320 characters looks a bit strange. I guess you have DBAL installed?

I have run a regular MySQL 5.0.32 from the debian etch. DBAL is not aktiv.
I thought the databases are created using the core TCA? As a user, I can not change anything.

These are the entries can not be eliminated:

Add fields
select/deselect all
ALTER TABLE cachingframework_cache_hash_tags ADD KEY cache_id (identifier);
ALTER TABLE sys_registry ADD UNIQUE entry_identifier (entry_namespace,entry_key(320));
ALTER TABLE cachingframework_cache_pages_tags ADD KEY cache_id (identifier);
ALTER TABLE cachingframework_cache_pagesection_tags ADD KEY cache_id (identifier);
ALTER TABLE tx_realurl_uniqalias ADD KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire);
Changing fields
select/deselect all
ALTER TABLE sys_refindex CHANGE tablename tablename text;
Current value: varchar(100) default ''
ALTER TABLE sys_refindex CHANGE ref_table ref_table text;
Current value: varchar(100) default ''

Tested on a website with a MySQL database and DBAL active:

- Dropped caching_* tables in MySQL
- Went to Install Tool > Database Analyzer > Compare
- 6 tables are selected in the Add tables section, namely cachingframework_cache_hash, cachingframework_cache_hash_tags, cachingframework_cache_pages, cachingframework_cache_pages_tags, cachingframework_cache_pagesection, cachingframework_cache_pagesection_tags. None of them contain a length restriction for an index. Typically, cachingframework_cache_hash_tags has

KEY cache_tag (tag)

and NOT

KEY cache_tag (tag(320))

as said in the description

- All went fine, tables are properly created

Tested again on a website with an Oracle database and (of course) DBAL active:

- Created mapping configuration to avoid identifier length restriction:

$TYPO3_CONF_VARS['EXTCONF']['dbal']['mapping'] = array(
'cachingframework_cache_hash' => array(
'mapTableName' => 'cf_cache_hash',
),
'cachingframework_cache_pages' => array(
'mapTableName' => 'cf_cache_pages',
),
'cachingframework_cache_hash_tags' => array(
'mapTableName' => 'cf_cache_hash_tags',
),
'cachingframework_cache_pages_tags' => array(
'mapTableName' => 'cf_cache_pages_tags',
),
'cachingframework_cache_pagesection' => array(
'mapTableName' => 'cf_cache_pagesection',
),
'cachingframework_cache_pagesection_tags' => array(
'mapTableName' => 'cf_cache_pagesection_tags',
),
);

- Dropped all cf_* tables in Oracle
- Went to Install Tool > Database Analyzer > Compare
- Same here: 6 tables are selected in the Add tables sections and are properly created except for some indexes but that is related to bug #16292 and has actually nothing to do with this bug report. Furthermore, I cannot confirm the length restriction of 320 or anything else in the CREATE TABLE statements.

Regarding the other "bug" when having a length restriction in an ALTER TABLE statement, this is related to bug #16292.

Tested without DBAL on a MySQL-only install of TYPO3. I cannot confirm the problem there either. I don't know why you have a restriction of 320 characters in your case. It seems to come from "nowhere"...

I just tried to upgrade our dev website from 4.2.10 to 4.3.0 and it seems we are having the same problem. We are using MySQL 5.0.77. DBAL isn't installed.

The MySQL Database replies with following error when trying to create the sys_registry table for example:
#14798 - BLOB/TEXT column 'entry_namespace' used in key specification without a key length

This is what the install tool want to do for the table mentioned above:

CREATE TABLE sys_registry (
uid int(11) unsigned NOT NULL auto_increment,
entry_namespace text NOT NULL,
entry_key text NOT NULL,
entry_value blob,
PRIMARY KEY (uid),
UNIQUE entry_identifier (entry_namespace,entry_key(256))
);

There are also the three tables, that are in the report above, listed for creation and some alteration too. All changing from a varchar to a text field type. Like this one:
ALTER TABLE sys_refindex CHANGE ref_table ref_table text;
Current value: varchar(80) default ''

I'm not sure if that helps finding/solving the problem but I found an article to the MySQL error here:
http://www.mydigitallife.info/2007/07/09/mysql-error-1170-42000-blobtext-column-used-in-key-specification-without-a-key-length/

This is exactly the problem I have, too. I'm not as fit in MySQL. Here are a few still have the error messages:

ALTER TABLE cachingframework_cache_hash_tags ADD KEY cache_id (identifier);
ALTER TABLE sys_registry ADD UNIQUE entry_identifier (entry_namespace,entry_key(320));
ALTER TABLE sys_refindex ADD KEY lookup_rec (tablename,recuid);
ALTER TABLE sys_refindex ADD KEY lookup_uid (ref_table,ref_uid);
ALTER TABLE cachingframework_cache_pages_tags ADD KEY cache_id (identifier);
ALTER TABLE cachingframework_cache_pagesection_tags ADD KEY cache_id (identifier);
ALTER TABLE tx_realurl_uniqalias ADD KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire);

Thanks for your help

Christian

I have a similar problem. After upgrade from 4.2.10 to 4.3.0 I get these messages in the database analyser after "Compare":

ALTER TABLE cachingframework_cache_hash DROP KEY cache_id;
ALTER TABLE cachingframework_cache_hash ADD KEY cache_id (identifier(256));
ALTER TABLE sys_refindex DROP KEY lookup_string;
ALTER TABLE sys_refindex ADD KEY lookup_string (ref_string(400));
ALTER TABLE cachingframework_cache_pages DROP KEY cache_id;
ALTER TABLE cachingframework_cache_pages ADD KEY cache_id (identifier(256));

"Write to database" doesn't work.
We use Mysql 5.1, the database is set to UTF-8. DBAL is not installed.

Thanks for help

Sascha

Same problem here. Our database is also set to UTF-8.

The problem also exists with other databases, like the tt_news 3.0.1.

Always get the mysql Error 1170

I have the same issue. I tried to upgrade from 4.2.9 to 4.3 using PHP 5.2.11 and MySQL 4.1.22

I downloaded the Typo3 4.3 package from the typo3.org website.

These are the tables that keep on showing in the install tool Database compare (I cannot get rid of them when clicking 'write to database'):

Add fields
ALTER TABLE sys_history ADD KEY recordident (tablename(510),recuid,tstamp);
Changing fields
select/deselect all
ALTER TABLE cache_hash ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE cachingframework_cache_hash ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE cache_imagesizes ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE sys_log ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE cache_pages ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE cache_pagesection ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE cachingframework_cache_pages ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE cachingframework_cache_pagesection ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE cache_typo3temp_log ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE cache_md5params ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE cache_treelist ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE fe_session_data ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE fe_sessions ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE index_phash ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE index_fulltext ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE index_rel ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE index_words ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE index_section ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE index_grlist ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE index_stat_search ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE index_stat_word ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE tx_realurl_pathcache ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE tx_realurl_chashcache ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE tx_realurl_urldecodecache ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE tx_realurl_urlencodecache ENGINE=InnoDB;
Current value: ENGINE=
ALTER TABLE sys_refindex CHANGE tablename tablename text;
Current value: varchar(80) default ''
ALTER TABLE sys_refindex CHANGE ref_table ref_table text;
Current value: varchar(80) default ''
ALTER TABLE sys_refindex DROP KEY lookup_string;
ALTER TABLE sys_refindex ADD KEY lookup_string (ref_string(400));
Add tables
select/deselect all
CREATE TABLE cachingframework_cache_hash_tags (
id int(11) unsigned NOT NULL auto_increment,
identifier text NOT NULL,
tag text NOT NULL,
PRIMARY KEY (id),
KEY cache_id (identifier),
KEY cache_tag (tag(256))
) ENGINE=InnoDB;
CREATE TABLE sys_registry (
uid int(11) unsigned NOT NULL auto_increment,
entry_namespace text NOT NULL,
entry_key text NOT NULL,
entry_value blob,
PRIMARY KEY (uid),
UNIQUE entry_identifier (entry_namespace,entry_key(256))
);
CREATE TABLE cachingframework_cache_pages_tags (
id int(11) unsigned NOT NULL auto_increment,
identifier text NOT NULL,
tag text NOT NULL,
PRIMARY KEY (id),
KEY cache_id (identifier),
KEY cache_tag (tag(256))
) ENGINE=InnoDB;
CREATE TABLE cachingframework_cache_pagesection_tags (
id int(11) unsigned NOT NULL auto_increment,
identifier text NOT NULL,
tag text NOT NULL,
PRIMARY KEY (id),
KEY cache_id (identifier),
KEY cache_tag (tag(256))
) ENGINE=InnoDB;

Is there any patch for this?

You are probably using

$TYPO3_CONF_VARS['SYS']['multiplyDBfieldSize']

somewhere. Thus TYPO3 makes the field to a bigger size (f.i. text instead of varchar(255). Those fields can not be used as keys without giving the key a length (see http://www.mydigitallife.info/2007/07/09/mysql-error-1170-42000-blobtext-column-used-in-key-specification-without-a-key-length/).

Take a look, if your database isn't utf-8 formatted yet. Then you dont need the multiplyDBfieldSize anymore.

Maybe the key-fields should be taken into account from the install tool, if multiplyDBfieldSize is being used?

Thanks for the info!
Our database is UTF-8 and ['SYS']['multiplyDBfieldSize'] was set to 2.
When removing the value the table creations and the alterations worked.

Thats it!
Our database was also set to UTF-8 and ['SYS']['multiplyDBfieldSize'] was set to 2. "ALTER TABLE" worked when i removed this value.
Thanks for your help Irene.

Yes its true.

A ['SYS']['multiplyDBfieldSize'] = 1 fixed this problem.

BUT dont forget that arabic and chinese sites still have this trouble. On this languages you need a ['SYS']['multiplyDBfieldSize'] = 2 or 3

Its still a bug. On 4.2.x this works fine

Christian

Hello,

I have set ['SYS']['multiplyDBfieldSize'] = 1

But I still keep getting errors when I compare the DB:

ERROR: Query could not be parsed: "SQL engine parse ERROR: Field type unknown in parseFieldDef()!: near "entry_identifier (entry_namespace,entry_key) "". Query: "ALTER TABLE sys_registry ADD UNIQUE entry_identifier (entry_namespace,entry_key);"

I get this error when I run:

Add fields
select/deselect all
ALTER TABLE cachingframework_cache_hash_tags ADD KEY cache_id (identifier);
ALTER TABLE sys_registry ADD UNIQUE entry_identifier (entry_namespace,entry_key);
ALTER TABLE sys_history ADD KEY recordident (tablename,recuid,tstamp);
ALTER TABLE cachingframework_cache_pages_tags ADD KEY cache_id (identifier);
ALTER TABLE cachingframework_cache_pagesection_tags ADD KEY cache_id (identifier);

I'm not sure if removing the index and setting it to 1 is/does the same.
In the configuration is pointed to this typo3 wiki entry:
http://wiki.typo3.org/index.php/UTF-8_support
All green boxes (at the bottom) say that when your database is set to UTF-8 you shouldn't use ['SYS']['multiplyDBfieldSize'] .
So I simply removed the digit out of the field.

Yes, multiplyDBfieldSize is an old way to deal with UTF8 character set without a real UTF8 DB. This should not be used anymore in any new install. I guess it only still makes sense for old install.

May this bug be closed?

Resolved, won't fix.

['SYS']['multiplyDBfieldSize'] is deprecated and shouldn't be used anymore.