~drizzle-trunk/drizzle/development

1 by brian
clean slate
1
# Objects to test:
2
#   - stored procedures/functions;
3
#   - triggers;
4
#   - events;
5
#   - views;
6
#
7
# For stored routines:
8
#   - create a database with collation utf8_unicode_ci;
9
#   - create an object, which
10
#     - contains SP-var with explicit CHARSET-clause;
11
#     - contains SP-var without CHARSET-clause;
12
#     - contains text constant;
13
#     - has localized routine/parameter names;
14
#   - check:
15
#     - execute;
16
#     - SHOW CREATE output;
17
#     - SHOW output;
18
#     - SELECT FROM INFORMATION_SCHEMA output;
19
#   - alter database character set;
20
#   - change connection collation;
21
#   - check again;
22
#   - dump definition using mysqldump;
23
#   - drop object;
24
#   - restore object;
25
# 
26
27
###########################################################################
28
#
29
# NOTE: this file contains text in UTF8 and KOI8-R encodings.
30
#
31
###########################################################################
32
33
--source include/have_utf8.inc
34
--source include/have_cp866.inc
35
--source include/have_cp1251.inc
36
--source include/have_koi8r.inc
37
38
###########################################################################
39
40
set names utf8;
41
delimiter |;
42
43
###########################################################################
44
#
45
# * Views.
46
#
47
###########################################################################
48
49
--echo
50
--echo -------------------------------------------------------------------
51
--echo Views
52
--echo -------------------------------------------------------------------
53
--echo
54
55
#
56
# Preparation:
57
#
58
59
#   - Create database with fixed, pre-defined character set.
60
61
--disable_warnings
62
DROP DATABASE IF EXISTS mysqltest1|
63
--enable_warnings
64
65
CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|
66
67
use mysqltest1|
68
69
CREATE TABLE t1(кПл INT)|
70
INSERT INTO t1 VALUES(1)|
71
72
#   - Create views;
73
74
--echo
75
76
CREATE VIEW v1 AS
77
  SELECT 'тест' AS c1, кПл AS c2
78
  FROM t1|
