MySQL - Index Creation Problem - Where is the difference?

Question

I have two instances of MySQL database, let's say: TST and DEV.

TST:

mysql --version
mysql  Ver 14.14 Distrib 5.6.40, for Linux (x86_64) using  EditLine wrapper

DEV:

mysql --version
mysql  Ver 14.14 Distrib 5.6.41, for Linux (x86_64) using  EditLine wrapper

Almost the same version on both environments.

Databases on both instances have the same schema and the same data.

Problematic table has structure as below:

CREATE TABLE `searchItem` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `dataType` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `value` varchar(1024) COLLATE utf8_unicode_ci NOT NULL,
  `creationTime` datetime NOT NULL,
  `modificationTime` datetime NOT NULL,
  `contentId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_36F9E76573A18A3B` (`contentId`),
  KEY `content_type` (`contentId`,`type`),
  CONSTRAINT `FK_36F9E76573A18A3B` FOREIGN KEY (`contentId`) REFERENCES `content` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23518 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

And now I want to add index on column value(1024). I know that in my case index size limit is 255.

On DEV:

CREATE INDEX value_type ON searchItem (value, type);
Query OK, 0 rows affected, 1 warning (0.30 sec)

And now I have:

CREATE TABLE `searchItem` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `dataType` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `value` varchar(1024) COLLATE utf8_unicode_ci NOT NULL,
  `creationTime` datetime NOT NULL,
  `modificationTime` datetime NOT NULL,
  `contentId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_36F9E76573A18A3B` (`contentId`),
  KEY `content_type` (`contentId`,`type`),
  KEY `value_type` (`value`(255),`type`),
  CONSTRAINT `FK_36F9E76573A18A3B` FOREIGN KEY (`contentId`) REFERENCES `content` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23518 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

so it automatically cut index to maximum size (255) and this is expected behavior for me.

but on TST:

CREATE INDEX value_type ON searchItem (value, type);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

I didn't find any differences in my.cnf file.

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[isamchk]
key_buffer_size = 16M

[mysqld]
basedir = /usr
bind-address = 0.0.0.0
character-set-server = utf8
datadir = /var/lib/mysql
default_storage_engine = InnoDB
expire_logs_days = 10
init-connect = SET NAMES utf8
innodb_file_per_table = 1
key_buffer_size = 16M
log-error = /var/log/mysql/error.log
max_allowed_packet = 128M
max_binlog_size = 100M
max_connections = 151
myisam_recover = BACKUP
pid-file = /var/run/mysqld/mysqld.pid
port = 3306
query_cache_limit = 1M
query_cache_size = 16M
skip-external-locking
socket = /var/run/mysqld/mysqld.sock
ssl = false
ssl-ca = /etc/mysql/cacert.pem
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem
thread_cache_size = 8
thread_stack = 256K
tmpdir = /tmp
user = mysql

[mysqld_safe]
log-error = /var/log/mysql/error.log
nice = 0
socket = /var/run/mysqld/mysqld.sock

[mysqldump]
max_allowed_packet = 128M
quick
quote-names

EDIT:

Essentials of the question:

On DEV:

CREATE INDEX value_type ON searchItem (value, type);
Query OK, 0 rows affected, 1 warning (0.30 sec)

but on TST:

CREATE INDEX value_type ON searchItem (value, type);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

The question is why it behaves differently on each environments? Is there any configuration parameter which can be responsible for this?

Possible duplicate of [#1071 - Specified key was too long; max key length is 767 bytes](https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes) — Martin Zeitler, Sep 19 '18 at 11:25

score 0 · Answer 1 · answered Sep 19 '18 at 11:39

0

What type of Engine do you use? 767 bytes is the stated prefix limitation for InnoDB tables in MySQL version 5.6 (and below). It's 999 (or 1000, don't remember exactly) bytes long for MyISAM tables

You can either switch engine type or upgrade to MySQL 5.7 which raises limitation to 3K

answered Sep 19 '18 at 11:39

Anton

919
7
22

I use InnoDB. And I know that there is limitation and it's different for InnoDB and MyISAM. The question was: why does it behave differently for each instance (on one it automatically sets maximum size of index and on second it throws error. I think that there is some configuration difference. – jkarkoszka Sep 19 '18 at 11:47
I found following in https://dev.mysql.com/doc/refman/8.0/en/innodb-restrictions.html - If you reduce the InnoDB page size to 8KB or 4KB by specifying the innodb_page_size option when creating the MySQL instance, the maximum length of the index key is lowered proportionally, based on the limit of 3072 bytes for a 16KB page size. That is, the maximum index key length is 1536 bytes when the page size is 8KB, and 768 bytes when the page size is 4KB. So check innodb_page_size variable – Anton Sep 19 '18 at 13:04

score 0 · Accepted Answer · answered Sep 19 '18 at 13:53

This is an effect of the Strict SQL Mode:

As of MySQL 5.6.11, strict mode produces an error for attempts to create a key that exceeds the maximum key length. Previously, this resulted in a warning and truncation of the key to the maximum key length (the same as when strict mode is not enabled).

Without strict mode, MySQL is allowed to adjust values to make them fit, and then reduces the error to a warning; this is the warning you got when you ran the statement.

You can check your current sql mode with select @@sql_mode (the TST-server will probably include e.g. STRICT_TRANS_TABLES, while DEV doesn't). The setting can be changed during runtime both globally and per session, so someone or something might have changed it.

This is answer I was looking for! Thank you! – jkarkoszka Sep 19 '18 at 14:51 — jkarkoszka, Sep 19 '18 at 14:51

MySQL - Index Creation Problem - Where is the difference?

2 Answers2