diff options
Diffstat (limited to 'Docs/manual.texi')
| -rw-r--r-- | Docs/manual.texi | 793 |
1 files changed, 543 insertions, 250 deletions
diff --git a/Docs/manual.texi b/Docs/manual.texi index 8166b0ce35f..cf1df129f29 100644 --- a/Docs/manual.texi +++ b/Docs/manual.texi @@ -69,7 +69,7 @@ END-INFO-DIR-ENTRY @sp 10 @center @titlefont{@strong{MySQL} Reference Manual} @sp 10 -@center Copyright @copyright{} 1997-2001 TcX AB, Detron HB and MySQL Finland AB +@center Copyright @copyright{} 1997-2001 MySQL AB @c blank page after title page makes page 1 be a page front. @c also makes the back of the title page blank. @page @@ -532,10 +532,20 @@ GEMINI Tables InnoDB Tables -* InnoDB overview:: +* InnoDB overview:: InnoDB tables overview * InnoDB start:: InnoDB startup options -* Using InnoDB tables:: Using InnoDB tables -* InnoDB restrictions:: Some restrictions on @code{InnoDB} tables: +* Creating an InnoDB database:: Creating an InnoDB database. +* Using InnoDB tables:: Creating InnoDB tables +* Adding and removing:: Adding and removing InnoDB data and log files +* Backing up:: Backing up and recovering an InnoDB database +* Moving:: Moving an InnoDB database to another machine +* InnoDB transaction model:: InnoDB transaction model. +* Implementation:: Implementation of multiversioning +* Table and index:: Table and index structures +* File space management:: File space management and disk i/o +* Error handling:: Error handling +* InnoDB restrictions:: Some restrictions on InnoDB tables +* InnoDB contact information:: InnoDB contact information. MySQL Tutorial @@ -658,7 +668,7 @@ Speed of Queries that Access or Update Data MySQL Utilites * Programs:: What do the executables do? -* mysqld-max:: +* mysqld-max:: mysqld-max, An extended mysqld server * safe_mysqld:: safe_mysqld, the wrapper around mysqld * mysqld_multi:: Program for managing multiple @strong{MySQL} servers * mysql:: The command line tool @@ -751,6 +761,7 @@ Problems and Common Errors * 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 +* Temporary table problems:: Some Common Errors When Using MySQL @@ -918,6 +929,7 @@ Changes in release 4.0.x (Development; Alpha) Changes in release 3.23.x (Stable) +* News-3.23.38:: Changes in release 3.23.38 * News-3.23.37:: Changes in release 3.23.37 * News-3.23.36:: Changes in release 3.23.36 * News-3.23.35:: Changes in release 3.23.35 @@ -2122,7 +2134,7 @@ because of bugs in @strong{MySQL}. @cindex retrieving, data @cindex data, ISAM table handler -@item The MyISAM table handler --- Gamma +@item The MyISAM table handler --- Stable This is new in @strong{MySQL} Version 3.23. It's largely based on the ISAM table code but has a lot of new and very useful features. @@ -2213,7 +2225,7 @@ The Berkeley DB code is very stable, but we are still improving the interface between @strong{MySQL} and BDB tables, so it will take some time before this is as tested as the other table types. -@item Innodb Tables -- Alpha +@item InnoDB Tables -- Alpha This is a very recent addition to @code{MySQL} and is not very tested yet. @item Automatic recovery of MyISAM tables - Beta @@ -2384,7 +2396,7 @@ Apart from the following links, you can find and download a lot of Information about the German MySQL mailing list. @item @uref{http://www2.rent-a-database.de/mysql/} -@strong{MySQL} manual in German. +@strong{MySQL} handbook in German. @item @uref{http://www.bitmover.com:8888//home/bk/mysql} Web access to the @strong{MySQL} BitKeeper repository. @@ -2432,6 +2444,9 @@ New Client libraries for the Mac OS Classic (Macintosh). @item @uref{http://www.lilback.com/macsql/} Client libraries for Mac OS Classic (Macintosh). + +@item @uref{http://sixk.maniasys.com/index_en.html} +MySQL for Amiga @end table @subheading Perl-related Links @@ -2706,7 +2721,7 @@ Popular iODBC Driver Manager (libiodbc) now available as Open Source. @item @uref{http://users.ids.net/~bjepson/freeODBC/} The FreeODBC Pages. -@item @uref{http:/http://genix.net/unixODBC/} +@item @uref{http://genix.net/unixODBC/} The unixODBC Project goals are to develop and promote unixODBC to be the definitive standard for ODBC on the Linux platform. This is to include GUI support for KDE. @@ -2763,7 +2778,7 @@ environment. @item @uref{http://www.wix.com/mysql-hosting/} Registry of Web providers who support @strong{MySQL}. -@item @uref{http://www.softagency.co.jp/mysql/index.en.phtml} +@item @uref{http://www.softagency.co.jp/mysql/index.en.html} Links about using @strong{MySQL} in Japan/Asia. @item @uref{http://abattoir.cc.ndsu.nodak.edu/~nem/mysql/udf/} @@ -2883,11 +2898,6 @@ same (or similar) query. Uses PHP and @strong{MySQL}. @item @uref{http://www.stopbit.com/} Stopbit - A technology news site using @strong{MySQL} and PHP. -@c Added 990604 -@c EMAIL: ah@dybdahl.dk -@item @uref{http://www.jokes2000.com/scripts/} -Example scripts at Jokes2000. - @item @uref{http://www.linuxsupportline.com/~kalendar/} KDE based calendar manager - The calendar manager has both single user (file based) and multi-user (@strong{MySQL} database) support. @@ -3092,8 +3102,23 @@ unsubscribe from the @code{myodbc} list, send a message to @email{myodbc-subscribe@@lists.mysql.com} or @email{myodbc-unsubscribe@@lists.mysql.com}. -There is also a german mailing list. You can find information about this -at: @uref{http://www.4t2.com/mysql}. +The following table shows some @strong{MySQL} mailing in other languages than +English. Note that these are not operated by @strong{MySQL AB}, so we can't +guarantee the quality on these. + +@table @code +@item @email{mysql-france-subscribe@@yahoogroups.com, A French mailing list} +@item @email{list@@tinc.net, A Korean mailing list} +Email @code{subscribe mysql your@@email.address} to this list. +@item @email{mysql-de-request@@lists.4t2.com, A German mailing list} +Email @code{subscribe mysql-de your@@email.address} to this list. +You can find information about this mailing list at +@uref{http://www.4t2.com/mysql}. +@item @email{mysql-br-request@@listas.linkway.com.br, A Portugese mailing list} +Email @code{subscribe mysql-br your@@email.address} to this list. +@item @email{mysql-alta@@elistas.net, A Spanish mailing list} +Email @code{subscribe mysql your@@email.address} to this list. +@end table @cindex net etiquette @cindex mailing lists, archive location @@ -5000,6 +5025,8 @@ sucessfully on the following operating system/thread package combinations: @item AIX 4.x with native threads. @xref{IBM-AIX}. @item +Amiga. +@item BSDI 2.x with the included MIT-pthreads package. @xref{BSDI}. @item BSDI 3.0, 3.1 and 4.x with native threads. @xref{BSDI}. @@ -5909,12 +5936,15 @@ A reasonable @code{tar} to unpack the distribution. GNU @code{tar} is known to work. Sun @code{tar} is known to have problems. @item -A working ANSI C++ compiler. @code{gcc} >= 2.8.1, @code{egcs} >= -1.0.2, SGI C++, and SunPro C++ are some of the compilers that are known to -work. @code{libg++} is not needed when using @code{gcc}. @code{gcc} -2.7.x has a bug that makes it impossible to compile some perfectly legal -C++ files, such as @file{sql/sql_base.cc}. If you only have @code{gcc} 2.7.x, -you must upgrade your @code{gcc} to be able to compile @strong{MySQL}. +A working ANSI C++ compiler. @code{gcc} >= 2.95.2, @code{egcs} >= 1.0.2 +or @code{egcs 2.91.66}, SGI C++, and SunPro C++ are some of the +compilers that are known to work. @code{libg++} is not needed when +using @code{gcc}. @code{gcc} 2.7.x has a bug that makes it impossible +to compile some perfectly legal C++ files, such as +@file{sql/sql_base.cc}. If you only have @code{gcc} 2.7.x, you must +upgrade your @code{gcc} to be able to compile @strong{MySQL}. @code{gcc} +2.8.1 is also known to have problems on some platforms so it should be +avoided if there exists a new compiler for the platform.. @code{gcc} >= 2.95.2 is recommended when compiling @strong{MySQL} Version 3.23.x. @@ -7716,7 +7746,7 @@ For the source distribution of @code{glibc} 2.0.7, a patch that is easy to apply and is tested with @strong{MySQL} may be found at: @example -@uref{http://www.mysql.com/Download/Linux/glibc-2.0.7-total-patch.tar.gz} +@uref{http://www.mysql.com/Downloads/Linux/glibc-2.0.7-total-patch.tar.gz} @end example If you experience crashes like these when you build @strong{MySQL}, you can @@ -8506,8 +8536,8 @@ We recommend the following @code{configure} line with @code{egcs} and @code{gcc 2.95} on AIX: @example -CC="gcc -pipe -mcpu=power2 -Wa,-many" \ -CXX="gcc -pipe -mcpu=power2 -Wa,-many" \ +CC="gcc -pipe -mcpu=power -Wa,-many" \ +CXX="gcc -pipe -mcpu=power -Wa,-many" \ CXXFLAGS="-felide-constructors -fno-exceptions -fno-rtti" \ ./configure --prefix=/usr/local/mysql --with-low-memory @end example @@ -8519,6 +8549,21 @@ available. We don't know if the @code{-fno-exceptions} is required with option generates faster code, we recommend that you should always use this option with @code{egcs / gcc}. +If you get a problem with assembler code try changing the -mcpu=xxx to +match your cpu. Typically power2, power, or powerpc may need to be used, +alternatively you might need to use 604 or 604e. I'm not positive but I +would think using "power" would likely be safe most of the time, even on +a power2 machine. + +If you don't know what your cpu is then do a "uname -m", this will give +you back a string that looks like "000514676700", with a format of +xxyyyyyymmss where xx and ss are always 0's, yyyyyy is a unique system +id and mm is the id of the CPU Planar. A chart of these values can be +found at +@uref{http://www.rs6000.ibm.com/doc_link/en_US/a_doc_lib/cmds/aixcmds5/uname.htm}. +This will give you a machine type and a machine model you can use to +determine what type of cpu you have. + If you have problems with signals (@strong{MySQL} dies unexpectedly under high load) you may have found an OS bug with threads and signals. In this case you can tell @strong{MySQL} not to use signals by @@ -8539,6 +8584,29 @@ On some versions of AIX, linking with @code{libbind.a} makes @code{getservbyname} core dump. This is an AIX bug and should be reported to IBM. +For AIX 4.2.1 and gcc you have to do the following changes. + +After configuring, edit @file{config.h} and @file{include/my_config.h} +and change the line that says + +@example +#define HAVE_SNPRINTF 1 +@end example + +to + +@example +#undef HAVE_SNPRINTF +@end example + +And finally, in @file{mysqld.cc} you need to add a prototype for initgoups. + +@example +#ifdef _AIX41 +extern "C" int initgroups(const char *,int); +#endif +@end example + @node HP-UX 10.20, HP-UX 11.x, IBM-AIX, Source install system issues @subsection HP-UX Version 10.20 Notes @@ -8666,6 +8734,16 @@ The optimization flags used by @strong{MySQL} (-O3) are not recognized by HP's compilers. I did not change the flags. @end itemize +If you get the following error from @code{configure} + +@example +checking for cc option to accept ANSI C... no +configure: error: MySQL requires a ANSI C compiler (and a C++ compiler). Try gcc. See the Installation chapter in the Reference Manual. +@end example + +Check that you don't have the path to the K&R compiler before the path +to the HP-UX C and C++ compiler. + @node Mac OS X, BEOS, HP-UX 11.x, Source install system issues @subsection Mac OS X Notes @@ -9939,7 +10017,7 @@ yourself with the different BDB specific startup options. @xref{BDB start}. If you are using Gemini tables, refer to the Gemini-specific startup options. @xref{GEMINI start}. -If you are using Innodb tables, refer to the Innodb-specific startup +If you are using InnoDB tables, refer to the InnoDB-specific startup options. @xref{InnoDB start}. @node Automatic start, Command-line options, Starting server, Post-installation @@ -10048,6 +10126,10 @@ Chroot mysqld daemon during startup. Recommended security measure. It will somewhat limit @code{LOAD DATA INFILE} and @code{SELECT ... INTO OUTFILE} though. +@item --core-file +Write a core file if @code{mysqld} dies. For some systems you must also +specify @code{--core-file-size} to @code{safe_mysqld}. @xref{safe_mysqld}. + @item -h, --datadir=path Path to the database root. @@ -10202,6 +10284,10 @@ gives everyone @emph{full access} to all databases! (You can tell a running server to start using the grant tables again by executing @code{mysqladmin flush-privileges} or @code{mysqladmin reload}.) +@item --skip-host-cache +Never use host name cache for faster name-ip resolution, but query DNS server +on every connect instead. @xref{DNS}. + @item --skip-locking Don't use system locking. To use @code{isamchk} or @code{myisamchk} you must shut down the server. @xref{Stability}. Note that in @strong{MySQL} Version @@ -10217,14 +10303,14 @@ Don't listen for TCP/IP connections at all. All interaction with @code{mysqld} must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. @xref{DNS}. -@item --skip-host-cache -Never use host name cache for faster name-ip resolution, but query DNS server -on every connect instead. @xref{DNS}. - @item --skip-new Don't use new, possible wrong routines. Implies @code{--skip-delay-key-write}. This will also set default table type to @code{ISAM}. @xref{ISAM}. +@item --skip-stack-trace +Don't write stack traces. This option is useful when you are running +@code{mysqld} under a debugger. @xref{Debugging server}. + @item --skip-safemalloc If @strong{MySQL} is configured with @code{--with-debug=full}, all programs will check the memory for overruns for every memory allocation and memory @@ -11925,10 +12011,11 @@ communication. All other information is transferred as text that can be read by anyone who is able to watch the connection. If you are concerned about this, you can use the compressed protocol (in @strong{MySQL} Version 3.22 and above) -to make things much harder. To make things even more secure you should -use @code{ssh} (see @uref{http://www.cs.hut.fi/ssh}). With this, you -can get an encrypted TCP/IP connection between a @strong{MySQL} server -and a @strong{MySQL} client. +to make things much harder. To make things even more secure you should use +@code{ssh}. You can find an open source ssh client at +@uref{http://www.openssh.org}, and a commercial ssh client at +@uref{http://www.ssh.com}. With this, you can get an encrypted TCP/IP +connection between a @strong{MySQL} server and a @strong{MySQL} client. To make a @strong{MySQL} system secure, you should strongly consider the following suggestions: @@ -18168,6 +18255,11 @@ per-connection basis. It will not be changed by another client. It will not even be changed if you update another @code{AUTO_INCREMENT} column with a non-magic value (that is, a value that is not @code{NULL} and not @code{0}). +If you insert many rows at the same time with an insert statement, +@code{LAST_INSERT_ID()} returns the value for the first inserted row. +The reason for this is so that you it makes it possible to easily reproduce +the same @code{INSERT} statement against some other server. + @cindex sequence emulation If @code{expr} is given as an argument to @code{LAST_INSERT_ID()} in an @code{UPDATE} clause, then the value of the argument is returned as a @@ -18671,9 +18763,10 @@ When you insert a value of @code{NULL} (recommended) or @code{0} into an @xref{mysql_insert_id, , @code{mysql_insert_id()}}. If you delete the row containing the maximum value for an -@code{AUTO_INCREMENT} column, the value will be reused with an ISAM -table but not with a @code{MyISAM} table. If you delete all rows in the -table with @code{DELETE FROM table_name} (without a @code{WHERE}) in +@code{AUTO_INCREMENT} column, the value will be reused with an +@code{ISAM}, @code{BDB} or @code{INNODB} table but not with a +@code{MyISAM} table. If you delete all rows in the table with +@code{DELETE FROM table_name} (without a @code{WHERE}) in @code{AUTOCOMMIT} mode, the sequence starts over for both table types. @strong{NOTE:} There can be only one @code{AUTO_INCREMENT} column per @@ -19683,7 +19776,7 @@ SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [ORDER BY @{unsigned_integer | col_name | formula@} [ASC | DESC] ,...] [LIMIT [offset,] rows] [PROCEDURE procedure_name] - [FOR UPDATE | IN SHARE MODE]] + [FOR UPDATE | LOCK IN SHARE MODE]] @end example @c help end @@ -20425,7 +20518,8 @@ like you could do this, but that was a bug that has been corrected. @section @code{LOAD DATA INFILE} Syntax @example -LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] +LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' + [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY '\t'] @@ -20453,6 +20547,12 @@ If you specify the keyword @code{LOW_PRIORITY}, execution of the @code{LOAD DATA} statement is delayed until no other clients are reading from the table. +If you specify the keyword @code{CONCURRENT} with a @code{MyISAM} table, +then other threads can retrieve data from the table while @code{LOAD +DATA} is executing. Using this option will of course affect the +performance of @code{LOAD DATA} a bit even if no other thread is using +the table at the same time. + Using @code{LOCAL} will be a bit slower than letting the server access the files directly, because the contents of the file must travel from the client host to the server host. On the other hand, you do not need the @@ -21682,7 +21782,7 @@ if @code{--skip-bdb} is used. @code{YES} if @code{mysqld} supports Gemini tables. @code{DISABLED} if @code{--skip-gemini} is used. @item @code{have_innodb} -@code{YES} if @code{mysqld} supports Innodb tables. @code{DISABLED} +@code{YES} if @code{mysqld} supports InnoDB tables. @code{DISABLED} if @code{--skip-innodb} is used. @item @code{have_raid} @code{YES} if @code{mysqld} supports the @code{RAID} option. @@ -22473,11 +22573,11 @@ non-transactional table will not change. If you are using @code{BEGIN} or @code{SET AUTOCOMMIT=0}, you should use the @strong{MySQL} binary log for backups instead of the -old update log; The transaction is stored in the binary log -in one chunk, during @code{COMMIT}, the to ensure and @code{ROLLBACK}:ed -transactions are not stored. @xref{Binary log}. +older update log. Transactions are stored in the binary log +in one chunk, upon @code{COMMIT}, to ensure that transactions which are +rolled back are not stored. @xref{Binary log}. -The following commands automatically ends an transaction (as if you had done +The following commands automatically end a transaction (as if you had done a @code{COMMIT} before executing the command): @multitable @columnfractions .33 .33 .33 @@ -22531,6 +22631,9 @@ locks while the thread is waiting for the @code{WRITE} lock. You should only use @code{LOW_PRIORITY WRITE} locks if you are sure that there will eventually be a time when no threads will have a @code{READ} lock. +@code{LOCK TABLES} and @code{UNLOCK TABLES} both commits any active +transactions. + When you use @code{LOCK TABLES}, you must lock all tables that you are going to use and you must use the same alias that you are going to use in your queries! If you are using a table multiple times in a query @@ -22767,7 +22870,7 @@ You can set the default isolation level for @code{mysqld} with @findex GRANT @findex REVOKE -@node GRANT, HANDLER, SET TRANSACTION, Reference +@node GRANT, CREATE INDEX, SET TRANSACTION, Reference @section @code{GRANT} and @code{REVOKE} Syntax @example @@ -23330,7 +23433,14 @@ the table type, the index and data will be stored in other files. The default table type in @strong{MySQL} is @code{MyISAM}. If you are trying to use a table type that is not compiled-in or activated, -@strong{MySQL} will instead create a table of type @code{MyISAM}. +@strong{MySQL} will instead create a table of type @code{MyISAM}. This +is a very useful feature when you want to copy tables between different +SQL servers that supports different table types (like copying tables to +a slave that is optimized for speed by not having transactional tables). +This automatic table changing can however also be very confusing for new +@strong{MySQL} users. We plan to fix this by introducing warnings in +@strong{MySQL} 4.0 and giving a warning when a table type is automaticly +changed. You can convert tables between different types with the @code{ALTER TABLE} statement. @xref{ALTER TABLE, , @code{ALTER TABLE}}. @@ -23430,7 +23540,7 @@ Internal handling of one @code{AUTO_INCREMENT} column. @code{MyISAM} will automatically update this on @code{INSERT/UPDATE}. The @code{AUTO_INCREMENT} value can be reset with @code{myisamchk}. This will make @code{AUTO_INCREMENT} columns faster (at least 10 %) and old -numbers will not be reused as with the old ISAM. Note that when an +numbers will not be reused as with the old @code{ISAM}. Note that when an @code{AUTO_INCREMENT} is defined on the end of a multi-part-key the old behavior is still present. @item @@ -23799,7 +23909,7 @@ is not signaled to the other servers. @section MERGE Tables @code{MERGE} tables are new in @strong{MySQL} Version 3.23.25. The code -is still in beta, but should stabilize soon! +is still in gamma, but should be resonable stable. A @code{MERGE} table is a collection of identical @code{MyISAM} tables that can be used as one. You can only @code{SELECT}, @code{DELETE}, and @@ -23812,8 +23922,8 @@ will only clear the mapping for the table, not delete everything in the mapped tables. (We plan to fix this in 4.0). With identical tables we mean that all tables are created with identical -column information. You can't put a MERGE over tables where the columns -are packed differently or doesn't have exactly the same columns. +column and key information. You can't put a MERGE over tables where the +columns are packed differently or doesn't have exactly the same columns. Some of the tables can however be compressed with @code{myisampack}. @xref{myisampack}. @@ -23848,8 +23958,10 @@ More efficient repairs. It's easier to repair the individual files that are mapped to a @code{MERGE} file than trying to repair a real big file. @item Instant mapping of many files as one. A @code{MERGE} table uses the -index of the individual tables. It doesn't need an index of its one. -This makes @code{MERGE} table collections VERY fast to make or remap. +index of the individual tables. It doesn't need to maintain an index of +its one. This makes @code{MERGE} table collections VERY fast to make or +remap. Note that you must specify the key definitions when you create +a @code{MERGE} table!. @item If you have a set of tables that you join to a big table on demand or batch, you should instead create a @code{MERGE} table on them on demand. @@ -24062,62 +24174,62 @@ SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*) * 2) @section BDB or Berkeley_DB Tables @menu -* BDB overview:: -* BDB install:: -* BDB start:: -* BDB characteristic:: -* BDB TODO:: -* BDB portability:: -* BDB errors:: +* BDB overview:: Overview of BDB Tables +* BDB install:: Installing BDB +* BDB start:: BDB startup options +* BDB characteristic:: Some characteristic of @code{BDB} tables: +* BDB TODO:: Some things we need to fix for BDB in the near future: +* BDB portability:: Operating systems supported by @strong{BDB} +* BDB errors:: Errors You May Get When Using BDB Tables @end menu @node BDB overview, BDB install, BDB, BDB -@subsection Overview over BDB tables +@subsection Overview of BDB Tables -BDB tables are included in the @strong{MySQL} source distribution -starting from 3.23.34 and will be activated in the @strong{MySQL}-max +Support for BDB tables is included in the @strong{MySQL} source distribution +starting from Version 3.23.34 and is activated in the @strong{MySQL}-Max binary. -Berkeley DB (@uref{http://www.sleepycat.com}) has provided -@strong{MySQL} with a transaction-safe table handler. This will survive -crashes and also provides @code{COMMIT} and @code{ROLLBACK} on -transactions. The @strong{MySQL} source distribution comes with a BDB -distribution that has a couple of small patches to make it work more -smoothly with @strong{MySQL}. You can't use a not-patched @code{BDB} -version with @strong{MySQL}. +BerkeleyDB, available at @uref{http://www.sleepycat.com/} has provided +@strong{MySQL} with a transactional table handler. By using BerkeleyDB +tables, your tables may have a greater chance of surviving crashes, and also +provides @code{COMMIT} and @code{ROLLBACK} on transactions. The +@strong{MySQL} source distribution comes with a BDB distribution that has a +couple of small patches to make it work more smoothly with @strong{MySQL}. +You can't use a non-patched @code{BDB} version with @strong{MySQL}. -We at MySQL AB are working in close cooperating with Sleepycat to -keep the quality of the @strong{MySQL} - BDB interface high. +We at @strong{MySQL AB} are working in close cooperation with Sleepycat to +keep the quality of the @strong{MySQL}/BDB interface high. When it comes to supporting BDB tables, we are committed to help our users to locate the problem and help creating a reproducable test case for any problems involving BDB tables. Any such test case will be forwarded to Sleepycat who in turn will help us find and fix the -problem. As this is a two stage operating, any problems with BDB tables -may take a little longer for us to fix than for other table handlers, -but as the Berkeley code itself has been used by many other applications -than @strong{MySQL} we don't envision any big problems with this. -@xref{Table handler support}. +problem. As this is a two stage operation, any problems with BDB tables +may take a little longer for us to fix than for other table handlers. +However, as the BerkeleyDB code itself has been used by many other +applications than @strong{MySQL}, we don't envision any big problems with +this. @xref{Table handler support}. @node BDB install, BDB start, BDB overview, BDB @subsection Installing BDB If you have downloaded a binary version of @strong{MySQL} that includes -support for Berkeley DB, simply follow the instructions for -installing a binary version of @strong{MySQL}. @xref{Installing binary}. -@xref{mysqld-max}. +support for BerkeleyDB, simply follow the instructions for installing a +binary version of @strong{MySQL}. +@xref{Installing binary}. @xref{mysqld-max}. To compile @strong{MySQL} with Berkeley DB support, download @strong{MySQL} -3.23.34 or newer and configure @code{MySQL} with the -@code{--with-berkeley-db} option. @xref{Installing source}. +Version 3.23.34 or newer and configure @code{MySQL} with the +@code{--with-berkeley-db} option. @xref{Installing source}. @example cd /path/to/source/of/mysql-3.23.34 ./configure --with-berkeley-db @end example -Please refer to the manual provided by @code{BDB} distribution for -more/updated information. +Please refer to the manual provided with the @code{BDB} distribution for +more updated information. Even though Berkeley DB is in itself very tested and reliable, the @strong{MySQL} interface is still considered beta quality. @@ -24425,24 +24537,24 @@ limited by @code{gemini_connection_limit}. The default is 100 users. NuSphere is working on removing these limitations. -@node InnoDB, , GEMINI, Table types +@node InnoDB, , GEMINI, Table types @section InnoDB Tables @menu -* InnoDB overview:: InnoDB tables overview -* InnoDB start:: InnoDB startup options -* Creating an InnoDB database:: Creating an InnoDB database -* Using InnoDB tables:: Creating InnoDB tables -* Adding and removing:: Adding and removing InnoDB data and log files -* Backing up:: Backing up and recovering an InnoDB database -* Moving:: Moving an InnoDB database to another machine -* InnoDB transaction model:: InnoDB transaction model -* Implementation:: Implementation of multiversioning -* Table and index:: Table and index structures -* File space management:: File space management and disk i/o -* Error handling:: Error handling -* InnoDB restrictions:: Some restrictions on InnoDB tables -* InnoDB contact information:: InnoDB contact information +* InnoDB overview:: InnoDB tables overview +* InnoDB start:: InnoDB startup options +* Creating an InnoDB database:: Creating an InnoDB database. +* Using InnoDB tables:: Creating InnoDB tables +* Adding and removing:: Adding and removing InnoDB data and log files +* Backing up:: Backing up and recovering an InnoDB database +* Moving:: Moving an InnoDB database to another machine +* InnoDB transaction model:: InnoDB transaction model. +* Implementation:: Implementation of multiversioning +* Table and index:: Table and index structures +* File space management:: File space management and disk i/o +* Error handling:: Error handling +* InnoDB restrictions:: Some restrictions on InnoDB tables +* InnoDB contact information:: InnoDB contact information. @end menu @node InnoDB overview, InnoDB start, InnoDB, InnoDB @@ -24452,55 +24564,56 @@ InnoDB tables are included in the @strong{MySQL} source distribution starting from 3.23.34a and are activated in the @strong{MySQL -max} binary. -If you have downloaded a binary version of MySQL that includes -support for InnoDB, simply follow the instructions for -installing a binary version of MySQL. -See section 4.6 'Installing a MySQL Binary Distribution'. +If you have downloaded a binary version of @strong{MySQL} that includes +support for InnoDB (mysqld-max), simply follow the instructions for +installing a binary version of @strong{MySQL}. @xref{Installing binary}. +@xref{mysqld-max}. -To compile MySQL with InnoDB support, download MySQL-3.23.34a or newer -and configure @code{MySQL} with the -@code{--with-innobase} option. Starting from MySQL-3.23.37 the option -is @code{--with-innodb}. See section -4.7 'Installing a MySQL Source Distribution'. +To compile @strong{MySQL} with InnoDB support, download MySQL-3.23.37 or newer +and configure @code{MySQL} with the @code{--with-innodb} option. +@xref{Installing source}. @example cd /path/to/source/of/mysql-3.23.37 ./configure --with-innodb @end example -InnoDB provides MySQL with a transaction safe table handler with +InnoDB provides @strong{MySQL} with a transaction safe table handler with commit, rollback, and crash recovery capabilities. InnoDB does locking on row level, and also provides an Oracle-style consistent non-locking read in @code{SELECTS}, which increases transaction concurrency. There is not need for lock escalation in InnoDB, because row level locks in InnoDB fit in very small space. -Technically, InnoDB is a database backend placed under MySQL. InnoDB +Technically, InnoDB is a database backend placed under @strong{MySQL}. InnoDB has its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a tablespace, which may consist of several files. This is different from, for example, @code{MyISAM} tables where each table is stored as a separate file. InnoDB is distributed under the GNU GPL License Version 2 (of June 1991). -In the source distribution of MySQL, InnoDB appears as a subdirectory. +In the source distribution of @strong{MySQL}, InnoDB appears as a subdirectory. -@node InnoDB start +@node InnoDB start, Creating an InnoDB database, InnoDB overview, InnoDB @subsection InnoDB startup options -Beginning from MySQL-3.23.37 the prefix of the options is changed +Beginning from @strong{MySQL}-3.23.37 the prefix of the options is changed from @code{innobase_...} to @code{innodb_...}. -To use InnoDB tables you must specify configuration parameters -in the MySQL configuration file in the @code{[mysqld]} section of -the configuration file @file{my.cnf}. -Suppose you have a Windows NT machine with 128 MB RAM and a -single 10 GB hard disk. -Below is an example of possible configuration parameters in @file{my.cnf} for -InnoDB: +To use InnoDB tables you @strong{MUST} specify configuration parameters +in the @strong{MySQL} configuration file in the @code{[mysqld]} section of +the configuration file @file{my.cnf}. @xref{Option files}. + +The only required parameter to use InnoDB is @code{innodb_data_file_path}, +but you should set others if you want to get a better performance. + +Suppose you have a Windows NT machine with 128 MB RAM and a single 10 GB +hard disk. Below is an example of possible configuration parameters in +@file{my.cnf} for InnoDB: @example -innodb_data_home_dir = c:\ibdata innodb_data_file_path = ibdata1:2000M;ibdata2:2000M +innodb_data_home_dir = c:\ibdata set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = c:\iblogs set-variable = innodb_log_files_in_group=3 @@ -24522,8 +24635,8 @@ Below is an example of possible configuration parameters in @file{my.cnf} for InnoDB: @example -innodb_data_home_dir = / innodb_data_file_path = ibdata/ibdata1:2000M;dr2/ibdata/ibdata2:2000M +innodb_data_home_dir = / set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = /dr3 set-variable = innodb_log_files_in_group=3 @@ -24593,7 +24706,7 @@ log archiving. The value of this parameter should currently be set the same as @code{innodb_log_group_home_dir}. @item @code{innodb_log_archive} @tab This value should currently be set to 0. As recovery from a backup is -done by MySQL using its own log files, there is currently no need to +done by @strong{MySQL} using its own log files, there is currently no need to archive InnoDB log files. @item @code{innodb_buffer_pool_size} @tab The size of the memory buffer InnoDB uses to cache data and indexes of @@ -24608,7 +24721,7 @@ and other internal data structures. A sensible value for this might be 2M, but the more tables you have in your application the more you will need to allocate here. If InnoDB runs out of memory in this pool, it will start to allocate memory from the operating system, and write -warning messages to the MySQL error log. +warning messages to the @strong{MySQL} error log. @item @code{innodb_file_io_threads} @tab Number of file i/o threads in InnoDB. Normally, this should be 4, but on Windows NT disk i/o may benefit from a larger number. @@ -24622,18 +24735,18 @@ InnoDB cannot notice. In cases like this the timeout is useful to resolve the situation. @end multitable -@node Creating an InnoDB database +@node Creating an InnoDB database, Using InnoDB tables, InnoDB start, InnoDB @subsection Creating an InnoDB database -Suppose you have installed MySQL and have edited @file{my.cnf} so that +Suppose you have installed @strong{MySQL} and have edited @file{my.cnf} so that it contains the necessary InnoDB configuration parameters. -Before starting MySQL you should check that the directories you have +Before starting @strong{MySQL} you should check that the directories you have specified for InnoDB data files and log files exist and that you have access rights to those directories. InnoDB cannot create directories, only files. Check also you have enough disk space for the data and log files. -When you now start MySQL, InnoDB will start creating your data files +When you now start @strong{MySQL}, InnoDB will start creating your data files and log files. InnoDB will print something like the following: @example @@ -24658,9 +24771,9 @@ InnoDB: Started mysqld: ready for connections @end example -A new InnoDB database has now been created. You can connect to the MySQL -server with the usual MySQL client programs like @code{mysql}. -When you shut down the MySQL server with @file{mysqladmin shutdown}, +A new InnoDB database has now been created. You can connect to the @strong{MySQL} +server with the usual @strong{MySQL} client programs like @code{mysql}. +When you shut down the @strong{MySQL} server with @file{mysqladmin shutdown}, InnoDB output will be like the following: @example @@ -24675,7 +24788,7 @@ will see the files created. The log directory will also contain a small file named @file{ib_arch_log_0000000000}. That file resulted from the database creation, after which InnoDB switched off log archiving. -When MySQL is again started, the output will be like the following: +When @strong{MySQL} is again started, the output will be like the following: @example ~/mysqlm/sql > mysqld @@ -24685,17 +24798,17 @@ mysqld: ready for connections @subsubsection If something goes wrong in database creation -If something goes wrong in an InnoDB database creation, you should delete -all files created by InnoDB. This means all data files, all log files, -the small archived log file, and in the case you already did create -some InnoDB tables, delete also the corresponding @file{.frm} -files for these tables from the MySQL database directories. Then you can -try the InnoDB database creation again. +If something goes wrong in an InnoDB database creation, you should +delete all files created by InnoDB. This means all data files, all log +files, the small archived log file, and in the case you already did +create some InnoDB tables, delete also the corresponding @file{.frm} +files for these tables from the @strong{MySQL} database +directories. Then you can try the InnoDB database creation again. -@node Using InnoDB tables +@node Using InnoDB tables, Adding and removing, Creating an InnoDB database, InnoDB @subsection Creating InnoDB tables -Suppose you have started the MySQL client with the command +Suppose you have started the @strong{MySQL} client with the command @code{mysql test}. To create a table in the InnoDB format you must specify @code{TYPE = InnoDB} in the table creation SQL command: @@ -24706,15 +24819,15 @@ CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) TYPE = InnoDB; This SQL command will create a table and an index on column @code{A} into the InnoDB tablespace consisting of the data files you specified -in @file{my.cnf}. In addition MySQL will create a file -@file{CUSTOMER.frm} to the MySQL database directory @file{test}. +in @file{my.cnf}. In addition @strong{MySQL} will create a file +@file{CUSTOMER.frm} to the @strong{MySQL} database directory @file{test}. Internally, InnoDB will add to its own data dictionary an entry for table @code{'test/CUSTOMER'}. Thus you can create a table -of the same name @code{CUSTOMER} in another database of MySQL, and +of the same name @code{CUSTOMER} in another database of @strong{MySQL}, and the table names will not collide inside InnoDB. You can query the amount of free space in the InnoDB tablespace -by issuing the table status command of MySQL for any table you have +by issuing the table status command of @strong{MySQL} for any table you have created with @code{TYPE = InnoDB}. Then the amount of free space in the tablespace appears in the table comment section in the output of @code{SHOW}. An example: @@ -24732,16 +24845,16 @@ You must drop the tables individually. Also take care not to delete or add @file{.frm} files to your InnoDB database manually: use @code{CREATE TABLE} and @code{DROP TABLE} commands. InnoDB has its own internal data dictionary, and you will get problems -if the MySQL @file{.frm} files are out of 'sync' with the InnoDB +if the @strong{MySQL} @file{.frm} files are out of 'sync' with the InnoDB internal data dictionary. -@node Adding and removing +@node Adding and removing, Backing up, Using InnoDB tables, InnoDB @subsection Adding and removing InnoDB data and log files You cannot increase the size of an InnoDB data file. To add more into your tablespace you have to add a new data file. To do this you have to -shut down your MySQL database, edit the @file{my.cnf} file, adding a -new file to @code{innodb_data_file_path}, and then start MySQL +shut down your @strong{MySQL} database, edit the @file{my.cnf} file, adding a +new file to @code{innodb_data_file_path}, and then start @strong{MySQL} again. Currently you cannot remove a data file from InnoDB. To decrease the @@ -24750,14 +24863,14 @@ all your tables, create a new database, and import your tables to the new database. If you want to change the number or the size of your InnoDB log files, -you have to shut down MySQL and make sure that it shuts down without errors. +you have to shut down @strong{MySQL} and make sure that it shuts down without errors. Then copy the old log files into a safe place just in case something went wrong in the shutdown and you will need them to recover the database. Delete then the old log files from the log file directory, -edit @file{my.cnf}, and start MySQL again. InnoDB will tell +edit @file{my.cnf}, and start @strong{MySQL} again. InnoDB will tell you at the startup that it is creating new log files. -@node Backing up +@node Backing up, Moving, Adding and removing, InnoDB @subsection Backing up and recovering an InnoDB database The key to safe database management is taking regular backups. @@ -24765,7 +24878,7 @@ To take a 'binary' backup of your database you have to do the following: @itemize @bullet @item -Shut down your MySQL database and make sure it shuts down without errors. +Shut down your @strong{MySQL} database and make sure it shuts down without errors. @item Copy all your data files into a safe place. @item @@ -24796,12 +24909,12 @@ dumps. Then you can take the binary backup, and you will then have a consistent snapshot of your database in two formats. To be able to recover your InnoDB database to the present from the -binary backup described above, you have to run your MySQL database -with the general logging and log archiving of MySQL switched on. Here -by the general logging we mean the logging mechanism of the MySQL server +binary backup described above, you have to run your @strong{MySQL} database +with the general logging and log archiving of @strong{MySQL} switched on. Here +by the general logging we mean the logging mechanism of the @strong{MySQL} server which is independent of InnoDB logs. -To recover from a crash of your MySQL server process, the only thing +To recover from a crash of your @strong{MySQL} server process, the only thing you have to do is to restart it. InnoDB will automatically check the logs and perform a roll-forward of the database to the present. InnoDB will automatically roll back uncommitted transactions which were @@ -24836,7 +24949,7 @@ mysqld: ready for connections If your database gets corrupted or your disk fails, you have to do the recovery from a backup. In the case of corruption, you should first find a backup which is not corrupted. From a backup do the recovery -from the general log files of MySQL according to instructions in the +from the general log files of @strong{MySQL} according to instructions in the MySQL manual. @subsubsection Checkpoints @@ -24869,7 +24982,7 @@ the total size of the log files as big as the buffer pool or even bigger. The drawback in big log files is that crash recovery can last longer because there will be more log to apply to the database. -@node Moving +@node Moving, InnoDB transaction model, Backing up, InnoDB @subsection Moving an InnoDB database to another machine InnoDB data and log files are binary-compatible on all platforms @@ -24889,7 +25002,7 @@ the big rollback segment the big import transaction will generate. Do the commit only after importing a whole table or a segment of a table. -@node InnoDB transaction model +@node InnoDB transaction model, Implementation, Moving, InnoDB @subsection InnoDB transaction model In the InnoDB transaction model the goal has been to combine the best @@ -24902,7 +25015,7 @@ to lock every row in the database, or any random subset of the rows, without InnoDB running out of memory. In InnoDB all user activity happens inside transactions. If the -auto commit mode is used in MySQL, then each SQL statement +auto commit mode is used in @strong{MySQL}, then each SQL statement will form a single transaction. If the auto commit mode is switched off, then we can think that a user always has a transaction open. If he issues @@ -24953,10 +25066,10 @@ happen that meanwhile some other user has deleted the parent row from the table @code{PARENT}, and you are not aware of that. The solution is to perform the @code{SELECT} in a locking -mode, @code{IN SHARE MODE}. +mode, @code{LOCK IN SHARE MODE}. @example -SELECT * FROM PARENT WHERE NAME = 'Jones' IN SHARE MODE; +SELECT * FROM PARENT WHERE NAME = 'Jones' LOCK IN SHARE MODE; @end example Performing a read in share mode means that we read the latest @@ -25055,7 +25168,7 @@ table. @code{SELECT ... FROM ...} : this is a consistent read, reading a snapshot of the database and setting no locks. @item -@code{SELECT ... FROM ... IN SHARE MODE} : sets shared next-key locks +@code{SELECT ... FROM ... LOCK IN SHARE MODE} : sets shared next-key locks on all index records the read encounters. @item @code{SELECT ... FROM ... FOR UPDATE} : sets exclusive next-key locks @@ -25070,7 +25183,7 @@ on the duplicate index record. @code{INSERT INTO T SELECT ... FROM S WHERE ...} sets an exclusive (non-next-key) lock on each row inserted into @code{T}. Does the search on @code{S} as a consistent read, but sets shared next-key -locks on @code{S} if the MySQL logging is on. InnoDB has to set +locks on @code{S} if the @strong{MySQL} logging is on. InnoDB has to set locks in the latter case because in roll-forward recovery from a backup every SQL statement has to be executed in exactly the same way as it was done originally. @@ -25090,10 +25203,10 @@ lock on every record the search encounters. lock on every record the search encounters. @item @code{LOCK TABLES ... } : sets table locks. In the implementation -the MySQL layer of code sets these locks. The automatic deadlock detection +the @strong{MySQL} layer of code sets these locks. The automatic deadlock detection of InnoDB cannot detect deadlocks where such table locks are involved: see the next section below. See also section 13 'InnoDB restrictions' -about the following: since MySQL does know about row level locks, +about the following: since @strong{MySQL} does know about row level locks, it is possible that you get a table lock on a table where another user currently has row level locks. But that does not put transaction integerity into danger. @@ -25104,7 +25217,7 @@ locks. But that does not put transaction integerity into danger. InnoDB automatically detects a deadlock of transactions and rolls back the transaction whose lock request was the last one to build a deadlock, that is, a cycle in the waits-for graph of transactions. -InnoDB cannot detect deadlocks where a lock set by a MySQL +InnoDB cannot detect deadlocks where a lock set by a @strong{MySQL} @code{LOCK TABLES} statement is involved, or if a lock set in another table handler than InnoDB is involved. You have to resolve these situations using @code{innodb_lock_wait_timeout} set in @@ -25117,7 +25230,7 @@ set by the SQL statement may be preserved. This is because InnoDB stores row locks in a format where it cannot afterwards know which was set by which SQL statement. -@node Implementation +@node Implementation, Table and index, InnoDB transaction model, InnoDB @subsection Implementation of multiversioning Since InnoDB is a multiversioned database, it must keep information @@ -25166,7 +25279,7 @@ its index records from the database. This removal operation is called a purge, and it is quite fast, usually taking the same order of time as the SQL statement which did the deletion. -@node Table and index +@node Table and index, File space management, Implementation, InnoDB @subsection Table and index structures Every InnoDB table has a special index called the clustered index @@ -25275,11 +25388,11 @@ Each secondary index record contains also all the fields defined for the clustered index key. @item A record contains also a pointer to each field of the record. -If the total length of the fields in a record is < 256 bytes, then +If the total length of the fields in a record is < 128 bytes, then the pointer is 1 byte, else 2 bytes. @end itemize -@node File space management +@node File space management, Error handling, Table and index, InnoDB @subsection File space management and disk i/o @subsubsection Disk i/o @@ -25358,7 +25471,7 @@ but remember that deleted rows can be physically removed only in a purge operation after they are no longer needed in transaction rollback or consistent read. -@node Error handling +@node Error handling, InnoDB restrictions, File space management, InnoDB @subsection Error handling The error handling in InnoDB is not always the same as @@ -25371,7 +25484,7 @@ The following list specifies the error handling of InnoDB. @itemize @bullet @item If you run out of file space in the tablespace, -you will get the MySQL @code{'Table is full'} error +you will get the @strong{MySQL} @code{'Table is full'} error and InnoDB rolls back the SQL statement. @item A transaction deadlock or a timeout in a lock wait will give @@ -25386,7 +25499,7 @@ statement. @item A 'row too long' error rolls back the SQL statement. @item -Other errors are mostly detected by the MySQL layer of code, and +Other errors are mostly detected by the @strong{MySQL} layer of code, and they roll back the corresponding SQL statement. @end itemize @@ -25394,19 +25507,20 @@ they roll back the corresponding SQL statement. @subsection Some restrictions on InnoDB tables @itemize @bullet -@item You cannot create an index on a prefix of a column: +@item +If you try to create an unique index on a prefix of a column you will get an +error: @example -@code{CREATE TABLE T (A CHAR(20), B INT, INDEX T_IND (A(5))) TYPE = InnoDB; -} +CREATE TABLE T (A CHAR(20), B INT, UNIQUE (A(5))) TYPE = InnoDB; @end example -The above will not work. For a MyISAM table the above would create an index -where only the first 5 characters from column @code{A} are stored. +If you create a non unique index on a prefix of a column, InnoDB will +create an index over the whole column. @item @code{INSERT DELAYED} is not supported for InnoDB tables. @item -The MySQL @code{LOCK TABLES} operation does not know of InnoDB +The @strong{MySQL} @code{LOCK TABLES} operation does not know of InnoDB row level locks set in already completed SQL statements: this means that you can get a table lock on a table even if there still exist transactions of other users which have row level locks on the same table. Thus @@ -25423,7 +25537,7 @@ A table cannot contain more than 1000 columns. @item @code{DELETE FROM TABLE} does not regenerate the table but instead deletes all rows, one by one, which is not that fast. In future versions -of MySQL you can use @code{TRUNCATE} which is fast. +of @strong{MySQL} you can use @code{TRUNCATE} which is fast. @item Before dropping a database with InnoDB tables one has to drop the individual InnoDB tables first. @@ -25444,7 +25558,7 @@ The maximum tablespace size is 4 billion database pages. This is also the maximum size for a table. @end itemize -@node InnoDB contact information, , InnoDB restrictions, InnoDB +@node InnoDB contact information, , InnoDB restrictions, InnoDB @subsection InnoDB contact information Contact information of Innobase Oy, producer of the InnoDB engine: @@ -29273,7 +29387,7 @@ have been assigned a low semantical value in @strong{a particular dataset}. * Fulltext TODO:: @end menu -@node Fulltext Fine-tuning, Fulltext Features to Appear in MySQL 4.0, , Fulltext Search +@node Fulltext Fine-tuning, Fulltext Features to Appear in MySQL 4.0, Fulltext Search, Fulltext Search @section Fine-tuning MySQL Full-text Search Unfortunately, full-text search has no user-tunable parameters yet, @@ -31501,7 +31615,7 @@ We can find the result from crash-me on a lot of different databases at @menu * Programs:: What do the executables do? -* mysqld-max:: +* mysqld-max:: mysqld-max, An extended mysqld server * safe_mysqld:: safe_mysqld, the wrapper around mysqld * mysqld_multi:: Program for managing multiple @strong{MySQL} servers * mysql:: The command line tool @@ -31652,12 +31766,47 @@ the following configure options: @multitable @columnfractions .3 .7 @item @strong{Option} @tab @strong{Comment} -@item --with-server-suffix=-max @tab Add a suffix to the @code{mysqld} version string. +@item --with-server-suffix=-Max @tab Add a suffix to the @code{mysqld} version string. @item --with-bdb @tab Support for Berkeley DB (BDB) tables @item --with-innodb @tab Support for InnoDB tables. @item CFLAGS=-DUSE_SYMDIR @tab Symbolic links support for Windows. @end multitable +Note that as Berkeley DB and InnoDB are not available for all platforms, +some of the @code{Max} binaries may not have support for both of these. +You can check which table types are supported by doing the following +query: + +@example +mysql> show variables like "have_%"; ++---------------+-------+ +| Variable_name | Value | ++---------------+-------+ +| have_bdb | YES | +| have_gemini | NO | +| have_innodb | NO | +| have_isam | YES | +| have_raid | YES | +| have_ssl | NO | ++---------------+-------+ +@end example + +The meaning of the values are: + +@multitable @columnfractions .3 .7 +@item @strong{Value} @tab @strong{Meaning}. +@item YES @tab The option is activated and usable. +@item NO @tab @strong{MySQL} is not compiled with support for this option. +@item DISABLED @tab The xxxx option is disabled because one started @code{mysqld} with @code{--skip-xxxx} or because one didn't start @code{mysqld} with all needed options to enable the option. In this case the @code{hostname.err} file should contain a reason for why the option is disabled. +@end multitable + +@strong{NOTE}: To be able to create InnoDB tables you @strong{MUST} edit +your startup options to include at least the @code{innodb_data_file_path} +option. @xref{InnoDB start}. + +To get better performance for BDB tables, you should add some configuration +options for these too. @xref{BDB start}. + @code{safe_mysqld} will automaticly try to start any @code{mysqld} binary with the @code{-max} prefix. This makes it very easy to test out a another @code{mysqld} binary in an existing installation. Just @@ -31665,9 +31814,26 @@ run @code{configure} with the options you want and then install the new @code{mysqld} binary as @code{mysqld-max} in the same directory where your old @code{mysqld} binary is. @xref{safe_mysqld}. -The @code{mysqld-max} RPM uses this @code{safe_mysqld} feature. It just -installs the @code{mysqld-max} executable and @code{safe_mysqld} will -automaticly use this when @code{mysqld} is restarted. +The @code{mysqld-max} RPM uses the above mentioned @code{safe_mysqld} +feature. It just installs the @code{mysqld-max} executable and +@code{safe_mysqld} will automaticly use this executable when +@code{safe_mysqld} is restarted. + +The following table shows which table types our standard @strong{MySQL-Max} +binaries includes: + +@multitable @columnfractions .4 .3 .3 +@item @strong{System} @tab @strong{BDB} @tab @strong{InnoDB} +@item AIX 4.3 @tab N @tab Y +@item HPUX 11.0 @tab N @tab Y +@item Linux-Alpha @tab N @tab Y +@item Linux-Intel @tab Y @tab Y +@item Linux-Ia64 @tab N @tab Y +@item Solaris-intel @tab N @tab Y +@item Solaris-sparc @tab Y @tab Y +@item SCO OSR5 @tab Y @tab Y +@item UnixWare @tab Y @tab Y +@end multitable @cindex tools, safe_mysqld @cindex scripts @@ -32194,9 +32360,9 @@ the @code{mysql} variables that affect your queries. @cindex @code{safe-mode} command A useful startup option for beginners (introduced in @strong{MySQL} -Version 3.23.11) is @code{--safe-mode} (or @code{--i-am-a-dummy} for +Version 3.23.11) is @code{--safe-updates} (or @code{--i-am-a-dummy} for users that has at some time done a @code{DELETE FROM table_name} but -forgot the @code{WHERE} clause. When using this option, @code{mysql} +forgot the @code{WHERE} clause). When using this option, @code{mysql} sends the following command to the @strong{MySQL} server when opening the connection: @@ -32521,6 +32687,10 @@ used.) @item -q, --quick Don't buffer query, dump directly to stdout. Uses @code{mysql_use_result()} to do this. +@item -r, --result-file=... +Direct output to a given file. This option should be used in MSDOS, +because it prevents new line '\n' from being converted to '\n\r' (new +line + carriage return). @item -S /path/to/socket, --socket=/path/to/socket The socket file to use when connecting to @code{localhost} (which is the default host). @@ -34198,9 +34368,8 @@ 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: +To get more information about the error you can run @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 @@ -34218,22 +34387,13 @@ shell> perror 126 127 132 134 135 136 141 144 145 Note that error 135, no more room in record file, is not an 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 +@example +ALTER TABLE table MAX_ROWS=xxx AVG_ROW_LENGTH=yyy; +@end example + @end itemize -In these cases, you must repair your tables. @code{myisamchk} +In the other 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 @@ -34243,12 +34403,12 @@ 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}. +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}. +@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. @@ -34510,12 +34670,13 @@ 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}). +many platforms (including Linux) 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 @@ -34932,12 +35093,13 @@ one that has been loaded with @code{CREATE FUNCTION} and not removed with @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. +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: @@ -35608,6 +35770,8 @@ INSERT INTO foo (auto,text) VALUES(NULL,'text'); INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text'); @end example +@xref{Getting unique ID}. + 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 @@ -35768,6 +35932,7 @@ pre-allocated MYSQL struct. * 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 +* Temporary table problems:: @end menu This chapter lists some common problems and error messages that users have @@ -36165,6 +36330,10 @@ 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. +Another common reason to receive the @code{MySQL server has gone away} error +is because you have issued a ``close'' on your @strong{MySQL} connection +and then tried to run a query on the closed connection. + You can check that the @strong{MySQL} hasn't died by executing @code{mysqladmin version} and examining the uptime. @@ -36697,6 +36866,17 @@ thread that is waiting on the disk-full condition will allow the other threads to continue. @end itemize +Exceptions to the above behaveour is when you use @code{REPAIR} or +@code{OPTIMIZE} or when the indexes are created in a batch after an +@code{LOAD DATA INFILE} or after an @code{ALTER TABLE} statement. + +All of the above commands may use big temporary files that left to +themself would cause big problems for the rest of the system. If +@strong{MySQL} gets disk full while doing any of the above operations, +it will remove the big temporary files and mark the table as crashed +(except for @code{ALTER TABLE}, in which the old table will be left +unchanged). + @node Multiple sql commands, Temporary files, Full disk, Problems @section How to Run SQL Commands from a Text File @@ -37314,7 +37494,7 @@ simple rename should get your data back. @cindex columns, changing @cindex changing, column order @cindex tables, changing column order -@node Change column order, , ALTER TABLE problems, Problems +@node Change column order, Temporary table problems, ALTER TABLE problems, Problems @section How To Change the Order of Columns in a Table The whole point of SQL is to abstract the application from the data @@ -37353,6 +37533,32 @@ Drop or rename @code{old_table}. @code{ALTER TABLE new_table RENAME old_table}. @end enumerate +@cindex temporary tables, problems +@node Temporary table problems, , Change column order, Problems +@section TEMPORARY TABLE problems + +The following are a list of the limitations with @code{TEMPORARY TABLES}. + +@itemize @bullet +@item +A temporary table can only be of type @code{HEAP}, @code{ISAM} or +@code{MyISAM}. +@item +You can't use temporary tables more than once in the same query. +For example, the following doesn't work. + +@example +select * from temporary_table, temporary_table as t2; +@end example + +We plan to fix the above in 4.0. +@item +You can't use @code{RENAME} on a @code{TEMPORARY} table. +Note that @code{ALTER TABLE org_name RENAME new_name} works! + +We plan to fix the above in 4.0. +@end itemize + @cindex problems, solving @cindex solving, problems @cindex databases, replicating @@ -41867,7 +42073,7 @@ This is a relatively low traffic list, in comparison with * MySQL test suite:: MySQL test suite @end menu -@node MySQL threads, MySQL test suite, , MySQL internals +@node MySQL threads, MySQL test suite, MySQL internals, MySQL internals @section MySQL Threads The @strong{MySQL} server creates the following threads: @@ -42218,7 +42424,6 @@ more than one way to compute} @item @uref{http://www.yaboo.dk/, Yaboo - Yet Another BOOkmarker} -@item @uref{http://www.yahoosuck.com, Yahoosuck} @item @uref{http://www.ozsearch.com.au, OzSearch Internet Guide} @@ -42231,7 +42436,7 @@ more than one way to compute} @itemize @bullet -@item @uref{http:www.spylog.ru/, SpyLOG ; A very popular Web counter site} +@item @uref{http://www.spylog.ru/, SpyLOG ; A very popular Web counter site} @item @uref{http://www.tucows.com/, TuCows Network; Free Software archive} @@ -42253,8 +42458,6 @@ more than one way to compute} @item @uref{http://www.game-developer.com/,The Game Development Search Engine} -@item @uref{http://www.i-run.com/html/cookbook.html,My-Recipe.com; Cookbook at i-run.com} - @item @uref{www.theinnkeeper.com, The Innkeeper Vacation Guides} @item @uref{http://www.macgamedatabase.com/, The Mac Game Database uses PHP and MySQL} @@ -42386,7 +42589,7 @@ the @strong{MySQL} database @itemize @bullet @c @item @uref{http://www.wh200th.com, White House 200th Anniversary site} -@item @uref{http://war.jgaa.com:8080/support/index.php3, Jgaa's Internet - Official Support Site} +@item @uref{http://support.jgaa.com/, Jgaa's Internet - Official Support Site} @item @uref{http://io.incluso.com, Ionline - online publication:} @strong{MySQL}, PHP, Java, Web programming, DB development @@ -42450,10 +42653,6 @@ Ecommerce site that is selling computers. @appendixsec Programming -@itemize @bullet -@item @uref{http://www.perl.org/cpan-testers, The Perl CPAN Testers results page} -@end itemize - @cindex web pages, miscellaneous @appendixsec Uncategorized Pages @@ -42873,6 +43072,15 @@ of several databases simultaneously. By Innovative-IT Development AB. @item @uref{http://www.mysql.com/downloads/gui-clients.html, MySQLGUI} The @strong{MySQL} GUI client homepage. By Sinisa at @strong{MySQL AB}. +@item @uref{http://www.mysql.com/Downloads/Contrib/mysql_navigator_0.9.0.tar.gz, MySQL navigator 0.9} +MySQL Navigator is MySQL database server GUI client program. The purpose +of MySQL Navigator is to provide a useful client interface to MySQL +database servers, whilst supporting multiple operating systems and +languages. You can currently import/export database, enter queries, get +result sets, edit scripts, run scripts, add, alter, and delete users, +and retrieve client and server information. Uses QT 2.2. GPL +@uref{http://sql.kldp.org/mysql, Home page for MySQL Navigator}. + @item @uref{http://www.mysql.com/Downloads/Win32/secman.zip, MySQL Security GUI} A user and security management GUI for @strong{MySQL} on Windows. By Martin Jeremic. @@ -42923,6 +43131,8 @@ import-/export-files. (Freeware). By Ansgar Becker. @item @uref{http://www.mysql.com/Downloads/Win32/W9xstop.zip,Utility from Artronic to stop MySQL on win9x}. +@item @uref{http://bardo.hyperlink.cz/mysqlmon,a light weight GUI client for Windows}. + @item @uref{http://dbtools.vila.bol.com.br/, Dbtools} A tool to manage @strong{MySQL} databases. Currently only for Windows. Some features: @@ -42951,8 +43161,8 @@ An open source client for exploring databases and executing SQL. Supports A query tool for @strong{MySQL} and PostgreSQL. @item @uref{http://dbman.linux.cz/,dbMan} A query tool written in Perl. Uses DBI and Tk. -@item @uref{http://www.mysql.com/Downloads/Win32/Msc201.EXE, Mascon 2.1.15} -@item @uref{http://www.mysql.com/Downloads/Win32/FrMsc201.EXE, Free Mascon 2.1.14} +@item @uref{http://www.mysql.com/Downloads/Win32/Msc201.EXE, Mascon 202} +@item @uref{http://www.mysql.com/Downloads/Win32/FrMsc202.EXE, Free Mascon 202} Mascon is a powerful Win32 GUI for the administering @strong{MySQL} server databases. Mascon's features include visual table design, connections to multiple servers, data and blob editing of tables, security setting, SQL @@ -43041,6 +43251,10 @@ Apache module to include HTML from @strong{MySQL} queries into your pages, and run update queries. Originally written to implement a simple fast low-overhead banner-rotation system. By Sasha Pachev. +@item @uref{http://htcheck.sourceforge.net, htCheck} - URL checker with +MySQL backend. Spidered URLs can later be queried using SQL to retrieve +various kinds of information, eg. broken links. Written by Gabriele Bartolini. + @item @uref{http://www.odbsoft.com/cook/sources.htm} This package has various functions for generating html code from a SQL table structure and for generating SQL statements (Select, Insert, @@ -43161,7 +43375,7 @@ An authentication module for the Cyrus IMAP server. By Aaron Newsome. @appendixsec Converters @itemize @bullet -item @uref{http://www.mysql.com/Downloads/Contrib/mssql2mysql.txt, mssql2mysql.txt} +@item @uref{http://www.mysql.com/Downloads/Contrib/mssql2mysql.txt, mssql2mysql.txt} Converter from MS-SQL to MySQL. By Michael Kofler. @uref{http://www.kofler.cc/mysql/mssql2mysql.html, mssql2mysql home page}. @@ -43881,6 +44095,9 @@ Allow @code{SELECT expression LIMIT ...}. Added @code{IDENTITY} as a synonym for @code{AUTO_INCREMENT} (like Sybase). @item Added @code{ORDER BY} syntax to @code{UPDATE} and @code{DELETE}. +@item +Optimized queries of type: +@code{SELECT DISTINCT * from table_name ORDER by key_part1 LIMIT #} @end itemize @node News-3.23.x, News-3.22.x, News-4.0.x, News @@ -43896,7 +44113,7 @@ A new ISAM library which is tuned for SQL and supports large files. @item @strong{BerkeleyDB} or @strong{BDB} Uses the Berkeley DB library from Sleepycat Software to implement transaction-safe tables. -@item @strong{Innodb} +@item @strong{InnoDB} A transaction-safe table handler that supports row level locking, and many Oracle-like features. @c change "three" to "four" above when uncommenting this @@ -43927,6 +44144,7 @@ users uses this code as the rest of the code and because of this we are not yet 100% confident in this code. @menu +* News-3.23.38:: Changes in release 3.23.38 * News-3.23.37:: Changes in release 3.23.37 * News-3.23.36:: Changes in release 3.23.36 * News-3.23.35:: Changes in release 3.23.35 @@ -43968,12 +44186,58 @@ not yet 100% confident in this code. * News-3.23.0:: Changes in release 3.23.0 @end menu -@node News-3.23.37, News-3.23.36, News-3.23.x, News-3.23.x +@node News-3.23.38, News-3.23.37, News-3.23.x, News-3.23.x +@appendixsubsec Changes in release 3.23.38 +@itemize @bullet +@item +Fixed a bug in @code{REPLACE()} when using the ujis character set. +@item +Applied Sleepycat BDB patches 3.2.9.1 and 3.2.9.2. +@item +Added option @code{--skip-stack-trace} to @code{mysqld}. +@item +@code{CREATE TEMPORARY} now works with @code{InnoDB} tables. +@item +@code{InnoDB} now promotes sub keys to whole keys. +@item +Added option @code{CONCURRENT} to @code{LOAD DATA}. +@item +Better error message when slave @code{max_allowed_packet} is to low to +read a very long log event from the master +@item +Fixed bug when too many rows where removed when using +@code{SELECT DISTINCT ... HAVING}. +@item +@code{SHOW CREATE TABLE} now returns @code{TEMPORARY} for temporary tables. +@item +Added @code{Rows_examined} to slow query log. +@item +Fixed problems with function returning empty string when using +together with a group functions and a @code{WHERE} that didn't match any rows. +@item +New program @code{mysqlcheck}. +@item +Added database name to output for administrative commands like @code{CHECK}, +@code{REPAIR}, @code{OPTIMIZE}. +@item +Lots of portability fixes for InnoDB. +@item +Changed optimizer so that queries like +@code{SELECT * FROM table_name,table_name2 ... ORDER BY key_part1 LIMIT #} +will use index on @code{key_part1} instead of @code{filesort}. +@item +Fixed bug when doing +@code{LOCK TABLE to_table WRITE,...; INSERT INTO to_table... SELECT ...} +when @code{to_table} was empty. +@item +Fixed bug with @code{LOCK TABLE} and BDB tables. +@end itemize + +@node News-3.23.37, News-3.23.36, News-3.23.38, News-3.23.x @appendixsubsec Changes in release 3.23.37 @itemize @bullet @item -Added variables @code{ft_min_word_len}, @code{ft_max_word_len}, and -@code{ft_max_word_len_for_sort}. +Fixed a bug when using @code{MATCH} in @code{HAVING} clause. @item Fixed a bug when using @code{HEAP} tables with @code{LIKE}. @item @@ -43990,35 +44254,35 @@ Fixed bug when using indexes on @code{CHAR(255) NULL} columns. Slave thread will now be started even if @code{master-host} is not set, as long as @code{server-id} is set and valid @code{master.info} is present @item -Partial updates ( terminated with kill) are now logged with a special error +Partial updates (terminated with kill) are now logged with a special error code to the binary log. Slave will refuse to execute them if the error code indicates the update was terminated abnormally, and will have to be recovered with @code{SET SQL_SLAVE_SKIP_COUNTER=1; SLAVE START} after a manual sanity -check/correction of data integrity +check/correction of data integrity. @item Fixed bug that erroneously logged a drop of internal temporary table - on thread termination to the binary log - bug affected replication +on thread termination to the binary log - bug affected replication. @item Fixed a bug in @code{REGEXP()} on 64-bit machines. @item @code{UPDATE} and @code{DELETE} with @code{WHERE unique_key_part IS NULL} didn't update/delete all rows. @item -Disabled @code{INSERT DELAYED} for tables that supports transactions. +Disabled @code{INSERT DELAYED} for tables that support transactions. @item Fixed bug when using date functions on @code{TEXT}/@code{BLOB} column with wrong date format. @item -UDF's now also works on windows. (Patch by Ralph Mason) +UDFs now also work on Windows. (Patch by Ralph Mason) @item Fixed bug in @code{ALTER TABLE} and @code{LOAD DATA INFILE} that disabled -key-sorting. These command should now be faster in most cases. +key-sorting. These commands should now be faster in most cases. @item Fixed performance bug where reopened tables (tables that had been waiting for @code{FLUSH} or @code{REPAIR}) would not use indexes for the next query. @item -Fixed problem with @code{ALTER TABLE} to Innobase tables on Freebsd. +Fixed problem with @code{ALTER TABLE} to Innobase tables on FreeBSD. @item Added @code{mysqld} variables @code{myisam_max_sort_file_size} and @code{myisam_max_extra_sort_file_size}. @@ -44037,8 +44301,9 @@ Added @code{--skip-safemalloc} option to @code{mysqld}. @appendixsubsec Changes in release 3.23.36 @itemize @bullet @item -Fixed a bug that allowed you to use database names with @code{.}. This -fixes a serious security issue when @code{mysqld} is run as root. +Fixed a bug that allowed you to use database names containing a @samp{.} +character. This fixes a serious security issue when @code{mysqld} is run +as root. @item Fixed bug when thread creation failed (could happen when doing a LOT of connections in a short time). @@ -48994,12 +49259,16 @@ mysql> UPDATE tbl_name SET KEY=KEY+1,KEY=KEY+1; will update @code{KEY} with @code{2} instead of with @code{1}. @item You can't use temporary tables more than once in the same query. +For example, the following doesn't work. @example select * from temporary_table, temporary_table as t2; @end example @item +@code{RENAME} doesn't work with @code{TEMPORARY} tables. + +@item The optimizer may handle @code{DISTINCT} differently if you are using 'hidden' columns in a join or not. In a join, hidden columns are counted as part of the result (even if they are not shown) while in @@ -49227,6 +49496,8 @@ Add @code{record_in_range()} method to @code{MERGE} tables to be able to choose the right index when there is many to choose from. We should also extend the info interface to get the key distribution for each index, of @code{analyze} is run on all sub tables. +@item +@code{SET SQL_DEFAULT_TABLE_TYPE=[MyISAM | INNODB | BDB | GEMINI | HEAP]}. @end itemize @node TODO future, TODO sometime, TODO MySQL 4.0, TODO @@ -49239,6 +49510,25 @@ Fail safe replication. Subqueries. @code{select id from t where grp in (select grp from g where u > 100)} @item +Derieved tables. +@example +select a.col1, b.col2 from (select max(col1) as col1 from root_table ) a, +other_table b where a.col1=b.col1 +@end example + +This could be done by automaticly create temporary tables for the +derived tables for the duration of the query. +@item +Add @code{PREPARE} of statements and sending of parameters to @code{mysqld}. +@item +Extend the server/client protocol to support warnings. +@item +Add options to the server/protocol protocol to get progress notes +for long running commands. +@item +Add database and real table name (in case of alias) to the MYSQL_FIELD +structure. +@item Don't allow more than a defined number of threads to run MyISAM recover at the same time. @item @@ -49765,6 +50055,9 @@ With some older @code{gdb} versions on Linux you must use @code{run --one-thread} if you want to be able to debug @code{mysqld} threads. In this case you can only have one thread active at a time. +When running @code{mysqld} under gdb, you should disable the stack trace +with @code{--skip-stack-trace} to be able to catch segfaults within gdb. + It's very hard to debug @strong{MySQL} under @code{gdb} if you do a lot of new connections the whole time as @code{gdb} doesn't free the memory for old threads. You can avoid this problem by starting @code{mysqld} with |