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