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 'Bug#35378: New session collation_database is not change by global collation_database'
43
--echo '#--------------------FN_DYNVARS_011_02-------------------------#'
44
#########################################################
45
# Begin the functionality Testing of collation_database #
46
#########################################################
48
--echo 'connection default'
52
DROP TABLE IF EXISTS t1,t2;
56
SELECT @@character_set_server,@@collation_server;
57
#==============================================================================
58
--echo '--check if setting collation_database update character_set_database--'
59
#==============================================================================
61
SET @@session.collation_database = utf8_spanish_ci;
62
SELECT @@collation_database, @@character_set_database;
64
#==============================================================================
65
--echo '--check if collation_database effects database/tables charset/collation'
66
#==============================================================================
67
SET @@session.collation_server = utf8_roman_ci;
68
SET @@session.collation_database = latin2_croatian_ci;
71
SHOW CREATE DATABASE db1;
72
--echo 'Bug#35380: collation_database does not effects CREATE DATABASE without characater set'
74
CREATE TABLE t1(a CHAR(20));
77
#==============================================================================
78
--echo '---check if updating collation_database effects new table/column---'
79
#==============================================================================
80
SET @@session.collation_database = latin7_general_cs;
81
CREATE TABLE t2(a CHAR(10));
87
#==============================================================================
88
--echo '--check value of collation and character set when current db is dropped'
89
#==============================================================================
91
SELECT @@collation_database,@@collation_server,@@character_set_database,@@character_set_server;
93
SELECT @@collation_database,@@collation_server,@@character_set_database,@@character_set_server;
96
--echo 'fill table with some test data';
97
CREATE TABLE t1(a CHAR(20))CHARACTER SET=latin1;
98
INSERT INTO t1 VALUES('Muffler'),('M�ller'),('MX Systems');
100
#==============================================================================
101
--echo '---check if collation_database effects results sort order---'
102
#==============================================================================
104
SET @@session.collation_database = latin1_swedish_ci;
105
SELECT * FROM t1 ORDER BY a;
107
SET @@session.collation_database = latin1_german1_ci;
108
SELECT * FROM t1 ORDER BY a;
110
--echo 'explicit Collate clause should effects results sort order';
112
SELECT * FROM t1 ORDER BY a COLLATE latin1_swedish_ci;
113
SELECT * FROM t1 ORDER BY a COLLATE latin1_german1_ci;
116
#==============================================================================
117
--echo '----check if indexing is effected by collation_database---'
118
#==============================================================================
119
SET @@session.collation_database = latin1_swedish_ci;
120
ALTER TABLE t1 ADD PRIMARY KEY (a);
122
SELECT * FROM t1 ORDER BY a;
124
ALTER TABLE t1 DROP PRIMARY KEY;
125
SET @@session.collation_database = latin1_german1_ci;
126
ALTER TABLE t1 ADD PRIMARY KEY (a);
128
SELECT * FROM t1 ORDER BY a;
132
SET @@global.collation_database = @global_collation_database;
133
SET @@session.collation_database = @session_collation_database;
134
SET @@session.collation_server = @session_collation_server;
136
#######################################################
137
# End of functionality Testing for collation_database #
138
#######################################################