79
80
--echo
81
82
CREATE VIEW v2 AS SELECT _koi8r'ÔÅÓÔ' as c1|
83
84
--echo
85
86
CREATE VIEW v3 AS SELECT _koi8r'ÔÅÓÔ'|
87
88
--echo
89
90
#
91
# First-round checks.
92
#
93
94
--source include/ddl_i18n.check_views.inc
95
96
#
97
# Change running environment (alter database character set, change session
98
# variables).
99
#
100
101
--echo
102
--echo
103
104
ALTER DATABASE mysqltest1 COLLATE cp866_general_ci|
105
106
#
107
# Second-round checks:
108
#
109
110
#   - Change connection to flush cache;
111
112
--connect (con2,localhost,root,,)
113
--echo
114
--echo ---> connection: con2
115
116
#   - Switch environment variables and trigger loading views;
117
118
SET @@character_set_client= cp1251|
119
SET @@character_set_results= cp1251|
120
SET @@collation_connection= cp1251_general_ci|
121
122
--disable_result_log
123
SELECT * FROM mysqltest1.v1|
124
SELECT * FROM mysqltest1.v2|
125
SELECT * FROM mysqltest1.v3|
126
--enable_result_log
127
128
use mysqltest1|
129
130
#   - Restore environment;
131
132
set names utf8|
133
134
#   - Check!
135
136
--source include/ddl_i18n.check_views.inc
137
138
#
139
# Check mysqldump.
140
#
141
142
#  - Dump mysqltest1;
143
144
--echo
145
--echo ---> Dumping mysqltest1 to ddl_i18n_utf8views.mysqltest1.sql
146
147
--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --databases mysqltest1 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8views.mysqltest1.sql
148
149
#   - Clean mysqltest1;
150
151
--echo
152
--echo
153
154
DROP DATABASE mysqltest1|
155
156
#   - Restore mysqltest1;
157
158
--echo
159
--echo
160
161
--echo ---> Restoring mysqltest1...
162
--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8views.mysqltest1.sql
163
164
#
165
# Third-round checks.
166
#
167
168
#   - Change connection to flush cache;
169
170
--connect (con3,localhost,root,,)
171
--echo
172
--echo ---> connection: con3
173
174
#   - Switch environment variables and trigger loading views;
175
176
SET @@character_set_client= cp1251|
177
SET @@character_set_results= cp1251|
178
SET @@collation_connection= cp1251_general_ci|
179
180
--disable_result_log
181
SELECT * FROM mysqltest1.v1|
182
SELECT * FROM mysqltest1.v2|
183
SELECT * FROM mysqltest1.v3|
184
--enable_result_log
185
186
use mysqltest1|
187
188
#   - Restore environment;
189
190
set names utf8|
191
192
#   - Check!
193
194
--source include/ddl_i18n.check_views.inc
195
196
#
197
# Cleanup.
198
#
199
200
--connection default
201
--echo
202
--echo ---> connection: default
203
204
--disconnect con2
205
--disconnect con3
206
207
use test|
208
209
DROP DATABASE mysqltest1|
210
211
###########################################################################
212
#
213
# * Stored procedures/functions.
214
#
215
###########################################################################
216
217
--echo
218
--echo -------------------------------------------------------------------
219
--echo Stored procedures/functions
220
--echo -------------------------------------------------------------------
221
--echo
222
223
#
224
# Preparation:
225
#
226
227
#   - Create database with fixed, pre-defined character set.
228
229
--disable_warnings
230
DROP DATABASE IF EXISTS mysqltest1|
231
DROP DATABASE IF EXISTS mysqltest2|
232
--enable_warnings
233
234
CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|
235
CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|
236
237
use mysqltest1|
238
239
#   - Create two stored routines -- with and without explicit
240
#     CHARSET-clause for SP-variable;
241
#
242
243
--echo
244
245
#     - Procedure p1
246
247
CREATE PROCEDURE p1(
248
  INOUT параЌ1 CHAR(10),
249
  OUT параЌ2 CHAR(10))
250
BEGIN
251
  DECLARE переЌ1 CHAR(10);
252
253
  SELECT
254
    COLLATION(переЌ1) AS c1,
255
    COLLATION(параЌ1) AS c2,
256
    COLLATION(параЌ2) AS c3;
257
258
  SELECT
259
    COLLATION('текст') AS c4,
260
    COLLATION(_utf8 'текст') AS c5,
261
    COLLATION(_koi8r    'ÔÅËÓÔ') AS c6,
262
    @@collation_connection AS c7,
263
    @@character_set_client AS c8;
264
265
  SET параЌ1 = 'a';
266
  SET параЌ2 = 'b';
267
END|
268
269
--echo
270
271
#     - Procedure p2
272
273
CREATE PROCEDURE p2(
274
  INOUT параЌ1 CHAR(10) CHARACTER SET utf8,
275
  OUT параЌ2 CHAR(10) CHARACTER SET utf8)
276
BEGIN
277
  DECLARE переЌ1 CHAR(10) CHARACTER SET utf8;
278
279
  SELECT
280
    COLLATION(переЌ1) AS c1,
281
    COLLATION(параЌ1) AS c2,
282
    COLLATION(параЌ2) AS c3;
283
284
  SELECT
285
    COLLATION('текст') AS c4,
286
    COLLATION(_utf8 'текст') AS c5,
287
    COLLATION(_koi8r    'ÔÅËÓÔ') AS c6,
288
    @@collation_connection AS c7,
289
    @@character_set_client AS c8;
290
291
  SET параЌ1 = 'a';
292
  SET параЌ2 = 'b';
293
END|
294
295
--echo
296
297
#     - Procedure p3
298
299
CREATE PROCEDURE mysqltest2.p3(
300
  INOUT параЌ1 CHAR(10),
301
  OUT параЌ2 CHAR(10))
