Drop partition in MySQL table, force create next partition

Sign in:

mysql -u$(grep "^DBUser" /etc/zabbix/zabbix_server.conf|sed "s/^.*=//") -p$(grep "^DBPassword" /etc/zabbix/zabbix_server.conf|sed "s/^.*=//")

Open database zabbix:

use zabbix;

List if there is any partitions at all in table 'history_uint':

show create table zabbix.history_uint\G

This will report:

*************************** 1. row ***************************
       Table: history_uint
Create Table: CREATE TABLE `history_uint` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT 0,
  `value` bigint(20) unsigned NOT NULL DEFAULT 0,
  `ns` int(11) NOT NULL DEFAULT 0,
  KEY `history_uint_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
 PARTITION BY RANGE (`clock`)
(PARTITION `p2018___OLD` VALUES LESS THAN (1527825600) ENGINE = InnoDB,
 PARTITION `p2018_06_01` VALUES LESS THAN (1527912000) ENGINE = InnoDB,
 PARTITION `p2018_06_02` VALUES LESS THAN (1527998400) ENGINE = InnoDB,
 PARTITION `p2018_06_03` VALUES LESS THAN (1528084800) ENGINE = InnoDB,
 PARTITION `p2018_06_04` VALUES LESS THAN (1528171200) ENGINE = InnoDB,
 PARTITION `p2018_06_05` VALUES LESS THAN (1528257600) ENGINE = InnoDB)
1 row in set (0.001 sec)

Drop partition:

ALTER TABLE history_uint DROP PARTITION p2018_06_06;

After this list again:

show create table zabbix.history_uint\G

To manually call the partition generator at first take a look of keep_history interval in every table type:

select * from zabbix.manage_partitions;

This should report:

+--------------+--------+--------------+---------------------+----------+
| tablename    | period | keep_history | last_updated        | comments |
+--------------+--------+--------------+---------------------+----------+
| history      | day    |           30 | 2018-06-04 06:35:13 |          |
| history_log  | day    |          120 | 2018-06-04 06:35:13 |          |
| history_str  | day    |          120 | 2018-06-04 06:35:13 |          |
| history_text | day    |          120 | 2018-06-04 06:35:13 |          |
| history_uint | day    |           30 | 2018-06-04 06:35:13 |          |
| trends       | month  |           24 | 2018-06-04 06:35:13 |          |
| trends_uint  | month  |           24 | 2018-06-04 06:35:13 |          |
+--------------+--------+--------------+---------------------+----------+

Make sure all procedures have been created:

SHOW PROCEDURE STATUS\G

Procedure list:

*************************** 1. row ***************************
                  Db: zabbix
                Name: create_next_partitions
                Type: PROCEDURE
             Definer: zabbix@localhost
            Modified: 2018-06-04 05:59:20
             Created: 2018-06-04 05:59:20
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_bin
*************************** 2. row ***************************
                  Db: zabbix
                Name: create_partition_by_day
                Type: PROCEDURE
             Definer: zabbix@localhost
            Modified: 2018-06-04 05:59:24
             Created: 2018-06-04 05:59:24
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_bin
*************************** 3. row ***************************
                  Db: zabbix
                Name: create_partition_by_month
                Type: PROCEDURE
             Definer: zabbix@localhost
            Modified: 2018-06-04 05:59:28
             Created: 2018-06-04 05:59:28
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_bin
*************************** 4. row ***************************
                  Db: zabbix
                Name: drop_old_partition
                Type: PROCEDURE
             Definer: zabbix@localhost
            Modified: 2018-06-04 05:59:36
             Created: 2018-06-04 05:59:36
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_bin
*************************** 5. row ***************************
                  Db: zabbix
                Name: drop_partitions
                Type: PROCEDURE
             Definer: zabbix@localhost
            Modified: 2018-06-04 05:59:32
             Created: 2018-06-04 05:59:32
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_bin

At list on partition needs to be created under:

show create table zabbix.history\G
show create table zabbix.history_uint\G
show create table zabbix.history_str\G
show create table zabbix.history_text\G
show create table zabbix.history_log\G
show create table zabbix.trends\G
show create table zabbix.trends_uint\G

Output:

Now I can invoke the manually the creation process:

CALL zabbix.create_next_partitions('zabbix');

If there is no necessity to create new partitions then all 7 tables will be mentioned in output: