1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
|
--source include/have_innodb.inc
--source include/have_partition.inc
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);
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();
drop procedure create_part_max;
prepare stmt from "alter table `test_data`
partition by range(unix_timestamp(clocktime)) (
partition partMAX values less than MAXVALUE
)";
execute stmt;
execute stmt;
deallocate prepare stmt;
drop table test_data;
#
# MDEV-12389 ADD CHECK leaves an orphaned .par file
#
--let $datadir=`SELECT @@datadir`
# InnoDB
create table t1(id int, d date not null, b bool not null default 0, primary key(id,d))
engine=innodb
partition by range columns (d) (
partition p1 values less than ('2016-10-18'),
partition p2 values less than ('2020-10-19'));
insert t1 values (0, '2000-01-02', 0);
insert t1 values (1, '2020-01-02', 10);
--replace_regex /#sql-[0-9a-f_]*/#sql-temporary/
--error ER_CONSTRAINT_FAILED
alter table t1 add check (b in (0, 1));
alter table t1 add check (b in (0, 10));
show create table t1;
--error ER_CONSTRAINT_FAILED
insert t1 values (2, '2020-01-03', 20);
drop table t1;
--list_files $datadir/test
# MyISAM, different execution path
create table t1(id int, d date not null, b bool not null default 0, primary key(id,d))
partition by range columns (d) (
partition p1 values less than ('2016-10-18'),
partition p2 values less than ('2020-10-19'));
insert t1 values (0, '2000-01-02', 0);
insert t1 values (1, '2020-01-02', 10);
--replace_regex /#sql-[0-9a-f_]*/#sql-temporary/
--error ER_CONSTRAINT_FAILED
alter table t1 add check (b in (0, 1));
alter table t1 add check (b in (0, 10));
show create table t1;
--error ER_CONSTRAINT_FAILED
insert t1 values (2, '2020-01-03', 20);
drop table t1;
--list_files $datadir/test
#
# MDEV-13097 Online alter of a partitioned MyISAM table with auto_increment
#
create table t1 (id_1 int auto_increment, id_2 int, id_3 int, d1 date, dt1 datetime default current_timestamp, dt2 datetime default current_timestamp on update current_timestamp, primary key (id_2, id_3), key(id_1)) partition by hash(id_2) partitions 3 (partition p01, partition p02, partition p03);
insert into t1 values(0, 1, 1, NULL, now(), now());
alter online table t1 delay_key_write=1;
show create table t1;
drop table t1;
|
