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.
Best Answer
(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. ConsiderCHARACTER SET ascii
.utf8
takes 3 bytes toward the limit nimrodm mentioned;ascii
takes only 1.MEDIUMINT UNSIGNED
, for example, is only 3 bytes (1..16M range) -- a lot less that 3*255.