Reference

This is the start of a few pages of largely version independent reference

In the spirit of wiki and in response to the post here: http://moodle.org/mod/forum/discuss.php?d=205117

Go for it Guillermo.

Maybe start here, and later on add a category:Reference and see what happens. I've set a watch on, and I may do a page on JSON errors.

Database

I'll start writing here about database and utf8 issues, but I guess that the idea would be for this to be the main reference table of contents page so it then links to each main topic page.

Basic concepts

Byte, character, character set, character encoding and collation

A byte (or octet) is the smallest unit of storage that can be allocated and, in almost all modern computers, it consists of 8 bits, where a bit can only have two values: 0 or 1.

A character is a symbol used in a writing system, such as a letter of the alphabet.

A character set (or a coded character set), refers to the set of characters and numbers, or code points, used to represent them. A code point can be understood as the position the character occupies in the set.

A character encoding (or a character encoding form), refers to how the code points are converted (encoded) into code values to be saved or stored in a computer.

Unicode is a character set developed to consistently encode, represent and handle text in most of the world's writing systems in use today. Unicode can be implemented by different character encodings, where the two most commonly used are UTF-8 and UTF-16:

• UTF-8 is a variable-width character encoding that can represent every character in the Unicode character set. UTF-8 encodes each of the 1,112,064 code points in the Unicode character set using one to four bytes.

Note. MySQL utf8 Unicode character set uses a maximum of three bytes per multi-byte character (http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8.html).

• UTF-16 is a variable-length character encoding for Unicode that uses one or two 16-bit code units, where each unit takes two 8-bit bytes, and the order of the bytes may depend on the byte order (endianness) of the computer architecture. To assist in recognizing the byte order of code units, UTF-16 allows a Byte Order Mark (BOM), a code unit with the hexadecimal value U+FEFF, to precede the first actual coded value.

Eventhough UTF-8 is the preferred character encoding nowadays, many others have been used, for example:

• ISO-8859-1 (or Latin-1). A character encoding for the Latin script, where each character is encoded as a single 8-bit code value. This character-encoding scheme consists of 191 characters and it is used throughout The Americas, Western Europe, Oceania, and much of Africa. It is also commonly used in most standard romanizations of East-Asian languages.
• ISO-8859-15 (or Latin-9). A character encoding similar to Latin-1, except that it substituted some rarely used characters with the euro sign and a few other letters.
• Shift JIS. A character encoding for the Japanese language.

To illustrate the concepts given above, the € (euro) character has a code point equal to 8364 (or U+20AC, in hexadecimal notation) in the Unicode character set. This code point can be stored in different ways, depending on the character encoding used:

Character encoding	Code value
ISO-8859-15 (hexadecimal)	A4
UTF-8 (hex)	E2 82 AC
UTF-16 (hex)	20AC

Finally, a Collation determines how data is sorted and how strings are compared to each other.

Encoding and decoding

One of the most important things one must keep in mind when working with databases (or files, for that matter) is to be aware of how characters (letters, numbers and non-alphanumeric characters) are actually being saved or encoded and how bytes (represented with hexadecimal values) are actually being interpreted or decoded.

For example, it is not enough to say that character “é” (small letter e with acute) has been saved in a text file, one also has to be aware of how was it encoded. Why? Because if it was encoded as Latin-1, the file will have one byte of hex value E9, but if it was encoded as UTF-8, the file would then have two bytes of hex value C3 and A9:

Character	Latin-1 hex value	UTF-8 hex values
é	E9	C3 A9

In the same way, it is not enough to say that an UTF-8 encoded character “é” is going to be read from a text file, because eventhough we might know that two bytes of hex value “C3 A9” will be read, if they are decoded (interpreted) as Latin-1, we will get characters é, but if they are decoded as UTF-8, we would then get character “é”:

Hex value	Latin-1 characters	UTF-8 character
C3 A9	é	é

Being aware of these two concepts will become relevant when trying to understand how data is encoded and decoded through the many stages (source, client, server) in order to be saved or restored.

MySQL

Character sets and collations

Until version 4.1, MySQL tables used the latin1 character set by default. From version 4.1, not only the concepts of "character set" and "collation" were introduced, but the character set by default (at the configuration file) was changed to utf8.

In MySQL, a character set is a set of symbols and encodings, and a collation is a set of rules for comparing characters in a character set.

Note. Eventhough in MySQL the term "character set" is used, actually it should be "character encoding" ("charset").

The MySQL server supports multiple character sets, like ascii, koi8r, latin1, sjis and utf8. Each character set has at least one collation, however, as it may have many more, a default one is always defined for each character set.

