[MDEV-9672] Incorrect error when dropping and adding back a column that is indexed Created: 2016-03-02  Updated: 2016-03-02

Status: Confirmed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.0.24, 5.5, 10.0, 10.1
Fix Version/s: 10.1

Type: Bug Priority: Minor
Reporter: Will Bryant Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: upstream


 Description   

Dropping and adding a column that is included in an index fails if this is done in a single ALTER statement, even though it works fine if it's first dropped in one ALTER and then added back in a second ALTER statement.

Additionally, the error message is inapplicable.

Steps to reproduce:

DROP TABLE IF EXISTS `example`;
 
 
 
CREATE TABLE `example` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `a` int(11) NOT NULL,
 
  `b` datetime DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `c` (`a`,`b`) USING BTREE
 
) ENGINE=InnoDB;
 
 
 
ALTER TABLE example DROP `b`, ADD `b` datetime;

produces

ERROR 1089 (HY000) at line 12: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys



 Comments   
Comment by Elena Stepanova [ 2016-03-02 ]

Actually, I'm not getting the error with the provided test case, but doing the same with `a` column instead does the trick:

MariaDB [test]> ALTER TABLE example DROP `a`, ADD `a` datetime;
 
ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

Comment by Elena Stepanova [ 2016-03-02 ]

This is an upstream bug https://bugs.mysql.com/bug.php?id=57497, reproducible up to and including 5.7. as of 5.7.10.

Generated at Thu Feb 08 07:36:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.