Debian Codegraph @

mysql-test/suite/rpl/r/rpl_autoextend_ddl.result

raw
1 #
2 # Test AUTOEXTEND_SIZE clause
3 # This test validates that the changes to AUTOEXTEND_SIZE
4 # option are replicated to the secondary nodes successfully
5 #
6 include/master-slave.inc
7 Warnings:
8 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
9 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.
10 [connection master]
11 drop database if exists test1;
12 create database test1;
13 use test;
14 # Scenario-1: Create a table with autoextend_size set to valid value
15 # and validate that the changes are propagated properly to the secondary nodes
16 CREATE TABLE t1(c1 INT) AUTOEXTEND_SIZE 4M;
17 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
18 WHERE NAME like '%t1%';
19 NAME FILE_SIZE AUTOEXTEND_SIZE
20 test/t1 4194304 4194304
21 include/sync_slave_sql_with_master.inc
22 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
23 WHERE NAME like '%t1%';
24 NAME FILE_SIZE AUTOEXTEND_SIZE
25 test/t1 4194304 4194304
26 SHOW CREATE TABLE t1;
27 Table Create Table
28 t1 CREATE TABLE `t1` (
29 `c1` int DEFAULT NULL
30 ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
31 # Scenario-2: Alter an existing table and set autoextend_size option
32 # and validate that the option is propagated to the slave nodes
33 ALTER TABLE t1 AUTOEXTEND_SIZE 0;
34 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
35 WHERE NAME like '%t1%';
36 NAME FILE_SIZE AUTOEXTEND_SIZE
37 test/t1 4194304 0
38 include/sync_slave_sql_with_master.inc
39 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
40 WHERE NAME like '%t1%';
41 NAME FILE_SIZE AUTOEXTEND_SIZE
42 test/t1 4194304 0
43 DROP TABLE t1;
44 # Scenario-3: Test that autoextend_size value for implicit tablespaces
45 # is visible on slave nodes
46 CREATE TABLESPACE myspace AUTOEXTEND_SIZE 4M;
47 include/sync_slave_sql_with_master.inc
48 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
49 WHERE NAME like '%myspace%';
50 NAME FILE_SIZE AUTOEXTEND_SIZE
51 myspace 4194304 4194304
52 ALTER TABLESPACE myspace AUTOEXTEND_SIZE 0;
53 include/sync_slave_sql_with_master.inc
54 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
55 WHERE NAME like '%myspace%';
56 NAME FILE_SIZE AUTOEXTEND_SIZE
57 myspace 4194304 0
58 DROP TABLESPACE myspace;
59 #Scenario-4: Test replication of autoextend_size on partitioned and
60 # sub-partitioned tables
61 CREATE TABLE tpart (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M
62 PARTITION BY RANGE (c1) (
63 PARTITION p0 VALUES LESS THAN (20),
64 PARTITION p1 VALUES LESS THAN maxvalue);
65 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
66 WHERE NAME like '%tpart%';
67 NAME FILE_SIZE AUTOEXTEND_SIZE
68 test/tpart#p#p0 4194304 4194304
69 test/tpart#p#p1 4194304 4194304
70 CREATE TABLE tsubpart (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M
71 PARTITION BY RANGE (c1)
72 SUBPARTITION BY HASH (c1)
73 SUBPARTITIONS 3 (
74 PARTITION p0 VALUES LESS THAN (10),
75 PARTITION p1 VALUES LESS THAN (20),
76 PARTITION p2 VALUES LESS THAN maxvalue);
77 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
78 WHERE NAME like '%tsubpart%';
79 NAME FILE_SIZE AUTOEXTEND_SIZE
80 test/tsubpart#p#p0#sp#p0sp0 4194304 4194304
81 test/tsubpart#p#p0#sp#p0sp1 4194304 4194304
82 test/tsubpart#p#p0#sp#p0sp2 4194304 4194304
83 test/tsubpart#p#p1#sp#p1sp0 4194304 4194304
84 test/tsubpart#p#p1#sp#p1sp1 4194304 4194304
85 test/tsubpart#p#p1#sp#p1sp2 4194304 4194304
86 test/tsubpart#p#p2#sp#p2sp0 4194304 4194304
87 test/tsubpart#p#p2#sp#p2sp1 4194304 4194304
88 test/tsubpart#p#p2#sp#p2sp2 4194304 4194304
89 include/sync_slave_sql_with_master.inc
90 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
91 WHERE NAME like '%tpart%';
92 NAME FILE_SIZE AUTOEXTEND_SIZE
93 test/tpart#p#p0 4194304 4194304
94 test/tpart#p#p1 4194304 4194304
95 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
96 WHERE NAME like '%tsubpart%';
97 NAME FILE_SIZE AUTOEXTEND_SIZE
98 test/tsubpart#p#p0#sp#p0sp0 4194304 4194304
99 test/tsubpart#p#p0#sp#p0sp1 4194304 4194304
100 test/tsubpart#p#p0#sp#p0sp2 4194304 4194304
101 test/tsubpart#p#p1#sp#p1sp0 4194304 4194304
102 test/tsubpart#p#p1#sp#p1sp1 4194304 4194304
103 test/tsubpart#p#p1#sp#p1sp2 4194304 4194304
104 test/tsubpart#p#p2#sp#p2sp0 4194304 4194304
105 test/tsubpart#p#p2#sp#p2sp1 4194304 4194304
106 test/tsubpart#p#p2#sp#p2sp2 4194304 4194304
107 DROP TABLE tpart;
108 DROP TABLE tsubpart;
109 # Scenario-5: Test replication of autoextend_size value when it is
110 # changed by ALTER TABLE statement for partitioned and sub-partitioned tables
111 CREATE TABLE tpart (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M;
112 ALTER TABLE tpart PARTITION BY RANGE (c1) (
113 PARTITION p0 VALUES LESS THAN (10),
114 PARTITION p1 VALUES LESS THAN (20),
115 PARTITION p2 VALUES LESS THAN maxvalue);
116 CREATE TABLE tsubpart (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M;
117 ALTER TABLE tsubpart
118 PARTITION BY RANGE (c1)
119 SUBPARTITION BY HASH (c1)
120 SUBPARTITIONS 3 (
121 PARTITION p0 VALUES LESS THAN (10),
122 PARTITION p1 VALUES LESS THAN (20),
123 PARTITION p2 VALUES LESS THAN maxvalue);
124 include/sync_slave_sql_with_master.inc
125 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
126 WHERE NAME like '%tpart%';
127 NAME FILE_SIZE AUTOEXTEND_SIZE
128 test/tpart#p#p0 4194304 4194304
129 test/tpart#p#p1 4194304 4194304
130 test/tpart#p#p2 4194304 4194304
131 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES
132 WHERE NAME like '%tsubpart%';
133 NAME FILE_SIZE AUTOEXTEND_SIZE
134 test/tsubpart#p#p0#sp#p0sp0 4194304 4194304
135 test/tsubpart#p#p0#sp#p0sp1 4194304 4194304
136 test/tsubpart#p#p0#sp#p0sp2 4194304 4194304
137 test/tsubpart#p#p1#sp#p1sp0 4194304 4194304
138 test/tsubpart#p#p1#sp#p1sp1 4194304 4194304
139 test/tsubpart#p#p1#sp#p1sp2 4194304 4194304
140 test/tsubpart#p#p2#sp#p2sp0 4194304 4194304
141 test/tsubpart#p#p2#sp#p2sp1 4194304 4194304
142 test/tsubpart#p#p2#sp#p2sp2 4194304 4194304
143 DROP TABLE tpart;
144 DROP TABLE tsubpart;
145 DROP DATABASE test1;
146 include/rpl_end.inc