[MDEV-7990] ERROR 1526 when procedure executed for second time ALTER TABLE partition ... pMAX values less than MAXVALUE Created: 2015-04-14  Updated: 2022-01-26  Resolved: 2015-09-14

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Procedure, Partitioning, Prepared Statements
Affects Version/s: 5.5, 10.0, 10.1
Fix Version/s: 10.0.22, 10.1.8

Type: Bug Priority: Critical
Reporter: Stoykov (Inactive) Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: partitioning, upstream, verified
Environment:

Linux

Issue Links:
Relates
relates to MDEV-8652 Partitioned table creation problem wh... Closed
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]>



 Comments   
Comment by Elena Stepanova [ 2015-04-15 ]

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

Comment by Oleksandr Byelkin [ 2015-05-04 ]

part_info->num_parts for second execution appeared to be 1 in the get_partition_id_range()

Comment by Oleksandr Byelkin [ 2015-05-04 ]

Above is wrong, the real cause is range_array[loc_part_id] equal to 0

Comment by Oleksandr Byelkin [ 2015-05-04 ]

col_val->fixed is 2 on second execution...

Comment by Oleksandr Byelkin [ 2015-05-04 ]

part_column_list_val has a lot of methods but cleunup(). How it is sopposed working in SP/PS?

Comment by Oleksandr Byelkin [ 2015-05-05 ]

Problem was that ALTER TABLE uses partition descriptors in LEX::part_info which was not reset after using. and there was no value set on the second execution.

lets try to attache this cleanup to cleanup of main UNIT iof the LEX (because it is already called in all places it need).

Comment by Oleksandr Byelkin [ 2015-05-05 ]

The problem with above approach is that memory can be freed...

Comment by Oleksandr Byelkin [ 2015-05-05 ]

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

Comment by Oleksandr Byelkin [ 2015-05-06 ]

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

Comment by Oleksandr Byelkin [ 2015-05-06 ]

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

Comment by Oleksandr Byelkin [ 2015-05-06 ]

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.

—

Comment by Oleksandr Byelkin [ 2015-08-06 ]

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.

—

Generated at Thu Feb 08 07:23:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.