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
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
#
# Test AUTOEXTEND_SIZE clause
# This test validates that the changes to AUTOEXTEND_SIZE
# option are replicated to the secondary nodes successfully
#
include/master-slave.inc
Warnings:
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
[connection master]
drop database if exists test1;
create database test1;
use test;
# Scenario-1: Create a table with autoextend_size set to valid value
# and validate that the changes are propagated properly to the secondary nodes
CREATE TABLE t1(c1 INT) AUTOEXTEND_SIZE 4M;
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE NAME like '%t1%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/t1 4194304 4194304
include/sync_slave_sql_with_master.inc
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE NAME like '%t1%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/t1 4194304 4194304
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` int DEFAULT NULL
) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# Scenario-2: Alter an existing table and set autoextend_size option
# and validate that the option is propagated to the slave nodes
ALTER TABLE t1 AUTOEXTEND_SIZE 0;
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE NAME like '%t1%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/t1 4194304 0
include/sync_slave_sql_with_master.inc
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE NAME like '%t1%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/t1 4194304 0
DROP TABLE t1;
# Scenario-3: Test that autoextend_size value for implicit tablespaces
# is visible on slave nodes
CREATE TABLESPACE myspace AUTOEXTEND_SIZE 4M;
include/sync_slave_sql_with_master.inc
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE NAME like '%myspace%';
NAME FILE_SIZE AUTOEXTEND_SIZE
myspace 4194304 4194304
ALTER TABLESPACE myspace AUTOEXTEND_SIZE 0;
include/sync_slave_sql_with_master.inc
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE NAME like '%myspace%';
NAME FILE_SIZE AUTOEXTEND_SIZE
myspace 4194304 0
DROP TABLESPACE myspace;
#Scenario-4: Test replication of autoextend_size on partitioned and
# sub-partitioned tables
CREATE TABLE tpart (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M
PARTITION BY RANGE (c1) (
PARTITION p0 VALUES LESS THAN (20),
PARTITION p1 VALUES LESS THAN maxvalue);
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE NAME like '%tpart%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/tpart#p#p0 4194304 4194304
test/tpart#p#p1 4194304 4194304
CREATE TABLE tsubpart (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M
PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c1)
SUBPARTITIONS 3 (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN maxvalue);
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE NAME like '%tsubpart%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/tsubpart#p#p0#sp#p0sp0 4194304 4194304
test/tsubpart#p#p0#sp#p0sp1 4194304 4194304
test/tsubpart#p#p0#sp#p0sp2 4194304 4194304
test/tsubpart#p#p1#sp#p1sp0 4194304 4194304
test/tsubpart#p#p1#sp#p1sp1 4194304 4194304
test/tsubpart#p#p1#sp#p1sp2 4194304 4194304
test/tsubpart#p#p2#sp#p2sp0 4194304 4194304
test/tsubpart#p#p2#sp#p2sp1 4194304 4194304
test/tsubpart#p#p2#sp#p2sp2 4194304 4194304
include/sync_slave_sql_with_master.inc
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE NAME like '%tpart%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/tpart#p#p0 4194304 4194304
test/tpart#p#p1 4194304 4194304
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE NAME like '%tsubpart%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/tsubpart#p#p0#sp#p0sp0 4194304 4194304
test/tsubpart#p#p0#sp#p0sp1 4194304 4194304
test/tsubpart#p#p0#sp#p0sp2 4194304 4194304
test/tsubpart#p#p1#sp#p1sp0 4194304 4194304
test/tsubpart#p#p1#sp#p1sp1 4194304 4194304
test/tsubpart#p#p1#sp#p1sp2 4194304 4194304
test/tsubpart#p#p2#sp#p2sp0 4194304 4194304
test/tsubpart#p#p2#sp#p2sp1 4194304 4194304
test/tsubpart#p#p2#sp#p2sp2 4194304 4194304
DROP TABLE tpart;
DROP TABLE tsubpart;
# Scenario-5: Test replication of autoextend_size value when it is
# changed by ALTER TABLE statement for partitioned and sub-partitioned tables
CREATE TABLE tpart (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M;
ALTER TABLE tpart PARTITION BY RANGE (c1) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN maxvalue);
CREATE TABLE tsubpart (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M;
ALTER TABLE tsubpart
PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c1)
SUBPARTITIONS 3 (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN maxvalue);
include/sync_slave_sql_with_master.inc
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE NAME like '%tpart%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/tpart#p#p0 4194304 4194304
test/tpart#p#p1 4194304 4194304
test/tpart#p#p2 4194304 4194304
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE NAME like '%tsubpart%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/tsubpart#p#p0#sp#p0sp0 4194304 4194304
test/tsubpart#p#p0#sp#p0sp1 4194304 4194304
test/tsubpart#p#p0#sp#p0sp2 4194304 4194304
test/tsubpart#p#p1#sp#p1sp0 4194304 4194304
test/tsubpart#p#p1#sp#p1sp1 4194304 4194304
test/tsubpart#p#p1#sp#p1sp2 4194304 4194304
test/tsubpart#p#p2#sp#p2sp0 4194304 4194304
test/tsubpart#p#p2#sp#p2sp1 4194304 4194304
test/tsubpart#p#p2#sp#p2sp2 4194304 4194304
DROP TABLE tpart;
DROP TABLE tsubpart;
DROP DATABASE test1;
include/rpl_end.inc
|
