Debian Codegraph @

mysql-test/suite/x/t/notices_enable.test

raw
1 ## This is test file for verification of enaling warning,password_expired,generated_insert_id,rows_affected,produced_message notices and their functionality
2
3 --source include/xplugin_preamble.inc
4 --source include/xplugin_create_user.inc
5
6 ## Test starts here
7 --let $xtest_result= $MYSQL_TMP_DIR/stmtexecute_query_result_mysqlx.xpl
8 --write_file $xtest_result
9 -->import assert_variable.macro
10
11 #
12 # This test verifies that :
13 #
14 # * Scenario 1: enabling group replication notices on non GR setup doesn't influence
15 # the message flow/test
16 # * Scenario 2: warnings generated by SQL execution are forwarded to X Client
17 # the testcase
18 #
19 # Generation of any GroupReplication notice would break the scenario 2, which
20 # executes a lot of queries (takes some time).
21 #
22
23 -->echo
24 -->echo ###
25 -->echo ### General setup for all scenarios
26 -->echo ###
27
28 # Print warnings into the result file
29 -->yeswarnings
30
31 # When there is a not expected warning, lets fail the test
32 -->fatalwarnings
33
34 -->echo
35 -->echo ### Scenario 1: Enable group replication notifications
36 -->echo ### Expected notices:
37 -->echo ### - Myself: N/A
38 -->echo ##########################################################################
39
40 stmtadmin enable_notices {"notice":["group_replication/membership/quorum_loss",
41 "group_replication/membership/view",
42 "group_replication/status/role_change",
43 "group_replication/status/state_change"]};
44 -->recvresult
45
46 -->echo
47 -->echo ### Scenario 2: Execute SQL queries that generate warning notices
48 -->echo ### Expected notices:
49 -->echo ### - Myself: Warnings
50 -->echo ##########################################################################
51
52 -->echo
53 -->echo ##
54 -->echo ## Check with dropping db which doesn't exists
55 -->expectwarnings ER_DB_DROP_EXISTS
56 drop database if exists mysqltest;
57
58 -->echo
59 -->echo ##
60 -->echo ## Check with dropping table which doesn't exists
61 -->expectwarnings ER_BAD_TABLE_ERROR
62 drop table if exists test.mysqltest;
63
64 -->echo
65 -->echo ##
66 -->echo ## Check with show variables like version
67 show variables like 'versio';
68
69 -->echo
70 -->echo ##
71 -->echo ## Check with dropping procedure which doesn't exists
72 -->expectwarnings ER_SP_DOES_NOT_EXIST
73 drop procedure if exists test.test_invalid;
74
75 -->echo
76 -->echo ##
77 -->echo ## Check with dropping function which doesn't exists
78 -->expectwarnings ER_SP_DOES_NOT_EXIST
79 drop function if exists test.test_signal_func;
80
81 -->echo
82 -->echo ##
83 -->echo ## Check with dropping view which doesn't exists
84 -->expectwarnings ER_BAD_TABLE_ERROR
85 drop view if exists test.v1;
86
87 -->echo
88 -->echo ##
89 -->echo ## Out of range values for INT,UNSIGN INT
90 create table test.t1 (c1 INT, c2 INT UNSIGNED);
91
92 -->expectwarnings ER_WARN_DATA_OUT_OF_RANGE
93 insert ignore into test.t1 values ('21474836461','21474836461');
94
95 -->expectwarnings ER_WARN_DATA_OUT_OF_RANGE
96 insert ignore into test.t1 values ('-21474836461','-21474836461');
97
98 drop table test.t1;
99
100 -->echo
101 -->echo ##
102 -->echo ## Checking double data type
103 create table test.t1 (grp int, a bigint unsigned, c char(10) not null, d char(10) not null, index grp_idx(grp));
104 -->sql
105 insert into test.t1 values (1,1,"a","a");
106 insert into test.t1 values (2,2,"b","a");
107 insert into test.t1 values (2,3,"c","b");
108 insert into test.t1 values (3,4,"E","a");
109 insert into test.t1 values (3,5,"C","b");
110 insert into test.t1 values (3,6,"D","b");
111 insert into test.t1 values (3,7,"d","d");
112 insert into test.t1 values (3,8,"d","d");
113 insert into test.t1 values (3,9,"D","c");
114 -->endsql
115
116 -->expectwarnings ER_TRUNCATED_WRONG_VALUE
117 select grp,group_concat(a order by a,d-ascii(c)-a) from test.t1 group by grp order by grp;
118
119 -->expectwarnings ER_TRUNCATED_WRONG_VALUE
120 select grp,group_concat(a order by c-ascii(c),a) from test.t1 group by grp order by grp;
121
122 set group_concat_max_len = 4;
123
124 -->expectwarnings ER_CUT_VALUE_GROUP_CONCAT
125 select grp,group_concat(c order by c) from test.t1 group by grp order by grp;
126
127 drop table test.t1;
128
129
130 -->echo
131 -->echo ##
132 -->echo ## Checking character data type
133 -->echo ## Error is returned instead of warning for insert operation with xplugin
134 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
135
136 -->expectwarnings ER_DEPRECATED_UTF8_ALIAS
137 prepare stmt from 'create table test.t1 (a varchar(10) character set utf8)';
138 execute stmt;
139
140 -->expectwarnings WARN_DATA_TRUNCATED
141 insert into test.t1 (a) values (repeat('a', 20));
142 select length(a) from test.t1;
143 drop table test.t1;
144 execute stmt;
145
146 -->echo
147 -->echo ##
148 -->echo ## Checking directly from IS tables
149 show session variables like 'max_join_size';
150 show session variables like 'concurrent_insert';
151 show session variables like 'default_storage_engine';
152
153 -->echo
154 -->echo ##
155 -->echo ## Checking errors and warnings with duplicate table
156 SET SQL_WARNINGS=1;
157
158 -->echo
159 -->echo ##
160 -->echo ## Checking errors and warnings with duplicate table names
161 create table test.dup (a int);
162
163 -->expecterror ER_TABLE_EXISTS_ERROR
164 create table test.dup (a int);
165 show count(*) errors;
166 show errors;
167 drop table test.dup;
168
169 -->echo
170 -->echo ##
171 -->echo ## Checking creating table with if not exists
172 flush status;
173 use test;
174
175 -->expecterror ER_TABLE_EXISTS_ERROR
176 create table t1 (a int not null, b int, primary key (a));
177
178 -->expectwarnings ER_TABLE_EXISTS_ERROR
179 create table if not exists t1 select 2;
180 select * from t1;
181
182 -->expectwarnings ER_TABLE_EXISTS_ERROR
183 create table if not exists t1 select 3 as 'a',4 as 'b';
184
185 show status like "Opened_tables";
186 select * from t1;
187 drop table t1;
188
189 -->echo
190 -->echo ##
191 -->echo ## Try dropping multiple tables with if exists and check multiple warnings
192 use test;
193
194 -->expectwarnings ER_BAD_TABLE_ERROR
195 drop tables if exists t1, t2;
196
197 -->echo
198 -->echo ##
199 -->echo ## Try checking many warnings
200 -->expectwarnings ER_BAD_TABLE_ERROR
201 drop tables if exists t1, t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,a1, a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21,a22,a23,a24,a25,a26,a27,a28,a29,a30,aa1, aa2,aa3,aa4,aa5,aa6,aa7,aa8,aa9,aa10,aa11,aa12,aa13,aa14,aa15,aa16,aa17,aa18,aa19,aa20,aa21,aa22,aa23,aa24,aa25,aa26,aa27,aa28,aa29,aa30,aaaa1, aaaa2,aaaa3,aaaa4,aaaa5,aaaa6,aaaa7,aaaa8,aaaa9,aaaa10,aaaa11,aaaa12,aaaa13,aaaa14,aaaa15,aaaa16,aaaa17,aaaa18,aaaa19,aaaa20,aaaa21,aaaa22,aaaa23,aaaa24,aaaa25,aaaa26,aaaa27,aaaa28,aaaa29,aaaa30;
202
203 -->echo
204 -->echo ##
205 -->echo ## Dropping same table with if exists and observe error/warnings
206 -->expecterror ER_NONUNIQ_TABLE
207 drop tables if exists t1,t1;
208 set max_error_count=65535;
209
210 -->echo
211 -->echo ##
212 -->echo ## Check more than 64 warnings returned
213 -->expectwarnings ER_BAD_TABLE_ERROR
214 drop tables if exists t1, t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,a1, a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21,a22,a23,a24,a25,a26,a27,a28,a29,a30,aa1, aa2,aa3,aa4,aa5,aa6,aa7,aa8,aa9,aa10,aa11,aa12,aa13,aa14,aa15,aa16,aa17,aa18,aa19,aa20,aa21,aa22,aa23,aa24,aa25,aa26,aa27,aa28,aa29,aa30,aaaa1, aaaa2,aaaa3,aaaa4,aaaa5,aaaa6,aaaa7,aaaa8,aaaa9,aaaa10,aaaa11,aaaa12,aaaa13,aaaa14,aaaa15,aaaa16,aaaa17,aaaa18,aaaa19,aaaa20,aaaa21,aaaa22,aaaa23,aaaa24,aaaa25,aaaa26,aaaa27,aaaa28,aaaa29,aaaa30,bb1, bb2,bb3,bb4,bb5,bb6,bb7,bb8,bb9,bb10,bb11,bb12,bb13,bb14,bb15,bb16,bb17,bb18,bb19,bb20,bb21,bb22,bb23,bb24,bb25,bb26,bb27,bb28,bb29,bb30,b1, b2,b3,b4,b5,b6,b7,b8,b9,b10,b11,b12,b13,b14,b15,b16,b17,b18,b19,b20,b21,b22,b23,b24,b25,b26,b27,b28,b29,b30;
215
216 -->echo
217 -->echo ##
218 -->echo ## Check no warnings based on max_error_count parameter set
219 set max_error_count=0;
220 #Following warning is not generated because of max_error_count set to 0
221 #-->expectwarnings ER_BAD_TABLE_ERROR
222 drop tables if exists t1,t2;
223 #Following warning is not generated because of max_error_count set to 0
224 #-->expectwarnings ER_TRUNCATED_WRONG_VALUE
225 set max_error_count=-64;
226 show variables like 'max_error_count';
227 set max_error_count=64;
228
229 -->echo
230 -->echo ##
231 -->echo ## Checking creating temporary table when normal and temp table exists
232 use test;
233 create table if not exists t1 (a int) select 2 as a;
234 create temporary table t1 (a int) select 2 as a;
235 select * from t1;
236
237 -->expectwarnings ER_TABLE_EXISTS_ERROR
238 create temporary table if not exists t1 (a int) select 2 as a;
239 select * from t1;
240 drop temporary table t1;
241 drop table t1;
242
243 -->echo
244 -->echo ##
245 -->echo ## Checking data truncation warnings for default values
246 use test;
247
248 -->expectwarnings WARN_DATA_TRUNCATED
249 CREATE TABLE t1 SELECT 0.123456789012345678901234567890123456 AS f1;
250 drop table t1;
251
252 -->echo
253 -->echo ##
254 -->echo ## Checking data truncation warnings while creating table with expression
255 use test;
256
257 -->expectwarnings WARN_DATA_TRUNCATED
258 create table t1 as select 5.05 / 0.014;
259
260 drop table t1;
261
262 -->echo
263 -->echo ##
264 -->echo ## Checking warnings with insert and select operations
265 -->echo ## Below insert into t1 returns 17775 warnings,checking only limited warnings as test takes more time to render the warnings
266 use test;
267 create table t2 (a char(1));
268 insert into t2 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7');
269 insert into t2 values ('8'),('9'),('A'),('B'),('C'),('D'),('E'),('F');
270 create table t1 (a varchar(2) character set cp932) engine=myisam;
271
272 -->echo
273 -->echo ##
274 -->echo ## Checking for more than default 64 warnings
275 set max_error_count=500;
276
277 -->expectwarnings ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
278 ## Add an ORDER BY so that the warnings are printed in a deterministic order.
279 insert into t1 select unhex(concat(t24.a, t23.a, t22.a, t21.a)) from t2 t21, t2 t22, t2 t23, t2 t24
280 ORDER BY t24.a, t23.a, t22.a, t21.a;
281 set max_error_count=64;
282 drop tables t1,t2;
283
284 -->echo
285 -->echo ##
286 -->echo ## Checking warnings with prepare and execute statements
287 use test;
288 CREATE TABLE t1(f1 VARCHAR(6) NOT NULL, FULLTEXT KEY(f1), UNIQUE(f1));
289 INSERT INTO t1 VALUES ('test');
290
291 -->expectwarnings ER_TRUNCATED_WRONG_VALUE
292 SELECT 1 FROM t1 WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) against ("")) WHERE t1.f1 GROUP BY t1.f1)) xor f1;
293 PREPARE stmt FROM 'SELECT 1 FROM t1 WHERE 1 > ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a ON (MATCH(t1.f1) against ("")) WHERE t1.f1 GROUP BY t1.f1)) xor f1';
294
295 -->expectwarnings ER_TRUNCATED_WRONG_VALUE
296 EXECUTE stmt;
297
298 -->expectwarnings ER_TRUNCATED_WRONG_VALUE
299 EXECUTE stmt;
300 DEALLOCATE PREPARE stmt;
301 PREPARE stmt FROM 'SELECT 1 FROM t1 WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) against ("")) WHERE t1.f1 GROUP BY t1.f1))';
302 EXECUTE stmt;
303 EXECUTE stmt;
304 DEALLOCATE PREPARE stmt;
305 drop table t1;
306
307 -->echo
308 -->echo ##
309 -->echo ## Checking warnings with group by when sql_mode is no_engine_substitution
310 use test;
311 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
312 create table t1 (c1 char(3), c2 char(3));
313 create table t2 (c3 char(3), c4 char(3));
314 insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
315 insert into t2 values ('aaa', 'bb1'), ('aaa', 'bb2');
316
317 -->expectwarnings ER_NON_UNIQ_ERROR
318 select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4 group by c2;
319 select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4 group by t1.c1;
320 drop table t1, t2;
321
322 -->echo
323 -->echo ##
324 -->echo ## Setting back to default of sql_mode
325 -->expectwarnings ER_SQL_MODE_MERGED
326 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
327
328 -->echo
329 -->echo ##
330 -->echo ## Checking with group by where both error and warnings expected with default sql_mode
331 use test;
332 create table t1 (c1 char(3), c2 char(3));
333 create table t2 (c3 char(3), c4 char(3));
334 insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
335 insert into t2 values ('aaa', 'bb1'), ('aaa', 'bb2');
336 -->expecterror ER_WRONG_FIELD_WITH_GROUP
337 -->expectwarnings ER_NON_UNIQ_ERROR
338 select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4 group by c2;
339
340
341 -->echo
342 -->echo ##
343 -->echo ## Expect both error and warning
344 select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4 group by t1.c1;
345 drop table t1, t2;
346
347 -->echo
348 -->echo ##
349 -->echo ## Checking warnings with rollback on non-transactional tables
350 use test;
351 create table t1 (n int not null primary key) engine=myisam;
352
353 begin work;
354 insert into t1 values (4);
355 insert into t1 values (5);
356
357 -->expectwarnings ER_WARNING_NOT_COMPLETE_ROLLBACK
358 rollback;
359 show warnings;
360 show errors;
361 select @@warning_count,@@error_count;
362 select * from t1;
363 show warnings;
364 select @@warning_count;
365 drop table t1;
366
367 -->echo
368 -->echo ##
369 -->echo ## Expect 2 warnings for insert related to truncation and incorrect date value
370 -->sql
371 use test;
372 set sql_mode='';
373 create table t1 (
374 a date not null,
375 b mediumtext generated always as ((a not in (a,a))) virtual,
376 c timestamp generated always as ((a not in (b,b))) stored not null
377 );
378 -->endsql
379
380 -->expectwarnings WARN_DATA_TRUNCATED,ER_TRUNCATED_WRONG_VALUE
381 insert t1(a) values(7777777777);
382
383 drop table t1;
384 -->expectwarnings ER_SQL_MODE_MERGED
385 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
386
387 -->echo
388 -->echo ##
389 -->echo ## Checking warnings while creating index which is more than row size
390 -->sql
391 use test;
392 create table worklog5743_1(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=1;
393 create table worklog5743_2(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=2;
394 create table worklog5743_4(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=4;
395 create table worklog5743_8(a1 int, a2 TEXT, a3 TEXT) KEY_BLOCK_SIZE=8;
396 -->endsql
397
398 -->echo
399 -->echo ##
400 -->echo ## Below statement throws warning as Error and actual error is missing
401 -->echo ## Change result file once Bug#21529270 fixed
402 -->expecterror ER_TOO_LONG_KEY
403 create index idx1 on worklog5743_1(a2(4000));
404
405 -->expecterror ER_UNKNOWN_SYSTEM_VARIABLE
406 set global innodb_large_prefix=1;
407
408 ## setting back to original value
409 drop table worklog5743_1;
410 drop table worklog5743_2;
411 drop table worklog5743_4;
412 drop table worklog5743_8;
413
414 -->echo
415 -->echo ##
416 -->echo ## Checking warnings with different innodb file format
417 use test;
418 set innodb_strict_mode = off;
419 set global innodb_file_per_table = 0;
420
421 -->expectwarnings ER_ILLEGAL_HA_CREATE_OPTION
422 create temporary table t (i int) engine = innodb row_format = compressed;
423
424 -->sql
425 ## setting back to original value
426 drop table t;
427 set innodb_strict_mode = on;
428 set @@global.innodb_file_per_table = 1;
429 -->endsql
430
431 -->echo
432 -->echo ##
433 -->echo ## Checking warnings with different innodb row format
434 use test;
435
436 -->echo
437 -->echo ##
438 -->echo ## Expect one Error and Warning
439 -->expecterror ER_ILLEGAL_HA
440 -->expectwarnings ER_ILLEGAL_HA_CREATE_OPTION
441 create table t2 (id int primary key) engine = innodb key_block_size = 9;
442
443 -->echo
444 -->echo ##
445 -->echo ## Expect 2 warnings and 1 Error
446 -->expecterror ER_ILLEGAL_HA
447 -->expectwarnings ER_ILLEGAL_HA_CREATE_OPTION
448 create table t2 (id int primary key) engine = innodb key_block_size = 9 row_format = redundant;
449
450 -->echo
451 -->echo ##
452 -->echo ## Expect 1 warning and 2 Error
453 -->expecterror ER_WRONG_NAME_FOR_INDEX
454 -->expectwarnings ER_WRONG_NAME_FOR_INDEX, ER_GET_ERRNO
455 create table bug46000(`id` int, key `GEN_clust_INDEX`(`id`))engine=innodb;
456
457 -->echo
458 -->echo ##
459 -->echo ## Check with show variables like versio and expect warning related to GTID session
460 show variables like 'versio';
461
462 -->echo
463 -->echo ##
464 -->echo ## Check with show variables like versio and expect warning related to GTID session
465 show session variables like 'max_join_size';
466 show session variables like 'concurrent_insert';
467 show session variables like 'default_storage_engine';
468
469 -->echo
470 -->echo ##
471 -->echo ## Verify the notices statistics
472 -->quiet
473
474 -->callmacro Assert_received_notices
475 -->callmacro Assert_status_variable Mysqlx_stmt_disable_notices 0
476 -->callmacro Assert_status_variable Mysqlx_stmt_enable_notices 1
477 -->callmacro Assert_status_variable Mysqlx_stmt_list_notices 0
478 EOF
479
480
481 --let $xtest_warnings= $MYSQL_TMP_DIR/stmtexecute_proc_warnings_mysqlx.xpl
482 --write_file $xtest_warnings
483 -->import assert_variable.macro
484 -->quiet
485 -->echo checking warnings with procedure execution
486 -->sql
487 SET @start_global_value = @@global.max_error_count;
488 SELECT @start_global_value;
489 SET @start_session_value = @@session.max_error_count;
490 SELECT @start_session_value;
491 drop database if exists demo;
492 create database demo;
493 use demo;
494 delimiter //
495 create procedure proc_1()
496 begin
497 declare exit handler for sqlexception
498 resignal sqlstate '45000' set message_text='Oops in proc_1';
499 call proc_2();
500 end//
501
502 delimiter //
503 create procedure proc_2()
504 begin
505 declare exit handler for sqlexception
506 resignal sqlstate '45000' set message_text='Oops in proc_2';
507 call proc_3();
508 end//
509
510 delimiter //
511 create procedure proc_3()
512 begin
513 declare exit handler for sqlexception
514 resignal sqlstate '45000' set message_text='Oops in proc_3';
515 call proc_4();
516 end//
517
518 delimiter //
519 create procedure proc_4()
520 begin
521 declare exit handler for sqlexception
522 resignal sqlstate '45000' set message_text='Oops in proc_4';
523 call proc_5();
524 end//
525
526 delimiter //
527 create procedure proc_5()
528 begin
529 declare exit handler for sqlexception
530 resignal sqlstate '45000' set message_text='Oops in proc_5';
531 call proc_6();
532 end//
533
534 delimiter //
535 create procedure proc_6()
536 begin
537 declare exit handler for sqlexception
538 resignal sqlstate '45000' set message_text='Oops in proc_6';
539 call proc_7();
540 end//
541
542 delimiter //
543 create procedure proc_7()
544 begin
545 declare exit handler for sqlexception
546 resignal sqlstate '45000' set message_text='Oops in proc_7';
547 call proc_8();
548 end//
549
550 delimiter //
551 create procedure proc_8()
552 begin
553 declare exit handler for sqlexception
554 resignal sqlstate '45000' set message_text='Oops in proc_8';
555 call proc_9();
556 end//
557
558 delimiter //
559 create procedure proc_9()
560 begin
561 declare exit handler for sqlexception
562 resignal sqlstate '45000' set message_text='Oops in proc_9';
563 ## Do something that fails, to see how errors are reported
564 drop table oops_it_is_not_here;
565 end//
566
567 -->endsql
568
569 -->stmtsql call proc_1();
570 -->expecterror ER_SIGNAL_EXCEPTION
571 -->recvresult
572
573 -->sql
574 SET @@session.max_error_count = 5;
575 SELECT @@session.max_error_count;
576 -->endsql
577
578 -->stmtsql call proc_1();
579 -->expecterror ER_SIGNAL_EXCEPTION
580 -->recvresult
581
582 -->sql
583 SET @@session.max_error_count = 7;
584 SELECT @@session.max_error_count;
585 -->endsql
586
587 -->stmtsql call proc_1();
588 -->expecterror ER_SIGNAL_EXCEPTION
589 -->recvresult
590
591 -->sql
592 SET @@session.max_error_count = 9;
593 SELECT @@session.max_error_count;
594 -->endsql
595
596 -->stmtsql call proc_1();
597 -->expecterror ER_SIGNAL_EXCEPTION
598 -->recvresult
599
600 -->sql
601 drop database demo;
602 SET @@global.max_error_count = @start_global_value;
603 SELECT @@global.max_error_count;
604 SET @@session.max_error_count = @start_session_value;
605 SELECT @@session.max_error_count;
606 -->endsql
607
608 -->echo checking warnings while creating index with more than row size
609 -->sql
610 use test;
611 create table worklog5743_1(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=1;
612 create table worklog5743_2(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=2;
613 create table worklog5743_4(a1 int, a2 TEXT not null) KEY_BLOCK_SIZE=4;
614 create table worklog5743_8(a1 int, a2 TEXT, a3 TEXT) KEY_BLOCK_SIZE=8;
615 -->endsql
616
617
618 -->echo below statement throwing warning as an Error and actual error is missing
619 -->echo change result file once Bug#21529270 is fixed
620 -->stmtsql create index idx1 on worklog5743_1(a2(4000));
621 -->expecterror ER_TOO_LONG_KEY
622 -->recvresult
623
624 -->sql
625 ## setting back to original value
626 drop table worklog5743_1;
627 drop table worklog5743_2;
628 drop table worklog5743_4;
629 drop table worklog5743_8;
630 -->endsql
631
632 -->callmacro Assert_received_notices
633 -->callmacro Assert_status_variable Mysqlx_stmt_disable_notices 0
634 -->callmacro Assert_status_variable Mysqlx_stmt_enable_notices 0
635 -->callmacro Assert_status_variable Mysqlx_stmt_list_notices 0
636 EOF
637
638 --let $xtest_enable_notice= $MYSQL_TMP_DIR/enable_notice_mysqlx.xpl
639 --write_file $xtest_enable_notice
640 -->import assert_status_variable.macro
641 -->quiet
642 -->echo Try enable warnings
643 -->stmtadmin enable_notices {"notice":"warnings"}
644 -->recvresult
645
646 -->echo Try enable account_expired
647 -->stmtadmin enable_notices {"notice":"account_expired"}
648 -->recvresult
649
650 -->echo Try enable generated_insert_id
651 -->stmtadmin enable_notices {"notice":"generated_insert_id"}
652 -->recvresult
653
654 -->echo Try enable rows_affected
655 -->stmtadmin enable_notices {"notice":"rows_affected"}
656 -->recvresult
657
658 -->echo Try enable produced_message
659 -->stmtadmin enable_notices {"notice":"produced_message"}
660 -->recvresult
661
662 -->callmacro Assert_received_notices
663 -->callmacro Assert_status_variable Mysqlx_stmt_disable_notices 0
664 -->callmacro Assert_status_variable Mysqlx_stmt_enable_notices 5
665 -->callmacro Assert_status_variable Mysqlx_stmt_list_notices 0
666 EOF
667
668 --let $xtest_multiple_notices= $MYSQL_TMP_DIR/enable_multiple_notices_mysqlx.xpl
669 --write_file $xtest_multiple_notices
670 -->import assert_status_variable.macro
671 -->quiet
672 -->echo Enable multiple notices both read-only and read-write with single StmtExecute,expect success
673 -->stmtadmin enable_notices {"notice":["warnings","account_expired","generated_insert_id","rows_affected","produced_message"]}
674 -->recvresult
675
676 -->stmtadmin list_notices
677 -->recvresult
678
679 -->callmacro Assert_received_notices
680 -->callmacro Assert_status_variable Mysqlx_stmt_disable_notices 0
681 -->callmacro Assert_status_variable Mysqlx_stmt_enable_notices 1
682 -->callmacro Assert_status_variable Mysqlx_stmt_list_notices 1
683 EOF
684
685 --let $xtest_stats= $MYSQL_TMP_DIR/stats_notices_mysqlx.xpl
686 --write_file $xtest_stats
687 -->import assert_status_variable.macro
688 -->quiet
689
690 -->sql
691 use mysql;
692 -->endsql
693
694 -->stmtsql drop tables if exists t1;
695 -->recvresult
696
697 -->echo expect Mysqlx_notice_other_sent=8 and Mysqlx_notice_warning_sent=3
698 -->echo seems like some problem with these stats, need to change values once issues fixed
699
700 -->echo disable warnings notice and observe stats
701 -->stmtadmin disable_notices {"notice": "warnings"}
702 -->recvresult
703
704 -->echo enable account_expired notice and check stats
705 -->stmtadmin enable_notices {"notice":"warnings"}
706 -->recvresult
707
708 -->echo enable account_expired notice and check stats
709 -->stmtadmin enable_notices {"notice":"account_expired"}
710 -->recvresult
711
712 -->echo listing notices after enabling warnings,account_expired notices
713 -->stmtadmin list_notices
714 -->recvresult
715
716 -->echo listing notices with mixed case
717 -->stmtadmin LIST_notices
718 -->recvresult
719
720 -->callmacro Assert_received_notices
721 -->callmacro Assert_status_variable Mysqlx_stmt_disable_notices 1
722 -->callmacro Assert_status_variable Mysqlx_stmt_enable_notices 2
723 -->callmacro Assert_status_variable Mysqlx_stmt_list_notices 2
724 EOF
725
726 --let $xtest_stats_noti= $MYSQL_TMP_DIR/stats_noti_mysqlx.xpl
727 --write_file $xtest_stats_noti
728 -->import assert_status_variable.macro
729 -->quiet
730 -->expecterror ER_DBACCESS_DENIED_ERROR
731 -->sql
732 use mysql;
733 -->endsql
734
735 -->stmtsql drop tables if exists t1;
736 -->expecterror ER_NO_DB_ERROR
737 -->recvresult
738
739 -->callmacro Assert_received_notices
740 -->callmacro Assert_status_variable Mysqlx_stmt_disable_notices 0
741 -->callmacro Assert_status_variable Mysqlx_stmt_enable_notices 0
742 -->callmacro Assert_status_variable Mysqlx_stmt_list_notices 0
743 EOF
744
745 --let $xtest_stats_noti_no_err= $MYSQL_TMP_DIR/stats_noti_no_err_mysqlx.xpl
746 --write_file $xtest_stats_noti_no_err
747 -->import assert_status_variable.macro
748 -->quiet
749 -->sql
750 use mysql;
751 -->endsql
752
753 -->stmtsql drop tables if exists t1;
754 -->recvresult
755
756 -->callmacro Assert_received_notices
757 EOF
758
759
760 --exec $MYSQLXTEST -ux_root --password='' --file=$xtest_result 2>&1
761
762 ##change result file once Bug#21611216 is fixed
763 --exec $MYSQLXTEST -ux_root --password='' --file=$xtest_warnings 2>&1
764
765 ##try enable read-only notices
766 --exec $MYSQLXTEST -ux_root --password='' --file=$xtest_enable_notice 2>&1
767
768 ##try enable read-only notices with normal user
769 create user xplugin@localhost identified by 'xplugin';
770 --exec $MYSQLXTEST -uxplugin --password='xplugin' --file=$xtest_enable_notice 2>&1
771
772 ## Enable multiple notices both read-only and read-write with single StmtExecute,expect success
773 --exec $MYSQLXTEST -ux_root --password='' --file=$xtest_multiple_notices 2>&1
774
775 ## checking notices related stats
776 --exec $MYSQLXTEST -ux_root --password='' --file=$xtest_stats 2>&1
777
778 ##verify MYSQLX_NOTICE_OTHER_SENT,MYSQLX_NOTICE_WARNING_SENT
779 let $restart_parameters = restart: ;
780 --source include/restart_mysqld.inc
781 --exec $MYSQLXTEST -ux_root --password='' --file=$xtest_stats_noti_no_err 2>&1
782
783 ##verify MYSQLX_NOTICE_OTHER_SENT,MYSQLX_NOTICE_WARNING_SENT with unprivileged user
784 let $restart_parameters = restart: ;
785 --source include/restart_mysqld.inc
786 create user noti@localhost identified by 'noti';
787 --exec $MYSQLXTEST -unoti --password='noti' --file=$xtest_stats_noti 2>&1
788
789 ## Cleanup
790 drop user noti@localhost;
791 drop user xplugin@localhost;
792 --source ../include/xplugin_cleanup.inc