Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jun 13 '13 at 11:45

This question came from our site for professional and enthusiast programmers.

3 Answers 3

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.

share|improve this answer

The syntax to partition a table is this :

ALTER TABLE t1
    PARTITION BY HASH(id)
    PARTITIONS 8;

But i recommend to read carefully the docs first .

Before you partition a table you first need to think :

  • on what field the partition of data will be made (ex: by date, by user etc) this is crucial since it can improve your performance dramatically if you choose the right field
  • what algorithm to use to partition the data , you need to read this chapter in order to make the best decision since it's NOT a general rule by which you partition a table.
share|improve this answer
    
Thank you,Stephan.when MySql-Event call procedure whice will remove all partition,and I want to dynamically create partitions by procedure. My flow chart is 'remove all partitions'->'create new partitions'->'remove all partitions'. –  user2481179 Jun 13 '13 at 8:12
    
but what is the purpose of this? remove->create->remove ? –  Stephan Jun 13 '13 at 8:26
    
Sorry,I can't describe clearly just now. My purpose is that: I create an event,and this event will call procedure, and when calling procedure,which will check up the other table my_partition_table,if my_partition_table has not record about table my_table,procedure will remove all partitions in my_table,and then create new partitions;If my_partition_table has record,procedure will add partition,and execute code:'alter table my_table add partion(...);'. –  user2481179 Jun 13 '13 at 8:44
2  
i see... then you can add a partition per month and drop the third one every month ... you don't need to drop all and create all –  Stephan Jun 13 '13 at 8:53
1  
its simple you will have a table with 2 partitions : one for current month and one for past month and when the month will change you have to do 2 things : add the partition for current month (alter table add partition...) and drop the last partition ( alter table frop partition ...) –  Stephan Jun 13 '13 at 9:19

Stephan and Michael-sqlbot,thank you.
According to Stephan said, when before running procedure,I have created partition in table.And it can run normally.
And According to Michael-sqlbot said,I modified my procedure and added code:‘DECLARE CONTINUE HANDLER FOR 1505 BEGIN END;’ in my procedure;And so, I did not created partition in table before running procedure,and procedure can run normally too.
Compared above two ways,I choosed Michael-sqlbot's way.It solves that the problem with the first run by this way. Thank you two again.
The following is my procedure after modified:

    -- general_procedure
    create procedure general_procedure(in tablenamein varchar(50),in intervalHour int,in newIntervalNum int)
