[MDEV-7990] ERROR 1526 when procedure executed for second time ALTER TABLE partition ... pMAX values less than MAXVALUE - JIRA

Details

Type: Bug
Status: Closed
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.1, 10.0, 5.5
Fix Version/s: 10.0.22, 10.1.8
Component/s: Data Definition - Procedure, Partitioning, Prepared Statements
Labels:
Environment:
Linux

Sprint:
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]>

Gliffy Diagrams

Attachments

Issue Links

relates to

MDEV-8652 Partitioned table creation problem when creating from procedure context twice in same session

Open

links to

Bug #77333 ALTER TABLE ... PARTITION is not reexecution safe

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Elena Stepanova added a comment - 2015-04-15 20:07

Reproducible on MySQL 5.5-5.7.
Also reproducible with prepared statements (2nd execution).

Test case (same as in the description, just cleaned up)

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`)
     ) ;

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);

DROP PROCEDURE IF EXISTS `create_part_max`;

CREATE PROCEDURE `create_part_max`()
     alter table `test_data`
     partition by range(unix_timestamp(clocktime)) (
     partition partMAX values less than MAXVALUE
     );

call create_part_max();
call create_part_max();

Show

Elena Stepanova added a comment - 2015-04-15 20:07 Reproducible on MySQL 5.5-5.7. Also reproducible with prepared statements (2nd execution). Test case (same as in the description, just cleaned up) 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`) ) ; 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); DROP PROCEDURE IF EXISTS `create_part_max`; CREATE PROCEDURE `create_part_max`() alter table `test_data` partition by range(unix_timestamp(clocktime)) ( partition partMAX values less than MAXVALUE ); call create_part_max(); call create_part_max();

6 older comments

Hide

Permalink

Oleksandr Byelkin added a comment - 2015-05-05 16:02

it appeared that in CREATE TABLE part of the definition was put in the TABLE memory root which was freed during closing tables

Show

Oleksandr Byelkin added a comment - 2015-05-05 16:02 it appeared that in CREATE TABLE part of the definition was put in the TABLE memory root which was freed during closing tables

Hide

Permalink

Oleksandr Byelkin added a comment - 2015-05-06 11:24

The problem is with NULL partitions which first removed, then somehow appeared to be present in the list...

Show

Oleksandr Byelkin added a comment - 2015-05-06 11:24 The problem is with NULL partitions which first removed, then somehow appeared to be present in the list...

Hide

Permalink

Oleksandr Byelkin added a comment - 2015-05-06 12:14

Problem was in "sharing" the list between 2 copies of the partition info

Show

Oleksandr Byelkin added a comment - 2015-05-06 12:14 Problem was in "sharing" the list between 2 copies of the partition info

Hide

Permalink

Oleksandr Byelkin added a comment - 2015-05-06 14:20

revision-id: 3eceb51e697d5a514e70c374a06e7eef6b5a37a0
parent(s): aa5095627e2619bdad7916d33d1016802a84a9e1
committer: Oleksandr Byelkin
branch nick: server
timestamp: 2015-05-06 13:19:22 +0200
message:

~~MDEV-7990~~: ERROR 1526 when procedure executed for second time ALTER TABLE partition ... pMAX values less than MAXVALUE

Added cleaning partition data which are saved in LEX structure to make possible correct re-execution of a SP or PS using the code.

—

Show

Oleksandr Byelkin added a comment - 2015-05-06 14:20 revision-id: 3eceb51e697d5a514e70c374a06e7eef6b5a37a0 parent(s): aa5095627e2619bdad7916d33d1016802a84a9e1 committer: Oleksandr Byelkin branch nick: server timestamp: 2015-05-06 13:19:22 +0200 message: MDEV-7990 : ERROR 1526 when procedure executed for second time ALTER TABLE partition ... pMAX values less than MAXVALUE Added cleaning partition data which are saved in LEX structure to make possible correct re-execution of a SP or PS using the code. —

Hide

Permalink

Oleksandr Byelkin added a comment - 2015-08-06 15:01

revision-id: 32b14970a1b88571aa96f604e5c59ab6bf1b298c
parent(s): aa5095627e2619bdad7916d33d1016802a84a9e1
committer: Oleksandr Byelkin
branch nick: server
timestamp: 2015-08-06 14:00:48 +0200
message:

~~MDEV-7990~~: ERROR 1526 when procedure executed for second time ALTER TABLE partition ... pMAX values less than MAXVALUE

Added cleaning partition data which are saved in LEX structure to make possible correct re-execution of a SP or PS using the code.

—

Show

Oleksandr Byelkin added a comment - 2015-08-06 15:01 revision-id: 32b14970a1b88571aa96f604e5c59ab6bf1b298c parent(s): aa5095627e2619bdad7916d33d1016802a84a9e1 committer: Oleksandr Byelkin branch nick: server timestamp: 2015-08-06 14:00:48 +0200 message: MDEV-7990 : ERROR 1526 when procedure executed for second time ALTER TABLE partition ... pMAX values less than MAXVALUE Added cleaning partition data which are saved in LEX structure to make possible correct re-execution of a SP or PS using the code. —

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

Details

Description

Gliffy Diagrams

Attachments

Issue Links

Activity

People

Dates

Time Tracking

Agile