Bug#16274455: CAN NOT ACESS PARTITIONED TABLES WHEN

DOWNGRADED FROM 5.6.11 TO 5.6.10

Problem was new syntax not accepted by previous version.

Fixed by adding version comment of /*!50531 around the
new syntax.

Like this in the .frm file:
'PARTITION BY KEY /*!50611 ALGORITHM = 2 */ () PARTITIONS 3'
and also changing the output from SHOW CREATE TABLE to:
CREATE TABLE t1 (a INT)
/*!50100 PARTITION BY KEY */ /*!50611 ALGORITHM = 1 */ /*!50100 ()
PARTITIONS 3 */

It will always add the ALGORITHM into the .frm for KEY [sub]partitioned
tables, but for SHOW CREATE TABLE it will only add it in case it is the non
default ALGORITHM = 1.

Also notice that for 5.5, it will say /*!50531 instead of /*!50611, which
will make upgrade from 5.5 > 5.5.31 to 5.6 < 5.6.11 fail!
If one downgrades an fixed version to the same major version (5.5 or 5.6) the
bug 14521864 will be visible again, but unless the .frm is updated, it will
work again when upgrading again.

Also fixed so that the .frm does not get updated version
if a single partition check passes.

7 files changed, 107 insertions, 52 deletions

diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc
index 4afae052b76..859b3891b25 100644
--- a/sql/ha_partition.cc
+++ b/sql/ha_partition.cc
@@ -7968,6 +7968,7 @@ int ha_partition::check_for_upgrade(HA_CHECK_OPT *check_opt)
                                                     true,
                                                     true,
                                                     NULL,
+                                                    NULL,
                                                     NULL)) ||
           /* Also check that the length is smaller than the output field! */
               (part_buf_len + db_name.length() + table_name.length()) >=
@@ -7991,8 +7992,8 @@ int ha_partition::check_for_upgrade(HA_CHECK_OPT *check_opt)
                             part_buf);
           }
           m_part_info->key_algorithm= old_algorithm;
+          DBUG_RETURN(error);
         }
-        break;
       default:
         /* Not affected! */
         ;
diff --git a/sql/handler.cc b/sql/handler.cc
index dc4fc9ce210..98d6e8fb103 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -3324,6 +3324,9 @@ int handler::ha_check(THD *thd, HA_CHECK_OPT *check_opt)
   }
   if ((error= check(thd, check_opt)))
     return error;
+  /* Skip updating frm version if not main handler. */
+  if (table->file != this)
+    return error;
   return update_frm_version(table);
 }
 