302
BEGIN
303
  DECLARE переЌ1 CHAR(10);
304
305
  SELECT
306
    COLLATION(переЌ1) AS c1,
307
    COLLATION(параЌ1) AS c2,
308
    COLLATION(параЌ2) AS c3;
309
310
  SELECT
311
    COLLATION('текст') AS c4,
312
    COLLATION(_utf8 'текст') AS c5,
313
    COLLATION(_koi8r    'ÔÅËÓÔ') AS c6,
314
    @@collation_connection AS c7,
315
    @@character_set_client AS c8;
316
317
  SET параЌ1 = 'a';
318
  SET параЌ2 = 'b';
319
END|
320
321
--echo
322
323
#     - Procedure p4
324
325
CREATE PROCEDURE mysqltest2.p4(
326
  INOUT параЌ1 CHAR(10) CHARACTER SET utf8,
327
  OUT параЌ2 CHAR(10) CHARACTER SET utf8)
328
BEGIN
329
  DECLARE переЌ1 CHAR(10) CHARACTER SET utf8;
330
331
  SELECT
332
    COLLATION(переЌ1) AS c1,
333
    COLLATION(параЌ1) AS c2,
334
    COLLATION(параЌ2) AS c3;
335
336
  SELECT
337
    COLLATION('текст') AS c4,
338
    COLLATION(_utf8 'текст') AS c5,
339
    COLLATION(_koi8r    'ÔÅËÓÔ') AS c6,
340
    @@collation_connection AS c7,
341
    @@character_set_client AS c8;
342
343
  SET параЌ1 = 'a';
344
  SET параЌ2 = 'b';
345
END|
346
347
#
348
# First-round checks.
349
#
350
351
--source include/ddl_i18n.check_sp.inc
352
353
#
354
# Change running environment (alter database character set, change session
355
# variables).
356
#
357
358
--echo
359
--echo
360
361
ALTER DATABASE mysqltest1 COLLATE cp866_general_ci|
362
ALTER DATABASE mysqltest2 COLLATE cp866_general_ci|
363
364
#
365
# Second-round checks:
366
#
367
368
#   - Change connection to flush SP-cache;
369
370
--connect (con2,localhost,root,,mysqltest1)
371
--echo
372
--echo ---> connection: con2
373
374
#   - Switch environment variables and trigger loading stored procedures;
375
376
SET @@character_set_client= cp1251|
377
SET @@character_set_results= cp1251|
378
SET @@collation_connection= cp1251_general_ci|
379
380
CALL p1(@a, @b)|
381
CALL p2(@a, @b)|
382
CALL mysqltest2.p3(@a, @b)|
383
CALL mysqltest2.p4(@a, @b)|
384
385
#   - Restore environment;
386
387
set names utf8|
388
389
#   - Check!
390
391
--source include/ddl_i18n.check_sp.inc
392
393
#
394
# Check mysqldump.
395
#
396
397
#  - Dump mysqltest1, mysqltest2;
398
399
--echo
400
--echo ---> Dump of mysqltest1
401
402
--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --routines --databases mysqltest1
403
404
--echo
405
--echo ---> Dumping mysqltest1 to ddl_i18n_utf8sp.mysqltest1.sql
406
407
--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --routines --databases mysqltest1 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8sp.mysqltest1.sql
408
409
--echo
410
--echo ---> Dump of mysqltest2
411
412
--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --routines --databases mysqltest2
413
414
--echo
415
--echo ---> Dumping mysqltest2 to ddl_i18n_utf8sp.mysqltest2.sql
416
417
--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --routines --databases mysqltest2 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8sp.mysqltest2.sql
418
419
#   - Clean mysqltest1, mysqltest2;
420
421
--echo
422
--echo
423
424
DROP DATABASE mysqltest1|
425
DROP DATABASE mysqltest2|
426
427
#   - Restore mysqltest1;
428
429
--echo
430
--echo
431
432
--echo ---> Restoring mysqltest1...
433
--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8sp.mysqltest1.sql
434
435
--echo ---> Restoring mysqltest2...
436
--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8sp.mysqltest2.sql
437
438
#
439
# Third-round checks.
440
#
441
442
#   - Change connection to flush SP-cache;
443
444
--connect (con3,localhost,root,,mysqltest1)
445
--echo
446
--echo ---> connection: con3
447
448
#   - Switch environment variables and trigger loading stored procedures;
449
450
SET @@character_set_client= cp1251|
451
SET @@character_set_results= cp1251|
452
SET @@collation_connection= cp1251_general_ci|
453
454
CALL p1(@a, @b)|
455
CALL p2(@a, @b)|
456
CALL mysqltest2.p3(@a, @b)|
457
CALL mysqltest2.p4(@a, @b)|
458
459
#   - Restore environment;
460
461
set names utf8|
462
463
#   - Check!
464
465
--source include/ddl_i18n.check_sp.inc
466
467
#
468
# Cleanup.
469
#
470
471
--connection default
472
--echo
473
--echo ---> connection: default
474
475
--disconnect con2
476
--disconnect con3
477
478
use test|
479
480
DROP DATABASE mysqltest1|
481
DROP DATABASE mysqltest2|
482
483
###########################################################################
484
#
485
# * Triggers.
486
#
487
###########################################################################
488
489
--echo
490
--echo -------------------------------------------------------------------
491
--echo Triggers
492
--echo -------------------------------------------------------------------
493
--echo
494
495
#
496
# Preparation:
497
#
498
499
#   - Create database with fixed, pre-defined character set;
500
501
--disable_warnings
502
DROP DATABASE IF EXISTS mysqltest1|
503
DROP DATABASE IF EXISTS mysqltest2|
504
--enable_warnings
505
506
CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|
507
CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|
508
509
use mysqltest1|
510
511
#   - Create tables for triggers;
512
513
CREATE TABLE t1(c INT)|
514
CREATE TABLE mysqltest2.t1(c INT)|
515
516
#   - Create log tables;
517
518
CREATE TABLE log(msg VARCHAR(255))|
519
CREATE TABLE mysqltest2.log(msg VARCHAR(255))|
520
521
522
#   - Create triggers -- with and without explicit CHARSET-clause for
523
#     SP-variable;
524
#
525
526
--echo
527
528
#     - Trigger trg1
529
530
CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
531
BEGIN
532
  DECLARE переЌ1 CHAR(10);
