merge of "BUG# 13975227: ONLINE OPTIMIZE TABLE FOR INNODB TABLES"

revno: 5820
committer: Nisha Gopalakrishnan <nisha.gopalakrishnan@oracle.com>
branch nick: mysql-5.6-13975225
timestamp: Mon 2014-02-17 15:12:16 +0530
message:
  BUG# 13975227: ONLINE OPTIMIZE TABLE FOR INNODB TABLES

13 files changed, 431 insertions, 88 deletions

diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index 207f6166fe0..622023d0e7d 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -1461,24 +1461,6 @@ t2	CREATE TABLE `t2` (
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 DROP TABLE t2;
 DROP TABLE t1;
-#
-# Bug#11938817 ALTER BEHAVIOR DIFFERENT THEN DOCUMENTED
-#
-DROP TABLE IF EXISTS t1;
-CREATE TABLE t1(a INT) engine=innodb;
-INSERT INTO t1 VALUES (1), (2);
-# This should not do anything
-ALTER TABLE t1;
-affected rows: 0
-# Check that we rebuild the table
-ALTER TABLE t1 engine=innodb;
-affected rows: 2
-info: Records: 2  Duplicates: 0  Warnings: 0
-# This should also rebuild the table
-ALTER TABLE t1 FORCE;
-affected rows: 2
-info: Records: 2  Duplicates: 0  Warnings: 0
-DROP TABLE t1;
 # Bug#11748057 (formerly known as 34972): ALTER TABLE statement doesn't
 #                                         identify correct column name.
 #
@@ -1888,8 +1870,8 @@ ALTER TABLE tm1 DEFAULT CHARACTER SET utf8;
 affected rows: 2
 info: Records: 2  Duplicates: 0  Warnings: 0
 ALTER TABLE ti1 FORCE;
-affected rows: 2
-info: Records: 2  Duplicates: 0  Warnings: 0
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tm1 FORCE;
 affected rows: 2
 info: Records: 2  Duplicates: 0  Warnings: 0
diff --git a/mysql-test/r/innodb_mysql_sync.result b/mysql-test/r/innodb_mysql_sync.result
index 0fecadf6de5..130b2273120 100644
--- a/mysql-test/r/innodb_mysql_sync.result
+++ b/mysql-test/r/innodb_mysql_sync.result
@@ -359,3 +359,184 @@ Note	1831	Duplicate index 'i4' defined on the table 'test.t1'. This is deprecate
 SET DEBUG_SYNC= 'RESET';
 DROP TABLE t1;
 SET DEBUG_SYNC= 'RESET';
+#
+#BUG#13975225:ONLINE OPTIMIZE TABLE FOR INNODB TABLES
+#
+SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue';
+#Setting up INNODB table.
+CREATE TABLE t1(fld1 INT, fld2 INT, fld3 INT) ENGINE= INNODB;
+INSERT INTO t1 VALUES (155, 45, 55);
+#Concurrent INSERT, UPDATE, SELECT and DELETE is supported 
+#during OPTIMIZE TABLE operation for INNODB tables.
+connection default;
+#OPTIMIZE TABLE operation.
+OPTIMIZE TABLE t1;
+connection con1;
+SET DEBUG_SYNC= 'now WAIT_FOR downgraded';
+# With the patch, concurrent DML operation succeeds.
+INSERT INTO t1 VALUES (10, 11, 12);
+UPDATE t1 SET fld1= 20 WHERE fld1= 155;
+DELETE FROM t1 WHERE fld1= 20;
+SELECT * from t1;
+fld1	fld2	fld3
+10	11	12
+SET DEBUG_SYNC= 'now SIGNAL continue';
+connection default;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
+test.t1	optimize	status	OK
+DROP TABLE t1;
+SET DEBUG_SYNC= 'RESET';
+#Concurrent INSERT, UPDATE, SELECT and DELETE is supported
+#during OPTIMIZE TABLE operation for Partitioned table.
+SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue';
+#Setup PARTITIONED table.
+CREATE TABLE t1(fld1 INT) ENGINE= INNODB PARTITION BY HASH(fld1) PARTITIONS 4;
+INSERT INTO t1 VALUES(10);
+#OPTIMIZE TABLE operation.
+OPTIMIZE TABLE t1;
+connection con1;
+SET DEBUG_SYNC= 'now WAIT_FOR downgraded';
+# With the patch, concurrent DML operation succeeds.
+INSERT INTO t1 VALUES (30);
+UPDATE t1 SET fld1= 20 WHERE fld1= 10;
+DELETE FROM t1 WHERE fld1= 20;
+SELECT * from t1;
+fld1
+30
+SET DEBUG_SYNC= 'now SIGNAL continue';
+connection default;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
+test.t1	optimize	status	OK
+DROP TABLE t1;
+SET DEBUG_SYNC= 'RESET';
+#ALTER TABLE FORCE and ALTER TABLE ENGINE uses online rebuild
+#of the table.
+CREATE TABLE t1(fld1 INT, fld2 INT);
+INSERT INTO t1 VALUES(10, 20);
+ALTER TABLE t1 FORCE;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+ALTER TABLE t1 ENGINE=INNODB;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+#ALTER TABLE FORCE, ALTER TABLE ENGINE and OPTIMIZE TABLE uses
+#table copy when the old_alter_table enabled.
+SET SESSION old_alter_table= TRUE;
+affected rows: 0
+ALTER TABLE t1 FORCE;
+affected rows: 1
+info: Records: 1  Duplicates: 0  Warnings: 0
+ALTER TABLE t1 ENGINE= INNODB;
+affected rows: 1
+info: Records: 1  Duplicates: 0  Warnings: 0
+SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded';
+affected rows: 0
+#OPTIMIZE TABLE operation using table copy.
+OPTIMIZE TABLE t1;
+connection con1;
+SET DEBUG_SYNC= 'now WAIT_FOR upgraded';
+affected rows: 0
+INSERT INTO t1 VALUES(10, 20);
+affected rows: 1
+connection default;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
+test.t1	optimize	status	OK
+affected rows: 2
+SET DEBUG_SYNC= 'RESET';
+affected rows: 0
+SET SESSION old_alter_table= FALSE;
+affected rows: 0
+#ALTER TABLE FORCE and ALTER TABLE ENGINE uses table copy
+#when ALGORITHM COPY is used.
+ALTER TABLE t1 FORCE, ALGORITHM= COPY;
+affected rows: 2
+info: Records: 2  Duplicates: 0  Warnings: 0
+ALTER TABLE t1 ENGINE= INNODB, ALGORITHM= COPY;
+affected rows: 2
+info: Records: 2  Duplicates: 0  Warnings: 0
+DROP TABLE t1;
+#OPTIMIZE TABLE on a table with FULLTEXT index uses
+#ALTER TABLE FORCE using COPY algorithm here. This
+#test case ensures the COPY table debug sync point is hit.
+SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded';
+#Setup a table with FULLTEXT index.
+connection default;
+CREATE TABLE t1(fld1 CHAR(10), FULLTEXT(fld1)) ENGINE= INNODB;
+INSERT INTO t1 VALUES("String1");
+#OPTIMIZE TABLE operation.
+OPTIMIZE TABLE t1;
+connection con1;
+SET DEBUG_SYNC= 'now WAIT_FOR upgraded';
+INSERT INTO t1 VALUES("String2");
+connection default;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
+test.t1	optimize	status	OK
+SET DEBUG_SYNC= 'RESET';
+DROP TABLE t1;
+#Test which demonstrates that ALTER TABLE, OPTIMIZE PARTITION
+#takes OPTIMIZE TABLE code path, hence does an online rebuild
+#of the table with the patch. 
+connection default;
+SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue';
+#Setup PARTITIONED table.
+CREATE TABLE t1(fld1 INT) ENGINE= INNODB PARTITION BY HASH(fld1) PARTITIONS 4;
+INSERT INTO t1 VALUES(10);
+#OPTIMIZE ALL PARTITIONS operation.
+ALTER TABLE t1 OPTIMIZE PARTITION ALL;
+connection con1;
+SET DEBUG_SYNC= 'now WAIT_FOR downgraded';
+# With the patch, concurrent DML operation succeeds.
+INSERT INTO t1 VALUES (30);
+UPDATE t1 SET fld1= 20 WHERE fld1= 10;
+DELETE FROM t1 WHERE fld1= 20;
+SELECT * from t1;
+fld1
+30
+SET DEBUG_SYNC= 'now SIGNAL continue';
+connection default;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
+test.t1	optimize	status	OK
+SET DEBUG_SYNC= 'RESET';
+#OPTIMIZE PER PARTITION operation.
+SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue';
+ALTER TABLE t1 OPTIMIZE PARTITION p0;
+connection con1;
+SET DEBUG_SYNC= 'now WAIT_FOR downgraded';
+# With the patch, concurrent DML operation succeeds.
+INSERT INTO t1 VALUES (30);
+UPDATE t1 SET fld1= 20 WHERE fld1= 10;
+DELETE FROM t1 WHERE fld1= 20;
+SELECT * from t1;
+fld1
+30
+30
+SET DEBUG_SYNC= 'now SIGNAL continue';
+connection default;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
+test.t1	optimize	status	OK
+SET DEBUG_SYNC= 'RESET';
+# Test case for Bug#11938817 (ALTER BEHAVIOR DIFFERENT THEN DOCUMENTED).
+# This should not do anything
+ALTER TABLE t1;
+affected rows: 0
+SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuild';
+# Check that we rebuild the table
+ALTER TABLE t1 engine=innodb;
+connection con1;
+SET DEBUG_SYNC= 'now WAIT_FOR rebuild';
+connection default;
+SET DEBUG_SYNC= 'RESET';
+SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuild';
+# Check that we rebuild the table
+ALTER TABLE t1 FORCE;
+connection con1;
+SET DEBUG_SYNC= 'now WAIT_FOR rebuild';
+connection default;
+SET DEBUG_SYNC= 'RESET';
+DROP TABLE t1;
diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result
index 5abce8af9c9..ba9ab47ea95 100644
--- a/mysql-test/r/mysqlcheck.result
+++ b/mysql-test/r/mysqlcheck.result
@@ -297,10 +297,7 @@ CHECK TABLE bug47205 FOR UPGRADE;
 Table	Op	Msg_type	Msg_text
 test.bug47205	check	error	Table rebuild required. Please do "ALTER TABLE `bug47205` FORCE" or dump/reload to fix it!
 # ALTER TABLE ... FORCE should rebuild the table
-# and therefore output "affected rows: 1"
 ALTER TABLE bug47205 FORCE;
-affected rows: 1
-info: Records: 1  Duplicates: 0  Warnings: 0
 # Table should now be ok
 CHECK TABLE bug47205 FOR UPGRADE;
 Table	Op	Msg_type	Msg_text
diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result
index bc961ba1e46..f8020e080e6 100644
--- a/mysql-test/suite/innodb/r/innodb.result
+++ b/mysql-test/suite/innodb/r/innodb.result
@@ -1691,7 +1691,7 @@ variable_value - @innodb_rows_deleted_orig
 71
 SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted';
 variable_value - @innodb_rows_inserted_orig
-1007
+964
 SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated';
 variable_value - @innodb_rows_updated_orig
 866
diff --git a/mysql-test/suite/perfschema/r/innodb_table_io.result b/mysql-test/suite/perfschema/r/innodb_table_io.result
index 9b5bd783f88..460518aeb65 100644
--- a/mysql-test/suite/perfschema/r/innodb_table_io.result
+++ b/mysql-test/suite/perfschema/r/innodb_table_io.result
@@ -106,9 +106,6 @@ wait/io/table/sql/handler	handler.cc:	TABLE	test	no_index_tab	fetch	NULL
 wait/io/table/sql/handler	handler.cc:	TABLE	test	no_index_tab	fetch	NULL
 wait/io/table/sql/handler	handler.cc:	TABLE	test	no_index_tab	fetch	NULL
 wait/io/table/sql/handler	handler.cc:	TABLE	test	marker	insert	NULL
-wait/io/table/sql/handler	handler.cc:	TABLE	test	no_index_tab	fetch	NULL
-wait/io/table/sql/handler	handler.cc:	TABLE	test	no_index_tab	fetch	NULL
-wait/io/table/sql/handler	handler.cc:	TABLE	test	no_index_tab	fetch	NULL
 wait/io/table/sql/handler	handler.cc:	TABLE	test	marker	insert	NULL
 wait/io/table/sql/handler	handler.cc:	TABLE	test	no_index_tab	fetch	NULL
 wait/io/table/sql/handler	handler.cc:	TABLE	test	no_index_tab	fetch	NULL
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test
index 3050fc0378d..4aeabf5f349 100644
--- a/mysql-test/t/alter_table.test
+++ b/mysql-test/t/alter_table.test
@@ -1316,28 +1316,6 @@ SHOW CREATE TABLE t2;
 DROP TABLE t2;
 DROP TABLE t1;
 
---echo #
---echo # Bug#11938817 ALTER BEHAVIOR DIFFERENT THEN DOCUMENTED
---echo #
-
---disable_warnings
-DROP TABLE IF EXISTS t1;
---enable_warnings
-
-CREATE TABLE t1(a INT) engine=innodb;
-INSERT INTO t1 VALUES (1), (2);
-
---enable_info
---echo # This should not do anything
-ALTER TABLE t1;
---echo # Check that we rebuild the table
-ALTER TABLE t1 engine=innodb;
---echo # This should also rebuild the table
-ALTER TABLE t1 FORCE;
---disable_info
-
-DROP TABLE t1;
-
 --echo # Bug#11748057 (formerly known as 34972): ALTER TABLE statement doesn't
 --echo #                                         identify correct column name.
 --echo #
diff --git a/mysql-test/t/innodb_mysql_sync.test b/mysql-test/t/innodb_mysql_sync.test
index b1e21837404..6b8aa6d0b89 100644
--- a/mysql-test/t/innodb_mysql_sync.test
+++ b/mysql-test/t/innodb_mysql_sync.test
@@ -592,6 +592,205 @@ DROP TABLE t1;
 SET DEBUG_SYNC= 'RESET';
 
 
+--echo #
+--echo #BUG#13975225:ONLINE OPTIMIZE TABLE FOR INNODB TABLES
+--echo #
+
+SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue';
+connect(con1,localhost,root,,);
+
+--echo #Setting up INNODB table.
+CREATE TABLE t1(fld1 INT, fld2 INT, fld3 INT) ENGINE= INNODB;
+INSERT INTO t1 VALUES (155, 45, 55);
+
+--echo #Concurrent INSERT, UPDATE, SELECT and DELETE is supported 
+--echo #during OPTIMIZE TABLE operation for INNODB tables.
+--enable_connect_log
+--connection default
+--echo #OPTIMIZE TABLE operation.
+--send OPTIMIZE TABLE t1
+
+--connection con1
+SET DEBUG_SYNC= 'now WAIT_FOR downgraded';
+--echo # With the patch, concurrent DML operation succeeds.
+INSERT INTO t1 VALUES (10, 11, 12);
+UPDATE t1 SET fld1= 20 WHERE fld1= 155;
+DELETE FROM t1 WHERE fld1= 20;
+SELECT * from t1;
+SET DEBUG_SYNC= 'now SIGNAL continue';
+
+--connection default
+--reap
+DROP TABLE t1;
+SET DEBUG_SYNC= 'RESET';
+
+--echo #Concurrent INSERT, UPDATE, SELECT and DELETE is supported
+--echo #during OPTIMIZE TABLE operation for Partitioned table.
+
+SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue';
+--echo #Setup PARTITIONED table.
+CREATE TABLE t1(fld1 INT) ENGINE= INNODB PARTITION BY HASH(fld1) PARTITIONS 4;
+INSERT INTO t1 VALUES(10);
+
+--echo #OPTIMIZE TABLE operation.
+--send OPTIMIZE TABLE t1
+
+--connection con1
+SET DEBUG_SYNC= 'now WAIT_FOR downgraded';
+--echo # With the patch, concurrent DML operation succeeds.
+INSERT INTO t1 VALUES (30);
+UPDATE t1 SET fld1= 20 WHERE fld1= 10;
+DELETE FROM t1 WHERE fld1= 20;
+SELECT * from t1;
+SET DEBUG_SYNC= 'now SIGNAL continue';
+
+--connection default
+--reap
+DROP TABLE t1;
+SET DEBUG_SYNC= 'RESET';
+
+--echo #ALTER TABLE FORCE and ALTER TABLE ENGINE uses online rebuild
+--echo #of the table.
+
+CREATE TABLE t1(fld1 INT, fld2 INT);
+INSERT INTO t1 VALUES(10, 20);
+
+--enable_info
+ALTER TABLE t1 FORCE;
+ALTER TABLE t1 ENGINE=INNODB;
+
+--echo #ALTER TABLE FORCE, ALTER TABLE ENGINE and OPTIMIZE TABLE uses
+--echo #table copy when the old_alter_table enabled.
+SET SESSION old_alter_table= TRUE;
+ALTER TABLE t1 FORCE;
+ALTER TABLE t1 ENGINE= INNODB;
+
+SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded';
+--echo #OPTIMIZE TABLE operation using table copy.
+--send OPTIMIZE TABLE t1
+
+--connection con1
+SET DEBUG_SYNC= 'now WAIT_FOR upgraded';
+INSERT INTO t1 VALUES(10, 20);
+
+--connection default
+--reap
+SET DEBUG_SYNC= 'RESET';
+SET SESSION old_alter_table= FALSE;
+
+--echo #ALTER TABLE FORCE and ALTER TABLE ENGINE uses table copy
+--echo #when ALGORITHM COPY is used.
+ALTER TABLE t1 FORCE, ALGORITHM= COPY;
+ALTER TABLE t1 ENGINE= INNODB, ALGORITHM= COPY;
+--disable_info
+
+#cleanup
+DROP TABLE t1;
+
+--echo #OPTIMIZE TABLE on a table with FULLTEXT index uses
+--echo #ALTER TABLE FORCE using COPY algorithm here. This
+--echo #test case ensures the COPY table debug sync point is hit.
+
+SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded';
+
+--echo #Setup a table with FULLTEXT index.
+--connection default
+CREATE TABLE t1(fld1 CHAR(10), FULLTEXT(fld1)) ENGINE= INNODB;
+INSERT INTO t1 VALUES("String1");
+
+--echo #OPTIMIZE TABLE operation.
+--send OPTIMIZE TABLE t1
+
+--connection con1
+SET DEBUG_SYNC= 'now WAIT_FOR upgraded';
+INSERT INTO t1 VALUES("String2");
+
+--connection default
+--reap
+SET DEBUG_SYNC= 'RESET';
+DROP TABLE t1;
+
+--echo #Test which demonstrates that ALTER TABLE, OPTIMIZE PARTITION
+--echo #takes OPTIMIZE TABLE code path, hence does an online rebuild
+--echo #of the table with the patch. 
+
+--connection default
+SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue';
+--echo #Setup PARTITIONED table.
+CREATE TABLE t1(fld1 INT) ENGINE= INNODB PARTITION BY HASH(fld1) PARTITIONS 4;
+INSERT INTO t1 VALUES(10);
+
+--echo #OPTIMIZE ALL PARTITIONS operation.
+--send ALTER TABLE t1 OPTIMIZE PARTITION ALL
+
+--connection con1
+SET DEBUG_SYNC= 'now WAIT_FOR downgraded';
+--echo # With the patch, concurrent DML operation succeeds.
+INSERT INTO t1 VALUES (30);
+UPDATE t1 SET fld1= 20 WHERE fld1= 10;
+DELETE FROM t1 WHERE fld1= 20;
+SELECT * from t1;
+SET DEBUG_SYNC= 'now SIGNAL continue';
+
+--connection default
+--reap
+SET DEBUG_SYNC= 'RESET';
+
+--echo #OPTIMIZE PER PARTITION operation.
+SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue';
+--send ALTER TABLE t1 OPTIMIZE PARTITION p0
+
+--connection con1
+SET DEBUG_SYNC= 'now WAIT_FOR downgraded';
+--echo # With the patch, concurrent DML operation succeeds.
+INSERT INTO t1 VALUES (30);
+UPDATE t1 SET fld1= 20 WHERE fld1= 10;
+DELETE FROM t1 WHERE fld1= 20;
+SELECT * from t1;
+SET DEBUG_SYNC= 'now SIGNAL continue';
+
+--connection default
+--reap
+SET DEBUG_SYNC= 'RESET';
+
+--echo # Test case for Bug#11938817 (ALTER BEHAVIOR DIFFERENT THEN DOCUMENTED).
+--enable_info
+--echo # This should not do anything
+ALTER TABLE t1;
+--disable_info
+
+#Note that sync point is activated in the online rebuild code path.
+SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuild';
+
+--echo # Check that we rebuild the table
+--send ALTER TABLE t1 engine=innodb
+
+--connection con1
+SET DEBUG_SYNC= 'now WAIT_FOR rebuild';
+
+--connection default
+--reap
+
+SET DEBUG_SYNC= 'RESET';
+
+SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuild';
+
+--echo # Check that we rebuild the table
+--send ALTER TABLE t1 FORCE
+
+--connection con1
+SET DEBUG_SYNC= 'now WAIT_FOR rebuild';
+
+--connection default
+--reap
+
+--disable_connect_log
+--disconnect con1
+
+SET DEBUG_SYNC= 'RESET';
+DROP TABLE t1;
+
+
 # Check that all connections opened by test cases in this file are really
 # gone so execution of other tests won't be affected by their presence.
 --source include/wait_until_count_sessions.inc
diff --git a/mysql-test/t/mysqlcheck.test b/mysql-test/t/mysqlcheck.test
index d7dab675dc6..d986b42fc91 100644
--- a/mysql-test/t/mysqlcheck.test
+++ b/mysql-test/t/mysqlcheck.test
@@ -263,10 +263,7 @@ let $MYSQLD_DATADIR= `select @@datadir`;
 CHECK TABLE bug47205 FOR UPGRADE;
 
 --echo # ALTER TABLE ... FORCE should rebuild the table
---echo # and therefore output "affected rows: 1"
---enable_info
 ALTER TABLE bug47205 FORCE;
---disable_info
 
 --echo # Table should now be ok
 CHECK TABLE bug47205 FOR UPGRADE;
diff --git a/sql/handler.h b/sql/handler.h
index 0b8bd6e9ce6..f5c6c8550c0 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -1,8 +1,8 @@
 #ifndef HANDLER_INCLUDED
 #define HANDLER_INCLUDED
 /*
-   Copyright (c) 2000, 2011, Oracle and/or its affiliates.
-   Copyright (c) 2009, 2013, Monty Program Ab.
+   Copyright (c) 2000, 2014, Oracle and/or its affiliates.
+   Copyright (c) 2009, 2014, Monty Program Ab.
 
    This program is free software; you can redistribute it and/or
    modify it under the terms of the GNU General Public License
@@ -1677,8 +1677,7 @@ public:
      All these operations are supported as in-place operations by the
      SQL layer. This means that operations that by their nature must
      be performed by copying the table to a temporary table, will not
-     have their own flags here (e.g. ALTER TABLE FORCE, ALTER TABLE
-     ENGINE).
+     have their own flags here.
 
      We generally try to specify handler flags only if there are real
      changes. But in cases when it is cumbersome to determine if some
@@ -1782,8 +1781,14 @@ public:
   // Partition operation with ALL keyword
   static const HA_ALTER_FLAGS ALTER_ALL_PARTITION        = 1L << 28;
 
+  /**
+    Recreate the table for ALTER TABLE FORCE, ALTER TABLE ENGINE
+    and OPTIMIZE TABLE operations.
+  */
+  static const HA_ALTER_FLAGS RECREATE_TABLE             = 1L << 29;
+
   // Virtual columns changed
-  static const HA_ALTER_FLAGS ALTER_COLUMN_VCOL          = 1L << 29;
+  static const HA_ALTER_FLAGS ALTER_COLUMN_VCOL          = 1L << 30;
 
   /**
     Create options (like MAX_ROWS) for the new version of table.
diff --git a/sql/sql_admin.cc b/sql/sql_admin.cc
index 41808bc2717..b3f53e3457a 100644
--- a/sql/sql_admin.cc
+++ b/sql/sql_admin.cc
@@ -1,5 +1,5 @@
-/* Copyright (c) 2010, 2013, Oracle and/or its affiliates.
-   Copyright (c) 2012, 2013, Monty Program Ab.
+/* Copyright (c) 2010, 2014, Oracle and/or its affiliates.
+   Copyright (c) 2012, 2014, Monty Program Ab.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -55,7 +55,7 @@ static bool admin_recreate_table(THD *thd, TABLE_LIST *table_list)
   DEBUG_SYNC(thd, "ha_admin_try_alter");
   tmp_disable_binlog(thd); // binlogging is done by caller if wanted
   result_code= (open_temporary_tables(thd, table_list) ||
-                mysql_recreate_table(thd, table_list));
+                mysql_recreate_table(thd, table_list, false));
   reenable_binlog(thd);
   /*
     mysql_recreate_table() can push OK or ERROR.
@@ -1196,7 +1196,7 @@ bool Sql_cmd_optimize_table::execute(THD *thd)
     goto error; /* purecov: inspected */
   thd->enable_slow_log= opt_log_slow_admin_statements;
   res= (specialflag & SPECIAL_NO_NEW_FUNC) ?
-    mysql_recreate_table(thd, first_table) :
+    mysql_recreate_table(thd, first_table, true) :
     mysql_admin_table(thd, first_table, &m_lex->check_opt,
                       "optimize", TL_WRITE, 1, 0, 0, 0,
                       &handler::ha_optimize, 0);
diff --git a/sql/sql_alter.h b/sql/sql_alter.h
index f0c0a873a5c..526442e83e2 100644
--- a/sql/sql_alter.h
+++ b/sql/sql_alter.h
@@ -1,4 +1,5 @@
-/* Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2010, 2014, Oracle and/or its affiliates.
+   Copyright (c) 2013, 2014, Monty Program Ab.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -73,6 +74,7 @@ public:
   static const uint ALTER_CONVERT               = 1L << 10;
 
   // Set for FORCE
+  // Set for ENGINE(same engine)
   // Set by mysql_recreate_table()
   static const uint ALTER_RECREATE              = 1L << 11;
 
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 502f1b30e8f..8af77d929ea 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -1,6 +1,6 @@
 /*
-   Copyright (c) 2000, 2013, Oracle and/or its affiliates.
-   Copyright (c) 2010, 2013, Monty Program Ab.
+   Copyright (c) 2000, 2014, Oracle and/or its affiliates.
+   Copyright (c) 2010, 2014, Monty Program Ab.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -6051,6 +6051,9 @@ static bool fill_alter_inplace_info(THD *thd,
     ha_alter_info->handler_flags|= Alter_inplace_info::ALTER_REMOVE_PARTITIONING;
   if (alter_info->flags & Alter_info::ALTER_ALL_PARTITION)
     ha_alter_info->handler_flags|= Alter_inplace_info::ALTER_ALL_PARTITION;
+  /* Check for: ALTER TABLE FORCE, ALTER TABLE ENGINE and OPTIMIZE TABLE. */
+  if (alter_info->flags & Alter_info::ALTER_RECREATE)
+    ha_alter_info->handler_flags|= Alter_inplace_info::RECREATE_TABLE;
 
   /*
     If we altering table with old VARCHAR fields we will be automatically
@@ -6734,12 +6737,8 @@ static bool is_inplace_alter_impossible(TABLE *table,
   if (table->s->tmp_table)
     DBUG_RETURN(true);
 
-
   /*
-    We also test if OPTIMIZE TABLE was given and was mapped to alter table.
-    In that case we always do full copy (ALTER_RECREATE is set in this case).
-
-    For the ALTER TABLE tbl_name ORDER BY ... we also always use copy
+    For the ALTER TABLE tbl_name ORDER BY ... we always use copy
     algorithm. In theory, this operation can be done in-place by some
     engine, but since a) no current engine does this and b) our current
     API lacks infrastructure for passing information about table ordering
@@ -6749,26 +6748,17 @@ static bool is_inplace_alter_impossible(TABLE *table,
     not supported for in-place in combination with other operations.
     Alone, it will be done by simple_rename_or_index_change().
   */
-  if (alter_info->flags & (Alter_info::ALTER_RECREATE |
-                           Alter_info::ALTER_ORDER |
+  if (alter_info->flags & (Alter_info::ALTER_ORDER |
                            Alter_info::ALTER_KEYS_ONOFF))
     DBUG_RETURN(true);
 
   /*
-    Test also that engine was not given during ALTER TABLE, or
-    we are force to run regular alter table (copy).
-    E.g. ALTER TABLE tbl_name ENGINE=MyISAM.
-    Note that in addition to checking flag in HA_CREATE_INFO we
-    also check HA_CREATE_INFO::db_type value. This is done
-    to cover cases in which engine is changed implicitly
-    (e.g. when non-partitioned table becomes partitioned).
-
-    Note that we do copy even if the table is already using the
-    given engine. Many users and tools depend on using ENGINE
-    to force a table rebuild.
+    If the table engine is changed explicitly (using ENGINE clause)
+    or implicitly (e.g. when non-partitioned table becomes
+    partitioned) a regular alter table (copy) needs to be
+    performed.
   */
-  if (create_info->db_type != table->s->db_type() ||
-      create_info->used_fields & HA_CREATE_USED_ENGINE)
+  if (create_info->db_type != table->s->db_type())
     DBUG_RETURN(true);
 
   /*
@@ -8498,6 +8488,15 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
   }
 
   /*
+    ALTER TABLE ... ENGINE to the same engine is a common way to
+    request table rebuild. Set ALTER_RECREATE flag to force table
+    rebuild.
+  */
+  if (create_info->db_type == table->s->db_type() &&
+      create_info->used_fields & HA_CREATE_USED_ENGINE)
+    alter_info->flags|= Alter_info::ALTER_RECREATE;
+
+  /*
     If the old table had partitions and we are doing ALTER TABLE ...
     engine= <new_engine>, the new table must preserve the original
     partitioning. This means that the new engine is still the
@@ -9465,12 +9464,14 @@ copy_data_between_tables(THD *thd, TABLE *from, TABLE *to,
     mysql_recreate_table()
     thd			Thread handler
     tables		Tables to recreate
+    table_copy          Recreate the table by using
+                        ALTER TABLE COPY algorithm
 
  RETURN
     Like mysql_alter_table().
 */
 
-bool mysql_recreate_table(THD *thd, TABLE_LIST *table_list)
+bool mysql_recreate_table(THD *thd, TABLE_LIST *table_list, bool table_copy)
 {
   HA_CREATE_INFO create_info;
   Alter_info alter_info;
@@ -9488,6 +9489,10 @@ bool mysql_recreate_table(THD *thd, TABLE_LIST *table_list)
   /* Force alter table to recreate table */
   alter_info.flags= (Alter_info::ALTER_CHANGE_COLUMN |
                      Alter_info::ALTER_RECREATE);
+
+  if (table_copy)
+    alter_info.requested_algorithm= Alter_info::ALTER_TABLE_ALGORITHM_COPY;
+
   DBUG_RETURN(mysql_alter_table(thd, NullS, NullS, &create_info,
                                 table_list, &alter_info, 0,
                                 (ORDER *) 0, 0));
diff --git a/sql/sql_table.h b/sql/sql_table.h
index cd1c4293c39..444626e0363 100644
--- a/sql/sql_table.h
+++ b/sql/sql_table.h
@@ -1,5 +1,5 @@
-/* Copyright (c) 2006, 2013, Oracle and/or its affiliates.
-   Copyright (c) 2011, 2013, Monty Program Ab.
+/* Copyright (c) 2006, 2014, Oracle and/or its affiliates.
+   Copyright (c) 2011, 2014, Monty Program Ab.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -222,7 +222,7 @@ bool mysql_compare_tables(TABLE *table,
                           Alter_info *alter_info,
                           HA_CREATE_INFO *create_info,
                           bool *metadata_equal);
-bool mysql_recreate_table(THD *thd, TABLE_LIST *table_list);
+bool mysql_recreate_table(THD *thd, TABLE_LIST *table_list, bool table_copy);
 bool mysql_create_like_table(THD *thd, TABLE_LIST *table,
                              TABLE_LIST *src_table,
                              HA_CREATE_INFO *create_info);