MySQL system variables and status variables have two scopes. These are GLOBAL and SESSION which are self explanatory.
This is important to realize when altering system variables dynamically. The following example does not produce the expected results.
mysql> USE test;
Database changed
mysql> CREATE TABLE example1(
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> col1 VARCHAR(10) NOT NULL,
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.29 sec)
mysql> SHOW CREATE TABLE example1G
*************************** 1. row ***************************
Table: example1
Create Table: CREATE TABLE `example1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col1` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
We see that the table has a default CHARACTER SET of latin1. If you wanted to ensure all tables are created as utf8 you change the appropriate system variable. For example, we change the GLOBAL system variable and re-create the table.
mysql> SHOW GLOBAL VARIABLES like 'char%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /Users/rbradfor/mysql/mysql-5.1.39-osx10.5-x86/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.00 sec)
mysql> SET GLOBAL character_set_server=utf8;
Query OK, 0 rows affected (0.10 sec)
mysql> DROP TABLE example1;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE example1( id INT UNSIGNED NOT NULL AUTO_INCREMENT, col1 VARCHAR(10) NOT NULL, PRIMARY KEY(id) );
Query OK, 0 rows affected (0.12 sec)
mysql> SHOW CREATE TABLE example1G
*************************** 1. row ***************************
Table: example1
Create Table: CREATE TABLE `example1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col1` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)
The table still is latin1. This is because now we have a SESSION scope that differs from the GLOBAL scope as seen in this output.
mysql> SELECT @@GLOBAL.character_set_server,@@SESSION.character_set_server; +-------------------------------+--------------------------------+ | @@GLOBAL.character_set_server | @@SESSION.character_set_server | +-------------------------------+--------------------------------+ | utf8 | latin1 | +-------------------------------+--------------------------------+ 1 row in set (0.00 sec)
The solution is easy however the trap can be easily overlooked and especially when changing other MySQL system variables.
mysql> SET SESSION character_set_server=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE example1;Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE example1( id INT UNSIGNED NOT NULL AUTO_INCREMENT, col1 VARCHAR(10) NOT NULL, PRIMARY KEY(id) );
Query OK, 0 rows affected (0.09 sec)
mysql> SHOW CREATE TABLE example1G
*************************** 1. row ***************************
Table: example1
Create Table: CREATE TABLE `example1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col1` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
By default, and when not specified in SHOW and SET commands the default scope is GLOBAL, however prior to MySQL 5.0.2 the default was SESSION. A note you will find in the 5.0 Reference Manual but not the current GA version 5.1 Reference Manual. See also SHOW STATUS Gotcha written in August 2006.
There are also other gotchas with scope that we will discuss at some other time.
References
About “Don’t Assume”
“Don’t Assume” is a series of posts to help the Oracle DBA understand, use and appreciate the subtle differences and unique characteristics of the MySQL RDBMS in comparison to Oracle. These points as essential to operate MySQL effectively in a production environment and avoid any loss of data or availability.
For more posts in this series be sure to follow the mysql4oracledba tag and also watch out for other MySQL for Oracle DBA presentations.
The MySQLCamp for the Oracle DBA is a series of educational talks all Oracle DBA resources should attend. Two presentations from this series IGNITION and LIFTOFF will be presented at the MySQL Users Conference 2010 in Santa Clara, April 2010 This series also includes JUMPSTART and VELOCITY. If you would like to here these presentations in your area, please contact me.