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

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

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

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)

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.

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).

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...

commit mail got lost... here is the committed patch

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

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

pushed into mysql-5.1-bugteam mysql-6.0-bugteam

Pushed into 6.0.8-alpha  (revid:mattias.jonsson@sun.com-20081010181238-47pnnvwbg1v6jml2) (version source revid:mattias.jonsson@sun.com-20081104113000-1clp99d31cfs9onj) (pib:5)

Pushed into 5.1.30  (revid:mattias.jonsson@sun.com-20081010181238-47pnnvwbg1v6jml2) (version source revid:mattias.jonsson@sun.com-20081104084701-f0gsow6q2n827o4f) (pib:5)

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.

5.1 fix will appear in 5.1.31 per mail from Joro. Adjusted changelog entry accordingly.

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?

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? :)

Documentation updated - see Bug#20129 for details.

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)

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)

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)