Character set	Default collation	Other collations
ascii	ascii_general_ci	ascii_bin
koi8r	koi8r_general_ci	koi8r_bin
latin1	latin1_swedish_ci	latin1_bin, latin1_general_ci, latin1_spanish_ci
sjis	sjis_japanese_ci	sjis_bin
utf8	utf8_general_ci	utf8_bin, utf8_unicode_ci, utf8_turkish_ci

Collation names follow a convention: they start with the name of the character set with which they are associated, they usually include a language name, and they end with _ci (case insensitive), _cs (case sensitive), or _bin (binary). From this, is should be evident that two different character sets cannot have the same collation.

Default settings

The MySQL database server uses a very flexible scheme where default settings for character sets and collations can be set at four levels: Server, Database, Table and Column.

Server

The server charset and collation are used as default values when the database charset and collation are not specified in CREATE DATABASE statements.

The current server character set and collation can be determined from the values of the character_set_server and collation_server system variables.

Database

The database charset and collation are used as default values for table definitions when the table charset and collation are not specified in CREATE TABLE statements.

The character set and collation for the default database can be determined from the values of the character_set_database and collation_database system variables.

Table

The table charset and collation are used as default values for column definitions when the column charset and collation are not specified in individual column definitions.

The table character set and collation are MySQL extensions; there do not exist in standard SQL.

Column

A character set and a collation may be specified for every "character" column (that is, columns of type CHAR, VARCHAR, or TEXT).

The column character set and collate clauses are standard SQL.

Configuration files, settings and default values

From here on it will be assumed that the server is already running. Also, for Windows users, commands must be entered from a Windows command (system) window.

Server and client

All MySQL settings, both for the server and the client components, are defined under one configuration or option file: my.ini (Windows) or my.cnf (Linux), although many copies of this file might be read at startup.

Settings in the configuration file are divided in option groups, where each one contains the settings for: the server, all clients and each particular client:

Settings for	Option group
The server	[mysqld], [server], etc.
All clients	[client]
Each particular client	[mysql], [mysqldump], [myisamchk], [mysqlhotcopy], etc.

Running a particular program with the parameters --verbose --help:

mysqld --verbose --help

mysql --verbose --help

mysqldump --verbose --help

will list four pieces of information:

1. The location of the configuration files.
2. The option groups read by the program.
3. The allowed options or variables (parameters) with their abreviations and descriptions.
4. The default values of variables that can be set from the command line.

Location of the configuration files

Default options are read from the following files in the given order:

Windows

WINDIR\my.ini WINDIR\my.cnf C:\my.ini C:\my.cnf INSTALLDIR\my.ini INSTALLDIR\my.cnf

Where WINDIR is the Windows directory (e.g. C:\Windows), and INSTALLDIR is the MySQL installation directory (e.g. C:\MySqlServer5.5).

Linux

/etc/my.cnf /etc/mysql/my.cnf SYSCONFDIR/my.cnf $MYSQL_HOME/my.cnf

The option groups read

The option groups (in the configuration file) read by each program; for example:

Program	The following groups are read
mysqld	mysqld server mysqld-5.5
mysql	mysql client
mysqldump	mysqldump client

Allowed options (variables)

The variables accepted (in the command line) by the program, with their abreviations and descriptions; for example, the following are some of those listed for the mysql client program:

Variable (option)	Description
-D, --database=name	Database to use.
--default-character-set=name	Set the default character set.

Variables and their values

The default values of variables that can be set from the command line for each program; for example, the following are some of those listed by the mysqld server program:

Variable	Default value
basedir	C:/MySqlServer5.5/
character-set-server	latin1
datadir	C:\ MySqlServer5.5\Data\
date-format	%Y-%m-%d
innodb	ON
tmpdir	C:\Tmp

Values used by a running server

While the --help --verbose options of the MySQL server (mysqld) show command line options and their default values, there are also other variables that can only be seen after the server has been started and that provide information about its operation.

To see what values is using a running server, the mysqladmin program can be used with the variables option:

mysqladmin [-u root -ppassword] variables

The following are some of the variables and values listed:

Variable_name	Value
character_set_client	latin1
character_set_connection	latin1
collation_connection	latin1_swedish_ci
connect_timeout	10

Database

For each database created, its character set and collation values are saved in a text file named db.opt, located in the datadir directory.

When a database is first created, if the character set and collation options are not defined in the CREATE DATABASE statement, then they are taken from the server charset and collation values. These two values can be modified via the ALTER DATABASE statement.