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.

  • 1
    maybe its not the shard length, but the max length of the user_id/package_name within that shard. – danblack Sep 18 '18 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. – nimrodm Sep 18 '18 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. – John aka hot2use Sep 25 '18 at 14:33
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.
  • 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. – nimrodm Oct 8 '18 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? – nimrodm Oct 8 '18 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. – Rick James Oct 8 '18 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.

  • 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. – John aka hot2use Sep 19 '18 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. – John aka hot2use Sep 19 '18 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. – nimrodm Sep 19 '18 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. – John aka hot2use Sep 19 '18 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.