diff --git a/sql/partition_info.cc b/sql/partition_info.cc
index 5a7d0bf0c43..056676503dd 100644
--- a/sql/partition_info.cc
+++ b/sql/partition_info.cc
@@ -2302,6 +2302,16 @@ bool partition_info::has_same_partitioning(partition_info *new_part_info)
 {
   DBUG_ENTER("partition_info::has_same_partitioning");
 
+  DBUG_ASSERT(part_field_array && part_field_array[0]);
+
+  /*
+    Only consider pre 5.5.3 .frm's to have same partitioning as
+    a new one with KEY ALGORITHM = 1 ().
+  */
+
+  if (part_field_array[0]->table->s->mysql_version >= 50503)
+    DBUG_RETURN(false);
+
   if (!new_part_info ||
       part_type != new_part_info->part_type ||
       num_parts != new_part_info->num_parts ||
@@ -2495,12 +2505,10 @@ bool partition_info::has_same_partitioning(partition_info *new_part_info)
 
   /*
     Only if key_algorithm was not specified before and it is now set,
-    consider this as nothing was changed!
-    But if already set, consider it as a change, and force rebuild!
+    consider this as nothing was changed, and allow change without rebuild!
   */
-  DBUG_ASSERT(new_part_info->key_algorithm !=
-                partition_info::KEY_ALGORITHM_NONE);
-  if (key_algorithm != partition_info::KEY_ALGORITHM_NONE)
+  if (key_algorithm != partition_info::KEY_ALGORITHM_NONE ||
+      new_part_info->key_algorithm == partition_info::KEY_ALGORITHM_NONE)
     DBUG_RETURN(false);
 
   DBUG_RETURN(true);
diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc
index a64520a298d..6850f5ada63 100644
--- a/sql/sql_partition.cc
+++ b/sql/sql_partition.cc
@@ -2412,6 +2412,58 @@ end:
   return err;
 }
 
+
+/**
+  Add 'KEY' word, with optional 'ALGORTIHM = N'.
+
+  @param fptr                   File to write to.
+  @param part_info              partition_info holding the used key_algorithm
+  @param current_comment_start  NULL, or comment string encapsulating the
+                                PARTITION BY clause.
+
+  @return Operation status.
+    @retval 0    Success
+    @retval != 0 Failure
+*/
+
+static int add_key_with_algorithm(File fptr, partition_info *part_info,
+                                  const char *current_comment_start)
+{
+  int err= 0;
+  err+= add_part_key_word(fptr, partition_keywords[PKW_KEY].str);
+
+  /*
+    current_comment_start is given when called from SHOW CREATE TABLE,
+    Then only add ALGORITHM = 1, not the default 2 or non-set 0!
+    For .frm current_comment_start is NULL, then add ALGORITHM if != 0.
+  */
+  if (part_info->key_algorithm == partition_info::KEY_ALGORITHM_51 || // SHOW
+      (!current_comment_start &&                                      // .frm
+       (part_info->key_algorithm != partition_info::KEY_ALGORITHM_NONE)))
+  {
+    /* If we already are within a comment, end that comment first. */
+    if (current_comment_start)
+      err+= add_string(fptr, "*/ ");
+    err+= add_string(fptr, "/*!50531 ");
+    err+= add_part_key_word(fptr, partition_keywords[PKW_ALGORITHM].str);
+    err+= add_equal(fptr);
+    err+= add_space(fptr);
+    err+= add_int(fptr, part_info->key_algorithm);
+    err+= add_space(fptr);
+    err+= add_string(fptr, "*/ ");
+    if (current_comment_start)
+    {
+      /* Skip new line. */
+      if (current_comment_start[0] == '\n')
+        current_comment_start++;
+      err+= add_string(fptr, current_comment_start);
+      err+= add_space(fptr);
+    }
+  }
+  return err;
+}
+
+
 /*
   Generate the partition syntax from the partition data structure.
   Useful for support of generating defaults, SHOW CREATE TABLES
@@ -2456,7 +2508,8 @@ char *generate_partition_syntax(partition_info *part_info,
                                 bool use_sql_alloc,
                                 bool show_partition_options,
                                 HA_CREATE_INFO *create_info,
-                                Alter_info *alter_info)
+                                Alter_info *alter_info,
+                                const char *current_comment_start)
 {
   uint i,j, tot_num_parts, num_subparts;
   partition_element *part_elem;
@@ -2490,18 +2543,8 @@ char *generate_partition_syntax(partition_info *part_info,
         err+= add_string(fptr, partition_keywords[PKW_LINEAR].str);
       if (part_info->list_of_part_fields)
       {
-        err+= add_part_key_word(fptr, partition_keywords[PKW_KEY].str);
-        if (part_info->key_algorithm != partition_info::KEY_ALGORITHM_NONE)
-        {
-          /*
-            Can't add a !50530 comment, since we are already within a comment!
-          */
-          err+= add_part_key_word(fptr, partition_keywords[PKW_ALGORITHM].str);
-          err+= add_equal(fptr);
-          err+= add_space(fptr);
-          err+= add_int(fptr, part_info->key_algorithm);
-          err+= add_space(fptr);
-        }
+        err+= add_key_with_algorithm(fptr, part_info,
+                                     current_comment_start);
         err+= add_part_field_list(fptr, part_info->part_field_list);
       }
       else
