1
############## mysql-test\t\collation_database_func.test #####################
3
# Variable Name: collation_database #
4
# Scope: GLOBAL | SESSION #
5
# Access Type: Dynamic #
11
# Creation Date: 2008-03-08 #
14
# Description: Test Cases of Dynamic System Variable collation_database #
15
# that checks the behavior of this variable #
17
# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
18
# server-system-variables.html #
20
###############################################################################
23
--echo '#--------------------FN_DYNVARS_011_01-------------------------#'
24
####################################################################
25
# Check if setting collation_database is changed in new connection #
26
####################################################################
27
SET @global_collation_database = @@global.collation_database;
28
SET @session_collation_database = @@session.collation_database;
29
SET @session_collation_server = @@session.collation_server;
32
SET @@global.collation_database = latin1_danish_ci;
33
--echo 'connect (con1,localhost,root,,,,)'
34
connect (con1,localhost,root,,,,);
35
--echo 'connection con1'
37
SELECT @@global.collation_database;
38
SELECT @@session.collation_database;
41
--echo '#--------------------FN_DYNVARS_011_02-------------------------#'
42
#########################################################
43
# Begin the functionality Testing of collation_database #
44
#########################################################
46
--echo 'connection default'
50
DROP TABLE IF EXISTS t1,t2;
54
SELECT @@character_set_server,@@collation_server;
55
#==============================================================================
56
--echo '--check if setting collation_database update character_set_database--'
57
#==============================================================================
59
SET @@session.collation_database = utf8_spanish_ci;
60
SELECT @@collation_database, @@character_set_database;
62
#==============================================================================
63
--echo '--check if collation_database effects database/tables charset/collation'
64
#==============================================================================
65
SET @@session.collation_server = utf8_roman_ci;
66
SET @@session.collation_database = latin2_croatian_ci;
69
SHOW CREATE DATABASE db1;
71
CREATE TABLE t1(a CHAR(20));
74
#==============================================================================
75
--echo '---check if updating collation_database effects new table/column---'
76
#==============================================================================
77
SET @@session.collation_database = latin7_general_cs;
78
CREATE TABLE t2(a CHAR(10));
84
#==============================================================================
85
--echo '--check value of collation and character set when current db is dropped'
86
#==============================================================================
88
SELECT @@collation_database,@@collation_server,@@character_set_database,@@character_set_server;
90
SELECT @@collation_database,@@collation_server,@@character_set_database,@@character_set_server;
93
--echo 'fill table with some test data';
94
CREATE TABLE t1(a CHAR(20))CHARACTER SET=latin1;
95
INSERT INTO t1 VALUES('Muffler'),('M�ller'),('MX Systems');
97
#==============================================================================
98
--echo '---check if collation_database effects results sort order---'
99
#==============================================================================
101
SET @@session.collation_database = latin1_swedish_ci;
102
SELECT * FROM t1 ORDER BY a;
104
SET @@session.collation_database = latin1_german1_ci;
105
SELECT * FROM t1 ORDER BY a;
107
--echo 'explicit Collate clause should effects results sort order';
109
SELECT * FROM t1 ORDER BY a COLLATE latin1_swedish_ci;
110
SELECT * FROM t1 ORDER BY a COLLATE latin1_german1_ci;
113
#==============================================================================
114
--echo '----check if indexing is effected by collation_database---'
115
#==============================================================================
116
SET @@session.collation_database = latin1_swedish_ci;
117
ALTER TABLE t1 ADD PRIMARY KEY (a);
119
SELECT * FROM t1 ORDER BY a;
121
ALTER TABLE t1 DROP PRIMARY KEY;
122
SET @@session.collation_database = latin1_german1_ci;
123
ALTER TABLE t1 ADD PRIMARY KEY (a);
125
SELECT * FROM t1 ORDER BY a;
129
SET @@global.collation_database = @global_collation_database;
130
SET @@session.collation_database = @session_collation_database;
131
SET @@session.collation_server = @session_collation_server;
133
#######################################################
134
# End of functionality Testing for collation_database #
135
#######################################################