Debian Codegraph @

mysql-test/suite/galera/t/galera_toi_alter_auto_increment.test

raw
1
2 #
3 # Test the operation of ALTER TABLE ... AUTO_INCREMENT
4 #
5
6 --source include/galera_cluster.inc
7 --source include/have_innodb.inc
8
9 --connection node_1
10 CREATE TABLE ten (f1 INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;
11 INSERT INTO ten VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
12
13 CREATE TABLE t1 (f1 INTEGER AUTO_INCREMENT PRIMARY KEY, f2 INTEGER) ENGINE=InnoDB;
14
15 INSERT INTO t1 (f2) SELECT 1 FROM ten;
16
17 --connection node_2
18 INSERT INTO t1 (f2) SELECT 1 FROM ten;
19
20 ALTER TABLE t1 AUTO_INCREMENT = 1000;
21 INSERT INTO t1 (f2) SELECT 1 FROM ten;
22
23 --connection node_1
24 INSERT INTO t1 (f2) SELECT 1 FROM ten;
25
26 SELECT MIN(f1) >= 1000, COUNT(*) = 20, COUNT(DISTINCT f1) = 20 FROM t1 WHERE f1 >= 1000;
27
28 --connection node_2
29 SELECT MIN(f1) >= 1000, COUNT(*) = 20, COUNT(DISTINCT f1) = 20 FROM t1 WHERE f1 >= 1000;
30
31 #
32 # AUTO_INCREMENT set to a value lower than the current one.
33 # The ALTER does nothing, the sequence continues from the current maximum.
34 #
35
36 --connection node_1
37 ALTER TABLE t1 AUTO_INCREMENT = 5;
38 INSERT INTO t1 (f2) SELECT 1 FROM ten;
39
40 --connection node_2
41 INSERT INTO t1 (f2) SELECT 1 FROM ten;
42 SELECT MIN(f1) >= 1000, COUNT(*) = 40, COUNT(DISTINCT f1) = 40 FROM t1 WHERE f1 >= 1000;
43
44 --connection node_1
45 SELECT MIN(f1) >= 1000, COUNT(*) = 40, COUNT(DISTINCT f1) = 40 FROM t1 WHERE f1 >= 1000;
46
47 DROP TABLE t1;
48
49 #
50 # Under wsrep_auto_increment_control = OFF
51 #
52
53 --connection node_1
54 --let $auto_increment_control_orig = `SELECT @@wsrep_auto_increment_control`
55 --let $auto_increment_increment_node1 = `SELECT @@auto_increment_increment`
56 --let $auto_increment_offset_node1 = `SELECT @@auto_increment_offset`
57
58 # Restore stock MySQL defaults
59 SET GLOBAL wsrep_auto_increment_control = OFF;
60 SET GLOBAL auto_increment_increment = 1;
61 SET GLOBAL auto_increment_offset = 1;
62
63 #Open a fresh connection to node_1 so that the variables above take effect
64 --let $galera_connection_name = node_1a
65 --let $galera_server_number = 1
66 --source include/galera_connect.inc
67
68 --connection node_2
69 --let $auto_increment_increment_node2 = `SELECT @@auto_increment_increment`
70 --let $auto_increment_offset_node2 = `SELECT @@auto_increment_offset`
71
72 SET GLOBAL wsrep_auto_increment_control = OFF;
73 SET GLOBAL auto_increment_increment = 1;
74 SET GLOBAL auto_increment_offset = 1;
75
76 #Open a fresh connection to node_2
77 --let $galera_connection_name = node_2a
78 --let $galera_server_number = 2
79 --source include/galera_connect.inc
80
81 --connection node_1a
82
83 CREATE TABLE t1 (f1 INTEGER AUTO_INCREMENT PRIMARY KEY, f2 INTEGER) ENGINE=InnoDB;
84
85 --connection node_2a
86 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1'
87 --source include/wait_condition.inc
88
89 ALTER TABLE t1 AUTO_INCREMENT=100;
90
91 --connection node_1a
92 INSERT INTO t1 (f2) SELECT 1 FROM ten;
93
94 --connection node_2a
95 INSERT INTO t1 (f2) SELECT 1 FROM ten;
96
97 SELECT MIN(f1) = 100, MAX(f1) = 119, COUNT(f1) = 20, COUNT(DISTINCT f1) = 20 FROM t1;
98
99 --connection node_1a
100 SELECT MIN(f1) = 100, MAX(f1) = 119, COUNT(f1) = 20, COUNT(DISTINCT f1) = 20 FROM t1;
101
102 DROP TABLE t1;
103
104 #
105 # Restore all variables as they were
106 #
107
108 --disable_query_log
109
110 --connection node_1
111 --eval SET GLOBAL wsrep_auto_increment_control = $auto_increment_control_orig
112 --eval SET GLOBAL auto_increment_increment = $auto_increment_increment_node1
113 --eval SET GLOBAL auto_increment_offset = $auto_increment_offset_node1
114
115 --connection node_2
116 --eval SET GLOBAL wsrep_auto_increment_control = $auto_increment_control_orig
117 --eval SET GLOBAL auto_increment_increment = $auto_increment_increment_node2
118 --eval SET GLOBAL auto_increment_offset = $auto_increment_offset_node2
119
120 --enable_query_log
121
122 DROP TABLE ten;