@@ -2541,19 +2584,9 @@ char *generate_partition_syntax(partition_info *part_info,
       err+= add_string(fptr, partition_keywords[PKW_LINEAR].str);
     if (part_info->list_of_subpart_fields)
     {
-      add_part_key_word(fptr, partition_keywords[PKW_KEY].str);
-      if (part_info->key_algorithm != partition_info::KEY_ALGORITHM_NONE)
-      {
-        /*
-          Can't add a !50530 comment, since we are already within a comment!
-        */
-        err+= add_part_key_word(fptr, partition_keywords[PKW_ALGORITHM].str);
-        err+= add_equal(fptr);
-        err+= add_space(fptr);
-        err+= add_int(fptr, part_info->key_algorithm);
-        err+= add_space(fptr);
-      }
-      add_part_field_list(fptr, part_info->subpart_field_list);
+      err+= add_key_with_algorithm(fptr, part_info,
+                                   current_comment_start);
+      err+= add_part_field_list(fptr, part_info->subpart_field_list);
     }
     else
       err+= add_part_key_word(fptr, partition_keywords[PKW_HASH].str);
diff --git a/sql/sql_partition.h b/sql/sql_partition.h
index 998e4b25f0b..f232eaa0629 100644
--- a/sql/sql_partition.h
+++ b/sql/sql_partition.h
@@ -1,7 +1,7 @@
 #ifndef SQL_PARTITION_INCLUDED
 #define SQL_PARTITION_INCLUDED
 
-/* Copyright (c) 2006, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2006, 2013, Oracle and/or its affiliates. All rights reserved.
 
   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
@@ -268,7 +268,8 @@ char *generate_partition_syntax(partition_info *part_info,
                                 uint *buf_length, bool use_sql_alloc,
                                 bool show_partition_options,
                                 HA_CREATE_INFO *create_info,
-                                Alter_info *alter_info);
+                                Alter_info *alter_info,
+                                const char *current_comment_start);
 #endif
 
 void create_partition_name(char *out, const char *in1,
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 326f09e7955..4a6568b605a 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -1528,24 +1528,30 @@ int store_create_info(THD *thd, TABLE_LIST *table_list, String *packet,
   }
 #ifdef WITH_PARTITION_STORAGE_ENGINE
   {
-    /*
-      Partition syntax for CREATE TABLE is at the end of the syntax.
-    */
-    uint part_syntax_len;
-    char *part_syntax;
     if (table->part_info &&
-        (!table->part_info->is_auto_partitioned) &&
-        ((part_syntax= generate_partition_syntax(table->part_info,
+        !((table->s->db_type()->partition_flags() & HA_USE_AUTO_PARTITION) &&
+          table->part_info->is_auto_partitioned))
+    {
+      /*
+        Partition syntax for CREATE TABLE is at the end of the syntax.
+      */
+      uint part_syntax_len;
+      char *part_syntax;
+      String comment_start;
+      table->part_info->set_show_version_string(&comment_start);
+      if ((part_syntax= generate_partition_syntax(table->part_info,
                                                   &part_syntax_len,
                                                   FALSE,
                                                   show_table_options,
-                                                  NULL, NULL))))
-    {
-       table->part_info->set_show_version_string(packet);
-       if (packet->append(part_syntax, part_syntax_len) ||
-           packet->append(STRING_WITH_LEN(" */")))
-        error= 1;
-       my_free(part_syntax);
+                                                  NULL, NULL,
+                                                  comment_start.c_ptr())))
+      {
+         packet->append(comment_start);
+         if (packet->append(part_syntax, part_syntax_len) ||
+             packet->append(STRING_WITH_LEN(" */")))
+          error= 1;
+         my_free(part_syntax);
+      }
     }
   }
 #endif
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 8bbe29cb3cf..cf4d7a9d955 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -1,5 +1,5 @@
 /*
-   Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
+   Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
 
    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
@@ -1694,7 +1694,8 @@ bool mysql_write_frm(ALTER_PARTITION_PARAM_TYPE *lpt, uint flags)
                                                          &syntax_len,
                                                          TRUE, TRUE,
                                                          lpt->create_info,
-                                                         lpt->alter_info)))
+                                                         lpt->alter_info,
+                                                         NULL)))
         {
           DBUG_RETURN(TRUE);
         }
@@ -1787,7 +1788,8 @@ bool mysql_write_frm(ALTER_PARTITION_PARAM_TYPE *lpt, uint flags)
                                                        &syntax_len,
                                                        TRUE, TRUE,
                                                        lpt->create_info,
-                                                       lpt->alter_info)))
+                                                       lpt->alter_info,
+                                                       NULL)))
       {
         error= 1;
         goto err;
@@ -4082,7 +4084,8 @@ bool mysql_create_table_no_lock(THD *thd,
                                                      &syntax_len,
                                                      TRUE, TRUE,
                                                      create_info,
-                                                     alter_info)))
+                                                     alter_info,
+                                                     NULL)))
       goto err;
     part_info->part_info_string= part_syntax_buf;
     part_info->part_info_len= syntax_len;