533
534
  INSERT INTO log VALUES(COLLATION(переЌ1));
535
  INSERT INTO log VALUES(COLLATION('текст'));
536
  INSERT INTO log VALUES(COLLATION(_utf8 'текст'));
537
  INSERT INTO log VALUES(COLLATION(_koi8r    'ÔÅËÓÔ'));
538
  INSERT INTO log VALUES(@@collation_connection);
539
  INSERT INTO log VALUES(@@character_set_client);
540
541
  SET @a1 = 'текст';
542
  SET @a2 = _utf8 'текст';
543
  SET @a3 = _koi8r    'ÔÅËÓÔ';
544
END|
545
546
--echo
547
548
#     - Trigger trg2
549
550
CREATE TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW
551
BEGIN
552
  DECLARE переЌ1 CHAR(10) CHARACTER SET utf8;
553
554
  INSERT INTO log VALUES(COLLATION(переЌ1));
555
  INSERT INTO log VALUES(COLLATION('текст'));
556
  INSERT INTO log VALUES(COLLATION(_utf8 'текст'));
557
  INSERT INTO log VALUES(COLLATION(_koi8r    'ÔÅËÓÔ'));
558
  INSERT INTO log VALUES(@@collation_connection);
559
  INSERT INTO log VALUES(@@character_set_client);
560
561
  SET @b1 = 'текст';
562
  SET @b2 = _utf8 'текст';
563
  SET @b3 = _koi8r    'ÔÅËÓÔ';
