diff options
| -rw-r--r-- | Docs/manual.texi | 98 |
1 files changed, 82 insertions, 16 deletions
diff --git a/Docs/manual.texi b/Docs/manual.texi index c2acf5f336a..b6d05119e36 100644 --- a/Docs/manual.texi +++ b/Docs/manual.texi @@ -3644,16 +3644,20 @@ differently: @item @code{--} is only a comment if followed by a white space. @xref{Missing comments}. + @item For @code{VARCHAR} columns, trailing spaces are removed when the value is stored. @xref{Bugs}. + @item In some cases, @code{CHAR} columns are silently changed to @code{VARCHAR} columns. @xref{Silent column changes}. + @item Privileges for a table are not automatically revoked when you delete a table. You must explicitly issue a @code{REVOKE} to revoke privileges for a table. @xref{GRANT, , @code{GRANT}}. + @item @code{NULL AND FALSE} will evaluate to @code{NULL} and not to @code{FALSE}. This is because we don't think it's good to have to evaluate a lot of @@ -3673,15 +3677,19 @@ behavior of MySQL changes: @itemize @bullet @item @code{||} is string concatenation instead of @code{OR}. + @item You can have any number of spaces between a function name and the @samp{(}. This forces all function names to be treated as reserved words. + @item @samp{"} will be an identifier quote character (like the MySQL @samp{`} quote character) and not a string quote character. + @item @code{REAL} will be a synonym for @code{FLOAT} instead of a synonym of @code{DOUBLE}. + @item The default transaction isolation level is @code{SERIALIZABLE}. @xref{SET TRANSACTION}. @@ -4315,17 +4323,21 @@ The following problems are known and will be fixed in due time: @itemize @bullet @item For the moment @code{MATCH} only works with @code{SELECT} statements. + @item When using @code{SET CHARACTER SET}, one can't use translated characters in database, table and column names. + @item @code{DELETE FROM merge_table} used without a @code{WHERE} will only clear the mapping for the table, not delete everything in the mapped tables + @item You cannot build in another directory when using MIT-pthreads. Because this requires changes to MIT-pthreads, we are not likely to fix this. + @item @code{BLOB} values can't ``reliably'' be used in @code{GROUP BY} or @code{ORDER BY} or @code{DISTINCT}. Only the first @code{max_sort_length} @@ -4333,6 +4345,7 @@ bytes (default 1024) are used when comparing @code{BLOB}bs in these cases. This can be changed with the @code{-O max_sort_length} option to @code{mysqld}. A workaround for most cases is to use a substring: @code{SELECT DISTINCT LEFT(blob,2048) FROM tbl_name}. + @item Calculation is done with @code{BIGINT} or @code{DOUBLE} (both are normally 64 bits long). It depends on the function which precision one @@ -4341,13 +4354,16 @@ precision, @code{IF}, and @code{ELT()} with @code{BIGINT} or @code{DOUBLE} precision and the rest with @code{DOUBLE} precision. One should try to avoid using bigger unsigned long long values than 63 bits (9223372036854775807) for anything else than bit fields! + @item All string columns, except @code{BLOB} and @code{TEXT} columns, automatically have all trailing spaces removed when retrieved. For @code{CHAR} types this is okay, and may be regarded as a feature according to ANSI SQL92. The bug is that in MySQL, @code{VARCHAR} columns are treated the same way. + @item You can only have up to 255 @code{ENUM} and @code{SET} columns in one table. + @item @code{safe_mysqld} re-directs all messages from @code{mysqld} to the @code{mysqld} log. One problem with this is that if you execute @@ -4357,14 +4373,18 @@ If you use @code{--log} extensively, you should edit @code{safe_mysqld} to log to @file{'hostname'.err} instead of @file{'hostname'.log} so you can easily reclaim the space for the old log by deleting the old one and executing @code{mysqladmin refresh}. + @item -In the @code{UPDATE} statement, columns are updated from left to right. -If you refer to an updated column, you will get the updated value instead of the +In the @code{UPDATE} statement, columns are updated from left to right. If +you refer to an updated column, you will get the updated value instead of the original value. For example: + @example mysql> UPDATE tbl_name SET KEY=KEY+1,KEY=KEY+1; @end example -will update @code{KEY} with @code{2} instead of with @code{1}. + +This will update @code{KEY} with @code{2} instead of with @code{1}. + @item You can't use temporary tables more than once in the same query. For example, the following doesn't work. @@ -4389,9 +4409,11 @@ An example of this is: @example SELECT DISTINCT mp3id FROM band_downloads WHERE userid = 9 ORDER BY id DESC; +@end example and +@example SELECT DISTINCT band_downloads.mp3id, FROM band_downloads,band_mp3 WHERE band_downloads.userid = 9 AND band_mp3.id = band_downloads.mp3id ORDER BY band_downloads.id DESC; @@ -4422,24 +4444,28 @@ optimizations that otherwise would be very hard to do. If you set a column to an incorrect value, MySQL will, instead of doing a rollback, store the @code{best possible value} in the column: -@itemize @bullet +@itemize @minus @item If you try to store a value outside the range in a numerical column, MySQL will instead store the smallest or biggest possible value in the column. + @item If you try to store a string that doesn't start with a number into a numerical column, MySQL will store 0 into it. + @item If you try to store @code{NULL} into a column that doesn't take @code{NULL} values, MySQL will store 0 or @code{''} (empty string) in it instead. (This behavior can, however, be changed with the -DDONT_USE_DEFAULT_FIELDS compile option). + @item MySQL allows you to store some wrong date values into @code{DATE} and @code{DATETIME} columns. (Like 2000-02-31 or 2000-02-00). If the date is totally wrong, MySQL will store the special 0000-00-00 date value in the column. + @item If you set an @code{enum} to an unsupported value, it will be set to the error value 'empty string', with numeric value 0. @@ -4448,15 +4474,18 @@ the error value 'empty string', with numeric value 0. @item If you execute a @code{PROCEDURE} on a query that returns an empty set, in some cases the @code{PROCEDURE} will not transform the columns. + @item Creation of a table of type @code{MERGE} doesn't check if the underlying tables are of compatible types. + @item MySQL can't yet handle @code{NaN}, @code{-Inf} and @code{Inf} values in double. Using these will cause problems when trying to export and import data. We should as an intermediate solution change @code{NaN} to @code{NULL} (if possible) and @code{-Inf} and @code{Inf} to the Minimum respective maximum possible @code{double} value. + @item @code{LIMIT} on negative numbers are treated as big positive numbers. @end itemize @@ -4470,11 +4499,14 @@ one among many tables that is locked with @code{LOCK TABLES}. @item In the following case you can get a core dump: -@enumerate + +@itemize @minus @item Delayed insert handler has pending inserts to a table. + @item @code{LOCK table} with @code{WRITE} + @item @code{FLUSH TABLES} @end enumerate @@ -4496,6 +4528,7 @@ mysql> UPDATE tbl_name SET KEY=KEY+1 WHERE KEY+0 > 100; This will work because MySQL will not use index on expressions in the @code{WHERE} clause. + @item Before 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 @@ -4545,10 +4578,13 @@ simple security, @code{mSQL} should be quicker at: @item Tests that perform repeated connects and disconnects, running a very simple query during each connection. + @item @code{INSERT} operations into very simple tables with few columns and keys. + @item @code{CREATE TABLE} and @code{DROP TABLE}. + @item @code{SELECT} on something that isn't an index. (A table scan is very easy.) @@ -4564,20 +4600,27 @@ most other SQL implementations) on the following: @itemize @bullet @item Complex @code{SELECT} operations. + @item Retrieving large results (MySQL has a better, faster, and safer protocol). + @item Tables with variable-length strings, because MySQL has more efficient handling and can have indexes on @code{VARCHAR} columns. + @item Handling tables with many columns. + @item Handling tables with large record lengths. + @item @code{SELECT} with many expressions. + @item @code{SELECT} on large tables. + @item Handling many connections at the same time. MySQL is fully multi-threaded. Each connection has its own thread, which means that @@ -4587,6 +4630,7 @@ is established, all others must wait until the first has finished, regardless of whether the connection is running a query that is short or long. When the first connection terminates, the next can be served, while all the others wait again, etc. + @item Joins. @code{mSQL} can become pathologically slow if you change the order of tables @@ -4596,10 +4640,13 @@ join optimizer to order tables in the optimal order. However, if you put the tables in exactly the right order in @code{mSQL}2 and the @code{WHERE} is simple and uses index columns, the join will be relatively fast! @xref{MySQL Benchmarks}. + @item @code{ORDER BY} and @code{GROUP BY}. + @item @code{DISTINCT}. + @item Using @code{TEXT} or @code{BLOB} columns. @end itemize @@ -4619,6 +4666,7 @@ columns are retrieved, and there is no @code{WHERE} clause. @code{MIN()} and @item @code{INSERT} and @code{UPDATE} with calculations. MySQL can do calculations in an @code{INSERT} or @code{UPDATE}. For example: + @example mysql> UPDATE SET x=x*10+y WHERE x<20; @end example @@ -4645,6 +4693,7 @@ saving even one byte per record is very important. @code{mSQL2} has a more limited set of column types, so it is more difficult to get small tables. + @item Stability This is harder to judge objectively. For a discussion of MySQL stability, see @ref{Stability}. @@ -4666,18 +4715,23 @@ some added features. @item JDBC (Java) MySQL currently has a lot of different JDBC drivers: + @itemize @bullet @item The mm driver: A type 4 JDBC driver by Mark Matthews @email{mmatthew@@ecn.purdue.edu}. This is released under the LGPL. + @item The Resin driver. This is a commercial JDBC driver released under open source. @uref{http://www.caucho.com/projects/jdbc-mysql/index.xtp} + @item The gwe driver: A Java interface by GWE technologies (not supported anymore). + @item The jms driver: An improved gwe driver by Xiaokun Kelvin ZHU @email{X.Zhu@@brad.ac.uk} (not supported anymore). + @item The twz driver: A type 4 JDBC driver by Terrence W. Zellers @email{zellert@@voicenet.com}. This is commercial but is free for private @@ -4739,29 +4793,35 @@ The conversion procedure is: @item Run the shell script @code{msql2mysql} on the source. This requires the @code{replace} program, which is distributed with MySQL. + @item Compile. + @item Fix all compiler errors. @end enumerate Differences between the @code{mSQL} C API and the MySQL C API are: + @itemize @bullet @item MySQL uses a @code{MYSQL} structure as a connection type (@code{mSQL} uses an @code{int}). + @item @code{mysql_connect()} takes a pointer to a @code{MYSQL} structure as a parameter. It is easy to define one globally or to use @code{malloc()} to get -one. -@code{mysql_connect()} also takes two parameters for specifying the user and -password. You may set these to @code{NULL, NULL} for default use. +one. @code{mysql_connect()} also takes two parameters for specifying the +user and password. You may set these to @code{NULL, NULL} for default use. + @item @code{mysql_error()} takes the @code{MYSQL} structure as a parameter. Just add the parameter to your old @code{msql_error()} code if you are porting old code. + @item MySQL returns an error number and a text error message for all errors. @code{mSQL} returns only a text error message. + @item Some incompatibilities exist as a result of MySQL supporting multiple connections to the server from the same process. @@ -4783,21 +4843,27 @@ from the @code{mSQL} protocol are listed below: @itemize @bullet @item A message buffer may contain many result rows. + @item The message buffers are dynamically enlarged if the query or the result is bigger than the current buffer, up to a configurable server and client limit. + @item All packets are numbered to catch duplicated or missing packets. + @item All column values are sent in ASCII. The lengths of columns and rows are sent in packed binary coding (1, 2, or 3 bytes). + @item MySQL can read in the result unbuffered (without having to store the full set in the client). + @item If a single read/write takes more than 30 seconds, the server closes the connection. + @item If a connection is idle for 8 hours, the server closes the connection. @end itemize @@ -5115,20 +5181,20 @@ existing programs than PostgreSQL. @xref{Contrib}. @item MySQL works on 24/7 heavy duty systems. In most circumstances -you never have to run any cleanups on @code{MySQL}. PostgreSQL doesn't -yet support 24/7 systems because you have to run @code{vacuum()} +you never have to run any cleanups on MySQL. PostgreSQL doesn't +yet support 24/7 systems because you have to run @code{VACUUM()} once in a while to reclaim space from @code{UPDATE} and @code{DELETE} commands and to perform statistics analyzes that are critical to get -good performance with PostgreSQL. Vacuum is also needed after adding -a lot of new rows to a table. On a busy system with lots of changes, -vacuum must be run very frequently, in the worst cases even many times a -day. During the @code{vacuum()} run, which may take hours if the -database is big, the database is from a production standpoint, +good performance with PostgreSQL. @code{VACUUM()} is also needed after +adding a lot of new rows to a table. On a busy system with lots of changes, +@code{VACUUM()} must be run very frequently, in the worst cases even +many times a day. During the @code{VACUUM()} run, which may take hours +if the database is big, the database is from a production standpoint, practically dead. The PostgreSQL team has fixing this on their TODO, but we assume that this is not an easy thing to fix permanently. @item -A working, tested replication feature used by sites like +A working, tested replication feature used by sites like: @itemize @minus @item Yahoo Finance (@uref{http://finance.yahoo.com}) @item Mobile.de (@uref{http://www.mobile.de/}) |