Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
10.4.19, 10.4(EOL), 10.5, 10.6, 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL)
Description
This runs just fine:
CREATE TABLE `test` ( |
`id` int unsigned NOT NULL, |
`foo` varchar(255) NOT NULL, |
PRIMARY KEY (`id`), |
UNIQUE KEY `foo_key` (`foo`) |
) ENGINE=MyISAM;
|
INSERT INTO `test` VALUES (1,'dummy'); |
However, when first disabling keys (commonly seen when using mysqldump) the INSERT query fails with an ERROR 1032 (HY000): Can't find record in 'test':
DROP TABLE IF EXISTS `test`; |
CREATE TABLE `test` ( |
`id` int unsigned NOT NULL, |
`foo` varchar(255) NOT NULL, |
PRIMARY KEY (`id`), |
UNIQUE KEY `foo_key` (`foo`) |
) ENGINE=MyISAM;
|
ALTER TABLE `test` DISABLE KEYS; |
INSERT INTO `test` VALUES (1,'dummy'); |
Character set being used is utf8mb4
If you use the ARIA storage engine, the CREATE statement will fail with ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes (varchar(255) in utf8mb4 is 1020 bytes).
If you use the InnoDB storage engine, there is no issue.
If you change the column foo to a varchar(250), it works fine as well.
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- is caused by
-
MDEV-371 Unique indexes for blobs
-
- Closed
-
Activity
Thanks for the report. Reproducible as described, yet another unique blob problem.
DROP TABLE IF EXISTS `test`; |
CREATE TABLE `test` ( |
`foo` varchar(255) NOT NULL, |
UNIQUE KEY `foo_key` (`foo`) |
) ENGINE=MyISAM CHARACTER SET utf8mb4; |
ALTER TABLE `test` DISABLE KEYS; |
INSERT INTO `test` VALUES ('dummy'); |
|
10.4 a48d2ec8 |
mysqltest: At line 8: query 'INSERT INTO `test` VALUES ('dummy')' failed: 1032: Can't find record in 'test' |
The resulting table structure:
SHOW CREATE TABLE `test`; |
Table Create Table |
test CREATE TABLE `test` ( |
`foo` varchar(255) NOT NULL, |
UNIQUE KEY `foo_key` (`foo`) USING HASH |
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 |
or, upon DDL,
CREATE TABLE t (f TEXT, UNIQUE(f)) ENGINE=MyISAM; |
INSERT INTO t VALUES ('x'); |
ALTER TABLE t DISABLE KEYS; |
ALTER TABLE t FORCE; |
|
|
# Cleanup
|
DROP TABLE t; |
Does not repro on 10.5.28 7b0f59da43b:
CREATE TABLE `test` ( |
`id` int unsigned NOT NULL, |
`foo` varchar(255) NOT NULL, |
PRIMARY KEY (`id`), |
UNIQUE KEY `foo_key` (`foo`) |
) ENGINE=MyISAM;
|
ALTER TABLE `test` DISABLE KEYS; |
INSERT INTO `test` VALUES (1,'dummy'); |
DROP TABLE IF EXISTS `test`; |
CREATE TABLE `test` ( |
`foo` varchar(255) NOT NULL, |
UNIQUE KEY `foo_key` (`foo`) |
) ENGINE=MyISAM CHARACTER SET utf8mb4; |
ALTER TABLE `test` DISABLE KEYS; |
INSERT INTO `test` VALUES ('dummy'); |
drop table test; |
Possible the same issue as
MDEV-25102