1

I'm using MySQL 5.7.2.23.

I need to copy some columns between two tables (u -> m) and I am using the following update command:

UPDATE m JOIN u ON u.id=m.user_id AND u.package_name=m.package_name SET m.host_version = u.host_version, m.device_model = u.device_model, ... m.device_name = u.device_name WHERE shard_id = 96

The UPDATE works

Both tables have keys on (id, package_name):

CREATE TABLE u ( id` varchar(255) COLLATE utf8_unicode_ci NOT NULL, package_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, device_model` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, device_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, host_version` int DEFAULT NULL, shard_id` int NOT NULL DEFAULT '0', device_group_id` int DEFAULT NULL, PRIMARY KEY (shard_id`,`package_name`,`id`), UNIQUE KEY by_id` (`id`,`package_name`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE TABLE m ( user_id varchar(255) COLLATE utf8_unicode_ci NOT NULL, package_name varchar(255) COLLATE utf8_unicode_ci NOT NULL, device_name varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, device_model varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, host_version int DEFAULT NULL, PRIMARY KEY (package_name,user_id), UNIQUE KEY by_id (user_id,package_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Innodb default row format is innodb_default_row_format=dynamic and Innodb innodb_large_prefix=ON.

And yet for some shardId-s I get the above error message (key too long).

Explain shows the following

id | 1 select_type | SIMPLE table | u partitions | <null> type | ref possible_keys | PRIMARY,by_id key | PRIMARY key_len | 4 ref | const rows | 5 filtered | 100.0 Extra | <null> id | 1 select_type | UPDATE table | m partitions | <null> type | eq_ref possible_keys | PRIMARY,by_id key | PRIMARY key_len | 1534 ref | activitydb.u.package_name,activitydb.u.id rows | 1 filtered | 100.0 Extra | <null>

I understand that the key is 1534 due to 3-byte UTF-8 encoding and changing the columns to latin1 reduces the key length and solves the problem.

What I don't understand is why MySQL complains only on some shard_id values. The shards that do work seem to have a very small number of rows.

And in general we use the above JOIN for many SELECT queries and MySQL does not complain about key length.

Improve this question
3
  • 1
    maybe its not the shard length, but the max length of the user_id/package_name within that shard. Commented Sep 18, 2018 at 5:09
  • @danblack: Doesn't seem that way. Tried adding a very long user_id/package_name to a small shard that is copied correctly - it works. Commented Sep 18, 2018 at 5:46
  • Could you share some pseudo data with us? (Pseudo-) records that work and other that don't seem to work? Edit it into your question and we'll see what we can do. Thank you. Commented Sep 25, 2018 at 14:33

2 Answers 2

Reset to default
1

(nimrodm's answer is better, but here are some workarounds)

  • VARCHAR(255) -- check the data; you probably do not need 255 in most fields.
  • COLLATE utf8_unicode_ci may be unnecessary for some of your columns. Consider CHARACTER SET ascii. utf8 takes 3 bytes toward the limit nimrodm mentioned; ascii takes only 1.
  • Consider normalizing those long strings. MEDIUMINT UNSIGNED, for example, is only 3 bytes (1..16M range) -- a lot less that 3*255.
Improve this answer
3
  • We did end up reducing the VARCHAR length and converting to latin1. I was afraid migrating the database will lock the tables for a long time but ultimately decided that a downtime of a few minutes is worth the potential gains in the future. Commented Oct 8, 2018 at 12:11
  • In the past I have seen references to MySQL allocating memory according to the maximum size of the VARCHAR field. So reducing field maximum size may improve performance in memory-limited cases. Is this still the case, with recent MySQL versions? Commented Oct 8, 2018 at 12:13
  • @nimrodm - (I'm glad you did the workarounds.) I think 8.0 has eliminated the issue, probably by doing something other than MEMORY for temp tables in complex SELECTs. Commented Oct 8, 2018 at 15:01
0

This behavior is new in MySql 5.7.23 and is due to this commit

MySQL creates a temporary table holding the keys of the updated table rows.This table is created with a fixed-size column which is limited to 1024 bytes in Innodb tables.

To see the latter restriction, try running 

CREATE TABLE zzz(kkk BINARY(256) PRIMARY KEY)

Mysql 5.7.23 returns an error: (1074, "Column length too big for column 'kkk' (max = 255); use BLOB or TEXT instead"))

I'm not sure why the maximum length here is 255 while for the temporary table it is 1024.

Improve this answer
4
  • I'm not sure if the bug introduced with the commit you are referencing is cause of your issue. Your primary key on both source and target tables doesn't seem to exceed the 1024 byte limit. But then again, the description might be misleading. Commented Sep 19, 2018 at 11:46
  • The BINARY() type is defined as: The BINARY type is similar to the CHAR type, but stores binary byte strings rather than nonbinary character strings. hence it has a restriction of BINARY(n - 255). You defined a BINARY(256) variable which will (naturally) result in an error message. Commented Sep 19, 2018 at 11:52
  • @hot2use. Thanks. I missed that part of the manual. So why does the commit message refers to "INNODB's fixed length column size is restricted to 1024"? Obviously varchar columns do not have this restriction. Commented Sep 19, 2018 at 12:01
  • At first I thought it might be an issue with the clustered index (automatically generated from primary key), but that doesn't seem to be the issue. I'll keep on looking. Commented Sep 19, 2018 at 14:16

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.