| Bug #53588 | Blackhole : Specified key was too long; max key length is 1000 bytes | ||
|---|---|---|---|
| Submitted: | 11 May 2010 22:59 | Modified: | 16 Jan 2018 14:51 |
| Reporter: | David Feymen | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.5/5.6/5.7/8.0 | OS: | Linux (Ubuntu, Synology OS) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | 1000 bytes, blackhole, INDEX, key, LENGTH, MAX | ||
[12 May 2010 3:45]
Valeriy Kravchuk
If your master server allows to create table with these columns and indexes, but different storage engine, then it has different charset (not utf8) defined. This is a problem to solve. Please, check.
[12 Jun 2010 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[9 Oct 2017 9:46]
Sveta Smirnova
This is hardcoded for Blackhole storage engine and I do not see the reason for this limitation: /* The following defines can be increased if necessary */ #define BLACKHOLE_MAX_KEY 64 /* Max allowed keys */ #define BLACKHOLE_MAX_KEY_SEG 16 /* Max segments for key */ #define BLACKHOLE_MAX_KEY_LENGTH 1000
[18 Nov 2017 7:19]
Nikolai Ikhalainen
It looks like this bug is not awaiting for any feedback from reporter, but the issue is still actual for 5.6,5.7,8.0
With innodb_large_prefix=1 blackhole storage engine is incompatible with innodb table definitions,
thus adding new indexes breaking slave.
mysqltest: At line 3: query 'ALTER TABLE t ADD KEY (c)' failed: 1071: Specified key was too long; max key length is 1000 bytes
ihanick@dev percona-server]$ git diff
diff --git a/storage/blackhole/ha_blackhole.h b/storage/blackhole/ha_blackhole.h
index df10e7d..e265ccf 100644
--- a/storage/blackhole/ha_blackhole.h
+++ b/storage/blackhole/ha_blackhole.h
@@ -66,7 +66,7 @@ public:
/* The following defines can be increased if necessary */
#define BLACKHOLE_MAX_KEY 64 /* Max allowed keys */
#define BLACKHOLE_MAX_KEY_SEG 16 /* Max segments for key */
-#define BLACKHOLE_MAX_KEY_LENGTH 1000
+#define BLACKHOLE_MAX_KEY_LENGTH 10000
uint max_supported_keys() const { return BLACKHOLE_MAX_KEY; }
uint max_supported_key_length() const { return BLACKHOLE_MAX_KEY_LENGTH; }
uint max_supported_key_part_length() const { return BLACKHOLE_MAX_KEY_LENGTH; }
[ihanick@dev percona-server]$ cat mysql-test/t/blackhole_max_key_len.test
--source include/have_blackhole.inc
CREATE TABLE t(c varchar(255)) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ENGINE=blackhole;
ALTER TABLE t ADD KEY (c);
DROP TABLE t;[ihanick@dev percona-server]$ cat mysql-test/r/blackhole_max_key_len.result
CREATE TABLE t(c varchar(255)) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ENGINE=blackhole;
ALTER TABLE t ADD KEY (c);
DROP TABLE t;
ihanick@dev percona-server]$ cat mysql-test/r/blackhole_max_key_len.result
CREATE TABLE t(c varchar(255)) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ENGINE=blackhole;
ALTER TABLE t ADD KEY (c);
DROP TABLE t;
[11 Dec 2017 5:01]
Laurynas Biveinis
Can this be reopened based on Nickolay's comment?
[16 Jan 2018 14:08]
Laurynas Biveinis
See bug 89265 for a contributed fix
[16 Jan 2018 14:49]
MySQL Verification Team
results for 5.5/5.6/5.7/8.0
Attachment: 53588.txt (text/plain), 4.18 KiB.
[16 Jan 2018 14:51]
MySQL Verification Team
Thank you for the bug report and feedback.
[2 Feb 2018 7:36]
Laurynas Biveinis
Bug 53588 fix for 8.0.4 by Vlad Lesin (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug53588-8.0.4.patch (application/octet-stream, text), 3.43 KiB.
[13 Jun 2018 12:34]
Laurynas Biveinis
Bug 53588 fix for 8.0.11 by Vlad Lesin
Attachment: bug53588-8.0.11.patch (application/octet-stream, text), 3.33 KiB.
[12 Dec 2018 16:49]
Thomas Benkert
This does not work yet with 8.0.12 I tried with this table: CREATE TABLE `interfaces_data_ware_house_datacheck_page` ( `internalFileName` varchar(255) NOT NULL, `pageName` varchar(255) NOT NULL, PRIMARY KEY (`internalFileName`,`pageName`) ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8;
[16 Apr 2021 7:39]
MySQL Verification Team
Bug #103336 marked as duplicate of this one.
Description: When creating the following table, the database returns an error : {{{ ERROR 1071 (42000) at line 102: Specified key was too long; max key length is 1000 bytes }}} It seems to be that since BLACKHOLE always returns nothing, this operation should be allowed. How to repeat: CREATE TABLE `aliases` ( `id` int(10) unsigned NOT NULL auto_increment, `username` varchar(64) NOT NULL default '', `domain` varchar(64) default NULL, `contact` varchar(255) NOT NULL default '', PRIMARY KEY (`id`), KEY `alias_idx` (`username`,`domain`,`contact`) ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8; Suggested fix: Indexes should be ignored on BLACKHOLE tables except for bin logging.