10 MySQL server functions

10.1 What languages are supported by MySQL?

mysqld can issue error messages in the following languages: Czech, Dutch, English (the default), Estonia, French, German, Hungarian, Italian, Norwegian, Norwegian-ny, Polish, Portuguese, Spanish and Swedish.

To start mysqld with a particular language, use either the --language=lang or -L lang options. For example:

shell> mysqld --language=swedish

or:

shell> mysqld --language=/usr/local/share/swedish

Note that all language names are specified in lowercase.

The language files are located (by default) in `mysql_base_dir/share/LANGUAGE/'.

To update the error message file, you should edit the `errmsg.txt' file and execute the following command to generate the `errmsg.sys' file:

shell> comp_err errmsg.txt errmsg.sys

If you upgrade to a newer version of MySQL, remember to repeat your changes with the new `errmsg.txt' file.

10.1.1 The character set used for data and sorting

By default, MySQL uses the ISO-8859-1 (Latin1) character set. This is the character set used in the USA and western Europe.

The character set determines what characters are allowed in names and how things are sorted by the ORDER BY and GROUP BY clauses of the SELECT statement.

You can change the character set at compile time by using the --with-charset=charset option to configure. See section 4.7.1 Quick installation overview.

To add another character set to MySQL, use the following procedure:

10.1.2 Adding a new character set

1. Choose a name for the character set, denoted MYSET below.
2. Create the file `strings/ctype-MYSET.c' in the MySQL source distribution.
3. Look at one of the existing `ctype-*.c' files to see what needs to be defined. Note that the arrays in your file must have names like ctype_MYSET, to_lower_MYSET and so on. to_lower[] and to_upper[] are simple arrays that hold the lowercase and uppercase characters corresponding to each member of the character set. For example:

   to_lower['A'] should contain 'a'
   to_upper['a'] should contain 'A'
   

   sort_order[] is a map indicating how characters should be ordered for comparison and sorting purposes. For many character sets, this is the same as to_upper[] (which means sorting will be case insensitive). MySQL will sort characters based on the value of sort_order[character]. ctype[] is an array of bit values, with one element for one character. (Note that to_lower[], to_upper[] and sort_order[] are indexed by character value, but ctype[] is indexed by character value + 1. This is an old legacy to be able to handle EOF.) You can find the following bitmask definitions in `m_ctype.h':

   #define _U      01      /* Upper case */
   #define _L      02      /* Lower case */
   #define _N      04      /* Numeral (digit) */
   #define _S      010     /* Spacing character */
   #define _P      020     /* Punctuation */
   #define _C      040     /* Control character */
   #define _B      0100    /* Blank */
   #define _X      0200    /* heXadecimal digit */
   

   The ctype[] entry for each character should be the union of the applicable bitmask values that describe the character. For example, 'A' is an uppercase character (_U) as well as a hexadecimal digit (_X), so ctype['A'+1] should contain the value:

   _U + _X = 01 + 0200 = 0201
   

4. Add a unique number for your character set to `include/m_ctype.h.in'.
5. Add the character set name to the CHARSETS_AVAILABLE list in configure.in.
6. Reconfigure, recompile and test.

10.1.3 Multi-byte character support

If you are creating a multi-byte character set, you can use the _MB macros. In `include/m_ctype.h.in', add:

#define MY_CHARSET_MYSET  X
#if MY_CHARSET_CURRENT == MY_CHARSET_MYSET
#define USE_MB
#define USE_MB_IDENT
#define ismbchar(p, end)  (...)
#define ismbhead(c)       (...)
#define mbcharlen(c)      (...)
#define MBMAXLEN          N
#endif

Where:

10.2 The update log

When started with the --log-update[=file_name] option, mysqld writes a log file containing all SQL commands that update data. If no file name is given, it defaults to the name of the host machine, If file name is given, but it doesn't contain a path the file is written in the data directory. If file_name doesn't have an extension, mysqld will create log file names of type file_name.#, where # is a number that is incremented each time you execute mysqladmin refresh or mysqladmin flush-logs, the FLUSH LOGS statement, or restart the server.

If you use the --log or -l options, mysqld writes a general log with a filename of `hostname.log', and restarts and refreshes do not cause a new log file to be generated (although it is closed and reopened). By default, the mysql.server script starts the MySQL server with the -l option. If you need better performance when you start using MySQL in a production environment, you can remove the -l option from mysql.server.

Update logging is smart since it logs only statements that really update data. So an UPDATE or a DELETE with a WHERE that finds no rows is not written to the log. It even skips UPDATE statements that set a column to the value it already has.

If you want to update a database from update log files, you could do the following (assuming your update logs have names of the form `file_name.#'):

shell> ls -1 -t -r file_name.[0-9]* | xargs cat | mysql

ls is used to get all the log files in the right order.

This can be useful if you have to revert to backup files after a crash and you want to redo the updates that occurred between the time of the backup and the crash.

You can also use the update logs when you have a mirrored database on another host and you want to replicate the changes that have been made to the master database.

10.3 How big MySQL tables can be

MySQL 3.22 has a 4G limit on table size. With the new MyISAM in MySQL 3.23 the maximum table size is pushed up to 8 million terabytes (2 ^ 63 bytes).

Note however that operating systems have their own file size limits. Here are some examples:

This means that the table size for MySQL is normally limited by the operating system.

By default, MySQL tables have a maximum size of about 4G. You can check the maximum table size for a table with the SHOW TABLE STATUS command or with the myisamchk -dv table_name. See section 7.23 SHOW syntax (Get information about tables, columns,...).

If you need bigger tables than 4G (and your operating system supports this), you should set the AVG_ROW_LENGTH and MAX_ROWS parameter when you create your table. See section 7.7 CREATE TABLE syntax. You can also set these later with ALTER TABLE. See section 7.8 ALTER TABLE syntax.

If you need to have bigger tables than 2G / 4G

If your big table is going to be read-only, you could use myisampack to merge and compress many tables to one. myisampack usually compresses a table by at least 50%, so you can have, in effect, much bigger tables. See section 13.7 The MySQL compressed read-only table generator.

Another solution can be the included MERGE library, which allows you to handle a collection of identical tables as one. (Identical in this case means that all tables are created with identical column information.) Currently MERGE can only be used to scan a collection of tables because it doesn't support indexes. We will add indexes to this in the near future.

Go to the first, previous, next, last section, table of contents.