564
END|
565
566
--echo
567
568
#     - Trigger trg3
569
570
CREATE TRIGGER mysqltest2.trg3 BEFORE INSERT ON mysqltest2.t1 FOR EACH ROW
571
BEGIN
572
  DECLARE переЌ1 CHAR(10);
573
574
  INSERT INTO log VALUES(COLLATION(переЌ1));
575
  INSERT INTO log VALUES(COLLATION('текст'));
576
  INSERT INTO log VALUES(COLLATION(_utf8 'текст'));
577
  INSERT INTO log VALUES(COLLATION(_koi8r    'ÔÅËÓÔ'));
578
  INSERT INTO log VALUES(@@collation_connection);
579
  INSERT INTO log VALUES(@@character_set_client);
580
581
  SET @a1 = 'текст';
582
  SET @a2 = _utf8 'текст';
583
  SET @a3 = _koi8r    'ÔÅËÓÔ';
584
END|
585
586
--echo
587
588
#     - Trigger trg4
589
590
CREATE TRIGGER mysqltest2.trg4 AFTER INSERT ON mysqltest2.t1 FOR EACH ROW
591
BEGIN
592
  DECLARE переЌ1 CHAR(10) CHARACTER SET utf8;
593
594
  INSERT INTO log VALUES(COLLATION(переЌ1));
595
  INSERT INTO log VALUES(COLLATION('текст'));
596
  INSERT INTO log VALUES(COLLATION(_utf8 'текст'));
597
  INSERT INTO log VALUES(COLLATION(_koi8r    'ÔÅËÓÔ'));
598
  INSERT INTO log VALUES(@@collation_connection);
599
  INSERT INTO log VALUES(@@character_set_client);
600
601
  SET @b1 = 'текст';
602
  SET @b2 = _utf8 'текст';
603
  SET @b3 = _koi8r    'ÔÅËÓÔ';
