403Webshell
Server IP : 172.67.216.182  /  Your IP : 172.68.242.44
Web Server : Apache
System : Linux krdc-ubuntu-s-2vcpu-4gb-amd-blr1-01.localdomain 5.15.0-142-generic #152-Ubuntu SMP Mon May 19 10:54:31 UTC 2025 x86_64
User : www ( 1000)
PHP Version : 7.4.33
Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv
MySQL : OFF  |  cURL : ON  |  WGET : ON  |  Perl : ON  |  Python : OFF  |  Sudo : ON  |  Pkexec : ON
Directory :  /www/server/mysql/src/mysql-test/suite/parts/r/

Upload File :
current_dir [ Writeable ] document_root [ Writeable ]

 

Command :


[ Back ]     

Current File : /www/server/mysql/src/mysql-test/suite/parts/r/part_exch_valid_list_innodb.result
use test;
call mtr.add_suppression("row in wrong partition.*from REBUILD/REORGANIZED");
DROP TABLE IF EXISTS t_10;
DROP TABLE IF EXISTS t_100;
DROP TABLE IF EXISTS t_1000;
DROP TABLE IF EXISTS tp_r;
DROP TABLE IF EXISTS tp_rvar;
DROP TABLE IF EXISTS tp_rintvar;
DROP TABLE IF EXISTS tp_rdate;
DROP TABLE IF EXISTS tp_rintdate;
DROP TABLE IF EXISTS tsp_r;
DROP TABLE IF EXISTS tsp_rvar;
DROP TABLE IF EXISTS tsp_rvar1;
DROP TABLE IF EXISTS tp_l;
DROP TABLE IF EXISTS tsp_l;
DROP TABLE IF EXISTS tsp_lvar;
DROP TABLE IF EXISTS tsp_ldate;
DROP TABLE IF EXISTS tp_k;
DROP TABLE IF EXISTS tp_kvar;
DROP TABLE IF EXISTS tp_kdate;
DROP TABLE IF EXISTS tp_h;
DROP TABLE IF EXISTS tp_hvar;
DROP TABLE IF EXISTS tp_hdate;
DROP TABLE IF EXISTS tsp_00;
DROP TABLE IF EXISTS tsp_01;
DROP TABLE IF EXISTS tsp_02;
DROP TABLE IF EXISTS tsp_03;
DROP TABLE IF EXISTS tsp_04;
DROP TABLE IF EXISTS t_empty;
DROP TABLE IF EXISTS t_null;
CREATE TABLE t_10 (a INT,
b VARCHAR(25),
c DATE,
PRIMARY KEY (a))  
ENGINE = InnoDB;
CREATE TABLE t_100 (a INT,
b VARCHAR(25),
c DATE,
PRIMARY KEY (a))  
ENGINE = InnoDB;
CREATE TABLE t_1000 (a INT,
b VARCHAR(25),
c DATE,
PRIMARY KEY (a))  
ENGINE = InnoDB;
INSERT INTO t_10 VALUES (1, "One", '2001-01-01'), (3, "Three", '2003-01-01'), (5, "Five", '2005-01-01'), (9, "Nine", '2009-01-01');
INSERT INTO t_100 VALUES (11, "Eleven", '2011-01-01'), (13, "Thirdteen", '2013-01-01'), (15, "Fifeteen", '2015-01-01'), (19, "Nineteen", '2019-01-01');
INSERT INTO t_100 VALUES (91, "Ninety-one", '2091-01-01'), (93, "Ninety-three", '2093-01-01'), (95, "Ninety-five", '2095-01-01'), (99, "Ninety-nine", '2099-01-01');
INSERT INTO t_1000 VALUES (111, "Hundred elven", '2111-01-01'), (113, "Hundred thirdteen", '2113-01-01'), (115, "Hundred fiveteen", '2115-01-01'), (119, "Hundred nineteen", '2119-01-01');
INSERT INTO t_1000 VALUES (131, "Hundred thirty-one", '2131-01-01'), (133, "Hundred thirty-three", '2133-01-01'), (135, "Hundred thirty-five", '2135-01-01'), (139, "Hundred thirty-nine", '2139-01-01');
INSERT INTO t_1000 VALUES (151, "Hundred fifty-one", '2151-01-01'), (153, "Hundred fifty-three", '2153-01-01'), (155, "Hundred fity-five", '2155-01-01'), (159, "Hundred fifty-nine", '2159-01-01');
INSERT INTO t_1000 VALUES (191, "Hundred ninety-one", '2191-01-01'), (193, "Hundred ninety-three", '2193-01-01'), (195, "Hundred ninety-five", '2195-01-01'), (199, "Hundred ninety-nine", '2199-01-01');
CREATE TABLE t_empty (a INT,
b VARCHAR(25),
c DATE,
PRIMARY KEY (a))  
ENGINE = InnoDB;
CREATE TABLE t_null (a INT,
b VARCHAR(25),
c DATE,
PRIMARY KEY (a))  
ENGINE = InnoDB;
CREATE TABLE tsp_01(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a))
ENGINE = InnoDB  
AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1;
CREATE TABLE tsp_02(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a)) 
ENGINE = InnoDB  
AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2;
CREATE TABLE tsp_03(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a)) 
ENGINE = InnoDB  
AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3;
CREATE TABLE tsp_04(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a)) 
ENGINE = InnoDB  
AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4;
CREATE TABLE tsp_00(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a)) 
ENGINE = InnoDB  
AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0;
CREATE TABLE tp_r (a INT,
b VARCHAR(25),
c DATE,
PRIMARY KEY (a))  
ENGINE = InnoDB
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (10)  ,
PARTITION p1 VALUES LESS THAN (100)  ,
PARTITION p2 VALUES LESS THAN (1000)  );
CREATE TABLE tsp_r (a INT,
b VARCHAR(25),
c DATE,
PRIMARY KEY (a))  
ENGINE = InnoDB
PARTITION BY RANGE (a)
SUBPARTITION BY HASH(a)
(PARTITION p0 VALUES LESS THAN (10)  
(SUBPARTITION sp00,
SUBPARTITION sp01,
SUBPARTITION sp02,
SUBPARTITION sp03,
SUBPARTITION sp04),
PARTITION p1 VALUES LESS THAN (100)
(SUBPARTITION sp10  ,
SUBPARTITION sp11  ,
SUBPARTITION sp12  ,
SUBPARTITION sp13  ,
SUBPARTITION sp14  ),
PARTITION p2 VALUES LESS THAN (1000)  
(SUBPARTITION sp20,
SUBPARTITION sp21,
SUBPARTITION sp22,
SUBPARTITION sp23,
SUBPARTITION sp24));
INSERT INTO tp_r VALUES (2, "Two", '2002-01-01'), (4, "Four", '2004-01-01'), (6, "Six", '2006-01-01'), (8, "Eight", '2008-01-01');
INSERT INTO tp_r VALUES (12, "twelve", '2012-01-01'), (14, "Fourteen", '2014-01-01'), (16, "Sixteen", '2016-01-01'), (18, "Eightteen", '2018-01-01');
INSERT INTO tp_r VALUES (112, "Hundred twelve", '2112-01-01'), (114, "Hundred fourteen", '2114-01-01'), (116, "Hundred sixteen", '2116-01-01'), (118, "Hundred eightteen", '2118-01-01');
INSERT INTO tp_r VALUES (122, "Hundred twenty-two", '2122-01-01'), (124, "Hundred twenty-four", '2124-01-01'), (126, "Hundred twenty-six", '2126-01-01'), (128, "Hundred twenty-eight", '2128-01-01');
INSERT INTO tp_r VALUES (162, "Hundred sixty-two", '2162-01-01'), (164, "Hundred sixty-four", '2164-01-01'), (166, "Hundred sixty-six", '2166-01-01'), (168, "Hundred sixty-eight", '2168-01-01');
INSERT INTO tp_r VALUES (182, "Hundred eighty-two", '2182-01-01'), (184, "Hundred eighty-four", '2184-01-01'), (186, "Hundred eighty-six", '2186-01-01'), (188, "Hundred eighty-eight", '2188-01-01');
INSERT INTO tsp_r VALUES (2, "Two", '2002-01-01'), (4, "Four", '2004-01-01'), (6, "Six", '2006-01-01'), (8, "Eight", '2008-01-01');
INSERT INTO tsp_r VALUES (12, "twelve", '2012-01-01'), (14, "Fourteen", '2014-01-01'), (16, "Sixteen", '2016-01-01'), (18, "Eightteen", '2018-01-01');
INSERT INTO tsp_r VALUES (112, "Hundred twelve", '2112-01-01'), (114, "Hundred fourteen", '2114-01-01'), (116, "Hundred sixteen", '2116-01-01'), (118, "Hundred eightteen", '2118-01-01');
INSERT INTO tsp_r VALUES (122, "Hundred twenty-two", '2122-01-01'), (124, "Hundred twenty-four", '2124-01-01'), (126, "Hundred twenty-six", '2126-01-01'), (128, "Hundred twenty-eight", '2128-01-01');
INSERT INTO tsp_r VALUES (162, "Hundred sixty-two", '2162-01-01'), (164, "Hundred sixty-four", '2164-01-01'), (166, "Hundred sixty-six", '2166-01-01'), (168, "Hundred sixty-eight", '2168-01-01');
INSERT INTO tsp_r VALUES (182, "Hundred eight-two", '2182-01-01'), (184, "Hundred eighty-four", '2184-01-01'), (186, "Hundred eighty-six", '2186-01-01'), (188, "Hundred eighty-eight", '2188-01-01');
CREATE TABLE tp_rvar(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (b)) 
ENGINE = InnoDB  
PARTITION BY RANGE (b)
(PARTITION p0 VALUES LESS THAN ('H')  ,
PARTITION p1 VALUES LESS THAN ('Q')  ,
PARTITION p2 VALUES LESS THAN ('Z')  ,
PARTITION p3 VALUES LESS THAN (MAXVALUE)  )
AS SELECT a, b FROM tp_r;
ERROR HY000: VALUES value for partition 'p0' must have type INT
CREATE TABLE tp_rintvar(a INT,b VARCHAR(25),c DATE,
PRIMARY KEY (a,b))
ENGINE = InnoDB  
PARTITION BY RANGE COLUMNS (a,b)
(PARTITION p0 VALUES LESS THAN (10,'HHHHHHHHHHHHHHHHHHHHHHHHH'),
PARTITION p1 VALUES LESS THAN (100,'PPPPPPPPPPPPPPPPPPPPPPPPP'),
PARTITION p2 VALUES LESS THAN (1000,'WWWWWWWWWWWWWWWWWWWWWWWWW'),
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE))
AS SELECT a, b, c FROM tp_r;
CREATE TABLE tp_rvar(a INT,b VARCHAR(25),c DATE) 
ENGINE = InnoDB  
PARTITION BY RANGE COLUMNS (b)
(PARTITION p0 VALUES LESS THAN ('HHHHHHHHHHHHHHHHHHHHHHHHH'),
PARTITION p1 VALUES LESS THAN ('PPPPPPPPPPPPPPPPPPPPPPPPP'),
PARTITION p2 VALUES LESS THAN ('WWWWWWWWWWWWWWWWWWWWWWWWW'),
PARTITION p3 VALUES LESS THAN (MAXVALUE))
AS SELECT a, b, c FROM tp_r;
CREATE TABLE tp_rintdate(a INT,b VARCHAR(25),c DATE, 
PRIMARY KEY (a,c))
ENGINE = InnoDB  
PARTITION BY RANGE COLUMNS (a,c)
(PARTITION p0 VALUES LESS THAN (10,'2010-01-01'),
PARTITION p1 VALUES LESS THAN (100,'2200-01-01'),
PARTITION p2 VALUES LESS THAN (1000,'2300-01-01'),
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE))
AS SELECT a, b, c FROM t_10;
INSERT tp_rintdate SELECT * FROM t_100;
INSERT tp_rintdate SELECT * FROM t_1000;
CREATE TABLE tp_rdate(a INT,b VARCHAR(25),c DATE,
PRIMARY KEY (c))
ENGINE = InnoDB  
PARTITION BY RANGE COLUMNS (c)
(PARTITION p0 VALUES LESS THAN ('2010-01-01'),
PARTITION p1 VALUES LESS THAN ('2200-01-01'),
PARTITION p2 VALUES LESS THAN ('2300-01-01'),
PARTITION p3 VALUES LESS THAN (MAXVALUE))
AS SELECT a, b, c FROM tp_r;
CREATE TABLE tsp_rvar(a INT,b VARCHAR(25),c DATE) 
ENGINE = InnoDB  
PARTITION BY RANGE COLUMNS (b)
SUBPARTITION BY HASH(a)
SUBPARTITIONS 5
(PARTITION p0 VALUES LESS THAN ('HHHHHHHHHHHHHHHHHHHHHHHHH')
(SUBPARTITION sp00,
SUBPARTITION sp01,
SUBPARTITION sp02,
SUBPARTITION sp03,
SUBPARTITION sp04),
PARTITION p1 VALUES LESS THAN ('PPPPPPPPPPPPPPPPPPPPPPPPP')
(SUBPARTITION sp10,
SUBPARTITION sp11,
SUBPARTITION sp12,
SUBPARTITION sp13,
SUBPARTITION sp14),
PARTITION p2 VALUES LESS THAN ('WWWWWWWWWWWWWWWWWWWWWWWWW')
(SUBPARTITION sp20,
SUBPARTITION sp21,
SUBPARTITION sp22,
SUBPARTITION sp23,
SUBPARTITION sp24))
AS SELECT a, b, c FROM tsp_r;
CREATE TABLE tsp_rvar1(a INT,b VARCHAR(25),c DATE) 
ENGINE = InnoDB  
PARTITION BY RANGE COLUMNS (b)
SUBPARTITION BY HASH(a)
SUBPARTITIONS 5
(PARTITION p0 VALUES LESS THAN ('HHHHHHHHHHHHHHHHHHHHHHHHH'),
PARTITION p1 VALUES LESS THAN ('PPPPPPPPPPPPPPPPPPPPPPPPP'),
PARTITION p2 VALUES LESS THAN ('WWWWWWWWWWWWWWWWWWWWWWWWW'))
AS SELECT a, b, c FROM tsp_r;
CREATE TABLE tp_l (a INT,
b VARCHAR(25),
c DATE,
PRIMARY KEY (a))  
ENGINE = InnoDB
PARTITION BY LIST (a)
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9),
PARTITION p1 VALUES IN (10,11,12,13,14,15,16,17,18,19,90,91,92,93,94,95,96,97,98,99),
PARTITION p2 VALUES IN (110,111,112,113,114,115,116,117,118,119,
120,121,122,123,124,125,126,127,128,129,
130,131,132,133,134,135,136,137,138,139,
150,151,152,153,154,155,156,157,158,159,
160,161,162,163,164,165,166,167,168,169,
180,181,182,183,184,185,186,187,188,189,
190,191,192,193,194,195,196,197,198,199))
AS SELECT a, b, c FROM tp_r;
CREATE TABLE tsp_l (a INT,
b VARCHAR(25),
c DATE,
PRIMARY KEY (a))  
ENGINE = InnoDB
PARTITION BY LIST (a)
SUBPARTITION BY HASH(a)
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9)
(SUBPARTITION sp00,
SUBPARTITION sp01,
SUBPARTITION sp02,
SUBPARTITION sp03,
SUBPARTITION sp04),
PARTITION p1 VALUES IN (10,11,12,13,14,15,16,17,18,19,90,91,92,93,94,95,96,97,98,99)
(SUBPARTITION sp10,
SUBPARTITION sp11,
SUBPARTITION sp12,
SUBPARTITION sp13,
SUBPARTITION sp14),
PARTITION p2 VALUES IN (110,111,112,113,114,115,116,117,118,119,
120,121,122,123,124,125,126,127,128,129,
130,131,132,133,134,135,136,137,138,139,
150,151,152,153,154,155,156,157,158,159,
160,161,162,163,164,165,166,167,168,169,
180,181,182,183,184,185,186,187,188,189,
190,191,192,193,194,195,196,197,198,199)
(SUBPARTITION sp20,
SUBPARTITION sp21,
SUBPARTITION sp22,
SUBPARTITION sp23,
SUBPARTITION sp24))
AS SELECT a, b, c FROM tsp_r;
CREATE TABLE tp_k (a INT,
b VARCHAR(25),
c DATE,
PRIMARY KEY (a))  
ENGINE = InnoDB
PARTITION BY KEY (a)
PARTITIONS 3
AS SELECT a, b, c FROM tp_r;
CREATE TABLE tp_kvar (a INT,
b VARCHAR(25),
c DATE,
PRIMARY KEY (b))  
ENGINE = InnoDB
PARTITION BY KEY (b)
PARTITIONS 3
AS SELECT a, b, c FROM tp_r;
CREATE TABLE tp_kdate (a INT,
b VARCHAR(25),
c DATE,
PRIMARY KEY (c))  
ENGINE = InnoDB
PARTITION BY KEY (c)
PARTITIONS 3
AS SELECT a, b, c FROM tp_r;
CREATE TABLE tp_h (a INT,
b VARCHAR(25),
c DATE,
PRIMARY KEY (a))  
ENGINE = InnoDB
PARTITION BY HASH (a)
PARTITIONS 3
AS SELECT a, b, c FROM tp_r;
########################################################################
### List partitioned tables with/out validation
ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_10 WITH VALIDATION;
SELECT * FROM tp_l PARTITION (p0);
a	b	c
1	One	2001-01-01
3	Three	2003-01-01
5	Five	2005-01-01
9	Nine	2009-01-01
SELECT * FROM tp_l PARTITION (p1);
a	b	c
12	twelve	2012-01-01
14	Fourteen	2014-01-01
16	Sixteen	2016-01-01
18	Eightteen	2018-01-01
SELECT * FROM tp_l PARTITION (p2);
a	b	c
112	Hundred twelve	2112-01-01
114	Hundred fourteen	2114-01-01
116	Hundred sixteen	2116-01-01
118	Hundred eightteen	2118-01-01
122	Hundred twenty-two	2122-01-01
124	Hundred twenty-four	2124-01-01
126	Hundred twenty-six	2126-01-01
128	Hundred twenty-eight	2128-01-01
162	Hundred sixty-two	2162-01-01
164	Hundred sixty-four	2164-01-01
166	Hundred sixty-six	2166-01-01
168	Hundred sixty-eight	2168-01-01
182	Hundred eighty-two	2182-01-01
184	Hundred eighty-four	2184-01-01
186	Hundred eighty-six	2186-01-01
188	Hundred eighty-eight	2188-01-01
ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_100 WITH VALIDATION;
ERROR HY000: Found a row that does not match the partition
ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_100 WITHOUT VALIDATION;
SELECT * FROM tp_l PARTITION (p0);
a	b	c
11	Eleven	2011-01-01
13	Thirdteen	2013-01-01
15	Fifeteen	2015-01-01
19	Nineteen	2019-01-01
91	Ninety-one	2091-01-01
93	Ninety-three	2093-01-01
95	Ninety-five	2095-01-01
99	Ninety-nine	2099-01-01
SELECT * FROM tp_l PARTITION (p1);
a	b	c
12	twelve	2012-01-01
14	Fourteen	2014-01-01
16	Sixteen	2016-01-01
18	Eightteen	2018-01-01
SELECT * FROM tp_l PARTITION (p2);
a	b	c
112	Hundred twelve	2112-01-01
114	Hundred fourteen	2114-01-01
116	Hundred sixteen	2116-01-01
118	Hundred eightteen	2118-01-01
122	Hundred twenty-two	2122-01-01
124	Hundred twenty-four	2124-01-01
126	Hundred twenty-six	2126-01-01
128	Hundred twenty-eight	2128-01-01
162	Hundred sixty-two	2162-01-01
164	Hundred sixty-four	2164-01-01
166	Hundred sixty-six	2166-01-01
168	Hundred sixty-eight	2168-01-01
182	Hundred eighty-two	2182-01-01
184	Hundred eighty-four	2184-01-01
186	Hundred eighty-six	2186-01-01
188	Hundred eighty-eight	2188-01-01
ALTER TABLE tp_l EXCHANGE PARTITION p2 WITH TABLE t_100 WITHOUT VALIDATION;
ALTER TABLE tp_l EXCHANGE PARTITION p2 WITH TABLE t_100 WITHOUT VALIDATION;
SELECT * FROM tp_l PARTITION (p2);
a	b	c
112	Hundred twelve	2112-01-01
114	Hundred fourteen	2114-01-01
116	Hundred sixteen	2116-01-01
118	Hundred eightteen	2118-01-01
122	Hundred twenty-two	2122-01-01
124	Hundred twenty-four	2124-01-01
126	Hundred twenty-six	2126-01-01
128	Hundred twenty-eight	2128-01-01
162	Hundred sixty-two	2162-01-01
164	Hundred sixty-four	2164-01-01
166	Hundred sixty-six	2166-01-01
168	Hundred sixty-eight	2168-01-01
182	Hundred eighty-two	2182-01-01
184	Hundred eighty-four	2184-01-01
186	Hundred eighty-six	2186-01-01
188	Hundred eighty-eight	2188-01-01
ALTER TABLE tp_l ANALYZE PARTITION p0;
Table	Op	Msg_type	Msg_text
test.tp_l	analyze	status	OK
ALTER TABLE tp_l OPTIMIZE PARTITION p0;
Table	Op	Msg_type	Msg_text
test.tp_l	optimize	note	Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
test.tp_l	optimize	status	OK
ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_10 WITH VALIDATION;
SELECT * FROM tp_l PARTITION (p0);
a	b	c
2	Two	2002-01-01
4	Four	2004-01-01
6	Six	2006-01-01
8	Eight	2008-01-01
SELECT * FROM tp_l PARTITION (p1);
a	b	c
12	twelve	2012-01-01
14	Fourteen	2014-01-01
16	Sixteen	2016-01-01
18	Eightteen	2018-01-01
SELECT * FROM tp_l PARTITION (p2);
a	b	c
112	Hundred twelve	2112-01-01
114	Hundred fourteen	2114-01-01
116	Hundred sixteen	2116-01-01
118	Hundred eightteen	2118-01-01
122	Hundred twenty-two	2122-01-01
124	Hundred twenty-four	2124-01-01
126	Hundred twenty-six	2126-01-01
128	Hundred twenty-eight	2128-01-01
162	Hundred sixty-two	2162-01-01
164	Hundred sixty-four	2164-01-01
166	Hundred sixty-six	2166-01-01
168	Hundred sixty-eight	2168-01-01
182	Hundred eighty-two	2182-01-01
184	Hundred eighty-four	2184-01-01
186	Hundred eighty-six	2186-01-01
188	Hundred eighty-eight	2188-01-01
CREATE TABLE t_11 LIKE tp_l;
ALTER TABLE t_11 REMOVE PARTITIONING;
INSERT INTO t_11 SELECT * FROM t_10;
INSERT INTO t_11 SELECT * FROM t_100;
ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
SELECT * FROM tp_l PARTITION (p0);
a	b	c
1	One	2001-01-01
11	Eleven	2011-01-01
13	Thirdteen	2013-01-01
15	Fifeteen	2015-01-01
19	Nineteen	2019-01-01
3	Three	2003-01-01
5	Five	2005-01-01
9	Nine	2009-01-01
91	Ninety-one	2091-01-01
93	Ninety-three	2093-01-01
95	Ninety-five	2095-01-01
99	Ninety-nine	2099-01-01
DELETE FROM tp_l PARTITION (p1);
SELECT * FROM tp_l PARTITION (p1);
a	b	c
ALTER TABLE tp_l CHECK PARTITION p0 ;
Table	Op	Msg_type	Msg_text
test.tp_l	check	error	Found a misplaced row in part 0 should be in part 1:
 a:11
