Debian Codegraph @

mysql-test/suite/ndb_binlog/r/purger.result

raw
1 SET autocommit=1;
2 RESET MASTER;
3 # Find physical binlog file name format as it varies between platforms
4 CREATE TABLE check_binlog_name (a int primary key) engine = NDB;
5 INSERT INTO check_binlog_name VALUES (1);
6 DROP TABLE check_binlog_name;
7 RESET MASTER;
8 # =======================================
9 # 1. Showing configuration required by test
10 # =======================================
11 # Show that binlogging of NDB changes are on
12 SHOW VARIABLES LIKE 'log_bin';
13 Variable_name Value
14 log_bin ON
15 SHOW VARIABLES LIKE 'ndb_log_bin';
16 Variable_name Value
17 ndb_log_bin ON
18 # Turn off binlogging statements in this file
19 SET sql_log_bin = OFF;
20 # Turn off writing queries to query log
21 SET sql_log_off = ON;
22 # Turn off using backslash as escape to avoid interfering with filename
23 SET sql_mode=(SELECT CONCAT(@@sql_mode,',NO_BACKSLASH_ESCAPES'));
24 #
25 # InnoDB adaptive flushing on
26 adaptive_flushing: ON
27 #
28 # Innodb buffer pool size
29 buffer_pool_size: 24 MB
30 #
31 # InnoDB redo log capacity controls how much can be changed without flush
32 redo_log_capacity: 10 MB
33 #
34 # The ndb binlog purger rate limit
35 purge_rate: 8192
36 #
37 # Show primary keys of mysql.ndb_binlog_index table
38 SELECT COLUMN_NAME, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS
39 WHERE TABLE_SCHEMA = 'mysql' AND
40 TABLE_NAME = 'ndb_binlog_index' AND
41 COLUMN_KEY = 'PRI'
42 ORDER BY ORDINAL_POSITION;
43 COLUMN_NAME COLUMN_TYPE
44 epoch bigint unsigned
45 orig_server_id int unsigned
46 orig_epoch bigint unsigned
47 #
48 # Show columns of mysql.ndb_binlog_index table
49 SELECT COLUMN_NAME, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS
50 WHERE TABLE_SCHEMA = 'mysql' AND
51 TABLE_NAME = 'ndb_binlog_index'
52 ORDER BY ORDINAL_POSITION;
53 COLUMN_NAME COLUMN_TYPE
54 Position bigint unsigned
55 File varchar(255)
56 epoch bigint unsigned
57 inserts int unsigned
58 updates int unsigned
59 deletes int unsigned
60 schemaops int unsigned
61 orig_server_id int unsigned
62 orig_epoch bigint unsigned
63 gci int unsigned
64 next_position bigint unsigned
65 next_file varchar(255)
66 # =======================================
67 # 2. Create 9 binary log files
68 # =======================================
69 FLUSH BINARY LOGS;
70 FLUSH BINARY LOGS;
71 FLUSH BINARY LOGS;
72 FLUSH BINARY LOGS;
73 FLUSH BINARY LOGS;
74 FLUSH BINARY LOGS;
75 FLUSH BINARY LOGS;
76 FLUSH BINARY LOGS;
77 FLUSH BINARY LOGS;
78 SHOW BINARY LOGS;
79 Log_name File_size Encrypted
80 binlog.000001 201 No
81 binlog.000002 201 No
82 binlog.000003 201 No
83 binlog.000004 201 No
84 binlog.000005 201 No
85 binlog.000006 201 No
86 binlog.000007 201 No
87 binlog.000008 201 No
88 binlog.000009 201 No
89 binlog.000010 157 No
90 # =======================================
91 # 3. Add additional rows to ndb_binlog_index corresponding to the
92 # binary logs, the number of rows should exceed the configured
93 # ndb_log_rate_limit
94 # =======================================
95 SELECT COUNT(*) FROM mysql.ndb_binlog_index;
96 COUNT(*)
97 90000
98 SELECT COUNT(*), File FROM mysql.ndb_binlog_index GROUP BY File ORDER BY File;
99 COUNT(*) File
100 10000 BINLOG_NAME1
101 10000 BINLOG_NAME2
102 10000 BINLOG_NAME3
103 10000 BINLOG_NAME4
104 10000 BINLOG_NAME5
105 10000 BINLOG_NAME6
106 10000 BINLOG_NAME7
107 10000 BINLOG_NAME8
108 10000 BINLOG_NAME9
109 # =======================================
110 # 4. Purging the binary logs
111 # =======================================
112 #
113 # Remove the first binlog
114 PURGE BINARY LOGS TO 'binlog.000002';
115 # Check that rows for first binlog file is removed
116 SELECT COUNT(*) FROM mysql.ndb_binlog_index
117 WHERE File IN (CONCAT('BINLOG_NAME', 1));
118 COUNT(*)
119 0
120 #
121 # Remove the subsequent two binlogs
122 PURGE BINARY LOGS TO 'binlog.000004';
123 # Check that rows for the two purged binlog files has been removed
124 SELECT COUNT(*) FROM mysql.ndb_binlog_index
125 WHERE File IN (CONCAT('BINLOG_NAME', 2),
126 CONCAT('BINLOG_NAME', 3));
127 COUNT(*)
128 0
129 #
130 # Remove the subsequent five binlogs
131 PURGE BINARY LOGS TO 'binlog.000009';
132 # Check that rows for the five purged binlog files has been removed
133 SELECT COUNT(*) FROM mysql.ndb_binlog_index
134 WHERE File IN (CONCAT('BINLOG_NAME', 4),
135 CONCAT('BINLOG_NAME', 5),
136 CONCAT('BINLOG_NAME', 6),
137 CONCAT('BINLOG_NAME', 7),
138 CONCAT('BINLOG_NAME', 8));
139 COUNT(*)
140 0
141 # Still rows left for the last binlog file
142 SELECT COUNT(*), File FROM mysql.ndb_binlog_index GROUP BY File ORDER BY File;
143 COUNT(*) File
144 10000 BINLOG_NAME9
145 # Rotate to new binary log and purge the last binlog file with rows
146 FLUSH BINARY LOGS;
147 PURGE BINARY LOGS TO 'binlog.000010';
148 # Show status counters:
149 purged_rows: 90000
150 purged_files: 9
151 # =======================================
152 # 5. Check that all rows has been removed from ndb_binlog_index
153 # =======================================