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

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.

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

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.

@bullet{MyISAM}

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:

• One can INSERT new rows in a table without deleted rows, at the same times as other threads are reading from the table.
• Supports for big files (63 bit) on filesystems/operating systems that support big files.
• All data are stored with low byte first. This makes the data machine and OS independent. The only requirement is that the machine uses 2 complement signed integers (as every machine for the last 20 years has) and IEEE floating point format (also totally dominant among mainstream machines). The only area of machines that may not support binary compatibility are embedded systems (since they sometimes have peculiar processors).
• All number keys are stored with high byte first to give better index compression.
• Internal handling of one AUTO_INCREMENT column. MyISAM will automatically update this on INSERT/UPDATE. The AUTO_INCREMENT value can be reset with myisamchk. This will make AUTO_INCREMENT columns faster and old numbers will not be reused as with the old ISAM. Note that when a AUTO_INCREMENT is defined on the end of a multi-part-key the old behavior is still present.
• BLOB and TEXT columns can be indexed.
• NULL values are allowed in indexed columns. This takes 0-1 bytes/key.
• Maximum key length is now 500 bytes by default. In cases of keys longer than 250 bytes, a bigger key block size than the default of 1024 bytes is used for this key.
• Maximum number of keys/table enlarged to 32 as default. This can be enlarged to 64 without having to recompile myisamchk.
• There is a flag in the MyISAM file that indicates whether or not the table was closed correctly. This will soon be used for automatic repair in the MySQL server.
• myisamchk will now mark tables as checked. myisamchk --fast will only check those tables that don't have this mark.
• myisamchk -a stores statistics for key parts (and not only for whole keys as in ISAM).
• Dynamic size rows will now be much less fragmented when mixing deletes with updates and inserts. This is done by automatically combining adjacent deleted blocks and by extending blocks if the next block is deleted.
• myisampack can pack BLOB and VARCHAR columns.

MyISAM also supports the following things, which MySQL will be able to use in the near future.

• Support for a true VARCHAR type; A VARCHAR column starts with a length stored in 2 bytes.
• Tables with VARCHAR may have fixed or dynamic record length.
• VARCHAR and CHAR may be up to 64K. All key segments have their own language definition. This will enable MySQL to have different language definitions per column.
• A hashed computed index can be used for UNIQUE; This will allow you to have UNIQUE on any combination of columns in a table. (You can't search on a UNIQUE computed index, however.)

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:

• Compressed and fixed length keys
• Fixed and dynamic record length
• 16 keys with 16 key parts / key
• Max key length 256 (default)
• Data is stored in machine format; Fast but is machine/OS dependent.

@bullet{HEAP}

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;
DROP TABLE test;

Here are some things you should consider when you use HEAP tables:

• You should always use specify MAX_ROWS in the CREATE statement to ensure that you accidently do not use all memory.
• Indexes will only be used with = and <=> (but are VERY fast).
• HEAP tables uses a fixed record length format.
• HEAP doesn't support BLOB/TEXT columns.
• HEAP doesn't support AUTO_INCREMENT columns.
• HEAP doesn't support an index on a NULL column.
• You can have non-unique keys in a HEAP table (not that normal with hashed tables).
• HEAP tables are shared between all clients (just like any other table).
• Data for HEAP tables are allocated in small blocks. The tables are 100% dynamic (on inserting). No overflow areas and no extra key space is needed. Deleted rows are put in a linked list and will be reused when you insert new data into the table.
• To free memory, you should execute DELETE FROM heap_table or DROP TABLE heap_table.
• To ensure that you accidentally don't do anything stupid, you can't create HEAP tables bigger than max_heap_table_size.

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