How do I remove partitioning?
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&gt; 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.