diff options
49 files changed, 1040 insertions, 681 deletions
diff --git a/BUILD/compile-alpha-cxx b/BUILD/compile-alpha-cxx index 7f982652f6c..82a9ee87cd1 100755 --- a/BUILD/compile-alpha-cxx +++ b/BUILD/compile-alpha-cxx @@ -4,7 +4,8 @@ make -k clean /bin/rm -f config.cache mysql-*.tar.gz aclocal; autoheader; aclocal; automake; autoconf -CC=ccc CFLAGS="-fast" CXX=cxx CXXFLAGS="-fast -noexceptions -nortti" ./configure --prefix=/usr/local/mysql --disable-shared --with-extra-charsets=complex --enable-thread-safe-client --with-mysqld-ldflags=-all-static +CC=ccc CFLAGS="-fast" CXX=cxx CXXFLAGS="-fast -noexceptions -nortti" ./configure --prefix=/usr/local/mysql --disable-shared --with-extra-charsets=complex --enable-thread-safe-client --with-mysqld-ldflags=-non_shared --with-client-ldflags=-non_shared + make rm */.deps/* make diff --git a/BitKeeper/etc/logging_ok b/BitKeeper/etc/logging_ok index e9b9b808409..437d68d72f8 100644 --- a/BitKeeper/etc/logging_ok +++ b/BitKeeper/etc/logging_ok @@ -1,7 +1,14 @@ jani@prima.mysql.com +jcole@tetra.bedford.progress.com +jcole@tetra.spaceapes.com monty@donna.mysql.com monty@narttu.mysql.fi +mwagner@evoq.home.mwagner.org mwagner@work.mysql.com +sasha@laptop.slkc.uswest.net sasha@mysql.sashanet.com +sasha@work.mysql.com serg@serg.mysql.com +tim@threads.polyesthetic.msg +tim@work.mysql.com yfaktoro@nslinuxw2.bedford.progress.com diff --git a/Docs/manual.texi b/Docs/manual.texi index e780fbd1678..4a797e159f6 100644 --- a/Docs/manual.texi +++ b/Docs/manual.texi @@ -148,7 +148,8 @@ version see the relevant distribution. * Function Index:: SQL command, type and function index * Concept Index:: Concept Index -@detailmenu --- The Detailed Node Listing --- +@detailmenu + --- The Detailed Node Listing --- General Information About MySQL @@ -482,6 +483,15 @@ MyISAM table formats * Dynamic format:: Dynamic table characteristics * Compressed format:: Compressed table characteristics +BDB or Berkeley_db tables + +* BDB overview:: +* BDB install:: +* BDB start:: +* BDB characteristic:: +* BDB TODO:: +* BDB errors:: + MySQL Tutorial * Connecting-disconnecting:: Connecting to and disconnecting from the server @@ -806,6 +816,11 @@ MySQL internals * MySQL threads:: MySQL threads * MySQL full-text search:: MySQL full-text search +Credits + +* developers:: +* Contributors:: + MySQL change history * News-3.23.x:: Changes in release 3.23.x (Recommended; beta) @@ -1287,7 +1302,7 @@ mysql> SELECT author_name FROM biblio_db.author_list; @end example SQL statements may be written in uppercase or lowercase. When this manual -shows an SQL statement, uppercase is used for particular keywords if those +shows a SQL statement, uppercase is used for particular keywords if those keywords are under discussion (to emphasize them) and lowercase is used for the rest of the statement. For example, you might see the following in a discussion of the @code{SELECT} statement: @@ -1972,6 +1987,11 @@ Apart from the following links, you can find and download a lot of @c @item @uref{http://www.devshed.com/resource/advanced/mysql/index.html, A @c beginner's tutorial of how to start using @strong{MySQL}} +@item @uref{http://www.4t2.com/mysql. German MySQL maling list} +Information about the German MySQL mailing list. + +@item @uref{http://www.bitmover.com:8888//home/bk/mysql, MySQL bitkeeper repository} + @item @uref{http://www.analysisandsolutions.com/code/mybasic.htm}@* Beginners @strong{MySQL} Tutorial on how to install and set up @strong{MySQL} on a Windows machine. @@ -2069,7 +2089,6 @@ organization. @item @uref{http://www.xnot.com/kmysql}@* KDE @strong{MySQL} client. - @c EMAIL: bilhaut_f@mail.cpod.fr (Frédérik Bilhaut) @item @uref{http://www.penguinpowered.com/~kmysql}@* KMySQL is a database client for KDE that primarily supports @strong{MySQL}. @@ -2090,6 +2109,14 @@ A free report writer in Java @item @uref{http://www.javaframework.de}@* MySQLExport - Export of @strong{MySQL} create statements and data in a lot of different formats (SQL, HTML, CVS, text, ZIP, GZIP...) + +@item @uref{http://dlabs.4t2.com, M2D} +A MySQL-ADmin-client for windows. It supports administration of +MySQL-Databases, creating of new DBs and tables, editing etc. + +@item @uref{http://www.scibit.com/Products/Software/Utils/Mascon.asp, +Mascon home page}. +Mascon is a powerful Win32 GUI for the administering MySQL server databases. @end itemize @subheading Distributions that Include MySQL @@ -2399,7 +2426,7 @@ A Contact Database using @strong{MySQL} and PHP. Web based interface and Community Calender with PHP. @item @uref{http://www.odbsoft.com/cook/sources.htm}@* -Perl package to generate html from an SQL table structure and for generating +Perl package to generate html from a SQL table structure and for generating SQL statements from an html form. @item @uref{http://www.gusnet.cx/proj/telsql/}@* @@ -2629,6 +2656,9 @@ 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}. + @cindex net etiquette @cindex mailing lists, archive location @cindex searching, MySQL webpages @@ -4503,7 +4533,7 @@ to get even more flexibility). @item If you want to configure @code{mysqld} with some extra feature that is NOT in -the standard binary distributions. Here follows a list of the most common +the standard binary distributions. Here is a list of the most common extra options that you may want to use @itemize @bullet @@ -6896,59 +6926,40 @@ We have tested @strong{MySQL} on Alpha with our benchmarks + test suite and it appears to work nicely. The main thing we haven't yet had time to test is how things works with many concurrent users. -When we compiled @strong{MySQL} we where using SuSE 6.3, kernel 2.2.13-SMP, -egcs 1.1.2, and libc-2.1.2-28. +When we compiled the standard @strong{MySQL} binary we are using SuSE 6.4, +kernel 2.2.13-SMP, Compaq C compiler (V6.2-504) and Compaq C++ compiler +(V6.3-005) on a Comaq DS20 machine with an Alpha EV6 processor + +You can find the above compilers at +@uref{http://www.support.compaq.com/alpha-tools}). By using these compilers, +instead of gcc, we get about 9-14 % better performance with @strong{MySQL}. -We used the following configure line: +Note that the configure line optimized the binary for the current CPU; This +means you can only use our binary if you have an Alpha EV6 processor. +We also compile staticly to avoid library problems. @example -CFLAGS="-O6 -fomit-frame-pointer" CXX=gcc CXXFLAGS="-O6 -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --disable-shared +CC=ccc CFLAGS="-fast" CXX=cxx CXXFLAGS="-fast -noexceptions -nortti" ./configure --prefix=/usr/local/mysql --disable-shared --with-extra-charsets=complex --enable-thread-safe-client --with-mysqld-ldflags=-non_shared --with-client-ldflags=-non_shared @end example -If you have access to Compaq's C compiler, the following configure line -will give you about 9 % more performance: +If you want to use egcs the following configure line worked for us: + @example -CC=ccc CFLAGS="-fast -O3 -fomit-frame-pointer" CXX=gcc CXXFLAGS="-O6 -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mcpu=ev6 -Wa,-mev6" CXXLDFLAGS='/usr/lib/compaq/libots-2.2.7/libots.so /usr/lib/compaq/cpml-5.0.0/libcpml_ev6.a' ./configure --prefix=/usr/local/mysql --disable-shared +CFLAGS="-O6 -fomit-frame-pointer" CXX=gcc CXXFLAGS="-O6 -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --disable-shared @end example -Note that the last example assumes your are using an Alpha EV6 processor. - Some known problems when running @strong{MySQL} on Linux-Alpha: @itemize @bullet @item -If you create many files ( > 10000) in one directory (as done by the -@code{test-create} in the @strong{MySQL} benchmarks, mysqld may report -the error @code{Can't create/write to file '...' (Errcode: 12)}. This -is probably a bug in the Linux-Alpha kernel. -@item Debugging threaded applications like @strong{MySQL} will not work with @code{gdb 4.18}. You should download and use gdb 5.0 instead! @item -If you try linking mysqld staticly, the resulting image will core dump -at start. In other words, @strong{DON'T} use -@code{--with-mysqld-ldflags=-all-static} +If you try linking mysqld staticly when using gcc, the resulting image +will core dump at start. In other words, @strong{DON'T} use +@code{--with-mysqld-ldflags=-all-static} with gcc @end itemize -We don't yet know if the following old information is still relevant, so we -leave this here until we have had time to test Linux-Alpha properly. - -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 -configuring with: - -@example -shell> CFLAGS=-DDONT_USE_THR_ALARM \ - CXXFLAGS=-DDONT_USE_THR_ALARM \ - ./configure ... -@end example - -This doesn't affect the performance of @strong{MySQL}, but has the side -effect that you can't kill clients that are ``sleeping'' on a connection with -@code{mysqladmin kill} or @code{mysqladmin shutdown}. Instead, the client -will die when it issues its next command. - @node MKLinux, Qube2, Linux-Alpha, Linux @subsubsection MkLinux Notes @@ -9119,7 +9130,9 @@ Log updates to @code{file.#} where @code{#} is a unique number if not given. @xref{Update log}. @item --log-long-format -Log some extra information to update log. +Log some extra information to update log. If you are using +@code{--log-slow-queries} then queries that are not using indexes are logged +to the slow query log. @item --low-priority-updates Table-modifying operations (@code{INSERT}/@code{DELETE}/@code{UPDATE}) @@ -10419,7 +10432,7 @@ In @strong{MySQL} Version 3.23 you can, however, use: The following discussion only concerns you if you are running a @strong{MySQL} version earlier than Version 3.23: -If you have an SQL program in a text file that contains @samp{--} comments +If you have a SQL program in a text file that contains @samp{--} comments you should use: @example @@ -14545,7 +14558,7 @@ statement, @strong{MySQL} reports the table structure using the equivalent @node Functions, CREATE DATABASE, Column types, Reference @section Functions for Use in @code{SELECT} and @code{WHERE} Clauses -A @code{select_expression} or @code{where_definition} in an SQL statement +A @code{select_expression} or @code{where_definition} in a SQL statement can consist of any expression using the functions described below. An expression that contains @code{NULL} always produces a @code{NULL} value @@ -16826,7 +16839,7 @@ file. See @code{ENCRYPT()}. @item ENCRYPT(str[,salt]) Encrypt @code{str} using the UNIX @code{crypt()} system call. The @code{salt} argument should be a string with two characters. -(As of @strong{MySQL} 3.22.16, @code{salt} may be longer than two characters.): +(As of @strong{MySQL} Version 3.22.16, @code{salt} may be longer than two characters.): @example mysql> select ENCRYPT("hello"); @@ -18088,7 +18101,7 @@ normal check has succeeded!). @findex BACKUP TABLE @node BACKUP TABLE, RESTORE TABLE, CHECK TABLE, Reference -@section @code{BACKUP TABLE} syntax +@section @code{BACKUP TABLE} Syntax @example BACKUP TABLE tbl_name[,tbl_name...] TO '/path/to/backup/directory' @@ -18119,7 +18132,7 @@ version 3.23.25 and later. @findex RESTORE TABLE @node RESTORE TABLE, ANALYZE TABLE, BACKUP TABLE, Reference -@section @code{RESTORE TABLE} syntax +@section @code{RESTORE TABLE} Syntax @example RESTORE TABLE tbl_name[,tbl_name...] FROM '/path/to/backup/directory' @@ -18146,7 +18159,7 @@ The command returns a table with the following columns: @findex ANALYZE TABLE @node ANALYZE TABLE, REPAIR TABLE, RESTORE TABLE, Reference -@section @code{ANALYZE TABLE} syntax +@section @code{ANALYZE TABLE} Syntax @example ANALYZE TABLE tbl_name[,tbl_name...] @@ -18154,7 +18167,7 @@ ANALYZE TABLE tbl_name[,tbl_name...] Analyze and store the key distribution for the table. During the analyze the table is locked with a read lock. -This is equivalent of running @code{myisamchk -a} on the table. +This is equivalent to running @code{myisamchk -a} on the table. @strong{MySQL} uses the stored key distribution to decide in which order tables should be joined when one does a join on something else than a @@ -18178,13 +18191,13 @@ the table will not be analyzed again. @findex REPAIR TABLE @node REPAIR TABLE, DELETE, ANALYZE TABLE, Reference -@section @code{REPAIR TABLE} syntax +@section @code{REPAIR TABLE} Syntax @example REPAIR TABLE tbl_name[,tbl_name...] [QUICK] [EXTENDED] @end example -@code{REPAIR TABLE} only works on @code{MyISAM} tables and is the same things +@code{REPAIR TABLE} only works on @code{MyISAM} tables and is the same as running @code{myisamchk -r table_name} on the table. Repair the corrupted table. The command returns a table with the following @@ -18215,7 +18228,7 @@ keys that compress very good. @findex DELETE @node DELETE, TRUNCATE, REPAIR TABLE, Reference -@section @code{DELETE} syntax +@section @code{DELETE} Syntax @example DELETE [LOW_PRIORITY] FROM tbl_name @@ -18250,7 +18263,7 @@ operations reuse old record positions. To reclaim unused space and reduce file sizes, use the @code{OPTIMIZE TABLE} statement or the @code{myisamchk} utility to reorganize tables. @code{OPTIMIZE TABLE} is easier, but @code{myisamchk} is faster. -@xref{OPTIMIZE TABLE, , @code{OPTIMIZE TABLE}}, and +@xref{OPTIMIZE TABLE, , @code{OPTIMIZE TABLE}} and @ref{Optimization}. The @strong{MySQL}-specific @code{LIMIT rows} option to @code{DELETE} tells @@ -18262,7 +18275,7 @@ the @code{LIMIT} value. @findex TRUNCATE @node TRUNCATE, SELECT, DELETE, Reference -@section @code{TRUNCATE} syntax +@section @code{TRUNCATE} Syntax @example TRUNCATE table_name @@ -18288,7 +18301,7 @@ files have become corrupted.. @findex SELECT @node SELECT, JOIN, TRUNCATE, Reference -@section @code{SELECT} syntax +@section @code{SELECT} Syntax @c help SELECT @example @@ -18341,7 +18354,7 @@ retrieve rows. If you name more than one table, you are performing a join. For information on join syntax, see @ref{JOIN, , @code{JOIN}}. @item -You can refer to a column as @code{col_name}, @code{tbl_name.col_name} or +You can refer to a column as @code{col_name}, @code{tbl_name.col_name}, or @code{db_name.tbl_name.col_name}. You need not specify a @code{tbl_name} or @code{db_name.tbl_name} prefix for a column reference in a @code{SELECT} statement unless the reference would be ambiguous. See @ref{Legal names}, @@ -18351,7 +18364,7 @@ forms. @item @cindex aliases, for tables @cindex table aliases -A table reference may be aliased using @code{tbl_name [AS] alias_name}. +A table reference may be aliased using @code{tbl_name [AS] alias_name}: @example mysql> select t1.name, t2.salary from employee AS t1, info AS t2 @@ -18362,8 +18375,8 @@ mysql> select t1.name, t2.salary from employee t1, info t2 @item Columns selected for output may be referred to in @code{ORDER BY} and -@code{GROUP BY} clauses using column names, column aliases or column -positions. Column positions begin with 1. +@code{GROUP BY} clauses using column names, column aliases, or column +positions. Column positions begin with 1: @example mysql> select college, region, seed from tournament @@ -18395,7 +18408,7 @@ Write this instead: mysql> select col_name from tbl_name WHERE col_name > 0; @end example -In @strong{MySQL} 3.22.5 or later, you can also write queries like this: +In @strong{MySQL} Version 3.22.5 or later, you can also write queries like this: @example mysql> select user,max(salary) from users @@ -18411,7 +18424,7 @@ mysql> select user,max(salary) AS sum from users @item @code{SQL_SMALL_RESULT}, @code{SQL_BIG_RESULT}, @code{SQL_BUFFER_RESULT}, -@code{STRAIGHT_JOIN} and @code{HIGH_PRIORITY} are @strong{MySQL} extensions +@code{STRAIGHT_JOIN}, and @code{HIGH_PRIORITY} are @strong{MySQL} extensions to ANSI SQL92. @item @@ -18424,12 +18437,12 @@ statement that is waiting for the table to be free. @item @code{SQL_BIG_RESULT} can be used with @code{GROUP BY} or @code{DISTINCT} to tell the optimizer that the result set will have many rows. In this case, -@strong{MySQL} will directly use disk based temporary tables if needed. -@strong{MySQL} will also in this case prefer sorting to doing a +@strong{MySQL} will directly use disk-based temporary tables if needed. +@strong{MySQL} will also, in this case, prefer sorting to doing a temporary table with a key on the @code{GROUP BY} elements. @item -@code{SQL_BUFFER_RESULT} will put force the result to be put into a temporary +@code{SQL_BUFFER_RESULT} will force the result to be put into a temporary table. This will help @strong{MySQL} free the table locks early and will help in cases where it takes a long time to send the result set to the client. @@ -18453,13 +18466,13 @@ arguments. If two arguments are given, the first specifies the offset of the first row to return, the second specifies the maximum number of rows to return. -The offset of the initial row is 0 (not 1). +The offset of the initial row is 0 (not 1): @example mysql> select * from table LIMIT 5,10; # Retrieve rows 6-15 @end example -If one argument is given, it indicates the maximum number of rows to return. +If one argument is given, it indicates the maximum number of rows to return: @example mysql> select * from table LIMIT 5; # Retrieve first 5 rows @@ -18470,7 +18483,7 @@ In other words, @code{LIMIT n} is equivalent to @code{LIMIT 0,n}. @item @tindex /etc/passwd The @code{SELECT ... INTO OUTFILE 'file_name'} form of @code{SELECT} writes -the selected rows to a file. The file is created on the server host, and +the selected rows to a file. The file is created on the server host and cannot already exist (among other things, this prevents database tables and files such as @file{/etc/passwd} from being destroyed). You must have the @strong{file} privilege on the server host to use this form of @code{SELECT}. @@ -18494,7 +18507,7 @@ Additionally, @code{ASCII 0} is converted to @code{ESCAPED BY} followed by 0 (@code{ASCII 48}). The reason for the above is that you MUST escape any @code{FIELDS -TERMINATED BY}, @code{ESCAPED BY} or @code{LINES TERMINATED BY} +TERMINATED BY}, @code{ESCAPED BY}, or @code{LINES TERMINATED BY} characters to reliably be able to read the file back. @code{ASCII 0} is escaped to make it easier to view with some pagers. @@ -18504,7 +18517,7 @@ else need be escaped. @end itemize @findex DUMPFILE -If you use @code{INTO DUMPFILE} instead of @code{INTO OUTFILE} @strong{MySQL} +If you use @code{INTO DUMPFILE} instead of @code{INTO OUTFILE}, @strong{MySQL} will only write one row into the file, without any column or line terminations and without any escaping. This is useful if you want to store a blob in a file. @@ -18522,7 +18535,7 @@ store a blob in a file. @findex NATURAL RIGHT OUTER JOIN @findex STRAIGHT_JOIN @node JOIN, INSERT, SELECT, Reference -@section @code{JOIN} syntax +@section @code{JOIN} Syntax @strong{MySQL} supports the following @code{JOIN} syntaxes for use in @code{SELECT} statements: @@ -18541,30 +18554,30 @@ table_reference RIGHT [OUTER] JOIN table_reference table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference @end example -Where @code{table_reference} is defined as +Where @code{table_reference} is defined as: @example table_name [[AS] alias] [USE INDEX (key_list)] [IGNORE INDEX (key_list)] @end example -and @code{join_condition} is defined as +and @code{join_condition} is defined as: @example ON conditional_expr | USING (column_list) @end example -Note that in version before 3.23.16 the @code{INNER JOIN} didn't take +Note that in versions before Version 3.23.16, the @code{INNER JOIN} didn't take a join condition! @cindex ODBC compatibility @cindex compatibility, with ODBC The last @code{LEFT OUTER JOIN} syntax shown above exists only for -compatibility with ODBC. +compatibility with ODBC: @itemize @bullet @item A table reference may be aliased using @code{tbl_name AS alias_name} or -@code{tbl_name alias_name}. +@code{tbl_name alias_name}: @example mysql> select t1.name, t2.salary from employee AS t1, info AS t2 @@ -18593,7 +18606,7 @@ mysql> select table1.* from table1 @end example This example finds all rows in @code{table1} with an @code{id} value that is -not present in @code{table2} (i.e., all rows in @code{table1} with no +not present in @code{table2} (that is, all rows in @code{table1} with no corresponding row in @code{table2}). This assumes that @code{table2.id} is declared @code{NOT NULL}, of course. @xref{LEFT JOIN optimization}. @@ -18614,7 +18627,7 @@ A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,... @item The @code{NATURAL [LEFT] JOIN} of two tables is defined to be -semantically equivalent to a @code{INNER JOIN} or a @code{LEFT JOIN} +semantically equivalent to an @code{INNER JOIN} or a @code{LEFT JOIN} with a @code{USING} clause that names all columns that exist in both tables. @@ -18657,7 +18670,7 @@ mysql> select * from table1 IGNORE INDEX (key3) WHERE key1=1 and key2=2 AND @findex INSERT @node INSERT, REPLACE, JOIN, Reference -@section @code{INSERT} syntax +@section @code{INSERT} Syntax @example INSERT [LOW_PRIORITY | DELAYED] [IGNORE] @@ -18672,16 +18685,16 @@ or INSERT [LOW_PRIORITY | DELAYED] [IGNORE] @end example @code{INSERT} inserts new rows into an existing table. The @code{INSERT ... -VALUES} form of the statement inserts rows based on explicitly-specified +VALUES} form of the statement inserts rows based on explicitly specified values. The @code{INSERT ... SELECT} form inserts rows selected from another table or tables. The @code{INSERT ... VALUES} form with multiple value lists -is supported in @strong{MySQL} 3.22.5 or later. The -@code{col_name=expression} syntax is supported in @strong{MySQL} 3.22.10 or +is supported in @strong{MySQL} Version 3.22.5 or later. The +@code{col_name=expression} syntax is supported in @strong{MySQL} Version 3.22.10 or later. @code{tbl_name} is the table into which rows should be inserted. The column name list or the @code{SET} clause indicates which columns the statement -specifies values for. +specifies values for: @itemize @bullet @item @@ -18715,13 +18728,13 @@ If you specify the keyword @code{LOW_PRIORITY}, execution of the @code{INSERT} is delayed until no other clients are reading from the table. In this case the client has to wait until the insert statement is completed, which may take a long time if the table is in heavy use. This is in -contrast to @code{INSERT DELAYED} which lets the client continue at once. +contrast to @code{INSERT DELAYED}, which lets the client continue at once. Note that @code{LOW_PRIORITY} should normally not be used with @code{MyISAM} tables as this disables concurrent inserts.@xref{MyISAM}. @item If you specify the keyword @code{IGNORE} in an @code{INSERT} with many value -rows, any rows which duplicate an existing @code{PRIMARY} or @code{UNIQUE} +rows, any rows that duplicate an existing @code{PRIMARY} or @code{UNIQUE} key in the table are ignored and are not inserted. If you do not specify @code{IGNORE}, the insert is aborted if there is any row that duplicates an existing key value. You can check with the C API function @@ -18734,7 +18747,7 @@ specify values for all columns that require a non-@code{NULL} value. @xref{configure options, , @code{configure} options}. @item -The following conditions hold for a @code{INSERT INTO ... SELECT} statement: +The following conditions hold for an @code{INSERT INTO ... SELECT} statement: @itemize @minus @item @@ -18742,7 +18755,7 @@ The query cannot contain an @code{ORDER BY} clause. @item The target table of the @code{INSERT} statement cannot appear in the -@code{FROM} clause of the @code{SELECT} part of the query, because it's +@code{FROM} clause of the @code{SELECT} part of the query because it's forbidden in ANSI SQL to @code{SELECT} from the same table into which you are inserting. (The problem is that the @code{SELECT} possibly would find records that were inserted earlier during the same run. When using @@ -18754,7 +18767,7 @@ sub-select clauses, the situation could easily be very confusing!) @end itemize @findex mysql_info() -If you use @code{INSERT ... SELECT} or a @code{INSERT ... VALUES} +If you use @code{INSERT ... SELECT} or an @code{INSERT ... VALUES} statement with multiple value lists, you can use the C API function @code{mysql_info()} to get information about the query. The format of the information string is shown below: @@ -18784,13 +18797,13 @@ garbage is stripped and the remaining numeric part is inserted. If the value doesn't make sense as a number at all, the column is set to @code{0}. @item -Inserting a string into a @code{CHAR}, @code{VARCHAR}, @code{TEXT} or +Inserting a string into a @code{CHAR}, @code{VARCHAR}, @code{TEXT}, or @code{BLOB} column that exceeds the column's maximum length. The value is truncated to the column's maximum length. @item Inserting a value into a date or time column that is illegal for the column -type. The column is set to the appropriate ``zero'' value for the type. +type. The column is set to the appropriate zero value for the type. @end itemize @findex INSERT DELAYED @@ -18802,10 +18815,10 @@ for the useful if you have clients that can't wait for the @code{INSERT} to complete. This is a common problem when you use @strong{MySQL} for logging and you also periodically run @code{SELECT} statements that take a long time to complete. -@code{DELAYED} was introduced in @strong{MySQL} 3.22.15. It is a +@code{DELAYED} was introduced in @strong{MySQL} Version 3.22.15. It is a @strong{MySQL} extension to ANSI SQL92. -When you use @code{INSERT DELAYED}, the client will get an ok at once +When you use @code{INSERT DELAYED}, the client will get an OK at once and the row will be inserted when the table is not in use by any other thread. Another major benefit of using @code{INSERT DELAYED} is that inserts @@ -18838,8 +18851,8 @@ or @code{WRITE} lock on the table. However, the handler will wait for all structure is up to date. @item -The thread executes the @code{INSERT} statement but instead of writing -the row to the table it puts a copy of the final row into a queue that +The thread executes the @code{INSERT} statement, but instead of writing +the row to the table, it puts a copy of the final row into a queue that is managed by the handler thread. Any syntax errors are noticed by the thread and reported to the client program. @@ -18885,7 +18898,7 @@ Note that the above means that @code{INSERT DELAYED} commands have higher priority than normal @code{INSERT} commands if there is an @code{INSERT DELAYED} handler already running! Other update commands will have to wait until the @code{INSERT DELAYED} queue is empty, someone kills the handler -thread (with @code{KILL thread_id}) or someone executes @code{FLUSH TABLES}. +thread (with @code{KILL thread_id}), or someone executes @code{FLUSH TABLES}. @item The following status variables provide information about @code{INSERT @@ -18910,7 +18923,7 @@ DELAYED} when you are really sure you need it! @findex REPLACE @node REPLACE, LOAD DATA, INSERT, Reference -@section @code{REPLACE} syntax +@section @code{REPLACE} Syntax @example REPLACE [LOW_PRIORITY | DELAYED] @@ -18934,7 +18947,7 @@ In other words, you can't access the values of the old row from a @findex LOAD DATA INFILE @node LOAD DATA, UPDATE, REPLACE, Reference -@section @code{LOAD DATA INFILE} syntax +@section @code{LOAD DATA INFILE} Syntax @example LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] @@ -18953,7 +18966,7 @@ The @code{LOAD DATA INFILE} statement reads rows from a text file into a table at a very high speed. If the @code{LOCAL} keyword is specified, the file is read from the client host. If @code{LOCAL} is not specified, the file must be located on the server. (@code{LOCAL} is available in -@strong{MySQL} 3.22.6 or later.) +@strong{MySQL} Version 3.22.6 or later.) For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. @@ -18972,7 +18985,7 @@ host to the server host. On the other hand, you do not need the @c old version If you are using @strong{MySQL} before Version 3.23.24 you can't read from a -FIFO with @code{LOAD DATA INFILE}; If you need to read from a FIFO (for +FIFO with @code{LOAD DATA INFILE}. If you need to read from a FIFO (for example the output from gunzip), use @code{LOAD DATA LOCAL INFILE} instead. @@ -19039,7 +19052,7 @@ must precede @code{LINES} if both are specified. If you specify a @code{FIELDS} clause, each of its subclauses (@code{TERMINATED BY}, @code{[OPTIONALLY] ENCLOSED -BY} and @code{ESCAPED BY}) is also optional, except that you must +BY}, and @code{ESCAPED BY}) is also optional, except that you must specify at least one of them. If you don't specify a @code{FIELDS} clause, the defaults are the @@ -19061,17 +19074,17 @@ when reading input: @itemize @bullet @item -Look for line boundaries at newlines +Look for line boundaries at newlines. @item -Break lines into fields at tabs +Break lines into fields at tabs. @item -Do not expect fields to be enclosed within any quoting characters +Do not expect fields to be enclosed within any quoting characters. @item -Interpret occurrences of tab, newline or @samp{\} preceded by -@samp{\} as literal characters that are part of field values +Interpret occurrences of tab, newline, or @samp{\} preceded by +@samp{\} as literal characters that are part of field values. @end itemize Conversely, the defaults cause @code{SELECT ... INTO OUTFILE} to act as @@ -19079,17 +19092,17 @@ follows when writing output: @itemize @bullet @item -Write tabs between fields +Write tabs between fields. @item -Do not enclose fields within any quoting characters +Do not enclose fields within any quoting characters. @item Use @samp{\} to escape instances of tab, newline or @samp{\} that occur -within field values +within field values. @item -Write newlines at the ends of lines +Write newlines at the ends of lines. @end itemize Note that to write @code{FIELDS ESCAPED BY '\\'}, you must specify two @@ -19258,7 +19271,7 @@ values are written and read using the ``display'' widths of the columns. For example, if a column is declared as @code{INT(7)}, values for the column are written using 7-character fields. On input, values for the column are obtained by reading 7 characters. Fixed-row format also affects handling of -@code{NULL} values; see below. Note that fixed size format will not work +@code{NULL} values; see below. Note that fixed-size format will not work if you are using a multi-byte character set. @end itemize @@ -19407,7 +19420,7 @@ For more information about the efficiency of @code{INSERT} versus @findex UPDATE @node UPDATE, USE, LOAD DATA, Reference -@section @code{UPDATE} syntax +@section @code{UPDATE} Syntax @example UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1,col_name2=expr2,... @@ -19447,16 +19460,16 @@ this and doesn't update it. @findex mysql_info() @code{UPDATE} returns the number of rows that were actually changed. -In @strong{MySQL} 3.22 or later, the C API function @code{mysql_info()} +In @strong{MySQL} Version 3.22 or later, the C API function @code{mysql_info()} returns the number of rows that were matched and updated and the number of warnings that occurred during the @code{UPDATE}. -In @strong{MySQL} Version 3.23 you can use @code{LIMIT #} to ensure that +In @strong{MySQL} Version 3.23, you can use @code{LIMIT #} to ensure that only a given number of rows are changed. @findex USE @node USE, FLUSH, UPDATE, Reference -@section @code{USE} syntax +@section @code{USE} Syntax @example USE db_name @@ -19464,7 +19477,7 @@ USE db_name The @code{USE db_name} statement tells @strong{MySQL} to use the @code{db_name} database as the default database for subsequent queries. The database remains -current until the end of the session, or until another @code{USE} statement +current until the end of the session or until another @code{USE} statement is issued: @example @@ -19494,7 +19507,7 @@ The @code{USE} statement is provided for Sybase compatibility. @cindex caches, clearing @findex FLUSH @node FLUSH, KILL, USE, Reference -@section @code{FLUSH} syntax (clearing caches) +@section @code{FLUSH} Syntax (Clearing Caches) @example FLUSH flush_option [,flush_option] @@ -19529,7 +19542,7 @@ the @code{mysql} database. @item @code{TABLES} @tab Closes all open tables and force all tables in use to be closed. -@item @code{[TABLE | TABLES] table_name [,table_name...]} @tab Flush only the given tables +@item @code{[TABLE | TABLES] table_name [,table_name...]} @tab Flushes only the given tables. @item @code{TABLES WITH READ LOCK} @tab Closes all open tables and locks all tables for all databases with a read until one executes @code{UNLOCK TABLES}. This is very convinient way to get backups if you have a file system, like Veritas,that can take snapshots in time. @@ -19537,20 +19550,20 @@ the @code{mysql} database. @end multitable You can also access each of the commands shown above with the @code{mysqladmin} -utility, using the @code{flush-hosts}, @code{flush-logs}, @code{reload} +utility, using the @code{flush-hosts}, @code{flush-logs}, @code{reload}, or @code{flush-tables} commands. @cindex @code{mysqladmin} @findex KILL @node KILL, SHOW, FLUSH, Reference -@section @code{KILL} syntax +@section @code{KILL} Syntax @example KILL thread_id @end example Each connection to @code{mysqld} runs in a separate thread. You can see -which threads are running with the @code{SHOW PROCESSLIST} command, and kill +which threads are running with the @code{SHOW PROCESSLIST} command and kill a thread with the @code{KILL thread_id} command. If you have the @strong{process} privilege, you can see and kill all threads. @@ -19572,7 +19585,7 @@ commands to examine and kill threads. @findex SHOW GRANTS @findex SHOW CREATE TABLE @node SHOW, EXPLAIN, KILL, Reference -@section @code{SHOW} syntax (Get information about tables, columns,...) +@section @code{SHOW} Syntax (Get Information About Tables, Columns,...) @example SHOW DATABASES [LIKE wild] @@ -19587,7 +19600,7 @@ or SHOW GRANTS FOR user or SHOW CREATE TABLE table_name @end example -@code{SHOW} provides information about databases, tables, columns or +@code{SHOW} provides information about databases, tables, columns, or status information about the server. If the @code{LIKE wild} part is used, the @code{wild} string can be a string that uses the SQL @samp{%} and @samp{_} wild-card characters. @@ -19610,7 +19623,7 @@ and @samp{_} wild-card characters. @cindex displaying, information, @code{SHOW} @node SHOW DATABASE INFO, SHOW TABLE STATUS, SHOW, SHOW -@subsection SHOW information about database, tables, columns and index +@subsection SHOW Information About Databases, Tables, Columns, and Indexes You can use @code{db_name.tbl_name} as an alternative to the @code{tbl_name FROM db_name} syntax. These two statements are equivalent: @@ -19626,7 +19639,7 @@ host. You can also get this list using the @code{mysqlshow} command. @code{SHOW TABLES} lists the tables in a given database. You can also get this list using the @code{mysqlshow db_name} command. -@strong{Note:} If a user doesn't have any privileges for a table, the table +@strong{NOTE:} If a user doesn't have any privileges for a table, the table will not show up in the output from @code{SHOW TABLES} or @code{mysqlshow db_name}. @@ -19639,7 +19652,7 @@ The @code{DESCRIBE} statement provides information similar to @code{SHOW COLUMNS}. @xref{DESCRIBE, , @code{DESCRIBE}}. -@code{SHOW FIELDS} is a synonym for @code{SHOW COLUMNS} and +@code{SHOW FIELDS} is a synonym for @code{SHOW COLUMNS}, and @code{SHOW KEYS} is a synonym for @code{SHOW INDEX}. You can also list a table's columns or indexes with @code{mysqlshow db_name tbl_name} or @code{mysqlshow -k db_name tbl_name}. @@ -19650,9 +19663,9 @@ are returned: @multitable @columnfractions .35 .65 @item @strong{Column} @tab @strong{Meaning} -@item @code{Table} @tab Name of the table +@item @code{Table} @tab Name of the table. @item @code{Non_unique} @tab 0 if the index can't contain duplicates. -@item @code{Key_name} @tab Name of the index +@item @code{Key_name} @tab Name of the index. @item @code{Seq_in_index} @tab Column sequence number in index, starting with 1. @item @code{Column_name} @tab Column name. @item @code{Collation} @tab How the column is sorted in the index. In @@ -19668,34 +19681,34 @@ only partly indexed. @code{NULL} if the entire key is indexed. @cindex tables, displaying status @cindex status, tables @node SHOW TABLE STATUS, SHOW STATUS, SHOW DATABASE INFO, SHOW -@subsection SHOW status information about tables +@subsection SHOW Status Information About Tables -@code{SHOW TABLE STATUS} (new in version 3.23) works likes @code{SHOW +@code{SHOW TABLE STATUS} (new in Version 3.23) works likes @code{SHOW STATUS}, but provides a lot of information about each table. You can also get this list using the @code{mysqlshow --status db_name} command. The following columns are returned: @multitable @columnfractions .30 .70 @item @strong{Column} @tab @strong{Meaning} -@item @code{Name} @tab Name of the table -@item @code{Type} @tab Type of table (BDB, ISAM, MERGE, MyISAM or HEAP) -@item @code{Row_format} @tab The row storage format (Fixed, Dynamic, or Compressed) -@item @code{Rows} @tab Number of rows -@item @code{Avg_row_length} @tab Average row length -@item @code{Data_length} @tab Length of the data file -@item @code{Max_data_length} @tab Max length of the data file -@item @code{Index_length} @tab Length of the index file -@item @code{Data_free} @tab Number of allocated but not used bytes -@item @code{Auto_increment} @tab Next autoincrement value -@item @code{Create_time} @tab When the table was created -@item @code{Update_time} @tab When the data file was last updated -@item @code{Check_time} @tab When one last run a check on the table -@item @code{Create_options} @tab Extra options used with @code{CREATE TABLE} +@item @code{Name} @tab Name of the table. +@item @code{Type} @tab Type of table (BDB, ISAM, MERGE, MyISAM, or HEAP). +@item @code{Row_format} @tab The row storage format (Fixed, Dynamic, or Compressed). +@item @code{Rows} @tab Number of rows. +@item @code{Avg_row_length} @tab Average row length. +@item @code{Data_length} @tab Length of the data file. +@item @code{Max_data_length} @tab Max length of the data file. +@item @code{Index_length} @tab Length of the index file. +@item @code{Data_free} @tab Number of allocated but not used bytes. +@item @code{Auto_increment} @tab Next autoincrement value. +@item @code{Create_time} @tab When the table was created. +@item @code{Update_time} @tab When the data file was last updated. +@item @code{Check_time} @tab When the table was last checked. +@item @code{Create_options} @tab Extra options used with @code{CREATE TABLE}. @item @code{Comment} @tab The comment used when creating the table (or some information why @strong{MySQL} couldn't access the table information). @end multitable @node SHOW STATUS, SHOW VARIABLES, SHOW TABLE STATUS, SHOW -@subsection SHOW status information +@subsection SHOW Status Information @cindex @code{mysqladmin} @code{SHOW STATUS} provides server status information @@ -19762,13 +19775,14 @@ The status variables listed above have the following meaning: @multitable @columnfractions .35 .65 @item @strong{Variable} @tab @strong{Meaning} -@item @code{Aborted_clients} @tab Number of connections that has been aborted because the client has died without closing the connection properly. -@item @code{Aborted_connects} @tab Number of tries to connect to the @strong{MySQL} server that has failed. -@item @code{Bytes_received} @tab Number of bytes received from all clients -@item @code{Bytes_sent} @tab Number of bytes sent to all clients +@item @code{Aborted_clients} @tab Number of connections aborted because the client died without closing the connection properly. +@item @code{Aborted_connects} @tab Number of tries to connect to the @strong{MySQL} server that failed. +@item @code{Bytes_received} @tab Number of bytes received from all clients. +@item @code{Bytes_sent} @tab Number of bytes sent to all clients. @item @code{Connections} @tab Number of connection attempts to the @strong{MySQL} server. -@item @code{Created_tmp_disk_tables} @tab Number of implicit temporary tables on disk that have been created while executing statements. -@item @code{Created_tmp_tables} @tab Number of implicit temporary tables in memory that have been created while executing statements. +@item @code{Created_tmp_disk_tables} @tab Number of implicit temporary tables on disk created while executing statements. +@item @code{Created_tmp_tables} @tab Number of implicit temporary tables in memory created while executing statements. +@item @code{Created_tmp_files} @tab How many temporary files @code{mysqld} have created. @item @code{Delayed_insert_threads} @tab Number of delayed insert handler threads in use. @item @code{Delayed_writes} @tab Number of rows written with @code{INSERT DELAYED}. @item @code{Delayed_errors} @tab Number of rows written with @code{INSERT DELAYED} for which some error occurred (probably @code{duplicate key}). @@ -19776,7 +19790,7 @@ The status variables listed above have the following meaning: @item @code{Handler_delete} @tab Number of times a row was deleted from a table. @item @code{Handler_read_first} @tab Number of times the first entry was read from an index. If this is high, it suggests that the server is doing a lot of full index scans, for example, -@code{SELECT col1 FROM foo}, assuming that col1 is indexed +@code{SELECT col1 FROM foo}, assuming that col1 is indexed. @item @code{Handler_read_key} @tab Number of requests to read a row based on a key. If this is high, it is a good indication that your queries and tables are properly indexed. @item @code{Handler_read_next} @tab Number of requests to read next row in key order. This @@ -19785,9 +19799,9 @@ will be incremented if you are doing an index scan. @item @code{Handler_read_rnd} @tab Number of requests to read a row based on a fixed position. This will be high if you are doing a lot of queries that require sorting of the result. @item @code{Handler_read_rnd_next} @tab Number of requests to read the next row in the datafile. -This will be high if you are doing a lot of table scans - generally this suggests that you tables -are not properly indexed or that you queries are not written to take advantaged of the indeces you -have.. +This will be high if you are doing a lot of table scans. Generally this suggests that your tables +are not properly indexed or that your queries are not written to take advantage of the indexes you +have. @item @code{Handler_update} @tab Number of requests to update a row in a table. @item @code{Handler_write} @tab Number of requests to insert a row in a table. @item @code{Key_blocks_used} @tab The number of used blocks in the key cache. @@ -19795,21 +19809,22 @@ have.. @item @code{Key_reads} @tab The number of physical reads of a key block from disk. @item @code{Key_write_requests} @tab The number of requests to write a key block to the cache. @item @code{Key_writes} @tab The number of physical writes of a key block to disk. -@item @code{Max_used_connections} @tab The maximum number of connections that has been in use simultaneously. +@item @code{Max_used_connections} @tab The maximum number of connections in use simultaneously. @item @code{Not_flushed_key_blocks} @tab Keys blocks in the key cache that has changed but hasn't yet been flushed to disk. @item @code{Not_flushed_delayed_rows} @tab Number of rows waiting to be written in @code{INSERT DELAY} queues. @item @code{Open_tables} @tab Number of tables that are open. @item @code{Open_files} @tab Number of files that are open. -@item @code{Open_streams} @tab Number of streams that are open (used mainly for logging) +@item @code{Open_streams} @tab Number of streams that are open (used mainly for logging). @item @code{Opened_tables} @tab Number of tables that have been opened. @item @code{Select_full_join} @tab Number of joins without keys (Should be 0). @item @code{Select_full_range_join} @tab Number of joins where we used a range search on reference table. -@item @code{Select_range} @tab Number of joins where we used ranges on the first table (It's normally not critical even if this is big) +@item @code{Select_range} @tab Number of joins where we used ranges on the first table. (It's normally not critical even if this is big.) @item @code{Select_scan} @tab Number of joins where we scanned the first table. @item @code{Select_range_check} @tab Number of joins without keys where we check for key usage after each row (Should be 0). @item @code{Questions} @tab Number of queries sent to the server. @item @code{Slow_launch_threads} @tab Number of threads that have taken more than @code{slow_launch_time} to connect. -@item @code{Slow_queries} @tab Number of queries that has taken more than @code{long_query_time}. @xref{Slow query log}. +@item @code{Slow_queries} @tab Number of queries that have taken more than @code{long_query_time}. @xref{Slow query log}. +@item @code{Sort_merge_passes} @tab Number of merges the sort has to do. If this value is large you should consider increasing @code{sort_buffer}. @item @code{Sort_range} @tab Number of sorts that where done with ranges. @item @code{Sort_rows} @tab Number of sorted rows. @item @code{Sort_scan} @tab Number of sorts that where done by scanning the table. @@ -19830,15 +19845,22 @@ If @code{key_reads} is big, then your @code{key_cache} is probably too small. The cache hit rate can be calculated with @code{key_reads}/@code{key_read_requests}. @item -If @code{Handler_read_rnd} is big, then you have a probably a lot of queries -that requires @strong{MySQL} to scan whole tables or you have joins that doesn't use +If @code{Handler_read_rnd} is big, then you probably have a lot of queries +that require @strong{MySQL} to scan whole tables or you have joins that don't use keys properly. +@item +If @code{Created_tmp_tables} or @code{Sort_merge_passes} are high then +your @code{mysqld} @code{sort_buffer} variables is probably too small. +@item +@code{Created_tmp_files} doesn't count the files needed to handle temporary +tables. +@item @end itemize @node SHOW VARIABLES, SHOW PROCESSLIST, SHOW STATUS, SHOW @subsection SHOW VARIABLES -@code{SHOW VARIABLES} shows the values of the some of @strong{MySQL} system +@code{SHOW VARIABLES} shows the values of some @strong{MySQL} system variables. You can also get this information using the @code{mysqladmin variables} command. If the default values are unsuitable, you can set most of these variables using command-line options when @code{mysqld} starts up. @@ -19914,11 +19936,11 @@ differ somewhat: +-------------------------+---------------------------------+ @end example -Each option is described below. Values for buffer sizes, lengths and stack +Each option is described below. Values for buffer sizes, lengths, and stack sizes are given in bytes. You can specify values with a suffix of @samp{K} or @samp{M} to indicate kilobytes or megabytes. For example, @code{16M} indicates 16 megabytes. The case of suffix letters does not matter; -@code{16M} and @code{16m} are equivalent. +@code{16M} and @code{16m} are equivalent: @table @code @item @code{ansi_mode}. @@ -19937,7 +19959,7 @@ number of connections in a short period of time. In other words, this value is the size of the listen queue for incoming TCP/IP connections. Your operating system has its own limit on the size -of this queue. The manual page for the Unix @code{listen(2)} system +of this queue. The manual page for the UNIX @code{listen(2)} system call should have more details. Check your OS documentation for the maximum value for this variable. Attempting to set @code{back_log} higher than your operating system limit will be ineffective. @@ -19990,7 +20012,7 @@ If enabled (is on by default), @strong{MySQL} will honor the @code{delay_key_write} option @code{CREATE TABLE}. This means that the key buffer for tables with this option will not get flushed on every index update, but only when a table is closed. This will speed up -writes on keys a lot but you should add automatic checking of all tables +writes on keys a lot, but you should add automatic checking of all tables with @code{myisamchk --fast --force} if you use this. Note that if you start @code{mysqld} with the @code{--delay-key-write-for-all-tables} option this means that all tables will be treated as if they were @@ -20018,7 +20040,7 @@ option. @item @code{flush_time} If this is set to a non-zero value, then every @code{flush_time} seconds all tables will be closed (to free up resources and sync things to disk). We -only recommend this option on Win95, Win98 or on system where you have +only recommend this option on Win95, Win98, or on systems where you have very little resources. @item @code{init_file} @@ -20048,7 +20070,7 @@ Index blocks are buffered and are shared by all threads. Increase this to get better index handling (for all reads and multiple writes) to as much as you can afford; 64M on a 256M machine that mainly -runs @strong{MySQL} is quite common. If you however make this too big +runs @strong{MySQL} is quite common. If you, however, make this too big (more than 50% of your total memory?) your system may start to page and become REALLY slow. Remember that because @strong{MySQL} does not cache data read, that you will have to leave some room for the OS filesystem @@ -20056,14 +20078,14 @@ cache. You can check the performance of the key buffer by doing @code{show status} and examine the variables @code{Key_read_requests}, -@code{Key_reads}, @code{Key_write_requests} and @code{Key_writes}. The +@code{Key_reads}, @code{Key_write_requests}, and @code{Key_writes}. The @code{Key_reads/Key_read_request} ratio should normally be < 0.01. The @code{Key_write/Key_write_requests} is usually near 1 if you are using mostly updates/deletes but may be much smaller if you tend to do updates that affect many at the same time or if you are using @code{delay_key_write}. @xref{SHOW}. -To get even more speed when writing many rows at the same time use +To get even more speed when writing many rows at the same time, use @code{LOCK TABLES}. @xref{LOCK TABLES, , @code{LOCK TABLES}}. @item @code{language} @@ -20093,7 +20115,7 @@ will be incremented. If you are using @code{--log-slow-queries}, the query will be logged to the slow query logfile. @xref{Slow query log}. @item @code{lower_case_table_names} -Table names are stored in lower case on disk. +Table names are stored in lowercase on disk. @item @code{max_allowed_packet} The maximum size of one packet. The message buffer is initialized to @@ -20125,7 +20147,7 @@ Don't allow creation of heap tables bigger than this. @item @code{max_join_size} Joins that are probably going to read more than @code{max_join_size} records return an error. Set this value if your users tend to perform joins -without a @code{WHERE} clause that take a long time and return +that lack a @code{WHERE} clause, that take a long time, and that return millions of rows. @item @code{max_sort_length} @@ -20134,7 +20156,7 @@ values (only the first @code{max_sort_length} bytes of each value are used; the rest are ignored). @item @code{max_tmp_tables} -(This option doesn't yet do anything). +(This option doesn't yet do anything.) Maximum number of temporary tables a client can keep open at the same time. @item @code{max_write_lock_count} @@ -20163,7 +20185,7 @@ is defined by @code{write_timeout}. @item @code{net_retry_count} If a read on a communication port is interrupted, retry this many times before giving up. This value should be quite high on @code{FreeBSD} as -internal interrupts is sent to all threads. +internal interrupts are sent to all threads. @item @code{net_write_timeout} Number of seconds to wait for a block to be written to a connection before @@ -20197,13 +20219,13 @@ Is OFF if @code{mysqld} uses external locking. Is ON if we only allow local (socket) connections. @item @code{skip_show_databases} -This prevents people from doing @code{SHOW DATABASES}, if they don't +This prevents people from doing @code{SHOW DATABASES} if they don't have the @code{PROCESS_PRIV} privilege. This can improve security if you're concerned about people being able to see what databases and tables other users have. @item @code{slow_launch_time} -If the creating of the thread longer than this (in seconds), the +If creating the thread takes longer than this value (in seconds), the @code{Slow_launch_threads} counter will be incremented. @item @code{socket} @@ -20236,19 +20258,19 @@ For information about how the table cache works, see @ref{Table cache}. The default table type @item @code{thread_cache_size} -How many threads we should keep keep in a cache for reuse. When a -client disconnects the clients threads is put in the cache if there -isn't more than @code{thread_cache_size} threads from before. All new -threads are first taken from the cache and only when the cache is empty -a new thread is created. This variable can be increased to improve -performance if you have a lot of new connections (Normally this doesn't -however give a notable performance improvement if you have a good -thread implementation). +How many threads we should keep in a cache for reuse. When a +client disconnects, the client's threads are put in the cache if there +aren't more than @code{thread_cache_size} threads from before. All new +threads are first taken from the cache, and only when the cache is empty +is a new thread created. This variable can be increased to improve +performance if you have a lot of new connections. (Normally this doesn't +give a notable performance improvement if you have a good +thread implementation.) @item @code{thread_concurrency} On Solaris, @code{mysqld} will call @code{thr_setconcurrency()} with this value. @code{thr_setconcurrency()} permits the application to give -the threads system a hint, for the desired number of threads that should +the threads system a hint for the desired number of threads that should be run at the same time. @item @code{thread_stack} @@ -20284,7 +20306,7 @@ information of how to tune the above variables. @xref{Server parameters}. @findex threads @findex PROCESSLIST @node SHOW PROCESSLIST, SHOW GRANTS, SHOW VARIABLES, SHOW -@subsection SHOW information about connected threads (processes) +@subsection SHOW Information About Connected Threads (Processes) @code{SHOW PROCESSLIST} shows you which threads are running. You can also get this information using the @code{mysqladmin processlist} @@ -20295,7 +20317,7 @@ the first 100 characters of each query will be shown. @cindex privileges, display @node SHOW GRANTS, SHOW CREATE TABLE, SHOW PROCESSLIST, SHOW -@subsection SHOW GRANTS (privileges) for a user +@subsection SHOW GRANTS (Privileges) for a User @code{SHOW GRANTS FOR user} lists the grant commands that must be issued to duplicate the grants for a user. @@ -20311,7 +20333,7 @@ mysql> SHOW GRANTS FOR root@@localhost; @node SHOW CREATE TABLE, , SHOW GRANTS, SHOW @subsection SHOW CREATE TABLE -Shows a @code{CREATE TABLE} statement that will create the given table +Shows a @code{CREATE TABLE} statement that will create the given table: @example mysql> show create table t\G *************************** 1. row *************************** @@ -20331,7 +20353,7 @@ Create Table: CREATE TABLE t ( @findex EXPLAIN @findex SELECT, optimizing @node EXPLAIN, DESCRIBE, SHOW, Reference -@section @code{EXPLAIN} syntax (Get information about a @code{SELECT}) +@section @code{EXPLAIN} Syntax (Get Information About a @code{SELECT}) @example EXPLAIN tbl_name @@ -20374,7 +20396,7 @@ The join type. Information about the various types is given below. The @code{possible_keys} column indicates which indexes @strong{MySQL} could use to find the rows in this table. Note that this column is totally independent of the order of the tables. That means that some of -the keys in possible_keys may not be useable in practice with the +the keys in possible_keys may not be usable in practice with the generated table order. If this column is empty, there are no relevant indexes. In this case, @@ -20396,7 +20418,7 @@ decided to use. The key is @code{NULL} if no index was chosen. If @item key_len The @code{key_len} column indicates the length of the key that @strong{MySQL} decided to use. The length is @code{NULL} if the -@code{key} is @code{NULL}. Note that this tell us how many parts of a +@code{key} is @code{NULL}. Note that this tells us how many parts of a multi-part key @strong{MySQL} will actually use. @item ref @@ -20409,7 +20431,7 @@ believes it must examine to execute the query. @item Extra This column contains additional information of how @strong{MySQL} will -resolve the query. Here follows an explanation of the different text +resolve the query. Here is an explanation of the different text strings that can be found in this column: @table @code @@ -20419,12 +20441,12 @@ combination after it has found the first matching row. @item Not exists @strong{MySQL} was able to do a @code{LEFT JOIN} optimization on the query and will not examine more rows in this table for a row combination -after it founds one rows that matches the @code{LEFT JOIN} criteria. +after it finds one row that matches the @code{LEFT JOIN} criteria. @item @code{range checked for each record (index map: #)} -@strong{MySQL} didn't find a real good index to use. It will instead for -each row combination in the preceding tables do a check which index to -use (if any) use this index to retrieve the rows from the table. This -isn't very fast but is of course faster than having to do a join without +@strong{MySQL} didn't find a real good index to use. It will, instead, for +each row combination in the preceding tables, do a check on which index to +use (if any), and use this index to retrieve the rows from the table. This +isn't very fast but is faster than having to do a join without an index. @item Using filesort @strong{MySQL} will need to do an extra pass to find out how to retrieve @@ -20435,17 +20457,17 @@ sorted. Finally the rows are retrieved in sorted order. @item Using index The column information is retrieved from the table using only information in the index tree without having to do an additional seek to -read the actually row. This can be done when all the used columns for +read the actual row. This can be done when all the used columns for the table are part of the same index. @item Using temporary -To be able to resolve the query @strong{MySQL} will need to create a +To resolve the query @strong{MySQL} will need to create a temporary table to hold the result. This typically happens if you do an -@code{ORDER BY} on a different column set than you did an @code{GROUP +@code{ORDER BY} on a different column set than you did a @code{GROUP BY} on. @item Where used A @code{WHERE} clause will be used to restrict which rows will be matched against the next table or sent to the client. If you don't have -this information and the table is of type @code{ALL} or @code{index} +this information and the table is of type @code{ALL} or @code{index}, you may have something wrong in your query (if you don't intend to fetch/examine all rows from the table). @end table @@ -20473,7 +20495,7 @@ optimizer. @code{const} tables are very fast as they are read only once! @item eq_ref One row will be read from this table for each combination of rows from -the previous tables. This the best possible join type, other than the +the previous tables. This is the best possible join type, other than the @code{const} types. It is used when all parts of an index are used by the join and the index is @code{UNIQUE} or a @code{PRIMARY KEY}. @@ -20581,7 +20603,7 @@ Because @code{type} is @code{ALL} for each table, this output indicates that long time, as the product of the number of rows in each table must be examined! For the case at hand, this is @code{74 * 2135 * 74 * 3872 = 45,268,558,720} rows. If the tables were bigger, you can only imagine how -long it would take... +long it would take. One problem here is that @strong{MySQL} can't (yet) use indexes on columns efficiently if they are declared differently. In this context, @@ -20633,7 +20655,7 @@ et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1 do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1 @end example -This is ``almost'' as good as it can get. +This is almost as good as it can get. The remaining problem is that, by default, @strong{MySQL} assumes that values in the @code{tt.ActualPC} column are evenly distributed, and that isn't the @@ -20645,7 +20667,7 @@ shell> myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt shell> mysqladmin refresh @end example -Now the join is ``perfect'', and @code{EXPLAIN} produces this result: +Now the join is perfect, and @code{EXPLAIN} produces this result: @example table type possible_keys key key_len ref rows Extra @@ -20656,7 +20678,7 @@ do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1 @end example Note that the @code{rows} column in the output from @code{EXPLAIN} is an -``educated guess'' from the @strong{MySQL} join optimizer; To optimize a +educated guess from the @strong{MySQL} join optimizer. To optimize a query, you should check if the numbers are even close to the truth. If not, you may get better performance by using @code{STRAIGHT_JOIN} in your @code{SELECT} statement and trying to list the tables in a different order in @@ -20665,7 +20687,7 @@ the @code{FROM} clause. @findex DESC @findex DESCRIBE @node DESCRIBE, COMMIT, EXPLAIN, Reference -@section @code{DESCRIBE} syntax (Get information about columns) +@section @code{DESCRIBE} Syntax (Get Information About Columns) @example @{DESCRIBE | DESC@} tbl_name @{col_name | wild@} @@ -20692,7 +20714,7 @@ The @code{SHOW} statement provides similar information. @findex COMMIT @findex ROLLBACK @node COMMIT, LOCK TABLES, DESCRIBE, Reference -@section @code{BEGIN/COMMIT/ROLLBACK} syntax +@section @code{BEGIN/COMMIT/ROLLBACK} Syntax By default, @strong{MySQL} runs in @code{autocommit} mode. This means that as soon as you execute an update, @strong{MySQL} will store the update on @@ -20709,8 +20731,8 @@ After this you must use @code{COMMIT} to store your changes to disk or @code{ROLLBACK} if you want to ignore the changes you have made since the beginning of your transaction. -If you want to switch from @code{AUTOCOMMIT} mode for one serie of -statements, you can use the @code{BEGIN} or @code{BEGIN WORK} statement. +If you want to switch from @code{AUTOCOMMIT} mode for one series of +statements, you can use the @code{BEGIN} or @code{BEGIN WORK} statement: @example BEGIN; @@ -20725,7 +20747,7 @@ stored at once, independent of the status of the @code{autocommit} mode. @findex LOCK TABLES @findex UNLOCK TABLES @node LOCK TABLES, SET OPTION, COMMIT, Reference -@section @code{LOCK TABLES/UNLOCK TABLES} syntax +@section @code{LOCK TABLES/UNLOCK TABLES} Syntax @example LOCK TABLES tbl_name [AS alias] @{READ | [READ LOCAL] | [LOW_PRIORITY] WRITE@} @@ -20823,7 +20845,7 @@ that can take snapshots in time. @findex SET OPTION @node SET OPTION, GRANT, LOCK TABLES, Reference -@section @code{SET} syntax +@section @code{SET} Syntax @example SET [OPTION] SQL_VALUE_OPTION= value, ... @@ -20875,7 +20897,7 @@ ODBC programs like Access. @item AUTOCOMMIT= 0 | 1 If set to @code{1} all changes to a table will be done at once. To start -an multi command transaction you have to use the @code{BEGIN} +a multi-command transaction, you have to use the @code{BEGIN} statement. @xref{COMMIT}. If set to @code{0} you have to use @code{COMMIT} / @code{ROLLBACK} to accept/revoke that transaction. @xref{COMMIT}. Note that when you change from not @code{AUTOCOMMIT} mode to @@ -20888,7 +20910,7 @@ If set to @code{1}, all temporary tables are stored on disk rather than in memory. This will be a little slower, but you will not get the error @code{The table tbl_name is full} for big @code{SELECT} operations that require a large temporary table. The default value for a new connection is -@code{0} (i.e., use in-memory temporary tables). +@code{0} (that is, use in-memory temporary tables). @item SQL_BIG_SELECTS = 0 | 1 If set to @code{0}, @strong{MySQL} will abort if a @code{SELECT} is attempted @@ -20905,14 +20927,14 @@ table locks early and will help in cases where it takes a long time to send the result set to the client. @item SQL_LOW_PRIORITY_UPDATES = 0 | 1 -If set to @code{1}, all @code{INSERT}, @code{UPDATE}, @code{DELETE} and +If set to @code{1}, all @code{INSERT}, @code{UPDATE}, @code{DELETE}, and and @code{LOCK TABLE WRITE} statements wait until there is no pending @code{SELECT} or @code{LOCK TABLE READ} on the affected table. @item SQL_MAX_JOIN_SIZE = value | DEFAULT -Don't allow @code{SELECT}'s that will probably need to examine more than +Don't allow @code{SELECT}s that will probably need to examine more than @code{value} row combinations. By setting this value, you can catch -@code{SELECT}'s where keys are not used properly and that would probably +@code{SELECT}s where keys are not used properly and that would probably take a long time. Setting this to a value other than @code{DEFAULT} will reset the @code{SQL_BIG_SELECTS} flag. If you set the @code{SQL_BIG_SELECTS} flag again, the @code{SQL_MAX_JOIN_SIZE} variable will be ignored. @@ -20920,7 +20942,7 @@ You can set a default value for this variable by starting @code{mysqld} with @code{-O max_join_size=#}. @item SQL_SAFE_MODE = 0 | 1 -If set to @code{1}, @strong{MySQL} will abort if a @code{UPDATE} or +If set to @code{1}, @strong{MySQL} will abort if an @code{UPDATE} or @code{DELETE} is attempted that doesn't use a key or @code{LIMIT} in the @code{WHERE} clause. This makes it possible to catch wrong updates when creating SQL commands by hand. @@ -20929,7 +20951,7 @@ when creating SQL commands by hand. The maximum number of records to return from @code{SELECT} statements. If a @code{SELECT} has a @code{LIMIT} clause, the @code{LIMIT} takes precedence over the value of @code{SQL_SELECT_LIMIT}. The default value for a new -connection is ``unlimited''. If you have changed the limit, the default value +connection is ``unlimited.'' If you have changed the limit, the default value can be restored by using a @code{SQL_SELECT_LIMIT} value of @code{DEFAULT}. @item SQL_LOG_OFF = 0 | 1 @@ -20972,7 +20994,7 @@ with the update log. @findex GRANT @findex REVOKE @node GRANT, CREATE INDEX, SET OPTION, Reference -@section @code{GRANT} and @code{REVOKE} syntax +@section @code{GRANT} and @code{REVOKE} Syntax @example GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...] @@ -20986,7 +21008,7 @@ REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...] FROM user_name [, user_name ...] @end example -@code{GRANT} is implemented in @strong{MySQL} 3.22.11 or later. For +@code{GRANT} is implemented in @strong{MySQL} Version 3.22.11 or later. For earlier @strong{MySQL} versions, the @code{GRANT} statement does nothing. The @code{GRANT} and @code{REVOKE} commands allow system administrators to @@ -21024,7 +21046,7 @@ DROP REFERENCES USAGE @end example @code{ALL} is a synonym for @code{ALL PRIVILEGES}. @code{REFERENCES} is not -yet implemented. @code{USAGE} is currently a synonym for ``no privileges''. +yet implemented. @code{USAGE} is currently a synonym for ``no privileges.'' It can be used when you want to create a user that has no privileges. To revoke the @strong{grant} privilege from a user, use a @code{priv_type} @@ -21036,16 +21058,16 @@ REVOKE GRANT OPTION ON ... FROM ...; The only @code{priv_type} values you can specify for a table are @code{SELECT}, @code{INSERT}, @code{UPDATE}, @code{DELETE}, @code{CREATE}, @code{DROP}, -@code{GRANT}, @code{INDEX} and @code{ALTER}. +@code{GRANT}, @code{INDEX}, and @code{ALTER}. The only @code{priv_type} values you can specify for a column (that is, when -you use a @code{column_list} clause) are @code{SELECT}, @code{INSERT} and +you use a @code{column_list} clause) are @code{SELECT}, @code{INSERT}, and @code{UPDATE}. You can set global privileges by using @code{ON *.*} syntax. You can set database privileges by using @code{ON db_name.*} syntax. If you specify @code{ON *} and you have a current database, you will set the privileges for -that database. (@strong{Warning:} If you specify @code{ON *} and you +that database. (@strong{WARNING:} If you specify @code{ON *} and you @emph{don't} have a current database, you will affect the global privileges!) In order to accommodate granting rights to users from arbitrary hosts, @@ -21061,7 +21083,7 @@ You can specify wild cards in the hostname. For example, for any host in the @code{144.155.166} class C subnet. The simple form @code{user} is a synonym for @code{user@@"%"}. -@strong{Note:} If you allow anonymous users to connect to the @strong{MySQL} +@strong{NOTE:} If you allow anonymous users to connect to the @strong{MySQL} server (which is the default), you should also add all local users as @code{user@@localhost} because otherwise the anonymous user entry for the local host in the @code{mysql.user} table will be used when the user tries to @@ -21074,7 +21096,7 @@ this query: mysql> SELECT Host,User FROM mysql.user WHERE User=''; @end example -For the moment, @code{GRANT} only supports host, table, database and +For the moment, @code{GRANT} only supports host, table, database, and column names up to 60 characters long. A user name can be up to 16 characters. @@ -21082,7 +21104,7 @@ The privileges for a table or column are formed from the logical OR of the privileges at each of the four privilege levels. For example, if the @code{mysql.user} table specifies that a user has a global @strong{select} privilege, this can't be denied by an -entry at the database, table or column level. +entry at the database, table, or column level. The privileges for a column can be calculated as follows: @@ -21094,7 +21116,7 @@ OR column privileges @end example In most cases, you grant rights to a user at only one of the privilege -levels, so life isn't normally as complicated as above. :) The details of the +levels, so life isn't normally as complicated as above. The details of the privilege-checking procedure are presented in @ref{Privilege system}. @@ -21105,13 +21127,13 @@ create @code{user} table entries, but @code{REVOKE} will not remove them; you must do that explicitly using @code{DELETE}. @cindex passwords, setting -In @strong{MySQL} 3.22.12 or later, +In @strong{MySQL} Version 3.22.12 or later, if a new user is created or if you have global grant privileges, the user's password will be set to the password specified by the @code{IDENTIFIED BY} clause, if one is given. If the user already had a password, it is replaced by the new one. -@strong{Warning:} If you create a new user but do not specify an +@strong{WARNING:} If you create a new user but do not specify an @code{IDENTIFIED BY} clause, the user has no password. This is insecure. Passwords can also be set with the @code{SET PASSWORD} command. @@ -21153,7 +21175,7 @@ server examines table and column privileges for all users and this will slow down @strong{MySQL} a bit. When @code{mysqld} starts, all privileges are read into memory. -Database, table and column privileges take effect at once and +Database, table, and column privileges take effect at once, and user-level privileges take effect the next time the user connects. Modifications to the grant tables that you perform using @code{GRANT} or @code{REVOKE} are noticed by the server immediately. @@ -21173,7 +21195,7 @@ In @strong{MySQL} privileges are given for an username + hostname combination and not only for an username. @item -ANSI SQL doesn't have global or database-level privileges and ANSI SQL +ANSI SQL doesn't have global or database-level privileges, and ANSI SQL doesn't support all privilege types that @strong{MySQL} supports. @strong{MySQL} doesn't support the ANSI SQL @code{TRIGGER}, @code{EXECUTE} or @code{UNDER} privileges. @@ -21204,7 +21226,7 @@ dropped only with explicit @code{REVOKE} commands or by manipulating the @cindex multi-part index @findex CREATE INDEX @node CREATE INDEX, DROP INDEX, GRANT, Reference -@section @code{CREATE INDEX} syntax +@section @code{CREATE INDEX} Syntax @example CREATE [UNIQUE|FULLTEXT] INDEX index_name ON tbl_name (col_name[(length)],... ) @@ -21255,7 +21277,7 @@ are available in @strong{MySQL} Version 3.23.23 and later. @findex DROP INDEX @node DROP INDEX, Comments, CREATE INDEX, Reference -@section @code{DROP INDEX} syntax +@section @code{DROP INDEX} Syntax @example DROP INDEX index_name ON tbl_name @@ -21322,7 +21344,7 @@ input from that file with @code{mysql < some-file}. @findex User-defined functions @findex Functions, user-defined @node CREATE FUNCTION, Reserved words, Comments, Reference -@section @code{CREATE FUNCTION/DROP FUNCTION} syntax +@section @code{CREATE FUNCTION/DROP FUNCTION} Syntax @example CREATE [AGGREGATE] FUNCTION function_name RETURNS @{STRING|REAL|INTEGER@} @@ -21665,7 +21687,7 @@ count variable for the table is not 0 and you are running with The table is checked for errors. @item If we found an error, try to do a fast repair (with sorting and without -recreating the data file) of the table. +re-creating the data file) of the table. @item If the repair fails because of an error in the data file (for example a duplicate key error), we try again, but this time we recreate the data file. @@ -21754,7 +21776,7 @@ Also when scanning a table it is very easy to read a constant number of records with each disk read. The security comes from if your computer crashes when writing to a -fixed size MyISAM file, @code{myisamchk} can easily figure out where each +fixed-size MyISAM file, @code{myisamchk} can easily figure out where each row starts and ends. So it can usually reclaim all records except the partially written one. Note that in @strong{MySQL} all indexes can always be reconstructed. @@ -22134,6 +22156,18 @@ SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*) * 2) @node BDB, , HEAP, Table types @section BDB or Berkeley_db tables +@menu +* BDB overview:: +* BDB install:: +* BDB start:: +* BDB characteristic:: +* BDB TODO:: +* BDB errors:: +@end menu + +@node BDB overview, BDB install, BDB, BDB +@subsection Overview over BDB tables + 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 @@ -22144,6 +22178,9 @@ will need Berkeley DB 3.1.14 or newer which can be downloaded from Sleepycat's download page at @uref{http://www.sleepycat.com/download.html}. +@node BDB install, BDB start, BDB overview, BDB +@subsection Installing BDB + To install Berkeley DB first uncompress the @code{BDB} distribution and follow the instructions in the README provided in the distiribution directory. Basically what you need to do is: @@ -22171,6 +22208,9 @@ Even if Berkeley DB is in itself very tested and reliably, the @strong{MySQL} interface is still very alpha, but we are actively improving and optimizing it to get it this stable real soon. +@node BDB start, BDB characteristic, BDB install, BDB +@subsection BDB startup options + If you are running with @code{AUTOCOMMIT=0} then your changes in @code{BDB} tables will not be updated until you execute @code{COMMIT}. Instead of commit you can execute @code{ROLLBACK} to forget your changes. @xref{COMMIT}. @@ -22197,7 +22237,8 @@ Normally you should start mysqld with @code{--bdb-recover} if you intend to use BDB tables. This may however give you problems when you try to start mysqld if the BDB log files are corrupted. @xref{Starting server}. -Some characteristic of @code{BDB} tables: +@node BDB characteristic, BDB TODO, BDB start, BDB +@subsection Some characteristic of @code{BDB} tables @itemize @bullet @item @@ -22249,22 +22290,32 @@ to rotate and remove old logs when using @code{BDB} tables than using other tabl types. @end itemize -Some things that we have to fix in the near future: +@node BDB TODO, BDB errors, BDB characteristic, BDB +@subsection Some things we need to fix for BDB in the near future @itemize @bullet @item -Remove the requirement that @code{BDB} tables must have a primary key. This -will be fixed by having an automatic hidden auto_increment column for -the tables without a primary key. -@item -@code{LOCK TABLES} should work as for other @strong{MySQL} tables. -@item -@code{ALTER TABLE} doesn't yet work. -@item @code{SHOW TABLE STATUS} doesn't yet provide that much information for BDB tables. +@item +Optimize performance. @end itemize +@node BDB errors, , BDB TODO, BDB +@subsection Errors you may get when using BDB tables + +If you are running in not @code{auto_commit} mode and delete a table you +are using you may get the following error messages in the @strong{MySQL} +error file: + +@example +001119 23:43:56 bdb: Missing log fileid entry +001119 23:43:56 bdb: txn_abort: Log undo failed for LSN: 1 3644744: Invalid +@end example + +This is not fatal but we don't recommend you to delete tables you are +using in not @code{auto_commit} mode. + @cindex tutorial @cindex terminal monitor, defined @cindex monitor, terminal @@ -22324,7 +22375,7 @@ To connect to the server, you'll usually need to provide a @strong{MySQL} user name when you invoke @code{mysql} and, most likely, a password. If the server runs on a machine other than the one where you log in, you'll also need to specify a hostname. Contact your administrator to find out what -connection parameters you should use to connect (i.e., what host, user name +connection parameters you should use to connect (that is, what host, user name and password to use). Once you know the proper parameters, you should be able to connect like this: @@ -22408,7 +22459,7 @@ This query illustrates several things about @code{mysql}: @itemize @bullet @item -A command normally consists of an SQL statement followed by a semicolon. +A command normally consists of a SQL statement followed by a semicolon. (There are some exceptions where a semicolon is not needed. @code{QUIT}, mentioned earlier, is one of them. We'll get to others later.) @@ -22605,7 +22656,7 @@ cancel the current command. @node Examples, Searching on two keys, Entering queries, Tutorial @section Examples of common queries -Here follows examples of how to solve some common problems with +Here are examples of how to solve some common problems with @strong{MySQL}. Some of the examples use the table @code{shop} to hold the price of each @@ -23948,7 +23999,7 @@ mysql> SELECT * FROM pet WHERE name REGEXP "[wW]"; Because a regular expression pattern matches if it occurs anywhere in the value, it is not necessary in the previous query to put a wild card on either side of the pattern to get it to match the entire value like it would be if -you used an SQL pattern. +you used a SQL pattern. To find names containing exactly five characters, use @samp{^} and @samp{$} to match the beginning and end of the name, and five instances of @samp{.} @@ -24675,7 +24726,7 @@ default character set in use to the client. The client will switch to use this character set for this connection. One should use @code{mysql_real_escape_string()} when escaping strings -for an SQL query. @code{mysql_real_escape_string()} is identical to the +for a SQL query. @code{mysql_real_escape_string()} is identical to the old @code{mysql_escape_string()} function, except that it takes the MYSQL connection handle as the first parameter. @@ -24792,8 +24843,8 @@ value + 1. This is an old legacy to be able to handle EOF.) You can find the following bitmask definitions in @file{m_ctype.h}: @example -#define _U 01 /* Upper case */ -#define _L 02 /* Lower case */ +#define _U 01 /* Uppercase */ +#define _L 02 /* Lowercase */ #define _N 04 /* Numeral (digit) */ #define _S 010 /* Spacing character */ #define _P 020 /* Punctuation */ @@ -24864,7 +24915,7 @@ the size in bytes of the largest character in the set. @node Table size, , Languages, Server @section How big MySQL tables can be -@strong{MySQL} 3.22 has a 4G limit on table size. With the new +@strong{MySQL} Version 3.22 has a 4G limit on table size. With the new @code{MyISAM} in @strong{MySQL} Version 3.23 the maximum table size is pushed up to 8 million terabytes (2 ^ 63 bytes). @@ -26196,7 +26247,7 @@ automatically changing the in-memory (HEAP) table to a disk-based increase the temporary table size by setting the @code{tmp_table_size} option to @code{mysqld}, or by setting the SQL option @code{SQL_BIG_TABLES} in the client program. @xref{SET OPTION, , -@code{SET OPTION}}. In @strong{MySQL} 3.20, the maximum size of the +@code{SET OPTION}}. In @strong{MySQL} Version 3.20, the maximum size of the temporary table was @code{record_buffer*16}, so if you are using this version, you have to increase the value of @code{record_buffer}. You can also start @code{mysqld} with the @code{--big-tables} option to always @@ -27514,7 +27565,7 @@ results. We would have used PHP or mod_perl instead but they were not available at that time. For graphical data we wrote a simple tool in @code{C} that can produce -GIFs based on the result of an SQL query (with some processing of the +GIFs based on the result of a SQL query (with some processing of the result). This is also dynamically executed from the Perl script that parses the @code{HTML} files. @@ -27874,7 +27925,7 @@ If you have problems due to insufficient memory in the client, use the retrieve the result set. Using @code{mysql} is very easy; Just start it as follows -@code{mysql database} or @code{mysql --user=user_name --password=your_password database}. Type an SQL statement, end it with @samp{;}, @samp{\g} or @samp{\G} +@code{mysql database} or @code{mysql --user=user_name --password=your_password database}. Type a SQL statement, end it with @samp{;}, @samp{\g} or @samp{\G} and press return/enter. @cindex command line options @@ -27919,7 +27970,7 @@ Print the output of a query (rows) vertically. Without this option you can also force this output by ending your statements with @code{\G}. @cindex force option @item -f, --force -Continue even if we get an SQL error. +Continue even if we get a SQL error. @cindex no-named-commands option @item -g, --no-named-commands Named commands are disabled. Use \* form only, or use named commands @@ -28021,7 +28072,7 @@ ego (\G) Send command to mysql server; Display result vertically print (\p) Print current command quit (\q) Quit mysql rehash (\#) Rebuild completion hash -source (\.) Execute an SQL script file. Takes a file name as an argument +source (\.) Execute a SQL script file. Takes a file name as an argument status (\s) Get status information from the server use (\u) Use another database. Takes database name as argument @end example @@ -28222,7 +28273,7 @@ meaning as the corresponding clauses for @code{LOAD DATA INFILE}. @item -F, --flush-logs Flush log file in the @strong{MySQL} server before starting the dump. @item -f, --force, -Continue even if we get an SQL error during a table dump. +Continue even if we get a SQL error during a table dump. @item -h, --host=.. Dump data from the @strong{MySQL} server on the named host. The default host is @code{localhost}. @@ -28438,7 +28489,7 @@ Verbose mode. Print out more information what the program does. Print version information and exit. @end table -Here follows a sample run of using @code{mysqlimport}: +Here is a sample run of using @code{mysqlimport}: @example $ mysql --version @@ -29113,7 +29164,7 @@ The following options are used if you start @code{myisamchk} with @table @code @item -D # or --data-file-length=# -Max length of data file (when recreating data file when it's 'full') +Max length of data file (when re-creating data file when it's 'full') @item -e or --extend-check Try to recover every possible row from the data file. Normally this will also find a lot of garbage rows; Don't use this option @@ -29596,7 +29647,7 @@ What percentage of the data file is used. What percentage of the data file is unused. @item Blocks/Record -Average number of blocks per record (i.e., how many links a fragmented +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}. @@ -29952,7 +30003,7 @@ 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} 3.21 you must use @code{mysqladmin refresh}. +If you are using @strong{MySQL} Version 3.21 you must use @code{mysqladmin refresh}. The above command does the following: @@ -30044,7 +30095,7 @@ functions. Consult this file to see how UDF calling conventions work. 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 an SQL function call, 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 @@ -30088,7 +30139,7 @@ The deinitialization function for @code{xxx()}. It should deallocate any memory allocated by the initialization function. @end table -When an SQL statement invokes @code{XXX()}, @strong{MySQL} calls the +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 @@ -30334,7 +30385,7 @@ parameter to @code{1}: 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 +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}: @@ -30763,7 +30814,7 @@ 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 provides a workaround for this bug! -Note that if you are using @strong{MySQL} 3.22, you must to apply the +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 @@ -30808,13 +30859,32 @@ problems if you have values > 127 in the column! @cindex ADO program @item ADO -When you access @strong{MyODBC} through ADO, @strong{MyODBC} doesn't -return a result for @code{SQLRowCount} for a query. We have tried to -track this down and according to ODBC logs, @strong{MyODBC} returns the -correct answer but the ADO client only gets -1 back from the ODBC -manager. Because of this we can only assume this is a bug in 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 -The only known workaround is to use a @code{SELECT COUNT(*)} statement +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 @@ -31119,7 +31189,7 @@ If you have one of the following symptoms, then it is probably a hardware 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 however try to reboot -your computer and check all cables to the keyboard :) +your computer and check all cables to the keyboard.) @item The mouse pointer doesn't move. @item @@ -31410,7 +31480,7 @@ an error that is related to your particular system libraries). If you have a problem with table 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 +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! @@ -31586,7 +31656,7 @@ 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} (i.e., the current +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} @@ -31796,7 +31866,7 @@ 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 +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 @@ -32045,7 +32115,7 @@ you. @item Start the server as user @code{user_name}, or, if you are using -@strong{MySQL} 3.22 or later, start @code{mysqld} as the Unix @code{root} +@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 Unix user @code{user_name} before accepting any connections. @@ -32646,7 +32716,7 @@ backup. To get a consistent backup, do a @code{LOCK TABLES} on the relevant tables. @xref{LOCK TABLES, , @code{LOCK TABLES}}. You only need a read lock; this allows other threads to continue to query the tables while you are making a copy of the files in the database directory. If you want to -make an SQL level backup of a table, you can use @code{SELECT INTO OUTFILE}. +make a SQL level backup of a table, you can use @code{SELECT INTO OUTFILE}. Another way to backup a database is to use the @code{mysqldump} program: @xref{mysqldump}. @@ -32712,7 +32782,7 @@ extension, @code{mysqld} will create log file names of type time you execute @code{mysqladmin refresh} or @code{mysqladmin flush-logs}, the @code{FLUSH LOGS} statement, or restart the server. -@strong{Note:} For the above scheme to work, you should NOT create +@strong{NOTE:} For the above scheme to work, you should NOT create own files with the same file name as the update log + some extensions that may be regarded as a number, in the directory used by the update log! @@ -32888,7 +32958,7 @@ files to @code{safe_mysqld} with @code{--log}, @code{--log-update} or @code{--log-slow-queries}. Otherwise, both servers may be trying to write to the same log file. -@strong{Warning}: Normally you should never have two servers that update +@strong{WARNING}: Normally you should never have two servers that update data in the same database! If your OS doesn't support fault-free system locking, this may lead to unpleasant surprises! @@ -33218,11 +33288,11 @@ Returns the error number for the most recently invoked @strong{MySQL} function. Returns the error message for the most recently invoked @strong{MySQL} function. @item @strong{mysql_real_escape_string()} @tab -Escapes special characters in a string for use in an SQL statement taking +Escapes special characters in a string for use in a SQL statement taking into account the current charset of the connection. @item @strong{mysql_escape_string()} @tab -Escapes special characters in a string for use in an SQL statement. +Escapes special characters in a string for use in a SQL statement. @item @strong{mysql_fetch_field()} @tab Returns the type of the next table field. @@ -33303,13 +33373,13 @@ Checks whether or not the connection to the server is working, reconnecting as necessary. @item @strong{mysql_query()} @tab -Executes an SQL query specified as a null-terminated string. +Executes a SQL query specified as a null-terminated string. @item @strong{mysql_real_connect()} @tab Connects to a @strong{MySQL} server. @item @strong{mysql_real_query()} @tab -Executes an SQL query specified as a counted string. +Executes a SQL query specified as a counted string. @item @strong{mysql_reload()} @tab Tells the server to reload the grant tables. @@ -33730,7 +33800,7 @@ None. Creates the database named by the @code{db} parameter. This function is deprecated. It is preferable to use @code{mysql_query()} -to issue an SQL @code{CREATE DATABASE} statement instead. +to issue a SQL @code{CREATE DATABASE} statement instead. @subsubheading Return values @@ -33823,7 +33893,7 @@ mysql_debug("d:t:O,/tmp/client.trace"); Drops the database named by the @code{db} parameter. This function is deprecated. It is preferable to use @code{mysql_query()} -to issue an SQL @code{DROP DATABASE} statement instead. +to issue a SQL @code{DROP DATABASE} statement instead. @subsubheading Return values @@ -34725,7 +34795,7 @@ or @code{unsigned int mysql_num_fields(MYSQL *mysql)} -The second form doesn't work on @strong{MySQL} 3.22.24 or newer. To pass a +The second form doesn't work on @strong{MySQL} Version 3.22.24 or newer. To pass a @code{MYSQL*} argument, you must use @code{unsigned int mysql_field_count(MYSQL *mysql)} instead. @@ -34857,6 +34927,7 @@ The specified group in the option file may contain the following options: @item @code{debug} @tab Debug options. @item @code{host} @tab Default host name. @item @code{init-command} @tab Command to execute when connecting to @strong{MySQL} server. Will automatically be re-executed when reconnecting. +@item @code{interactive-timeout} @tab Same as specifying @code{CLIENT_INTERACTIVE} to @code{mysql_real_connect()}. @xref{mysql_real_connect}. @item @code{password} @tab Default password. @item @code{pipe} @tab Use named pipes to connect to a @strong{MySQL} server on NT. @item @code{port} @tab Default port number. @@ -35032,11 +35103,14 @@ of the following flags in very special circumstances: @multitable @columnfractions .25 .7 @item @strong{Flag name} @tab @strong{Flag meaning} +@code{mysqld} to be more ODBC-friendly. +@item @code{CLIENT_COMPRESS} @tab Use compression protocol. @item @code{CLIENT_FOUND_ROWS} @tab Return the number of found (matched) rows, not the number of affected rows. +@item @code{CLIENT_IGNORE_SPACE} $tab Allow spaces after function names. Makes all functions names reserved words. +@item @code{CLIENT_INTERACTIVE} @tab Allow @code{interactive_timeout} seconds (instead of @code{wait_timeout} seconds) of inactivity before closing the connection. @item @code{CLIENT_NO_SCHEMA} @tab Don't allow the @code{db_name.tbl_name.col_name} syntax. This is for ODBC; It causes the parser to generate an error if you use that syntax, which is useful for trapping bugs in some ODBC programs. -@item @code{CLIENT_COMPRESS} @tab Use compression protocol. @item @code{CLIENT_ODBC} @tab The client is an ODBC client. This changes -@code{mysqld} to be more ODBC-friendly. +@item @code{CLIENT_SSL} @tab Use SSL (encrypted protocol). @end multitable @end itemize @@ -35124,7 +35198,7 @@ try reconnecting to the server before giving up. Encodes the string in @code{from} to an escaped SQL string, taking into account the current charset of the connection, that can be sent to the -server in an SQL statement, places the result in @code{to}, and adds a +server in a SQL statement, places the result in @code{to}, and adds a terminating null byte. Characters encoded are @code{NUL} (ASCII 0), @samp{\n}, @samp{\r}, @samp{\}, @samp{'}, @samp{"} and Control-Z (@pxref{Literals}). @@ -35223,7 +35297,7 @@ Asks the @strong{MySQL} server to reload the grant tables. The connected user must have the @strong{reload} privilege. This function is deprecated. It is preferable to use @code{mysql_query()} -to issue an SQL @code{FLUSH PRIVILEGES} statement instead. +to issue a SQL @code{FLUSH PRIVILEGES} statement instead. @subsubheading Return values @@ -35793,9 +35867,9 @@ Installation instructions for @strong{MySQL} Perl support are given in @multitable @columnfractions .3 .7 @item @code{connect} @tab Establishes a connection to a database server. @item @code{disconnect} @tab Disconnects from the database server. -@item @code{prepare} @tab Prepares an SQL statement for execution. +@item @code{prepare} @tab Prepares a SQL statement for execution. @item @code{execute} @tab Executes prepared statements. -@item @code{do} @tab Prepares and executes an SQL statement. +@item @code{do} @tab Prepares and executes a SQL statement. @item @code{quote} @tab Quotes string or @code{BLOB} values to be inserted. @item @code{fetchrow_array} @tab Fetches the next row as an array of fields. @item @code{fetchrow_arrayref} @tab Fetches next row as a reference array of fields. @@ -35889,7 +35963,7 @@ Use compressed communication between the client and server (@strong{MySQL} @item mysql_socket=/path/to/socket Specify the pathname of the Unix socket that is used to connect -to the server (@strong{MySQL} 3.21.15 or later). +to the server (@strong{MySQL} Version 3.21.15 or later). @end table Multiple modifiers may be given; each must be preceded by a semicolon. @@ -35928,7 +36002,7 @@ $rc = $dbh->disconnect; @findex DBI->prepare() @findex prepare() DBI method @item prepare($statement) -Prepares an SQL statement for execution by the database engine +Prepares a SQL statement for execution by the database engine and returns a statement handle @code{($sth)} which you can use to invoke the @code{execute} method. Typically you handle @code{SELECT} statements (and @code{SELECT}-like statements @@ -35959,7 +36033,7 @@ $rv = $sth->execute @findex DBI->do() @findex do() DBI method @item do($statement) -The @code{do} method prepares and executes an SQL statement and returns the +The @code{do} method prepares and executes a SQL statement and returns the number of rows affected. If no rows are affected, @code{do} returns @code{"0E0"}, which Perl treats as zero but regards as true. This method is generally used for non-@code{SELECT} statements which cannot be prepared in @@ -37017,7 +37091,7 @@ may sometimes produce bizarre results. For example search for the word "search" will produce no results in the above example. Word "search" is present in more than half of rows, and -as, such, is effectively treated as stopword (i.e. with semantical value +as, such, is effectively treated as stopword (that is, with semantical value zero). It is, really, the desired behaviour - natural language query should not return every second row in 1GB table. @@ -37032,7 +37106,7 @@ particular dataset}. @node Environment variables, Users, MySQL internals, Top @appendix Environment variables -Here follows a list of all environment variables that are used directly or +Here is a list of all environment variables that are used directly or indirectly by @strong{MySQL}. Most of these can also be found at other places in this manual. @@ -37854,15 +37928,15 @@ and "post-processing" on both fields, records and operations. @itemize @bullet @item @uref{http://www.mysql.com/Downloads/Contrib/mod_mysql_include_1.0.tar.gz, mod_mysql_include_1.0.tar.gz} -Apache module to include HTML from MySQL queries into your pages, and run update queries. -Originally written to implement a simple fast low-overhead banner-rotation system. -By Sasha Pachev. +Apache module to include HTML from 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://www.odbsoft.com/cook/sources.htm} -This package has various functions for generating html code from an SQL +This package has various functions for generating html code from a SQL table structure and for generating SQL statements (Select, Insert, Update, Delete) from an html form. You can build a complete forms -interface to an SQL database (query, add, update, delete) without any +interface to a SQL database (query, add, update, delete) without any programming! By Marc Beneteau, @email{marc@@odbsoft.com}. @item @uref{http://www.mysql.com/Downloads/Contrib/sqlhtml.tar.gz, sqlhtml.tar.gz} @@ -37871,7 +37945,7 @@ SQL/HTML is an HTML database manager for @strong{MySQL} using @code{DBI} 1.06. @item @uref{http://www.mysql.com/Downloads/Contrib/udmsearch-3.0.21.tar.gz, UdmSearch 3.0.22 (stable version)} @item @uref{http://www.mysql.com/Downloads/Contrib/udmsearch-3.1.3.tar.gz, UdmSearch 3.1.3 (development version)} @item @uref{http://search.mnoGo.ru, UdmSearch home page} -An SQL-based search engine for Internet. By +A SQL-based search engine for Internet. By Alexander I. Barkov @email{bar@@izhcom.ru}. @item @uref{http://www.mysql.com/Downloads/Contrib/wmtcl.doc, wmtcl.doc} @@ -38017,7 +38091,7 @@ use Access for all DB design and administration, and synchronize with your actual @strong{MySQL} server either way. Free of charge. See @uref{http://www.netdive.com/freebies/importsql/} for any updates. Created by Laurent Bossavit of NetDIVE. -@strong{Note:} Doesn't work with Access2! +@strong{NOTE:} Doesn't work with Access2! @item @uref{http://www.mysql.com/Downloads/Contrib/msql2mysqlWrapper-1.0.tgz, /msql2mysqlWrapper 1.0} A C wrapper from @code{mSQL} to @strong{MySQL}. By @email{alfred@@sb.net} @@ -38037,7 +38111,7 @@ By Steve Shreeve. @itemize @bullet @item @uref{http://www.mysql.com/Downloads/Contrib/emacs-sql-mode.tar.gz, emacs-sql-mode.tar.gz} -Raw port of an SQL mode for XEmacs. Supports completion. Original by +Raw port of a SQL mode for XEmacs. Supports completion. Original by Peter D. Pezaris @email{pez@@atlantic2.sbi.com} and partial @strong{MySQL} port by David Axmark. @@ -38173,13 +38247,22 @@ interested in. @end itemize @page -@cindex contributors, list of -@cindex credits @node Credits, News, Contrib, Top -@appendix Contributors to MySQL +@appendix Credits -Contributors to the @strong{MySQL} distribution are listed below, in -somewhat random order: +The following persons have helped us make MySQL what it's today + +@cindex developers, list of +@menu +* developers:: +* Contributors:: +@end menu + +@node developers, Contributors, Credits, Credits +@appendixsubsec Delevopers at MySQL AB + +Developers that are or has been employed by MySQL AB to work on MySQL. +(In about the order they started to work with us) @table @asis @item Michael (Monty) Widenius @@ -38208,24 +38291,23 @@ also Unireg, a curses-based application tool with many utilities. Porting of @code{mSQL} tools like @code{msqlperl}, @code{DBD}/@code{DBI} and @code{DB2mysql}. @item -Most parts of crash-me and the @strong{MySQL} benchmarks. +Most parts of crash-me and the foundation for the @strong{MySQL} benchmarks. @end itemize + @item David Axmark @itemize @bullet @item -Coordinator and main writer for the @strong{Reference Manual}, including -enhancements to @code{texi2html}. Also automatic website updating from -this manual. +Coordinator and intial main writer for the @strong{Reference Manual}, +including enhancements to @code{texi2html}. Also automatic website +updating from this manual. @item -Autoconf, Automake and @code{libtool} support. +Inital Autoconf, Automake and @code{libtool} support. @item The licensing stuff. @item Parts of all the text files. (Nowadays only the @file{README} is left. The rest ended up in the manual.) @item -Our Mail master. -@item Lots of testing of new features. @item Our in-house ``free'' software lawyer. @@ -38238,6 +38320,125 @@ only some parts of @code{mysys} are left. Someone for Monty to call in the middle of the night when he just got that new feature to work. :-) @end itemize + +@item Jani Tolonen +@itemize @bullet +@item +@code{mysqlimport} +@item +A lot of extensions to the @code{mysql} client. +@item +@code{procedure analyse} +@end itemize + +@item Sinisa Milivojevic +@itemize @bullet +@item +Compression (with @code{zlib}) to the client/server protocol. +@item +Perfect hashing for the lexical analyzer phase. +@item +The MySQLGUI client. +@item +Maintainer of mysql++. +@end itemize + +@item Tõnu Samual +@itemize @bullet +@item +Our security expert. +@item +Vio interface (foundation for the the encrypted client/server protocol). +@item +@code{RAID} support for @code{MyISAM} tables. +@end itemize + +@item Sasha Pachev +@itemize @bullet +@item +Replication. +@item +@code{SHOW TABLE CREATE}. +@item +mod_mysql_include +@item +cgi++ +@item +mysql-bench +@end itemize + +@item Matt Wagner +@itemize @bullet +@item +@strong{MySQL} test suite +@item +Our web master. +@end itemize + +@item Miguel Solórzano +@itemize @bullet +Winmysqladmin +@end itemize + +@item Timothy Smith +@itemize @bullet +@item +Dynamic character support. +@item +Responsible for @strong{MySQL} configure. +@end itemize + +@item Sergei A. Golubchick +@itemize @bullet +@item +Text search +@item +Added keys to the @code{MERGE} library. +@end itemize + +@item Jeremy Cole +@itemize @bullet +@item +Proofreading and editing this fine manual. +@item +@code{ALTER TABLE ... ORDER BY} +@end itemize + +@item John Dean +@itemize @bullet +@item +The new @strong{MySQL} GUI client. +@end itemize + +@item Indrek Sitan +@itemize @bullet +Desinger/programmer of our web interface. +@end itemize +@end table + +The following non developers are also working in/with MySQL AB + +@table @asis +@item +Hans Kierkegaard ; Resonsible for MySQL license handling. +@item +Antti Halonen ; Sales manager +@item +Jonas Norrman ; Handles licensing questions sent to @email{info@@mysql.com}. +@item +Erik Granberg ; Handles MySQL partners (and a lot of other stuff) +@item +Allan Larsson (The BOSS for TCX DataKonsult AB). +@end table + +@cindex contributors, list of +@node Contributors, , developers, Credits +@appendixsubsec Contributors to MySQL + +Contributors to the @strong{MySQL} distribution are listed below, in +somewhat random order: + +@table @asis @item Paul DuBois Help with making the Reference Manual correct and understandable. That includes rewriting Monty's and David's attempts at English into English @@ -38247,10 +38448,6 @@ The initial port to Win32/NT. @item Kim Aldale Helped to rewrite Monty's and David's early attempts at English into English. -@item Allan Larsson (The BOSS for TCX DataKonsult AB) -For all the time he has allowed Monty to spend on this ``maybe useful'' -tool (@strong{MySQL}). Dedicated user (and bug finder) of Unireg & -@strong{MySQL}. @item Per Eric Olsson For more or less constructive criticism and real testing of the dynamic record format. @@ -38260,7 +38457,7 @@ Win32 port with Borland compiler. @code{mysqlshutdown.exe} and @item David J. Hughes For the effort to make a shareware SQL database. We at TcX started with @code{mSQL}, but found that it couldn't satisfy our purposes so instead we -wrote an SQL interface to our application builder Unireg. @code{mysqladmin} +wrote a SQL interface to our application builder Unireg. @code{mysqladmin} and @code{mysql} are programs that were largely influenced by their @code{mSQL} counterparts. We have put a lot of effort into making the @strong{MySQL} syntax a superset of @code{mSQL}. Many of the APIs ideas are @@ -38295,6 +38492,8 @@ incredible help we got in managing the @strong{MySQL} mailing lists. Monty). @item Tim Bunce, Alligator Descartes For the @code{DBD} (Perl) interface. +@item Tim Bunce +Author of @code{mysqlhotcopy}. @item Andreas Koenig @email{a.koenig@@mind.de} For the Perl interface to @strong{MySQL}. @item Eugene Chan @email{eugene@@acenet.com.sg} @@ -38396,19 +38595,15 @@ The @file{libmysql.dll} library. Mysqlmanager, a Win32 GUI tool for administrating @strong{MySQL}. @item Curt Sampson @email{cjs@@portal.ca} Porting of MIT-pthreads to NetBSD/Alpha and NetBSD 1.3/i386. -@item Sinisa Milivojevic @email{sinisa@@coresinc.com} -Compression (with @code{zlib}) to the client/server protocol. -Perfect hashing for the lexical analyzer phase. @item Antony T. Curtis @email{antony.curtis@@olcs.net} Porting of @strong{MySQL} to OS/2. @item Martin Ramsch @email{m.ramsch@@computer.org} Examples in the @strong{MySQL} Tutorial. -@item Tim Bunce -Author of @code{mysqlhotcopy}. @item Steve Harvey For making @code{mysqlaccess} more secure. -@item Jeremy Cole @email{jeremy@@spaceapes.com} -Proofreading and editing this fine manual. +@item Konark IA-64 Centre of Persistent Systems Private Limited +@uref{http://www.pspl.co.in/konark/}. Help with the Win64 port of the +@strong{MySQL} server. @item Valueclick Inc. For sponsoring the optimize section in this manual. @end table @@ -38457,9 +38652,6 @@ Simple billing/license/support/copyright issues. ODBC and VisualC++ interface questions. @item Randy Harmon @email{rjharmon@@uptimecomputers.com} @code{DBD}, Linux, some SQL syntax questions. -@item Konark IA-64 Centre of Persistent Systems Private Limited -@uref{http://www.pspl.co.in/konark/}. Help with the Win64 port of the -@strong{MySQL} server. @end table @node News, Bugs, Credits, Top @@ -38531,6 +38723,31 @@ though, so Version 3.23 is not released as a stable version yet. @appendixsubsec Changes in release 3.23.28 @itemize @bullet @item +One can now specify @code{interactive-timeout} in the option file that +is read by @code{mysql_options()}. This makes it possible to force +programs that runs for a long time (like @code{mysqlhotcopy}) to use +@code{interactive_timeout} instead of @code{wait_timeout}. +@item +We now always print when the query was made and who made it in the 'slow +query log'. If you are using @code{--log-long-format} then also queries +that are not using indexes are printed. +@item +Fixed problem in @code{LEFT JOIN} which caused all columns in reference +table to be NULL. +@item +Fixed problem when using @code{NATURAL JOIN} without keys. +@item +Fixed bug when using a multi-part keys where the first part was of type +@code{TEXT} or @code{BLOB}. +@item +@code{DROP} of temporary tables wasn't stored in the update/binary log. +@item +Fixed bug where @code{SELECT DISTINCT * ... LIMIT #} only returned 1 row. +@item +Fixed a bug in the assembler code in strstr for sparc and cleaned up +the global.h header file to avoid a problem with bad aliasing with +the compiler submitted with RedHat 7.0. (Reported by Trond Eivind Glomsrød) +@item Fixed the @code{--skip-networking} works properly on NT. @item Fixed long outstanding bug in the @code{ISAM} tables when a row with a length @@ -38550,9 +38767,12 @@ Fixed fatal bug in @code{CREATE TEMPORARY TABLE ...SELECT ...}. @item Fixed problem with @code{CREATE TABLE .. SELECT NULL}. @item -Added status variables @code{large_file_support},@code{net_read_timeout}, +Added variables @code{large_file_support},@code{net_read_timeout}, @code{net_write_timeout} and @code{query_buffer_size} to @code{SHOW VARIABLES}. @item +Added status variables @code{created_tmp_files} and @code{sort_merge_passes} +to @code{SHOW STATUS}. +@item Fixed bug where we didn't allow an index name after the @code{FOREIGN KEY} definition. @item @@ -38789,7 +39009,7 @@ To make it possible to reliably dump and restore tables with @code{TIMESTAMP(X)} columns, @strong{MySQL} now reports columns with @code{X} other than 14 or 8 to be strings. @item -Changed sort order for latin1 as it was before @strong{MySQL} 3.23.23. +Changed sort order for latin1 as it was before @strong{MySQL} Version 3.23.23. Any table with @code{CHAR} columns that may have characters with ASCII values greater than 128 that was created or modified with 3.23.22 must be repaired! @@ -39552,7 +39772,7 @@ Changed the argument to @code{mysql_data_seek()} from @code{ulong} to @itemize @bullet @item Added @code{mysqld} option @code{-O lower_case_table_names=@{0|1@}} to allow -users to force table names to lower case. +users to force table names to lowercase. @item Added @code{SELECT ... INTO DUMPFILE}. @item @@ -39923,7 +40143,7 @@ Added aggregate UDF functions. Thanks to Andreas F. Bobak Some small changes to the join table optimizer to make some joins faster. @item @code{SELECT DISTINCT} is much faster; It uses the new @code{UNIQUE} -functionality in @code{MyISAM}. One difference compared to @strong{MySQL} 3.22 +functionality in @code{MyISAM}. One difference compared to @strong{MySQL} Version 3.22 is that the output of @code{DISTINCT} is not sorted anymore. @item All C client API macros are now functions to make shared libraries more @@ -40210,7 +40430,7 @@ Better support for SCO in @code{configure}. Added option @code{--defaults-file=###} to option file handling to force use of only one specific option file. @item -Extended @code{CREATE} syntax to ignore @strong{MySQL} 3.23 keywords. +Extended @code{CREATE} syntax to ignore @strong{MySQL} Version 3.23 keywords. @item Fixed deadlock problem when using @code{INSERT DELAYED} on a table locked with @code{LOCK TABLES}. @@ -40884,7 +41104,7 @@ Added @code{DATE_ADD()} and @code{DATE_SUB()} functions. @itemize @bullet @item -Fixed a lock problem (bug in @strong{MySQL} 3.22.1) when closing temporary tables. +Fixed a lock problem (bug in @strong{MySQL} Version 3.22.1) when closing temporary tables. @item Added missing @code{mysql_ping()} to the client library. @item @@ -40932,7 +41152,7 @@ Server error messages are now in @file{mysqld_error.h}. @item The server/client protocol now supports compression. @item -All bug fixes from @strong{MySQL} 3.21.32. +All bug fixes from @strong{MySQL} Version 3.21.32. @end itemize @node News-3.22.1, News-3.22.0, News-3.22.2, News-3.22.x @@ -40963,7 +41183,7 @@ within a row in an existing table. the week starts on Monday (some European countries). By default, @code{WEEK()} assumes the week starts on Sunday. @item -@code{TIME} columns weren't stored properly (bug in @strong{MySQL} 3.22.0). +@code{TIME} columns weren't stored properly (bug in @strong{MySQL} Version 3.22.0). @item @code{UPDATE} now returns information about how many rows were matched and updated, and how many ``warnings'' occurred when doing the update. @@ -41406,7 +41626,7 @@ Added command @code{variables} to @code{mysqladmin}. @item A lot of small changes to the binary releases. @item -Fixed a bug in the new protocol from @strong{MySQL} 3.21.20. +Fixed a bug in the new protocol from @strong{MySQL} Version 3.21.20. @item Changed @code{ALTER TABLE} to work with Windows (Windows can't rename open files). Also fixed a couple of small bugs in the Windows version. @@ -41630,7 +41850,7 @@ New portable benchmark suite with @code{DBD}, with test results from @code{mSQL} 2.0.3, @strong{MySQL}, PostgreSQL 6.2.1 and Solid server 2.2. @item @code{crash-me} is now included with the benchmarks; This is a Perl program -designed to find as many limits as possible in an SQL server. Tested with +designed to find as many limits as possible in a SQL server. Tested with @code{mSQL}, PostgreSQL, Solid and @strong{MySQL}. @item Fixed bug in range-optimizer that crashed @strong{MySQL} on some queries. @@ -41704,7 +41924,7 @@ Sorting on calculated @code{DOUBLE} values sorted on integer results instead. @code{mysql} no longer needs a database argument. @item Changed the place where @code{HAVING} should be. According to ANSI, it should -be after @code{GROUP BY} but before @code{ORDER BY}. @strong{MySQL} 3.20 +be after @code{GROUP BY} but before @code{ORDER BY}. @strong{MySQL} Version 3.20 incorrectly had it last. @item Added Sybase command @code{USE DATABASE} to start using another database. @@ -41788,7 +42008,7 @@ easily portable to Win95. Changed the @code{CREATE COLUMN} syntax of @code{NOT NULL} columns to be after the @code{DEFAULT} value, as specified in the ANSI SQL standard. This will make @code{mysqldump} with @code{NOT NULL} and default values incompatible with -@strong{MySQL} 3.20. +@strong{MySQL} Version 3.20. @item Added many function name aliases so the functions can be used with ODBC or ANSI SQL92 syntax. @@ -42737,7 +42957,7 @@ New functions: @code{INSERT()}, @code{RTRIM()}, @code{LTRIM()} and @code{AUTO_INCREMENT}). The format for @code{SHOW FIELDS FROM tbl_name} is changed so the @code{Type} column contains information suitable for @code{CREATE TABLE}. In previous releases, some @code{CREATE TABLE} -information had to be patched when recreating tables. +information had to be patched when re-creating tables. @item Some parser bugs from 3.19.5 (@code{BLOB} and @code{TIMESTAMP}) are corrected. @code{TIMESTAMP} now returns different date information depending on its @@ -42896,7 +43116,7 @@ Because @strong{MySQL} allows you to work with table types that doesn't support transactions (and thus can't @code{rollback} data) some things behaves a little different in @strong{MySQL} than in other SQL servers: (This is just to ensure that @strong{MySQL} never need to do a rollback -for an SQL command). This may be a little akward at times as column +for a SQL command). This may be a little akward at times as column values must be checked in the application, but this will actually give you a nice speed increase as it allows @strong{MySQL} to do some optimizations that otherwice would be very hard to do. @@ -42953,7 +43173,7 @@ Delayed insert handler has pending inserts to a table. @end enumerate @item -Before @strong{MySQL} 3.23.2 an @code{UPDATE} that updated a key with +Before @strong{MySQL} Version 3.23.2 an @code{UPDATE} that updated a key with a @code{WHERE} on the same key may have failed because the key was used to search for records and the same row may have been found multiple times: @@ -42970,7 +43190,7 @@ mysql> UPDATE tbl_name SET KEY=KEY+1 WHERE KEY+0 > 100; This will work because @strong{MySQL} will not use index on expressions in the @code{WHERE} clause. @item -Before @strong{MySQL} 3.23, all numeric types where treated as fixed-point +Before @strong{MySQL} Version 3.23, all numeric types where treated as fixed-point fields. That means you had to specify how many decimals a floating-point field shall have. All results were returned with the correct number of decimals. @@ -43045,6 +43265,9 @@ Allow users to change startup options without taking down the server. Help for all commands from the client. @item Secure connections (with SSL). +@item +Extend the optimizer to be able to optimize some +@code{ORDER BY key_name DESC} queries. @end itemize @node TODO future, TODO sometime, TODO MySQL 4.0, TODO @@ -43185,8 +43408,7 @@ Function @code{CASE}. Many more variables for @code{show status}. Counts for: @code{INSERT}/@code{DELETE}/@code{UPDATE} statements. Records reads and updated. Selects on 1 table and selects with joins. Mean number of -tables in select. Key buffer read/write hits (logical and real). -@code{ORDER BY}, @code{GROUP BY}, temporary tables created. +tables in select. Number of @code{ORDER BY} and @code{GROUP BY} queries. @item If you abort @code{mysql} in the middle of a query, you should open another connection and kill the old running query. @@ -43439,7 +43661,7 @@ If you run into problems with a new port, you may have to do some debugging of @strong{MySQL}! @xref{Debugging server}. -@strong{Note:} Before you start debugging @code{mysqld}, first get the test +@strong{NOTE:} Before you start debugging @code{mysqld}, first get the test programs @code{mysys/thr_alarm} and @code{mysys/thr_lock} to work. This will ensure that your thread installation has even a remote chance to work! @@ -43556,7 +43778,7 @@ If you have problems debugging threads with gdb, you should download gdb 5.x and try this instead. The new gdb version has very improved thread handling! -Here follows an example how to debug mysqld: +Here is an example how to debug mysqld: @example shell> gdb /usr/local/libexec/mysqld @@ -43740,7 +43962,7 @@ The currently recognized flag characters are: @multitable @columnfractions .1 .9 @item d @tab Enable output from DBUG_<N> macros for for the current state. May be followed by a list of keywords which selects output only for the DBUG macros with that keyword. A empty list of keywords implies output for all macros. -@item D @tab Delay after each debugger output line. The argument is the number of tenths of seconds to delay, subject to machine capabilities. I.E. @code{-#D,20} is delay two seconds. +@item D @tab Delay after each debugger output line. The argument is the number of tenths of seconds to delay, subject to machine capabilities. That is, @code{-#D,20} is delay two seconds. @item f @tab Limit debugging and/or tracing, and profiling to the list of named functions. Note that a null list will disable all functions. The appropriate "d" or "t" flags must still be given, this flag only limits their actions if they are enabled. @item F @tab Identify the source file name for each line of debug or trace output. @item i @tab Identify the process with the pid or thread id for each line of debug or trace output. @@ -43857,7 +44079,7 @@ for the application but generally it's very hard to say that a given lock type is better than another; Everything depends on the application and different part of the application may require different lock types. -Here follows some tips about locking in @strong{MySQL}: +Here are some tips about locking in @strong{MySQL}: On web application most applications do lots of selects, very few deletes, updates mainly on keys and inserts in some specific tables. @@ -43931,7 +44153,7 @@ If there are some small differences in the implementation, they may be fixed by changing @file{my_pthread.h} and @file{my_pthread.c}. @item Run @code{thr_alarm}. If it runs without any ``warning'', ``error'' or aborted -messages, you are on the right track. Here follows a successful run on +messages, you are on the right track. Here is a successful run on Solaris: @example Main thread: 1 diff --git a/acinclude.m4 b/acinclude.m4 index c8dab319c99..7f039dbf7af 100644 --- a/acinclude.m4 +++ b/acinclude.m4 @@ -41,14 +41,20 @@ then CXXFLAGS="$CXXFLAGS -Werror" fi mysql_cv_btype_last_arg_accept=none -[AC_TRY_COMPILE([#include <stdlib.h> +[AC_TRY_COMPILE([#if defined(inline) +#undef inline +#endif +#include <stdlib.h> #include <sys/types.h> #include <sys/socket.h> ], [int a = accept(1, (struct sockaddr *) 0, (socklen_t *) 0); return (a != 0);], mysql_cv_btype_last_arg_accept=socklen_t)] if test $mysql_cv_btype_last_arg_accept = none; then -[AC_TRY_COMPILE([#include <stdlib.h> +[AC_TRY_COMPILE([#if defined(inline) +#undef inline +#endif +#include <stdlib.h> #include <sys/types.h> #include <sys/socket.h> ], diff --git a/client/mysql.cc b/client/mysql.cc index cca7b8c6ce5..55990db5750 100644 --- a/client/mysql.cc +++ b/client/mysql.cc @@ -109,6 +109,8 @@ static HashTable ht; enum enum_info_type { INFO_INFO,INFO_ERROR,INFO_RESULT}; typedef enum enum_info_type INFO_TYPE; +const char *VER="11.4"; + static MYSQL mysql; /* The connection */ static bool info_flag=0,ignore_errors=0,wait_flag=0,quick=0, connected=0,opt_raw_data=0,unbuffered=0,output_tables=0, @@ -138,6 +140,7 @@ const char *default_dbug_option="d:t:o,/tmp/mysql.trace"; void tee_fprintf(FILE *file, const char *fmt, ...); void tee_fputs(const char *s, FILE *file); void tee_puts(const char *s, FILE *file); +void tee_putc(int c, FILE *file); /* The names of functions that actually do the manipulation. */ static int get_options(int argc,char **argv); static int com_quit(String *str,char*), @@ -437,8 +440,8 @@ CHANGEABLE_VAR changeable_vars[] = { static void usage(int version) { - printf("%s Ver 11.2 Distrib %s, for %s (%s)\n", - my_progname, MYSQL_SERVER_VERSION, SYSTEM_TYPE, MACHINE_TYPE); + printf("%s Ver %s Distrib %s, for %s (%s)\n", + my_progname, VER, MYSQL_SERVER_VERSION, SYSTEM_TYPE, MACHINE_TYPE); if (version) return; puts("Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB"); @@ -770,21 +773,23 @@ static int read_lines(bool execute_commands) line=_cgets(linebuffer); } #else - if (opt_outfile) { - if (glob_buffer.is_empty()) - fflush(OUTFILE); - fputs(glob_buffer.is_empty() ? "mysql> " : - !in_string ? " -> " : - in_string == '\'' ? - " '> " : " \"> ", OUTFILE); + if (opt_outfile) + { + if (glob_buffer.is_empty()) + fflush(OUTFILE); + fputs(glob_buffer.is_empty() ? "mysql> " : + !in_string ? " -> " : + in_string == '\'' ? + " '> " : " \"> ", OUTFILE); + } + line=readline((char*) (glob_buffer.is_empty() ? "mysql> " : + !in_string ? " -> " : + in_string == '\'' ? + " '> " : " \"> ")); + if (opt_outfile) + fprintf(OUTFILE, "%s\n", line); } - line=readline((char*) (glob_buffer.is_empty() ? "mysql> " : - !in_string ? " -> " : - in_string == '\'' ? - " '> " : " \"> ")); - if (opt_outfile) - fprintf(OUTFILE, "%s\n", line); #endif if (!line) // End of file { @@ -1635,7 +1640,7 @@ safe_put_field(const char *pos,ulong length) if (use_mb(default_charset_info) && (l = my_ismbchar(default_charset_info, pos, end))) { while (l--) - tee_fputs((const char *) *pos++, PAGER); + tee_putc(*pos++, PAGER); pos--; continue; } @@ -1649,7 +1654,7 @@ safe_put_field(const char *pos,ulong length) else if (*pos == '\\') tee_fputs("\\\\", PAGER); else - tee_fputs(pos, PAGER); + tee_putc(*pos, PAGER); } } } @@ -1865,7 +1870,7 @@ com_print(String *buffer,char *line __attribute__((unused))) tee_puts("--------------", stdout); (void) tee_fputs(buffer->c_ptr(), stdout); if (!buffer->length() || (*buffer)[buffer->length()-1] != '\n') - tee_fputs("\n", stdout); + tee_putc('\n', stdout); tee_puts("--------------\n", stdout); return 0; /* If empty buffer */ } @@ -2172,7 +2177,7 @@ com_status(String *buffer __attribute__((unused)), } if (status) { - tee_fputs("\n", stdout); + tee_putc('\n', stdout); tee_puts(status, stdout); } } @@ -2293,6 +2298,13 @@ void tee_puts(const char *s, FILE *file) } } +void tee_putc(int c, FILE *file) +{ + putc(c, file); + if (opt_outfile) + putc(c, OUTFILE); +} + #ifdef __WIN__ #include <time.h> #else diff --git a/client/thread_test.c b/client/thread_test.c index def3381eb89..c76489ed946 100644 --- a/client/thread_test.c +++ b/client/thread_test.c @@ -16,10 +16,6 @@ MA 02111-1307, USA */ #include <global.h> -#include <my_sys.h> -#include <my_pthread.h> -#include "mysql.h" -#include <getopt.h> #ifndef THREAD @@ -30,6 +26,11 @@ int main(int argc, char **argv) } #else +#include <my_sys.h> +#include <my_pthread.h> +#include "mysql.h" +#include <getopt.h> + static my_bool version,verbose; static uint thread_count,number_of_tests=1000,number_of_threads=2; static pthread_cond_t COND_thread_count; diff --git a/include/my_sys.h b/include/my_sys.h index c1c47663e12..98aab1062a2 100644 --- a/include/my_sys.h +++ b/include/my_sys.h @@ -187,7 +187,7 @@ extern char *get_charsets_dir(char *buf); extern ulong _my_cache_w_requests,_my_cache_write,_my_cache_r_requests, _my_cache_read; extern ulong _my_blocks_used,_my_blocks_changed; -extern uint my_file_opened,my_stream_opened; +extern ulong my_file_opened,my_stream_opened, my_tmp_file_created; extern my_bool key_cache_inited; /* Point to current my_message() */ diff --git a/libmysql/libmysql.c b/libmysql/libmysql.c index 5cdbf12dfc4..c48b3e61403 100644 --- a/libmysql/libmysql.c +++ b/libmysql/libmysql.c @@ -684,8 +684,8 @@ mysql_free_result(MYSQL_RES *result) static const char *default_options[]= {"port","socket","compress","password","pipe", "timeout", "user", "init-command", "host", "database", "debug", "return-found-rows", - "ssl_key" ,"ssl_cert" ,"ssl_ca" ,"ssl_capath", - "character-set-dir", "default-character-set", + "ssl-key" ,"ssl-cert" ,"ssl-ca" ,"ssl-capath", + "character-set-dir", "default-character-set", "interactive-timeout", NullS }; @@ -815,6 +815,9 @@ static void mysql_read_default_options(struct st_mysql_options *options, my_free(options->charset_name,MYF(MY_ALLOW_ZERO_PTR)); options->charset_name = my_strdup(opt_arg, MYF(MY_WME)); break; + case 19: /* Interactive-timeout */ + options->client_flag|=CLIENT_INTERACTIVE; + break; default: DBUG_PRINT("warning",("unknown option: %s",option[0])); } diff --git a/myisam/mi_key.c b/myisam/mi_key.c index d64a02ea5f5..bf2f5c292dd 100644 --- a/myisam/mi_key.c +++ b/myisam/mi_key.c @@ -181,10 +181,14 @@ uint _mi_pack_key(register MI_INFO *info, uint keynr, uchar *key, uchar *old, } else if (keyseg->flag & (HA_VAR_LENGTH | HA_BLOB_PART)) { - uint tmp_length=uint2korr(pos); pos+=2; + /* Length of key-part used with mi_rkey() always 2 */ + uint tmp_length=uint2korr(pos); + k_length-= 2+length; set_if_smaller(length,tmp_length); store_key_length_inc(key,length); - k_length-=2; + memcpy((byte*) key, pos+2,(size_t) length); + key+= length; + continue; } else if (keyseg->flag & HA_SWAP_KEY) { /* Numerical column */ diff --git a/mysys/Makefile.am b/mysys/Makefile.am index d64f4959746..a53fc1a545c 100644 --- a/mysys/Makefile.am +++ b/mysys/Makefile.am @@ -61,7 +61,7 @@ DEFS = -DDEFAULT_BASEDIR=\"$(prefix)\" \ -DSHAREDIR="\"$(MYSQLSHAREdir)\"" \ @DEFS@ -#getopt1.o: @THREAD_LOBJECTS@ +libmysys_a_DEPENDENCIES= @THREAD_LOBJECTS@ OMIT_DEPENDENCIES = pthread.h stdio.h __stdio.h stdlib.h __stdlib.h math.h\ __math.h time.h __time.h unistd.h __unistd.h types.h \ @@ -75,11 +75,25 @@ OMIT_DEPENDENCIES = pthread.h stdio.h __stdio.h stdlib.h __stdlib.h math.h\ # I hope this always does the right thing. Otherwise this is only test programs FLAGS=$(DEFS) $(INCLUDES) $(CPPFLAGS) $(CFLAGS) +# +# The CP .. RM stuff is to avoid problems with some compilers (like alpha ccc) +# which automaticly removes the object files you use to compile a final program +# + test_thr_alarm: thr_alarm.c $(LIBRARIES) - $(LINK) $(FLAGS) -DMAIN $(srcdir)/thr_alarm.c $(LDADD) $(LIBS) + $(CP) $(srcdir)/thr_alarm.c ./test_thr_alarm.c + $(LINK) $(FLAGS) -DMAIN ./test_thr_alarm.c $(LDADD) $(LIBS) + $(RM) ./test_thr_alarm.* test_thr_lock: thr_lock.c $(LIBRARIES) - $(LINK) $(FLAGS) -DMAIN $(srcdir)/thr_lock.c $(LDADD) $(LIBS) + $(CP) $(srcdir)/thr_lock.c test_thr_lock.c + $(LINK) $(FLAGS) -DMAIN ./test_thr_lock.c $(LDADD) $(LIBS) + $(RM) ./test_thr_lock.* + +test_vsnprintf: my_vsnprintf.c $(LIBRARIES) + $(CP) $(srcdir)/my_vsnprintf.c test_vsnprintf.c + $(LINK) $(FLAGS) -DMAIN ./test_vsnprintf.c $(LDADD) $(LIBS) + $(RM) test_vsnprintf.* test_dir: test_dir.c $(LIBRARIES) $(LINK) $(FLAGS) -DMAIN $(srcdir)/test_dir.c $(LDADD) $(LIBS) @@ -87,9 +101,6 @@ test_dir: test_dir.c $(LIBRARIES) test_charset: test_charset.c $(LIBRARIES) $(LINK) $(FLAGS) -DMAIN $(srcdir)/test_charset.c $(LDADD) $(LIBS) -test_vsnprintf: my_vsnprintf.c $(LIBRARIES) - $(LINK) $(FLAGS) -DMAIN $(srcdir)/my_vsnprintf.c $(LDADD) $(LIBS) - test_hash: test_hash.c $(LIBRARIES) $(LINK) $(FLAGS) -DMAIN $(srcdir)/test_dir.c $(LDADD) $(LIBS) diff --git a/mysys/mf_tempfile.c b/mysys/mf_tempfile.c index c3fccda09bf..c9b60c87ef3 100644 --- a/mysys/mf_tempfile.c +++ b/mysys/mf_tempfile.c @@ -170,5 +170,7 @@ File create_temp_file(char *to, const char *dir, const char *prefix, } } #endif + if (file >= 0) + thread_safe_increment(my_tmp_file_created,&THR_LOCK_open); DBUG_RETURN(file); } diff --git a/mysys/my_static.c b/mysys/my_static.c index db942d96db9..f1339877273 100644 --- a/mysys/my_static.c +++ b/mysys/my_static.c @@ -30,7 +30,7 @@ my_string home_dir=0,my_progname=0; char NEAR curr_dir[FN_REFLEN]= {0}, NEAR home_dir_buff[FN_REFLEN]= {0}; -uint my_stream_opened=0,my_file_opened=0; +ulong my_stream_opened=0,my_file_opened=0, my_tmp_file_created=0; int NEAR my_umask=0664, NEAR my_umask_dir=0777; #ifndef THREAD int NEAR my_errno=0; diff --git a/scripts/safe_mysqld.sh b/scripts/safe_mysqld.sh index 615670326bb..315859530b5 100644 --- a/scripts/safe_mysqld.sh +++ b/scripts/safe_mysqld.sh @@ -19,22 +19,44 @@ case "$1" in ;; esac -# Parse arguments to see if caller wants the pid_file somewhere else. parse_arguments() { - for arg in "$@"; do - case $arg in - --datadir=*) DATADIR=`echo "$arg" | sed -e "s;--datadir=;;"` ;; + # We only need to pass arguments through to the server if we don't + # handle them here. So, we collect unrecognized options (passed on + # the command line) into the args variable. + pick_args= + if test "$1" = PICK-ARGS-FROM-ARGV + then + pick_args=1 + shift + fi + + for arg do + case "$arg" in + # these get passed explicitly to mysqld + --basedir=*) MY_BASEDIR_VERSION=`echo "$arg" | sed -e "s;--basedir=;;"` ;; + --datadir=*) DATADIR=`echo "$arg" | sed -e "s;--datadir=;;"` ;; --pid-file=*) pid_file=`echo "$arg" | sed -e "s;--pid-file=;;"` ;; - --socket=*) MYSQL_UNIX_PORT=`echo "$arg" | sed -e "s;--socket=;;"` ;; - --port=*) MYSQL_TCP_PORT=`echo "$arg" | sed -e "s;--port=;;"` ;; - --log=*) log=`echo "$arg" | sed -e "s;--log=;;"` ;; - --basedir=*) MY_BASEDIR_VERSION=`echo "$arg" | sed -e "s;--basedir=;;"` ;; - --user=*) user=`echo "$arg" | sed -e "s;--user=;;"` ;; - --ledir=*) ledir=`echo "$arg" | sed -e "s;--ledir=;;"` ;; - --err-log=*) err_log=`echo "$arg" | sed -e "s;--err-log=;;"` ;; + --user=*) user=`echo "$arg" | sed -e "s;--user=;;"` ;; + + # these two might have been set in a [safe_mysqld] section of my.cnf + # they get passed via environment variables to safe_mysqld + --socket=*) MYSQL_UNIX_PORT=`echo "$arg" | sed -e "s;--socket=;;"` ;; + --port=*) MYSQL_TCP_PORT=`echo "$arg" | sed -e "s;--port=;;"` ;; + + # safe_mysqld-specific options - must be set in my.cnf ([safe_mysqld])! + --ledir=*) ledir=`echo "$arg" | sed -e "s;--ledir=;;"` ;; + --err-log=*) err_log=`echo "$arg" | sed -e "s;--err-log=;;"` ;; --open-files=*) open_files=`echo "$arg" | sed -e "s;--open-files=;;"` ;; - --core-file-size*) core_file_size=`echo "$arg" | sed -e "s;--core_file_size=;;"` ;; + --core-file-size=*) core_file_size=`echo "$arg" | sed -e "s;--core_file_size=;;"` ;; --timezone=*) TZ=`echo "$arg" | sed -e "s;--timezone=;;"` ; export TZ; ;; + *) + if test -n "$pick_args" + then + # This sed command makes sure that any special chars are quoted, + # so the arg gets passed exactly to the server. + args="$args "`echo "$arg" | sed -e 's,\([^a-zA-Z0-9_.-]\),\\\\\1,g'` + fi + ;; esac done } @@ -60,26 +82,32 @@ else ledir=@libexecdir@ fi -pid_file=$DATADIR/`@HOSTNAME@`.pid MYSQL_UNIX_PORT=${MYSQL_UNIX_PORT:-@MYSQL_UNIX_ADDR@} MYSQL_TCP_PORT=${MYSQL_TCP_PORT:-@MYSQL_TCP_PORT@} -log=$DATADIR/`@HOSTNAME@`.log -err_log=$DATADIR/`@HOSTNAME@`.err user=@MYSQLD_USER@ +# these rely on $DATADIR by default, so we'll set them later on +pid_file= +err_log= + # Get first arguments from the my.cfg file, groups [mysqld] and [safe_mysqld] # and then merge with the command line arguments -if test -x ./bin/my_print_defaults; then +if test -x ./bin/my_print_defaults +then print_defaults="./bin/my_print_defaults" -elif test -x @bindir@/my_print_defaults; then +elif test -x @bindir@/my_print_defaults +then print_defaults="@bindir@/my_print_defaults" -elif test -x @bindir@/mysql_print_defaults; then +elif test -x @bindir@/mysql_print_defaults +then print_defaults="@bindir@/mysql_print_defaults" else print_defaults="my_print_defaults" fi + +args= parse_arguments `$print_defaults $defaults mysqld safe_mysqld` -parse_arguments "$@" +parse_arguments PICK-ARGS-FROM-ARGV "$@" if test ! -x $ledir/mysqld then @@ -87,23 +115,28 @@ then echo "Please do a cd to the mysql installation directory and restart" echo "this script from there as follows:" echo "./bin/safe_mysqld". - exit 1; + exit 1 fi +test -z "$pid_file" && pid_file=$DATADIR/`@HOSTNAME@`.pid +test -z "$err_log" && err_log=$DATADIR/`@HOSTNAME@`.err + +export MYSQL_UNIX_PORT +export MYSQL_TCP_PORT + NOHUP_NICENESS="nohup" if test -w / then NOHUP_NICENESS=`nohup nice 2>&1` - if test $? -eq 0 && test x"$NOHUP_NICENESS" != x0 && nice --1 echo foo > /dev/null 2>&1; then + if test $? -eq 0 && test x"$NOHUP_NICENESS" != x0 && nice --1 echo foo > /dev/null 2>&1 + then NOHUP_NICENESS="nice --$NOHUP_NICENESS nohup" else NOHUP_NICENESS="nohup" fi fi -export MYSQL_UNIX_PORT -export MYSQL_TCP_PORT if test -w / then # If we are root, change the err log to the right user. @@ -130,7 +163,7 @@ then then # The pid contains a mysqld process echo "A mysqld process already exists" echo "A mysqld process already exists at " `date` >> $err_log - exit 1; + exit 1 fi fi rm -f $pid_file @@ -140,7 +173,7 @@ then echo "Fatal error: Can't remove the pid file: $pid_file at " `date` >> $err_log echo "Please remove it manually and start $0 again" echo "mysqld daemon not started" - exit 1; + exit 1 fi fi @@ -164,16 +197,17 @@ echo "`date +'%y%m%d %H:%M:%S mysqld started'`" >> $err_log while true do rm -f $MYSQL_UNIX_PORT $pid_file # Some extra safety - if test "$#" -eq 0 + if test -z "$args" then - (trap "" 1 ; exec $NOHUP_NICENESS $ledir/mysqld $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR --user=$user --pid-file=$pid_file @MYSQLD_DEFAULT_SWITCHES@ >> $err_log 2>&1 ) + $NOHUP_NICENESS $ledir/mysqld $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR --user=$user --pid-file=$pid_file @MYSQLD_DEFAULT_SWITCHES@ >> $err_log 2>&1 else - (trap "" ; exec $NOHUP_NICENESS $ledir/mysqld $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR --user=$user --pid-file=$pid_file @MYSQLD_DEFAULT_SWITCHES@ "$@" >> $err_log 2>&1 ) + eval "$NOHUP_NICENESS $ledir/mysqld $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR --user=$user --pid-file=$pid_file @MYSQLD_DEFAULT_SWITCHES@ $args >> $err_log 2>&1" fi if test ! -f $pid_file # This is removed if normal shutdown then - break; + break fi + if @IS_LINUX@ then # Test if one process was hanging. @@ -201,6 +235,7 @@ do I=`expr $I + 1` done fi + echo "`date +'%y%m%d %H:%M:%S mysqld restarted'`" | tee -a $err_log done diff --git a/sql-bench/Results/ATIS-mysql-Linux_2.2.13_SMP_alpha b/sql-bench/Results/ATIS-mysql-Linux_2.2.13_SMP_alpha index b50e5bf75fc..05c922f77bf 100644 --- a/sql-bench/Results/ATIS-mysql-Linux_2.2.13_SMP_alpha +++ b/sql-bench/Results/ATIS-mysql-Linux_2.2.13_SMP_alpha @@ -1,4 +1,4 @@ -Testing server 'MySQL 3.23.25 beta' at 2000-10-13 0:30:52 +Testing server 'MySQL 3.23.28 gamma' at 2000-11-18 15:05:04 ATIS table test @@ -6,14 +6,14 @@ Creating tables Time for create_table (28): 0 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Inserting data -Time to insert (9768): 4 wallclock secs ( 0.66 usr 0.60 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time to insert (9768): 3 wallclock secs ( 0.68 usr 0.60 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Retrieving data -Time for select_simple_join (500): 2 wallclock secs ( 0.64 usr 0.38 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for select_join (200): 13 wallclock secs ( 4.22 usr 3.17 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for select_distinct (800): 11 wallclock secs ( 1.68 usr 1.02 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for select_group (2800): 10 wallclock secs ( 1.54 usr 0.65 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_simple_join (500): 2 wallclock secs ( 0.62 usr 0.39 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_join (200): 12 wallclock secs ( 4.31 usr 3.11 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_distinct (800): 11 wallclock secs ( 1.72 usr 0.95 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_group (2800): 10 wallclock secs ( 1.58 usr 0.66 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Removing tables Time to drop_table (28): 0 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Total time: 40 wallclock secs ( 8.74 usr 5.83 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Total time: 38 wallclock secs ( 8.93 usr 5.72 sys + 0.00 cusr 0.00 csys = 0.00 CPU) diff --git a/sql-bench/Results/RUN-mysql-Linux_2.2.13_SMP_alpha b/sql-bench/Results/RUN-mysql-Linux_2.2.13_SMP_alpha index a4e9f92a9e3..6cd4861215b 100644 --- a/sql-bench/Results/RUN-mysql-Linux_2.2.13_SMP_alpha +++ b/sql-bench/Results/RUN-mysql-Linux_2.2.13_SMP_alpha @@ -1,86 +1,92 @@ Benchmark DBD suite: 2.9 -Date of test: 2000-10-13 0:30:52 +Date of test: 2000-11-18 18:20:34 Running tests on: Linux 2.2.13-SMP alpha Arguments: Comments: Alpha DS20 2x500 MHz, 2G memory, key_buffer=16M; gcc 2.95.2 + ccc Limits from: -Server version: MySQL 3.23.25 beta +Server version: MySQL 3.23.28 gamma -ATIS: Total time: 40 wallclock secs ( 8.74 usr 5.83 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -alter-table: Total time: 435 wallclock secs ( 0.29 usr 0.17 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -big-tables: Total time: 41 wallclock secs ( 8.36 usr 10.28 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -connect: Total time: 76 wallclock secs (34.42 usr 12.79 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -create: Total time: 135 wallclock secs (10.37 usr 4.56 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -insert: Total time: 1756 wallclock secs (370.65 usr 171.18 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -select: Total time: 1982 wallclock secs (130.60 usr 108.91 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -wisconsin: Total time: 19 wallclock secs ( 3.67 usr 2.69 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +ATIS: Total time: 38 wallclock secs ( 8.93 usr 5.72 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +alter-table: Total time: 375 wallclock secs ( 0.30 usr 0.15 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +big-tables: Total time: 42 wallclock secs ( 9.09 usr 11.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +connect: Total time: 77 wallclock secs (34.21 usr 17.98 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +create: Total time: 134 wallclock secs (10.66 usr 4.31 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +insert: Total time: 1851 wallclock secs (450.58 usr 224.15 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +select: Total time: 1215 wallclock secs (57.43 usr 28.03 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +wisconsin: Total time: 19 wallclock secs ( 3.77 usr 2.78 sys + 0.00 cusr 0.00 csys = 0.00 CPU) All 8 test executed successfully Totals per operation: Operation seconds usr sys cpu tests -alter_table_add 238.00 0.17 0.07 0.00 992 -alter_table_drop 189.00 0.06 0.04 0.00 496 -connect 13.00 7.52 2.74 0.00 10000 -connect+select_1_row 16.00 7.90 3.25 0.00 10000 -connect+select_simple 16.00 7.94 3.21 0.00 10000 -count 53.00 0.04 0.01 0.00 100 -count_distinct 94.00 0.64 0.20 0.00 1000 -count_distinct_big 851.00 79.09 89.50 0.00 1020 -count_distinct_group 69.00 1.13 0.58 0.00 1000 -count_distinct_group_on_key 48.00 0.37 0.16 0.00 1000 -count_distinct_group_on_key_parts 69.00 1.10 0.62 0.00 1000 -count_group_on_key_parts 44.00 1.15 0.58 0.00 1000 -count_on_key 452.00 15.84 4.57 0.00 50100 -create+drop 13.00 2.72 1.19 0.00 10000 -create_MANY_tables 91.00 1.80 0.61 0.00 10000 +alter_table_add 205.00 0.17 0.07 0.00 992 +alter_table_drop 161.00 0.07 0.03 0.00 496 +connect 14.00 8.15 2.36 0.00 10000 +connect+select_1_row 17.00 8.55 3.39 0.00 10000 +connect+select_simple 16.00 8.31 3.17 0.00 10000 +count 49.00 0.03 0.01 0.00 100 +count_distinct 86.00 0.71 0.21 0.00 2000 +count_distinct_big 140.00 7.24 8.69 0.00 120 +count_distinct_group 72.00 1.07 0.63 0.00 1000 +count_distinct_group_on_key 42.00 0.45 0.15 0.00 1000 +count_distinct_group_on_key_parts 72.00 1.08 0.62 0.00 1000 +count_group_on_key_parts 39.00 0.99 0.61 0.00 1000 +count_on_key 454.00 15.83 4.45 0.00 50100 +create+drop 13.00 2.86 0.85 0.00 10000 +create_MANY_tables 91.00 1.85 0.61 0.00 10000 create_index 4.00 0.00 0.00 0.00 8 -create_key+drop 17.00 4.20 1.25 0.00 10000 +create_key+drop 17.00 4.39 1.33 0.00 10000 create_table 0.00 0.00 0.00 0.00 31 delete_all 11.00 0.00 0.00 0.00 12 -delete_all_many_keys 49.00 0.01 0.00 0.00 1 +delete_all_many_keys 49.00 0.01 0.01 0.00 1 delete_big 0.00 0.00 0.00 0.00 1 -delete_big_many_keys 49.00 0.01 0.00 0.00 128 -delete_key 5.00 0.68 0.58 0.00 10000 +delete_big_many_keys 49.00 0.01 0.01 0.00 128 +delete_key 4.00 0.65 0.54 0.00 10000 drop_index 4.00 0.00 0.00 0.00 8 drop_table 0.00 0.00 0.00 0.00 28 -drop_table_when_MANY_tables 7.00 0.69 0.60 0.00 10000 -insert 132.00 22.47 20.01 0.00 350768 -insert_duplicates 40.00 6.20 6.19 0.00 100000 -insert_key 95.00 8.49 5.48 0.00 100000 -insert_many_fields 14.00 0.34 0.12 0.00 2000 -insert_select_1_key 5.00 0.00 0.00 0.00 1 +drop_table_when_MANY_tables 7.00 0.63 0.62 0.00 10000 +insert 137.00 26.71 20.07 0.00 350768 +insert_duplicates 39.00 5.58 6.03 0.00 100000 +insert_key 93.00 8.08 5.73 0.00 100000 +insert_many_fields 14.00 0.34 0.16 0.00 2000 +insert_select_1_key 4.00 0.00 0.00 0.00 1 insert_select_2_keys 7.00 0.00 0.00 0.00 1 -min_max 23.00 0.03 0.00 0.00 60 -min_max_on_key 188.00 25.65 7.76 0.00 85000 -multiple_value_insert 7.00 2.08 0.05 0.00 100000 -order_by 51.00 21.06 22.46 0.00 10 -order_by_key 36.00 20.39 14.82 0.00 10 -outer_join 65.00 0.00 0.00 0.00 10 -outer_join_found 60.00 0.00 0.00 0.00 10 -outer_join_not_found 38.00 0.01 0.00 0.00 500 -outer_join_on_key 42.00 0.00 0.00 0.00 10 -select_1_row 3.00 0.38 1.04 0.00 10000 -select_2_rows 3.00 0.35 0.96 0.00 10000 -select_big 56.00 30.32 15.00 0.00 10080 -select_column+column 3.00 0.32 0.73 0.00 10000 -select_diff_key 175.00 0.24 0.05 0.00 500 -select_distinct 11.00 1.68 1.02 0.00 800 -select_group 51.00 1.59 0.66 0.00 2911 -select_group_when_MANY_tables 7.00 0.95 0.90 0.00 10000 -select_join 13.00 4.22 3.17 0.00 200 -select_key 141.00 74.40 21.54 0.00 200000 -select_key2 146.00 76.95 20.42 0.00 200000 -select_key_prefix 146.00 76.59 19.98 0.00 200000 -select_many_fields 27.00 8.01 10.15 0.00 2000 -select_range 269.00 9.47 5.61 0.00 410 -select_range_key2 19.00 6.47 2.37 0.00 25010 -select_range_prefix 20.00 6.54 2.29 0.00 25010 -select_simple 2.00 0.28 0.79 0.00 10000 -select_simple_join 2.00 0.64 0.38 0.00 500 -update_big 28.00 0.00 0.00 0.00 10 -update_of_key 44.00 2.93 2.56 0.00 50256 -update_of_key_big 19.00 0.04 0.03 0.00 501 -update_with_key 139.00 23.06 19.06 0.00 300000 -wisc_benchmark 4.00 1.75 0.93 0.00 114 -TOTALS 4533.00 566.96 316.29 0.00 1944607 +min_max 21.00 0.02 0.00 0.00 60 +min_max_on_key 186.00 25.42 7.61 0.00 85000 +multiple_value_insert 7.00 2.19 0.05 0.00 100000 +order_by_big 54.00 22.86 21.67 0.00 10 +order_by_big_key 37.00 22.01 14.79 0.00 10 +order_by_big_key2 37.00 22.02 14.86 0.00 10 +order_by_big_key_desc 37.00 22.03 14.87 0.00 10 +order_by_big_key_diff 52.00 23.11 21.71 0.00 10 +order_by_key 3.00 1.17 0.64 0.00 500 +order_by_key2_diff 5.00 2.05 1.25 0.00 500 +order_by_range 5.00 1.17 0.65 0.00 500 +outer_join 61.00 0.01 0.00 0.00 10 +outer_join_found 55.00 0.00 0.00 0.00 10 +outer_join_not_found 35.00 0.01 0.00 0.00 500 +outer_join_on_key 40.00 0.00 0.00 0.00 10 +select_1_row 3.00 0.41 1.01 0.00 10000 +select_2_rows 3.00 0.38 0.89 0.00 10000 +select_big 57.00 30.30 20.70 0.00 10080 +select_column+column 3.00 0.27 0.75 0.00 10000 +select_diff_key 154.00 0.23 0.06 0.00 500 +select_distinct 11.00 1.72 0.95 0.00 800 +select_group 52.00 1.62 0.67 0.00 2911 +select_group_when_MANY_tables 6.00 0.92 0.90 0.00 10000 +select_join 12.00 4.31 3.11 0.00 200 +select_key 137.00 75.62 20.87 0.00 200000 +select_key2 144.00 76.78 20.52 0.00 200000 +select_key_prefix 144.00 76.89 20.33 0.00 200000 +select_many_fields 28.00 8.73 10.85 0.00 2000 +select_range 228.00 8.51 5.59 0.00 410 +select_range_key2 20.00 6.50 2.29 0.00 25010 +select_range_prefix 18.00 6.46 2.33 0.00 25010 +select_simple 2.00 0.29 0.75 0.00 10000 +select_simple_join 2.00 0.62 0.39 0.00 500 +update_big 25.00 0.00 0.00 0.00 10 +update_of_key 47.00 3.41 2.97 0.00 50256 +update_of_key_big 18.00 0.04 0.04 0.00 501 +update_with_key 134.00 21.08 18.70 0.00 300000 +wisc_benchmark 4.00 1.92 0.94 0.00 114 +TOTALS 3797.00 574.84 294.06 0.00 1946237 diff --git a/sql-bench/Results/alter-table-mysql-Linux_2.2.13_SMP_alpha b/sql-bench/Results/alter-table-mysql-Linux_2.2.13_SMP_alpha index acf5c463223..2e2126c04c2 100644 --- a/sql-bench/Results/alter-table-mysql-Linux_2.2.13_SMP_alpha +++ b/sql-bench/Results/alter-table-mysql-Linux_2.2.13_SMP_alpha @@ -1,16 +1,16 @@ -Testing server 'MySQL 3.23.25 beta' at 2000-10-13 0:31:33 +Testing server 'MySQL 3.23.28 gamma' at 2000-11-18 15:05:42 Testing of ALTER TABLE Testing with 1000 columns and 1000 rows in 20 steps Insert data into the table -Time for insert (1000) 0 wallclock secs ( 0.06 usr 0.06 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for insert (1000) 1 wallclock secs ( 0.06 usr 0.04 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for alter_table_add (992): 238 wallclock secs ( 0.17 usr 0.07 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for alter_table_add (992): 205 wallclock secs ( 0.17 usr 0.07 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Time for create_index (8): 4 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Time for drop_index (8): 4 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for alter_table_drop (496): 189 wallclock secs ( 0.06 usr 0.04 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for alter_table_drop (496): 161 wallclock secs ( 0.07 usr 0.03 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Total time: 435 wallclock secs ( 0.29 usr 0.17 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Total time: 375 wallclock secs ( 0.30 usr 0.15 sys + 0.00 cusr 0.00 csys = 0.00 CPU) diff --git a/sql-bench/Results/big-tables-mysql-Linux_2.2.13_SMP_alpha b/sql-bench/Results/big-tables-mysql-Linux_2.2.13_SMP_alpha index 02962161846..8ab20319026 100644 --- a/sql-bench/Results/big-tables-mysql-Linux_2.2.13_SMP_alpha +++ b/sql-bench/Results/big-tables-mysql-Linux_2.2.13_SMP_alpha @@ -1,19 +1,19 @@ -Testing server 'MySQL 3.23.25 beta' at 2000-10-13 0:38:49 +Testing server 'MySQL 3.23.28 gamma' at 2000-11-18 15:11:57 Testing of some unusual tables All tests are done 1000 times with 1000 fields Testing table with 1000 fields Testing select * from table with 1 record -Time to select_many_fields(1000): 11 wallclock secs ( 3.96 usr 5.06 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time to select_many_fields(1000): 12 wallclock secs ( 4.18 usr 5.43 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Testing select all_fields from table with 1 record -Time to select_many_fields(1000): 16 wallclock secs ( 4.05 usr 5.09 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time to select_many_fields(1000): 16 wallclock secs ( 4.55 usr 5.42 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Testing insert VALUES() -Time to insert_many_fields(1000): 5 wallclock secs ( 0.31 usr 0.06 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time to insert_many_fields(1000): 5 wallclock secs ( 0.30 usr 0.08 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Testing insert (all_fields) VALUES() -Time to insert_many_fields(1000): 9 wallclock secs ( 0.03 usr 0.06 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time to insert_many_fields(1000): 9 wallclock secs ( 0.04 usr 0.08 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Total time: 41 wallclock secs ( 8.36 usr 10.28 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Total time: 42 wallclock secs ( 9.09 usr 11.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) diff --git a/sql-bench/Results/connect-mysql-Linux_2.2.13_SMP_alpha b/sql-bench/Results/connect-mysql-Linux_2.2.13_SMP_alpha index 86eceb339d5..bf89d8f6bed 100644 --- a/sql-bench/Results/connect-mysql-Linux_2.2.13_SMP_alpha +++ b/sql-bench/Results/connect-mysql-Linux_2.2.13_SMP_alpha @@ -1,30 +1,30 @@ -Testing server 'MySQL 3.23.25 beta' at 2000-10-13 0:39:31 +Testing server 'MySQL 3.23.28 gamma' at 2000-11-18 15:12:39 Testing the speed of connecting to the server and sending of data All tests are done 10000 times Testing connection/disconnect -Time to connect (10000): 13 wallclock secs ( 7.52 usr 2.74 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time to connect (10000): 14 wallclock secs ( 8.15 usr 2.36 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Test connect/simple select/disconnect -Time for connect+select_simple (10000): 16 wallclock secs ( 7.94 usr 3.21 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for connect+select_simple (10000): 16 wallclock secs ( 8.31 usr 3.17 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Test simple select -Time for select_simple (10000): 2 wallclock secs ( 0.28 usr 0.79 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_simple (10000): 2 wallclock secs ( 0.29 usr 0.75 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Testing connect/select 1 row from table/disconnect -Time to connect+select_1_row (10000): 16 wallclock secs ( 7.90 usr 3.25 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time to connect+select_1_row (10000): 17 wallclock secs ( 8.55 usr 3.39 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Testing select 1 row from table -Time to select_1_row (10000): 3 wallclock secs ( 0.38 usr 1.04 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time to select_1_row (10000): 3 wallclock secs ( 0.41 usr 1.01 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Testing select 2 rows from table -Time to select_2_rows (10000): 3 wallclock secs ( 0.35 usr 0.96 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time to select_2_rows (10000): 3 wallclock secs ( 0.38 usr 0.89 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Test select with aritmetic (+) -Time for select_column+column (10000): 3 wallclock secs ( 0.32 usr 0.73 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_column+column (10000): 3 wallclock secs ( 0.27 usr 0.75 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Testing retrieval of big records (65000 bytes) -Time to select_big (10000): 20 wallclock secs ( 9.73 usr 0.06 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time to select_big (10000): 19 wallclock secs ( 7.84 usr 5.67 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Total time: 76 wallclock secs (34.42 usr 12.79 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Total time: 77 wallclock secs (34.21 usr 17.98 sys + 0.00 cusr 0.00 csys = 0.00 CPU) diff --git a/sql-bench/Results/create-mysql-Linux_2.2.13_SMP_alpha b/sql-bench/Results/create-mysql-Linux_2.2.13_SMP_alpha index e0b355208d4..566dcce6077 100644 --- a/sql-bench/Results/create-mysql-Linux_2.2.13_SMP_alpha +++ b/sql-bench/Results/create-mysql-Linux_2.2.13_SMP_alpha @@ -1,18 +1,18 @@ -Testing server 'MySQL 3.23.25 beta' at 2000-10-13 0:40:47 +Testing server 'MySQL 3.23.28 gamma' at 2000-11-18 15:13:56 Testing the speed of creating and droping tables Testing with 10000 tables and 10000 loop count Testing create of tables -Time for create_MANY_tables (10000): 91 wallclock secs ( 1.80 usr 0.61 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for create_MANY_tables (10000): 91 wallclock secs ( 1.85 usr 0.61 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Accessing tables -Time to select_group_when_MANY_tables (10000): 7 wallclock secs ( 0.95 usr 0.90 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time to select_group_when_MANY_tables (10000): 6 wallclock secs ( 0.92 usr 0.90 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Testing drop -Time for drop_table_when_MANY_tables (10000): 7 wallclock secs ( 0.69 usr 0.60 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for drop_table_when_MANY_tables (10000): 7 wallclock secs ( 0.63 usr 0.62 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Testing create+drop -Time for create+drop (10000): 13 wallclock secs ( 2.72 usr 1.19 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for create_key+drop (10000): 17 wallclock secs ( 4.20 usr 1.25 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Total time: 135 wallclock secs (10.37 usr 4.56 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for create+drop (10000): 13 wallclock secs ( 2.86 usr 0.85 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for create_key+drop (10000): 17 wallclock secs ( 4.39 usr 1.33 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Total time: 134 wallclock secs (10.66 usr 4.31 sys + 0.00 cusr 0.00 csys = 0.00 CPU) diff --git a/sql-bench/Results/insert-mysql-Linux_2.2.13_SMP_alpha b/sql-bench/Results/insert-mysql-Linux_2.2.13_SMP_alpha index 3f0bc8835ae..ad448190c98 100644 --- a/sql-bench/Results/insert-mysql-Linux_2.2.13_SMP_alpha +++ b/sql-bench/Results/insert-mysql-Linux_2.2.13_SMP_alpha @@ -1,4 +1,4 @@ -Testing server 'MySQL 3.23.25 beta' at 2000-10-13 0:43:02 +Testing server 'MySQL 3.23.28 gamma' at 2000-11-18 15:16:11 Testing the speed of inserting data into 1 table and do some selects on it. The tests are done with a table that has 100000 rows. @@ -8,72 +8,78 @@ Creating tables Inserting 100000 rows in order Inserting 100000 rows in reverse order Inserting 100000 rows in random order -Time for insert (300000): 110 wallclock secs (19.18 usr 17.06 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for insert (300000): 116 wallclock secs (23.42 usr 16.99 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Testing insert of duplicates -Time for insert_duplicates (100000): 40 wallclock secs ( 6.20 usr 6.19 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for insert_duplicates (100000): 39 wallclock secs ( 5.58 usr 6.03 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Retrieving data from the table -Time for select_big (10:3000000): 35 wallclock secs (20.45 usr 14.85 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for order_by_key (10:3000000): 36 wallclock secs (20.39 usr 14.82 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for order_by (10:3000000): 51 wallclock secs (21.06 usr 22.46 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for select_diff_key (500:1000): 175 wallclock secs ( 0.24 usr 0.05 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for select_range_prefix (5010:42084): 11 wallclock secs ( 2.81 usr 0.93 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for select_range_key2 (5010:42084): 10 wallclock secs ( 2.78 usr 1.01 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for select_key_prefix (200000): 146 wallclock secs (76.59 usr 19.98 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for select_key (200000): 141 wallclock secs (74.40 usr 21.54 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for select_key2 (200000): 146 wallclock secs (76.95 usr 20.42 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_big (10:3000000): 37 wallclock secs (22.33 usr 14.93 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for order_by_big_key (10:3000000): 37 wallclock secs (22.01 usr 14.79 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for order_by_big_key_desc (10:3000000): 37 wallclock secs (22.03 usr 14.87 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for order_by_big_key2 (10:3000000): 37 wallclock secs (22.02 usr 14.86 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for order_by_big_key_diff (10:3000000): 52 wallclock secs (23.11 usr 21.71 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for order_by_big (10:3000000): 54 wallclock secs (22.86 usr 21.67 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for order_by_range (500:125750): 5 wallclock secs ( 1.17 usr 0.65 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for order_by_key (500:125750): 3 wallclock secs ( 1.17 usr 0.64 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for order_by_key2_diff (500:250500): 5 wallclock secs ( 2.05 usr 1.25 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_diff_key (500:1000): 154 wallclock secs ( 0.23 usr 0.06 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_range_prefix (5010:42084): 10 wallclock secs ( 2.79 usr 0.99 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_range_key2 (5010:42084): 11 wallclock secs ( 2.80 usr 1.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_key_prefix (200000): 144 wallclock secs (76.89 usr 20.33 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_key (200000): 137 wallclock secs (75.62 usr 20.87 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_key2 (200000): 144 wallclock secs (76.78 usr 20.52 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Test of compares with simple ranges -Time for select_range_prefix (20000:43500): 9 wallclock secs ( 3.73 usr 1.36 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for select_range_key2 (20000:43500): 9 wallclock secs ( 3.69 usr 1.36 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for select_group (111): 41 wallclock secs ( 0.05 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for min_max_on_key (15000): 10 wallclock secs ( 4.68 usr 1.32 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for min_max (60): 23 wallclock secs ( 0.03 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for count_on_key (100): 38 wallclock secs ( 0.04 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for count (100): 53 wallclock secs ( 0.04 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_range_prefix (20000:43500): 8 wallclock secs ( 3.67 usr 1.34 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_range_key2 (20000:43500): 9 wallclock secs ( 3.70 usr 1.29 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_group (111): 42 wallclock secs ( 0.04 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for min_max_on_key (15000): 9 wallclock secs ( 4.71 usr 1.26 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for min_max (60): 21 wallclock secs ( 0.02 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for count_on_key (100): 41 wallclock secs ( 0.04 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for count (100): 49 wallclock secs ( 0.03 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Time for count_distinct_big (20): 58 wallclock secs ( 0.00 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Testing update of keys with functions -Time for update_of_key (50000): 22 wallclock secs ( 2.91 usr 2.54 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for update_of_key_big (501): 19 wallclock secs ( 0.04 usr 0.03 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for update_of_key (50000): 26 wallclock secs ( 3.38 usr 2.96 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for update_of_key_big (501): 18 wallclock secs ( 0.04 usr 0.04 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Testing update with key -Time for update_with_key (300000): 139 wallclock secs (23.06 usr 19.06 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for update_with_key (300000): 134 wallclock secs (21.08 usr 18.70 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Testing update of all rows -Time for update_big (10): 28 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for update_big (10): 25 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Testing left outer join -Time for outer_join_on_key (10:10): 42 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for outer_join (10:10): 65 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for outer_join_found (10:10): 60 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for outer_join_not_found (500:10): 38 wallclock secs ( 0.01 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for outer_join_on_key (10:10): 40 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for outer_join (10:10): 61 wallclock secs ( 0.01 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for outer_join_found (10:10): 55 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for outer_join_not_found (500:10): 35 wallclock secs ( 0.01 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Testing INSERT INTO ... SELECT -Time for insert_select_1_key (1): 5 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for insert_select_1_key (1): 4 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Time for insert_select_2_keys (1): 7 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Time for drop table(2): 0 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Testing delete -Time for delete_key (10000): 5 wallclock secs ( 0.68 usr 0.58 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for delete_key (10000): 4 wallclock secs ( 0.65 usr 0.54 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Time for delete_all (12): 11 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Insert into table with 16 keys and with a primary key with 16 parts -Time for insert_key (100000): 95 wallclock secs ( 8.49 usr 5.48 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for insert_key (100000): 93 wallclock secs ( 8.08 usr 5.73 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Testing update of keys -Time for update_of_key (256): 22 wallclock secs ( 0.02 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for update_of_key (256): 21 wallclock secs ( 0.03 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Deleting rows from the table -Time for delete_big_many_keys (128): 49 wallclock secs ( 0.01 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for delete_big_many_keys (128): 49 wallclock secs ( 0.01 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Deleting everything from table -Time for delete_all_many_keys (1): 49 wallclock secs ( 0.01 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for delete_all_many_keys (1): 49 wallclock secs ( 0.01 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Inserting 100000 rows with multiple values -Time for multiple_value_insert (100000): 7 wallclock secs ( 2.08 usr 0.05 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for multiple_value_insert (100000): 7 wallclock secs ( 2.19 usr 0.05 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Time for drop table(1): 0 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Total time: 1756 wallclock secs (370.65 usr 171.18 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Total time: 1851 wallclock secs (450.58 usr 224.15 sys + 0.00 cusr 0.00 csys = 0.00 CPU) diff --git a/sql-bench/Results/select-mysql-Linux_2.2.13_SMP_alpha b/sql-bench/Results/select-mysql-Linux_2.2.13_SMP_alpha index 5b096dd8875..67dbf08fa05 100644 --- a/sql-bench/Results/select-mysql-Linux_2.2.13_SMP_alpha +++ b/sql-bench/Results/select-mysql-Linux_2.2.13_SMP_alpha @@ -1,23 +1,23 @@ -Testing server 'MySQL 3.23.25 beta' at 2000-10-13 1:12:21 +Testing server 'MySQL 3.23.28 gamma' at 2000-11-18 17:02:47 Testing the speed of selecting on keys that consist of many parts The test-table has 10000 rows and the test is done with 500 ranges. Creating table Inserting 10000 rows -Time to insert (10000): 3 wallclock secs ( 0.72 usr 0.58 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time to insert (10000): 3 wallclock secs ( 0.76 usr 0.64 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Testing big selects on the table -Time for select_big (70:17207): 1 wallclock secs ( 0.14 usr 0.09 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for select_range (410:1057904): 269 wallclock secs ( 9.47 usr 5.61 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for min_max_on_key (70000): 178 wallclock secs (20.97 usr 6.44 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for count_on_key (50000): 414 wallclock secs (15.80 usr 4.56 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_big (70:17207): 1 wallclock secs ( 0.13 usr 0.10 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_range (410:1057904): 228 wallclock secs ( 8.51 usr 5.59 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for min_max_on_key (70000): 177 wallclock secs (20.71 usr 6.35 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for count_on_key (50000): 413 wallclock secs (15.79 usr 4.45 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for count_group_on_key_parts (1000:0): 44 wallclock secs ( 1.15 usr 0.58 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for count_group_on_key_parts (1000:100000): 39 wallclock secs ( 0.99 usr 0.61 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Testing count(distinct) on the table -Time for count_distinct (1000:2000): 94 wallclock secs ( 0.64 usr 0.20 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for count_distinct_group_on_key (1000:6000): 48 wallclock secs ( 0.37 usr 0.16 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for count_distinct_group_on_key_parts (1000:100000): 69 wallclock secs ( 1.10 usr 0.62 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for count_distinct_group (1000:100000): 69 wallclock secs ( 1.13 usr 0.58 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Time for count_distinct_big (1000:10000000): 793 wallclock secs (79.09 usr 89.49 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Total time: 1982 wallclock secs (130.60 usr 108.91 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for count_distinct (2000:2000): 86 wallclock secs ( 0.71 usr 0.21 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for count_distinct_group_on_key (1000:6000): 42 wallclock secs ( 0.45 usr 0.15 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for count_distinct_group_on_key_parts (1000:100000): 72 wallclock secs ( 1.08 usr 0.62 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for count_distinct_group (1000:100000): 72 wallclock secs ( 1.07 usr 0.63 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for count_distinct_big (100:1000000): 82 wallclock secs ( 7.24 usr 8.68 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Total time: 1215 wallclock secs (57.43 usr 28.03 sys + 0.00 cusr 0.00 csys = 0.00 CPU) diff --git a/sql-bench/Results/wisconsin-mysql-Linux_2.2.13_SMP_alpha b/sql-bench/Results/wisconsin-mysql-Linux_2.2.13_SMP_alpha index f417d1b444c..4d467ff9045 100644 --- a/sql-bench/Results/wisconsin-mysql-Linux_2.2.13_SMP_alpha +++ b/sql-bench/Results/wisconsin-mysql-Linux_2.2.13_SMP_alpha @@ -1,14 +1,14 @@ -Testing server 'MySQL 3.23.25 beta' at 2000-10-13 1:45:23 +Testing server 'MySQL 3.23.28 gamma' at 2000-11-18 16:16:05 Wisconsin benchmark test Time for create_table (3): 0 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Inserting data -Time to insert (31000): 15 wallclock secs ( 1.91 usr 1.77 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time to insert (31000): 15 wallclock secs ( 1.85 usr 1.84 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Time to delete_big (1): 0 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) Running actual benchmark -Time for wisc_benchmark (114): 4 wallclock secs ( 1.75 usr 0.93 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for wisc_benchmark (114): 4 wallclock secs ( 1.92 usr 0.94 sys + 0.00 cusr 0.00 csys = 0.00 CPU) -Total time: 19 wallclock secs ( 3.67 usr 2.69 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Total time: 19 wallclock secs ( 3.77 usr 2.78 sys + 0.00 cusr 0.00 csys = 0.00 CPU) diff --git a/sql-bench/crash-me.sh b/sql-bench/crash-me.sh index 45bcd2d4a1c..394f64b85ab 100755 --- a/sql-bench/crash-me.sh +++ b/sql-bench/crash-me.sh @@ -292,7 +292,7 @@ $dbh->do("drop table crash_q"); report("truncate","truncate_table", "create table crash_q (a integer, b integer,c CHAR(10))", "truncate crash_q", - "drop table crash_q1"); + "drop table crash_q"); if ($dbh->do("create table crash_q (a integer, b integer,c CHAR(10))") && $dbh->do("create table crash_q1 (a integer, b integer,c CHAR(10) not null)")) diff --git a/sql-bench/limits/mysql-3.23.cfg b/sql-bench/limits/mysql-3.23.cfg index 186d1e740eb..759208ad713 100644 --- a/sql-bench/limits/mysql-3.23.cfg +++ b/sql-bench/limits/mysql-3.23.cfg @@ -1,4 +1,4 @@ -#This file is automaticly generated by crash-me 1.50 +#This file is automaticly generated by crash-me 1.51 NEG=yes # update of column= -column Need_cast_for_null=no # Need to cast NULL for arithmetic @@ -31,12 +31,12 @@ comment_--=yes # -- as comment (ANSI) comment_/**/=yes # /* */ as comment comment_//=no # // as comment (ANSI) compute=no # Compute -connections=30 # Simultaneous connections (installation default) +connections=101 # Simultaneous connections (installation default) constraint_check=no # Column constraints constraint_check_table=no # Table constraints constraint_null=yes # NULL constraint (SyBase style) crash_me_safe=yes # crash me safe -crash_me_version=1.50 # crash me version +crash_me_version=1.51 # crash me version create_default=yes # default value for column create_default_func=no # default value function for column create_if_not_exists=yes # create table if not exists @@ -315,7 +315,7 @@ intersect=no # intersect intersect_all=no # intersect all intersect_all_incompat=no # intersect all (incompatible lists) intersect_incompat=no # intersect (incompatible lists) -join_tables=31 # tables in join +join_tables=63 # tables in join left_outer_join=yes # left outer join left_outer_join_using=yes # left outer join using like_with_column=yes # column LIKE column @@ -327,7 +327,7 @@ max_char_size=255 # max char() size max_column_name=64 # column name length max_columns=3398 # Columns in table max_conditions=85660 # OR and AND in WHERE -max_expressions=859 # simple expressions +max_expressions=1837 # simple expressions max_index=32 # max index max_index_length=500 # index length max_index_name=64 # index name length @@ -337,7 +337,7 @@ max_index_varchar_part_length=255 # index varchar part length max_row_length=65534 # max table row length (without blobs) max_row_length_with_null=65502 # table row length with nulls (without blobs) max_select_alias_name=+512 # select alias name length -max_stack_expression=859 # stacked expressions +max_stack_expression=1837 # stacked expressions max_table_alias_name=+512 # table alias name length max_table_name=64 # table name length max_text_size=1048543 # max text or blob size @@ -361,7 +361,7 @@ null_in_index=yes # null in index null_in_unique=yes # null in unique index null_num_expr=yes # Is 1+NULL = NULL odbc_left_outer_join=yes # left outer join odbc style -operating_system=Linux 2.2.14-my-SMP i686 # crash-me tested on +operating_system=Linux 2.2.13-SMP alpha # crash-me tested on order_by=yes # Order by order_by_alias=yes # Order by alias order_by_function=yes # Order by function @@ -380,7 +380,7 @@ quote_with_"=yes # Allows ' and " as string markers remember_end_space=no # Remembers end space in char() remember_end_space_varchar=no # Remembers end space in varchar() rename_table=yes # rename table -repeat_string_size=1048576 # return string size from function +repeat_string_size=1047552 # return string size from function right_outer_join=yes # right outer join rowid=auto_increment # Type for row id select_constants=yes # Select constants @@ -389,7 +389,7 @@ select_limit2=yes # SELECT with LIMIT #,# select_string_size=1048565 # constant string size in SELECT select_table_update=no # Update with sub select select_without_from=yes # SELECT without FROM -server_version=MySQL 3.23.25 beta debug # server version +server_version=MySQL 3.23.28 gamma # server version simple_joins=yes # ANSI SQL simple joins storage_of_float=round # Storage of float values subqueries=no # subqueries @@ -398,6 +398,7 @@ table_name_case=no # case independent table names table_wildcard=yes # Select table_name.* tempoary_table=yes # temporary tables transactions=no # transactions +truncate_table=yes # truncate type_extra_abstime=no # Type abstime type_extra_bfile=no # Type bfile type_extra_blob=yes # Type blob @@ -512,5 +513,6 @@ union_all_incompat=no # union all (incompatible lists) union_incompat=no # union (incompatible lists) unique_in_create=yes # unique in create table unique_null_in_create=yes # unique null in create +user_comment=Alpha DS20 2x500 MHz, 2G memory, key_buffer=16M; ccc + cxx # comment views=no # views where_string_size=1048540 # constant string size in where diff --git a/sql-bench/limits/mysql.cfg b/sql-bench/limits/mysql.cfg index 186d1e740eb..759208ad713 100644 --- a/sql-bench/limits/mysql.cfg +++ b/sql-bench/limits/mysql.cfg @@ -1,4 +1,4 @@ -#This file is automaticly generated by crash-me 1.50 +#This file is automaticly generated by crash-me 1.51 NEG=yes # update of column= -column Need_cast_for_null=no # Need to cast NULL for arithmetic @@ -31,12 +31,12 @@ comment_--=yes # -- as comment (ANSI) comment_/**/=yes # /* */ as comment comment_//=no # // as comment (ANSI) compute=no # Compute -connections=30 # Simultaneous connections (installation default) +connections=101 # Simultaneous connections (installation default) constraint_check=no # Column constraints constraint_check_table=no # Table constraints constraint_null=yes # NULL constraint (SyBase style) crash_me_safe=yes # crash me safe -crash_me_version=1.50 # crash me version +crash_me_version=1.51 # crash me version create_default=yes # default value for column create_default_func=no # default value function for column create_if_not_exists=yes # create table if not exists @@ -315,7 +315,7 @@ intersect=no # intersect intersect_all=no # intersect all intersect_all_incompat=no # intersect all (incompatible lists) intersect_incompat=no # intersect (incompatible lists) -join_tables=31 # tables in join +join_tables=63 # tables in join left_outer_join=yes # left outer join left_outer_join_using=yes # left outer join using like_with_column=yes # column LIKE column @@ -327,7 +327,7 @@ max_char_size=255 # max char() size max_column_name=64 # column name length max_columns=3398 # Columns in table max_conditions=85660 # OR and AND in WHERE -max_expressions=859 # simple expressions +max_expressions=1837 # simple expressions max_index=32 # max index max_index_length=500 # index length max_index_name=64 # index name length @@ -337,7 +337,7 @@ max_index_varchar_part_length=255 # index varchar part length max_row_length=65534 # max table row length (without blobs) max_row_length_with_null=65502 # table row length with nulls (without blobs) max_select_alias_name=+512 # select alias name length -max_stack_expression=859 # stacked expressions +max_stack_expression=1837 # stacked expressions max_table_alias_name=+512 # table alias name length max_table_name=64 # table name length max_text_size=1048543 # max text or blob size @@ -361,7 +361,7 @@ null_in_index=yes # null in index null_in_unique=yes # null in unique index null_num_expr=yes # Is 1+NULL = NULL odbc_left_outer_join=yes # left outer join odbc style -operating_system=Linux 2.2.14-my-SMP i686 # crash-me tested on +operating_system=Linux 2.2.13-SMP alpha # crash-me tested on order_by=yes # Order by order_by_alias=yes # Order by alias order_by_function=yes # Order by function @@ -380,7 +380,7 @@ quote_with_"=yes # Allows ' and " as string markers remember_end_space=no # Remembers end space in char() remember_end_space_varchar=no # Remembers end space in varchar() rename_table=yes # rename table -repeat_string_size=1048576 # return string size from function +repeat_string_size=1047552 # return string size from function right_outer_join=yes # right outer join rowid=auto_increment # Type for row id select_constants=yes # Select constants @@ -389,7 +389,7 @@ select_limit2=yes # SELECT with LIMIT #,# select_string_size=1048565 # constant string size in SELECT select_table_update=no # Update with sub select select_without_from=yes # SELECT without FROM -server_version=MySQL 3.23.25 beta debug # server version +server_version=MySQL 3.23.28 gamma # server version simple_joins=yes # ANSI SQL simple joins storage_of_float=round # Storage of float values subqueries=no # subqueries @@ -398,6 +398,7 @@ table_name_case=no # case independent table names table_wildcard=yes # Select table_name.* tempoary_table=yes # temporary tables transactions=no # transactions +truncate_table=yes # truncate type_extra_abstime=no # Type abstime type_extra_bfile=no # Type bfile type_extra_blob=yes # Type blob @@ -512,5 +513,6 @@ union_all_incompat=no # union all (incompatible lists) union_incompat=no # union (incompatible lists) unique_in_create=yes # unique in create table unique_null_in_create=yes # unique null in create +user_comment=Alpha DS20 2x500 MHz, 2G memory, key_buffer=16M; ccc + cxx # comment views=no # views where_string_size=1048540 # constant string size in where diff --git a/sql-bench/test-select.sh b/sql-bench/test-select.sh index 32059d6ac8a..ef76c027380 100755 --- a/sql-bench/test-select.sh +++ b/sql-bench/test-select.sh @@ -338,13 +338,14 @@ if ($limits->{'group_functions'}) $loop_time=new Benchmark; $rows=$estimated=$count=0; - for ($i=0 ; $i < $opt_medium_loop_count ; $i++) + $test_count=$opt_medium_loop_count/10; + for ($i=0 ; $i < $test_count ; $i++) { $count++; $rows+=fetch_all_rows($dbh,"select idn,count(distinct region) from bench1 group by idn"); $end_time=new Benchmark; last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, - $opt_medium_loop_count)); + $test_count)); } print_time($estimated); print " for count_distinct_big ($count:$rows): " . diff --git a/sql/filesort.cc b/sql/filesort.cc index 220ff69a3e8..115698a9d79 100644 --- a/sql/filesort.cc +++ b/sql/filesort.cc @@ -716,6 +716,8 @@ static int merge_buffers(SORTPARAM *param, IO_CACHE *from_file, volatile bool *killed= ¤t_thd->killed; DBUG_ENTER("merge_buffers"); + statistic_increment(filesort_merge_passes, &LOCK_status); + count=error=0; offset=param->sort_length-param->ref_length; maxcount=(ulong) (param->keys/((uint) (Tb-Fb) +1)); diff --git a/sql/ha_berkeley.cc b/sql/ha_berkeley.cc index df54bef5d52..db5bff0c7b0 100644 --- a/sql/ha_berkeley.cc +++ b/sql/ha_berkeley.cc @@ -1375,8 +1375,9 @@ int ha_berkeley::external_lock(THD *thd, int lock_type) && !(thd->options & OPTION_BEGIN)) { /* - F_UNLOCK is done without a transaction commit / rollback. This - means that something went wrong. + F_UNLOCK is done without a transaction commit / rollback. + This happens if the thread didn't update any rows or if + something went wrong during an update. We can in this case silenty abort the transaction. */ DBUG_PRINT("trans",("aborting transaction")); diff --git a/sql/log.cc b/sql/log.cc index 3563b7b1a08..2dda594ea59 100644 --- a/sql/log.cc +++ b/sql/log.cc @@ -703,7 +703,7 @@ void MYSQL_LOG::write(THD *thd,const char *query, uint query_length, VOID(pthread_mutex_unlock(&LOCK_log)); return; } - if (specialflag & SPECIAL_LONG_LOG_FORMAT) + if ((specialflag & SPECIAL_LONG_LOG_FORMAT) || query_start) { current_time=time(NULL); if (current_time != last_time) @@ -724,28 +724,26 @@ void MYSQL_LOG::write(THD *thd,const char *query, uint query_length, if (my_b_write(&log_file, (byte*) buff,24)) error=errno; } - if (my_b_printf(&log_file, "# User@Host: %s [%s] @ %s [%s]\n", + if (my_b_printf(&log_file, "# User@Host: %s[%s] @ %s [%s]\n", thd->priv_user, thd->user, thd->host ? thd->host : "", - thd->ip ? thd->ip : "")) + thd->ip ? thd->ip : "") == (uint) -1) error=errno; } if (query_start) { /* For slow query log */ - if (!(specialflag & SPECIAL_LONG_LOG_FORMAT)) - current_time=time(NULL); if (my_b_printf(&log_file, "# Time: %lu Lock_time: %lu Rows_sent: %lu\n", (ulong) (current_time - query_start), (ulong) (thd->time_after_lock - query_start), - (ulong) thd->sent_row_count)) + (ulong) thd->sent_row_count) == (uint) -1) error=errno; } if (thd->db && strcmp(thd->db,db)) { // Database changed - if (my_b_printf(&log_file,"use %s;\n",thd->db)) + if (my_b_printf(&log_file,"use %s;\n",thd->db) == (uint) -1) error=errno; strmov(db,thd->db); } diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 7081c52ad1d..0fe3b725e34 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -160,6 +160,10 @@ void kill_one_thread(THD *thd, ulong id); #define OPTION_QUICK OPTION_BEGIN*2 #define OPTION_QUOTE_SHOW_CREATE OPTION_QUICK*2 +/* The following is set when parsing the query */ +#define OPTION_NO_INDEX_USED OPTION_QUOTE_SHOW_CREATE*2 +#define OPTION_NO_GOOD_INDEX_USED OPTION_NO_INDEX_USED*2 + #define RAID_BLOCK_SIZE 1024 /* BINLOG_DUMP options */ @@ -445,6 +449,7 @@ extern ulong refresh_version,flush_version, thread_id,query_id,opened_tables, delayed_insert_threads, delayed_insert_writes, delayed_rows_in_use,delayed_insert_errors; extern ulong filesort_rows, filesort_range_count, filesort_scan_count; +extern ulong filesort_merge_passes; extern ulong select_range_check_count, select_range_count, select_scan_count; extern ulong select_full_range_join_count,select_full_join_count; extern uint test_flags,select_errors,mysql_port,ha_open_options; @@ -615,6 +620,7 @@ inline bool add_group_to_list(Item *item,bool asc) inline void mark_as_null_row(TABLE *table) { table->null_row=1; + table->status|=STATUS_NULL_ROW; bfill(table->null_flags,table->null_bytes,255); } diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 42221be9176..bbacf46dd01 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -172,7 +172,7 @@ static char **defaults_argv,time_zone[30]; static const char *default_table_type_name; #ifdef HAVE_OPENSSL -static bool opt_use_ssl = false; +static bool opt_use_ssl = FALSE; static char *opt_ssl_key = 0; static char *opt_ssl_cert = 0; static char *opt_ssl_ca = 0; @@ -222,6 +222,7 @@ ulong query_id=1L,long_query_count,long_query_time,aborted_threads, delayed_queue_size,delayed_insert_threads,delayed_insert_writes, delayed_rows_in_use,delayed_insert_errors,flush_time; ulong filesort_rows, filesort_range_count, filesort_scan_count; +ulong filesort_merge_passes; ulong select_range_check_count, select_range_count, select_scan_count; ulong select_full_range_join_count,select_full_join_count; ulong specialflag=0,opened_tables=0,created_tmp_tables=0, @@ -2517,6 +2518,7 @@ struct show_var_st status_vars[]= { {"Connections", (char*) &thread_id, SHOW_LONG_CONST}, {"Created_tmp_disk_tables", (char*) &created_tmp_disk_tables,SHOW_LONG}, {"Created_tmp_tables", (char*) &created_tmp_tables, SHOW_LONG}, + {"Created_tmp_files", (char*) &my_tmp_file_created, SHOW_LONG}, {"Delayed_insert_threads", (char*) &delayed_insert_threads, SHOW_LONG}, {"Delayed_writes", (char*) &delayed_insert_writes, SHOW_LONG}, {"Delayed_errors", (char*) &delayed_insert_errors, SHOW_LONG}, @@ -2551,6 +2553,7 @@ struct show_var_st status_vars[]= { {"Slave_running", (char*) &slave_running, SHOW_BOOL}, {"Slow_launch_threads", (char*) &slow_launch_threads, SHOW_LONG}, {"Slow_queries", (char*) &long_query_count, SHOW_LONG}, + {"Sort_merge_passes", (char*) &filesort_merge_passes, SHOW_LONG}, {"Sort_range", (char*) &filesort_range_count, SHOW_LONG}, {"Sort_rows", (char*) &filesort_rows, SHOW_LONG}, {"Sort_scan", (char*) &filesort_scan_count, SHOW_LONG}, diff --git a/sql/share/german/errmsg.sys b/sql/share/german/errmsg.sys Binary files differindex 055541ae3d9..755783a4d20 100644 --- a/sql/share/german/errmsg.sys +++ b/sql/share/german/errmsg.sys diff --git a/sql/share/german/errmsg.txt b/sql/share/german/errmsg.txt index bdb3ee40844..580eddc0cb1 100644 --- a/sql/share/german/errmsg.txt +++ b/sql/share/german/errmsg.txt @@ -166,35 +166,35 @@ "Timeout beim Lesen eines Communication-Packets", "Fehler beim Schreiben eines Communication-Packets", "Timeout beim Schreiben eines Communication-Packets", -"Result string is longer than max_allowed_packet", -"The used table type doesn't support BLOB/TEXT columns", -"The used table type doesn't support AUTO_INCREMENT columns", -"INSERT DELAYED can't be used with table '%-.64s', because it is locked with LOCK TABLES", -"Incorrect column name '%-.100s'", -"The used table handler can't index column '%-.64s'", -"All tables in the MERGE table are not defined identically", -"Can't write, because of unique constraint, to table '%-.64s'", -"BLOB column '%-.64s' used in key specification without a key length", -"All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead", -"Result consisted of more than one row", -"This table type requires a primary key", -"This version of MySQL is not compiled with RAID support", -"You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column", -"Key '%-.64s' doesn't exist in table '%-.64s'", -"Can't open table", -"The handler for the table doesn't support check/repair", -"You are not allowed to execute this command in a transaction", -"Got error %d during COMMIT", -"Got error %d during ROLLBACK", -"Got error %d during FLUSH_LOGS", -"Got error %d during CHECKPOINT", -"Aborted connection %ld to db: '%-.64s' user: '%-.32s' host: `%-.64s' (%-.64s)", -"The handler for the table does not support binary table dump", -"Binlog closed while trying to FLUSH MASTER", -"Failed rebuilding the index of dumped table '%-.64s'", -"Error from master: '%-.64s'", -"Net error reading from master", -"Net error writing to master", -"Can't find FULLTEXT index matching the column list", +"Ergebnisstring ist länger als max_allowed_packet", +"Der verwendete Tabellentyp unterstützt keine BLOB/TEXT Spalten", +"Der verwendete Tabellentyp unterstützt keine AUTO_INCREMENT Spalte", +"INSERT DELAYED kann nicht auf Tabelle '%-.64s' angewendet werden, da diese mit LOCK TABLES gesperrt ist", +"Falscher Spaltenname '%-.100s'", +"Der verwendete Tabellen-Handler kann die Spalte '%-.64s' nicht indizieren", +"Alle Tabelle in der MERGE-Tabelle sind nicht gleich definiert", +"Schreiben in Tabelle '%-.64s' nicht möglich wegen eines Unique Constraint", +"BLOB Spalte '%-.64s' wird in der Key-Definition ohne Längenangabe verwendet", +"Alle Teile eines PRIMARY KEY müssen als NOT NULL definiert sein; Wenn NULL benötigt wird sollte ein UNIQUE Key verwendet werden", +"Ergebnis besteht aus mehr als einer Reihe", +"Dieser Tabellentyp verlangt nach einem PRIMARY KEY", +"Diese MySQL-Version ist nicht mit RAID-Unterstützung kompiliert", +"Unter Verwendung des Sicheren Updatemodes wurde versucht eine Tabelle zu updaten ohne eine KEY-Spalte in der WHERE-Klausel", +"Schlüssel '%-.64s' existiert nicht in der Tabelle '%-.64s'", +"Kann Tabelle nicht öffnen", +"Der Tabellen-Handler für diese Tabelle unterstützt kein check/repair", +"Keine Berechtigung dieses Kommando in einer Transaktion auszuführen", +"Fehler %d wärend COMMIT", +"Fehler %d wärend ROLLBACK", +"Fehler %d wärend FLUSH_LOGS", +"Fehler %d wärend CHECKPOINT", +"Verbindungsabbruch %ld zu db: '%-.64s' user: '%-.32s' host: `%-.64s' (%-.64s)", +"Der Tabellenhandler für die Tabelle unterstützt kein Binary Tabellendump", +"Binlog wurde beendet wärend FLUSH MASTER", +"Neubau des Index der gedumpten Tabelle '%-.64s' fehlgeschlagen", +"Fehler vom Master: '%-.64s'", +"Netzfehler beim Lesen vom Master", +"Netzfehler beim Schreiben zum Master", +"Kann keinen FULLTEXT-Index finden der der Spaltenliste entspricht", "Can't execute the given command because you have active locked tables or an active transaction", "Unknown system variable '%-.64'", diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 1c006a83ce7..4a5f5e582f3 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1687,6 +1687,7 @@ insert_fields(THD *thd,TABLE_LIST *tables, const char *table_name, if (!table_name || !strcmp(table_name,tables->name)) { Field **ptr=table->field,*field; + thd->used_tables|=table->map; while ((field = *ptr++)) { Item_field *item= new Item_field(field); @@ -1782,6 +1783,8 @@ int setup_conds(THD *thd,TABLE_LIST *tables,COND **conds) DBUG_RETURN(1); tmp->fix_length_and_dec(); // Update cmp_type tmp->const_item_cache=0; + /* Mark field used for table cache */ + t1->field[i]->query_id=t2->field[j]->query_id=thd->query_id; cond_and->list.push_back(tmp); if ((tmp_map=t1->field[i]->part_of_key)) { diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 29526b4d7d5..4875be9be6c 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -35,21 +35,21 @@ int generate_table(THD *thd, TABLE_LIST *table_list, thd->proc_info="generate_table"; if(global_read_lock) + { + if(thd->global_read_lock) { - if(thd->global_read_lock) - { - my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE,MYF(0), - table_list->real_name); - DBUG_RETURN(-1); - } - pthread_mutex_lock(&LOCK_open); - while (global_read_lock && ! thd->killed || - thd->version != refresh_version) - { - (void) pthread_cond_wait(&COND_refresh,&LOCK_open); - } - pthread_mutex_unlock(&LOCK_open); + my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE,MYF(0), + table_list->real_name); + DBUG_RETURN(-1); + } + pthread_mutex_lock(&LOCK_open); + while (global_read_lock && ! thd->killed || + thd->version != refresh_version) + { + (void) pthread_cond_wait(&COND_refresh,&LOCK_open); } + pthread_mutex_unlock(&LOCK_open); + } /* If it is a temporary table, close and regenerate it */ @@ -173,14 +173,16 @@ int mysql_delete(THD *thd,TABLE_LIST *table_list,COND *conds,ha_rows limit, } /* If running in safe sql mode, don't allow updates without keys */ - if ((thd->options & OPTION_SAFE_UPDATES) && !table->quick_keys && - limit == HA_POS_ERROR) + if (!table->quick_keys) { - delete select; - send_error(&thd->net,ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE); - DBUG_RETURN(1); + thd->options|=OPTION_NO_INDEX_USED; + if ((thd->options & OPTION_SAFE_UPDATES) && limit == HA_POS_ERROR) + { + delete select; + send_error(&thd->net,ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE); + DBUG_RETURN(1); + } } - (void) table->file->extra(HA_EXTRA_NO_READCHECK); if (options & OPTION_QUICK) (void) table->file->extra(HA_EXTRA_QUICK); diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index ec727591e2a..25ca7d76e03 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -118,11 +118,10 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, List<Item> &fields, if we are told to replace duplicates, the insert cannot be concurrent delayed insert changed to regular in slave thread */ - if (lock_type == TL_WRITE_DELAYED && - ((specialflag & (SPECIAL_NO_NEW_FUNC | SPECIAL_SAFE_MODE)) || - thd->slave_thread - ) || - lock_type == TL_WRITE_CONCURRENT_INSERT && duplic == DUP_REPLACE) + if ((lock_type == TL_WRITE_DELAYED && + ((specialflag & (SPECIAL_NO_NEW_FUNC | SPECIAL_SAFE_MODE)) || + thd->slave_thread)) || + (lock_type == TL_WRITE_CONCURRENT_INSERT && duplic == DUP_REPLACE)) lock_type=TL_WRITE; if (lock_type == TL_WRITE_DELAYED) diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 79c36431cb1..b2e68e914eb 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -825,7 +825,9 @@ bool do_command(THD *thd) /* If not reading from backup and if the query took too long */ if (!thd->user_time) { - if ((ulong) (thd->start_time - thd->time_after_lock) > long_query_time) + if ((ulong) (thd->start_time - thd->time_after_lock) > long_query_time || + ((thd->options & (OPTION_NO_INDEX_USED | OPTION_NO_GOOD_INDEX_USED)) && + (specialflag & SPECIAL_LONG_LOG_FORMAT))) { long_query_count++; mysql_slow_log.write(thd, thd->query, thd->query_length, start_of_query); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 9d6742884a5..3aec452fe88 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2420,6 +2420,7 @@ make_join_readinfo(JOIN *join,uint options) /* These init changes read_record */ if (tab->use_quick == 2) { + join->thd->options|=OPTION_NO_GOOD_INDEX_USED; tab->read_first_record= join_init_quick_read_record; statistic_increment(select_range_check_count, &LOCK_status); } @@ -2434,6 +2435,7 @@ make_join_readinfo(JOIN *join,uint options) } else { + join->thd->options|=OPTION_NO_INDEX_USED; statistic_increment(select_scan_count, &LOCK_status); } } @@ -2445,6 +2447,7 @@ make_join_readinfo(JOIN *join,uint options) } else { + join->thd->options|=OPTION_NO_INDEX_USED; statistic_increment(select_full_join_count, &LOCK_status); } } @@ -4234,7 +4237,7 @@ join_read_key(JOIN_TAB *tab) TABLE *table= tab->table; if (cmp_buffer_with_ref(tab) || - (table->status & (STATUS_GARBAGE | STATUS_NO_PARENT))) + (table->status & (STATUS_GARBAGE | STATUS_NO_PARENT | STATUS_NULL_ROW))) { if (tab->ref.key_err) { @@ -4252,6 +4255,7 @@ join_read_key(JOIN_TAB *tab) return 1; } } + table->null_row=0; return table->status ? -1 : 0; } diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 173511083e4..68b5d90217c 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -79,7 +79,10 @@ int mysql_rm_table(THD *thd,TABLE_LIST *tables, my_bool if_exists) { char *db=table->db ? table->db : thd->db; if (!close_temporary_table(thd, db, table->real_name)) + { + some_tables_deleted=1; // Log query continue; // removed temporary table + } abort_locked_tables(thd,db,table->real_name); while (remove_table_from_cache(thd,db,table->real_name) && !thd->killed) diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 29e9c8b8aac..41a1a9be7c2 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -100,15 +100,17 @@ int mysql_update(THD *thd,TABLE_LIST *table_list,List<Item> &fields, DBUG_RETURN(0); } /* If running in safe sql mode, don't allow updates without keys */ - if ((thd->options & OPTION_SAFE_UPDATES) && !table->quick_keys && - limit == HA_POS_ERROR) + if (!table->quick_keys) { - delete select; - table->time_stamp=save_time_stamp; - send_error(&thd->net,ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE); - DBUG_RETURN(1); + thd->options|=OPTION_NO_INDEX_USED; + if ((thd->options & OPTION_SAFE_UPDATES) && limit == HA_POS_ERROR) + { + delete select; + table->time_stamp=save_time_stamp; + send_error(&thd->net,ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE); + DBUG_RETURN(1); + } } - /* Check if we are modifying a key that we are used to search with */ if (select && select->quick) used_key_is_modified= (!select->quick->unique_key_range() && diff --git a/sql/structs.h b/sql/structs.h index aa28a2b41fb..f1db62f5d9e 100644 --- a/sql/structs.h +++ b/sql/structs.h @@ -161,3 +161,4 @@ typedef struct st_lex_user { #define STATUS_NO_PARENT 4 /* Parent record wasn't found */ #define STATUS_NOT_READ 8 /* Record isn't read */ #define STATUS_UPDATED 16 /* Record is updated by formula */ +#define STATUS_NULL_ROW 32 /* table->null_row is set */ diff --git a/support-files/my-huge.cnf.sh b/support-files/my-huge.cnf.sh index fd0d70b2d04..a57014b3979 100644 --- a/support-files/my-huge.cnf.sh +++ b/support-files/my-huge.cnf.sh @@ -62,3 +62,6 @@ set-variable = key_buffer=256M set-variable = sort_buffer=256M set-variable = read_buffer=2M set-variable = write_buffer=2M + +[mysqlhotcopy] +interactive-timeout diff --git a/support-files/my-large.cnf.sh b/support-files/my-large.cnf.sh index 8b58bed642f..53c8960da96 100644 --- a/support-files/my-large.cnf.sh +++ b/support-files/my-large.cnf.sh @@ -62,3 +62,6 @@ set-variable = key_buffer=128M set-variable = sort_buffer=128M set-variable = read_buffer=2M set-variable = write_buffer=2M + +[mysqlhotcopy] +interactive-timeout diff --git a/support-files/my-medium.cnf.sh b/support-files/my-medium.cnf.sh index 13bf8e739ec..b66a607d353 100644 --- a/support-files/my-medium.cnf.sh +++ b/support-files/my-medium.cnf.sh @@ -61,3 +61,6 @@ set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M + +[mysqlhotcopy] +interactive-timeout diff --git a/support-files/my-small.cnf.sh b/support-files/my-small.cnf.sh index 48017226a51..844312aa18f 100644 --- a/support-files/my-small.cnf.sh +++ b/support-files/my-small.cnf.sh @@ -55,3 +55,6 @@ set-variable = sort_buffer=8M [myisamchk] set-variable = key_buffer=8M set-variable = sort_buffer=8M + +[mysqlhotcopy] +interactive-timeout diff --git a/vio/VioFd.cc b/vio/VioFd.cc index 3c6b0108fee..da59798fc25 100644 --- a/vio/VioFd.cc +++ b/vio/VioFd.cc @@ -82,13 +82,13 @@ VioFd::keepalive(boolonoff) bool VioFd::fcntl() const { - return false; + return FALSE; } bool VioFd::should_retry() const { - return false; + return FALSE; } int diff --git a/vio/VioSSL.cc b/vio/VioSSL.cc index a8efacf912f..15964c09aba 100644 --- a/vio/VioSSL.cc +++ b/vio/VioSSL.cc @@ -48,7 +48,7 @@ report_errors() VioSSL::VioSSL(int fd, vio_ptr ssl_context, int state) - : bio_(0), ssl_con_(0), open_(false), sd_(new VioSocket(fd)) + : bio_(0), ssl_con_(0), open_(FALSE), sd_(new VioSocket(fd)) { DBUG_ENTER("VioSSL::VioSSL"); DBUG_PRINT("enter", ("this=%p, fd=%d, ssl_context=%p, state=%d", @@ -66,7 +66,7 @@ VioSSL::VioSSL(int fd, VioSSL::VioSSL(VioSocket* sd, vio_ptr ssl_context, int state) - :bio_(0), ssl_con_(0), open_(false), sd_(sd) + :bio_(0), ssl_con_(0), open_(FALSE), sd_(sd) { DBUG_ENTER("VioSSL::VioSSL"); DBUG_PRINT("enter", diff --git a/vio/VioSSLFactoriesFd.cc b/vio/VioSSLFactoriesFd.cc index 3fa660c049c..bd64202770a 100644 --- a/vio/VioSSLFactoriesFd.cc +++ b/vio/VioSSLFactoriesFd.cc @@ -24,8 +24,8 @@ VIO_NS_BEGIN #define this_ssl_context my_static_cast(SSL_CTX*)(this->ssl_context_) typedef unsigned char* ssl_data_ptr_t; -static bool ssl_algorithms_added = false; -static bool ssl_error_strings_loaded= false; +static bool ssl_algorithms_added = FALSE; +static bool ssl_error_strings_loaded= FALSE; static int verify_depth = 0; static int verify_error = X509_V_OK; diff --git a/vio/VioSocket.cc b/vio/VioSocket.cc index cefb0b4bdc1..e8390edb98a 100644 --- a/vio/VioSocket.cc +++ b/vio/VioSocket.cc @@ -73,7 +73,7 @@ VIO_NS_BEGIN VioSocket::VioSocket(vio_socket sd, enum_vio_type type, bool localhost) :sd_(sd), localhost_(localhost), fcntl_(0), - fcntl_set_(false), cipher_description_(0) + fcntl_set_(FALSE), cipher_description_(0) { DBUG_ENTER("VioSocket::VioSocket"); DBUG_PRINT("enter", ("sd=%d", sd)); |