| Bug #77554 | alter table ... CONVERT TO CHARACTER SET not effect and remain a temp table | ||
|---|---|---|---|
| Submitted: | 30 Jun 2015 2:33 | Modified: | 16 Jan 2016 14:45 |
| Reporter: | zhang yingqiang (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.6.16 5.7.7, 5.6.27, 5.7.8 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | character; temporary table | ||
[30 Jun 2015 2:36]
zhang yingqiang
One mistake When we execute sql “alter table t1 convert to character set XXX”, while table t1 does not have any column like varchar/text/...
[30 Jun 2015 5:07]
MySQL Verification Team
Hello Zhang Yingqiang, Thank you for the report and test case. Observed this with 5.6.27/5.7.8 builds. Thanks, Umesh
[30 Jun 2015 5:08]
MySQL Verification Team
Also, please note that in order to submit contributions you must first sign the Oracle Contribution Agreement (OCA). For additional information please check http://www.oracle.com/technetwork/community/oca-486395.html. If you have any questions, please contact the MySQL community team.
[30 Jun 2015 5:08]
MySQL Verification Team
// 5.6.27 [umshastr@hod03]/export/umesh/server/binaries/mysql-5.6.27/mysql-test: ./mtr 77554 Logging: ./mtr 77554 2015-06-30 06:59:53 0 [Note] /export/umesh/server/binaries/mysql-5.6.27/bin/mysqld (mysqld 5.6.27-enterprise-commercial-advanced) starting as process 8695 ... 2015-06-30 06:59:53 8695 [Note] Plugin 'FEDERATED' is disabled. 2015-06-30 06:59:53 8695 [Note] Binlog end 2015-06-30 06:59:53 8695 [Note] Shutting down plugin 'CSV' 2015-06-30 06:59:53 8695 [Note] Shutting down plugin 'MyISAM' MySQL Version 5.6.27 Checking supported features... - SSL connections supported Collecting tests... Checking leftover processes... Removing old var directory... Creating var directory '/export/umesh/server/binaries/mysql-5.6.27/mysql-test/var'... Installing system database... ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 create table t1 (id int primary key) engine = innodb default charset gbk; alter table t1 CONVERT TO CHARACTER SET UTF8, ALGORITHM = INPLACE; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk -rw-rw---- 1 umshastr common 8556 Jun 30 07:59 #sql-2211_2.frm 1 drop table t1; main.77554 [ pass ] 13 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 0.013 of 5 seconds executing testcases Completed: All 1 tests were successful. // 5.7.8 [umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8/mysql-test: ./mtr 77554 Logging: ./mtr 77554 MySQL Version 5.7.8 Checking supported features... - SSL connections supported Collecting tests... Removing old var directory... Creating var directory '/export/umesh/server/binaries/mysql-5.7.8/mysql-test/var'... Installing system database... ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 create table t1 (id int primary key) engine = innodb default charset gbk; alter table t1 CONVERT TO CHARACTER SET UTF8, ALGORITHM = INPLACE; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk -rw-rw---- 1 umshastr common 8556 Jun 30 08:01 #sql-23cd_3.frm 1 drop table t1; main.77554 [ pass ] 15 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 0.015 of 7 seconds executing testcases Completed: All 1 tests were successful.
[16 Jan 2016 14:45]
Paul DuBois
Noted in 5.6.29, 5.7.11, 5.8.0 changelogs. ALTER TABLE ... CONVERT TO CHARACTER SET operations that used the INPLACE algorithm were ineffective if the table contained only numeric data types. Also, such operations failed to clean up their temporary .frm file.
Description: When we execute sql “alter table t1 convert to character set XXX”, while table t1 has any column like varchar/text/... The default charset of t1 is not change, and left a internal temporary table. In create_table_impl, mysql create the new (temporary) table with new default charset. But in fill_alter_inplace_info, mysql find out that there is no need to change any column type, so it stop the mysql_alter_table process, without changing the default charset and cleaning the temporary table. How to repeat: create table t1 (id int primary key) engine = innodb default charset gbk; alter table t1 CONVERT TO CHARACTER SET UTF8, ALGORITHM = INPLACE; show create table t1; let $test_dir = `select concat(variable_value, 'test/') from information_schema.global_variables where lower(variable_name) = 'datadir'`; --exec ls -trl $test_dir|egrep 'sql-.*.frm'|wc -l drop table t1; Suggested fix: $git diff diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 1ffc90a..542867e 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -6002,6 +6002,9 @@ static bool fill_alter_inplace_info(THD *thd, ha_alter_info->handler_flags|= Alter_inplace_info::ADD_INDEX; } + if (ha_alter_info->create_info->table_charset != NULL && ha_alter_info->handler_flags == 0) + ha_alter_info->handler_flags|= Alter_inplace_info::CHANGE_CREATE_OPTION; + DBUG_RETURN(false); }