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

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL)
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]>

Attachments

Issue Links

relates to

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

Closed

links to

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

Activity

Ascending order - Click to sort in descending order

View 7 older comments

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

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

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...

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...

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

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

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

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.

—

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. —

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.

—

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. —

MariaDB Server

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

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration