| Bug #78119 | an error for auto_increment online ddl | ||
|---|---|---|---|
| Submitted: | 18 Aug 2015 7:30 | Modified: | 20 Aug 2015 14:20 |
| Reporter: | fan zhang | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S4 (Feature request) |
| Version: | 5.6.20 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | auto_increment, DDL, online | ||
[18 Aug 2015 10:32]
kevin tian
Hi Guys,
MySQL returns "Cannot change column type INPLACE"
it looks due to below code:
uint Field_num::is_equal(Create_field *new_field)
{
return ((new_field->sql_type == real_type()) &&
((new_field->flags & UNSIGNED_FLAG) ==
(uint) (flags & UNSIGNED_FLAG)) &&
((new_field->flags & AUTO_INCREMENT_FLAG) ==
(uint) (flags & AUTO_INCREMENT_FLAG)) &&
(new_field->pack_length == pack_length()));
}
why auto_incr could affect column type? is it expected behavior?
thanks.
[18 Aug 2015 10:58]
fan zhang
make my question more specific
[18 Aug 2015 14:56]
MySQL Verification Team
Thank you for your bug report. However, this is not a bug. Please read our manual more carefully next time. When you specify a target column, you must provide its full definition. Hence, this works like a charm: alter table sbtest3 change id2 id2 int(11) NOT NULL AUTO_INCREMENT after id,algorithm=inplace,lock=none; Our ALTER command can not work INPLACE when you change a column type and you have omitted AUTO_INCREMENT.
[19 Aug 2015 1:43]
fan zhang
thank you for your answer, but I was told changing(or omitting) auto_increment is not changing column type.
[19 Aug 2015 3:09]
kevin tian
Hi Sinisa, Thanks for your quick response! Fan might use wrong category -- should be "improvement" if we just remove auto_incr property for a column, it looks possible to do it online? ---the existing table data is not affected at all, and the auto_incr stuff is only known in write process, so it could be pure meta-data change only? thanks.
[20 Aug 2015 3:13]
fan zhang
need to be resolved
[20 Aug 2015 3:20]
fan zhang
change category from "bug" to "ddl"
[20 Aug 2015 14:20]
MySQL Verification Team
auto_increment is a very, very important attribute that changes column sub-type. Similar as it is for some character set changes for CHAR-type derived columns. But, let us follow manual implications, where it says: " Alterations that modify only table metadata and not table data are immediate because the server only needs to alter the table .frm file, not touch table contents. " AFAIK, I do not see a need to change existing table contents when AUTO_INCREMENT is removed. Your ALTER command, actually removes AUTO_INCREMENT, but I see no need to touch table contents for that. Still, it is a verified feature request. A nice feature request. And there is already a bug, # 72109, that precedes this one. Duplicate of 72109.
[20 Aug 2015 17:52]
MySQL Verification Team
Actually, I have to add something. The rules outlined in that excerpt from manual must be applied. Hence, this feature cannot be implemented in MyISAM, because it keeps auto-increment attribute in data files, actually in .MYI. InnoDB does not keep the attribute in the data files, so, there, it could be implemented. However, this could change, since there is one bug that might require InnoDB data files also keep this attribute. Hence, it is uncertain, yet, that this feature might be implemented.
[21 Aug 2015 7:12]
kevin tian
Hi Sinisa, Thanks for your explain. yes, I forgot to mention the storage engine refers to InnoDB. it looks not a good idea to ask innodb keep the attribute..since auto_incr is a runtime behavior, better loosely coupled with underlying data and use its own control structure.. Could you point me the bug that "might require InnoDB data files also keep this attribute"? thanks.
Description: mysql> show create table sbtest3; +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | sbtest3 | CREATE TABLE `sbtest3` ( `id` int(11) NOT NULL DEFAULT '0', `id2` int(11) NOT NULL AUTO_INCREMENT, `class_time2` int(11) DEFAULT NULL, `class_time` int(11) DEFAULT NULL, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL, `pad` char(60) NOT NULL, `class_hour` datetime DEFAULT NULL, `class_property` set('A类','B类','C类') DEFAULT NULL, `college_id` int(11) DEFAULT NULL, `class_time3` datetime DEFAULT NULL, `class_time4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) KEY_BLOCK_SIZE=1024, UNIQUE KEY `id2` (`id2`), KEY `combine_index` (`c`(5),`pad`(5)) USING HASH KEY_BLOCK_SIZE=100, KEY `foreign_key_of_sbtest3_from_college` (`college_id`) ) ENGINE=InnoDB AUTO_INCREMENT=423449 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=10 COMMENT='table sbtest3 展示' | +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table sbtest3 change id2 id2 int after id,algorithm=inplace,lock=none; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. mysql> How to repeat: mysql> alter table sbtest3 change id2 id2 int after id,algorithm=inplace,lock=none; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. mysql> Suggested fix: none