1'#--------------------FN_DYNVARS_011_01-------------------------#'
2SET @global_collation_database = @@global.collation_database;
3SET @session_collation_database = @@session.collation_database;
4SET @session_collation_server = @@session.collation_server;
5SET @@global.collation_database = latin1_danish_ci;
6connect  con1,localhost,root,,,,;
7connection con1;
8SELECT @@global.collation_database;
9@@global.collation_database
10latin1_danish_ci
11SELECT @@session.collation_database;
12@@session.collation_database
13latin1_swedish_ci
14disconnect con1;
15'#--------------------FN_DYNVARS_011_02-------------------------#'
16connection default;
17DROP TABLE IF EXISTS t1,t2;
18'--check if setting collation_database update character_set_database--'
19SET @@session.collation_database = utf8_spanish_ci;
20SELECT @@collation_database, @@character_set_database;
21@@collation_database	@@character_set_database
22utf8_spanish_ci	utf8
23'--check if collation_database effects database/tables charset/collation'
24SET @@session.collation_server = utf8_roman_ci;
25SET @@session.collation_database = latin2_croatian_ci;
26CREATE DATABASE db1;
27USE db1;
28SHOW CREATE DATABASE db1;
29Database	Create Database
30db1	CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_roman_ci */
31CREATE TABLE t1(a CHAR(20));
32SHOW CREATE TABLE t1;
33Table	Create Table
34t1	CREATE TABLE `t1` (
35  `a` char(20) COLLATE utf8_roman_ci DEFAULT NULL
36) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_roman_ci
37'---check if updating collation_database effects new table/column---'
38SET @@session.collation_database = latin7_general_cs;
39CREATE TABLE t2(a CHAR(10));
40SHOW CREATE TABLE t2;
41Table	Create Table
42t2	CREATE TABLE `t2` (
43  `a` char(10) COLLATE utf8_roman_ci DEFAULT NULL
44) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_roman_ci
45DROP TABLE t1,t2;
46'--check value of collation and character set when current db is dropped'
47DROP DATABASE db1;
48SELECT @@collation_database,@@collation_server,@@character_set_database,@@character_set_server;
49@@collation_database	@@collation_server	@@character_set_database	@@character_set_server
50utf8_roman_ci	utf8_roman_ci	utf8	utf8
51USE test;
52SELECT @@collation_database,@@collation_server,@@character_set_database,@@character_set_server;
53@@collation_database	@@collation_server	@@character_set_database	@@character_set_server
54latin1_swedish_ci	utf8_roman_ci	latin1	utf8
55'fill table with some test data';
56CREATE TABLE t1(a CHAR(20))CHARACTER SET=latin1;
57INSERT INTO t1 VALUES('Muffler'),('M�ller'),('MX Systems');
58'---check if collation_database effects results sort order---'
59SET @@session.collation_database = latin1_swedish_ci;
60SELECT * FROM t1 ORDER BY a;
61a
62Muffler
63MX Systems
64M�ller
65SET @@session.collation_database = latin1_german1_ci;
66SELECT * FROM t1 ORDER BY a;
67a
68Muffler
69MX Systems
70M�ller
71'explicit Collate clause should effects results sort order';
72SELECT * FROM t1 ORDER BY a COLLATE latin1_swedish_ci;
73a
74Muffler
75MX Systems
76M�ller
77SELECT * FROM t1 ORDER BY a COLLATE latin1_german1_ci;
78a
79Muffler
80M�ller
81MX Systems
82'----check if indexing is effected by collation_database---'
83SET @@session.collation_database = latin1_swedish_ci;
84ALTER TABLE t1 ADD PRIMARY KEY (a);
85REPAIR TABLE t1;
86Table	Op	Msg_type	Msg_text
87test.t1	repair	status	OK
88SELECT * FROM t1 ORDER BY a;
89a
90Muffler
91MX Systems
92M�ller
93ALTER TABLE t1 DROP PRIMARY KEY;
94SET @@session.collation_database = latin1_german1_ci;
95ALTER TABLE t1 ADD PRIMARY KEY (a);
96REPAIR TABLE t1;
97Table	Op	Msg_type	Msg_text
98test.t1	repair	status	OK
99SELECT * FROM t1 ORDER BY a;
100a
101Muffler
102MX Systems
103M�ller
104DROP TABLE t1;
105SET @@global.collation_database = @global_collation_database;
106SET @@session.collation_database = @session_collation_database;
107SET @@session.collation_server = @session_collation_server;
108