How do I remove partitioning?

There is a table my_table which has not been created any partition in MySQL 5.6. And I'm trying to create partitions by procedure in my_table, when I run this code:

alter table my_table remove partitioning

I'm getting this error:

1505 - Partition management on a not partitioned table is not possible

But when I run above code in MySQL version 5.5, this code can be run.

If my_table has been created partitions, and when I run above code, not only MySQL 5.5 can run, but also MySQL 5.6 can run too.

I guess that if this code can be run, MySQL 5.6 need to be config some files.But I am not able to why.

Can you solve it?

I'm not sure I would use the mechanism that you're using to purge old data, but since that's what you want to do, there is a way to work around this.

What you are seeing appears to be an undocumented incompatible change in behavior between MySQL 5.5 and MySQL 5.6 -- although, arguably, the real problem is that the statement should not be valid in MySQL 5.5 either when the table is not partitioned.

I confirmed the behavior you are seeing with MySQL 5.5.30 (no error) and 5.6.10 (error).


I'm trying to create partitions by procedure

Do you mean in a Stored Procedure?

If that's the case, then you could use a CONTINUE HANDLER specifically written to allow your stored procedure to ignore that one specific error code.

The following works in both MySQL 5.5 and 5.6.

DELIMITER $

DROP PROCEDURE IF EXISTS `test`.`test_continue_handler` $
CREATE PROCEDURE `test`.`test_continue_handler` ()
BEGIN

  -- you may have some code that executes before the ALTER TABLE, here

  BEGIN
    DECLARE CONTINUE HANDLER FOR 1505 BEGIN END;
    ALTER TABLE t1 REMOVE PARTITIONING;
  END;

  SELECT 'that worked okay' AS message; -- remove this, your usual code after the ALTER TABLE goes here

END $

DELIMITER ;

mysql> call test_continue_handler;
+------------------+
| message          |
+------------------+
| that worked okay |
+------------------+
1 row in set (0.06 sec)

Query OK, 0 rows affected (0.07 sec)

The BEGIN END; after 1505 is telling the server "when this error occurs, run the code that appears in this empty block instead of throwing an error." There's no code between BEGIN and END but MySQL doesn't care and this results in suppressing error 1505.

The BEGIN and END; before "DECLARE..." and after "ALTER..." limit the scope of that CONTINUE HANDLER to only the single ALTER TABLE so that any other statements throwing that same error code later in your stored procedure (or in any procedure called by your procedure) will not be ignored. Note this does not mean statements after the ALTER TABLE but before the next END will not be executed if an error occurs -- they will. It only means that after the END;, the CONTINUE HANDLER will be out of scope and will not trap subsequent errors.

StackExchange Database Administrators Q#44458, answer score: 2