DROP PROCEDURE IF EXISTS mydb_1.Set_Partition;
CREATE PROCEDURE mydb_1.`Set_Partition`()
begin
# 发生错误回滚2个操作
declare exit handler for sqlexception rollback;
start TRANSACTION;
# 获取最小的分区名称
select partition_name into @P0_Name from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA='mydb_1' and table_name='terminal_parameter' order by partition_ordinal_position limit 1;
SET @s=concat('ALTER TABLE terminal_parameter DROP PARTITION ',@P0_Name);
# 预处理语句处理
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1; # 第二个预处理语句 （问题所在，存储过程处理完第一个预处理后，直接退出了，不继续往下执行）
# 增加一个新的分区，时间为当前时间加15天
set @Max_date= date(DATE_ADD(now(), INTERVAL 15 DAY));
SET @s1=concat('ALTER TABLE terminal_parameter ADD PARTITION (PARTITION p',DATE(now())+0,' VALUES LESS THAN (TO_DAYS (''',date(@Max_date),''')))');
PREPARE stmt2 FROM @s1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
COMMIT ; end;
红色部分是问题。
我希望用这个存储过程完成删除分区和添加新分区2个操作，第二个预处理不执行，不知道为何。本存储过程的目的是表维持12个分区，超过时间后，把最小的分区删除，同时增加一个分区，每15天执行一次。
在线等。
CREATE TABLE `terminal_parameter` (
`terminal_parameter_id` int(11) NOT NULL AUTO_INCREMENT,
`serial` int(11) DEFAULT NULL,
`network_type` char(1) DEFAULT NULL,
`mcc` int(8) DEFAULT NULL,
`mnc` int(8) DEFAULT NULL,
`lac` int(8) DEFAULT NULL,
`cellid` int(8) DEFAULT NULL,
`bsic_psc` int(8) DEFAULT NULL,
`ta_ec_io` int(8) DEFAULT NULL,
`bcch_rxlev_rscp` int(8) DEFAULT NULL,
`arfcn_uarfcn` int(8) DEFAULT NULL,
`rxq` int(8) DEFAULT NULL,
`c1` int(8) DEFAULT NULL,
`c2` int(8) DEFAULT NULL,
`signal_intensity` int(8) DEFAULT NULL,
`error_rate` int(8) DEFAULT NULL,
`alarm_type` varchar(16) DEFAULT NULL,
`txpower` int(8) DEFAULT NULL,
`small_running_number` int(8) DEFAULT NULL,
`createtime` datetime NOT NULL,
`userid` int(8) NOT NULL,
`terminal_id` int(8) DEFAULT NULL,
`state` char(1) DEFAULT '0',
`order_definition_id` int(8) DEFAULT NULL,
`order_code` varchar(20) DEFAULT NULL,
`charg_voltage` float(8,2) DEFAULT NULL,
`battery_voltage` float(8,2) DEFAULT NULL,
`temprad` float(8,2) DEFAULT NULL,
`run_state` int(8) DEFAULT NULL,
`switching_value1` int(8) DEFAULT NULL,
`switching_value2` int(8) DEFAULT NULL,
`bcch_freq` int(8) DEFAULT NULL,
`rxlev` int(8) DEFAULT NULL,
`rxlev_full` int(8) DEFAULT NULL,
`rxlev_sub` int(8) DEFAULT NULL,
`rxqual` int(8) DEFAULT NULL,
`rxqual_full` int(8) DEFAULT NULL,
`rxqual_sub` int(8) DEFAULT NULL,
`idle_ts` int(8) DEFAULT NULL,
`timing_advance` int(8) DEFAULT NULL,
`tch_efr_out` int(8) DEFAULT NULL,
`tch_efr_in` int(8) DEFAULT NULL,
`dtx` int(8) DEFAULT NULL,
`major_cycle_frequency` int(8) DEFAULT NULL,
PRIMARY KEY (`terminal_parameter_id`,`createtime`),
KEY `idx_createtime` (`createtime`),
KEY `idx_terminal_id` (`terminal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(TO_DAYS (createtime))
(
PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-11-15')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2010-11-30')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-12-15')),
PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-12-31')),
PARTITION p5 VALUES LESS THAN (TO_DAYS('2011-01-15')),
PARTITION p6 VALUES LESS THAN (TO_DAYS('2011-01-31')),
PARTITION p7 VALUES LESS THAN (TO_DAYS('2011-02-15')),
PARTITION p8 VALUES LESS THAN (TO_DAYS('2011-02-28')),
PARTITION p9 VALUES LESS THAN (TO_DAYS('2011-03-15')),
PARTITION p10 VALUES LESS THAN (TO_DAYS('2011-03-31')),
PARTITION p11 VALUES LESS THAN (TO_DAYS('2011-04-15')),
PARTITION p12 VALUES LESS THAN (TO_DAYS('2011-04-30'))
);
DELIMITER $$USE `ee`$$DROP PROCEDURE IF EXISTS `Set_Partition`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `Set_Partition`()
BEGIN
# 发生错误回滚2个操作
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
START TRANSACTION;
# 获取最小的分区名称
SELECT partition_name INTO @P0_Name FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='ee' AND table_name='terminal_parameter' ORDER BY partition_ordinal_position LIMIT 1;
SET @s=CONCAT('ALTER TABLE terminal_parameter DROP PARTITION ',@P0_Name);
SELECT @s;
# 预处理语句处理
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
# 第二个预处理语句 （问题所在，存储过程处理完第一个预处理后，直接退出了，不继续往下执行）
# 增加一个新的分区，时间为当前时间加15天
SET @Max_date= DATE(DATE_ADD(NOW(), INTERVAL 15 DAY));
SET @s1=CONCAT('ALTER TABLE terminal_parameter ADD PARTITION (PARTITION p',DATE(NOW())+0,' VALUES LESS THAN (TO_DAYS (''',DATE(@Max_date),''')))');
SELECT '123';
SELECT @s1;
PREPARE stmt2 FROM @s1;
SELECT '456';
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
COMMIT ;
END$$DELIMITER ;均有返回信息，检查一下
SELECT partition_name INTO @P0_Name FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='ee' AND table_name='terminal_parameter' ORDER BY partition_ordinal_position LIMIT 1;
是否有结果
Category Timestamp Duration Message Line Position
Error 2010-11-8 17:52:39 0:00:00.015 MySQL Database Error: VALUES LESS THAN value must be strictly increasing for each partition 29 0存储过程里面做了错误处理，如果发生错误，就回滚。
但测试发现，第一个预处理并没有回滚。不知道咋回事。
前面是因为错误回滚的问题，没有把错误报告出来，我还以为没执行，呵呵。
MYSQL HELP：
12.4.2. Statements That Cannot Be Rolled Back
Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines. You should design your transactions not to include such statements. If you issue a statement early in a transaction that cannot be rolled back, and then another statement later fails, the full effect of the transaction cannot be rolled back in such cases by issuing a ROLLBACK statement. 12.4.3. Statements That Cause an Implicit Commit
Each of the following statements (and any synonyms for them) implicitly end a transaction, as if you had done a COMMIT before executing the statement: ALTER EVENT, ALTER FUNCTION, ALTER FUNCTION, ALTER PROCEDURE, ALTER TABLE, BEGIN, CREATE DATABASE, CREATE EVENT, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, DROP DATABASE, DROP EVENT, DROP FUNCTION, DROP INDEX, DROP PROCEDURE, DROP TABLE, LOAD DATA INFILE LOCK TABLES, RENAME TABLE, SET AUTOCOMMIT=1, START TRANSACTION, TRUNCATE TABLE, UNLOCK TABLES.