MySQL数据库

开发平台：

Visual C++

manual.texi：源码内容

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
@end example
Example of @code{myisamchk -eis} output:
@example
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
@end example
Example of @code{myisamchk -eiv} output:
@example
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
@end example
Here are the sizes of the data and index files for the table used in the
preceding examples:
@example
-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
@end example
Explanations for the types of information @code{myisamchk} produces are
given below. The ``keyfile'' is the index file. ``Record'' and ``row''
are synonymous:
@table @code
@item ISAM file
Name of the ISAM (index) file.
@item Isam-version
Version of ISAM format. Currently always 2.
@item Creation time
When the data file was created.
@item Recover time
When the index/data file was last reconstructed.
@item Data records
How many records are in the table.
@item Deleted blocks
How many deleted blocks still have reserved space.
You can optimize your table to minimize this space.
@xref{Optimization}.
@item 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 @code{Data
records}.
@item Deleted data
How many bytes of non-reclaimed deleted data there are.
You can optimize your table to minimize this space.
@xref{Optimization}.
@item 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 @strong{MySQL} yet. For fixed tables, this is a record address. For
dynamic tables, this is a byte address.
@item 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 @strong{MySQL}. It is always a block address.
@item Max datafile length
How long the table's data file (@code{.MYD} file) can become, in bytes.
@item Max keyfile length
How long the table's key file (@code{.MYI} file) can become, in bytes.
@item Recordlength
How much space each record takes, in bytes.
@item Record format
The format used to store table rows.
The examples shown above use @code{Fixed length}.
Other possible values are @code{Compressed} and @code{Packed}.
@item table description
A list of all keys in the table. For each key, some low-level information
is presented:
@table @code
@item Key
This key's number.
@item Start
Where in the record this index part starts.
@item 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.
@item Index
@code{unique} or @code{multip.} (multiple). Indicates whether or not one value
can exist multiple times in this index.
@item Type
What data-type this index part has. This is an ISAM data-type
with the options @code{packed}, @code{stripped} or @code{empty}.
@item Root
Address of the root index block.
@item Blocksize
The size of each index block. By default this is 1024, but the value may be
changed at compile time.
@item 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 @code{myisamchk -a}. If this is not updated at all, a default
value of 30 is given.
@end table
@item
In the first example above, the 9th key is a multi-part key with two parts.
@item Keyblocks used
What percentage of the keyblocks are used. Because the table used in the
examples had just been reorganized with @code{myisamchk}, the values are very
high (very near the theoretical maximum).
@item Packed
@strong{MySQL} tries to pack keys with a common suffix. This can only be used
for @code{CHAR}/@code{VARCHAR}/@code{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.
@item Max levels
How deep the B-tree for this key is. Large tables with long keys get high
values.
@item Records
How many rows are in the table.
@item M.recordlength
The average record length. For tables with fixed-length records, this is the
exact record length.
@item Packed
@strong{MySQL} strips spaces from the end of strings. The @code{Packed}
value indicates the percentage of savings achieved by doing this.
@item Recordspace used
What percentage of the data file is used.
@item Empty space
What percentage of the data file is unused.
@item 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 @code{myisamchk}.
@xref{Optimization}.
@item Recordblocks
How many blocks (links) are used. For fixed format, this is the same as the number
of records.
@item Deleteblocks
How many blocks (links) are deleted.
@item Recorddata
How many bytes in the data file are used.
@item Deleted data
How many bytes in the data file are deleted (unused).
@item 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.
@item Linkdata
When the dynamic table format is used, record fragments are linked with
pointers (4 to 7 bytes each). @code{Linkdata} is the sum of the amount of
storage used by all such pointers.
@end table
If a table has been compressed with @code{myisampack}, @code{myisamchk
-d} prints additional information about each table column. See
@ref{myisampack, , @code{myisampack}}, for an example of this
information and a description of what it means.
@cindex crash, recovery
@cindex recovery, from crash
@node Crash recovery, Log file maintenance, Table-info, Maintenance
@section Using @code{myisamchk} for Crash Recovery
If you run @code{mysqld} with @code{--skip-locking} (which is the default on
some systems, like Linux), you can't reliably use @code{myisamchk} to
check a table when @code{mysqld} is using the same table. If you
can be sure that no one is accessing the tables through @code{mysqld}
while you run @code{myisamchk}, you only have to do @code{mysqladmin
flush-tables} before you start checking the tables. If you can't
guarantee the above, then you must take down @code{mysqld} while you
check the tables. If you run @code{myisamchk} while @code{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 @code{--skip-locking}, you can use @code{myisamchk}
to check tables at any time. While you do this, all clients that try
to update the table will wait until @code{myisamchk} is ready before
continuing.
If you use @code{myisamchk} to repair or optimize tables, you
@strong{MUST} always ensure that the @code{mysqld} server is not using
the table (this also applies if you are using @code{--skip-locking}).
If you don't take down @code{mysqld} you should at least do a
@code{mysqladmin flush-tables} before you run @code{myisamchk}.
The file format that @strong{MySQL} uses to store data has been extensively
tested, but there are always external circumstances that may cause database
tables to become corrupted:
@itemize @bullet
@item
The @code{mysqld} process being killed in the middle of a write.
@item
Unexpected shutdown of the computer (for example, if the computer is turned
off).
@item
A hardware error.
@end itemize
This chapter describes how to check for and deal with data corruption
in @strong{MySQL} databases. If your tables get corrupted a lot you should
try to find the reason for this! @xref{Debugging server}.
When performing crash recovery, it is important to understand that each table
@code{tbl_name} in a database corresponds to three files in the database
directory:
@multitable @columnfractions .2 .8
@item @strong{File} @tab @strong{Purpose}
@item @file{tbl_name.frm} @tab Table definition (form) file
@item @file{tbl_name.MYD} @tab Data file
@item @file{tbl_name.MYI} @tab Index file
@end multitable
Each of these three file types is subject to corruption in various ways, but
problems occur most often in data files and index files.
@code{myisamchk} works by creating a copy of the @file{.MYD} (data) file
row by row. It ends the repair stage by removing the old @file{.MYD}
file and renaming the new file to the original file name. If you use
@code{--quick}, @code{myisamchk} does not create a temporary @file{.MYD}
file, but instead assumes that the @file{.MYD} file is correct and only
generates a new index file without touching the @file{.MYD} file. This
is safe, because @code{myisamchk} automatically detects if the
@file{.MYD} file is corrupt and aborts the repair in this case. You can
also give two @code{--quick} options to @code{myisamchk}. In this case,
@code{myisamchk} does not abort on some errors (like duplicate key) but
instead tries to resolve them by modifying the @file{.MYD}
file. Normally the use of two @code{--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 @code{myisamchk}.
@menu
* Check:: How to check tables for errors
* Repair:: How to repair tables
* Optimization:: Table optimization
@end menu
@cindex checking, tables for errors
@cindex tables, error checking
@cindex errors, checking tables for
@node Check, Repair, Crash recovery, Crash recovery
@subsection How to Check Tables for Errors
To check a MyISAM table, use the following commands:
@table @code
@item myisamchk tbl_name
This finds 99.99% of all errors. What it can't find is corruption that
involves @strong{ONLY} the data file (which is very unusual). If you want
to check a table, you should normally run @code{myisamchk} without options or
with either the @code{-s} or @code{--silent} option.
@item 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.
@item myisamchk -e tbl_name
This does a complete and thorough check of all data (@code{-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. @code{myisamchk} will normally stop after the first
error it finds. If you want to obtain more information, you can add the
@code{--verbose} (@code{-v}) option. This causes @code{myisamchk} to keep
going, up through a maximum of 20 errors. In normal usage, a simple
@code{myisamchk} (with no arguments other than the table name) is sufficient.
@item myisamchk -e -i tbl_name
Like the previous command, but the @code{-i} option tells @code{myisamchk} to
print some informational statistics, too.
@end table
@cindex tables, repairing
@cindex repairing, tables
@node Repair, Optimization, Check, Crash recovery
@subsection How to Repair Tables
In the following section we only talk about using @code{myisamchk} on
@code{MyISAM} tables (extensions @code{.MYI} and @code{.MYD}). If you
are using @code{ISAM} tables (extensions @code{.ISM} and @code{.ISD}),
you should use @code{isamchk} instead.
Starting with @strong{MySQL} Version 3.23.14, you can repair MyISAM
tables with the @code{REPAIR TABLE} command. @xref{REPAIR TABLE}.
The symptoms of a corrupted table include queries that abort unexpectedly
and observable errors such as these:
@itemize @bullet
@item
@file{tbl_name.frm} is locked against change
@item
Can't find file @file{tbl_name.MYI} (Errcode: ###)
@item
Unexpected end of file
@item
Record file is crashed
@item
Got error ### from table handler
To get more information about the error you can do @code{perror
###}. Here is the most common errors that indicates a problem with the
table:
@example
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
@end example
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:
@itemize @bullet
@item
@code{CREATE TABLE ...} for the table with proper @code{MAX_ROWS} and
@code{AVG_ROW_LENGTH} values. @xref{CREATE TABLE}.
@item
Copy the data over from the old table with @code{INSERT INTO new_table
SELECT * from old_table}.
@item
Rename the old table to the new table:
@code{RENAME old_table to tmp_table, new_table to old_table}
@item
Delete the old table: @code{DROP TABLE tmp_table}.
@end itemize
@end itemize
In these cases, you must repair your tables. @code{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 @code{cd} to the database directory and check the
permissions of the table files. Make sure they are readable by the Unix user
that @code{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 @strong{MySQL} Version 3.23.16 and above, you can (and should) use the
@code{CHECK} and @code{REPAIR} commands to check and repair @code{MyISAM}
tables. @xref{CHECK TABLE}. @xref{REPAIR TABLE}.
The manual section about table maintenance includes the options to
@code{isamchk}/@code{myisamchk}. @xref{Table maintenance}.
The following section is for the cases where the above command fails or
if you want to use the extended features that @code{isamchk}/@code{myisamchk} provides.
If you are going to repair a table from the command line, you must first
take down the @code{mysqld} server. Note that when you do
@code{mysqladmin shutdown} on a remote server, the @code{mysqld} server
will still be alive for a while after @code{mysqladmin} returns, until
all queries are stopped and all keys have been flushed to disk.
@noindent
@strong{Stage 1: Checking your tables}
Run @code{myisamchk *.MYI} or @code{myisamchk -e *.MYI} if you have
more time. Use the @code{-s} (silent) option to suppress unnecessary
information.
If the mysqld server is done you should use the --update option to tell
@code{myisamchk} to mark the table as 'checked'.
You have to repair only those tables for which @code{myisamchk} announces an
error. For such tables, proceed to Stage 2.
If you get weird errors when checking (such as @code{out of
memory} errors), or if @code{myisamchk} crashes, go to Stage 3.
@noindent
@strong{Stage 2: Easy safe repair}
NOTE: If you want repairing to go much faster, you should add: @code{-O
sort_buffer=# -O key_buffer=#} (where # is about 1/4 of the available
memory) to all @code{isamchk/myisamchk} commands.
First, try @code{myisamchk -r -q tbl_name} (@code{-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:
@enumerate
@item
Make a backup of the data file before continuing.
@item
Use @code{myisamchk -r tbl_name} (@code{-r} means ``recovery mode''). This will
remove incorrect records and deleted records from the data file and
reconstruct the index file.
@item
If the preceding step fails, use @code{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).
@end enumerate
If you get weird errors when repairing (such as @code{out of
memory} errors), or if @code{myisamchk} crashes, go to Stage 3.
@noindent
@strong{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:
@enumerate
@item
Move the data file to some safe place.
@item
Use the table description file to create new (empty) data and index files:
@example
shell> mysql db_name
mysql> SET AUTOCOMMIT=1;
mysql> TRUNCATE TABLE table_name;
mysql> quit
@end example
If your SQL version doesn't have @code{TRUNCATE TABLE}, use @code{DELETE FROM
table_name} instead.
@item
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.)
@end enumerate
Go back to Stage 2. @code{myisamchk -r -q} should work now. (This shouldn't
be an endless loop.)
@noindent
@strong{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:
@enumerate
@item
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 @code{myisamchk -r}.
@item
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.
@end enumerate
@cindex tables, optimizing
@cindex optimizing, tables
@node Optimization, , Repair, Crash recovery
@subsection Table Optimization
To coalesce fragmented records and eliminate wasted space resulting from
deleting or updating records, run @code{myisamchk} in recovery mode:
@example
shell> myisamchk -r tbl_name
@end example
You can optimize a table in the same way using the SQL @code{OPTIMIZE TABLE}
statement. @code{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
@code{OPTIMIZE TABLE}. @xref{OPTIMIZE TABLE}.
@code{myisamchk} also has a number of other options you can use to improve
the performance of a table:
@table @code
@item -S, --sort-index
@item -R index_num, --sort-records=index_num
@item -a, --analyze
@end table
For a full description of the option, see @xref{myisamchk syntax}.
@cindex files, log
@cindex maintaining, log files
@cindex log files, maintaining
@node Log file maintenance, , Crash recovery, Maintenance
@section Log file Maintenance
@strong{MySQL} has a lot of log files which make it easy to see what is
going. @xref{Log files}. One must however from time to time clean up
after @code{MysQL} to ensure that the logs doesn't take up too much disk
space.
When using @strong{MySQL} with log files, you will, from time to time,
want to remove/backup old log files and tell @strong{MySQL} to start
logging on new files. @xref{Backup}.
On a Linux (@code{Redhat}) installation, you can use the
@code{mysql-log-rotate} script for this. If you installed @strong{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 @code{cron} to handle log files.
You can force @strong{MySQL} to start using new log files by using
@code{mysqladmin flush-logs} or by using the SQL command @code{FLUSH LOGS}.
If you are using @strong{MySQL} Version 3.21 you must use @code{mysqladmin refresh}.
The above command does the following:
@itemize @bullet
@item
If standard logging (@code{--log}) or slow query logging
(@code{--log-slow-queries}) is used, closes and reopens the log file.
(@file{mysql.log} and @file{`hostname`-slow.log} as default).
@item
If update logging (@code{--log-update}) is used, closes the update log and
opens a new log file with a higher sequence number.
@end itemize
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:
@example
shell> cd mysql-data-directory
shell> mv mysql.log mysql.old
shell> mysqladmin flush-logs
@end example
and then take a backup and remove @file{mysql.old}.
@cindex functions, new
@cindex adding, new functions
@cindex user-defined functions, adding
@cindex UDFs, defined
@cindex functions, user-defined
@node Adding functions, Adding procedures, Maintenance, Top
@chapter Adding New Functions to MySQL
There are two ways to add new functions to @strong{MySQL}:
@itemize @bullet
@item You can add the function through the user-definable function (UDF)
interface. User-definable functions are added and removed dynamically using
the @code{CREATE FUNCTION} and @code{DROP FUNCTION} statements.
@xref{CREATE FUNCTION, , @code{CREATE FUNCTION}}.
@item You can add the function as a native (built in) @strong{MySQL} function.
Native functions are compiled into the @code{mysqld} server and become
available on a permanent basis.
@end itemize
Each method has advantages and disadvantages:
@itemize @bullet
@item
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.
@item
You can add UDFs to a binary @strong{MySQL} distribution. Native functions
require you to modify a source distribution.
@item
If you upgrade your @strong{MySQL} distribution, you can continue to use your
previously installed UDFs. For native functions, you must repeat your
modifications each time you upgrade.
@end itemize
Whichever method you use to add new functions, they may be used just like
native functions such as @code{ABS()} or @code{SOUNDEX()}.
@menu
* Adding UDF:: Adding a new user-definable function
* Adding native function:: Adding a new native function
@end menu
@cindex adding, user-definable functions
@cindex user-defined functions, adding
@cindex functions, user-definable, adding
@node Adding UDF, Adding native function, Adding functions, Adding functions
@section Adding a New User-definable Function
@menu
* UDF calling sequences:: UDF calling sequences
* UDF arguments:: Argument processing
* UDF return values:: Return values and error handling
* UDF compiling:: Compiling and installing user-definable functions
@end menu
For the UDF mechanism to work, functions must be written in C or C++ and your
operating system must support dynamic loading. The @strong{MySQL} source
distribution includes a file @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 @code{--with-mysqld-ldflags=-rdynamic} The reason is that to on
many platforms you can load a dynamic library (with @code{dlopen()})
from a static linked program, which you would get if you are using
@code{--with-mysqld-ldflags=-all-static} If you want to use an UDF that
needs to access symbols from mysqld (like the @code{methaphone} example
in @file{sql/udf_example.cc} that uses @code{default_charset_info}), you must
link the program with @code{-rdynamic}. (see @code{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, @code{XXX()} (uppercase) indicates a SQL function call, and
@code{xxx()} (lowercase) indicates a C/C++ function call.
The C/C++ functions that you write to implement the inferface for
@code{XXX()} are:
@table @asis
@item @code{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:
@multitable @columnfractions .2 .8
@item @strong{SQL type} @tab @strong{C/C++ type}
@item @code{STRING} @tab @code{char *}
@item @code{INTEGER} @tab @code{long long}
@item @code{REAL} @tab @code{double}
@end multitable
@item @code{xxx_init()} (optional)
The initialization function for @code{xxx()}. It can be used to:
@itemize @bullet
@item
Check the number of arguments to @code{XXX()}.
@item
Check that the arguments are of a required type or, alternatively,
tell @strong{MySQL} to coerce arguments to the types you want when
the main function is called.
@item
Allocate any memory required by the main function.
@item
Specify the maximum length of the result.
@item
Specify (for @code{REAL} functions) the maximum number of decimals.
@item
Specify whether or not the result can be @code{NULL}.
@end itemize
@item @code{xxx_deinit()} (optional)
The deinitialization function for @code{xxx()}. It should deallocate any
memory allocated by the initialization function.
@end table
When a SQL statement invokes @code{XXX()}, @strong{MySQL} calls the
initialization function @code{xxx_init()} to let it perform any required
setup, such as argument checking or memory allocation. If @code{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 @code{xxx()} is called once for each row. After all rows have been
processed, the deinitialization function @code{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 @code{xxx_init()}
and free it in @code{xxx_deinit()}.
@cindex calling sequences, UDF
@node UDF calling sequences, UDF arguments, Adding UDF, Adding UDF
@subsection 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 @code{XXX()} to return @code{STRING}, @code{INTEGER}, or @code{REAL}
in the @code{CREATE FUNCTION} statement:
@noindent
For @code{STRING} functions:
@example
char *xxx(UDF_INIT *initid, UDF_ARGS *args,
char *result, unsigned long *length,
char *is_null, char *error);
@end example
@noindent
For @code{INTEGER} functions:
@example
long long xxx(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
@end example
@noindent
For @code{REAL} functions:
@example
double xxx(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
@end example
The initialization and deinitialization functions are declared like this:
@example
my_bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
void xxx_deinit(UDF_INIT *initid);
@end example
The @code{initid} parameter is passed to all three functions. It points to a
@code{UDF_INIT} structure that is used to communicate information between
functions. The @code{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.):
@table @code
@item my_bool maybe_null
@code{xxx_init()} should set @code{maybe_null} to @code{1} if @code{xxx()}
can return @code{NULL}. The default value is @code{1} if any of the
arguments are declared @code{maybe_null}.
@item 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 @code{1.34}, @code{1.345}, and @code{1.3}, the default would be 3,
because @code{1.345} has 3 decimals.
@item 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 @code{initid->decimals}. (For numeric functions, the length
includes any sign or decimal point characters.)
@item char *ptr
A pointer that the function can use for its own purposes. For example,
functions can use @code{initid->ptr} to communicate allocated memory
between functions. In @code{xxx_init()}, allocate the memory and assign it
to this pointer:
@example
initid->ptr = allocated_memory;
@end example
In @code{xxx()} and @code{xxx_deinit()}, refer to @code{initid->ptr} to use
or deallocate the memory.
@end table
@cindex argument processing
@cindex processing, arguments
@node UDF arguments, UDF return values, UDF calling sequences, Adding UDF
@subsection Argument Processing
The @code{args} parameter points to a @code{UDF_ARGS} structure that thas the
members listed below:
@table @code
@item 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:
@example
if (args->arg_count != 2)
@{
strcpy(message,"XXX() requires two arguments");
return 1;
@}
@end example
@item enum Item_result *arg_type
The types for each argument. The possible type values are
@code{STRING_RESULT}, @code{INT_RESULT}, and @code{REAL_RESULT}.
To make sure that arguments are of a given type and return an
error if they are not, check the @code{arg_type} array in the initialization
function. For example:
@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;
@}
@end example
As an alternative to requiring your function's arguments to be of particular
types, you can use the initialization function to set the @code{arg_type}
elements to the types you want. This causes @strong{MySQL} to coerce
arguments to those types for each call to @code{xxx()}. For example, to
specify coercion of the first two arguments to string and integer, do this in
@code{xxx_init()}:
@example
args->arg_type[0] = STRING_RESULT;
args->arg_type[1] = INT_RESULT;
@end example
@item char **args
@code{args->args} communicates information to the initialization function
about the general nature of the arguments your function was called with. For a
constant argument @code{i}, @code{args->args[i]} points to the argument
value. (See below for instructions on how to access the value properly.)
For a non-constant argument, @code{args->args[i]} is @code{0}.
A constant argument is an expression that uses only constants, such as
@code{3} or @code{4*7-2} or @code{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, @code{args->args} contains the
actual arguments that are passed for the row currently being processed.
Functions can refer to an argument @code{i} as follows:
@itemize @bullet
@item
An argument of type @code{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 @code{args->args[i]} and the string length
is @code{args->lengths[i]}. You should not assume that strings are
null-terminated.
@item
For an argument of type @code{INT_RESULT}, you must cast
@code{args->args[i]} to a @code{long long} value:
@example
long long int_val;
int_val = *((long long*) args->args[i]);
@end example
@item
For an argument of type @code{REAL_RESULT}, you must cast
@code{args->args[i]} to a @code{double} value:
@example
double real_val;
real_val = *((double*) args->args[i]);
@end example
@end itemize
@item unsigned long *lengths
For the initialization function, the @code{lengths} array indicates the
maximum string length for each argument. For each invocation of the main
function, @code{lengths} contains the actual lengths of any string arguments
that are passed for the row currently being processed. For arguments of
types @code{INT_RESULT} or @code{REAL_RESULT}, @code{lengths} still contains
the maximum length of the argument (as for the initialization function).
@end table
@cindex UDFs, return values
@cindex return values, UDFs
@cindex errors, handling for UDFs
@cindex handling, errors
@node UDF return values, UDF compiling, UDF arguments, Adding UDF
@subsection Return Values and Error Handling
The initialization function should return @code{0} if no error occurred and
@code{1} otherwise. If an error occurs, @code{xxx_init()} should store a
null-terminated error message in the @code{message} parameter. The message
will be returned to the client. The message buffer is
@code{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 @code{xxx()} is the function value, for
@code{long long} and @code{double} functions. A string functions should
return a pointer to the result and store the length of the string in the
@code{length} arguments. @code{result} is a buffer at least 255 bytes long.
Set these to the contents and length of the return value. For example:
@example
memcpy(result, "result string", 13);
*length = 13;
@end example
If your string functions that needs to return a string longer than 255
bytes, you must allocate the space for it with @code{malloc()} in your
@code{xxx_init()} function or your @code{xxx()} function and free it in
your @code{xxx_deinit()} function. You can store the allocated memory
in the @code{ptr} slot in the @code{UDF_INIT} structure for reuse by
future @code{xxx()} calls. @xref{UDF calling sequences}.
To indicate a return value of @code{NULL} in the main function, set
@code{is_null} to @code{1}:
@example
*is_null = 1;
@end example
To indicate an error return in the main function, set the @code{error}
parameter to @code{1}:
@example
*error = 1;
@end example
If @code{xxx()} sets @code{*error} to @code{1} for any row, the function
value is @code{NULL} for the current row and for any subsequent rows
processed by the statement in which @code{XXX()} was invoked. (@code{xxx()}
will not even be called for subsequent rows.) @strong{NOTE:} In
@strong{MySQL} versions prior to 3.22.10, you should set both @code{*error}
and @code{*is_null}:
@example
*error = 1;
*is_null = 1;
@end example
@cindex compiling, user-defined functions
@cindex UDFs, compiling
@cindex installing, user-defined functions
@node UDF compiling, , UDF return values, Adding UDF
@subsection 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
@file{udf_example.cc} that is included in the @strong{MySQL} source
distribution. This file contains the following functions:
@itemize @bullet
@item
@code{metaphon()} returns a metaphon string of the string argument.
This is something like a soundex string, but it's more tuned for English.
@item
@code{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.
@item
@code{myfunc_int()} returns the sum of the length of its arguments.
@item
@code{sequence([const int])} returns an sequence starting from the given
number or 1 if no number has been given.
@item
@code{lookup()} returns the IP number for a hostname.
@item
@code{reverse_lookup()} returns the hostname for an IP number.
The function may be called with a string @code{"xxx.xxx.xxx.xxx"} or
four numbers.
@end itemize
A dynamically loadable file should be compiled as a sharable object file,
using a command something like this:
@example
shell> gcc -shared -o udf_example.so myfunc.cc
@end example
You can easily find out the correct compiler options for your system by
running this command in the @file{sql} directory of your @strong{MySQL}
source tree:
@example
shell> make udf_example.o
@end example
You should run a compile command similar to the one that @code{make} displays,
except that you should remove the @code{-c} option near the end of the line
and add @code{-o udf_example.so} to the end of the line. (On some systems,
you may need to leave the @code{-c} on the command.)
Once you compile a shared object containing UDFs, you must install it
and tell @strong{MySQL} about it. Compiling a shared object from
@file{udf_example.cc} produces a file named something like
@file{udf_example.so} (the exact name may vary from platform to platform).
Copy this file to some directory searched by @code{ld}, such as
@file{/usr/lib}. On many systems, you can set the @code{LD_LIBRARY} or
@code{LD_LIBRARY_PATH} environment variable to point at the directory where
you have your UDF function files. The @code{dlopen} manual page tells you
which variable you should use on your system. You should set this in
@code{mysql.server} or @code{safe_mysqld} and restart @code{mysqld}.
After the library is installed, notify @code{mysqld} about the new
functions with these commands:
@example
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";
@end example
Functions can be deleted using @code{DROP FUNCTION}:
@example
mysql> DROP FUNCTION metaphon;
mysql> DROP FUNCTION myfunc_double;
mysql> DROP FUNCTION myfunc_int;
mysql> DROP FUNCTION lookup;
mysql> DROP FUNCTION reverse_lookup;
@end example
The @code{CREATE FUNCTION} and @code{DROP FUNCTION} statements update the
system table @code{func} in the @code{mysql} database. The function's name,
type and shared library name are saved in the table. You must have the
@strong{insert} and @strong{delete} privileges for the @code{mysql} database
to create and drop functions.
You should not use @code{CREATE FUNCTION} to add a function that has already
been created. If you need to reinstall a function, you should remove it with
@code{DROP FUNCTION} and then reinstall it with @code{CREATE FUNCTION}. You
would need to do this, for example, if you recompile a new version of your
function, so that @code{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
@code{mysqld} with the @code{--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 @code{CREATE FUNCTION} and not removed with
@code{DROP FUNCTION}.)
@cindex adding, native functions
@cindex native functions, adding
@cindex functions, native, adding
@node Adding native function, , Adding UDF, Adding functions
@section 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 @strong{MySQL} source code. You must compile
@strong{MySQL} yourself from a source distribution. Also note that if you
migrate to another version of @strong{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 @strong{MySQL} function, follow these steps:
@enumerate
@item
Add one line to @file{lex.h} that defines the function name in the
@code{sql_functions[]} array.
@item
Add two lines to @file{sql_yacc.yy}. One indicates the preprocessor
symbol that @code{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 @code{simple_expr} parsing rule.
For an example, check all occurrences of @code{SOUNDEX} in
@file{sql_yacc.yy} to see how this is done.
@item
In @file{item_func.h}, declare a class inheriting from @code{Item_num_func} or
@code{Item_str_func}, depending on whether your function returns a number or a
string.
@item
In @file{item_func.cc}, add one of the following declarations, depending
on whether you are defining a numeric or string function:
@example
double Item_func_newname::val()
longlong Item_func_newname::val_int()
String *Item_func_newname::Str(String *str)
@end example
@item
You should probably also define the following function:
@example
void Item_func_newname::fix_length_and_dec()
@end example
This function should at least calculate @code{max_length} based on the
given arguments. @code{max_length} is the maximum number of characters
the function may return. This function should also set @code{maybe_null = 0}
if the main function can't return a @code{NULL} value. The function can check
if any of the function arguments can return @code{NULL} by checking the
arguments @code{maybe_null} variable.
@end enumerate
All functions must be thread safe.
For string functions, there are some additional considerations to be aware of:
@itemize @bullet
@item
The @code{String *str} argument provides a string
buffer that may be used to hold the result.
@item
The function should return the string that holds the result.
@item
All current string functions try to avoid allocating any memory unless
absolutely necessary!
@end itemize
@cindex procedures, adding
@cindex adding, procedures
@cindex new procedures, adding
@node Adding procedures, ODBC, Adding functions, Top
@chapter Adding New Procedures to MySQL
In @strong{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 @code{GROUP BY} level.
We have created an example procedure in @strong{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. @xref{Contrib}. Which this you can use the LUA
language to load a procedure at runtime into @code{mysqld}.
@menu
* procedure analyse:: Procedure analyse
* Writing a procedure:: Writing a procedure.
@end menu
@node procedure analyse, Writing a procedure, Adding procedures, Adding procedures
@section Procedure Analyse
@code{analyse([max elements,[max memory]])}
This procedure is defined in the @file{sql/sql_analyse.cc}. This
examines the result from your query and returns an analysis of the
results:
@itemize @bullet
@item
@code{max elements} (default 256) is the maximum number of distinct values
@code{analyse} will notice per column. This is used by @code{analyse} to check if
the optimal column type should be of type @code{ENUM}.
@item
@code{max memory} (default 8192) is the maximum memory @code{analyse} should
allocate per column while trying to find all distinct values.
@end itemize
@example
SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max elements,[max memory]])
@end example
@node Writing a procedure, , procedure analyse, Adding procedures
@section 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:
@itemize @bullet
@item @file{sql/sql_analyse.cc}
@item @file{sql/procedure.h}
@item @file{sql/procedure.cc}
@item @file{sql/sql_select.cc}
@end itemize
@cindex ODBC
@cindex Windows
@cindex MyODBC
@node ODBC, Common programs, Adding procedures, Top
@chapter MySQL ODBC Support
@menu
* Installing MyODBC:: How to install MyODBC
* ODBC administrator:: How to fill in the various fields in the ODBC administrator program
* ODBC Problems:: How to report problems with @strong{MySQL} ODBC
* MyODBC clients:: Programs known to work with @strong{MyODBC}
* ODBC and last_insert_id:: How to get the value of an @code{AUTO_INCREMENT} column in ODBC
* MyODBC bug report:: Reporting problems with MyODBC
@end menu
@strong{MySQL} provides support for ODBC by means of the @strong{MyODBC}
program.
@node Installing MyODBC, ODBC administrator, ODBC, ODBC
@section How To Install MyODBC
@strong{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
@strong{MySQL}. @strong{MyODBC} works on Windows95, Windows98, NT, and
on most Unix platforms.
@strong{MyODBC} is in public domain, and you can find the newest version
at @uref{http://www.mysql.com/downloads/api-myodbc.html}.
If you have problem with @strong{MyODBC} and your program also works
with OLEDB, you should try the OLEDB driver that you can find in the
Contrib section. @xref{Contrib}.
Normally you only need to install @strong{MyODBC} on Windows machines.
You only need @strong{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 @strong{MyODBC} on a Unix box, you will also need
an @strong{ODBC} manager. @strong{MyODBC} is known to work with
most of the Unix ODBC managers. You can find a list at these in the
@strong{ODBC}-related links section on the @strong{MySQL} useful links page.
@xref{Useful Links}.
To install @strong{MyODBC} on windows, you should download the
appropriate @strong{MyODBC} .zip file (for Windows or NT/Win2000),
unpack it with @code{WINZIP}, or some similar program, and execute the
@code{SETUP.EXE} file.
On Windows/NT you may get the following error when trying to install
@strong{MyODBC}:
@example
An error occurred while copying C:WINDOWSSYSTEMMFC30.DLL. Restart
Windows and try installing again (before running any applications which
use ODBC)
@end example
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 @code{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 @strong{MyODBC}, and reboot to normal
mode.
@itemize @bullet
@item
To make a connection to a Unix box from a Windows box, with an ODBC
application (one that doesn't support @strong{MySQL} natively), you must
first install @strong{MyODBC} on the Windows machine.
@item
The user and Windows machine must have the access privileges to the
@strong{MySQL} server on the Unix machine. This is set up with the
@code{GRANT} command. @xref{GRANT,,@code{GRANT}}.
@item
You must create an ODBC DSN entry as follows:
@itemize @minus
@item
Open the Control Panel on the Windows machine.
@item
Double-click the ODBC Data Sources 32 bits icon.
@item
Click the tab User DSN.
@item
Click the button Add.
@item
Select @strong{MySQL} in the screen Create New Data Source and click
the Finish button.
@item
The @strong{MySQL} Driver default configuration screen is shown.
@xref{ODBC administrator}.
@end itemize
@item
Now start your application and select the ODBC driver with the DSN you
specified in the ODBC administrator.
@end itemize
Notice that there are other configuration options on the screen of
@strong{MySQL} (trace, don't prompt on connect, etc) that you can try if
you run into problems.
@cindex ODBC, administrator
@node ODBC administrator, ODBC Problems, Installing MyODBC, ODBC
@section How to Fill in the Various Fields in the ODBC Administrator Program
There are three possibilities for specifying the server name on
Windows95:
@itemize @bullet
@item
Use the IP address of the server.
@item
Add a file @file{windowslmhosts} with the following information:
@example
ip hostname
@end example
For example:
@example
194.216.84.21 my_hostname
@end example
@item
Configure the PC to use DNS.
@end itemize
Example of how to fill in the @code{ODBC setup}:
@example
Windows DSN name: test
Description: This is my test database
MySql Database: test
Server: 194.216.84.21
User: monty
Password: my_password
Port:
@end example
The value for the @code{Windows DSN name} field is any name that is unique
in your Windows ODBC setup.
You don't have to specify values for the @code{Server}, @code{User},
@code{Password}, or @code{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 (@value{default_port})
is used.
If you specify the option @code{Read options from C:my.cnf}, the groups
@code{client} and @code{odbc} will be read from the @file{C:my.cnf} file.
You can use all options that are usable by @code{mysql_options()}.
@xref{mysql_options, , @code{mysql_options}}.
@node ODBC Problems, MyODBC clients, ODBC administrator, ODBC
@section How to Report Problems with MyODBC
@strong{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 @strong{MyODBC}, please
send mail to @email{myodbc@@lists.mysql.com} about this!
With some programs you may get an error like:
@code{Another user has modifies the record that you have modified}. In most
cases this can be solved by doing one of the following things:
@itemize @bullet
@item
Add a primary key for the table if there isn't one already.
@item
Add a timestamp column if there isn't one already.
@item
Only use double float fields. Some programs may fail when they compare
single floats.
@end itemize
If the above doesn't help, you should do a @code{MyODBC} trace file and
try to figure out why things go wrong.
@node MyODBC clients, ODBC and last_insert_id, ODBC Problems, ODBC
@section Programs Known to Work with MyODBC
Most programs should work with @strong{MyODBC}, but for each of those
listed below, we have tested it ourselves or received confirmation from
some user that it works:
@table @asis
@item @strong{Program}
@strong{Comment}
@cindex Access program
@item Access
To make Access work:
@itemize @bullet
@item
If you are using Access 2000, you should get and install the newest
Microsoft MDAC (@code{Microsoft Data Access Components}) from
@uref{http://www.microsoft.com/data}. This will fix the following bug
in Access: when you export data to @strong{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 @strong{MySQL} Version 3.23.x, which together
provide a workaround for this bug!
Note that if you are using @strong{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.
@item
Set the ``Return matching rows'' MyODBC option field when connecting to
@strong{MySQL}.
@item
You should have a primary key in the table. If not, new or updated rows
may show up as @code{#Deleted#}.
@item
You should have a timestamp in all tables you want to be able to update.
For maximum portability @code{TIMESTAMP(14)} or simple @code{TIMESTAMP}
is recommended instead of other @code{TIMESTAMP(X)} variations.
@item
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 @code{#Deleted#}
or that you can't find or update rows.
@item
If you still get the error @code{Another user has changed your data} after
adding a @code{TIMESTAMP} column, the following trick may help you:
Don't use @code{table} data sheet view. Create instead a form with the
fields you want, and use that @code{form} data sheet view. You should
set the @code{DefaultValue} property for the @code{TIMESTAMP} column to
@code{NOW()}. It may be a good idea to hide the @code{TIMESTAMP} column
from view so your users are not confused.
@item
Access on NT will report @code{BLOB} columns as @code{OLE OBJECTS}. If
you want to have @code{MEMO} columns instead, you should change the
column to @code{TEXT} with @code{ALTER TABLE}.
@item
Access can't always handle @code{DATE} columns properly. If you have a problem
with these, change the columns to @code{DATETIME}.
@item
In some cases, Access may generate illegal SQL queries that
@strong{MySQL} can't understand. You can fix this by selecting
@code{"Query|SQLSpecific|Pass-Through"} from the Access menu.
@item
If you have in Access a column defined as BYTE, Access will try to export this
as @code{TINYINT} instead of @code{TINYINT UNSIGNED}. This will give you
problems if you have values > 127 in the column!
@end itemize
@cindex ADO program
@item ADO
When you are coding with the ADO API and @strong{MyODBC} you need to put
attention in some default properties that aren't supported by the
@strong{MySQL} server. For example, using the @code{CursorLocation
Property} as @code{adUseServer} will return for the @code{RecordCount
Property} a result of -1. To have the right value, you need to set this
property to @code{adUseClient}, like is showing in the VB code below:
@example
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
@end example
Another workaround is to use a @code{SELECT COUNT(*)} statement
for a similar query to get the correct row count.
@cindex Borland Buidler 4 program
@item Borland Builder 4
When you start a query you can use the property @code{Active} or use the
method @code{Open}. Note that @code{Active} will start by automatically
issuing a @code{SELECT * FROM ...} query that may not be a good thing if
your tables are big!
@item 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 @strong{MyODBC} for @strong{MySQL} data
sources. Allaire has verified that @strong{MyODBC} Version 2.50.26
works with @strong{MySQL} Version 3.22.27 and ColdFusion for Linux. (Any
newer version should also work.) You can download @strong{MyODBC} at
@uref{http://www.mysql.com/downloads/api-myodbc.html}
@cindex ColdFusion program
ColdFusion Version 4.5.1 allows you to us the ColdFusion Administrator
to add the @strong{MySQL} data source. However, the driver is not
included with ColdFusion Version 4.5.1. Before the @strong{MySQL} driver
will appear in the ODBC datasources drop-down list, you must build and
copy the @strong{MyODBC} driver to
@file{/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.
@cindex DataJunction
@item DataJunction
You have to change it to output @code{VARCHAR} rather than @code{ENUM}, as
it exports the latter in a manner that causes @strong{MySQL} grief.
@cindex Excel
@item Excel
Works. Some tips:
@itemize @bullet
@item
If you have problems with dates, try to select them as strings using the
@code{CONCAT()} function. For example:
@example
select CONCAT(rise_time), CONCAT(set_time)
from sunrise_sunset;
@end example
Values retrieved as strings this way should be correctly recognized
as time values by Excel97.
The purpose of @code{CONCAT()} in this example is to fool ODBC into thinking
the column is of ``string type''. Without the @code{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.
@end itemize
@cindex Word program
@item Word
To retrieve data from @strong{MySQL} to Word/Excel documents, you need to
use the @code{MyODBC} driver and the Add-in Microsoft Query help.
For example, create a db with a table containing 2 columns of text:
@itemize @bullet
@item
Insert rows using the mysql client command-line tool.
@item
Create a DSN file using the MyODBC driver, for example, my for the db above.
@item
Open the Word application.
@item
Create a blank new documentation.
@item
Using the tool bar called Database, press the button insert database.
@item
Press the button Get Data.
@item
At the right hand of the screen Get Data, press the button Ms Query.
@item
In the Ms Query create a New Data Source using the DSN file my.
@item
Select the new query.
@item
Select the columns that you want.
@item
Make a filter if you want.
@item
Make a Sort if you want.
@item
Select Return Data to Microsoft Word.
@item
Click Finish.
@item
Click Insert data and select the records.
@item
Click OK and you see the rows in your Word document.
@end itemize
@cindex odbcadmin program
@item odbcadmin
Test program for ODBC.
@cindex Delphi program
@item Delphi
You must use DBE Version 3.2 or newer. Set the `Don't optimize column width'
option field when connecting to @strong{MySQL}.
Also, here is some potentially useful Delphi code that sets up both an
ODBC entry and a BDE entry for @strong{MyODBC} (the BDE entry requires a BDE
Alias Editor that is free at a Delphi Super Page near
you. (Thanks to Bryan Brunton @email{bryan@@flesherfab.com} for this):
@example
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);
@end example
@cindex C++Builder
@item 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.
@cindex Visual Basic
@item 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 @code{SHOW PROCESSLIST} will not work properly. The fix is to set
add the option @code{OPTION=16834} in the ODBC connect string or set
the @code{Change BIGINT columns to INT} option in the MyODBC connect screen.
@item VisualInterDev
If you get the error @code{[Microsoft][ODBC Driver Manager] Driver does
not support this parameter} the reason may be that you have a
@code{BIGINT} in your result. Try setting the @code{Change BIGINT
columns to INT} option in the MyODBC connect screen.
@end table
@cindex AUTO-INCREMENT, ODBC
@node ODBC and last_insert_id, MyODBC bug report, MyODBC clients, ODBC
@section How to Get the Value of an @code{AUTO_INCREMENT} Column in ODBC
A common problem is how to get the value of an automatically generated ID
from an @code{INSERT}. With ODBC, you can do something like this (assuming
that @code{auto} is an @code{AUTO_INCREMENT} field):
@example
INSERT INTO foo (auto,text) VALUES(NULL,'text');
SELECT LAST_INSERT_ID();
@end example
Or, if you are just going to insert the ID into another table, you can do this:
@example
INSERT INTO foo (auto,text) VALUES(NULL,'text');
INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text');
@end example
For the benefit of some ODBC applications (at least Delphi and Access),
the following query can be used to find a newly inserted row:
@example
SELECT * FROM tbl_name WHERE auto IS NULL;
@end example
@cindex reporting, MyODBC problems
@cindex problems, ODBC
@cindex MyODBC, reporting problems
@node MyODBC bug report, , ODBC and last_insert_id, ODBC
@section Reporting Problems with MyODBC
If you encounter difficulties with @strong{MyODBC}, you should start by
making a log file from the ODBC manager (the log you get when requesting
logs from ODBCADMIN) and a @strong{MyODBC} log.
To get a @strong{MyODBC} log, you need to do the following:
@enumerate
@item
Ensure that you are using @code{myodbcd.dll} and not @code{myodbc.dll}.
The easiest way to do this is to get @code{myodbcd.dll} from the MyODBC
distribution and copy it over the @code{myodbc.dll}, which is probably
in your @code{C:windowssystem32} or @code{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 @code{myodbcd.dll}.
@item
Tag the `Trace MyODBC' option flag in the @strong{MyODBC} connect/configure
screen. The log will be written to file @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 @code{myodbcd.dll}
driver (see above).
@item
Start your application and try to get it to fail.
@end enumerate
Check the @code{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 @code{>mysql_real_query} in the @file{myodbc.log} file.
You should also try duplicating the queries in the @code{mysql} monitor
or @code{admndemo} to find out if the error is MyODBC or @strong{MySQL}.
If you find out something is wrong, please only send the relevant rows
(max 40 rows) to @email{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 @uref{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!
@node Common programs, Problems, ODBC, Top
@chapter Using MySQL with Some Common Programs
@menu
* Apache:: Using @strong{MySQL} with Apache
* Borland C++::
@end menu
@cindex Apache
@node Apache, Borland C++, Common programs, Common programs
@section Using MySQL with Apache
The contrib section includes programs that let you authenticate your
users from a @strong{MySQL} database and also let you log your log files
into a @strong{MySQL} table. @xref{Contrib}.
You can change the Apache logging format to be easily readable by
@strong{MySQL} by putting the following into the Apache configuration file:
@example
LogFormat
""%h",%@{%Y%m%d%H%M%S@}t,%>s,"%b","%@{Content-Type@}o",
"%U","%@{Referer@}i","%@{User-Agent@}i""
@end example
In @strong{MySQL} you can do something like this:
@example
LOAD DATA INFILE '/local/access_log' INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\'
@end example
@cindex Borland C++ compiler
@node Borland C++, , Apache, Common programs
@section Borland C++
You can compile the @strong{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 @code{libmysql.dll} libraries (that was compiled
with VC++) with Borland C++. You can do one of the following to avoid
this problem.
@itemize @bullet
@item
You can use the static @strong{MySQL} libraries for Borland C++ that you
can find on @uref{http://www.mysql.net/downloads/os-win32.html}.
@item
Only call @code{mysql_init()} with @code{NULL} as an argument, not a
pre-allocated MYSQL struct.
@end itemize
@cindex problems, common errors
@cindex errors, common
@node Problems, Common problems, Common programs, Top
@chapter Problems and Common Errors
@menu
* What is crashing:: How to determine what is causing problems
* Crashing:: What to do if @strong{MySQL} keeps crashing
* Link errors:: Problems when linking with the @strong{MySQL} client library
* Common errors:: Some common errors when using @strong{MySQL}
* Full disk:: How @strong{MySQL} handles a full disk
* Multiple sql commands:: How to run SQL commands from a text file
* Temporary files:: Where @strong{MySQL} stores temporary files
* Problems with mysql.sock:: How to protect @file{/tmp/mysql.sock}
* Changing MySQL user:: How to run @strong{MySQL} as a normal user
* Resetting permissions:: How to reset a forgotten password.
* File permissions :: Problems with file permissions
* Not enough file handles:: File not found
* Using DATE:: Problems using @code{DATE} columns
* Timezone problems:: Timezone problems
* Case sensitivity:: Case sensitivity in searches
* Problems with NULL:: Problems with @code{NULL} values
* Problems with alias:: Problems with @code{alias}
* Deleting from related tables:: Deleting rows from related tables
* No matching rows:: Solving problems with no matching rows
* ALTER TABLE problems:: Problems with @code{ALTER TABLE}.
* Change column order:: How to change the order of columns in a table
@end menu
@node What is crashing, Crashing, Problems, Problems
@section 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:
@itemize @bullet
@item
If you have one of the following symptoms, then it is probably a hardware
(like memory, motherboard, CPU, or hard disk) or kernel problem:
@itemize @minus
@item
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.)
@item
The mouse pointer doesn't move.
@item
The machine doesn't answer to a remote machine's pings.
@item
Different, unrelated programs don't behave correctly.
@item
If your system rebooted unexpectedly (a faulty user level program should
NEVER be able to take down your system).
@end itemize
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!
@item
If your keyboard is locked up, you may be able to fix this by
logging into your machine from another machine and execute
@code{kbd_mode -a} on it.
@item
Please examine your system log file (/var/log/messages or similar) for
reasons for your problems. If you think the problem is in @strong{MySQL}
then you should also examine @strong{MySQL}'s log files. @xref{Update log}.
@item
If you don't think you have hardware problems, you should try to find
out which program is causing problems.
Try using @code{top}, @code{ps}, @code{taskmanager}, or some similar program,
to check which program is taking all CPU or is locking the machine.
@item
Check with @code{top}, @code{df}, or a similar program if you are out of
memory, disk space, open files, or some other critical resource.
@item
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.
@end itemize
If after you have examined all other possibilities and you have
concluded that it's the @strong{MySQL} server or a @strong{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 @strong{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:
@itemize @bullet
@item
Has the program in question made a segmentation fault (core dumped)?
@item
Is the program taking the whole CPU? Check with @code{top}. Let the
program run for a while, it may be evaluating something heavy.
@item
If it's the @code{mysqld} server that is causing problems, can you
do @code{mysqladmin -u root ping} or @code{mysqladmin -u root processlist}?
@item
What does a client program say (try with @code{mysql}, for example)
when you try to connect to the @strong{MySQL} server?
Does the client jam? Do you get any output from the program?
@end itemize
When sending a bug report, you should of follow the outlines
described in this manual. @xref{Asking questions}.
@cindex crash, repeated
@node Crashing, Link errors, What is crashing, Problems
@section What to Do if MySQL Keeps Crashing
All @strong{MySQL} versions are tested on many platforms before they are
released. This doesn't mean that there aren't any bugs in
@strong{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
@code{mysqld} daemon dies or whether your problem has to do with your
client. You can check how long your @code{mysqld} server has been up by
executing @code{mysqladmin version}. If @code{mysqld} has died, you may
find the reason for this in the file
@file{mysql-data-directory/`hostname`.err}. @xref{Error log}.
Many crashes of @strong{MySQL} are caused by corrupted index / data
files. @strong{MySQL} will update the data on disk, with the
@code{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 @code{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
@strong{MySQL} to sync everything to disk after every SQL command by
starting @code{mysqld} with @code{--flush}.
The above means that normally you shouldn't get corrupted tables unless:
@itemize @bullet
@item
Someone/something killed @code{mysqld} or the machine in the middle
of an update.
@item
You have found a bug in @code{mysqld} that caused it to die in the
middle of an update.
@item
Someone is manipulating the data/index files outside of @strong{mysqld}
without locking the table properly.
@item
If you are running many @code{mysqld} servers on the same data on a
system that doesn't support good file system locks (normally handled by
the @code{lockd} deamon ) or if you are running
multiple servers with @code{--skip-locking}
@item
You have a crashed index/data file that contains very wrong data that
got mysqld confused.
@item
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 @code{ALTER TABLE} on a
repaired copy of the table!
@end itemize
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:
@itemize @bullet
@item
Take down the @code{mysqld} daemon with @code{mysqladmin shutdown}, run
@code{myisamchk --silent --force */*.MYI} on all tables, and restart the
@code{mysqld} daemon. This will ensure that you are running from a clean
state. @xref{Maintenance}.
@item
Use @code{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 @strong{MySQL} restarted. @xref{Query log}.
If you can repeatadly kill @strong{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!
@xref{Bug reports}.
@item
Try to make a test case that we can use to reproduce the problem.
@xref{Reproducable test case}.
@item
Try running the included mysql-test test and the @strong{MySQL}
benchmarks. @xref{MySQL test suite}. They should test @strong{MySQL}
rather well. You can also add code that to the benchmarks to simulates
your application! The benchmarks can be found in the @file{bench}
directory in the source distribution or, for a binary distribution, in
the @file{sql-bench} directory under your @strong{MySQL} installation
directory.
@item
Try @code{fork_test.pl} and @code{fork2_test.pl}.
@item
If you configure @strong{MySQL} for debugging, it will be much easier to
gather information about possible errors if something goes wrong.
Reconfigure @strong{MySQL} with the @code{--with-debug} option to
@code{configure} and then recompile. @xref{Debugging server}.
@item
Configuring @strong{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.
@item
Have you applied the latest patches for your operating system?
@item
Use the @code{--skip-locking} option to @code{mysqld}. On some systems, the
@code{lockd} lock manager does not work properly; the @code{--skip-locking}
option tells @code{mysqld} not to use external locking. (This means that you
cannot run 2 @code{mysqld} servers on the same data and that you must be
careful if you use @code{myisamchk}, but it may be instructive to try the
option as a test.)
@item
Have you tried @code{mysqladmin -u root processlist} when @code{mysqld}
appears to be running but not responding? Sometimes @code{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.
@code{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.
@item
Run the command @code{mysqladmin -i 5 status} or @code{mysqladmin -i 5
-r status} or in a separate window to produce statistics while you run
your other queries.
@item
Try the following:
@enumerate
@item
Start @code{mysqld} from @code{gdb} (or in another debugger).
@item
Run your test scripts.
@item
Print the backtrace and the local variables at the 3 lowest levels. In gdb you
can do this with the following commands when @code{mysqld} has crashed inside
gdb:
@example
backtrace
info local
up
info local
up
info local
@end example
With gdb you can also examine which threads exist with @code{info
threads} and switch to a specific thread with @code{thread #}, where
@code{#} is the thread id.
@end enumerate
@item
Try to simulate your application with a Perl script to force
@strong{MySQL} to crash or misbehave.
@item
Send a normal bug report. @xref{Bug reports}. Be even more detailed
than usual. Because @strong{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).
@item
If you have a problem with tables with dynamic-length rows and you are
not using @code{BLOB/TEXT} columns (but only @code{VARCHAR} columns), you
can try to change all @code{VARCHAR} to @code{CHAR} with @code{ALTER
TABLE}. This will force @strong{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!
@end itemize
@cindex linking, errors
@cindex errors, linking
@cindex problems, linking
@node Link errors, Common errors, Crashing, Problems
@section Problems When Linking with the MySQL Client Library
If you are linking your program and you get errors for unreferenced
symbols that start with @code{mysql_}, like the following:
@example
/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'
@end example
you should be able to solve this by adding @code{-Lpath-to-the-mysql-library
-lmysqlclient} @strong{LAST} on your link line.
If you get @code{undefined reference} errors for the @code{uncompress}
or @code{compress} function, add @code{-lgz} @strong{LAST} on your link
line and try again!
If you get @code{undefined reference} errors for functions that should
exist on your system, like @code{connect}, check the man page for the
function in question, for which libraries you should add to the link
line!
If you get @code{undefined reference} errors for functions that don't
exist on your system, like the following:
@example
mf_format.o(.text+0x201): undefined reference to `__lxstat'
@end example
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 @strong{MySQL} source distribution and compile this yourself.
@xref{Installing source}.
If you are trying to run a program and you then get errors for
unreferenced symbols that start with @code{mysql_} or that the
@code{mysqlclient} library can't be found, this means that your system
can't find the share @code{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:
@itemize @bullet
@item
Add the path to the directory where you have @code{libmysqlclient.so} the
@code{LD_LIBRARY_PATH} environment variable.
@item
Add the path to the directory where you have @code{libmysqlclient.so} the
@code{LD_LIBRARY} environment variable.
@item
Copy @code{libmysqlclient.so} to some place that is searched by your system,
like @file{/lib}, and update the shared library information by executing
@code{ldconfig}.
@end itemize
Another way to solve this problem is to link your program statically, with
@code{-static}, or by removing the dynamic @strong{MySQL} libraries
before linking your code. In the second case you should be
sure that no other programs are using the dynamic libraries!
@cindex errors, list of
@node Common errors, Full disk, Link errors, Problems
@section Some Common Errors When Using MySQL
@menu
* Error Access denied:: @code{Access denied} Error
* Gone away:: @code{MySQL server has gone away} error
* Can not connect to server:: @code{Can't connect to [local] MySQL server} error
* Blocked host:: @code{Host '...' is blocked} error
* Too many connections:: @code{Too many connections} error
* Non-transactional tables:: @code{Some non-transactional changed tables couldn't be rolled back} Error
* Out of memory:: @code{Out of memory} error
* Packet too large:: @code{Packet too large} error
* Communication errors:: Communication errors / Aborted connection
* Full table:: @code{The table is full} error
* Cannot create:: @code{Can't create/write to file} Error
* Commands out of sync:: @code{Commands out of sync} error in client
* Ignoring user:: @code{Ignoring user} error
* Cannot find table:: @code{Table 'xxx' doesn't exist} error
* Cannot initialize character set::
@end menu
@cindex errors, access denied
@cindex problems, access denied errors
@cindex access denied errors
@node Error Access denied, Gone away, Common errors, Common errors
@subsection @code{Access denied} Error
@xref{Privileges}, and especially see @xref{Access denied}.
@node Gone away, Can not connect to server, Error Access denied, Common errors
@subsection @code{MySQL server has gone away} Error
This section also covers the related @code{Lost connection to server
during query} error.
The most common reason for the @code{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 @code{wait_timeout} variable when
you start mysqld.
You can check that the @strong{MySQL} hasn't died by executing
@code{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):
@multitable @columnfractions .3 .7
@item @code{CR_SERVER_GONE_ERROR} @tab The client couldn't send a question to the
server.
@item @code{CR_SERVER_LOST} @tab 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.
@end multitable
You can also get these errors if you send a query to the server that is
incorrect or too large. If @code{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 @code{BLOB} columns), you can increase the query limit by
starting @code{mysqld} with the @code{-O max_allowed_packet=#} option
(default 1M). The extra memory is allocated on demand, so @code{mysqld} will
use more memory only when you issue a big query or when @code{mysqld} must
return a big result row!
@node Can not connect to server, Blocked host, Gone away, Common errors
@subsection @code{Can't connect to [local] MySQL server} error
A @strong{MySQL} client on Unix can connect to the @code{mysqld} server in two
different ways: Unix sockets, which connect through a file in the file
system (default @file{/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 @code{localhost}.
On Windows you can connect only with TCP/IP if the @code{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 @strong{MySQL}. If you
don't give a hostname when connecting to @code{mysqld}, a @strong{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 @code{.} as the hostname.
The error (2002) @code{Can't connect to ...} normally means that there
isn't a @strong{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
@code{mysqld} server.
Start by checking (using @code{ps} or the task manager on Windows) that
there is a process running named @code{mysqld} on your server! If there
isn't any @code{mysqld} process, you should start one. @xref{Starting
server}.
If a @code{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):
@example
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
@end example
Note the use of backquotes rather than forward quotes with the @code{hostname}
command; these cause the output of @code{hostname} (that is, the current
hostname) to be substituted into the @code{mysqladmin} command.
Here are some reasons the @code{Can't connect to local MySQL server}
error might occur:
@itemize @bullet
@item
@code{mysqld} is not running.
@item
You are running on a system that uses MIT-pthreads.
If you are running on a system that doesn't have native threads,
@code{mysqld} uses the MIT-pthreads package. @xref{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:
@example
shell> mysqladmin -h `hostname` version
@end example
@item
Someone has removed the Unix socket that @code{mysqld} uses (default
@file{/tmp/mysqld.sock}). You might have a @code{cron} job that removes
the @strong{MySQL} socket (for example, a job that removes old files
from the @file{/tmp} directory). You can always run @code{mysqladmin
version} and check that the socket @code{mysqladmin} is trying to use
really exists. The fix in this case is to change the @code{cron} job to
not remove @file{mysqld.sock} or to place the socket somewhere else. You
can specify a different socket location at @strong{MySQL} configuration
time with this command:
@example
shell> ./configure --with-unix-socket-path=/path/to/socket
@end example
You can also start @code{safe_mysqld} with the
@code{--socket=/path/to/socket} option and set the environment variable
@code{MYSQL_UNIX_PORT} to the socket pathname before starting your
@strong{MySQL} clients.
@item
You have started the @code{mysqld} server with
the @code{--socket=/path/to/socket} option. If you change the socket
pathname for the server, you must also notify the @strong{MySQL} clients
about the new path. You can do this by setting the environment variable
@code{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:
@example
shell> mysqladmin --socket=/path/to/socket version
@end example
@item
You are using Linux and one thread has died (core dumped). In this case
you must kill the other @code{mysqld} threads (for example, with the
@code{mysql_zap} script before you can start a new @strong{MySQL}
server. @xref{Crashing}.
@item
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
@code{mysqld} so that it uses a directory that you can access.
@end itemize
If you get the error message @code{Can't connect to MySQL server on
some_hostname}, you can try the following things to find out what the
problem is :
@itemize @bullet
@item
Check if the server is up by doing @code{telnet your-host-name
tcp-ip-port-number} and press @code{RETURN} a couple of times. If there
is a @strong{MySQL} server running on this port you should get a
responses that includes the version number of the running @strong{MySQL}
server. If you get an error like @code{telnet: Unable to connect to
remote host: Connection refused}, then there is no server running on the
given port.
@item
Try connecting to the @code{mysqld} daemon on the local machine and check
the TCP/IP port that mysqld it's configured to use (variable @code{port}) with
@code{mysqladmin variables}.
@item
Check that your @code{mysqld} server is not started with the
@code{--skip-networking} option.
@end itemize
@node Blocked host, Too many connections, Can not connect to server, Common errors
@subsection @code{Host '...' is blocked} Error
If you get an error like this:
@example
Host 'hostname' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'
@end example
this means that @code{mysqld} has gotten a lot (@code{max_connect_errors})
of connect requests from the host @code{'hostname'} that have been interrupted
in the middle. After @code{max_connect_errors} failed requests, @code{mysqld}
assumes that something is wrong (like an attack from a cracker), and
blocks the site from further connections until someone executes the command
@code{mysqladmin flush-hosts}.
By default, @code{mysqld} blocks a host after 10 connection errors.
You can easily adjust this by starting the server like this:
@example
shell> safe_mysqld -O max_connect_errors=10000 &
@end example
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 @code{max_connect_errors} variable!
@node Too many connections, Non-transactional tables, Blocked host, Common errors
@subsection @code{Too many connections} Error
If you get the error @code{Too many connections} when you try to connect
to @strong{MySQL}, this means that there is already @code{max_connections}
clients connected to the @code{mysqld} server.
If you need more connections than the default (100), then you should restart
@code{mysqld} with a bigger value for the @code{max_connections} variable.
Note that @code{mysqld} actually allows (@code{max_connections}+1)
clients to connect. The last connection is reserved for a user with the
@strong{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 @code{SHOW PROCESSLIST} to find out what could be
wrong. @xref{SHOW}.
The maximum number of connects @strong{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.
@cindex Non-transactional tables
@node Non-transactional tables, Out of memory, Too many connections, Common errors
@subsection @code{Some non-transactional changed tables couldn't be rolled back} Error
If you get the error/warning: @code{Warning: Some non-transactional
changed tables couldn't be rolled back} when trying to do a
@code{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 @code{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 @code{mysqld} binary.
If @code{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 @code{MyISAM}.
You can check the table type for a table by doing:
@code{SHOW TABLE STATUS LIKE 'table_name'}. @xref{SHOW TABLE STATUS}.
You can check the extensions your @code{mysqld} binary supports by doing:
@code{show variables like 'have_%'}. @xref{SHOW VARIABLES}.
@node Out of memory, Packet too large, Non-transactional tables, Common errors
@subsection @code{Out of memory} Error
If you issue a query and get something like the following error:
@example
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
@end example
note that the error refers to the @strong{MySQL} client @code{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 @code{mysql --quick}, which uses @code{mysql_use_result()}
to retrieve the result set. This places less of a load on the client (but
more on the server).
@node Packet too large, Communication errors, Out of memory, Common errors
@subsection @code{Packet too large} Error
When a @strong{MySQL} client or the @code{mysqld} server gets a packet bigger
than @code{max_allowed_packet} bytes, it issues a @code{Packet too large}
error and closes the connection.
If you are using the @code{mysql} client, you may specify a bigger buffer by
starting the client with @code{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 @code{DBI}), you need to set the packet size when you
start the server. You cau use a command-line option to @code{mysqld} to set
@code{max_allowed_packet} to a larger size. For example, if you are
expecting to store the full length of a @code{BLOB} into a table, you'll need
to start the server with the @code{--set-variable=max_allowed_packet=16M}
option.
@cindex aborted clients
@cindex aborted connection
@cindex connection, aborted
@node Communication errors, Full table, Packet too large, Common errors
@subsection Communication Errors / Aborted Connection
If you find errors like the following in your error log.
@example
010301 14:38:23 Aborted connection 854 to db: 'users' user: 'josh'
@end example
@xref{Error log}.
This means that something of the following has happened:
@itemize @bullet
@item
The client program did not call @code{mysql_close()} before exit.
@item
The client had been sleeping more than @code{wait_timeout} or
@code{interactive_timeout} without doing any requests. @xref{SHOW
VARIABLES}.
@item
The client program ended abruptly in the middle of the transfer.
@end itemize
When the above happens, the server variable @code{Aborted_clients} is
incremented.
The server variable @code{Aborted_connects} is incremented when:
@itemize @bullet
@item
When a connection packet doesn't contain the right information.
@item
When the user didn't have privileges to connect to a database.
@item
When a user uses a wrong password.
@item
When it takes more than @code{connect_timeout} seconds to get
a connect package.
@end itemize
Note that the above could indicate that someone is trying to break into
your database!
@xref{SHOW VARIABLES}.
Other reason for problems with Aborted clients / Aborted connections.
@itemize @bullet
@item
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.
@item
Some problem with the thread library that causes interrupts on reads.
@item
Badly configured TCP/IP.
@item
Faulty Ethernets or hubs or switches, cables ... This can be diagnosed
properly only by replacing hardware.
@end itemize
@cindex table is full
@node Full table, Cannot create, Communication errors, Common errors
@subsection @code{The table is full} Error
This error occurs in older @strong{MySQL} versions when an in-memory temporary
table becomes larger than @code{tmp_table_size} bytes. To avoid this
problem, you can use the @code{-O tmp_table_size=#} option to
@code{mysqld} to increase the temporary table size or use the SQL
option @code{SQL_BIG_TABLES} before you issue the problematic
query. @xref{SET OPTION, , @code{SET OPTION}}.
You can also start @code{mysqld} with the @code{--big-tables} option.
This is exactly the same as using @code{SQL_BIG_TABLES} for all queries.
In @strong{MySQL} Version 3.23, in-memory temporary tables will automatically be
converted to a disk-based @code{MyISAM} table after the table size gets
bigger than @code{tmp_table_size}.
@cindex can't create/write to file
@node Cannot create, Commands out of sync, Full table, Common errors
@subsection @code{Can't create/write to file} Error
If you get an error for some queries of type:
@example
Can't create/write to file '\sqla3fe_0.ism'.
@end example
this means that @strong{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 @code{--tmpdir=path} or to add to your option
file:
@example
[mysqld]
tmpdir=C:/temp
@end example
assuming that the @file{c:\temp} directory exists. @xref{Option files}.
Check also the error code that you get with @code{perror}. One reason
may also be a disk full error;
@example
shell> perror 28
Error code 28: No space left on device
@end example
@cindex commands out of sync
@node Commands out of sync, Ignoring user, Cannot create, Common errors
@subsection @code{Commands out of sync} Error in Client
If you get @code{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 @code{mysql_use_result()} and
try to execute a new query before you have called @code{mysql_free_result()}.
It can also happen if you try to execute two queries that return data without
a @code{mysql_use_result()} or @code{mysql_store_result()} in between.
@node Ignoring user, Cannot find table, Commands out of sync, Common errors
@subsection @code{Ignoring user} Error
If you get the following error:
@code{Found wrong password for user: 'some_user@@some_host'; Ignoring user}
this means that when @code{mysqld} was started or when it reloaded the
permissions tables, it found an entry in the @code{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:
@itemize @bullet
@item
You may be running a new version of @code{mysqld} with an old
@code{user} table.
You can check this by executing @code{mysqlshow mysql user} to see if
the password field is shorter than 16 characters. If so, you can correct this
condition by running the @code{scripts/add_long_password} script.
@item
The user has an old password (8 chararacters long) and you didn't start
@code{mysqld} with the @code{--old-protocol} option.
Update the user in the @code{user} table with a new password or
restart @code{mysqld} with @code{--old-protocol}.
@item
@findex PASSWORD()
You have specified a password in the @code{user} table without using the
@code{PASSWORD()} function. Use @code{mysql} to update the user in the
@code{user} table with a new password. Make sure to use the @code{PASSWORD()}
function:
@example
mysql> update user set password=PASSWORD('your password')
where user='XXX';
@end example
@end itemize
@node Cannot find table, Cannot initialize character set, Ignoring user, Common errors
@subsection @code{Table 'xxx' doesn't exist} Error
If you get the error @code{Table 'xxx' doesn't exist} or @code{Can't
find file: 'xxx' (errno: 2)}, this means that no table exists
in the current database with the name @code{xxx}.
Note that as @strong{MySQL} uses directories and files to store databases and
tables, the database and table names are @strong{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
@code{SHOW TABLES}. @xref{SHOW, , @code{SHOW}}.
@cindex multibyte character sets
@node Cannot initialize character set, , Cannot find table, Common errors
@subsection @code{Can@'t initialize character set xxx} error.
If you get an error like:
@example
MySQL Connection Failed: Can't initialize character set xxx
@end example
This means one of the following things:
@itemize @bullet
@item
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
@code{--with-charset=xxx} or with @code{--with-extra-charsets=xxx}.
@xref{configure options}.
All standard @strong{MySQL} binaries are compiled with
@code{--with-extra-character-sets=complex} which will enable support for
all multi-byte character sets. @xref{Character sets}.
@item
The character set is a simple character set which is not compiled into
@code{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:
@itemize @bullet
@item
Recompile the client with support for the character set.
@xref{configure options}.
@item
Specify to the client where the character set definition files are. For many
client you can do this with the
@code{--character-sets-dir=path-to-charset-dir} option.
@item
Copy the character definition files to the path where the client expect them
to be.
@end itemize
@end itemize
@cindex full disk
@cindex disk full
@node Full disk, Multiple sql commands, Common errors, Problems
@section How MySQL Handles a Full Disk
@noindent
When a disk-full condition occurs, @strong{MySQL} does the following:
@itemize @bullet
@item
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.
@item
Every 6 minutes it writes an entry to the log file warning about the disk
full condition.
@end itemize
@noindent
To alleviate the problem, you can take the following actions:
@itemize @bullet
@item
To continue, you only have to free enough disk space to insert all records.
@item
To abort the thread, you must send a @code{mysqladmin kill} to the thread.
The thread will be aborted the next time it checks the disk (in 1 minute).
@item
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.
@end itemize
@node Multiple sql commands, Temporary files, Full disk, Problems
@section How to Run SQL Commands from a Text File
The @code{mysql} client typically is used interactively, like this:
@example
shell> mysql database
@end example
However, it's also possible to put your SQL commands in a file and tell
@code{mysql} to read its input from that file. To do so, create a text
file @file{text_file} that contains the commands you wish to execute.
Then invoke @code{mysql} as shown below:
@example
shell> mysql database < text_file
@end example
You can also start your text file with a @code{USE db_name} statement. In
this case, it is unnecessary to specify the database name on the command
line:
@example
shell> mysql < text_file
@end example
@xref{Programs}.
@node Temporary files, Problems with mysql.sock, Multiple sql commands, Problems
@section Where MySQL Stores Temporary Files
@strong{MySQL} uses the value of the @code{TMPDIR} environment variable as
the pathname of the directory in which to store temporary files. If you don't
have @code{TMPDIR} set, @strong{MySQL} uses the system default, which is
normally @file{/tmp} or @file{/usr/tmp}. If the file system containing your
temporary file directory is too small, you should edit @code{safe_mysqld} to
set @code{TMPDIR} to point to a directory in a file system where you have
enough space! You can also set the temporary directory using the
@code{--tmpdir} option to @code{mysqld}.
@strong{MySQL} creates all temporary files as hidden files. This ensures
that the temporary files will be removed if @code{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 (@code{ORDER BY} or @code{GROUP BY}), @strong{MySQL} normally
uses one or two temporary files. The maximum disk-space needed is:
@example
(length of what is sorted + sizeof(database pointer))
* number of matched rows
* 2
@end example
@code{sizeof(database pointer)} is usually 4, but may grow in the future for
really big tables.
For some @code{SELECT} queries, @strong{MySQL} also creates temporary SQL
tables. These are not hidden and have names of the form @file{SQL_*}.
@code{ALTER TABLE} creates a temporary table in the same directory as
the original table.
@cindex @code{mysql.sock}, protection
@cindex deletion, @code{mysql.sock}
@node Problems with mysql.sock, Changing MySQL user, Temporary files, Problems
@section How to Protect @file{/tmp/mysql.sock} from Being Deleted
If you have problems with the fact that anyone can delete the
@strong{MySQL} communication socket @file{/tmp/mysql.sock}, you can,
on most versions of Unix, protect your @file{/tmp} file system by setting
the @code{sticky} bit on it. Log in as @code{root} and do the following:
@example
shell> chmod +t /tmp
@end example
This will protect your @file{/tmp} file system so that files can be deleted
only by their owners or the superuser (@code{root}).
You can check if the @code{sticky} bit is set by executing @code{ls -ld /tmp}.
If the last permission bit is @code{t}, the bit is set.
@cindex starting, @code{mysqld}
@cindex @code{mysqld}, starting
@node Changing MySQL user, Resetting permissions, Problems with mysql.sock, Problems
@section How to Run MySQL As a Normal User
The @strong{MySQL} server @code{mysqld} can be started and run by any user.
In order to change @code{mysqld} to run as a Unix user @code{user_name}, you must
do the following:
@enumerate
@item
Stop the server if it's running (use @code{mysqladmin shutdown}).
@item
Change the database directories and files so that @code{user_name} has
privileges to read and write files in them (you may need to do this as
the Unix @code{root} user):
@example
shell> chown -R user_name /path/to/mysql/datadir
@end example
If directories or files within the @strong{MySQL} data directory are
symlinks, you'll also need to follow those links and change the directories
and files they point to. @code{chown -R} may not follow symlinks for
you.
@item
Start the server as user @code{user_name}, or, if you are using
@strong{MySQL} Version 3.22 or later, start @code{mysqld} as the Unix @code{root}
user and use the @code{--user=user_name} option. @code{mysqld} will switch
to run as the Unix user @code{user_name} before accepting any connections.
@item
If you are using the @code{mysql.server} script to start @code{mysqld} when
the system is rebooted, you should edit @code{mysql.server} to use @code{su}
to run @code{mysqld} as user @code{user_name}, or to invoke @code{mysqld}
with the @code{--user} option. (No changes to @code{safe_mysqld} are
necessary.)
@end enumerate
At this point, your @code{mysqld} process should be running fine and dandy as
the Unix user @code{user_name}. One thing hasn't changed, though: the
contents of the permissions tables. By default (right after running the
permissions table install script @code{mysql_install_db}), the @strong{MySQL}
user @code{root} is the only user with permission to access the @code{mysql}
database or to create or drop databases. Unless you have changed those
permissions, they still hold. This shouldn't stop you from accessing
@strong{MySQL} as the @strong{MySQL} @code{root} user when you're logged in
as a Unix user other than @code{root}; just specify the @code{-u root} option
to the client program.
Note that accessing @strong{MySQL} as @code{root}, by supplying @code{-u
root} on the command line, has @emph{nothing} to do with @strong{MySQL} running
as the Unix @code{root} user, or, indeed, as another Unix user. The access
permissions and user names of @strong{MySQL} are completely separate from
Unix user names. The only connection with Unix user names is that if you
don't provide a @code{-u} option when you invoke a client program, the client
will try to connect using your Unix login name as your @strong{MySQL} user
name.
If your Unix box itself isn't secured, you should probably at least put a
password on the @strong{MySQL} @code{root} users in the access tables.
Otherwise, any user with an account on that machine can run @code{mysql -u
root db_name} and do whatever he likes.
@cindex passwords, forgotten
@cindex passwords, resetting
@cindex root user, password resetting
@node Resetting permissions, File permissions , Changing MySQL user, Problems
@section How to Reset a Forgotten Password
If you have forgotten the @code{root} user password for @strong{MySQL}, you
can restore it with the following procedure:
@enumerate
@item
Take down the mysqld server by sending a @code{kill} (not @code{kill
-9}) to the @code{mysqld} server. The pid is stored in a @code{.pid}
file, which is normally in the @strong{MySQL} database directory:
@example
kill `cat /mysql-data-directory/hostname.pid`
@end example
You must be either the Unix @code{root} user or the same user the server
runs as to do this.
@item
Restart @code{mysqld} with the @code{--skip-grant-tables} option.
@item
Connect to the mysqld server with @code{mysql -h hostname mysql} and change
the password with a @code{GRANT} command. @xref{GRANT,,@code{GRANT}}.
You can also do this with
@code{mysqladmin -h hostname -u user password 'new password'}
@item
Load the privilege tables with: @code{mysqladmin -h hostname
flush-privileges} or with the SQL command @code{FLUSH PRIVILEGES}.
@end enumerate
Note that after you started @code{mysqld} with @code{--skip-grant-tables},
any usage of @code{GRANT} commands will give you an @code{Unknown command}
error until you have executed @code{FLUSH PRIVILEGES}.
@cindex files, permissions
@cindex error mesaages, can't find file
@cindex files, not found message
@node File permissions , Not enough file handles, Resetting permissions, Problems
@section Problems with File Permissions
If you have problems with file permissions, for example, if @code{mysql}
issues the following error message when you create a table:
@example
ERROR: Can't find file: 'path/with/filename.frm' (Errcode: 13)
@end example
@tindex UMASK environment variable
@tindex Environment variable, UMASK
then the environment variable @code{UMASK} might be set incorrectly when
@code{mysqld} starts up. The default umask value is @code{0660}. You can
change this behavior by starting @code{safe_mysqld} as follows:
@example
shell> UMASK=384 # = 600 in octal
shell> export UMASK
shell> /path/to/safe_mysqld &
@end example
@tindex UMASK_DIR environment variable
@tindex Environment variable, UMASK_DIR
By default @strong{MySQL} will create database and @code{RAID}
directories with permission type 0700. You can modify this behavior by
setting the @code{UMASK_DIR} variable. If you set this, new
directories are created with the combined @code{UMASK} and
@code{UMASK_DIR}. For example, if you want to give group access to
all new directories, you can do:
@example
shell> UMASK_DIR=504 # = 770 in octal
shell> export UMASK_DIR
shell> /path/to/safe_mysqld &
@end example
In @strong{MySQL} Version 3.23.25 and above, @strong{MySQL} assumes that the
value for @code{UMASK} and @code{UMASK_DIR} is in octal if it starts
with a zero.
@xref{Environment variables}.
@node Not enough file handles, Using DATE, File permissions , Problems
@section File Not Found
If you get @code{ERROR '...' not found (errno: 23)}, @code{Can't open
file: ... (errno: 24)}, or any other error with @code{errno 23} or
@code{errno 24} from @strong{MySQL}, it means that you haven't allocated
enough file descriptors for @strong{MySQL}. You can use the
@code{perror} utility to get a description of what the error number
means:
@example
shell> perror 23
File table overflow
shell> perror 24
Too many open files
@end example
The problem here is that @code{mysqld} is trying to keep open too many
files simultaneously. You can either tell @code{mysqld} not to open so
many files at once or increase the number of file descriptors
available to @code{mysqld}.
To tell @code{mysqld} to keep open fewer files at a time, you can make
the table cache smaller by using the @code{-O table_cache=32} option to
@code{safe_mysqld} (the default value is 64). Reducing the value of
@code{max_connections} will also reduce the number of open files (the
default value is 90).
@tindex ulimit
To change the number of file descriptors available to @code{mysqld}, you
can use the option @code{--open-files-limit=#} to @code{safe_mysqld} or
@code{-O open-files-limit=#} to @code{mysqld}. @xref{SHOW VARIABLES}.
The easyest way to do that is to add the option to your option file.
@xref{Option files}. If you have an old @code{mysqld} version that
doesn't support this, you can edit the @code{safe_mysqld} script. There
is a commented-out line @code{ulimit -n 256} in the script. You can
remove the @code{'#'} character to uncomment this line, and change the
number 256 to affect the number of file descriptors available to
@code{mysqld}.
@code{ulimit} (and @code{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 @code{safe_mysqld} or @code{mysqld} as root (Just remember that
you need to also use the @code{--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 @code{tcsh} shell, @code{ulimit} will not work!
@code{tcsh} will also report incorrect values when you ask for the current
limits! In this case you should start @code{safe_mysqld} with @code{sh}!
@findex DATE
@cindex DATE columns, problems
@cindex problems, @code{DATE} columns
@node Using DATE, Timezone problems, Not enough file handles, Problems
@section Problems Using @code{DATE} Columns
The format of a @code{DATE} value is @code{'YYYY-MM-DD'}. According to ANSI
SQL, no other format is allowed. You should use this format in @code{UPDATE}
expressions and in the WHERE clause of @code{SELECT} statements. For
example:
@example
mysql> SELECT * FROM tbl_name WHERE date >= '1997-05-05';
@end example
As a convenience, @strong{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 @code{WHERE}
clause that compares a date to a @code{TIMESTAMP}, @code{DATE}, or a
@code{DATETIME} column. (Relaxed form means that any punctuation character
may be used as the separator between parts. For example, @code{'1998-08-15'}
and @code{'1998#08#15'} are equivalent.) @strong{MySQL} can also convert a
string containing no separators (such as @code{'19980815'}), provided it
makes sense as a date.
The special date @code{'0000-00-00'} can be stored and retrieved as
@code{'0000-00-00'.} When using a @code{'0000-00-00'} date through
@strong{MyODBC}, it will automatically be converted to @code{NULL} in
@strong{MyODBC} Version 2.50.12 and above, because ODBC can't handle this kind of
date.
Because @strong{MySQL} performs the conversions described above, the following
statements work:
@example
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';
@end example
@noindent
However, the following will not work:
@example
mysql> SELECT idate FROM tbl_name WHERE STRCMP(idate,'19970505')=0;
@end example
@code{STRCMP()} is a string function, so it converts @code{idate} to
a string and performs a string comparison. It does not convert
@code{'19970505'} to a date and perform a date comparison.
Note that @strong{MySQL} does no checking whether or not the date is
correct. If you store an incorrect date, such as @code{'1998-2-31'}, the
wrong date will be stored. If the date cannot be converted to any reasonable
value, a @code{0} is stored in the @code{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.
@cindex timezone problems
@cindex problems, timezone
@tindex TZ environment variable
@tindex Environment variable, TZ
@node Timezone problems, Case sensitivity, Using DATE, Problems
@section Time Zone Problems
If you have a problem with @code{SELECT NOW()} returning values in GMT and
not your local time, you have to set the @code{TZ} environment variable to
your current time zone. This should be done for the environment in which
the server runs, for example, in @code{safe_mysqld} or @code{mysql.server}.
@xref{Environment variables}.
@cindex case sensitivity, in searches
@cindex searching, and case-sensitivity
@cindex Chinese
@cindex Big5 Chinese character encoding
@node Case sensitivity, Problems with NULL, Timezone problems, Problems
@section Case Sensitivity in Searches
By default, @strong{MySQL} searches are case-insensitive (although there are
some character sets that are never case insensitive, such as @code{czech}).
That means that if you search with @code{col_name LIKE 'a%'}, you will get all
column values that start with @code{A} or @code{a}. If you want to make this
search case-sensitive, use something like @code{INDEX(col_name, "A")=0} to
check a prefix. Or use @code{STRCMP(col_name, "A") = 0} if the column value
must be exactly @code{"A"}.
Simple comparison operations (@code{>=, >, = , < , <=}, sorting and
grouping) are based on each character's ``sort value''. Characters with
the same sort value (like E, e and