Bug #23973
closedCompare produces wrong SQL in DB Analyser: MySQL Error 1170: BLOB/TEXT Column used in Key Specification without a key length
0%
Description
I tried to use utf8 and set
[SYS][multiplyDBfieldSize]=2
running Compare leads to
ALTER TABLE sys_registry ADD UNIQUE entry_identifier (entry_namespace,entry_key(256));
Applying this directly to phpMyAdmin SQL interface results in
#14798 - BLOB/TEXT column 'entry_namespace' used in key specification without a key length
ALTER TABLE cachingframework_cache_hash_tags CHANGE identifier identifier text;
ALTER TABLE sys_refindex CHANGE tablename tablename text;
ALTER TABLE cachingframework_cache_pages_tags CHANGE identifier identifier text;
ALTER TABLE cachingframework_cache_pagesection_tags CHANGE identifier identifier text;
and so on, changing varchar to text leads to trouble with associated key spec.
Compare x times
XAMPP (Basis Package) version 1.7.3
Windows7
I tried
ALTER TABLE sys_registry ADD UNIQUE entry_identifier (entry_namespace(256),entry_key(256))
ALTER TABLE `typo344`.`cachingframework_cache_hash_tags` DROP INDEX `cache_id`;
ALTER TABLE cachingframework_cache_hash_tags CHANGE identifier identifier text
ALTER TABLE `typo344`.`cachingframework_cache_hash_tags` ADD INDEX `cache_id` ( `identifier` ( 256 ) );
ALTER TABLE sys_refindex CHANGE tablename tablename varchar510;
... ???
(issue imported from #M16286)
Updated by Steffen Gebert about 14 years ago
And does it work without multiplyDBfieldSize=2?
I don't know a case, where this setting is needed anymore, think we should remove it.
Updated by Johanna about 14 years ago
I don't know whether I'll run into trouble with multiplyDBfieldSize=1 using utf8 and German language? I was told to use 2 with utf8 and so I did.
Reverting to multiplyDBfieldSize=1 didn't seem to produce wrong SQL code
Updated by Chris topher about 14 years ago
$TYPO3_CONF_VARS['SYS']['multiplyDBfieldSize'] is only needed if your database charset is different from the charset which you store in it, e.g. if the DB charset is not UTF-8, but the content is UTF-8. If you use UTF-8 everywhere, it's not needed and only wastes space.
Updated by Christian Kuhn almost 14 years ago
Resolved, no change required:
[SYS][multiplyDBfieldSize]=2 is deprecated since a long time, it leads to strange errors like the one you run into. Do not use it.