Specified key was too long; max key length is 1000 bytes

Question

I'm currently moving my concrete5.7 setup from localhost to a live server. First thing I did was export the SQL database and import it on the server, however this is giving me an error:

Error
SQL query:

-- --------------------------------------------------------
--
-- Tabelstructuur voor tabel `config`
--
CREATE TABLE IF NOT EXISTS  `config` (

 `configNamespace` VARCHAR( 255 ) COLLATE utf8_unicode_ci NOT NULL DEFAULT  '',
 `configGroup` VARCHAR( 255 ) COLLATE utf8_unicode_ci NOT NULL ,
 `configItem` VARCHAR( 255 ) COLLATE utf8_unicode_ci NOT NULL ,
 `configValue` LONGTEXT COLLATE utf8_unicode_ci,
PRIMARY KEY (  `configNamespace` ,  `configGroup` ,  `configItem` ) ,
KEY  `configGroup` (  `configGroup` )
) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;

MySQL said: 

#1071 - Specified key was too long; max key length is 1000 bytes

I'm aware that the key is too long but is there any freedom to change? I've tried changing the database collation to latin1 but it did not work.

What settings can I change?

Thank you for any feedback

score 0 · Answer 1 · answered Dec 05 '14 at 04:36

That is a poor choice of primary key - you do not need 1000 bytes of entropy to make a row unique If there is no natural choice of primary key, you can generate a surrogate key for the row.

e.g. replace

 PRIMARY KEY (  `configNamespace` ,  `configGroup` ,  `configItem` ) ,

with

 configId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

This should not be a breaking change for an application which uses the above.

score 0 · Answer 2 · answered Dec 05 '14 at 04:52

I would recommend the following structure:

CREATE TABLE IF NOT EXISTS  `config` (
    configId int not null auto_increment primary key,
    configNamespace VARCHAR( 255 ) COLLATE utf8_unicode_ci NOT NULL DEFAULT  '',
    configGroup VARCHAR( 255 ) COLLATE utf8_unicode_ci NOT NULL ,
    configItem VARCHAR( 255 ) COLLATE utf8_unicode_ci NOT NULL ,
    configValue LONGTEXT COLLATE utf8_unicode_ci,
    UNIQUE (configNamespace, configGroup,  configItem) ,   
    KEY  `configGroup` (  `configGroup` )
) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;

This creates the auto-incremented primary key for the table. In addition, it keeps the unique constraint on the three columns previously used for the primary key.

Specified key was too long; max key length is 1000 bytes

2 Answers2