general_pro:begin 
    declare maxMonitTime datetime default SYSDATE();
    declare maxMonitTimeStr varchar(50);
    declare minMonitTime datetime default SYSDATE();
    declare minMonitTimeStr varchar(50);
    declare recoidNum int default 0;
    DECLARE CONTINUE HANDLER FOR 1505 BEGIN END;
    if tablenamein is null then 
        leave general_pro;
    end if;
    if intervalHour <= 0 then 
        set intervalHour = 6;
    end if;
    if newIntervalNum <= 0 then 
        set newIntervalNum = 1;
    end if;

    select count(*) into recoidNum from table_fen_qu where tablename=tablenamein and backupflag=0;
    if recoidNum > 0 then 
        select monittime into maxMonitTime from table_fen_qu where tablename=tablenamein and backupflag=0 order by monittime desc limit 1;
        if maxMonitTime is null then 
            set maxMonitTime = SYSDATE();
        end if;
        set recoidNum = timestampdiff(hour,SYSDATE(),DATE_SUB(maxMonitTime,INTERVAL 3 DAY));

        if recoidNum <= 0 then
            set recoidNum = 1; 
            while recoidNum <= newIntervalNum do 
                set maxMonitTime = ADDDATE(maxMonitTime,INTERVAL intervalHour HOUR);
                set maxMonitTimeStr = CONCAT('p',DATE_FORMAT(maxMonitTime,"%Y%m%d%H%i%s"));
                set @v_add_s = CONCAT('ALTER TABLE ',tablenamein,' ADD PARTITION  (PARTITION ',maxMonitTimeStr,' VALUES LESS THAN (\'',maxMonitTime,'\') ENGINE = InnoDB )');
                prepare stmt from @v_add_s;
                execute stmt; 
                deallocate prepare stmt;
                insert into table_fen_qu(fenquname,tablename,monittime,backupflag) VALUES(maxMonitTimeStr,tablenamein,maxMonitTime,0);
                set recoidNum = recoidNum + 1;
            end while;
        end if;
    else
        set @v_del_s = CONCAT('ALTER TABLE ',tablenamein,' remove partitioning');
        prepare stmt from @v_del_s;
        execute stmt; 
        deallocate prepare stmt;
        delete from table_fen_qu where tablename = tablenamein;
        set @sele = CONCAT('select min(moint_time),max(moint_time) into @mintime,@maxtime from ',tablenamein);
        prepare stmt from @sele;
        execute stmt; 
        deallocate prepare stmt;
        set minMonitTime = @mintime;
        set maxMonitTime = @maxtime;
        if minMonitTime is null then 
            set minMonitTimeStr = CONCAT(DATE_FORMAT(ADDDATE(SYSDATE(),INTERVAL 4 DAY),'%Y-%m-%d'),' 00:00:00');
            set minMonitTime = STR_TO_DATE(minMonitTimeStr,'%Y-%m-%d %H:%i:%s');
            set minMonitTimeStr = CONCAT('p',DATE_FORMAT(minMonitTime,"%Y%m%d%H%i%s"));
            set @v_add_s = CONCAT('ALTER TABLE ',tablenamein,' PARTITION BY RANGE COLUMNS(moint_time) (PARTITION ',minMonitTimeStr,' VALUES LESS THAN (\'',minMonitTime,'\') ENGINE = InnoDB);');
            prepare stmt from @v_add_s;
            execute stmt; 
            deallocate prepare stmt;
            insert into table_fen_qu(fenquname,tablename,monittime,backupflag) VALUES(minMonitTimeStr,tablenamein,minMonitTime,0);
        else 
            set recoidNum = 2; 
            set minMonitTimeStr = CONCAT(DATE_FORMAT(minMonitTime,'%Y-%m-%d'),' 00:00:00');
            set minMonitTime = STR_TO_DATE(minMonitTimeStr,'%Y-%m-%d %H:%i:%s');
            set maxMonitTimeStr = CONCAT(DATE_FORMAT(ADDDATE(maxMonitTime,INTERVAL 4 DAY),'%Y-%m-%d'),' 00:00:00');
            set maxMonitTime = STR_TO_DATE(maxMonitTimeStr,'%Y-%m-%d %H:%i:%s');
            set newIntervalNum = floor(timestampdiff(hour,minMonitTime,maxMonitTime) / intervalHour) + 1;
            if newIntervalNum < 12 then
                set newIntervalNum = 12;
            end if;
            set minMonitTimeStr = CONCAT('p',DATE_FORMAT(minMonitTime,"%Y%m%d%H%i%s"));
            set @v_add_s = CONCAT('ALTER TABLE ',tablenamein,' PARTITION BY RANGE COLUMNS(moint_time) (PARTITION ',minMonitTimeStr,' VALUES LESS THAN (\'',minMonitTime,'\') ENGINE = InnoDB,');
            insert into table_fen_qu(fenquname,tablename,monittime,backupflag) VALUES(minMonitTimeStr,tablenamein,minMonitTime,0);
            while recoidNum <= newIntervalNum do 
                set minMonitTime = ADDDATE(minMonitTime,INTERVAL intervalHour HOUR);
                set minMonitTimeStr = CONCAT('p',DATE_FORMAT(minMonitTime,"%Y%m%d%H%i%s"));
                set @v_add_s = CONCAT(@v_add_s,'PARTITION ',minMonitTimeStr,' VALUES LESS THAN (\'',minMonitTime,'\') ENGINE = InnoDB,');
                insert into table_fen_qu(fenquname,tablename,monittime,backupflag) VALUES(minMonitTimeStr,tablenamein,minMonitTime,0);
                set recoidNum = recoidNum + 1;
            end while;
            set @v_add_s = left(@v_add_s,LENGTH(@v_add_s)-1);
            set @v_add_s = CONCAT(@v_add_s,');');
            prepare stmt from @v_add_s;
            execute stmt; 
            deallocate prepare stmt;
        end if;
    end if;
end general_pro;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.