Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7990

ERROR 1526 when procedure executed for second time ALTER TABLE partition ... pMAX values less than MAXVALUE

    XMLWordPrintable

Details

    • 10.0.20

    Description

      test case:

      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:20:47 2015][MDEV]> CREATE TABLE `test_data` (
          ->   `hid` bigint(20) unsigned NOT NULL,
          ->   `itid` bigint(20) unsigned NOT NULL,
          ->   `clocktime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          ->   `values` double(16,4) NOT NULL,
          ->   PRIMARY KEY (`hid`,`itid`,`clocktime`)
          -> ) ENGINE=InnoDB
          -> ;
      Query OK, 0 rows affected (0.17 sec)
       
      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:21:00 2015][MDEV]> INSERT INTO `test_data` (`hid`, `itid`, `clocktime`, `values`) VALUES
          -> (1, 1, '2015-03-10 06:25:16', 0.0000),
          -> (1, 1, '2015-03-10 06:26:24', 0.0000),
          -> (1, 1, '2015-03-10 06:27:32', 0.0000),
          -> (1, 1, '2015-03-10 06:28:40', 0.0000),
          -> (1, 1, '2015-03-10 06:29:49', 0.0000),
          -> (1, 1, '2015-03-10 06:30:57', 0.0000),
          -> (1, 1, '2015-03-10 06:32:05', 0.0000),
          -> (1, 1, '2015-03-10 06:33:14', 0.0000),
          -> (1, 1, '2015-03-10 06:34:22', 0.0000),
          -> (1, 1, '2015-03-10 06:35:30', 0.0000),
          -> (1, 1, '2015-03-10 06:36:39', 0.0000),
          -> (1, 1, '2015-03-10 06:37:47', 0.0000),
          -> (1, 1, '2015-03-10 06:38:55', 0.0000),
          -> (1, 1, '2015-03-10 06:40:03', 0.0000),
          -> (1, 1, '2015-03-10 06:41:09', 0.0000),
          -> (1, 1, '2015-03-10 06:42:21', 0.0000),
          -> (1, 1, '2015-03-10 06:43:29', 0.0000),
          -> (1, 1, '2015-03-10 06:44:37', 0.0000),
          -> (1, 1, '2015-03-10 06:45:46', 0.0000),
          -> (1, 1, '2015-03-10 06:47:05', 0.0000),
          -> (1, 1, '2015-03-10 06:48:21', 0.0000),
          -> (1, 1, '2015-03-10 06:49:41', 0.0000),
          -> (1, 1, '2015-03-10 06:50:58', 0.0000),
          -> (1, 1, '2015-03-10 06:52:08', 0.0000),
          -> (1, 1, '2015-03-10 06:53:17', 0.0000),
          -> (1, 1, '2015-03-10 06:54:25', 0.0000),
          -> (563, 1, '2015-03-17 14:28:28', 0.3125),
          -> (563, 1, '2015-03-17 14:29:39', 0.2775),
          -> (563, 1, '2015-03-17 14:30:49', 0.2675);
      Query OK, 29 rows affected (0.01 sec)
      Records: 29  Duplicates: 0  Warnings: 0
       
      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:21:06 2015][MDEV]> DELIMITER $$
      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:21:21 2015][MDEV]> 
      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:21:21 2015][MDEV]> DROP PROCEDURE IF EXISTS `create_part_max`$$
      Query OK, 0 rows affected, 1 warning (0.00 sec)
       
      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:21:21 2015][MDEV]> 
      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:21:21 2015][MDEV]> CREATE PROCEDURE `create_part_max`()
          -> BEGIN
          -> alter table `test_data`
          -> partition by range(unix_timestamp(clocktime)) (
          -> partition partMAX values less than MAXVALUE
          -> );
          -> END
          -> $$
      Query OK, 0 rows affected (0.03 sec)
       
      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:21:21 2015][MDEV]> DELIMITER ;
      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:21:22 2015][MDEV]> show create table test_data \G
      *************************** 1. row ***************************
             Table: test_data
      Create Table: CREATE TABLE `test_data` (
        `hid` bigint(20) unsigned NOT NULL,
        `itid` bigint(20) unsigned NOT NULL,
        `clocktime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        `values` double(16,4) NOT NULL,
        PRIMARY KEY (`hid`,`itid`,`clocktime`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0.00 sec)
       
      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:21:31 2015][MDEV]>  show create procedure create_part_max \G
      *************************** 1. row ***************************
                 Procedure: create_part_max
                  sql_mode: 
          Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `create_part_max`()
      BEGIN
      alter table `test_data`
      partition by range(unix_timestamp(clocktime)) (
      partition partMAX values less than MAXVALUE
      );
      END
      character_set_client: utf8
      collation_connection: utf8_general_ci
        Database Collation: latin1_swedish_ci
      1 row in set (0.00 sec)
       
      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:21:47 2015][MDEV]> call create_part_max();
      Query OK, 29 rows affected (0.50 sec)              
       
      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:23:25 2015][MDEV]> call create_part_max();
      ERROR 1526 (HY000): Table has no partition for value 1425965116
      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:23:27 2015][MDEV]> alter table `test_data`
          -> partition by range(unix_timestamp(clocktime)) (
          -> partition partMAX values less than MAXVALUE
          -> );
      Query OK, 29 rows affected (0.45 sec)              
      Records: 29  Duplicates: 0  Warnings: 0
       
      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:23:31 2015][MDEV]> alter table `test_data`
          -> partition by range(unix_timestamp(clocktime)) (
          -> partition partMAX values less than MAXVALUE
          -> );
      Query OK, 29 rows affected (0.53 sec)              
      Records: 29  Duplicates: 0  Warnings: 0
       
      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:23:33 2015][MDEV]> 
      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:23:34 2015][MDEV]>  call create_part_max();
      ERROR 1526 (HY000): Table has no partition for value 1425965116
      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:23:40 2015][MDEV]> Bye
      root@centos-6-5-minimal-base:[Tue Apr 14 16:23:42][/tmp/mariadb-10.0.17-centos6-amd64]$ mysql MDEV
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 14
      Server version: 10.0.17-MariaDB MariaDB Server
       
      Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:23:50 2015][MDEV]>  call create_part_max();
      Query OK, 29 rows affected (0.52 sec)              
       
      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:23:52 2015][MDEV]>  call create_part_max();
      ERROR 1526 (HY000): Table has no partition for value 1425965116
      mysql root@centos-6-5-minimal-base:[Tue Apr 14 16:23:53 2015][MDEV]> 

      Attachments

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              ivan.stoykov@skysql.com Stoykov (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.