Bug #39434 ALTER TABLE CHECK/OPTIMIZE/ANALYZE PARTITION work on non-partitioned table
Submitted: 13 Sep 2008 23:58 Modified: 12 Dec 2008 10:30
Reporter: Giuseppe Maxia Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.28 OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any
Tags: CHECK TABLE, partitioning, regression

[13 Sep 2008 23:58] Giuseppe Maxia
Description:
Using ALTER TABLE ... CHECK/ANALYZE/OPTIMIZE/REPAIR PARTITION ... on a non partitioned table, the statement works without warnings, with non existing partitions.

Example:
create table t1 (i int) partition by range (i) (partition p1 values less than (10));
Query OK, 0 rows affected (0.14 sec)

create table t2 (i int);
Query OK, 0 rows affected (0.17 sec)

alter table t1 check partition p1;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | status   | OK       | 
+---------+-------+----------+----------+
1 row in set (0.00 sec)

alter table t1 check partition p2; 
+---------+-------+----------+----------------------------------------+
| Table   | Op    | Msg_type | Msg_text                               |
+---------+-------+----------+----------------------------------------+
| test.t1 | check | error    | Error in list of partitions to test.t1 | 
+---------+-------+----------+----------------------------------------+
1 row in set (0.00 sec)
# correct. Partition does not exist

alter table t2 check partition p2;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t2 | check | status   | OK       | 
+---------+-------+----------+----------+
1 row in set (0.00 sec)
# incorrect. Partition does not exist, and it should give an error

How to repeat:
drop table if exists t1,t2;

create table t1 (i int) partition by range (i) (partition p1 values less than (10));
create table t2 (i int);

alter table t1 check partition p1; # ok    : succeeds as expected
alter table t1 check partition p2; # ok    : fails as expected
alter table t2 check partition p2; # not ok: should fail
[14 Sep 2008 3:02] MySQL Verification Team
Thank you for the bug report. Verified as described on bzr source tree. This bug isn't repeatable an older released version 5.0.22:

mysql> alter table t1 check partition p2; # ok    : fails as expected
ERROR 1504 (HY000): Error in list of partitions to CHECK
mysql> alter table t2 check partition p2; # not ok: should fail
ERROR 1502 (HY000): Partition management on a not partitioned table is not possible
mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| protocol_version        | 10                           |
| version                 | 5.1.22-rc-community          |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | ia32                         |
| version_compile_os      | Win32                        |
+-------------------------+------------------------------+
5 rows in set (0.03 sec)
[29 Sep 2008 7:49] Mattias Jonsson
What should the correct handling for those partition commands of a non partitioned table be?

1) just return an error like before bug#20129.

2) Don't set an error, but use the Msg_text column to print out the error like:
Table   Op      Msg_type        Msg_text
test.t2 check  error   Partition management on a not partitioned table is not possible

3) just ignore the partition clause, and do a full 'CHECK/OPTIMIZE/ANALYZE/REPAIR TABLE'

Since I remapped the code path for these admin commands in the fix for bug#20129, I did not stop the syntax for non partitioned tables (if used, it will work like a 'CHECK/OPTIMIZE/ANALYZE/REPAIR TABLE').

But I can do either 1) or 2) if that is the correct way to do it.
[29 Sep 2008 9:49] Giuseppe Maxia
I think the expected result is a full error, because there are no partitions.
Using the Msg_text is only appropriate when there is something wrong within partitioned tables (as in the case when we ask for a non existing name).
[10 Oct 2008 12:15] Mattias Jonsson
I think that this patch would do it:
=== modified file 'sql/sql_table.cc'
--- sql/sql_table.cc	2008-09-15 09:19:56 +0000
+++ sql/sql_table.cc	2008-10-10 12:13:28 +0000
@@ -4196,7 +4196,7 @@
       table->next_local= save_next_local;
       thd->open_options&= ~extra_open_options;
 #ifdef WITH_PARTITION_STORAGE_ENGINE
-      if (table->table && table->table->part_info)
+      if (table->table)
       {
         /*
           Set up which partitions that should be processed
@@ -4204,11 +4204,15 @@
         */
         Alter_info *alter_info= &lex->alter_info;
 
-        if (alter_info->flags & ALTER_ANALYZE_PARTITION ||
-            alter_info->flags & ALTER_CHECK_PARTITION ||
-            alter_info->flags & ALTER_OPTIMIZE_PARTITION ||
-            alter_info->flags & ALTER_REPAIR_PARTITION)
+        if (alter_info->flags & (ALTER_ANALYZE_PARTITION |
+            ALTER_CHECK_PARTITION | ALTER_OPTIMIZE_PARTITION |
+            ALTER_REPAIR_PARTITION))
         {
+          if (!table->table->part_info)
+          {
+            my_error(ER_PARTITION_MGMT_ON_NONPARTITIONED, MYF(0));
+            DBUG_RETURN(TRUE);
+          }
           uint no_parts_found;
           uint no_parts_opt= alter_info->partition_names.elements;
           no_parts_found= set_part_state(alter_info, table->table->part_info,

Will commit a proper patch later...
[10 Oct 2008 19:19] Mattias Jonsson
commit mail got lost... here is the committed patch

Attachment: b39434.patch.tar.gz (application/x-gzip, text), 2.85 KiB.

[10 Oct 2008 19:20] Mattias Jonsson
committed patch (file included above)

Bug#39434: ALTER TABLE CHECK/OPTIMIZE/ANALYZE PARTITION work
on non-partitioned table

Problem was that partitioning specific commands was accepted
for non partitioned tables and treated like
ANALYZE/CHECK/OPTIMIZE/REPAIR TABLE, after bug#20129 was fixed,
which changed the code path from mysql_alter_table to
mysql_admin_table.

Solution was to check if the table was partitioned before
trying to execute the admin command
[5 Nov 2008 10:30] Mattias Jonsson
pushed into mysql-5.1-bugteam mysql-6.0-bugteam
[10 Nov 2008 10:53] Bugs System
Pushed into 6.0.8-alpha  (revid:mattias.jonsson@sun.com-20081010181238-47pnnvwbg1v6jml2) (version source revid:mattias.jonsson@sun.com-20081104113000-1clp99d31cfs9onj) (pib:5)
[10 Nov 2008 11:37] Bugs System
Pushed into 5.1.30  (revid:mattias.jonsson@sun.com-20081010181238-47pnnvwbg1v6jml2) (version source revid:mattias.jonsson@sun.com-20081104084701-f0gsow6q2n827o4f) (pib:5)
[10 Nov 2008 15:01] Jon Stephens
Documented bugfix in the 5.1.30 and 6.0.8 changelogs as follows:

        The server attempted to execute the statements ALTER TABLE ... ANALYZE
        PARTITION, ALTER TABLE ... CHECK PARTITION, ALTER TABLE ... OPTIMIZE
        PARTITION, and ALTER TABLE ... REORGANIZE PARTITION on tables that were
        not partitioned.
[10 Nov 2008 15:20] Jon Stephens
5.1 fix will appear in 5.1.31 per mail from Joro. Adjusted changelog entry accordingly.
[11 Dec 2008 17:55] Jon Stephens
Time for a reality check here - according to BUG#20129, these statements were *removed* in 5.1.24/6.0.5.

Were they reinstated by the fix for this bug?
[11 Dec 2008 18:54] Mattias Jonsson
ALTER TABLE ... CHECK/ANALYZE/OPTIMIZE/REPAIR PARTITION was not working on corrupt partitions (see bug#20129) and was disabled in 5.1.24.

Then bug#20129 was fixed in 5.1.28 (or 5.1.27?). ALTER TABLE ... CHECK/ANALYZE/OPTIMIZE/REPAIR PARTITION was fixed and enabled, and at the same time CHECK/ANALYZE/OPTIMIZE/REPAIR TABLE was allowed on partitioned tables.

But when fixing this, it also enabled the use ALTER TABLE ... CHECK/ANALYZE/OPTIMIZE/REPAIR PARTITION for non partitioned tables (worked just like CHECK/ANALYZE/OPTIMIZE/REPAIR TABLE).

This was later prohibited for non partitioned tables in the patch for this bug (will be in 5.1.31).

So current status is:
ALTER TABLE ... CHECK/ANALYZE/OPTIMIZE/REPAIR PARTITION {ALL|partition1[,partition2[,part3...]]}
works only for partitioned tables (where supported by the storage engine).
(was disabled in 5.1.24, since it did not work correctly, and was fiexed and enabled in 5.1.28 or 27? and then also allowed it to be used on non partitioned tables. In 5.1.31 it is not allowed for non partitioned tables.)

CHECK/ANALYZE/OPTIMIZE/REPAIR TABLE
works for all tables, partitioned or not (where supported by the storage engine). From 5.1.28 (or 27?) it also works for partitioned tables.

Jon, does all these ramblings make sense to you? :)
[12 Dec 2008 10:30] Jon Stephens
Documentation updated - see Bug#20129 for details.
[19 Jan 2009 11:28] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:06] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:12] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)