MySQL数据库

开发平台：

Visual C++

manual.txt：源码内容

This space is not needed if one does a repair with `--quick', as
in this case only the index file will be re-created. This space
is needed on the same disk as the original record file!
* Space for the new index file that replaces the old one. The old
index file is truncated at start, so one usually ignore this space.
This space is needed on the same disk as the original index file!
* When using `--recover' or `--sort-recover' (but not when using
`--safe-recover', you will need space for a sort buffer for:
`(largest_key + row_pointer_length)*number_of_rows * 2'. You can
check the length of the keys and the row_pointer_length with
`myisamchk -dv table'. This space is allocated on the temporary
disk (specified by `TMPDIR' or `--tmpdir=#').
If you have a problem with disk space during repair, you can try to use
`--safe-recover' instead of `--recover'.
Setting Up a Table Maintenance Regimen
======================================
Starting with *MySQL* Version 3.23.13, you can check MyISAM tables with
the `CHECK TABLE' command. *Note CHECK TABLE::. You can repair tables
with the `REPAIR TABLE' command. *Note REPAIR TABLE::.
It is a good idea to perform table checks on a regular basis rather than
waiting for problems to occur. For maintenance purposes, you can use
`myisamchk -s' to check tables. The `-s' option (short for `--silent')
causes `myisamchk' to run in silent mode, printing messages only when
errors occur.
It's also a good idea to check tables when the server starts up. For
example, whenever the machine has done a reboot in the middle of an
update, you usually need to check all the tables that could have been
affected. (This is an "expected crashed table".) You could add a test to
`safe_mysqld' that runs `myisamchk' to check all tables that have been
modified during the last 24 hours if there is an old `.pid' (process
ID) file left after a reboot. (The `.pid' file is created by `mysqld'
when it starts up and removed when it terminates normally. The
presence of a `.pid' file at system startup time indicates that
`mysqld' terminated abnormally.)
An even better test would be to check any table whose last-modified time
is more recent than that of the `.pid' file.
You should also check your tables regularly during normal system
operation. At MySQL AB, we run a `cron' job to check all our important
tables once a week, using a line like this in a `crontab' file:
35 0 * * 0 /path/to/myisamchk --fast --silent /path/to/datadir/*/*.MYI
This prints out information about crashed tables so we can examine and
repair them when needed.
As we haven't had any unexpectedly crashed tables (tables that become
corrupted for reasons other than hardware trouble) for a couple of
years now (this is really true), once a week is more than enough for us.
We recommend that to start with, you execute `myisamchk -s' each night
on all tables that have been updated during the last 24 hours, until
you come to trust *MySQL* as much as we do.
Normally you don't need to maintain MySQL tables that much. If you are
changing tables with dynamic size rows (tables with `VARCHAR', `BLOB'
or `TEXT' columns) or have tables with many deleted rows you may want
to from time to time (once a month?) defragment/reclaim space from the
tables.
You can do this by using `OPTIMIZE TABLE' on the tables in question or
if you can take the `mysqld' server down for a while do:
isamchk -r --silent --sort-index -O sort_buffer_size=16M */*.ISM
myisamchk -r --silent --sort-index -O sort_buffer_size=16M */*.MYI
Getting Information About a Table
=================================
To get a description of a table or statistics about it, use the
commands shown below. We explain some of the information in more detail
later:
`myisamchk -d tbl_name'
Runs `myisamchk' in "describe mode" to produce a description of
your table. If you start the *MySQL* server using the
`--skip-locking' option, `myisamchk' may report an error for a
table that is updated while it runs. However, because `myisamchk'
doesn't change the table in describe mode, there isn't any risk of
destroying data.
`myisamchk -d -v tbl_name'
To produce more information about what `myisamchk' is doing, add
`-v' to tell it to run in verbose mode.
`myisamchk -eis tbl_name'
Shows only the most important information from a table. It is slow
because it must read the whole table.
`myisamchk -eiv tbl_name'
This is like `-eis', but tells you what is being done.
Example of `myisamchk -d' output:
MyISAM file: company.MYI
Record format: Fixed length
Data records: 1403698 Deleted blocks: 0
Recordlength: 226
table description:
Key Start Len Index Type
1 2 8 unique double
2 15 10 multip. text packed stripped
3 219 8 multip. double
4 63 10 multip. text packed stripped
5 167 2 multip. unsigned short
6 177 4 multip. unsigned long
7 155 4 multip. text
8 138 4 multip. unsigned long
9 177 4 multip. unsigned long
193 1 text
Example of `myisamchk -d -v' output:
MyISAM file: company
Record format: Fixed length
File-version: 1
Creation time: 1999-10-30 12:12:51
Recover time: 1999-10-31 19:13:01
Status: checked
Data records: 1403698 Deleted blocks: 0
Datafile parts: 1403698 Deleted data: 0
Datafilepointer (bytes): 3 Keyfile pointer (bytes): 3
Max datafile length: 3791650815 Max keyfile length: 4294967294
Recordlength: 226
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 8 unique double 1 15845376 1024
2 15 10 multip. text packed stripped 2 25062400 1024
3 219 8 multip. double 73 40907776 1024
4 63 10 multip. text packed stripped 5 48097280 1024
5 167 2 multip. unsigned short 4840 55200768 1024
6 177 4 multip. unsigned long 1346 65145856 1024
7 155 4 multip. text 4995 75090944 1024
8 138 4 multip. unsigned long 87 85036032 1024
9 177 4 multip. unsigned long 178 96481280 1024
193 1 text
Example of `myisamchk -eis' output:
Checking MyISAM file: company
Key: 1: Keyblocks used: 97% Packed: 0% Max levels: 4
Key: 2: Keyblocks used: 98% Packed: 50% Max levels: 4
Key: 3: Keyblocks used: 97% Packed: 0% Max levels: 4
Key: 4: Keyblocks used: 99% Packed: 60% Max levels: 3
Key: 5: Keyblocks used: 99% Packed: 0% Max levels: 3
Key: 6: Keyblocks used: 99% Packed: 0% Max levels: 3
Key: 7: Keyblocks used: 99% Packed: 0% Max levels: 3
Key: 8: Keyblocks used: 99% Packed: 0% Max levels: 3
Key: 9: Keyblocks used: 98% Packed: 0% Max levels: 4
Total: Keyblocks used: 98% Packed: 17%
Records: 1403698 M.recordlength: 226 Packed: 0%
Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00
Record blocks: 1403698 Delete blocks: 0
Recorddata: 317235748 Deleted data: 0
Lost space: 0 Linkdata: 0
User time 1626.51, System time 232.36
Maximum resident set size 0, Integral resident set size 0
Non physical pagefaults 0, Physical pagefaults 627, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 639, Involuntary context switches 28966
Example of `myisamchk -eiv' output:
Checking MyISAM file: company
Data records: 1403698 Deleted blocks: 0
- check file-size
- check delete-chain
block_size 1024:
index 1:
index 2:
index 3:
index 4:
index 5:
index 6:
index 7:
index 8:
index 9:
No recordlinks
- check index reference
- check data record references index: 1
Key: 1: Keyblocks used: 97% Packed: 0% Max levels: 4
- check data record references index: 2
Key: 2: Keyblocks used: 98% Packed: 50% Max levels: 4
- check data record references index: 3
Key: 3: Keyblocks used: 97% Packed: 0% Max levels: 4
- check data record references index: 4
Key: 4: Keyblocks used: 99% Packed: 60% Max levels: 3
- check data record references index: 5
Key: 5: Keyblocks used: 99% Packed: 0% Max levels: 3
- check data record references index: 6
Key: 6: Keyblocks used: 99% Packed: 0% Max levels: 3
- check data record references index: 7
Key: 7: Keyblocks used: 99% Packed: 0% Max levels: 3
- check data record references index: 8
Key: 8: Keyblocks used: 99% Packed: 0% Max levels: 3
- check data record references index: 9
Key: 9: Keyblocks used: 98% Packed: 0% Max levels: 4
Total: Keyblocks used: 9% Packed: 17%
- check records and index references
[LOTS OF ROW NUMBERS DELETED]
Records: 1403698 M.recordlength: 226 Packed: 0%
Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00
Record blocks: 1403698 Delete blocks: 0
Recorddata: 317235748 Deleted data: 0
Lost space: 0 Linkdata: 0
User time 1639.63, System time 251.61
Maximum resident set size 0, Integral resident set size 0
Non physical pagefaults 0, Physical pagefaults 10580, Swaps 0
Blocks in 4 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 10604, Involuntary context switches 122798
Here are the sizes of the data and index files for the table used in the
preceding examples:
-rw-rw-r-- 1 monty tcx 317235748 Jan 12 17:30 company.MYD
-rw-rw-r-- 1 davida tcx 96482304 Jan 12 18:35 company.MYM
Explanations for the types of information `myisamchk' produces are
given below. The "keyfile" is the index file. "Record" and "row" are
synonymous:
`ISAM file'
Name of the ISAM (index) file.
`Isam-version'
Version of ISAM format. Currently always 2.
`Creation time'
When the data file was created.
`Recover time'
When the index/data file was last reconstructed.
`Data records'
How many records are in the table.
`Deleted blocks'
How many deleted blocks still have reserved space. You can
optimize your table to minimize this space. *Note Optimization::.
`Datafile: Parts'
For dynamic record format, this indicates how many data blocks
there are. For an optimized table without fragmented records, this
is the same as `Data records'.
`Deleted data'
How many bytes of non-reclaimed deleted data there are. You can
optimize your table to minimize this space. *Note Optimization::.
`Datafile pointer'
The size of the data file pointer, in bytes. It is usually 2, 3,
4, or 5 bytes. Most tables manage with 2 bytes, but this cannot be
controlled from *MySQL* yet. For fixed tables, this is a record
address. For dynamic tables, this is a byte address.
`Keyfile pointer'
The size of the index file pointer, in bytes. It is usually 1, 2,
or 3 bytes. Most tables manage with 2 bytes, but this is calculated
automatically by *MySQL*. It is always a block address.
`Max datafile length'
How long the table's data file (`.MYD' file) can become, in bytes.
`Max keyfile length'
How long the table's key file (`.MYI' file) can become, in bytes.
`Recordlength'
How much space each record takes, in bytes.
`Record format'
The format used to store table rows. The examples shown above use
`Fixed length'. Other possible values are `Compressed' and
`Packed'.
`table description'
A list of all keys in the table. For each key, some low-level
information is presented:
`Key'
This key's number.
`Start'
Where in the record this index part starts.
`Len'
How long this index part is. For packed numbers, this should
always be the full length of the column. For strings, it may
be shorter than the full length of the indexed column,
because you can index a prefix of a string column.
`Index'
`unique' or `multip.' (multiple). Indicates whether or not
one value can exist multiple times in this index.
`Type'
What data-type this index part has. This is an ISAM data-type
with the options `packed', `stripped' or `empty'.
`Root'
Address of the root index block.
`Blocksize'
The size of each index block. By default this is 1024, but
the value may be changed at compile time.
`Rec/key'
This is a statistical value used by the optimizer. It tells
how many records there are per value for this key. A unique
key always has a value of 1. This may be updated after a
table is loaded (or greatly changed) with `myisamchk -a'. If
this is not updated at all, a default value of 30 is given.
`'
In the first example above, the 9th key is a multi-part key with
two parts.
`Keyblocks used'
What percentage of the keyblocks are used. Because the table used
in the examples had just been reorganized with `myisamchk', the
values are very high (very near the theoretical maximum).
`Packed'
*MySQL* tries to pack keys with a common suffix. This can only be
used for `CHAR'/`VARCHAR'/`DECIMAL' keys. For long strings like
names, this can significantly reduce the space used. In the third
example above, the 4th key is 10 characters long and a 60%
reduction in space is achieved.
`Max levels'
How deep the B-tree for this key is. Large tables with long keys
get high values.
`Records'
How many rows are in the table.
`M.recordlength'
The average record length. For tables with fixed-length records,
this is the exact record length.
`Packed'
*MySQL* strips spaces from the end of strings. The `Packed' value
indicates the percentage of savings achieved by doing this.
`Recordspace used'
What percentage of the data file is used.
`Empty space'
What percentage of the data file is unused.
`Blocks/Record'
Average number of blocks per record (that is, how many links a
fragmented record is composed of). This is always 1 for
fixed-format tables. This value should stay as close to 1.0 as
possible. If it gets too big, you can reorganize the table with
`myisamchk'. *Note Optimization::.
`Recordblocks'
How many blocks (links) are used. For fixed format, this is the
same as the number of records.
`Deleteblocks'
How many blocks (links) are deleted.
`Recorddata'
How many bytes in the data file are used.
`Deleted data'
How many bytes in the data file are deleted (unused).
`Lost space'
If a record is updated to a shorter length, some space is lost.
This is the sum of all such losses, in bytes.
`Linkdata'
When the dynamic table format is used, record fragments are linked
with pointers (4 to 7 bytes each). `Linkdata' is the sum of the
amount of storage used by all such pointers.
If a table has been compressed with `myisampack', `myisamchk -d' prints
additional information about each table column. See *Note
`myisampack': myisampack, for an example of this information and a
description of what it means.
Using `myisamchk' for Crash Recovery
====================================
If you run `mysqld' with `--skip-locking' (which is the default on some
systems, like Linux), you can't reliably use `myisamchk' to check a
table when `mysqld' is using the same table. If you can be sure that
no one is accessing the tables through `mysqld' while you run
`myisamchk', you only have to do `mysqladmin flush-tables' before you
start checking the tables. If you can't guarantee the above, then you
must take down `mysqld' while you check the tables. If you run
`myisamchk' while `mysqld' is updating the tables, you may get a
warning that a table is corrupt even if it isn't.
If you are not using `--skip-locking', you can use `myisamchk' to check
tables at any time. While you do this, all clients that try to update
the table will wait until `myisamchk' is ready before continuing.
If you use `myisamchk' to repair or optimize tables, you *MUST* always
ensure that the `mysqld' server is not using the table (this also
applies if you are using `--skip-locking'). If you don't take down
`mysqld' you should at least do a `mysqladmin flush-tables' before you
run `myisamchk'.
The file format that *MySQL* uses to store data has been extensively
tested, but there are always external circumstances that may cause
database tables to become corrupted:
* The `mysqld' process being killed in the middle of a write.
* Unexpected shutdown of the computer (for example, if the computer
is turned off).
* A hardware error.
This chapter describes how to check for and deal with data corruption
in *MySQL* databases. If your tables get corrupted a lot you should
try to find the reason for this! *Note Debugging server::.
When performing crash recovery, it is important to understand that each
table `tbl_name' in a database corresponds to three files in the
database directory:
*File* *Purpose*
`tbl_name.frm' Table definition (form) file
`tbl_name.MYD' Data file
`tbl_name.MYI' Index file
Each of these three file types is subject to corruption in various
ways, but problems occur most often in data files and index files.
`myisamchk' works by creating a copy of the `.MYD' (data) file row by
row. It ends the repair stage by removing the old `.MYD' file and
renaming the new file to the original file name. If you use `--quick',
`myisamchk' does not create a temporary `.MYD' file, but instead
assumes that the `.MYD' file is correct and only generates a new index
file without touching the `.MYD' file. This is safe, because
`myisamchk' automatically detects if the `.MYD' file is corrupt and
aborts the repair in this case. You can also give two `--quick'
options to `myisamchk'. In this case, `myisamchk' does not abort on
some errors (like duplicate key) but instead tries to resolve them by
modifying the `.MYD' file. Normally the use of two `--quick' options is
useful only if you have too little free disk space to perform a normal
repair. In this case you should at least make a backup before running
`myisamchk'.
How to Check Tables for Errors
------------------------------
To check a MyISAM table, use the following commands:
`myisamchk tbl_name'
This finds 99.99% of all errors. What it can't find is corruption
that involves *ONLY* the data file (which is very unusual). If you
want to check a table, you should normally run `myisamchk' without
options or with either the `-s' or `--silent' option.
`myisamchk -m tbl_name'
This finds 99.999% of all errors. It checks first all index
entries for errors and then it reads through all rows. It
calculates a checksum for all keys in the rows and verifies that
they checksum matches the checksum for the keys in the index tree.
`myisamchk -e tbl_name'
This does a complete and thorough check of all data (`-e' means
"extended check"). It does a check-read of every key for each row
to verify that they indeed point to the correct row. This may
take a LONG time on a big table with many keys. `myisamchk' will
normally stop after the first error it finds. If you want to
obtain more information, you can add the `--verbose' (`-v')
option. This causes `myisamchk' to keep going, up through a
maximum of 20 errors. In normal usage, a simple `myisamchk' (with
no arguments other than the table name) is sufficient.
`myisamchk -e -i tbl_name'
Like the previous command, but the `-i' option tells `myisamchk' to
print some informational statistics, too.
How to Repair Tables
--------------------
In the following section we only talk about using `myisamchk' on
`MyISAM' tables (extensions `.MYI' and `.MYD'). If you are using
`ISAM' tables (extensions `.ISM' and `.ISD'), you should use `isamchk'
instead.
Starting with *MySQL* Version 3.23.14, you can repair MyISAM tables
with the `REPAIR TABLE' command. *Note REPAIR TABLE::.
The symptoms of a corrupted table include queries that abort
unexpectedly and observable errors such as these:
* `tbl_name.frm' is locked against change
* Can't find file `tbl_name.MYI' (Errcode: ###)
* Unexpected end of file
* Record file is crashed
* Got error ### from table handler
To get more information about the error you can do `perror ###'.
Here is the most common errors that indicates a problem with the
table:
shell> perror 126 127 132 134 135 136 141 144 145
126 = Index file is crashed / Wrong file format
127 = Record-file is crashed
132 = Old database file
134 = Record was already deleted (or record file crashed)
135 = No more room in record file
136 = No more room in index file
141 = Duplicate unique key or constraint on write or update
144 = Table is crashed and last repair failed
145 = Table was marked as crashed and should be repaired
Note that error 135, no more room in record file, is not a error
that can be fixed by a simple repair. In this case you have to do:
* `CREATE TABLE ...' for the table with proper `MAX_ROWS' and
`AVG_ROW_LENGTH' values. *Note CREATE TABLE::.
* Copy the data over from the old table with `INSERT INTO
new_table SELECT * from old_table'.
* Rename the old table to the new table: `RENAME old_table to
tmp_table, new_table to old_table'
* Delete the old table: `DROP TABLE tmp_table'.
In these cases, you must repair your tables. `myisamchk' can usually
detect and fix most things that go wrong.
The repair process involves up to four stages, described below. Before
you begin, you should `cd' to the database directory and check the
permissions of the table files. Make sure they are readable by the Unix
user that `mysqld' runs as (and to you, because you need to access the
files you are checking). If it turns out you need to modify files,
they must also be writable by you.
If you are using *MySQL* Version 3.23.16 and above, you can (and
should) use the `CHECK' and `REPAIR' commands to check and repair
`MyISAM' tables. *Note CHECK TABLE::. *Note REPAIR TABLE::.
The manual section about table maintenance includes the options to
`isamchk'/`myisamchk'. *Note Table maintenance::.
The following section is for the cases where the above command fails or
if you want to use the extended features that `isamchk'/`myisamchk'
provides.
If you are going to repair a table from the command line, you must first
take down the `mysqld' server. Note that when you do `mysqladmin
shutdown' on a remote server, the `mysqld' server will still be alive
for a while after `mysqladmin' returns, until all queries are stopped
and all keys have been flushed to disk.
*Stage 1: Checking your tables*
Run `myisamchk *.MYI' or `myisamchk -e *.MYI' if you have more time.
Use the `-s' (silent) option to suppress unnecessary information.
If the mysqld server is done you should use the -update option to tell
`myisamchk' to mark the table as 'checked'.
You have to repair only those tables for which `myisamchk' announces an
error. For such tables, proceed to Stage 2.
If you get weird errors when checking (such as `out of memory' errors),
or if `myisamchk' crashes, go to Stage 3.
*Stage 2: Easy safe repair*
NOTE: If you want repairing to go much faster, you should add: `-O
sort_buffer=# -O key_buffer=#' (where # is about 1/4 of the available
memory) to all `isamchk/myisamchk' commands.
First, try `myisamchk -r -q tbl_name' (`-r -q' means "quick recovery
mode"). This will attempt to repair the index file without touching the
data file. If the data file contains everything that it should and the
delete links point at the correct locations within the data file, this
should work, and the table is fixed. Start repairing the next table.
Otherwise, use the following procedure:
1. Make a backup of the data file before continuing.
2. Use `myisamchk -r tbl_name' (`-r' means "recovery mode"). This will
remove incorrect records and deleted records from the data file and
reconstruct the index file.
3. If the preceding step fails, use `myisamchk --safe-recover
tbl_name'. Safe recovery mode uses an old recovery method that
handles a few cases that regular recovery mode doesn't (but is
slower).
If you get weird errors when repairing (such as `out of memory'
errors), or if `myisamchk' crashes, go to Stage 3.
*Stage 3: Difficult repair*
You should only reach this stage if the first 16K block in the index
file is destroyed or contains incorrect information, or if the index
file is missing. In this case, it's necessary to create a new index
file. Do so as follows:
1. Move the data file to some safe place.
2. Use the table description file to create new (empty) data and
index files:
shell> mysql db_name
mysql> SET AUTOCOMMIT=1;
mysql> TRUNCATE TABLE table_name;
mysql> quit
If your SQL version doesn't have `TRUNCATE TABLE', use `DELETE FROM
table_name' instead.
3. Copy the old data file back onto the newly created data file.
(Don't just move the old file back onto the new file; you want to
retain a copy in case something goes wrong.)
Go back to Stage 2. `myisamchk -r -q' should work now. (This shouldn't
be an endless loop.)
*Stage 4: Very difficult repair*
You should reach this stage only if the description file has also
crashed. That should never happen, because the description file isn't
changed after the table is created:
1. Restore the description file from a backup and go back to Stage 3.
You can also restore the index file and go back to Stage 2. In
the latter case, you should start with `myisamchk -r'.
2. If you don't have a backup but know exactly how the table was
created, create a copy of the table in another database. Remove
the new data file, then move the description and index files from
the other database to your crashed database. This gives you new
description and index files, but leaves the data file alone. Go
back to Stage 2 and attempt to reconstruct the index file.
Table Optimization
------------------
To coalesce fragmented records and eliminate wasted space resulting from
deleting or updating records, run `myisamchk' in recovery mode:
shell> myisamchk -r tbl_name
You can optimize a table in the same way using the SQL `OPTIMIZE TABLE'
statement. `OPTIMIZE TABLE' does a repair of the table, a key analyzes
and also sorts the index tree to give faster key lookups. There is
also no possibility of unwanted interaction between a utility and the
server, because the server does all the work when you use `OPTIMIZE
TABLE'. *Note OPTIMIZE TABLE::.
`myisamchk' also has a number of other options you can use to improve
the performance of a table:
`-S, --sort-index'
`-R index_num, --sort-records=index_num'
`-a, --analyze'
For a full description of the option, see *Note myisamchk syntax::.
Log file Maintenance
====================
*MySQL* has a lot of log files which make it easy to see what is going.
*Note Log files::. One must however from time to time clean up after
`MysQL' to ensure that the logs doesn't take up too much disk space.
When using *MySQL* with log files, you will, from time to time, want to
remove/backup old log files and tell *MySQL* to start logging on new
files. *Note Backup::.
On a Linux (`Redhat') installation, you can use the `mysql-log-rotate'
script for this. If you installed *MySQL* from an RPM distribution, the
script should have been installed automatically. Note that you should
be careful with this if you are using the log for replication!
On other systems you must install a short script yourself that you
start from `cron' to handle log files.
You can force *MySQL* to start using new log files by using `mysqladmin
flush-logs' or by using the SQL command `FLUSH LOGS'. If you are using
*MySQL* Version 3.21 you must use `mysqladmin refresh'.
The above command does the following:
* If standard logging (`--log') or slow query logging
(`--log-slow-queries') is used, closes and reopens the log file.
(`mysql.log' and ``hostname`-slow.log' as default).
* If update logging (`--log-update') is used, closes the update log
and opens a new log file with a higher sequence number.
If you are using only an update log, you only have to flush the logs
and then move away the old update log files to a backup. If you are
using the normal logging, you can do something like:
shell> cd mysql-data-directory
shell> mv mysql.log mysql.old
shell> mysqladmin flush-logs
and then take a backup and remove `mysql.old'.
Adding New Functions to MySQL
*****************************
There are two ways to add new functions to *MySQL*:
* You can add the function through the user-definable function (UDF)
interface. User-definable functions are added and removed
dynamically using the `CREATE FUNCTION' and `DROP FUNCTION'
statements. *Note `CREATE FUNCTION': CREATE FUNCTION.
* You can add the function as a native (built in) *MySQL* function.
Native functions are compiled into the `mysqld' server and become
available on a permanent basis.
Each method has advantages and disadvantages:
* If you write a user-definable function, you must install the
object file in addition to the server itself. If you compile your
function into the server, you don't need to do that.
* You can add UDFs to a binary *MySQL* distribution. Native
functions require you to modify a source distribution.
* If you upgrade your *MySQL* distribution, you can continue to use
your previously installed UDFs. For native functions, you must
repeat your modifications each time you upgrade.
Whichever method you use to add new functions, they may be used just
like native functions such as `ABS()' or `SOUNDEX()'.
Adding a New User-definable Function
====================================
For the UDF mechanism to work, functions must be written in C or C++
and your operating system must support dynamic loading. The *MySQL*
source distribution includes a file `sql/udf_example.cc' that defines 5
new functions. Consult this file to see how UDF calling conventions
work.
For mysqld to be able to use UDF functions, you should configure MySQL
with `--with-mysqld-ldflags=-rdynamic' The reason is that to on many
platforms you can load a dynamic library (with `dlopen()') from a
static linked program, which you would get if you are using
`--with-mysqld-ldflags=-all-static' If you want to use an UDF that
needs to access symbols from mysqld (like the `methaphone' example in
`sql/udf_example.cc' that uses `default_charset_info'), you must link
the program with `-rdynamic'. (see `man dlopen').
For each function that you want to use in SQL statements, you should
define corresponding C (or C++) functions. In the discussion below,
the name "xxx" is used for an example function name. To distinquish
between SQL and C/C++ usage, `XXX()' (uppercase) indicates a SQL
function call, and `xxx()' (lowercase) indicates a C/C++ function call.
The C/C++ functions that you write to implement the inferface for
`XXX()' are:
`xxx()' (required)
The main function. This is where the function result is computed.
The correspondence between the SQL type and return type of your
C/C++ function is shown below:
*SQL type* *C/C++ type*
`STRING' `char *'
`INTEGER' `long long'
`REAL' `double'
`xxx_init()' (optional)
The initialization function for `xxx()'. It can be used to:
* Check the number of arguments to `XXX()'.
* Check that the arguments are of a required type or,
alternatively, tell *MySQL* to coerce arguments to the types
you want when the main function is called.
* Allocate any memory required by the main function.
* Specify the maximum length of the result.
* Specify (for `REAL' functions) the maximum number of decimals.
* Specify whether or not the result can be `NULL'.
`xxx_deinit()' (optional)
The deinitialization function for `xxx()'. It should deallocate
any memory allocated by the initialization function.
When a SQL statement invokes `XXX()', *MySQL* calls the initialization
function `xxx_init()' to let it perform any required setup, such as
argument checking or memory allocation. If `xxx_init()' returns an
error, the SQL statement is aborted with an error message and the main
and deinitialization functions are not called. Otherwise, the main
function `xxx()' is called once for each row. After all rows have been
processed, the deinitialization function `xxx_deinit()' is called so it
can perform any required cleanup.
All functions must be thread safe (not just the main function, but the
initialization and deinitialization functions as well). This means that
you are not allowed to allocate any global or static variables that
change! If you need memory, you should allocate it in `xxx_init()' and
free it in `xxx_deinit()'.
UDF Calling Sequences
---------------------
The main function should be declared as shown below. Note that the
return type and parameters differ, depending on whether you will
declare the SQL function `XXX()' to return `STRING', `INTEGER', or
`REAL' in the `CREATE FUNCTION' statement:
For `STRING' functions:
char *xxx(UDF_INIT *initid, UDF_ARGS *args,
char *result, unsigned long *length,
char *is_null, char *error);
For `INTEGER' functions:
long long xxx(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
For `REAL' functions:
double xxx(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
The initialization and deinitialization functions are declared like
this:
my_bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
void xxx_deinit(UDF_INIT *initid);
The `initid' parameter is passed to all three functions. It points to a
`UDF_INIT' structure that is used to communicate information between
functions. The `UDF_INIT' structure members are listed below. The
initialization function should fill in any members that it wishes to
change. (To use the default for a member, leave it unchanged.):
`my_bool maybe_null'
`xxx_init()' should set `maybe_null' to `1' if `xxx()' can return
`NULL'. The default value is `1' if any of the arguments are
declared `maybe_null'.
`unsigned int decimals'
Number of decimals. The default value is the maximum number of
decimals in the arguments passed to the main function. (For
example, if the function is passed `1.34', `1.345', and `1.3', the
default would be 3, because `1.345' has 3 decimals.
`unsigned int max_length'
The maximum length of the string result. The default value
differs depending on the result type of the function. For string
functions, the default is the length of the longest argument. For
integer functions, the default is 21 digits. For real functions,
the default is 13 plus the number of decimals indicated by
`initid->decimals'. (For numeric functions, the length includes
any sign or decimal point characters.)
`char *ptr'
A pointer that the function can use for its own purposes. For
example, functions can use `initid->ptr' to communicate allocated
memory between functions. In `xxx_init()', allocate the memory
and assign it to this pointer:
initid->ptr = allocated_memory;
In `xxx()' and `xxx_deinit()', refer to `initid->ptr' to use or
deallocate the memory.
Argument Processing
-------------------
The `args' parameter points to a `UDF_ARGS' structure that thas the
members listed below:
`unsigned int arg_count'
The number of arguments. Check this value in the initialization
function if you want your function to be called with a particular
number of arguments. For example:
if (args->arg_count != 2)
{
strcpy(message,"XXX() requires two arguments");
return 1;
}
`enum Item_result *arg_type'
The types for each argument. The possible type values are
`STRING_RESULT', `INT_RESULT', and `REAL_RESULT'.
To make sure that arguments are of a given type and return an
error if they are not, check the `arg_type' array in the
initialization function. For example:
if (args->arg_type[0] != STRING_RESULT ||
args->arg_type[1] != INT_RESULT)
{
strcpy(message,"XXX() requires a string and an integer");
return 1;
}
As an alternative to requiring your function's arguments to be of
particular types, you can use the initialization function to set
the `arg_type' elements to the types you want. This causes
*MySQL* to coerce arguments to those types for each call to
`xxx()'. For example, to specify coercion of the first two
arguments to string and integer, do this in `xxx_init()':
args->arg_type[0] = STRING_RESULT;
args->arg_type[1] = INT_RESULT;
`char **args'
`args->args' communicates information to the initialization
function about the general nature of the arguments your function
was called with. For a constant argument `i', `args->args[i]'
points to the argument value. (See below for instructions on how
to access the value properly.) For a non-constant argument,
`args->args[i]' is `0'. A constant argument is an expression that
uses only constants, such as `3' or `4*7-2' or `SIN(3.14)'. A
non-constant argument is an expression that refers to values that
may change from row to row, such as column names or functions that
are called with non-constant arguments.
For each invocation of the main function, `args->args' contains the
actual arguments that are passed for the row currently being
processed.
Functions can refer to an argument `i' as follows:
* An argument of type `STRING_RESULT' is given as a string
pointer plus a length, to allow handling of binary data or
data of arbitrary length. The string contents are available
as `args->args[i]' and the string length is
`args->lengths[i]'. You should not assume that strings are
null-terminated.
* For an argument of type `INT_RESULT', you must cast
`args->args[i]' to a `long long' value:
long long int_val;
int_val = *((long long*) args->args[i]);
* For an argument of type `REAL_RESULT', you must cast
`args->args[i]' to a `double' value:
double real_val;
real_val = *((double*) args->args[i]);
`unsigned long *lengths'
For the initialization function, the `lengths' array indicates the
maximum string length for each argument. For each invocation of
the main function, `lengths' contains the actual lengths of any
string arguments that are passed for the row currently being
processed. For arguments of types `INT_RESULT' or `REAL_RESULT',
`lengths' still contains the maximum length of the argument (as
for the initialization function).
Return Values and Error Handling
--------------------------------
The initialization function should return `0' if no error occurred and
`1' otherwise. If an error occurs, `xxx_init()' should store a
null-terminated error message in the `message' parameter. The message
will be returned to the client. The message buffer is
`MYSQL_ERRMSG_SIZE' characters long, but you should try to keep the
message to less than 80 characters so that it fits the width of a
standard terminal screen.
The return value of the main function `xxx()' is the function value, for
`long long' and `double' functions. A string functions should return a
pointer to the result and store the length of the string in the
`length' arguments. `result' is a buffer at least 255 bytes long. Set
these to the contents and length of the return value. For example:
memcpy(result, "result string", 13);
*length = 13;
If your string functions that needs to return a string longer than 255
bytes, you must allocate the space for it with `malloc()' in your
`xxx_init()' function or your `xxx()' function and free it in your
`xxx_deinit()' function. You can store the allocated memory in the
`ptr' slot in the `UDF_INIT' structure for reuse by future `xxx()'
calls. *Note UDF calling sequences::.
To indicate a return value of `NULL' in the main function, set
`is_null' to `1':
*is_null = 1;
To indicate an error return in the main function, set the `error'
parameter to `1':
*error = 1;
If `xxx()' sets `*error' to `1' for any row, the function value is
`NULL' for the current row and for any subsequent rows processed by the
statement in which `XXX()' was invoked. (`xxx()' will not even be
called for subsequent rows.) *NOTE:* In *MySQL* versions prior to
3.22.10, you should set both `*error' and `*is_null':
*error = 1;
*is_null = 1;
Compiling and Installing User-definable Functions
-------------------------------------------------
Files implementing UDFs must be compiled and installed on the host
where the server runs. This process is described below for the example
UDF file `udf_example.cc' that is included in the *MySQL* source
distribution. This file contains the following functions:
* `metaphon()' returns a metaphon string of the string argument.
This is something like a soundex string, but it's more tuned for
English.
* `myfunc_double()' returns the sum of the ASCII values of the
characters in its arguments, divided by the sum of the length of
its arguments.
* `myfunc_int()' returns the sum of the length of its arguments.
* `sequence([const int])' returns an sequence starting from the given
number or 1 if no number has been given.
* `lookup()' returns the IP number for a hostname.
* `reverse_lookup()' returns the hostname for an IP number. The
function may be called with a string `"xxx.xxx.xxx.xxx"' or four
numbers.
A dynamically loadable file should be compiled as a sharable object
file, using a command something like this:
shell> gcc -shared -o udf_example.so myfunc.cc
You can easily find out the correct compiler options for your system by
running this command in the `sql' directory of your *MySQL* source tree:
shell> make udf_example.o
You should run a compile command similar to the one that `make'
displays, except that you should remove the `-c' option near the end of
the line and add `-o udf_example.so' to the end of the line. (On some
systems, you may need to leave the `-c' on the command.)
Once you compile a shared object containing UDFs, you must install it
and tell *MySQL* about it. Compiling a shared object from
`udf_example.cc' produces a file named something like `udf_example.so'
(the exact name may vary from platform to platform). Copy this file to
some directory searched by `ld', such as `/usr/lib'. On many systems,
you can set the `LD_LIBRARY' or `LD_LIBRARY_PATH' environment variable
to point at the directory where you have your UDF function files. The
`dlopen' manual page tells you which variable you should use on your
system. You should set this in `mysql.server' or `safe_mysqld' and
restart `mysqld'.
After the library is installed, notify `mysqld' about the new functions
with these commands:
mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so";
mysql> CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so";
mysql> CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so";
mysql> CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so";
mysql> CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "udf_example.so";
Functions can be deleted using `DROP FUNCTION':
mysql> DROP FUNCTION metaphon;
mysql> DROP FUNCTION myfunc_double;
mysql> DROP FUNCTION myfunc_int;
mysql> DROP FUNCTION lookup;
mysql> DROP FUNCTION reverse_lookup;
The `CREATE FUNCTION' and `DROP FUNCTION' statements update the system
table `func' in the `mysql' database. The function's name, type and
shared library name are saved in the table. You must have the *insert*
and *delete* privileges for the `mysql' database to create and drop
functions.
You should not use `CREATE FUNCTION' to add a function that has already
been created. If you need to reinstall a function, you should remove
it with `DROP FUNCTION' and then reinstall it with `CREATE FUNCTION'.
You would need to do this, for example, if you recompile a new version
of your function, so that `mysqld' gets the new version. Otherwise the
server will continue to use the old version.
Active functions are reloaded each time the server starts, unless you
start `mysqld' with the `--skip-grant-tables' option. In this case, UDF
initialization is skipped and UDFs are unavailable. (An active
function is one that has been loaded with `CREATE FUNCTION' and not
removed with `DROP FUNCTION'.)
Adding a New Native Function
============================
The procedure for adding a new native function is described below.
Note that you cannot add native functions to a binary distribution
because the procedure involves modifying *MySQL* source code. You must
compile *MySQL* yourself from a source distribution. Also note that if
you migrate to another version of *MySQL* (for example, when a new
version is released), you will need to repeat the procedure with the
new version.
To add a new native *MySQL* function, follow these steps:
1. Add one line to `lex.h' that defines the function name in the
`sql_functions[]' array.
2. Add two lines to `sql_yacc.yy'. One indicates the preprocessor
symbol that `yacc' should define (this should be added at the
beginning of the file). Then define the function parameters and
add an "item" with these parameters to the `simple_expr' parsing
rule. For an example, check all occurrences of `SOUNDEX' in
`sql_yacc.yy' to see how this is done.
3. In `item_func.h', declare a class inheriting from `Item_num_func'
or `Item_str_func', depending on whether your function returns a
number or a string.
4. In `item_func.cc', add one of the following declarations, depending
on whether you are defining a numeric or string function:
double Item_func_newname::val()
longlong Item_func_newname::val_int()
String *Item_func_newname::Str(String *str)
5. You should probably also define the following function:
void Item_func_newname::fix_length_and_dec()
This function should at least calculate `max_length' based on the
given arguments. `max_length' is the maximum number of characters
the function may return. This function should also set
`maybe_null = 0' if the main function can't return a `NULL' value.
The function can check if any of the function arguments can
return `NULL' by checking the arguments `maybe_null' variable.
All functions must be thread safe.
For string functions, there are some additional considerations to be
aware of:
* The `String *str' argument provides a string buffer that may be
used to hold the result.
* The function should return the string that holds the result.
* All current string functions try to avoid allocating any memory
unless absolutely necessary!
Adding New Procedures to MySQL
******************************
In *MySQL*, you can define a procedure in C++ that can access and
modify the data in a query before it is sent to the client. The
modification can be done on row-by-row or `GROUP BY' level.
We have created an example procedure in *MySQL* Version 3.23 to show
you what can be done.
Additionally we recommend you to take a look at 'mylua', which you can
find in the Contrib directory. *Note Contrib::. Which this you can use
the LUA language to load a procedure at runtime into `mysqld'.
Procedure Analyse
=================
`analyse([max elements,[max memory]])'
This procedure is defined in the `sql/sql_analyse.cc'. This examines
the result from your query and returns an analysis of the results:
* `max elements' (default 256) is the maximum number of distinct
values `analyse' will notice per column. This is used by
`analyse' to check if the optimal column type should be of type
`ENUM'.
* `max memory' (default 8192) is the maximum memory `analyse' should
allocate per column while trying to find all distinct values.
SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max elements,[max memory]])
Writing a Procedure
===================
For the moment, the only documentation for this is the source.
You can find all information about procedures by examining the
following files:
* `sql/sql_analyse.cc'
* `sql/procedure.h'
* `sql/procedure.cc'
* `sql/sql_select.cc'
MySQL ODBC Support
******************
*MySQL* provides support for ODBC by means of the *MyODBC* program.
How To Install MyODBC
=====================
*MyODBC* is a 32-bit ODBC (2.50) level 0 (with level 1 and level 2
features) driver for connecting an ODBC-aware application to *MySQL*.
*MyODBC* works on Windows95, Windows98, NT, and on most Unix platforms.
*MyODBC* is in public domain, and you can find the newest version at
`http://www.mysql.com/downloads/api-myodbc.html'.
If you have problem with *MyODBC* and your program also works with
OLEDB, you should try the OLEDB driver that you can find in the Contrib
section. *Note Contrib::.
Normally you only need to install *MyODBC* on Windows machines. You
only need *MyODBC* for Unix if you have a program like ColdFusion that
is running on the Unix machine and uses ODBC to connect to the
databases.
If you want to install *MyODBC* on a Unix box, you will also need an
*ODBC* manager. *MyODBC* is known to work with most of the Unix ODBC
managers. You can find a list at these in the *ODBC*-related links
section on the *MySQL* useful links page. *Note Useful Links::.
To install *MyODBC* on windows, you should download the appropriate
*MyODBC* .zip file (for Windows or NT/Win2000), unpack it with
`WINZIP', or some similar program, and execute the `SETUP.EXE' file.
On Windows/NT you may get the following error when trying to install
*MyODBC*:
An error occurred while copying C:WINDOWSSYSTEMMFC30.DLL. Restart
Windows and try installing again (before running any applications which
use ODBC)
The problem in this case is that some other program is using ODBC and
because of how Windows is designed, you may not in this case be able to
install a new ODBC drivers with Microsoft's ODBC setup program. In most
cases you can continue by just pressing `Ignore' to copy the rest of
the MyODBC files and the final installation should still work. If this
doesn't work, the the solution is to reboot your computer in "safe
mode" (Choose this by pressing F8 just before your machine starts
Windows during rebooting), install *MyODBC*, and reboot to normal mode.
* To make a connection to a Unix box from a Windows box, with an ODBC
application (one that doesn't support *MySQL* natively), you must
first install *MyODBC* on the Windows machine.
* The user and Windows machine must have the access privileges to the
*MySQL* server on the Unix machine. This is set up with the
`GRANT' command. *Note `GRANT': GRANT.
* You must create an ODBC DSN entry as follows:
- Open the Control Panel on the Windows machine.
- Double-click the ODBC Data Sources 32 bits icon.
- Click the tab User DSN.
- Click the button Add.
- Select *MySQL* in the screen Create New Data Source and click
the Finish button.
- The *MySQL* Driver default configuration screen is shown.
*Note ODBC administrator::.
* Now start your application and select the ODBC driver with the DSN
you specified in the ODBC administrator.
Notice that there are other configuration options on the screen of
*MySQL* (trace, don't prompt on connect, etc) that you can try if you
run into problems.
How to Fill in the Various Fields in the ODBC Administrator Program
===================================================================
There are three possibilities for specifying the server name on
Windows95:
* Use the IP address of the server.
* Add a file `windowslmhosts' with the following information:
ip hostname
For example:
194.216.84.21 my_hostname
* Configure the PC to use DNS.
Example of how to fill in the `ODBC setup':
Windows DSN name: test
Description: This is my test database
MySql Database: test
Server: 194.216.84.21
User: monty
Password: my_password
Port:
The value for the `Windows DSN name' field is any name that is unique
in your Windows ODBC setup.
You don't have to specify values for the `Server', `User', `Password',
or `Port' fields in the ODBC setup screen. However, if you do, the
values will be used as the defaults later when you attempt to make a
connection. You have the option of changing the values at that time.
If the port number is not given, the default port (3306) is used.
If you specify the option `Read options from C:my.cnf', the groups
`client' and `odbc' will be read from the `C:my.cnf' file. You can
use all options that are usable by `mysql_options()'. *Note
`mysql_options': mysql_options.
How to Report Problems with MyODBC
==================================
*MyODBC* has been tested with Access, Admndemo.exe, C++-Builder,
Borland Builder 4, Centura Team Developer (formerly Gupta SQL/Windows),
ColdFusion (on Solaris and NT with svc pack 5), Crystal Reports,
DataJunction, Delphi, ERwin, Excel, iHTML, FileMaker Pro, FoxPro, Notes
4.5/4.6, SBSS, Perl DBD-ODBC, Paradox, Powerbuilder, Powerdesigner 32
bit, VC++, and Visual Basic.
If you know of any other applications that work with *MyODBC*, please
send mail to <myodbc@lists.mysql.com> about this!
With some programs you may get an error like: `Another user has
modifies the record that you have modified'. In most cases this can be
solved by doing one of the following things:
* Add a primary key for the table if there isn't one already.
* Add a timestamp column if there isn't one already.
* Only use double float fields. Some programs may fail when they
compare single floats.
If the above doesn't help, you should do a `MyODBC' trace file and try
to figure out why things go wrong.
Programs Known to Work with MyODBC
==================================
Most programs should work with *MyODBC*, but for each of those listed
below, we have tested it ourselves or received confirmation from some
user that it works:
*Program*
*Comment*
Access
To make Access work:
* If you are using Access 2000, you should get and install the
newest Microsoft MDAC (`Microsoft Data Access Components')
from `http://www.microsoft.com/data'. This will fix the
following bug in Access: when you export data to *MySQL*, the
table and column names aren't specified. Another way to
around this bug is to upgrade to MyODBC Version 2.50.33 and
*MySQL* Version 3.23.x, which together provide a workaround
for this bug!
Note that if you are using *MySQL* Version 3.22, you must to
apply the MDAC patch and use MyODBC 2.50.32 or 2.50.34 and
above to go around this problem.
* Set the "Return matching rows" MyODBC option field when
connecting to *MySQL*.
* You should have a primary key in the table. If not, new or
updated rows may show up as `#Deleted#'.
* You should have a timestamp in all tables you want to be able
to update. For maximum portability `TIMESTAMP(14)' or simple
`TIMESTAMP' is recommended instead of other `TIMESTAMP(X)'
variations.
* Only use double float fields. Access fails when comparing
with single floats. The symptom usually is that new or
updated rows may show up as `#Deleted#' or that you can't
find or update rows.
* If you still get the error `Another user has changed your
data' after adding a `TIMESTAMP' column, the following trick
may help you:
Don't use `table' data sheet view. Create instead a form with
the fields you want, and use that `form' data sheet view.
You should set the `DefaultValue' property for the
`TIMESTAMP' column to `NOW()'. It may be a good idea to hide
the `TIMESTAMP' column from view so your users are not
confused.
* Access on NT will report `BLOB' columns as `OLE OBJECTS'. If
you want to have `MEMO' columns instead, you should change the
column to `TEXT' with `ALTER TABLE'.
* Access can't always handle `DATE' columns properly. If you
have a problem with these, change the columns to `DATETIME'.
* In some cases, Access may generate illegal SQL queries that
*MySQL* can't understand. You can fix this by selecting
`"Query|SQLSpecific|Pass-Through"' from the Access menu.
* If you have in Access a column defined as BYTE, Access will
try to export this as `TINYINT' instead of `TINYINT
UNSIGNED'. This will give you problems if you have values >
127 in the column!
ADO
When you are coding with the ADO API and *MyODBC* you need to put
attention in some default properties that aren't supported by the
*MySQL* server. For example, using the `CursorLocation Property'
as `adUseServer' will return for the `RecordCount Property' a
result of -1. To have the right value, you need to set this
property to `adUseClient', like is showing in the VB code below:
Dim myconn As New ADODB.Connection
Dim myrs As New Recordset
Dim mySQL As String
Dim myrows As Long
myconn.Open "DSN=MyODBCsample"
mySQL = "SELECT * from user"
myrs.Source = mySQL
Set myrs.ActiveConnection = myconn
myrs.CursorLocation = adUseClient
myrs.Open
myrows = myrs.RecordCount
myrs.Close
myconn.Close
Another workaround is to use a `SELECT COUNT(*)' statement for a
similar query to get the correct row count.
Borland Builder 4
When you start a query you can use the property `Active' or use the
method `Open'. Note that `Active' will start by automatically
issuing a `SELECT * FROM ...' query that may not be a good thing if
your tables are big!
ColdFusion (On Unix)
The following information is taken from the ColdFusion
documentation:
Use the following information to configure ColdFusion Server for
Linux to use the unixODBC driver with *MyODBC* for *MySQL* data
sources. Allaire has verified that *MyODBC* Version 2.50.26 works
with *MySQL* Version 3.22.27 and ColdFusion for Linux. (Any newer
version should also work.) You can download *MyODBC* at
`http://www.mysql.com/downloads/api-myodbc.html'
ColdFusion Version 4.5.1 allows you to us the ColdFusion
Administrator to add the *MySQL* data source. However, the driver
is not included with ColdFusion Version 4.5.1. Before the *MySQL*
driver will appear in the ODBC datasources drop-down list, you
must build and copy the *MyODBC* driver to
`/opt/coldfusion/lib/libmyodbc.so'.
The Contrib directory contains the program mydsn-xxx.zip which
allows you to build and remove the DSN registry file for the
MyODBC driver on Coldfusion applications.
DataJunction
You have to change it to output `VARCHAR' rather than `ENUM', as
it exports the latter in a manner that causes *MySQL* grief.
Excel
Works. Some tips:
* If you have problems with dates, try to select them as
strings using the `CONCAT()' function. For example:
select CONCAT(rise_time), CONCAT(set_time)
from sunrise_sunset;
Values retrieved as strings this way should be correctly
recognized as time values by Excel97.
The purpose of `CONCAT()' in this example is to fool ODBC
into thinking the column is of "string type". Without the
`CONCAT()', ODBC knows the column is of time type, and Excel
does not understand that.
Note that this is a bug in Excel, because it automatically
converts a string to a time. This would be great if the
source was a text file, but is plain stupid when the source
is an ODBC connection that reports exact types for each
column.
Word
To retrieve data from *MySQL* to Word/Excel documents, you need to
use the `MyODBC' driver and the Add-in Microsoft Query help.
For example, create a db with a table containing 2 columns of text:
* Insert rows using the mysql client command-line tool.
* Create a DSN file using the MyODBC driver, for example, my
for the db above.
* Open the Word application.
* Create a blank new documentation.
* Using the tool bar called Database, press the button insert
database.
* Press the button Get Data.
* At the right hand of the screen Get Data, press the button Ms
Query.
* In the Ms Query create a New Data Source using the DSN file
my.
* Select the new query.
* Select the columns that you want.
* Make a filter if you want.
* Make a Sort if you want.
* Select Return Data to Microsoft Word.
* Click Finish.
* Click Insert data and select the records.
* Click OK and you see the rows in your Word document.
odbcadmin
Test program for ODBC.
Delphi
You must use DBE Version 3.2 or newer. Set the `Don't optimize
column width' option field when connecting to *MySQL*.
Also, here is some potentially useful Delphi code that sets up
both an ODBC entry and a BDE entry for *MyODBC* (the BDE entry
requires a BDE Alias Editor that is free at a Delphi Super Page
near you. (Thanks to Bryan Brunton <bryan@flesherfab.com> for
this):
fReg:= TRegistry.Create;
fReg.OpenKey('SoftwareODBCODBC.INIDocumentsFab', True);
fReg.WriteString('Database', 'Documents');
fReg.WriteString('Description', ' ');
fReg.WriteString('Driver', 'C:WINNTSystem32myodbc.dll');
fReg.WriteString('Flag', '1');
fReg.WriteString('Password', '');
fReg.WriteString('Port', ' ');
fReg.WriteString('Server', 'xmark');
fReg.WriteString('User', 'winuser');
fReg.OpenKey('SoftwareODBCODBC.INIODBC Data Sources', True);
fReg.WriteString('DocumentsFab', 'MySQL');
fReg.CloseKey;
fReg.Free;
Memo1.Lines.Add('DATABASE NAME=');
Memo1.Lines.Add('USER NAME=');
Memo1.Lines.Add('ODBC DSN=DocumentsFab');
Memo1.Lines.Add('OPEN MODE=READ/WRITE');
Memo1.Lines.Add('BATCH COUNT=200');
Memo1.Lines.Add('LANGDRIVER=');
Memo1.Lines.Add('MAX ROWS=-1');
Memo1.Lines.Add('SCHEMA CACHE DIR=');
Memo1.Lines.Add('SCHEMA CACHE SIZE=8');
Memo1.Lines.Add('SCHEMA CACHE TIME=-1');
Memo1.Lines.Add('SQLPASSTHRU MODE=SHARED AUTOCOMMIT');
Memo1.Lines.Add('SQLQRYMODE=');
Memo1.Lines.Add('ENABLE SCHEMA CACHE=FALSE');
Memo1.Lines.Add('ENABLE BCD=FALSE');
Memo1.Lines.Add('ROWSET SIZE=20');
Memo1.Lines.Add('BLOBS TO CACHE=64');
Memo1.Lines.Add('BLOB SIZE=32');
AliasEditor.Add('DocumentsFab','MySQL',Memo1.Lines);
C++Builder
Tested with BDE Version 3.0. The only known problem is that when
the table schema changes, query fields are not updated. BDE,
however, does not seem to recognize primary keys, only the index
PRIMARY, though this has not been a problem.
Visual Basic
To be able to update a table, you must define a primary key for
the table.
Visual Basic with ADO can't handle big integers. This means that
some queries like `SHOW PROCESSLIST' will not work properly. The
fix is to set add the option `OPTION=16834' in the ODBC connect
string or set the `Change BIGINT columns to INT' option in the
MyODBC connect screen.
VisualInterDev
If you get the error `[Microsoft][ODBC Driver Manager] Driver does
not support this parameter' the reason may be that you have a
`BIGINT' in your result. Try setting the `Change BIGINT columns
to INT' option in the MyODBC connect screen.
How to Get the Value of an `AUTO_INCREMENT' Column in ODBC
==========================================================
A common problem is how to get the value of an automatically generated
ID from an `INSERT'. With ODBC, you can do something like this (assuming
that `auto' is an `AUTO_INCREMENT' field):
INSERT INTO foo (auto,text) VALUES(NULL,'text');
SELECT LAST_INSERT_ID();
Or, if you are just going to insert the ID into another table, you can
do this:
INSERT INTO foo (auto,text) VALUES(NULL,'text');
INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text');
For the benefit of some ODBC applications (at least Delphi and Access),
the following query can be used to find a newly inserted row:
SELECT * FROM tbl_name WHERE auto IS NULL;
Reporting Problems with MyODBC
==============================
If you encounter difficulties with *MyODBC*, you should start by making
a log file from the ODBC manager (the log you get when requesting logs
from ODBCADMIN) and a *MyODBC* log.
To get a *MyODBC* log, you need to do the following:
1. Ensure that you are using `myodbcd.dll' and not `myodbc.dll'. The
easiest way to do this is to get `myodbcd.dll' from the MyODBC
distribution and copy it over the `myodbc.dll', which is probably
in your `C:windowssystem32' or `C:winntsystem32' directory.
Note that you probably want to restore the old myodbc.dll file
when you have finished testing, as this is a lot faster than
`myodbcd.dll'.
2. Tag the `Trace MyODBC' option flag in the *MyODBC*
connect/configure screen. The log will be written to file
`C:myodbc.log'.
If the trace option is not remembered when you are going back to
the above screen, it means that you are not using the `myodbcd.dll'
driver (see above).
3. Start your application and try to get it to fail.
Check the `MyODBC trace file', to find out what could be wrong. You
should be able to find out the the issued queries by searching after
the string `>mysql_real_query' in the `myodbc.log' file.
You should also try duplicating the queries in the `mysql' monitor or
`admndemo' to find out if the error is MyODBC or *MySQL*.
If you find out something is wrong, please only send the relevant rows
(max 40 rows) to <myodbc@lists.mysql.com>. Please never send the whole
MyODBC or ODBC log file!
If you are unable to find out what's wrong, the last option is to make
an archive (tar or zip) that contains a MyODBC trace file, the ODBC log
file, and a README file that explains the problem. You can send this
to `ftp://support.mysql.com/pub/mysql/secret'. Only we at MySQL AB
will have access to the files you upload, and we will be very discrete
with the data!
If you can create a program that also shows this problem, please upload
this too!
If the program works with some other SQL server, you should make an
ODBC log file where you do exactly the same thing in the other SQL
server.
Remember that the more information you can supply to us, the more
likely it is that we can fix the problem!
Using MySQL with Some Common Programs
*************************************
Using MySQL with Apache
=======================
The contrib section includes programs that let you authenticate your
users from a *MySQL* database and also let you log your log files into
a *MySQL* table. *Note Contrib::.
You can change the Apache logging format to be easily readable by
*MySQL* by putting the following into the Apache configuration file:
LogFormat
""%h",%{%Y%m%d%H%M%S}t,%>s,"%b","%{Content-Type}o",
"%U","%{Referer}i","%{User-Agent}i""
In *MySQL* you can do something like this:
LOAD DATA INFILE '/local/access_log' INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\'
Borland C++
===========
You can compile the *MySQL* windows source with Borland C++ 5.02. (The
windows source includes only projects for Microsoft VC++, for Borland
C++ you have to do the project files yourself).
One known problem with Borland C++ is that it uses a different structure
alignment than VC++. This means that you will run into problems if you
try to use the default `libmysql.dll' libraries (that was compiled with
VC++) with Borland C++. You can do one of the following to avoid this
problem.
* You can use the static *MySQL* libraries for Borland C++ that you
can find on `http://www.mysql.net/downloads/os-win32.html'.
* Only call `mysql_init()' with `NULL' as an argument, not a
pre-allocated MYSQL struct.
Problems and Common Errors
**************************
How to Determine What Is Causing Problems
=========================================
When you run into problems, the first thing you should do is to find out
which program / piece of equipment is causing problems:
* If you have one of the following symptoms, then it is probably a
hardware (like memory, motherboard, CPU, or hard disk) or kernel
problem:
- The keyboard doesn't work. This can normally be checked by
pressing Caps Lock. If the Caps Lock light doesn't change you
have to replace your keyboard. (Before doing this, you
should try to reboot your computer and check all cables to
the keyboard.)
- The mouse pointer doesn't move.
- The machine doesn't answer to a remote machine's pings.
- Different, unrelated programs don't behave correctly.
- If your system rebooted unexpectedly (a faulty user level
program should NEVER be able to take down your system).
In this case you should start by checking all your cables and run
some diagnostic tool to check your hardware! You should also
check if there are any patches, updates, or service packs for your
operating system that could likely solve your problems. Check
also that all your libraries (like glibc) are up to date.
It's always good to use a machine with ECC memory to discover
memory problems early!
* If your keyboard is locked up, you may be able to fix this by
logging into your machine from another machine and execute
`kbd_mode -a' on it.
* Please examine your system log file (/var/log/messages or similar)
for reasons for your problems. If you think the problem is in
*MySQL* then you should also examine *MySQL*'s log files. *Note
Update log::.
* If you don't think you have hardware problems, you should try to
find out which program is causing problems.
Try using `top', `ps', `taskmanager', or some similar program, to
check which program is taking all CPU or is locking the machine.
* Check with `top', `df', or a similar program if you are out of
memory, disk space, open files, or some other critical resource.
* If the problem is some runaway process, you can always try to kill
it. If it doesn't want to die, there is probably a bug in the
operating system.
If after you have examined all other possibilities and you have
concluded that it's the *MySQL* server or a *MySQL* client that is
causing the problem, it's time to do a bug report for our mailing list
or our support team. In the bug report, try to give a very detailed
description of how the system is behaving and what you think is
happening. You should also state why you think it's *MySQL* that is
causing the problems. Take into consideration all the situations in
this chapter. State any problems exactly how they appear when you
examine your system. Use the 'cut and paste' method for any output
and/or error messages from programs and/or log files!
Try to describe in detail which program is not working and all symptoms
you see! We have in the past received many bug reports that just state
"the system doesn't work". This doesn't provide us with any
information about what could be the problem.
If a program fails, it's always useful to know:
* Has the program in question made a segmentation fault (core
dumped)?
* Is the program taking the whole CPU? Check with `top'. Let the
program run for a while, it may be evaluating something heavy.
* If it's the `mysqld' server that is causing problems, can you do
`mysqladmin -u root ping' or `mysqladmin -u root processlist'?
* What does a client program say (try with `mysql', for example)
when you try to connect to the *MySQL* server? Does the client
jam? Do you get any output from the program?
When sending a bug report, you should of follow the outlines described
in this manual. *Note Asking questions::.
What to Do if MySQL Keeps Crashing
==================================
All *MySQL* versions are tested on many platforms before they are
released. This doesn't mean that there aren't any bugs in *MySQL*, but
it means if there are bugs, they are very few and can be hard to find.
If you have a problem, it will always help if you try to find out
exactly what crashes your system, as you will have a much better chance
of getting this fixed quickly.
First, you should try to find out whether the problem is that the
`mysqld' daemon dies or whether your problem has to do with your
client. You can check how long your `mysqld' server has been up by
executing `mysqladmin version'. If `mysqld' has died, you may find the
reason for this in the file `mysql-data-directory/`hostname`.err'.
*Note Error log::.
Many crashes of *MySQL* are caused by corrupted index / data files.
*MySQL* will update the data on disk, with the `write()' system call,
after every SQL statement and before the client is notified about the
result. (This is not true if you are running with `delayed_key_writes',
in which case only the data is written.) This means that the data is
safe even if mysqld crashes, as the OS will ensure that the not flushed
data is written to disk. You can force *MySQL* to sync everything to
disk after every SQL command by starting `mysqld' with `--flush'.
The above means that normally you shouldn't get corrupted tables unless:
* Someone/something killed `mysqld' or the machine in the middle of
an update.
* You have found a bug in `mysqld' that caused it to die in the
middle of an update.
* Someone is manipulating the data/index files outside of *mysqld*
without locking the table properly.
* If you are running many `mysqld' servers on the same data on a
system that doesn't support good file system locks (normally
handled by the `lockd' deamon ) or if you are running multiple
servers with `--skip-locking'
* You have a crashed index/data file that contains very wrong data
that got mysqld confused.
* You have found a bug in the data storage code. This isn't that
likely, but it's at least possible. In this case you can try to
change the file type to another database handler by using `ALTER
TABLE' on a repaired copy of the table!
Because it is very difficult to know why something is crashing, first
try to check whether or not things that work for others crash for you.
Please try the following things:
* Take down the `mysqld' daemon with `mysqladmin shutdown', run
`myisamchk --silent --force */*.MYI' on all tables, and restart the
`mysqld' daemon. This will ensure that you are running from a
clean state. *Note Maintenance::.
* Use `mysqld --log' and try to determine from the information in
the log whether or not some specific query kills the server. About
95% of all bugs are related to a particular query! Normally this
is one of the last queries in the log file just before *MySQL*
restarted. *Note Query log::. If you can repeatadly kill *MySQL*
with one of the queries, even when you have checked all tables
just before doing the query, then you have been able to locate the
bug and should do a bug report for this! *Note Bug reports::.
* Try to make a test case that we can use to reproduce the problem.
*Note Reproducable test case::.
* Try running the included mysql-test test and the *MySQL*
benchmarks. *Note MySQL test suite::. They should test *MySQL*
rather well. You can also add code that to the benchmarks to
simulates your application! The benchmarks can be found in the
`bench' directory in the source distribution or, for a binary
distribution, in the `sql-bench' directory under your *MySQL*
installation directory.
* Try `fork_test.pl' and `fork2_test.pl'.
* If you configure *MySQL* for debugging, it will be much easier to
gather information about possible errors if something goes wrong.
Reconfigure *MySQL* with the `--with-debug' option to `configure'
and then recompile. *Note Debugging server::.
* Configuring *MySQL* for debugging causes a safe memory allocator
to be included that can find some errors. It also provides a lot
of output about what is happening.
* Have you applied the latest patches for your operating system?
* Use the `--skip-locking' option to `mysqld'. On some systems, the
`lockd' lock manager does not work properly; the `--skip-locking'
option tells `mysqld' not to use external locking. (This means
that you cannot run 2 `mysqld' servers on the same data and that
you must be careful if you use `myisamchk', but it may be
instructive to try the option as a test.)
* Have you tried `mysqladmin -u root processlist' when `mysqld'
appears to be running but not responding? Sometimes `mysqld' is
not comatose even though you might think so. The problem may be
that all connections are in use, or there may be some internal
lock problem. `mysqladmin processlist' will usually be able to
make a connection even in these cases, and can provide useful
information about the current number of connections and their
status.
* Run the command `mysqladmin -i 5 status' or `mysqladmin -i 5 -r
status' or in a separate window to produce statistics while you run
your other queries.
* Try the following:
1. Start `mysqld' from `gdb' (or in another debugger).
2. Run your test scripts.
3. Print the backtrace and the local variables at the 3 lowest
levels. In gdb you can do this with the following commands
when `mysqld' has crashed inside gdb:
backtrace
info local
up
info local
up
info local
With gdb you can also examine which threads exist with `info
threads' and switch to a specific thread with `thread #',
where `#' is the thread id.
* Try to simulate your application with a Perl script to force
*MySQL* to crash or misbehave.
* Send a normal bug report. *Note Bug reports::. Be even more
detailed than usual. Because *MySQL* works for many people, it
may be that the crash results from something that exists only on
your computer (for example, an error that is related to your
particular system libraries).
* If you have a problem with tables with dynamic-length rows and you
are not using `BLOB/TEXT' columns (but only `VARCHAR' columns), you
can try to change all `VARCHAR' to `CHAR' with `ALTER TABLE'.
This will force *MySQL* to use fixed-size rows. Fixed-size rows
take a little extra space, but are much more tolerant to
corruption!
The current dynamic row code has been in use at MySQL AB for at
least 3 years without any problems, but by nature dynamic-length
rows are more prone to errors, so it may be a good idea to try the
above to see if it helps!
Problems When Linking with the MySQL Client Library
===================================================
If you are linking your program and you get errors for unreferenced
symbols that start with `mysql_', like the following:
/tmp/ccFKsdPa.o: In function `main':
/tmp/ccFKsdPa.o(.text+0xb): undefined reference to `mysql_init'
/tmp/ccFKsdPa.o(.text+0x31): undefined reference to `mysql_real_connect'
/tmp/ccFKsdPa.o(.text+0x57): undefined reference to `mysql_real_connect'
/tmp/ccFKsdPa.o(.text+0x69): undefined reference to `mysql_error'
/tmp/ccFKsdPa.o(.text+0x9a): undefined reference to `mysql_close'
you should be able to solve this by adding `-Lpath-to-the-mysql-library
-lmysqlclient' *LAST* on your link line.
If you get `undefined reference' errors for the `uncompress' or
`compress' function, add `-lgz' *LAST* on your link line and try again!
If you get `undefined reference' errors for functions that should exist
on your system, like `connect', check the man page for the function in
question, for which libraries you should add to the link line!
If you get `undefined reference' errors for functions that don't exist
on your system, like the following:
mf_format.o(.text+0x201): undefined reference to `__lxstat'
it usually means that your library is compiled on a system that is not
100 % compatible with yours. In this case you should download the
latest *MySQL* source distribution and compile this yourself. *Note
Installing source::.
If you are trying to run a program and you then get errors for
unreferenced symbols that start with `mysql_' or that the `mysqlclient'
library can't be found, this means that your system can't find the
share `libmysqlclient.so' library.
The fix for this is to tell your system to search after shared
libraries where the library is located by one of the following methods:
* Add the path to the directory where you have `libmysqlclient.so'
the `LD_LIBRARY_PATH' environment variable.
* Add the path to the directory where you have `libmysqlclient.so'
the `LD_LIBRARY' environment variable.
* Copy `libmysqlclient.so' to some place that is searched by your
system, like `/lib', and update the shared library information by
executing `ldconfig'.
Another way to solve this problem is to link your program statically,
with `-static', or by removing the dynamic *MySQL* libraries before
linking your code. In the second case you should be sure that no other
programs are using the dynamic libraries!
Some Common Errors When Using MySQL
===================================
`Access denied' Error
---------------------
*Note Privileges::, and especially see *Note Access denied::.
`MySQL server has gone away' Error
----------------------------------
This section also covers the related `Lost connection to server during
query' error.
The most common reason for the `MySQL server has gone away' error is
that the server timed out and closed the connection. By default, the
server closes the connection after 8 hours if nothing has happened. You
can change the time limit by setting the `wait_timeout' variable when
you start mysqld.
You can check that the *MySQL* hasn't died by executing `mysqladmin
version' and examining the uptime.
If you have a script, you just have to issue the query again for the
client to do an automatic reconnection.
You normally can get the following error codes in this case (which one
you get is OS-dependent):
`CR_SERVER_GONE_ERROR' The client couldn't send a question to the server.
`CR_SERVER_LOST' The client didn't get an error when writing to
the server, but it didn't get a full answer (or
any answer) to the question.
You can also get these errors if you send a query to the server that is
incorrect or too large. If `mysqld' gets a packet that is too large or
out of order, it assumes that something has gone wrong with the client
and closes the connection. If you need big queries (for example, if
you are working with big `BLOB' columns), you can increase the query
limit by starting `mysqld' with the `-O max_allowed_packet=#' option
(default 1M). The extra memory is allocated on demand, so `mysqld' will
use more memory only when you issue a big query or when `mysqld' must
return a big result row!
`Can't connect to [local] MySQL server' error
---------------------------------------------
A *MySQL* client on Unix can connect to the `mysqld' server in two
different ways: Unix sockets, which connect through a file in the file
system (default `/tmp/mysqld.sock') or TCP/IP, which connects through a
port number. Unix sockets are faster than TCP/IP but can only be used
when connecting to a server on the same computer. Unix sockets are
used if you don't specify a hostname or if you specify the special
hostname `localhost'.
On Windows you can connect only with TCP/IP if the `mysqld' server is
running on Win95/Win98. If it's running on NT, you can also connect
with named pipes. The name of the named pipe is *MySQL*. If you don't
give a hostname when connecting to `mysqld', a *MySQL* client will
first try to connect to the named pipe, and if this doesn't work it
will connect to the TCP/IP port. You can force the use of named pipes
on Windows by using `.' as the hostname.
The error (2002) `Can't connect to ...' normally means that there isn't
a *MySQL* server running on the system or that you are using a wrong
socket file or TCP/IP port when trying to connect to the `mysqld'
server.
Start by checking (using `ps' or the task manager on Windows) that
there is a process running named `mysqld' on your server! If there
isn't any `mysqld' process, you should start one. *Note Starting
server::.
If a `mysqld' process is running, you can check the server by trying
these different connections (the port number and socket pathname might
be different in your setup, of course):
shell> mysqladmin version
shell> mysqladmin variables
shell> mysqladmin -h `hostname` version variables
shell> mysqladmin -h `hostname` --port=3306 version
shell> mysqladmin -h 'ip for your host' version
shell> mysqladmin --socket=/tmp/mysql.sock version
Note the use of backquotes rather than forward quotes with the
`hostname' command; these cause the output of `hostname' (that is, the
current hostname) to be substituted into the `mysqladmin' command.
Here are some reasons the `Can't connect to local MySQL server' error
might occur:
* `mysqld' is not running.
* You are running on a system that uses MIT-pthreads. If you are
running on a system that doesn't have native threads, `mysqld'
uses the MIT-pthreads package. *Note Which OS::. However, all
MIT-pthreads versions doesn't support Unix sockets. On a system
without sockets support you must always specify the hostname
explicitly when connecting to the server. Try using this command
to check the connection to the server:
shell> mysqladmin -h `hostname` version
* Someone has removed the Unix socket that `mysqld' uses (default
`/tmp/mysqld.sock'). You might have a `cron' job that removes the
*MySQL* socket (for example, a job that removes old files from the
`/tmp' directory). You can always run `mysqladmin version' and
check that the socket `mysqladmin' is trying to use really exists.
The fix in this case is to change the `cron' job to not remove
`mysqld.sock' or to place the socket somewhere else. You can
specify a different socket location at *MySQL* configuration time
with this command:
shell> ./configure --with-unix-socket-path=/path/to/socket
You can also start `safe_mysqld' with the
`--socket=/path/to/socket' option and set the environment variable
`MYSQL_UNIX_PORT' to the socket pathname before starting your
*MySQL* clients.
* You have started the `mysqld' server with the
`--socket=/path/to/socket' option. If you change the socket
pathname for the server, you must also notify the *MySQL* clients
about the new path. You can do this by setting the environment
variable `MYSQL_UNIX_PORT' to the socket pathname or by providing
the socket path as an argument to the clients. You can test the
socket with this command:
shell> mysqladmin --socket=/path/to/socket version
* You are using Linux and one thread has died (core dumped). In
this case you must kill the other `mysqld' threads (for example,
with the `mysql_zap' script before you can start a new *MySQL*
server. *Note Crashing::.
* You may not have read and write privilege to either the directory
that holds the socket file or privilege to the socket file itself.
In this case you have to either change the privilege for the
directory / file or restart `mysqld' so that it uses a directory
that you can access.
If you get the error message `Can't connect to MySQL server on
some_hostname', you can try the following things to find out what the
problem is :
* Check if the server is up by doing `telnet your-host-name
tcp-ip-port-number' and press `RETURN' a couple of times. If there
is a *MySQL* server running on this port you should get a
responses that includes the version number of the running *MySQL*
server. If you get an error like `telnet: Unable to connect to
remote host: Connection refused', then there is no server running
on the given port.
* Try connecting to the `mysqld' daemon on the local machine and
check the TCP/IP port that mysqld it's configured to use (variable
`port') with `mysqladmin variables'.
* Check that your `mysqld' server is not started with the
`--skip-networking' option.
`Host '...' is blocked' Error
-----------------------------
If you get an error like this:
Host 'hostname' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'
this means that `mysqld' has gotten a lot (`max_connect_errors') of
connect requests from the host `'hostname'' that have been interrupted
in the middle. After `max_connect_errors' failed requests, `mysqld'
assumes that something is wrong (like an attack from a cracker), and
blocks the site from further connections until someone executes the
command `mysqladmin flush-hosts'.
By default, `mysqld' blocks a host after 10 connection errors. You can
easily adjust this by starting the server like this:
shell> safe_mysqld -O max_connect_errors=10000 &
Note that if you get this error message for a given host, you should
first check that there isn't anything wrong with TCP/IP connections
from that host. If your TCP/IP connections aren't working, it won't do
you any good to increase the value of the `max_connect_errors' variable!
`Too many connections' Error
----------------------------
If you get the error `Too many connections' when you try to connect to
*MySQL*, this means that there is already `max_connections' clients
connected to the `mysqld' server.
If you need more connections than the default (100), then you should
restart `mysqld' with a bigger value for the `max_connections' variable.
Note that `mysqld' actually allows (`max_connections'+1) clients to
connect. The last connection is reserved for a user with the *process*
privilege. By not giving this privilege to normal users (they
shouldn't need this), an administrator with this privilege can log in
and use `SHOW PROCESSLIST' to find out what could be wrong. *Note
SHOW::.
The maximum number of connects *MySQL* is depending on how good the
thread library is on a given platform. Linux or Solaris should be able
to support 500-1000 simultaneous connections, depending on how much RAM
you have and what your clients are doing.
`Some non-transactional changed tables couldn't be rolled back' Error
---------------------------------------------------------------------
If you get the error/warning: `Warning: Some non-transactional changed
tables couldn't be rolled back' when trying to do a `ROLLBACK', this
means that some of the tables you used in the transaction didn't
support transactions. These non-transactional tables will not be
affected by the `ROLLBACK' statement.
The most typical case when this happens is when you have tried to create
a table of a type that is not supported by your `mysqld' binary. If
`mysqld' doesn't support a table type (or if the table type is disabled
by a startup option) , it will instead create the table type with the
table type that is most resembles to the one you requested, probably
`MyISAM'.
You can check the table type for a table by doing:
`SHOW TABLE STATUS LIKE 'table_name''. *Note SHOW TABLE STATUS::.
You can check the extensions your `mysqld' binary supports by doing:
`show variables like 'have_%''. *Note SHOW VARIABLES::.
`Out of memory' Error
---------------------
If you issue a query and get something like the following error:
mysql: Out of memory at line 42, 'malloc.c'
mysql: needed 8136 byte (8k), memory in use: 12481367 bytes (12189k)
ERROR 2008: MySQL client ran out of memory
note that the error refers to the *MySQL* client `mysql'. The reason
for this error is simply that the client does not have enough memory to
store the whole result.
To remedy the problem, first check that your query is correct. Is it
reasonable that it should return so many rows? If so, you can use
`mysql --quick', which uses `mysql_use_result()' to retrieve the result
set. This places less of a load on the client (but more on the server).
`Packet too large' Error
------------------------
When a *MySQL* client or the `mysqld' server gets a packet bigger than
`max_allowed_packet' bytes, it issues a `Packet too large' error and
closes the connection.
If you are using the `mysql' client, you may specify a bigger buffer by
starting the client with `mysql --set-variable=max_allowed_packet=8M'.
If you are using other clients that do not allow you to specify the
maximum packet size (such as `DBI'), you need to set the packet size
when you start the server. You cau use a command-line option to
`mysqld' to set `max_allowed_packet' to a larger size. For example, if
you are expecting to store the full length of a `BLOB' into a table,
you'll need to start the server with the
`--set-variable=max_allowed_packet=16M' option.
Communication Errors / Aborted Connection
-----------------------------------------
If you find errors like the following in your error log.
010301 14:38:23 Aborted connection 854 to db: 'users' user: 'josh'
*Note Error log::.
This means that something of the following has happened:
* The client program did not call `mysql_close()' before exit.
* The client had been sleeping more than `wait_timeout' or
`interactive_timeout' without doing any requests. *Note SHOW
VARIABLES::.
* The client program ended abruptly in the middle of the transfer.
When the above happens, the server variable `Aborted_clients' is
incremented.
The server variable `Aborted_connects' is incremented when:
* When a connection packet doesn't contain the right information.
* When the user didn't have privileges to connect to a database.
* When a user uses a wrong password.
* When it takes more than `connect_timeout' seconds to get a connect
package.
Note that the above could indicate that someone is trying to break into
your database!
*Note SHOW VARIABLES::.
Other reason for problems with Aborted clients / Aborted connections.
* Usage of duplex Ethernet protocol, both half and full with Linux.
Many Linux Ethernet drivers have this bug. You should test for
this bug by transferring a huge file via ftp between these two
machines. If a transfer goes in burst-pause-burst-pause ... mode
then you are experiencing a Linux duplex syndrome. The only
solution to this problem is switching of both half and full
duplexing on hubs and switches.
* Some problem with the thread library that causes interrupts on
reads.
* Badly configured TCP/IP.
* Faulty Ethernets or hubs or switches, cables ... This can be
diagnosed properly only by replacing hardware.
`The table is full' Error
-------------------------
This error occurs in older *MySQL* versions when an in-memory temporary
table becomes larger than `tmp_table_size' bytes. To avoid this
problem, you can use the `-O tmp_table_size=#' option to `mysqld' to
increase the temporary table size or use the SQL option
`SQL_BIG_TABLES' before you issue the problematic query. *Note `SET
OPTION': SET OPTION.
You can also start `mysqld' with the `--big-tables' option. This is
exactly the same as using `SQL_BIG_TABLES' for all queries.
In *MySQL* Version 3.23, in-memory temporary tables will automatically
be converted to a disk-based `MyISAM' table after the table size gets
bigger than `tmp_table_size'.
`Can't create/write to file' Error
----------------------------------
If you get an error for some queries of type:
Can't create/write to file '\sqla3fe_0.ism'.
this means that *MySQL* can't create a temporary file for the result
set in the given temporary directory. (The above error is a typical
error message on Windows, and the Unix error message is similar.) The
fix is to start mysqld with `--tmpdir=path' or to add to your option
file:
[mysqld]
tmpdir=C:/temp
assuming that the `c:\temp' directory exists. *Note Option files::.
Check also the error code that you get with `perror'. One reason may
also be a disk full error;
shell> perror 28
Error code 28: No space left on device
`Commands out of sync' Error in Client
--------------------------------------
If you get `Commands out of sync; You can't run this command now' in
your client code, you are calling client functions in the wrong order!
This can happen, for example, if you are using `mysql_use_result()' and
try to execute a new query before you have called `mysql_free_result()'.
It can also happen if you try to execute two queries that return data
without a `mysql_use_result()' or `mysql_store_result()' in between.
`Ignoring user' Error
---------------------
If you get the following error:
`Found wrong password for user: 'some_user@some_host'; Ignoring user'
this means that when `mysqld' was started or when it reloaded the
permissions tables, it found an entry in the `user' table with an
invalid password. As a result, the entry is simply ignored by the
permission system.
Possible causes of and fixes for this problem:
* You may be running a new version of `mysqld' with an old `user'
table. You can check this by executing `mysqlshow mysql user' to
see if the password field is shorter than 16 characters. If so,
you can correct this condition by running the
`scripts/add_long_password' script.
* The user has an old password (8 chararacters long) and you didn't
start `mysqld' with the `--old-protocol' option. Update the user
in the `user' table with a new password or restart `mysqld' with
`--old-protocol'.
* You have specified a password in the `user' table without using the
`PASSWORD()' function. Use `mysql' to update the user in the
`user' table with a new password. Make sure to use the `PASSWORD()'
function:
mysql> update user set password=PASSWORD('your password')
where user='XXX';
`Table 'xxx' doesn't exist' Error
---------------------------------
If you get the error `Table 'xxx' doesn't exist' or `Can't find file:
'xxx' (errno: 2)', this means that no table exists in the current
database with the name `xxx'.
Note that as *MySQL* uses directories and files to store databases and
tables, the database and table names are *case sensitive*! (On Windows
the databases and tables names are not case sensitive, but all
references to a given table within a query must use the same case!)
You can check which tables you have in the current database with `SHOW
TABLES'. *Note `SHOW': SHOW.
`Cant' initialize character set xxx' error.
-------------------------------------------
If you get an error like:
MySQL Connection Failed: Can't initialize character set xxx
This means one of the following things:
* The character set is a multi-byte character set and you have not
support for the character set in the client.
In this case you need to recompile the client with
`--with-charset=xxx' or with `--with-extra-charsets=xxx'. *Note
configure options::.
All standard *MySQL* binaries are compiled with
`--with-extra-character-sets=complex' which will enable support for
all multi-byte character sets. *Note Character sets::.
* The character set is a simple character set which is not compiled
into `mysqld' and the character set definition files is not in the
place where the client expect to find them.
In this case you need to:
* Recompile the client with support for the character set.
*Note configure options::.
* Specify to the client where the character set definition
files are. For many client you can do this with the
`--character-sets-dir=path-to-charset-dir' option.
* Copy the character definition files to the path where the
client expect them to be.
How MySQL Handles a Full Disk
=============================
When a disk-full condition occurs, *MySQL* does the following:
* It checks once every minute to see whether or not there is enough
space to write the current row. If there is enough space, it
continues as if nothing had happened.
* Every 6 minutes it writes an entry to the log file warning about
the disk full condition.
To alleviate the problem, you can take the following actions:
* To continue, you only have to free enough disk space to insert all
records.
* To abort the thread, you must send a `mysqladmin kill' to the
thread. The thread will be aborted the next time it checks the
disk (in 1 minute).
* Note that other threads may be waiting for the table that caused
the disk full condition. If you have several "locked" threads,
killing the one thread that is waiting on the disk-full condition
will allow the other threads to continue.
How to Run SQL Commands from a Text File
========================================
The `mysql' client typically is used interactively, like this:
shell> mysql database
However, it's also possible to put your SQL commands in a file and tell
`mysql' to read its input from that file. To do so, create a text file
`text_file' that contains the commands you wish to execute. Then
invoke `mysql' as shown below:
shell> mysql database < text_file
You can also start your text file with a `USE db_name' statement. In
this case, it is unnecessary to specify the database name on the command
line:
shell> mysql < text_file
*Note Programs::.
Where MySQL Stores Temporary Files
==================================
*MySQL* uses the value of the `TMPDIR' environment variable as the
pathname of the directory in which to store temporary files. If you
don't have `TMPDIR' set, *MySQL* uses the system default, which is
normally `/tmp' or `/usr/tmp'. If the file system containing your
temporary file directory is too small, you should edit `safe_mysqld' to
set `TMPDIR' to point to a directory in a file system where you have
enough space! You can also set the temporary directory using the
`--tmpdir' option to `mysqld'.
*MySQL* creates all temporary files as hidden files. This ensures that
the temporary files will be removed if `mysqld' is terminated. The
disadvantage of using hidden files is that you will not see a big
temporary file that fills up the file system in which the temporary
file directory is located.
When sorting (`ORDER BY' or `GROUP BY'), *MySQL* normally uses one or
two temporary files. The maximum disk-space needed is:
(length of what is sorted + sizeof(database pointer))
* number of matched rows
* 2
`sizeof(database pointer)' is usually 4, but may grow in the future for
really big tables.
For some `SELECT' queries, *MySQL* also creates temporary SQL tables.
These are not hidden and have names of the form `SQL_*'.
`ALTER TABLE' creates a temporary table in the same directory as the
original table.
How to Protect `/tmp/mysql.sock' from Being Deleted
===================================================
If you have problems with the fact that anyone can delete the *MySQL*
communication socket `/tmp/mysql.sock', you can, on most versions of
Unix, protect your `/tmp' file system by setting the `sticky' bit on
it. Log in as `root' and do the following:
shell> chmod +t /tmp
This will protect your `/tmp' file system so that files can be deleted
only by their owners or the superuser (`root').
You can check if the `sticky' bit is set by executing `ls -ld /tmp'.
If the last permission bit is `t', the bit is set.
How to Run MySQL As a Normal User
=================================
The *MySQL* server `mysqld' can be started and run by any user. In
order to change `mysqld' to run as a Unix user `user_name', you must do
the following:
1. Stop the server if it's running (use `mysqladmin shutdown').
2. Change the database directories and files so that `user_name' has
privileges to read and write files in them (you may need to do
this as the Unix `root' user):
shell> chown -R user_name /path/to/mysql/datadir
If directories or files within the *MySQL* data directory are
symlinks, you'll also need to follow those links and change the
directories and files they point to. `chown -R' may not follow
symlinks for you.
3. Start the server as user `user_name', or, if you are using *MySQL*
Version 3.22 or later, start `mysqld' as the Unix `root' user and
use the `--user=user_name' option. `mysqld' will switch to run as
the Unix user `user_name' before accepting any connections.
4. If you are using the `mysql.server' script to start `mysqld' when
the system is rebooted, you should edit `mysql.server' to use `su'
to run `mysqld' as user `user_name', or to invoke `mysqld' with
the `--user' option. (No changes to `safe_mysqld' are necessary.)
At this point, your `mysqld' process should be running fine and dandy as
the Unix user `user_name'. One thing hasn't changed, though: the
contents of the permissions tables. By default (right after running the
permissions table install script `mysql_install_db'), the *MySQL* user
`root' is the only user with permission to access the `mysql' database
or to create or drop databases. Unless you have changed those
permissions, they still hold. This shouldn't stop you from accessing
*MySQL* as the *MySQL* `root' user when you're logged in as a Unix user
other than `root'; just specify the `-u root' option to the client
program.
Note that accessing *MySQL* as `root', by supplying `-u root' on the
command line, has _nothing_ to do with *MySQL* running as the Unix
`root' user, or, indeed, as another Unix user. The access permissions
and user names of *MySQL* are completely separate from Unix user names.
The only connection with Unix user names is that if you don't provide a
`-u' option when you invoke a client program, the client will try to
connect using your Unix login name as your *MySQL* user name.
If your Unix box itself isn't secured, you should probably at least put
a password on the *MySQL* `root' users in the access tables.
Otherwise, any user with an account on that machine can run `mysql -u
root db_name' and do whatever he likes.
How to Reset a Forgotten Password
=================================
If you have forgotten the `root' user password for *MySQL*, you can
restore it with the following procedure:
1. Take down the mysqld server by sending a `kill' (not `kill -9') to
the `mysqld' server. The pid is stored in a `.pid' file, which is
normally in the *MySQL* database directory:
kill `cat /mysql-data-directory/hostname.pid`
You must be either the Unix `root' user or the same user the server
runs as to do this.
2. Restart `mysqld' with the `--skip-grant-tables' option.
3. Connect to the mysqld server with `mysql -h hostname mysql' and
change the password with a `GRANT' command. *Note `GRANT': GRANT.
You can also do this with `mysqladmin -h hostname -u user password
'new password''
4. Load the privilege tables with: `mysqladmin -h hostname
flush-privileges' or with the SQL command `FLUSH PRIVILEGES'.
Note that after you started `mysqld' with `--skip-grant-tables', any
usage of `GRANT' commands will give you an `Unknown command' error
until you have executed `FLUSH PRIVILEGES'.
Problems with File Permissions
==============================
If you have problems with file permissions, for example, if `mysql'
issues the following error message when you create a table:
ERROR: Can't find file: 'path/with/filename.frm' (Errcode: 13)
then the environment variable `UMASK' might be set incorrectly when
`mysqld' starts up. The default umask value is `0660'. You can change
this behavior by starting `safe_mysqld' as follows:
shell> UMASK=384 # = 600 in octal
shell> export UMASK
shell> /path/to/safe_mysqld &
By default *MySQL* will create database and `RAID' directories with
permission type 0700. You can modify this behavior by setting the
`UMASK_DIR' variable. If you set this, new directories are created with
the combined `UMASK' and `UMASK_DIR'. For example, if you want to give
group access to all new directories, you can do:
shell> UMASK_DIR=504 # = 770 in octal
shell> export UMASK_DIR
shell> /path/to/safe_mysqld &
In *MySQL* Version 3.23.25 and above, *MySQL* assumes that the value
for `UMASK' and `UMASK_DIR' is in octal if it starts with a zero.
*Note Environment variables::.
File Not Found
==============
If you get `ERROR '...' not found (errno: 23)', `Can't open file: ...
(errno: 24)', or any other error with `errno 23' or `errno 24' from
*MySQL*, it means that you haven't allocated enough file descriptors
for *MySQL*. You can use the `perror' utility to get a description of
what the error number means:
shell> perror 23
File table overflow
shell> perror 24
Too many open files
The problem here is that `mysqld' is trying to keep open too many files
simultaneously. You can either tell `mysqld' not to open so many files
at once or increase the number of file descriptors available to
`mysqld'.
To tell `mysqld' to keep open fewer files at a time, you can make the
table cache smaller by using the `-O table_cache=32' option to
`safe_mysqld' (the default value is 64). Reducing the value of
`max_connections' will also reduce the number of open files (the
default value is 90).
To change the number of file descriptors available to `mysqld', you can
use the option `--open-files-limit=#' to `safe_mysqld' or `-O
open-files-limit=#' to `mysqld'. *Note SHOW VARIABLES::. The easyest
way to do that is to add the option to your option file. *Note Option
files::. If you have an old `mysqld' version that doesn't support
this, you can edit the `safe_mysqld' script. There is a commented-out
line `ulimit -n 256' in the script. You can remove the `'#'' character
to uncomment this line, and change the number 256 to affect the number
of file descriptors available to `mysqld'.
`ulimit' (and `open-files-limit') can increase the number of file
descriptors, but only up to the limit imposed by the operating system.
There is also a 'hard' limit that can only be overrided if you start
`safe_mysqld' or `mysqld' as root (Just remember that you need to also
use the `--user=..' option in this case). If you need to increase the
OS limit on the number of file descriptors available to each process,
consult the documentation for your operating system.
Note that if you run the `tcsh' shell, `ulimit' will not work! `tcsh'
will also report incorrect values when you ask for the current limits!
In this case you should start `safe_mysqld' with `sh'!
Problems Using `DATE' Columns
=============================
The format of a `DATE' value is `'YYYY-MM-DD''. According to ANSI SQL,
no other format is allowed. You should use this format in `UPDATE'
expressions and in the WHERE clause of `SELECT' statements. For
example:
mysql> SELECT * FROM tbl_name WHERE date >= '1997-05-05';
As a convenience, *MySQL* automatically converts a date to a number if
the date is used in a numeric context (and vice versa). It is also smart
enough to allow a "relaxed" string form when updating and in a `WHERE'
clause that compares a date to a `TIMESTAMP', `DATE', or a `DATETIME'
column. (Relaxed form means that any punctuation character may be used
as the separator between parts. For example, `'1998-08-15'' and
`'1998#08#15'' are equivalent.) *MySQL* can also convert a string
containing no separators (such as `'19980815''), provided it makes
sense as a date.
The special date `'0000-00-00'' can be stored and retrieved as
`'0000-00-00'.' When using a `'0000-00-00'' date through *MyODBC*, it
will automatically be converted to `NULL' in *MyODBC* Version 2.50.12
and above, because ODBC can't handle this kind of date.
Because *MySQL* performs the conversions described above, the following
statements work:
mysql> INSERT INTO tbl_name (idate) VALUES (19970505);
mysql> INSERT INTO tbl_name (idate) VALUES ('19970505');
mysql> INSERT INTO tbl_name (idate) VALUES ('97-05-05');
mysql> INSERT INTO tbl_name (idate) VALUES ('1997.05.05');
mysql> INSERT INTO tbl_name (idate) VALUES ('1997 05 05');
mysql> INSERT INTO tbl_name (idate) VALUES ('0000-00-00');
mysql> SELECT idate FROM tbl_name WHERE idate >= '1997-05-05';
mysql> SELECT idate FROM tbl_name WHERE idate >= 19970505;
mysql> SELECT mod(idate,100) FROM tbl_name WHERE idate >= 19970505;
mysql> SELECT idate FROM tbl_name WHERE idate >= '19970505';
However, the following will not work:
mysql> SELECT idate FROM tbl_name WHERE STRCMP(idate,'19970505')=0;
`STRCMP()' is a string function, so it converts `idate' to a string and
performs a string comparison. It does not convert `'19970505'' to a
date and perform a date comparison.
Note that *MySQL* does no checking whether or not the date is correct.
If you store an incorrect date, such as `'1998-2-31'', the wrong date
will be stored. If the date cannot be converted to any reasonable
value, a `0' is stored in the `DATE' field. This is mainly a speed
issue and we think it is up to the application to check the dates, and
not the server.
Time Zone Problems
==================
If you have a problem with `SELECT NOW()' returning values in GMT and
not your local time, you have to set the `TZ' environment variable to
your current time zone. This should be done for the environment in
which the server runs, for example, in `safe_mysqld' or `mysql.server'.
*Note Environment variables::.
Case Sensitivity in Searches
============================
By default, *MySQL* searches are case-insensitive (although there are
some character sets that are never case insensitive, such as `czech').
That means that if you search with `col_name LIKE 'a%'', you will get
all column values that start with `A' or `a'. If you want to make this
search case-sensitive, use something like `INDEX(col_name, "A")=0' to
check a prefix. Or use `STRCMP(col_name, "A") = 0' if the column value
must be exactly `"A"'.
Simple comparison operations (`>=, >, = , < , <=', sorting and
grouping) are based on each character's "sort value". Characters with
the same sort value (like E, e and