1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
############## mysql-test\t\collation_database_func.test #####################
# #
# Variable Name: collation_database #
# Scope: GLOBAL | SESSION #
# Access Type: Dynamic #
# Data Type: string #
# Default Value: #
# Range: #
# #
# #
# Creation Date: 2008-03-08 #
# Author: Rizwan #
# #
# Description: Test Cases of Dynamic System Variable collation_database #
# that checks the behavior of this variable #
# #
# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
# server-system-variables.html #
# #
###############################################################################
--echo '#--------------------FN_DYNVARS_011_01-------------------------#'
####################################################################
# Check if setting collation_database is changed in new connection #
####################################################################
SET @global_collation_database = @@global.collation_database;
SET @session_collation_database = @@session.collation_database;
SET @session_collation_server = @@session.collation_server;
SET @@global.collation_database = latin1_danish_ci;
connect (con1,localhost,root,,,,);
connection con1;
SELECT @@global.collation_database;
SELECT @@session.collation_database;
disconnect con1;
--echo '#--------------------FN_DYNVARS_011_02-------------------------#'
#########################################################
# Begin the functionality Testing of collation_database #
#########################################################
connection default;
--disable_warnings
DROP TABLE IF EXISTS t1,t2;
--enable_warnings
#==============================================================================
--echo '--check if setting collation_database update character_set_database--'
#==============================================================================
SET @@session.collation_database = utf8_spanish_ci;
SELECT @@collation_database, @@character_set_database;
#==============================================================================
--echo '--check if collation_database effects database/tables charset/collation'
#==============================================================================
SET @@session.collation_server = utf8_roman_ci;
SET @@session.collation_database = latin2_croatian_ci;
CREATE DATABASE db1;
USE db1;
SHOW CREATE DATABASE db1;
CREATE TABLE t1(a CHAR(20));
SHOW CREATE TABLE t1;
#==============================================================================
--echo '---check if updating collation_database effects new table/column---'
#==============================================================================
SET @@session.collation_database = latin7_general_cs;
CREATE TABLE t2(a CHAR(10));
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;
#==============================================================================
--echo '--check value of collation and character set when current db is dropped'
#==============================================================================
DROP DATABASE db1;
SELECT @@collation_database,@@collation_server,@@character_set_database,@@character_set_server;
USE test;
SELECT @@collation_database,@@collation_server,@@character_set_database,@@character_set_server;
--echo 'fill table with some test data';
CREATE TABLE t1(a CHAR(20))CHARACTER SET=latin1;
INSERT INTO t1 VALUES('Muffler'),('Müller'),('MX Systems');
#==============================================================================
--echo '---check if collation_database effects results sort order---'
#==============================================================================
SET @@session.collation_database = latin1_swedish_ci;
SELECT * FROM t1 ORDER BY a;
SET @@session.collation_database = latin1_german1_ci;
SELECT * FROM t1 ORDER BY a;
--echo 'explicit Collate clause should effects results sort order';
SELECT * FROM t1 ORDER BY a COLLATE latin1_swedish_ci;
SELECT * FROM t1 ORDER BY a COLLATE latin1_german1_ci;
#==============================================================================
--echo '----check if indexing is effected by collation_database---'
#==============================================================================
SET @@session.collation_database = latin1_swedish_ci;
ALTER TABLE t1 ADD PRIMARY KEY (a);
REPAIR TABLE t1;
SELECT * FROM t1 ORDER BY a;
ALTER TABLE t1 DROP PRIMARY KEY;
SET @@session.collation_database = latin1_german1_ci;
ALTER TABLE t1 ADD PRIMARY KEY (a);
REPAIR TABLE t1;
SELECT * FROM t1 ORDER BY a;
# clean up
DROP TABLE t1;
SET @@global.collation_database = @global_collation_database;
SET @@session.collation_database = @session_collation_database;
SET @@session.collation_server = @session_collation_server;
#######################################################
# End of functionality Testing for collation_database #
#######################################################
|
