Go to the first, previous, next, last section, table of contents.
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.
mysqld with a particular language, use either the
-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
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.
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
You can change the character set at compile time by using the
--with-charset=charset option to
See section 4.7.1 Quick installation overview.
To add another character set to MySQL, use the following procedure:
Choose a name for the character set, denoted
Create the file
`strings/ctype-MYSET.c' in the MySQL source distribution.
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
to_lower_MYSET and so on.
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
ctype is an array of bit values, with one element for one character.
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 */
ctype entry for each character should be the union of the
applicable bitmask values that describe the character.
'A' is an uppercase character (
_U) as well as a
hexadecimal digit (
ctype['A'+1] should contain the value:
_U + _X = 01 + 0200 = 0201
Add a unique number for your character set to `include/m_ctype.h.in'.
Add the character set name to the
CHARSETS_AVAILABLE list in
Reconfigure, recompile and test.
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 ismbchar(p, end) (...)
#define ismbhead(c) (...)
#define mbcharlen(c) (...)
#define MBMAXLEN N
| A unique character set value.
| This character set has multi-byte
characters, handled by |
| (optional) If defined, you can use table and
column names that use multi-byte characters
| return 0 if |
p is not a multi-byte
character string, or the size of the character (in bytes) if it is.
e point to the beginning and end of the string.
| True if |
c is the first character of
a multi-byte character string
| Size of a multi-byte character string if
c is the first character of such a string
| Size in bytes of the largest character in
When started with the
writes a log file containing all SQL commands that update data. The file is
written in the data directory and has a name of
# is a number that is incremented each time you execute
mysqladmin refresh or
mysqladmin flush-logs, the
LOGS statement, or restart the
If you use the
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
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
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.
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
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.5 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.
With MySQL you can currently (version 3.23.6) choose between tree 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
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
utility. See section 13.4 Using
myisamchk for crash recovery.
The following is new in
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
All number keys are stored with high byte first to give better index
Internal handling of one
will automatically update this on
AUTO_INCREMENT value can be reset with
AUTO_INCREMENT columns faster and old numbers will not
be reused as with the old ISAM. Note that when a
is defined on the end of a multi-part-key the old behavior is still present.
TEXT columns can be indexed.
NULL values are allowed in indexed columns. This takes 0-1
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
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.
--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
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
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
Support for a true
VARCHAR type; A
VARCHAR column starts
with a length stored in 2 bytes.
VARCHAR may have fixed or dynamic record length.
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.)
MyISAM is a better implementation of the same
thing. ISAM uses a
B-tree index. The index is stored in a file
.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.
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
You should always use specify
MAX_ROWS in the
to ensure that you accidently do not use all memory.
Indexes will only be used with
<=> (but are VERY fast).
HEAP tables uses a fixed record length format.
HEAP doesn't support
HEAP doesn't support
HEAP doesn't support an index on a
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
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
Go to the first, previous, next, last section, table of contents.