604
END|
605
606
--echo
607
608
#
609
# First-round checks.
610
#
611
612
--source include/ddl_i18n.check_triggers.inc
613
614
#
615
# Change running environment (alter database character set, change session
616
# variables).
617
#
618
619
--echo
620
--echo
621
622
ALTER DATABASE mysqltest1 COLLATE cp866_general_ci|
623
ALTER DATABASE mysqltest2 COLLATE cp866_general_ci|
624
625
#
626
# Second-round checks:
627
#
628
629
#  - Flush table cache;
630
631
ALTER TABLE t1 ADD COLUMN fake INT|
632
ALTER TABLE t1 DROP COLUMN fake|
633
634
ALTER TABLE mysqltest2.t1 ADD COLUMN fake INT|
635
ALTER TABLE mysqltest2.t1 DROP COLUMN fake|
636
637
#   - Switch environment variables and initiate loading of triggers
638
#     (connect using NULL database);
639
640
--connect (con2,localhost,root,,)
641
--echo
642
--echo ---> connection: con2
643
644
SET @@character_set_client= cp1251|
645
SET @@character_set_results= cp1251|
646
SET @@collation_connection= cp1251_general_ci|
647
648
INSERT INTO mysqltest1.t1 VALUES(0)|
649
INSERT INTO mysqltest2.t1 VALUES(0)|
650
651
DELETE FROM mysqltest1.log|
652
DELETE FROM mysqltest2.log|
653
654
#   - Restore environment;
655
656
set names utf8|
657
658
use mysqltest1|
659
660
#   - Check!
661
662
--source include/ddl_i18n.check_triggers.inc
663
664
#
665
# Check mysqldump.
666
#
667
668
#  - Dump mysqltest1, mysqltest2;
669
670
--echo
671
--echo ---> Dump of mysqltest1
672
673
--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --triggers --databases mysqltest1
674
675
--echo
676
--echo ---> Dumping mysqltest1 to ddl_i18n_utf8triggers.mysqltest1.sql
677
678
--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --triggers --databases mysqltest1 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8triggers.mysqltest1.sql
679
680
--echo
681
--echo ---> Dump of mysqltest2
682
683
--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --triggers --databases mysqltest2
684
685
--echo
686
--echo ---> Dumping mysqltest2 to ddl_i18n_utf8triggers.mysqltest2.sql
687
688
--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --triggers --databases mysqltest2 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8triggers.mysqltest2.sql
689
690
#   - Clean mysqltest1, mysqltest2;
691
692
--echo
693
--echo
694
695
DROP DATABASE mysqltest1|
696
DROP DATABASE mysqltest2|
697
698
#   - Restore mysqltest1;
699
700
--echo
701
--echo
702
703
--echo ---> Restoring mysqltest1...
704
--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8triggers.mysqltest1.sql
705
706
--echo ---> Restoring mysqltest2...
707
--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8triggers.mysqltest2.sql
708
709
#
710
# Third-round checks.
711
#
712
713
#   - Flush table cache;
714
715
ALTER TABLE mysqltest1.t1 ADD COLUMN fake INT|
716
ALTER TABLE mysqltest1.t1 DROP COLUMN fake|
717
718
ALTER TABLE mysqltest2.t1 ADD COLUMN fake INT|
719
ALTER TABLE mysqltest2.t1 DROP COLUMN fake|
720
721
#   - Switch environment variables and initiate loading of triggers
722
#     (connect using NULL database);
723
724
--connect (con3,localhost,root,,)
725
--echo
726
--echo ---> connection: con3
727
728
SET @@character_set_client= cp1251|
729
SET @@character_set_results= cp1251|
730
SET @@collation_connection= cp1251_general_ci|
731
732
INSERT INTO mysqltest1.t1 VALUES(0)|
733
INSERT INTO mysqltest2.t1 VALUES(0)|
734
735
DELETE FROM mysqltest1.log|
736
DELETE FROM mysqltest2.log|
737
738
#   - Restore environment;
739
740
set names utf8|
741
742
use mysqltest1|
743
744
#   - Check!
745
746
--source include/ddl_i18n.check_triggers.inc
747
748
#
749
# Cleanup.
750
#
751
752
--connection default
753
--echo
754
--echo ---> connection: default
755
756
--disconnect con2
757
--disconnect con3
758
759
use test|
760
761
DROP DATABASE mysqltest1|
762
DROP DATABASE mysqltest2|
763
764
###########################################################################
765
#
766
# * Events
767
#
768
# We don't have EXECUTE EVENT so far, so this test is limited. It checks that
769
# event with non-latin1 symbols can be created, dumped, restored and SHOW
770
# statements work properly.
771
#
772
###########################################################################
773
774
--echo
775
--echo -------------------------------------------------------------------
776
--echo Events
777
--echo -------------------------------------------------------------------
778
--echo
779
780
#
781
# Preparation:
782
#
783
784
#   - Create database with fixed, pre-defined character set.
785
786
--disable_warnings
787
DROP DATABASE IF EXISTS mysqltest1|
788
DROP DATABASE IF EXISTS mysqltest2|
789
--enable_warnings
790
791
CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|
792
CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|
793
794
use mysqltest1|
795
796
#   - Create two stored routines -- with and without explicit
797
#     CHARSET-clause for SP-variable;
798
#
799
800
--echo
801
802
#     - Event ev1
803
804
CREATE EVENT ev1 ON SCHEDULE AT '2030-01-01 00:00:00' DO
805
BEGIN
806
  DECLARE переЌ1 CHAR(10);
807
808
  SELECT
809
    COLLATION(переЌ1) AS c1,
810
    COLLATION('текст') AS c2,
811
    COLLATION(_utf8 'текст') AS c3,
812
    COLLATION(_koi8r    'ÔÅËÓÔ') AS c4,
813
    @@collation_connection AS c5,
814
    @@character_set_client AS c6;
815
END|
816
817
--echo
818
819
#     - Event ev2
820
821
CREATE EVENT ev2 ON SCHEDULE AT '2030-01-01 00:00:00' DO
822
BEGIN
823
  DECLARE переЌ1 CHAR(10) CHARACTER SET utf8;
824
825
  SELECT
