| 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 |