Debian Codegraph @

mysql-test/suite/sys_vars/r/binlog_row_value_options_basic.result

raw
1 #### Initialize ####
2
3 SET @start_binlog_row_value_options = @@global.binlog_row_value_options;
4 SET @start_binlog_row_image = @@global.binlog_row_image;
5 SET @start_binlog_format = @@global.binlog_format;
6 Warnings:
7 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
8 # Default value
9 SELECT @start_binlog_row_value_options;
10 @start_binlog_row_value_options
11
12
13 #### Exists as session and global ####
14
15 # Avoid warnings
16 SET SESSION binlog_row_image = MINIMAL;
17 SET GLOBAL binlog_row_image = MINIMAL;
18 SET SESSION binlog_format = 'ROW';
19 Warnings:
20 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
21 SET GLOBAL binlog_format = 'ROW';
22 Warnings:
23 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
24 # Test
25 SELECT @@global.binlog_row_value_options;
26 @@global.binlog_row_value_options
27
28 SET @@global.binlog_row_value_options = PARTIAL_JSON;
29 SELECT @@binlog_row_value_options;
30 @@binlog_row_value_options
31
32 SET @@binlog_row_value_options= PARTIAL_JSON;
33 SELECT @@session.binlog_row_value_options;
34 @@session.binlog_row_value_options
35 PARTIAL_JSON
36 SET @@session.binlog_row_value_options = PARTIAL_JSON;
37
38 #### Valid and invalid values ####
39
40 SET @@global.binlog_row_value_options = PARTIAL_JSON,PARTIAL_JSON;
41 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
42 SET @@session.binlog_row_value_options = PARTIAL_JSON,PARTIAL_JSON;
43 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
44 SET @@global.binlog_row_value_options = 'PARTIAL_JSON';
45 SET @@session.binlog_row_value_options = 'PARTIAL_JSON';
46 SET @@global.binlog_row_value_options = 'PARTIAL_JSON,PARTIAL_JSON';
47 SET @@session.binlog_row_value_options = 'PARTIAL_JSON,PARTIAL_JSON';
48 SET @@global.binlog_row_value_options = '';
49 SET @@session.binlog_row_value_options = '';
50 SET @@global.binlog_row_value_options = 0;
51 SET @@session.binlog_row_value_options = 0;
52 SET @@global.binlog_row_value_options = 1;
53 SET @@session.binlog_row_value_options = 1;
54 SET @@global.binlog_row_value_options = DEFAULT;
55 SET @@session.binlog_row_value_options = DEFAULT;
56 SELECT @@global.binlog_row_value_options;
57 @@global.binlog_row_value_options
58
59 SELECT @@session.binlog_row_value_options;
60 @@session.binlog_row_value_options
61
62 SET @@global.binlog_row_value_options = 2;
63 ERROR 42000: Variable 'binlog_row_value_options' can't be set to the value of '2'
64 SET @@session.binlog_row_value_options = 2;
65 ERROR 42000: Variable 'binlog_row_value_options' can't be set to the value of '2'
66 SET @@global.binlog_row_value_options = 'invalid';
67 ERROR 42000: Variable 'binlog_row_value_options' can't be set to the value of 'invalid'
68 SET @@session.binlog_row_value_options = 'invalid';
69 ERROR 42000: Variable 'binlog_row_value_options' can't be set to the value of 'invalid'
70 SET @@global.binlog_row_value_options = NULL;
71 ERROR 42000: Variable 'binlog_row_value_options' can't be set to the value of 'NULL'
72 SET @@session.binlog_row_value_options = NULL;
73 ERROR 42000: Variable 'binlog_row_value_options' can't be set to the value of 'NULL'
74 SET @@global.binlog_row_value_options = ON;
75 ERROR 42000: Variable 'binlog_row_value_options' can't be set to the value of 'ON'
76 SET @@session.binlog_row_value_options = ON;
77 ERROR 42000: Variable 'binlog_row_value_options' can't be set to the value of 'ON'
78 SET @@global.binlog_row_value_options = OFF;
79 ERROR 42000: Variable 'binlog_row_value_options' can't be set to the value of 'OFF'
80 SET @@session.binlog_row_value_options = OFF;
81 ERROR 42000: Variable 'binlog_row_value_options' can't be set to the value of 'OFF'
82
83 #### Warnings for incompatible options ####
84
85 ---- Setting session or default value ----
86
87 SET @@global.binlog_row_value_options = '';
88 SET @@session.binlog_row_value_options = 'PARTIAL_JSON';
89
90 # warning for sql_log_bin=0
91 SET SESSION sql_log_bin = 0;
92 SET @@session.binlog_row_value_options = '';
93 SET @@session.binlog_row_value_options = 'PARTIAL_JSON';
94 Warnings:
95 Warning 3645 When the binary log is disabled, the option binlog_row_value_options=PARTIAL_JSON will be ignored and updates will be written in full format to binary log.
96 SET @@binlog_row_value_options = '';
97 SET @@binlog_row_value_options = 'PARTIAL_JSON';
98 Warnings:
99 Warning 3645 When the binary log is disabled, the option binlog_row_value_options=PARTIAL_JSON will be ignored and updates will be written in full format to binary log.
100 SET SESSION sql_log_bin = 1;
101
102 # warning for statement
103 SET SESSION binlog_format = statement;
104 Warnings:
105 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
106 Warning 3645 When binlog_format=STATEMENT, the option binlog_row_value_options=PARTIAL_JSON will be ignored and updates will be written in full format to binary log.
107 SET @@session.binlog_row_value_options = '';
108 SET @@session.binlog_row_value_options = 'PARTIAL_JSON';
109 Warnings:
110 Warning 3645 When binlog_format=STATEMENT, the option binlog_row_value_options=PARTIAL_JSON will be ignored and updates will be written in full format to binary log.
111 SET @@binlog_row_value_options = '';
112 SET @@binlog_row_value_options = 'PARTIAL_JSON';
113 Warnings:
114 Warning 3645 When binlog_format=STATEMENT, the option binlog_row_value_options=PARTIAL_JSON will be ignored and updates will be written in full format to binary log.
115
116 # no warning for mixed or row
117 SET SESSION binlog_format = mixed;
118 Warnings:
119 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
120 SET @@session.binlog_row_value_options = '';
121 SET @@session.binlog_row_value_options = 'PARTIAL_JSON';
122 SET @@binlog_row_value_options = '';
123 SET @@binlog_row_value_options = 'PARTIAL_JSON';
124 SET SESSION binlog_format = 'row';
125 Warnings:
126 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
127 SET @@session.binlog_row_value_options = '';
128 SET @@session.binlog_row_value_options = 'PARTIAL_JSON';
129 SET @@binlog_row_value_options = '';
130 SET @@binlog_row_value_options = 'PARTIAL_JSON';
131
132 # warning for full
133 SET SESSION binlog_row_image = FULL;
134 Warnings:
135 Warning 3647 When binlog_row_image=FULL, the option binlog_row_value_options=PARTIAL_JSON will be used only for the after-image. Full values will be written in the before-image, so the saving in disk space due to binlog_row_value_options is limited to less than 50%.
136 SET @@session.binlog_row_value_options = '';
137 SET @@session.binlog_row_value_options = 'PARTIAL_JSON';
138 Warnings:
139 Warning 3647 When binlog_row_image=FULL, the option binlog_row_value_options=PARTIAL_JSON will be used only for the after-image. Full values will be written in the before-image, so the saving in disk space due to binlog_row_value_options is limited to less than 50%.
140 SET @@binlog_row_value_options = '';
141 SET @@binlog_row_value_options = 'PARTIAL_JSON';
142 Warnings:
143 Warning 3647 When binlog_row_image=FULL, the option binlog_row_value_options=PARTIAL_JSON will be used only for the after-image. Full values will be written in the before-image, so the saving in disk space due to binlog_row_value_options is limited to less than 50%.
144
145 # no warning for NOBLOB or MINIMAL
146 SET SESSION binlog_row_image = NOBLOB;
147 SET @@session.binlog_row_value_options = '';
148 SET @@session.binlog_row_value_options = 'PARTIAL_JSON';
149 SET @@binlog_row_value_options = '';
150 SET @@binlog_row_value_options = 'PARTIAL_JSON';
151 SET SESSION binlog_row_image = MINIMAL;
152 SET @@session.binlog_row_value_options = '';
153 SET @@session.binlog_row_value_options = 'PARTIAL_JSON';
154 SET @@binlog_row_value_options = '';
155 SET @@binlog_row_value_options = 'PARTIAL_JSON';
156
157 # warning for old format
158 SET GLOBAL log_bin_use_v1_row_events = 1;
159 Warnings:
160 Warning 1287 '@@log_bin_use_v1_row_events' is deprecated and will be removed in a future release.
161 SET @@session.binlog_row_value_options = '';
162 SET @@session.binlog_row_value_options = 'PARTIAL_JSON';
163 Warnings:
164 Warning 3646 When binlog_row_value_options=PARTIAL_JSON, the option log_bin_use_v1_row_events=1 will be ignored and row events will be written in new format to binary log.
165
166 # no warning for new format
167 SET GLOBAL log_bin_use_v1_row_events = 0;
168 Warnings:
169 Warning 1287 '@@log_bin_use_v1_row_events' is deprecated and will be removed in a future release.
170 SET @@session.binlog_row_value_options = '';
171 SET @@session.binlog_row_value_options = 'PARTIAL_JSON';
172
173 ---- Setting global value ----
174
175 SET @@session.binlog_row_value_options = '';
176 SET @@global.binlog_row_value_options = 'PARTIAL_JSON';
177
178 # warning for statement
179 SET GLOBAL binlog_format = statement;
180 Warnings:
181 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
182 Warning 3645 When binlog_format=STATEMENT, the option binlog_row_value_options=PARTIAL_JSON will be ignored and updates will be written in full format to binary log.
183 SET @@global.binlog_row_value_options = '';
184 SET @@global.binlog_row_value_options = 'PARTIAL_JSON';
185 Warnings:
186 Warning 3645 When binlog_format=STATEMENT, the option binlog_row_value_options=PARTIAL_JSON will be ignored and updates will be written in full format to binary log.
187
188 # no warning for mixed or row
189 SET GLOBAL binlog_format = mixed;
190 Warnings:
191 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
192 SET @@global.binlog_row_value_options = '';
193 SET @@global.binlog_row_value_options = 'PARTIAL_JSON';
194 SET GLOBAL binlog_format = 'row';
195 Warnings:
196 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
197 SET @@global.binlog_row_value_options = '';
198 SET @@global.binlog_row_value_options = 'PARTIAL_JSON';
199
200 # warning for full
201 SET GLOBAL binlog_row_image = FULL;
202 Warnings:
203 Warning 3647 When binlog_row_image=FULL, the option binlog_row_value_options=PARTIAL_JSON will be used only for the after-image. Full values will be written in the before-image, so the saving in disk space due to binlog_row_value_options is limited to less than 50%.
204 SET @@global.binlog_row_value_options = '';
205 SET @@global.binlog_row_value_options = 'PARTIAL_JSON';
206 Warnings:
207 Warning 3647 When binlog_row_image=FULL, the option binlog_row_value_options=PARTIAL_JSON will be used only for the after-image. Full values will be written in the before-image, so the saving in disk space due to binlog_row_value_options is limited to less than 50%.
208
209 # no warning for NOBLOB or MINIMAL
210 SET GLOBAL binlog_row_image = NOBLOB;
211 SET @@global.binlog_row_value_options = '';
212 SET @@global.binlog_row_value_options = 'PARTIAL_JSON';
213 SET GLOBAL binlog_row_image = MINIMAL;
214 SET @@global.binlog_row_value_options = '';
215 SET @@global.binlog_row_value_options = 'PARTIAL_JSON';
216
217 # warning for old format
218 SET GLOBAL log_bin_use_v1_row_events = 1;
219 Warnings:
220 Warning 1287 '@@log_bin_use_v1_row_events' is deprecated and will be removed in a future release.
221 Warning 3646 When binlog_row_value_options=PARTIAL_JSON, the option log_bin_use_v1_row_events=1 will be ignored and row events will be written in new format to binary log.
222 SET @@global.binlog_row_value_options = '';
223 SET @@global.binlog_row_value_options = 'PARTIAL_JSON';
224 Warnings:
225 Warning 3646 When binlog_row_value_options=PARTIAL_JSON, the option log_bin_use_v1_row_events=1 will be ignored and row events will be written in new format to binary log.
226
227 # no warning for new format
228 SET GLOBAL log_bin_use_v1_row_events = 0;
229 Warnings:
230 Warning 1287 '@@log_bin_use_v1_row_events' is deprecated and will be removed in a future release.
231 SET @@global.binlog_row_value_options = '';
232 SET @@global.binlog_row_value_options = 'PARTIAL_JSON';
233
234 ---- Setting persistent value ----
235
236 SET @@session.binlog_row_value_options = '';
237 SET @@persist.binlog_row_value_options = 'PARTIAL_JSON';
238
239 # warning for statement
240 SET PERSIST binlog_format = statement;
241 Warnings:
242 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
243 Warning 3645 When binlog_format=STATEMENT, the option binlog_row_value_options=PARTIAL_JSON will be ignored and updates will be written in full format to binary log.
244 SET @@persist.binlog_row_value_options = '';
245 SET @@persist.binlog_row_value_options = 'PARTIAL_JSON';
246 Warnings:
247 Warning 3645 When binlog_format=STATEMENT, the option binlog_row_value_options=PARTIAL_JSON will be ignored and updates will be written in full format to binary log.
248
249 # no warning for mixed or row
250 SET PERSIST binlog_format = mixed;
251 Warnings:
252 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
253 SET @@persist.binlog_row_value_options = '';
254 SET @@persist.binlog_row_value_options = 'PARTIAL_JSON';
255 SET PERSIST binlog_format = 'row';
256 Warnings:
257 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
258 SET @@persist.binlog_row_value_options = '';
259 SET @@persist.binlog_row_value_options = 'PARTIAL_JSON';
260
261 # warning for full
262 SET PERSIST binlog_row_image = FULL;
263 Warnings:
264 Warning 3647 When binlog_row_image=FULL, the option binlog_row_value_options=PARTIAL_JSON will be used only for the after-image. Full values will be written in the before-image, so the saving in disk space due to binlog_row_value_options is limited to less than 50%.
265 SET @@persist.binlog_row_value_options = '';
266 SET @@persist.binlog_row_value_options = 'PARTIAL_JSON';
267 Warnings:
268 Warning 3647 When binlog_row_image=FULL, the option binlog_row_value_options=PARTIAL_JSON will be used only for the after-image. Full values will be written in the before-image, so the saving in disk space due to binlog_row_value_options is limited to less than 50%.
269
270 # no warning for NOBLOB or MINIMAL
271 SET PERSIST binlog_row_image = NOBLOB;
272 SET @@persist.binlog_row_value_options = '';
273 SET @@persist.binlog_row_value_options = 'PARTIAL_JSON';
274 SET PERSIST binlog_row_image = MINIMAL;
275 SET @@persist.binlog_row_value_options = '';
276 SET @@persist.binlog_row_value_options = 'PARTIAL_JSON';
277
278 #### Can't set in transaction, sp, sf, or trigger, or without root ####
279
280 ---- transaction ----
281
282 BEGIN;
283 SET GLOBAL binlog_row_value_options = '';
284 ERROR HY000: The system variable binlog_row_value_options cannot be set when there is an ongoing transaction.
285 SET GLOBAL binlog_row_value_options = 'PARTIAL_JSON';
286 ERROR HY000: The system variable binlog_row_value_options cannot be set when there is an ongoing transaction.
287 SET SESSION binlog_row_value_options = '';
288 ERROR HY000: The system variable binlog_row_value_options cannot be set when there is an ongoing transaction.
289 SET SESSION binlog_row_value_options = 'PARTIAL_JSON';
290 ERROR HY000: The system variable binlog_row_value_options cannot be set when there is an ongoing transaction.
291 COMMIT;
292
293 ---- SP ----
294
295 create procedure sp1 () set @@global.binlog_row_value_options = 'PARTIAL_JSON';
296 create procedure sp2 () set @@session.binlog_row_value_options = 'PARTIAL_JSON';
297 create procedure sp3 () set @@persist.binlog_row_value_options = 'PARTIAL_JSON';
298 create procedure sp4 () set @@binlog_row_value_options = 'PARTIAL_JSON';
299 call sp1();
300 ERROR HY000: The system variable binlog_row_value_options cannot be set in stored procedures.
301 call sp2();
302 ERROR HY000: The system variable binlog_row_value_options cannot be set in stored procedures.
303 call sp3();
304 ERROR HY000: The system variable binlog_row_value_options cannot be set in stored procedures.
305 call sp4();
306 ERROR HY000: The system variable binlog_row_value_options cannot be set in stored procedures.
307
308 ---- SF ----
309
310 create function sf1 () returns int begin set @@global.binlog_row_value_options = 'PARTIAL_JSON'; return 1; end;
311 create function sf2 () returns int begin set @@session.binlog_row_value_options = 'PARTIAL_JSON'; return 1; end;
312 create function sf3 () returns int begin set @@persist.binlog_row_value_options = 'PARTIAL_JSON'; return 1; end;
313 create function sf4 () returns int begin set @@binlog_row_value_options = 'PARTIAL_JSON'; return 1; end;
314 select sf1();
315 ERROR HY000: The system variable binlog_row_value_options cannot be set in stored functions or triggers.
316 select sf2();
317 ERROR HY000: The system variable binlog_row_value_options cannot be set in stored functions or triggers.
318 select sf3();
319 ERROR HY000: The system variable binlog_row_value_options cannot be set in stored functions or triggers.
320 select sf4();
321 ERROR HY000: The system variable binlog_row_value_options cannot be set in stored functions or triggers.
322
323 ---- Trigger ----
324
325 create table t1 (a int);
326 create table t2 (a int);
327 create table t3 (a int);
328 create table t4 (a int);
329 create trigger trig1 after insert on t1 for each row set @@global.binlog_row_value_options = 'PARTIAL_JSON';
330 create trigger trig2 before insert on t2 for each row set @@session.binlog_row_value_options = 'PARTIAL_JSON';
331 create trigger trig3 after insert on t3 for each row set @@persist.binlog_row_value_options = 'PARTIAL_JSON';
332 create trigger trig4 before insert on t4 for each row set @@binlog_row_value_options = 'PARTIAL_JSON';
333 insert into t1 values (1);
334 ERROR HY000: The system variable binlog_row_value_options cannot be set in stored functions or triggers.
335 insert into t2 values (1);
336 ERROR HY000: The system variable binlog_row_value_options cannot be set in stored functions or triggers.
337 insert into t3 values (1);
338 ERROR HY000: The system variable binlog_row_value_options cannot be set in stored functions or triggers.
339 insert into t4 values (1);
340 ERROR HY000: The system variable binlog_row_value_options cannot be set in stored functions or triggers.
341
342 ---- Non-super ----
343
344 CREATE USER user@localhost;
345 SET @@global.binlog_row_value_options = '';
346 ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
347 SET @@session.binlog_row_value_options = '';
348 ERROR 42000: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
349 SET @@persist.binlog_row_value_options = '';
350 ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
351 SET @@binlog_row_value_options = '';
352 ERROR 42000: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
353
354 #### Clean up ####
355
356 DROP USER user@localhost;
357 DROP TABLE t1;
358 DROP TABLE t2;
359 DROP TABLE t3;
360 DROP TABLE t4;
361 DROP FUNCTION sf1;
362 DROP FUNCTION sf2;
363 DROP FUNCTION sf3;
364 DROP FUNCTION sf4;
365 DROP PROCEDURE sp1;
366 DROP PROCEDURE sp2;
367 DROP PROCEDURE sp3;
368 DROP PROCEDURE sp4;
369 SET @@global.binlog_row_value_options = @start_binlog_row_value_options;
370 SET @@global.binlog_row_image = @start_binlog_row_image;
371 SET @@global.binlog_format = @start_binlog_format;
372 Warnings:
373 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
374 RESET PERSIST;