test.tp_l	check	error	Partition p0 returned error
test.tp_l	check	error	Table upgrade required. Please do "REPAIR TABLE `tp_l`" or dump/reload to fix it!
ALTER TABLE tp_l REPAIR PARTITION p0 ;
Table	Op	Msg_type	Msg_text
test.tp_l	repair	warning	Moved 8 misplaced rows
test.tp_l	repair	status	OK
SELECT * FROM tp_l PARTITION (p0);
a	b	c
1	One	2001-01-01
3	Three	2003-01-01
5	Five	2005-01-01
9	Nine	2009-01-01
SELECT * FROM tp_l PARTITION (p1);
a	b	c
11	Eleven	2011-01-01
13	Thirdteen	2013-01-01
15	Fifeteen	2015-01-01
19	Nineteen	2019-01-01
91	Ninety-one	2091-01-01
93	Ninety-three	2093-01-01
95	Ninety-five	2095-01-01
99	Ninety-nine	2099-01-01
SELECT * FROM tp_l PARTITION (p2);
a	b	c
112	Hundred twelve	2112-01-01
114	Hundred fourteen	2114-01-01
116	Hundred sixteen	2116-01-01
118	Hundred eightteen	2118-01-01
122	Hundred twenty-two	2122-01-01
124	Hundred twenty-four	2124-01-01
126	Hundred twenty-six	2126-01-01
128	Hundred twenty-eight	2128-01-01
162	Hundred sixty-two	2162-01-01
164	Hundred sixty-four	2164-01-01
166	Hundred sixty-six	2166-01-01
168	Hundred sixty-eight	2168-01-01
182	Hundred eighty-two	2182-01-01
184	Hundred eighty-four	2184-01-01
186	Hundred eighty-six	2186-01-01
188	Hundred eighty-eight	2188-01-01
DROP TABLE IF EXISTS t_11;
DELETE FROM t_10;
INSERT INTO t_10 VALUES (1, "One", '2001-01-01'), (3, "Three", '2003-01-01'), (5, "Five", '2005-01-01'), (9, "Nine", '2009-01-01');
DELETE FROM t_100;
INSERT INTO t_100 VALUES (11, "Eleven", '2011-01-01'), (13, "Thirdteen", '2013-01-01'), (15, "Fifeteen", '2015-01-01'), (19, "Nineteen", '2019-01-01');
INSERT INTO t_100 VALUES (91, "Ninety-one", '2091-01-01'), (93, "Ninety-three", '2093-01-01'), (95, "Ninety-five", '2095-01-01'), (99, "Ninety-nine", '2099-01-01');
DELETE FROM tsp_l;
INSERT INTO tsp_l VALUES (2, "Two", '2002-01-01'), (4, "Four", '2004-01-01'), (6, "Six", '2006-01-01'), (8, "Eight", '2008-01-01');
INSERT INTO tsp_l VALUES (12, "twelve", '2012-01-01'), (14, "Fourteen", '2014-01-01'), (16, "Sixteen", '2016-01-01'), (18, "Eightteen", '2018-01-01');
INSERT INTO tsp_l VALUES (112, "Hundred twelve", '2112-01-01'), (114, "Hundred fourteen", '2114-01-01'), (116, "Hundred sixteen", '2116-01-01'), (118, "Hundred eightteen", '2118-01-01');
INSERT INTO tsp_l VALUES (122, "Hundred twenty-two", '2122-01-01'), (124, "Hundred twenty-four", '2124-01-01'), (126, "Hundred twenty-six", '2126-01-01'), (128, "Hundred twenty-eight", '2128-01-01');
INSERT INTO tsp_l VALUES (162, "Hundred sixty-two", '2162-01-01'), (164, "Hundred sixty-four", '2164-01-01'), (166, "Hundred sixty-six", '2166-01-01'), (168, "Hundred sixty-eight", '2168-01-01');
INSERT INTO tsp_l VALUES (182, "Hundred eight-two", '2182-01-01'), (184, "Hundred eighty-four", '2184-01-01'), (186, "Hundred eighty-six", '2186-01-01'), (188, "Hundred eighty-eight", '2188-01-01');
### Range sub/partitioned tables with/out validation
ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_10 WITH VALIDATION;
ERROR HY000: Found a row that does not match the partition
ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_100 WITH VALIDATION;
ERROR HY000: Found a row that does not match the partition
ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_100 WITHOUT VALIDATION;
SELECT * FROM t_100;
a	b	c
SELECT * FROM tsp_l PARTITION (sp00);
a	b	c
11	Eleven	2011-01-01
13	Thirdteen	2013-01-01
15	Fifeteen	2015-01-01
19	Nineteen	2019-01-01
91	Ninety-one	2091-01-01
93	Ninety-three	2093-01-01
95	Ninety-five	2095-01-01
99	Ninety-nine	2099-01-01
SELECT * FROM tsp_l PARTITION (sp00);
a	b	c
11	Eleven	2011-01-01
13	Thirdteen	2013-01-01
15	Fifeteen	2015-01-01
19	Nineteen	2019-01-01
91	Ninety-one	2091-01-01
93	Ninety-three	2093-01-01
95	Ninety-five	2095-01-01
99	Ninety-nine	2099-01-01
SELECT * FROM tsp_l PARTITION (sp22);
a	b	c
112	Hundred twelve	2112-01-01
122	Hundred twenty-two	2122-01-01
162	Hundred sixty-two	2162-01-01
182	Hundred eight-two	2182-01-01
ALTER TABLE tsp_l EXCHANGE PARTITION sp22 WITH TABLE t_100 WITHOUT VALIDATION;
SELECT * FROM tsp_l PARTITION (sp22);
a	b	c
SELECT * FROM tsp_l PARTITION (sp22);
a	b	c
ALTER TABLE tsp_l EXCHANGE PARTITION sp22 WITH TABLE t_100 WITHOUT VALIDATION;
SELECT * FROM tsp_l PARTITION (sp22);
a	b	c
112	Hundred twelve	2112-01-01
122	Hundred twenty-two	2122-01-01
162	Hundred sixty-two	2162-01-01
182	Hundred eight-two	2182-01-01
ALTER TABLE tsp_l ANALYZE PARTITION p0;
Table	Op	Msg_type	Msg_text
test.tsp_l	analyze	status	OK
ALTER TABLE tsp_l OPTIMIZE PARTITION p0;
Table	Op	Msg_type	Msg_text
test.tsp_l	optimize	note	Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
test.tsp_l	optimize	status	OK
ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_10 WITH VALIDATION;
ERROR HY000: Found a row that does not match the partition
CREATE TABLE t_11 LIKE t_10;
INSERT INTO t_11 SELECT * FROM t_10;
INSERT INTO t_11 SELECT * FROM t_100;
ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_11 WITHOUT VALIDATION;
SELECT * FROM tsp_l PARTITION (sp00);
a	b	c
1	One	2001-01-01
3	Three	2003-01-01
5	Five	2005-01-01
9	Nine	2009-01-01
SELECT * FROM tsp_l PARTITION (sp01);
a	b	c
6	Six	2006-01-01
SELECT * FROM tsp_l PARTITION (sp03);
a	b	c
8	Eight	2008-01-01
SELECT * FROM tsp_l PARTITION (sp04);
a	b	c
4	Four	2004-01-01
SELECT * FROM tsp_l PARTITION (sp02);
a	b	c
2	Two	2002-01-01
ALTER TABLE tsp_l CHECK PARTITION sp00 ;
Table	Op	Msg_type	Msg_text
test.tsp_l	check	error	Found a misplaced row in part 0 should be in part 1:
 a:1
