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

9 MySQL server functions

9.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


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.

9.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:

9.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.

9.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 USE_MB
#define USE_MB_IDENT
#define ismbchar(p, end)  (...)
#define ismbhead(c)       (...)
#define mbcharlen(c)      (...)
#define MBMAXLEN          N


MY_CHARSET_MYSET A unique character set value.
USE_MB This character set has multi-byte characters, handled by ismbhead() and mbcharlen()
USE_MB_IDENT (optional) If defined, you can use table and column names that use multi-byte characters
ismbchar(p, e) return 0 if p is not a multi-byte character string, or the size of the character (in bytes) if it is. p and e point to the beginning and end of the string. Check from (char*)p to (char*)e-1.
ismbhead(c) True if c is the first character of a multi-byte character string
mbcharlen(c) Size of a multi-byte character string if c is the first character of such a string
MBMAXLEN Size in bytes of the largest character in the set

9.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. The file is written in the data directory and has a name of 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.

9.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. On Linux, the current limit is 2G; on Solaris 2.5.1, the limit is 4G; on Solaris 2.6, the limit is 1000G. 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.21 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 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 12.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.

9.4 MySQL table types

With MySQL you can currently (version 3.23.6) choose between three basic table formats. When you create a new table, you can tell MySQL which table type it should use for the table. MySQL will always create a .frm file to hold the table and column definitions. Depending on the table type the index and data will be stored in other files.

You can convert tables between different types with the ALTER TABLE statement. See section 7.8 ALTER TABLE syntax.

MyISAM is the default table type in MySQL 3.23. It's based on the ISAM code and has a lot of useful extensions. The index is stored in a file with the .MYI (MYindex) extension and the data is stored in file with the .MYD (MYData) extension. You can check/repair MyISAM tables with the myisamchk utility. See section 13.4 Using myisamchk for crash recovery. The following is new in MyISAM: MyISAM also supports the following things, which MySQL will be able to use in the near future. You can also use the deprecated ISAM table type. This will disappear in rather soon since MyISAM is a better implementation of the same thing. ISAM uses a B-tree index. The index is stored in a file with the .ISM extension and the data is stored in file with the .ISD extension. You can check/repair ISAM tables with the isamchk utility. See section 13.4 Using myisamchk for crash recovery. ISAM tables are not binary portable across OS/Platforms. ISAM has the following features/properties:
HEAP tables use a hashed index and are stored in memory. This makes them very fast, but if MySQL crashes you will lose all data stored in them. HEAP is very usable as temporary tables!
CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) as down
        FROM log_table GROUP BY ip;
SELECT COUNT(ip),AVG(down) FROM test;
Here are some things you should consider when you use HEAP tables:

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