826
    COLLATION(переЌ1) AS c1,
827
    COLLATION('текст') AS c2,
828
    COLLATION(_utf8 'текст') AS c3,
829
    COLLATION(_koi8r    'ÔÅËÓÔ') AS c4,
830
    @@collation_connection AS c5,
831
    @@character_set_client AS c6;
832
END|
833
834
--echo
835
836
#     - Event ev3
837
838
CREATE EVENT mysqltest2.ev3 ON SCHEDULE AT '2030-01-01 00:00:00' DO
839
BEGIN
840
  DECLARE переЌ1 CHAR(10) CHARACTER SET utf8;
841
842
  SELECT
843
    COLLATION(переЌ1) AS c1,
844
    COLLATION('текст') AS c2,
845
    COLLATION(_utf8 'текст') AS c3,
846
    COLLATION(_koi8r    'ÔÅËÓÔ') AS c4,
847
    @@collation_connection AS c5,
848
    @@character_set_client AS c6;
849
END|
850
851
--echo
852
853
#     - Event ev4
854
855
CREATE EVENT mysqltest2.ev4 ON SCHEDULE AT '2030-01-01 00:00:00' DO
856
BEGIN
857
  DECLARE переЌ1 CHAR(10) CHARACTER SET utf8;
858
859
  SELECT
860
    COLLATION(переЌ1) AS c1,
861
    COLLATION('текст') AS c2,
862
    COLLATION(_utf8 'текст') AS c3,
863
    COLLATION(_koi8r    'ÔÅËÓÔ') AS c4,
864
    @@collation_connection AS c5,
865
    @@character_set_client AS c6;
866
END|
867
868
--echo
869
870
871
#
872
# First-round checks.
873
#
874
875
--source include/ddl_i18n.check_events.inc
876
877
#
878
# Change running environment (alter database character set, change session
879
# variables).
880
#
881
882
--echo
883
--echo
884
885
ALTER DATABASE mysqltest1 COLLATE cp866_general_ci|
886
ALTER DATABASE mysqltest2 COLLATE cp866_general_ci|
887
888
#
889
# Second-round checks:
890
#
891
892
#   - Change connection to flush cache;
893
894
--connect (con2,localhost,root,,mysqltest1)
895
--echo
896
--echo ---> connection: con2
897
898
#   - Switch environment variables and trigger loading stored procedures;
899
900
SET @@character_set_client= cp1251|
901
SET @@character_set_results= cp1251|
902
SET @@collation_connection= cp1251_general_ci|
903
904
--disable_result_log
905
SHOW CREATE EVENT ev1|
906
SHOW CREATE EVENT ev2|
907
SHOW CREATE EVENT mysqltest2.ev3|
908
SHOW CREATE EVENT mysqltest2.ev4|
909
--enable_result_log
910
911
#   - Restore environment;
912
913
set names utf8|
914
915
#   - Check!
916
917
--source include/ddl_i18n.check_events.inc
918
919
#
920
# Check mysqldump.
921
#
922
923
#  - Dump mysqltest1, mysqltest2;
924
925
--echo
926
--echo ---> Dump of mysqltest1
927
928
--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --events --databases mysqltest1
929
930
--echo
931
--echo ---> Dumping mysqltest1 to ddl_i18n_utf8events.mysqltest1.sql
932
933
--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --events --databases mysqltest1 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8events.mysqltest1.sql
934
935
--echo
936
--echo ---> Dump of mysqltest2
937
938
--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --events --databases mysqltest2
939
940
--echo
941
--echo ---> Dumping mysqltest2 to ddl_i18n_utf8events.mysqltest2.sql
942
943
--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --compact --events --databases mysqltest2 > $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8events.mysqltest2.sql
944
945
#   - Clean mysqltest1, mysqltest2;
946
947
--echo
948
--echo
949
950
DROP DATABASE mysqltest1|
951
DROP DATABASE mysqltest2|
952
953
#   - Restore mysqltest1;
954
955
--echo
956
--echo
957
958
--echo ---> Restoring mysqltest1...
959
--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8events.mysqltest1.sql
960
961
--echo ---> Restoring mysqltest2...
962
--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/ddl_i18n_utf8events.mysqltest2.sql
963
964
#
965
# Third-round checks.
966
#
967
968
#   - Change connection to flush cache;
969
970
--connect (con3,localhost,root,,mysqltest1)
971
--echo
972
--echo ---> connection: con3
973
974
#   - Switch environment variables and trigger loading stored procedures;
975
976
SET @@character_set_client= cp1251|
977
SET @@character_set_results= cp1251|
978
SET @@collation_connection= cp1251_general_ci|
979
980
--disable_result_log
981
SHOW CREATE EVENT ev1|
982
SHOW CREATE EVENT ev2|
983
SHOW CREATE EVENT mysqltest2.ev3|
984
SHOW CREATE EVENT mysqltest2.ev4|
985
--enable_result_log
986
987
#   - Restore environment;
988
989
set names utf8|
990
991
#   - Check!
992
993
--source include/ddl_i18n.check_events.inc
994
995
###########################################################################
996
#
997
# * DDL statements inside stored routine.
998
#
999
# Here we check that DDL statements use actual database collation even if they
1000
# are called from stored routine.
1001
#
1002
###########################################################################
1003
1004
--echo
1005
--echo -------------------------------------------------------------------
1006
--echo DDL statements within stored routine.
1007
--echo -------------------------------------------------------------------
1008
--echo
1009
1010
#
1011
# Preparation:
1012
#
1013
1014
#   - Create database with fixed, pre-defined character set.
1015
1016
--disable_warnings
1017
DROP DATABASE IF EXISTS mysqltest1|
1018
DROP DATABASE IF EXISTS mysqltest2|
1019
--enable_warnings
1020
1021
CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|
1022
CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci|
1023
1024
use mysqltest1|
1025
1026
#   - Create procedures;
1027
1028
--echo
1029
1030
CREATE PROCEDURE p1()
1031
BEGIN
1032
  CREATE TABLE t1(col1 VARCHAR(10));