test.tsp_l	check	error	Subpartition sp00 returned error
test.tsp_l	check	error	Table upgrade required. Please do "REPAIR TABLE `tsp_l`" or dump/reload to fix it!
ALTER TABLE tsp_l REPAIR PARTITION sp00 ;
Table	Op	Msg_type	Msg_text
test.tsp_l	repair	warning	Moved 3 misplaced rows
test.tsp_l	repair	status	OK
SELECT * FROM tsp_l PARTITION (sp00);
a	b	c
5	Five	2005-01-01
SELECT * FROM tsp_l PARTITION (sp01);
a	b	c
1	One	2001-01-01
6	Six	2006-01-01
SELECT * FROM tsp_l PARTITION (sp02);
a	b	c
2	Two	2002-01-01
SELECT * FROM tsp_l PARTITION (sp03);
a	b	c
3	Three	2003-01-01
8	Eight	2008-01-01
SELECT * FROM tsp_l PARTITION (sp04);
a	b	c
4	Four	2004-01-01
9	Nine	2009-01-01
DROP TABLE IF EXISTS t_11;
DROP TABLE IF EXISTS t_10;
DROP TABLE IF EXISTS t_100;
DROP TABLE IF EXISTS t_1000;
DROP TABLE IF EXISTS tp_r;
DROP TABLE IF EXISTS tp_rvar;
DROP TABLE IF EXISTS tp_rintvar;
DROP TABLE IF EXISTS tp_rdate;
DROP TABLE IF EXISTS tp_rintdate;
DROP TABLE IF EXISTS tsp_r;
DROP TABLE IF EXISTS tsp_rvar;
DROP TABLE IF EXISTS tsp_rvar1;
DROP TABLE IF EXISTS tp_l;
DROP TABLE IF EXISTS tsp_l;
DROP TABLE IF EXISTS tsp_lvar;
DROP TABLE IF EXISTS tsp_ldate;
DROP TABLE IF EXISTS tp_k;
DROP TABLE IF EXISTS tp_kvar;
DROP TABLE IF EXISTS tp_kdate;
DROP TABLE IF EXISTS tp_h;
DROP TABLE IF EXISTS tp_hvar;
DROP TABLE IF EXISTS tp_hdate;
DROP TABLE IF EXISTS tsp_00;
DROP TABLE IF EXISTS tsp_01;
DROP TABLE IF EXISTS tsp_02;
DROP TABLE IF EXISTS tsp_03;
DROP TABLE IF EXISTS tsp_04;
DROP TABLE IF EXISTS t_empty;
DROP TABLE IF EXISTS t_null;

Youez - 2016 - github.com/yon3zu
LinuXploit