[Maria-discuss] 10.2.7 - Specified key was too long; max key length is 1000 bytes
what the hell is this when the MYISAM database has a utf8 charset while this don't happen with latin1_german1_ci - this seems to be a regression in 10.2 and wasted an hour of time to realize what is going on just because anotehr guy did not take care of the database charset
create table `cms1_formmailer_generic_cc` (`fg_key` mediumint(7) unsigned not null auto_increment, `fg_form` varchar(255) not null default '', `fg_email` varchar(255) not null default '', primary key(`fg_key`), key`fg_key` (`fg_form`, `fg_email`)) - Specified key was too long; max key length is 1000 bytes
On 7/31/2017 6:59 AM, Reindl Harald wrote:
what the hell is this when the MYISAM database has a utf8 charset while this don't happen with latin1_german1_ci - this seems to be a regression in 10.2 and wasted an hour of time to realize what is going on just because anotehr guy did not take care of the database charset
create table `cms1_formmailer_generic_cc` (`fg_key` mediumint(7) unsigned not null auto_increment, `fg_form` varchar(255) not null default '', `fg_email` varchar(255) not null default '', primary key(`fg_key`), key`fg_key` (`fg_form`, `fg_email`)) - Specified key was too long; max key length is 1000 bytes
A UTF8 character takes 3 bytes (or 4 bytes with utf8mb4). Your secondary key is 255 * 3 * 2 or 1530 bytes.
Brian
Am 31.07.2017 um 15:01 schrieb Brian Evans:
On 7/31/2017 6:59 AM, Reindl Harald wrote:
what the hell is this when the MYISAM database has a utf8 charset while this don't happen with latin1_german1_ci - this seems to be a regression in 10.2 and wasted an hour of time to realize what is going on just because anotehr guy did not take care of the database charset
create table `cms1_formmailer_generic_cc` (`fg_key` mediumint(7) unsigned not null auto_increment, `fg_form` varchar(255) not null default '', `fg_email` varchar(255) not null default '', primary key(`fg_key`), key`fg_key` (`fg_form`, `fg_email`)) - Specified key was too long; max key length is 1000 bytes
A UTF8 character takes 3 bytes (or 4 bytes with utf8mb4). Your secondary key is 255 * 3 * 2 or 1530 bytes
but since when has MyISAM such key limits?
i know from tests how the system behaves in InnoDB that there are horrible low limits for keys but not on MyISAM
On 7/31/2017 9:15 AM, Reindl Harald wrote:
Am 31.07.2017 um 15:01 schrieb Brian Evans:
On 7/31/2017 6:59 AM, Reindl Harald wrote:
what the hell is this when the MYISAM database has a utf8 charset while this don't happen with latin1_german1_ci - this seems to be a regression in 10.2 and wasted an hour of time to realize what is going on just because anotehr guy did not take care of the database charset
create table `cms1_formmailer_generic_cc` (`fg_key` mediumint(7) unsigned not null auto_increment, `fg_form` varchar(255) not null default '', `fg_email` varchar(255) not null default '', primary key(`fg_key`), key`fg_key` (`fg_form`, `fg_email`)) - Specified key was too long; max key length is 1000 bytes
A UTF8 character takes 3 bytes (or 4 bytes with utf8mb4). Your secondary key is 255 * 3 * 2 or 1530 bytes
but since when has MyISAM such key limits?
i know from tests how the system behaves in InnoDB that there are horrible low limits for keys but not on MyISAM
Even back to 3.23, MyISAM, and by extension Aria, has a hard coded index limit of 1000-bytes unless modified in the source code. Source: https://web.archive.org/web/20060110190745/http://dev.mysql.com:80/doc/refma...
FWIW, the above statement works perfectly in 10.2.7 on InnoDB.
Brian
participants (2)
-
Brian Evans -
Reindl Harald