1033
  SHOW CREATE TABLE t1;
1034
END|
1035
1036
--echo
1037
1038
CREATE PROCEDURE mysqltest2.p2()
1039
BEGIN
1040
  CREATE TABLE t2(col1 VARCHAR(10));
1041
  SHOW CREATE TABLE t2;
1042
END|
1043
1044
--echo
1045
1046
#
1047
# First-round checks.
1048
#
1049
1050
CALL p1()|
1051
1052
--echo
1053
1054
SHOW CREATE TABLE t1|
1055
1056
--echo
1057
--echo
1058
1059
CALL mysqltest2.p2()|
1060
1061
--echo
1062
1063
SHOW CREATE TABLE mysqltest2.t2|
1064
1065
#
1066
# Alter database.
1067
#
1068
1069
--echo
1070
1071
ALTER DATABASE mysqltest1 COLLATE cp1251_general_cs|
1072
ALTER DATABASE mysqltest2 COLLATE cp1251_general_cs|
1073
1074
DROP TABLE t1|
1075
DROP TABLE mysqltest2.t2|
1076
1077
--echo
1078
1079
#
1080
# Second-round checks.
1081
#
1082
1083
CALL p1()|
1084
1085
--echo
1086
1087
SHOW CREATE TABLE t1|
1088
1089
--echo
1090
--echo
1091
1092
CALL mysqltest2.p2()|
1093
1094
--echo
1095
1096
SHOW CREATE TABLE mysqltest2.t2|
1097
1098
###########################################################################
1099
#
1100
# That's it.
1101
#
1102
###########################################################################
1103
1104
#
1105
# Cleanup.
1106
#
1107
1108
--connection default
1109
--echo
1110
--echo ---> connection: default
1111
1112
--disconnect con2
1113
--disconnect con3
1114
1115
use test|
1116
1117
DROP DATABASE mysqltest1|
1118
DROP DATABASE mysqltest2|