diff options
Diffstat (limited to 'Docs/manual.texi')
| -rw-r--r-- | Docs/manual.texi | 745 |
1 files changed, 534 insertions, 211 deletions
diff --git a/Docs/manual.texi b/Docs/manual.texi index 83999b6daae..2a8909f0e6a 100644 --- a/Docs/manual.texi +++ b/Docs/manual.texi @@ -1709,7 +1709,7 @@ domain names is not allowed without written permission from @code{MySQL AB}. Dateline: 16 October 2001, Uppsala, Sweden Long promised by @code{MySQL AB} and long awaited by our users, -MySQL Server 4.0 is now available in alpha version for download from +MySQL Server 4.0 is now available in beta version for download from @uref{http://www.mysql.com/} and our mirrors. Main new features of MySQL Server 4.0 are geared toward our existing @@ -2758,7 +2758,7 @@ Strings may be enclosed by either @samp{"} or @samp{'}, not just by @samp{'}. Use of the escape @samp{\} character. @item -The @code{SET OPTION} statement. @xref{SET OPTION, , @code{SET OPTION}}. +The @code{SET} statement. @xref{SET OPTION, , @code{SET}}. @item You don't need to name all selected columns in the @code{GROUP BY} part. @@ -3688,26 +3688,7 @@ this without sacrificing the speed or compromising the code. @node TODO MySQL 4.0, TODO MySQL 4.1, TODO, TODO @subsection Things That Should be in 4.0 -We are now in the final stages of the development of the MySQL Server -4.0. server. The target is to quickly implement the rest of the -following features and then shift development to MySQL Server -4.1. @xref{MySQL 4.0 In A Nutshell}. - -The news section for 4.0 includes a list of the features we have already -implemented in the 4.0 tree. @xref{News-4.0.x}. - -This section lists features not yet implemented in the current version -of MySQL Server 4.0, which will, however, be implemented in later versions -of MySQL 4.0. This being very volatile information, please consider this -list valid only if you are reading it from the MySQL web site -(@uref{http://www.mysql.com/}). - -@itemize @bullet -@item -Allow users to change startup options without taking down the server. -@item -@code{SET SQL_DEFAULT_TABLE_TYPE=[MyISAM | INNODB | BDB | HEAP]}. -@end itemize +All done. We now only do bug fixes MySQL 4.0. @node TODO MySQL 4.1, TODO future, TODO MySQL 4.0, TODO @subsection Things That Should be in 4.1 @@ -6920,8 +6901,8 @@ shell> ./configure --with-charset=CHARSET @xref{Character sets}. If you want to convert characters between the server and the client, -you should take a look at the @code{SET OPTION CHARACTER SET} command. -@xref{SET OPTION, , @code{SET OPTION}}. +you should take a look at the @code{SET CHARACTER SET} command. +@xref{SET OPTION, , @code{SET}}. @cindex @code{myisamchk} @strong{Warning}: If you change character sets after having created any @@ -8165,6 +8146,36 @@ should check if you need some of the new grants that you didn't need before. In particular, you will need @code{REPLICATION SLAVE} (instead of @code{FILE}) for new slaves. @item +The startup parameters @code{myisam_max_extra_sort_file_size} and +@code{myisam_max_extra_sort_file_size} are now given in bytes +(was megabytes before 4.0.3). +@item +The following startup variables/options have been renamed: +@multitable @columnfractions .50 .50 +@item @strong{From} @tab @strong{to}. +@item @code{myisam_bulk_insert_tree_size} @tab @code{bulk_insert_buffer_size} +@item @code{query_cache_startup_type} @tab @code{query_cache_type} +@item @code{record_buffer} @tab @code{read_buffer_size} +@item @code{record_rnd_buffer} @tab @code{read_rnd_buffer_size} +@item @code{sort_buffer} @tab @code{sort_buffer_size} +@item @code{warnings} @tab @code{log-warnings} +@end multitable + +The startup options @code{record_buffer}, @code{sort_buffer} and @code{warnings} will still work in MySQL 4.0 but are deprecated. +@item +The following SQL variables have changed name. +@multitable @columnfractions .50 .50 +@item @strong{From} @tab @strong{to}. +@item @code{SQL_BIG_TABLES} @tab @code{BIG_TABLES} +@item @code{SQL_LOW_PRIORITY_UPDATES} @tab @code{LOW_PRIORITY_UPDATES} +@item @code{SQL_MAX_JOIN_SIZE} @tab @code{MAX_JOIN_SIZE} +@item @code{SQL_QUERY_CACHE_TYPE} @tab @code{QUERY_CACHE_TYPE} +@end multitable +The old names still work in MySQL 4.0 but are depricated. +@item +You have to use @code{SET GLOBAL SQL_SLAVE_SKIP_COUNTER=#} instead of +@code{SET SQL_SLAVE_SKIP_COUNTER=#}. +@item @code{DOUBLE} and @code{FLOAT} columns are now honoring the @code{UNSIGNED} flag on storage (before, @code{UNSIGNED} was ignored for these columns). @@ -9395,7 +9406,7 @@ C:\> mysqladmin --user=root --password=your_password shutdown If you are using the old shareware version of MySQL Version 3.21 under Windows, the above command will fail with an error: -@code{parse error near 'SET OPTION password'}. The solution for +@code{parse error near 'SET password'}. The solution for this is to download and upgrade to the latest MySQL version, which is now freely available. @@ -14235,7 +14246,7 @@ Table-modifying operations (@code{INSERT}/@code{DELETE}/@code{UPDATE}) will have lower priority than selects. It can also be done via @code{@{INSERT | REPLACE | UPDATE | DELETE@} LOW_PRIORITY ...} to lower the priority of only one query, or by -@code{SET OPTION SQL_LOW_PRIORITY_UPDATES=1} to change the priority in one +@code{SET LOW_PRIORITY_UPDATES=1} to change the priority in one thread. @xref{Table locking}. @item --memlock @@ -14296,6 +14307,18 @@ description for all variables in the @code{SHOW VARIABLES} section in this manual. @xref{SHOW VARIABLES}. The tuning server parameters section includes information of how to optimise these. @xref{Server parameters}. +In MySQL 4.0.2 one can set a variable directly with +@code{--variable-name=option} and @code{set-variable} is not anymore needed +in option files. + +If you want to restrict the maximum value a startup option can be set to +with @code{SET}, you can define this by using the +@code{--maximum-variable-name} command line option. @xref{SET OPTION}. + +Note that when setting a variable to a value, MySQL may automaticly +correct it to stay within a given range and also adjusts the value a +little to fix for the used algorithm. + @item --safe-mode Skip some optimise stages. Implies @code{--skip-delay-key-write}. @@ -14408,11 +14431,13 @@ This option is @strong{mandatory} when starting @code{mysqld} as root. @item -V, --version Output version information and exit. -@item -W, --warnings +@item -W, --log-warnings (Was --warnings) Print out warnings like @code{Aborted connection...} to the @file{.err} file. @xref{Communication errors}. @end table +One can change most values for a running server with the +@code{SET} command. @xref{SET OPTION}. @node Option files, Installing many servers, Command-line options, Configuring MySQL @subsection @file{my.cnf} Option Files @@ -16641,7 +16666,7 @@ function @code{PASSWORD()} or the C API function @code{IDENTIFIED BY} clause, the user has no password. This is insecure. Passwords can also be set with the @code{SET PASSWORD} command. -@xref{SET OPTION, , @code{SET OPTION}}. +@xref{SET OPTION, , @code{SET}}. If you grant privileges for a database, an entry in the @code{mysql.db} table is created if needed. When all privileges for the database have been @@ -19609,7 +19634,7 @@ instead of disk based. @subsubsection @code{SHOW VARIABLES} @example -SHOW VARIABLES [LIKE wild] +SHOW [GLOBAL | SESSION] VARIABLES [LIKE wild] @end example @code{SHOW VARIABLES} shows the values of some MySQL system @@ -19618,6 +19643,15 @@ variables} command. If the default values are unsuitable, you can set most of these variables using command-line options when @code{mysqld} starts up. @xref{Command-line options}. +The options @code{GLOBAL} and @code{SESSION} are new in MySQL 4.0.3. +With @code{GLOBAL} you will get the variables that will be used for new +connections to MySQL. With @code{SESSION} you will get the values that +are in effect for the current connection. If you are not using either +option, @code{SESSION} is used. + +You can change most options with the @code{SET} command. +@xref{SET OPTION, , @code{SET}}. + The output resembles that shown here, though the format and numbers may differ somewhat: @@ -19636,6 +19670,7 @@ differ somewhat: | bdb_shared_data | OFF | | bdb_tmpdir | /tmp/ | | binlog_cache_size | 32768 | +| bulk_insert_buffer_size | 8388608 | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | /my/monty/data/ | @@ -19676,7 +19711,6 @@ differ somewhat: | max_sort_length | 1024 | | max_tmp_tables | 32 | | max_write_lock_count | 4294967295 | -| myisam_bulk_insert_tree_size | 8388608 | | myisam_recover_options | DEFAULT | | myisam_sort_buffer_size | 8388608 | | net_buffer_length | 16384 | @@ -19687,11 +19721,10 @@ differ somewhat: | pid_file | /my/monty/data/donna.pid | | port | 3306 | | protocol_version | 10 | -| record_buffer | 131072 | -| query_buffer_size | 0 | +| read_buffer_size | 131072 | | query_cache_limit | 1048576 | | query_cache_size | 16768060 | -| query_cache_startup_type | 1 | +| query_cache_type | 1 | | safe_show_database | OFF | | server_id | 0 | | skip_locking | ON | @@ -19783,6 +19816,15 @@ statements for the binary log during a transaction. If you often use big, multi-statement transactions you can increase this to get more performance. @xref{COMMIT}. +@item @code{bulk_insert_buffer_size} (was @code{myisam_bulk_insert_tree_size}) +MyISAM uses special tree-like cache to make bulk inserts (that is, +@code{INSERT ... SELECT}, @code{INSERT ... VALUES (...), (...), ...}, and +@code{LOAD DATA INFILE}) faster. This variable limits +the size of the cache tree in bytes per thread. Setting it to 0 +will disable this optimization. +@strong{Note}: this cache is only used when adding data to non-empty table. +Default value is 8 MB. + @item @code{character_set} The default character set. @@ -20012,15 +20054,6 @@ Maximum number of temporary tables a client can keep open at the same time. @item @code{max_write_lock_count} After this many write locks, allow some read locks to run in between. -@item @code{myisam_bulk_insert_tree_size} -MySQL uses special tree-like cache to make bulk inserts (that is, -@code{INSERT ... SELECT}, @code{INSERT ... VALUES (...), (...), ...}, and -@code{LOAD DATA INFILE}) faster. This variable limits -the size of the cache tree in bytes per thread. Setting it to 0 -will disable this optimization. -@strong{Note}: this cache is only used when adding data to non-empty table. -Default value is 8 MB. - @item @code{myisam_recover_options} The value of the @code{--myisam-recover} option. @@ -20081,19 +20114,17 @@ The value of the @code{--port} option. @item @code{protocol_version} The protocol version used by the MySQL server. -@item @code{record_buffer} +@item @code{read_buffer_size} (was @code{record_buffer}) Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increase this value. -@item @code{record_rnd_buffer} -When reading rows in sorted order after a sort, the rows are read through this -buffer to avoid a disk seeks. If not set, then it's set to the value of -@code{record_buffer}. - -@item @code{query_buffer_size} -The initial allocation of the query buffer. If most of your queries are -long (like when inserting blobs), you should increase this! +@item @code{record_rnd_buffer_size} +When reading rows in sorted order after a sort, the rows are read +through this buffer to avoid a disk seeks. Can improve @code{ORDER BY} +by a lot if set to a high value. As this is a thread specific variable, +one should not set this big globally, but just change this when running +some specific big queries. @item @code{query_cache_limit} Don't cache results that are bigger than this. (Default 1M). @@ -20102,7 +20133,7 @@ Don't cache results that are bigger than this. (Default 1M). The memory allocated to store results from old queries. If this is 0, the query cache is disabled (default). -@item @code{query_cache_startup_type} +@item @code{query_cache_type} This may be set (only numeric) to @multitable @columnfractions .09 .14 .72 @item @strong{Value} @tab @strong{Alias} @tab @strong{Comment} @@ -20388,7 +20419,7 @@ Create Table: CREATE TABLE t ( @code{SHOW CREATE TABLE} will quote table and column names according to @code{SQL_QUOTE_SHOW_CREATE} option. -@ref{SET OPTION, , @code{SET OPTION SQL_QUOTE_SHOW_CREATE}}. +@ref{SET OPTION, , @code{SET SQL_QUOTE_SHOW_CREATE}}. @node Localisation, Server-Side Scripts, Database Administration, MySQL Database Administration @@ -24066,25 +24097,26 @@ contrast to @code{replicate-do-db}. Example: @code{replicate-do-table=some_db.some_table} @item @code{replicate-ignore-table=db_name.table_name} @tab -Tells the slave thread to not replicate to the specified table. To -specify more than one table to ignore, use the directive multiple times, -once for each table. This will work for cross-datbase updates, -in contrast to @code{replicate-ignore-db}. +Tells the slave thread to not replicate any command that updates the +specified table (even if any other tables may be update by the same +command). To specify more than one table to ignore, use the directive +multiple times, once for each table. This will work for cross-datbase +updates, in contrast to @code{replicate-ignore-db}. Example: @code{replicate-ignore-table=db_name.some_table} @item @code{replicate-wild-do-table=db_name.table_name} @tab -Tells the slave thread to restrict replication to the tables that match -the specified wildcard pattern. To specify more than one table, use the -directive multiple times, once for each table. This will work for -cross-database updates. +Tells the slave thread to restrict replication to queries where any of +the updated tables match the specified wildcard pattern. To specify +more than one table, use the directive multiple times, once for each +table. This will work for cross-database updates. -Example: @code{replicate-wild-do-table=foo%.bar%} will replicate only updates -to tables in all databases that start with @code{foo} and whose table names -start with @code{bar}. +Example: @code{replicate-wild-do-table=foo%.bar%} will replicate only +updates that uses a table in any databases that start with @code{foo} +and whose table names start with @code{bar}. @item @code{replicate-wild-ignore-table=db_name.table_name} @tab -Tells the slave thread to not replicate to the tables that match the +Tells the slave thread to not replicate a query whare any table matches the given wildcard pattern. To specify more than one table to ignore, use the directive multiple times, once for each table. This will work for cross-database updates. @@ -24094,21 +24126,32 @@ to tables in databases that start with @code{foo} and whose table names start with @code{bar}. @item @code{replicate-ignore-db=database_name} @tab -Tells the slave thread to not replicate to the specified database. To -specify more than one database to ignore, use the directive multiple -times, once for each database. This option will not work if you use cross -database updates. If you need cross database updates to work, make sure -you have 3.23.28 or later, and use -@code{replicate-wild-ignore-table=db_name.%}. + +Tells the slave thread to not replicate any command where the current +database is @code{database_name}. To specify more than one database to +ignore, use the directive multiple times, once for each database. +You should not use this directive if you are using cross table updates +and you don't want these update to be replicated. + +The main reason for this behavior is that it's hard from the command +alone know if a query should be replicated or not; For example if you +are using multi-table-delete or multi-table-update commands in MySQL 4.x +that goes across multiple databases. It's also very fast to just check +the current database, as this only has to be done once at connect time +or when the database changes. + +If you need cross database updates to work, make sure you have 3.23.28 +or later, and use @code{replicate-wild-ignore-table=db_name.%}. Example: @code{replicate-ignore-db=some_db} @item @code{replicate-do-db=database_name} @tab -Tells the slave thread to restrict replication to the specified -database. To specify more than one database, use the directive multiple -times, once for each database. Note that this will only work if you do -not use cross-database queries such as @code{UPDATE some_db.some_table +Tells the slave thread to restrict replication to commands where +the current database is @code{database_name}. +To specify more than one database, use the directive multiple +times, once for each database. Note that this will not replicate +cross-database queries such as @code{UPDATE some_db.some_table SET foo='bar'} while having selected a different or no database. If you need cross database updates to work, make sure you have 3.23.28 or later, and use @code{replicate-wild-do-table=db_name.%}. @@ -24190,7 +24233,7 @@ summary of commands: @tab Re-enables update logging if the user has the @code{SUPER} privilege. Ignored otherwise. (Master) -@item @code{SET SQL_SLAVE_SKIP_COUNTER=n} +@item @code{GLOBAL SET SQL_SLAVE_SKIP_COUNTER=n} @tab Skip the next @code{n} events from the master. Only valid when the slave thread is not running, otherwise, gives an error. Useful for recovering from replication glitches. @@ -24696,9 +24739,9 @@ be safe to make the update manually ( if needed) and then ignore the next query from the master. @item If you have decided you can skip the next query, do -@code{SET SQL_SLAVE_SKIP_COUNTER=1; SLAVE START;} to skip a query that +@code{SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; SLAVE START;} to skip a query that does not use @code{AUTO_INCREMENT} or @code{LAST_INSERT_ID()}, or -@code{SET SQL_SLAVE_SKIP_COUNTER=2; SLAVE START;} otherwise. The reason +@code{SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; SLAVE START;} otherwise. The reason queries that use @code{AUTO_INCREMENT} or @code{LAST_INSERT_ID()} are different is that they take two events in the binary log of the master. @@ -26065,7 +26108,7 @@ Some ways to speed up inserts: If you are inserting many rows from the same client at the same time, use multiple value lists @code{INSERT} statements. This is much faster (many times in some cases) than using separate @code{INSERT} statements. If you are adding -data to non-empty table, you may tune up @code{myisam_bulk_insert_tree_size} +data to non-empty table, you may tune up @code{bulk_insert_buffer_size} variable to make it even faster. @xref{SHOW VARIABLES}. @item If you are inserting a lot of rows from different clients, you can get @@ -26550,8 +26593,8 @@ Start @code{mysqld} with a low value for @strong{max_write_lock_count} to give @item You can specify that all updates from a specific thread should be done with -low priority by using the SQL command: @code{SET SQL_LOW_PRIORITY_UPDATES=1}. -@xref{SET OPTION, , @code{SET OPTION}}. +low priority by using the SQL command: @code{SET LOW_PRIORITY_UPDATES=1}. +@xref{SET OPTION, , @code{SET}}. @item You can specify that a specific @code{SELECT} is very important with the @@ -27232,9 +27275,8 @@ net_buffer_length current value: 16384 net_retry_count current value: 10 net_read_timeout current value: 30 net_write_timeout current value: 60 -query_buffer_size current value: 0 -record_buffer current value: 131072 -record_rnd_buffer current value: 131072 +read_buffer_size current value: 131072 +record_rnd_buffer_size current value: 131072 slow_launch_time current value: 2 sort_buffer current value: 2097116 table_cache current value: 64 @@ -27271,7 +27313,7 @@ with a moderate number of clients, you should use something like this: @example shell> safe_mysqld -O key_buffer=64M -O table_cache=256 \ - -O sort_buffer=4M -O record_buffer=1M & + -O sort_buffer=4M -O read_buffer_size=1M & @end example If you have only 128M and only a few tables, but you still do a lot of @@ -27285,14 +27327,14 @@ If you have little memory and lots of connections, use something like this: @example shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k \ - -O record_buffer=100k & + -O read_buffer_size=100k & @end example or even: @example shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \ - -O table_cache=32 -O record_buffer=8k -O net_buffer=1K & + -O table_cache=32 -O read_buffer_size=8k -O net_buffer_length=1K & @end example If you are doing a @code{GROUP BY} or @code{ORDER BY} on files that are @@ -27484,8 +27526,8 @@ automatically changing the in-memory (HEAP) table to a disk-based (MyISAM) table as necessary. To work around this problem, you can 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 MySQL Version 3.20, the maximum size of the +@code{BIG_TABLES} in the client program. @xref{SET OPTION, , +@code{SET}}. In 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 @@ -27579,12 +27621,86 @@ by starting @code{mysqld} with @code{--skip-networking}. @findex SET OPTION @example -SET [OPTION] SQL_VALUE_OPTION= value, ... +SET [GLOBAL | SESSION] sql_variable=expression, [[GLOBAL | SESSION] sql_variable=expression...] +@end example + +@code{SET} sets various options that affect the operation of the +server or your client. + +The following examples shows the different syntaxes one can use to set +variables: + +In old MySQL versions we allowed the use of the @code{SET OPTION} syntax, +but this syntax is now deprecated. + +In MySQL 4.0.3 we added the @code{GLOBAL} and @code{SESSION} options +and access to most important startup variables. + +@code{LOCAL} can be used as a synonym for @code{SESSION}. + +If you set several variables on the same command line, the last used +@code{GLOBAL | SESSION} mode is used. + +@example +SET sort_buffer_size=10000; +SET @@@@local.sort_buffer_size=10000; +SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000; +SET @@@@sort_buffer_size=1000000; +SET @@@@global.sort_buffer_size=1000000, @@@@local.sort_buffer_size=1000000; +@end example + +The @code{@@@@variable_name} syntax is supported to make MySQL syntax +compatible with some other databases. + +The different system variables one can set are described in the system +variable section of this manual. @xref{System Variables}. + +If you are using @code{SESSION} (the default) the option you set remains +in effect until the current session ends, or until you set the option to +a different value. If you use @code{GLOBAL}, which require the +@code{SUPER} privilege, the option is remembered and used for new +connections until the server restarts. If you want to make an option +permanent, you should set it in one of the MySQL option +files. @xref{Option files}. + +To avoid wrong usage MySQL will give an error if you use @code{SET +GLOBAL} with a variable that can only be used with @code{SET SESSION} or if +you are not using @code{SET GLOBAL} with a global variable. + +If you want to set a @code{SESSION} variable to the @code{GLOBAL} value or a +@code{GLOBAL} value to the MySQL default value, you can set it to +@code{DEFAULT}. + +@example +SET max_join_size=DEFAULT; +@end example + +This is idential to: + +@example +SET @@@@session.max_join_size=@@@@global.max_join_size; +@end example + +If you want to restrict the maximum value a startup option can be set to +with the @code{SET} command, you can specify this by using the +@code{--maximum-variable-name} command line option. @xref{Command-line +options}. + +You can get a list of most variables with @code{SHOW VARIABLES}. +@xref{SHOW VARIABLES}. You can get the value for a specific value with +the @code{@@@@[global.|local.]variable_name} syntax: +@example +SHOW VARIABLES like "max_join_size"; +SHOW GLOBAL VARIABLES like "max_join_size"; +SELECT @@@@max_join_size, @@@@global.max_join_size; @end example -@code{SET OPTION} sets various options that affect the operation of the -server or your client. Any option you set remains in effect until the -current session ends, or until you set the option to a different value. +Here follows a description of the variables that uses a the variables +that uses a non-standard @code{SET} syntax and some of the other +variables. The other variable definitions can be found in the system +variable section, among the startup options or in the description of +@code{SHOW VARIABLES}. @xref{System Variables}. @xref{Command-line +options}. @xref{SHOW VARIABLES}. @table @code @item CHARACTER SET character_set_name | DEFAULT @@ -27636,13 +27752,14 @@ that when you change from not @code{AUTOCOMMIT} mode to @code{AUTOCOMMIT} mode, MySQL will do an automatic @code{COMMIT} on any open transactions. -@item SQL_BIG_TABLES = 0 | 1 +@item BIG_TABLES = 0 | 1 @cindex table is full 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} (that is, use in-memory temporary tables). +This option was before named @code{SQL_BIG_TABLES}. @item SQL_BIG_SELECTS = 0 | 1 If set to @code{0}, MySQL will abort if a @code{SELECT} is attempted @@ -27658,12 +27775,13 @@ to be put into a temporary table. This will help 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. -@item SQL_LOW_PRIORITY_UPDATES = 0 | 1 +@item LOW_PRIORITY_UPDATES = 0 | 1 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. +This option was before named @code{SQL_LOW_PRIORITY_UPDATES}. -@item SQL_MAX_JOIN_SIZE = value | DEFAULT +@item MAX_JOIN_SIZE = value | DEFAULT 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 @@ -27672,14 +27790,15 @@ 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. You can set a default value for this variable by starting @code{mysqld} with @code{-O max_join_size=#}. +This option was before named @code{SQL_MAX_JOIN_SIZE}. Note that if the result of the query is already in the query cache, the above check will not be made. Instead, MySQL will send the result to the client. Since the query result is already computed and it will not burden the server to send the result to the client. -@item SQL_QUERY_CACHE_TYPE = OFF | ON | DEMAND -@item SQL_QUERY_CACHE_TYPE = 0 | 1 | 2 +@item QUERY_CACHE_TYPE = OFF | ON | DEMAND +@item QUERY_CACHE_TYPE = 0 | 1 | 2 Set query cache setting for this thread. @multitable @columnfractions .20 .70 @item @strong{Option} @tab @strong{Description} @@ -27733,8 +27852,6 @@ command when inserting an @code{AUTO_INCREMENT} value. This is mainly used with the update log. @end table -@xref{SET TRANSACTION}. - @node Disk issues, , Optimising the Server, MySQL Optimisation @section Disk Issues @@ -27822,7 +27939,7 @@ too much reliability. (This flag is on by default on Linux.) @item If you don't need to know when a file was last accessed (which is not really useful on a database server), you can mount your filesystems -with the noatime flag. +with the @code{noatime} flag. @end itemize @menu @@ -28008,6 +28125,7 @@ may find it useful to refer to the various indexes. * Legal names:: Database, Table, Index, Column, and Alias Names * Name case sensitivity:: Case-Sensitivity in Names * Variables:: User Variables +* System Variables:: System Variables * Comments:: Comment Syntax * Reserved words:: Is MySQL Picky About Reserved Words? @end menu @@ -28428,14 +28546,14 @@ the case of the file extensions in each specified database directory @file{mysql_fix_extensions} can be found in the @file{script} subdirectory. -@node Variables, Comments, Name case sensitivity, Language Structure +@node Variables, System Variables, Name case sensitivity, Language Structure @subsection User Variables @cindex variables, user @cindex user variables @cindex names, variables -MySQL supports thread-specific variables with the +MySQL supports thread-specific user variables with the @code{@@variablename} syntax. A variable name may consist of alphanumeric characters from the current character set and also @samp{_}, @samp{$}, and @samp{.} . The default character set is @@ -28486,7 +28604,181 @@ mysql> SELECT (@@aa:=id) AS a, (@@aa+3) AS b FROM table_name HAVING b=5; The reason is that @code{@@aa} will not contain the value of the current row, but the value of @code{id} for the previous accepted row. -@node Comments, Reserved words, Variables, Language Structure +@node System Variables, Comments, Variables, Language Structure +@subsection System Variables + +Starting from MySQL 4.0.3 we provide better access to a lot of system +variables and one can change most of them without having to take +down the server. + +There are two kind of system variables: Thread (or connection) specific +variables that are unique to the current connection and global variables +that are either used to configure global events or used as initial +variables for a new connection. + +When mysqld starts all global variables are initialised from command +line arguments and option files. You can change the used value with the +@code{SET GLOBAL} command. When a new thread is created the thread +specific variables are initialised from the global variables and they +will not change even if one issues a new @code{SET GLOBAL} command. + +To set the value for a @code{GLOBAL} variable, you should use one +of the following syntaxes: +(Here we use @code{sort_buffer_size} as an example variable) + +@example +SET GLOBAL sort_buffer_size=value; +SET @@@@global.sort_buffer_size=value; +@end example + +To set the value for @code{SESSION} variable, you can use on of the +following syntaxes: + +@example +SET SESSION sort_buffer_size=value; +SET @@@@session.sort_buffer_size=value; +SET sort_buffer_size=value; +@end example + +If you don't specify @code{GLOBAL} or @code{SESSION} then @code{SESSION} +is used. @xref{SET OPTION}. + +@code{LOCAL} is a synonym for @code{SESSION}. + +To retrieve the value for a @code{GLOBAL} variable you can use one of the +following commands: + +@example +SELECT @@@@global.sort_buffer_size; +SHOW GLOBAL VARIABLES like 'sort_buffer_size'; +@end example + +To retrieve the value for a @code{SESSION} variable you can use one of the +following commands: + +@example +SELECT @@@@session.sort_buffer_size; +SHOW SESSION VARIABLES like 'sort_buffer_size'; +@end example + +When you @strong{retrieve} a variable value with the +@code{@@@@variable_name} syntax and you don't specify @code{GLOBAL} or +@code{SESSION} then MySQL will return the thread specific +(@code{SESSION}) value if a such exist. If not, MySQL will return the +global value. + +The reason for requiring @code{GLOBAL} for setting @code{GLOBAL} only +variables but not for retrieving them is to ensure that we don't later +run into problems if we later would introduce a thread specific variable +with the same name or remove a thread specific variable. In this case +you could accidently change the state for the whole server and not +just for your own connection. + +The following is a full list of all variables that you change and retrieve +and if you can use @code{GLOBAL} or @code{SESSION} with them. + +@multitable @columnfractions .50 .25 .25 +@item @strong{Variable name} @tab @strong{Value type} @tab @strong{Type} +@item autocommit @tab bool @tab SESSION +@item big_tables @tab bool @tab SESSION +@item binlog_cache_size @tab num @tab GLOBAL +@item bulk_insert_buffer_size @tab num @tab GLOBAL | SESSION +@item concurrent_insert @tab bool @tab GLOBAL +@item connect_timeout @tab num @tab GLOBAL +@item convert_character_set @tab string @tab SESSION +@item delay_key_write @tab bool @tab GLOBAL +@item delayed_insert_limit @tab num @tab GLOBAL +@item delayed_insert_timeout @tab num @tab GLOBAL +@item delayed_queue_size @tab num @tab GLOBAL +@item flush @tab bool @tab GLOBAL +@item flush_time @tab num @tab GLOBAL +@item identity @tab num @tab SESSION +@item insert_id @tab bool @tab SESSION +@item interactive_timeout @tab num @tab GLOBAL | SESSION +@item join_buffer_size @tab num @tab GLOBAL | SESSION +@item key_buffer_size @tab num @tab GLOBAL +@item last_insert_id @tab bool @tab SESSION +@item local_infile @tab bool @tab GLOBAL +@item log_warnings @tab bool @tab GLOBAL +@item long_query_time @tab num @tab GLOBAL | SESSION +@item low_priority_updates @tab bool @tab GLOBAL | SESSION +@item max_allowed_packet @tab num @tab GLOBAL | SESSION +@item max_binlog_cache_size @tab num @tab GLOBAL +@item max_binlog_size @tab num @tab GLOBAL +@item max_connect_errors @tab num @tab GLOBAL +@item max_connections @tab num @tab GLOBAL +@item max_delayed_threads @tab num @tab GLOBAL +@item max_heap_table_size @tab num @tab GLOBAL | SESSION +@item max_join_size @tab num @tab GLOBAL | SESSION +@item max_sort_length @tab num @tab GLOBAL | SESSION +@item max_tmp_tables @tab num @tab GLOBAL +@item max_user_connections @tab num @tab GLOBAL +@item max_write_lock_count @tab num @tab GLOBAL +@item myisam_max_extra_sort_file_size @tab num @tab GLOBAL | SESSION +@item myisam_max_sort_file_size @tab num @tab GLOBAL | SESSION +@item myisam_sort_buffer_size @tab num @tab GLOBAL | SESSION +@item net_buffer_length @tab num @tab GLOBAL | SESSION +@item net_read_timeout @tab num @tab GLOBAL | SESSION +@item net_write_timeout @tab num @tab GLOBAL | SESSION +@item query_cache_limit @tab num @tab GLOBAL +@item query_cache_size @tab num @tab GLOBAL +@item query_cache_type @tab enum @tab GLOBAL +@item read_buffer_size @tab num @tab GLOBAL | SESSION +@item read_rnd_buffer_size num @tab GLOBAL | SESSION +@item rpl_recovery_rank @tab num @tab GLOBAL +@item safe_show_database @tab bool @tab GLOBAL +@item server_id @tab num @tab GLOBAL +@item slave_net_timeout @tab num @tab GLOBAL +@item slow_launch_time @tab num @tab GLOBAL +@item sort_buffer_size @tab num @tab GLOBAL | SESSION +@item sql_auto_is_null @tab bool @tab SESSION +@item sql_big_selects @tab bool @tab SESSION +@item sql_big_tables @tab bool @tab SESSION +@item sql_buffer_result @tab bool @tab SESSION +@item sql_log_binlog @tab bool @tab SESSION +@item sql_log_off @tab bool @tab SESSION +@item sql_log_update @tab bool @tab SESSION +@item sql_low_priority_updates @tab bool @tab GLOBAL | SESSION +@item sql_max_join_size @tab num @tab GLOBAL | SESSION +@item sql_quote_show_create @tab bool @tab SESSION +@item sql_safe_updates @tab bool @tab SESSION +@item sql_select_limit @tab bool @tab SESSION +@item sql_slave_skip_counter @tab num @tab GLOBAL +@item sql_warnings @tab bool @tab SESSION +@item table_cache @tab num @tab GLOBAL +@item table_type @tab enum @tab GLOBAL | SESSION +@item thread_cache_size @tab num @tab GLOBAL +@item timestamp @tab bool @tab SESSION +@item tmp_table_size @tab enum @tab GLOBAL | SESSION +@item tx_isolation @tab enum @tab GLOBAL | SESSION +@item version @tab string @tab GLOBAL +@item wait_timeout @tab num @tab GLOBAL | SESSION +@end multitable + +Variables that are marked with @code{num} can be given a numerical +value. Variables that are marked with @code{bool} can be set to 0, 1, +@code{ON} or @code{OFF}. Variables that are of type @code{enum} should +normally be set to one of the available values for the variable, but can +also be set to the number that correspond to the enum value. (The first +enum value is 0). + +Here is a description of some of the variables: + +@multitable @columnfractions 0.30 0.70 +@item @strong{Variable} @tab @strong{Description} +@item identity @tab Alias for last_insert_id (Sybase compatiblity) +@item sql_low_priority_updates @tab Alias for low_priority_updates +@item sql_max_join_size @tab Alias for max_join_size +@item version @tab Alias for VERSION() (Sybase (?) compatability) +@end multitable + +A description of the other variable definitions can be found in the +startup options section, the description of @code{SHOW VARIABLES} and in +the @code{SET} section. @xref{Command-line +options}. @xref{SHOW VARIABLES}. @xref{SET OPTION}. + + +@node Comments, Reserved words, System Variables, Language Structure @subsection Comment Syntax @findex Comment syntax @@ -28559,7 +28851,7 @@ A few are reserved because MySQL needs them and is @c START_OF_RESERVED_WORDS -@c Reserved word list updated Fri Jun 14 09:34:10 2002 by arjen. +@c Reserved word list updated Tue Jul 23 02:10:12 2002 by monty. @c To regenerate, use Support/update-reserved-words.pl. @multitable @columnfractions .33 .33 .34 @@ -28637,118 +28929,103 @@ A few are reserved because MySQL needs them and is @tab @code{INNER} @item @code{INNODB} @tab @code{INSERT} - @tab @code{INSERT_ID} -@item @code{INT} - @tab @code{INTEGER} + @tab @code{INT} +@item @code{INTEGER} @tab @code{INTERVAL} -@item @code{INTO} - @tab @code{IS} + @tab @code{INTO} +@item @code{IS} @tab @code{JOIN} -@item @code{KEY} - @tab @code{KEYS} + @tab @code{KEY} +@item @code{KEYS} @tab @code{KILL} -@item @code{LAST_INSERT_ID} @tab @code{LEADING} - @tab @code{LEFT} -@item @code{LIKE} +@item @code{LEFT} + @tab @code{LIKE} @tab @code{LIMIT} - @tab @code{LINES} -@item @code{LOAD} +@item @code{LINES} + @tab @code{LOAD} @tab @code{LOCK} - @tab @code{LONG} -@item @code{LONGBLOB} +@item @code{LONG} + @tab @code{LONGBLOB} @tab @code{LONGTEXT} - @tab @code{LOW_PRIORITY} -@item @code{MASTER_SERVER_ID} +@item @code{LOW_PRIORITY} + @tab @code{MASTER_SERVER_ID} @tab @code{MATCH} - @tab @code{MEDIUMBLOB} -@item @code{MEDIUMINT} +@item @code{MEDIUMBLOB} + @tab @code{MEDIUMINT} @tab @code{MEDIUMTEXT} - @tab @code{MIDDLEINT} -@item @code{MINUTE_SECOND} +@item @code{MIDDLEINT} + @tab @code{MINUTE_SECOND} @tab @code{MRG_MYISAM} - @tab @code{NATURAL} -@item @code{NOT} +@item @code{NATURAL} + @tab @code{NOT} @tab @code{NULL} - @tab @code{NUMERIC} -@item @code{ON} +@item @code{NUMERIC} + @tab @code{ON} @tab @code{OPTIMIZE} - @tab @code{OPTION} -@item @code{OPTIONALLY} +@item @code{OPTION} + @tab @code{OPTIONALLY} @tab @code{OR} - @tab @code{ORDER} -@item @code{OUTER} +@item @code{ORDER} + @tab @code{OUTER} @tab @code{OUTFILE} - @tab @code{PARTIAL} -@item @code{PRECISION} +@item @code{PARTIAL} + @tab @code{PRECISION} @tab @code{PRIMARY} - @tab @code{PRIVILEGES} -@item @code{PROCEDURE} +@item @code{PRIVILEGES} + @tab @code{PROCEDURE} @tab @code{PURGE} - @tab @code{READ} -@item @code{REAL} +@item @code{READ} + @tab @code{REAL} @tab @code{REFERENCES} - @tab @code{REGEXP} -@item @code{RENAME} +@item @code{REGEXP} + @tab @code{RENAME} @tab @code{REPLACE} - @tab @code{REQUIRE} -@item @code{RESTRICT} +@item @code{REQUIRE} + @tab @code{RESTRICT} @tab @code{RETURNS} - @tab @code{REVOKE} -@item @code{RIGHT} +@item @code{REVOKE} + @tab @code{RIGHT} @tab @code{RLIKE} - @tab @code{SELECT} -@item @code{SET} +@item @code{SELECT} + @tab @code{SET} @tab @code{SHOW} - @tab @code{SMALLINT} -@item @code{SONAME} - @tab @code{SQL_AUTO_IS_NULL} +@item @code{SMALLINT} + @tab @code{SONAME} @tab @code{SQL_BIG_RESULT} -@item @code{SQL_BIG_SELECTS} - @tab @code{SQL_BIG_TABLES} - @tab @code{SQL_BUFFER_RESULT} @item @code{SQL_CALC_FOUND_ROWS} - @tab @code{SQL_LOG_BIN} - @tab @code{SQL_LOG_OFF} -@item @code{SQL_LOG_UPDATE} - @tab @code{SQL_LOW_PRIORITY_UPDATES} - @tab @code{SQL_MAX_JOIN_SIZE} -@item @code{SQL_QUOTE_SHOW_CREATE} - @tab @code{SQL_SAFE_UPDATES} - @tab @code{SQL_SELECT_LIMIT} -@item @code{SQL_SLAVE_SKIP_COUNTER} @tab @code{SQL_SMALL_RESULT} - @tab @code{SQL_WARNINGS} -@item @code{SSL} - @tab @code{STARTING} + @tab @code{SSL} +@item @code{STARTING} @tab @code{STRAIGHT_JOIN} -@item @code{STRIPED} - @tab @code{TABLE} + @tab @code{STRIPED} +@item @code{TABLE} @tab @code{TABLES} -@item @code{TERMINATED} - @tab @code{THEN} + @tab @code{TERMINATED} +@item @code{THEN} @tab @code{TINYBLOB} -@item @code{TINYINT} - @tab @code{TINYTEXT} + @tab @code{TINYINT} +@item @code{TINYTEXT} @tab @code{TO} -@item @code{TRAILING} - @tab @code{UNION} + @tab @code{TRAILING} +@item @code{UNION} @tab @code{UNIQUE} -@item @code{UNLOCK} - @tab @code{UNSIGNED} + @tab @code{UNLOCK} +@item @code{UNSIGNED} @tab @code{UPDATE} -@item @code{USAGE} - @tab @code{USE} + @tab @code{USAGE} +@item @code{USE} @tab @code{USER_RESOURCES} -@item @code{USING} - @tab @code{VALUES} + @tab @code{USING} +@item @code{VALUES} @tab @code{VARBINARY} -@item @code{VARCHAR} - @tab @code{VARYING} + @tab @code{VARCHAR} +@item @code{VARYING} @tab @code{WHEN} -@item @code{WHERE} - @tab @code{WITH} + @tab @code{WHERE} +@item @code{WITH} @tab @code{WRITE} + @tab @code{XOR} @item @code{YEAR_MONTH} @tab @code{ZEROFILL} @tab @@ -33742,7 +34019,7 @@ FOUND_ROWS()}. @xref{Miscellaneous functions}. @item @code{SQL_CACHE} tells MySQL to store the query result in the query cache -if you are using @code{SQL_QUERY_CACHE_TYPE=2} (@code{DEMAND}). +if you are using @code{QUERY_CACHE_TYPE=2} (@code{DEMAND}). @xref{Query Cache}. @item @@ -34094,8 +34371,7 @@ HANDLER tbl_name CLOSE @end example The @code{HANDLER} statement provides direct access to the @code{MyISAM} table -handler interface, bypassing the SQL optimiser. Thus, it is faster than -@code{SELECT}. +handler interface. The first form of @code{HANDLER} statement opens a table, making it accessible via subsequent @code{HANDLER ... READ} statements. @@ -34129,6 +34405,34 @@ means that after a @code{HANDLER ... OPEN} is issued, table data can be modified (by this or any other thread) and these modifications may appear only partially in @code{HANDLER ... NEXT} or @code{HANDLER ... PREV} scans. +The reasons to use this interface instead of normal SQL are: + +@itemize @bullet +@item +It's faster than @code{SELECT} because: +@itemize @bullet +@item +A designated table handler is allocated for the thread in @code{HANDLER open}. +@item +There is less parsing involved. +@item +No optimizer and no query checking overhead. +@item +The used table doesn't have to be locked between two handler requests. +@item +The handler interface doesn't have to provide a consistent look of the +data (for example dirty-reads are allow), which allows the table handler +to do optimizations that SQL doesn't normally allow. +@end itemize +@item +It makes it much easier to port applications that uses an ISAM like +interface to MySQL. +@item +It allows one to traverse a database in a manner that is not easy +(in some case impossible) to do with SQL. The handler interface is +more natural way to look at data when working with applications that +provide an interactive user interfaces to the database. +@end itemize @node INSERT, INSERT DELAYED, HANDLER, Data Manipulation @subsection @code{INSERT} Syntax @@ -37024,7 +37328,7 @@ Don't cache results that are bigger than this. (Default 1M). The memory allocated to store results from old queries. If this is 0, the query cache is disabled (default). -@item @code{query_cache_startup_type} +@item @code{query_cache_type} This may be set (only numeric) to @multitable @columnfractions .10 .75 @item @strong{Option} @tab @strong{Description} @@ -37034,12 +37338,11 @@ This may be set (only numeric) to @end multitable @end itemize - Inside a thread (connection), the behaviour of the query cache can be changed from the default. The syntax is as follows: -@code{SQL_QUERY_CACHE_TYPE = OFF | ON | DEMAND} -@code{SQL_QUERY_CACHE_TYPE = 0 | 1 | 2} +@code{QUERY_CACHE_TYPE = OFF | ON | DEMAND} +@code{QUERY_CACHE_TYPE = 0 | 1 | 2} @multitable @columnfractions .20 .70 @item @strong{Option} @tab @strong{Description} @@ -37048,9 +37351,6 @@ changed from the default. The syntax is as follows: @item 2 or DEMAND @tab Cache only @code{SELECT SQL_CACHE ...} queries. @end multitable -By default @code{SQL_QUERY_CACHE_TYPE} depends on the value of -@code{query_cache_startup_type} when the thread was created. - @node Query Cache in SELECT, Query Cache Status and Maintenance, Query Cache Configuration, Query Cache @subsection Query Cache Options in @code{SELECT} @@ -37064,9 +37364,9 @@ specified in a @code{SELECT} query: @multitable @columnfractions .20 .80 @item @strong{Option} @tab @strong{Description} @item @code{SQL_CACHE} - @tab If @code{SQL_QUERY_CACHE_TYPE} is @code{DEMAND}, allow the query to be cached. - If @code{SQL_QUERY_CACHE_TYPE} is @code{ON}, this is the default. - If @code{SQL_QUERY_CACHE_TYPE} is @code{OFF}, do nothing. + @tab If @code{QUERY_CACHE_TYPE} is @code{DEMAND}, allow the query to be cached. + If @code{QUERY_CACHE_TYPE} is @code{ON}, this is the default. + If @code{QUERY_CACHE_TYPE} is @code{OFF}, do nothing. @item @code{SQL_NO_CACHE} @tab Make this query non-cachable, don't allow this query to be stored in the cache. @end multitable @@ -37095,7 +37395,7 @@ You can monitor query cache performance in @code{SHOW STATUS}: @tab Number of cache hits. @item @code{Qcache_not_cached} @tab Number of non-cached queries - (not cachable, or due to @code{SQL_QUERY_CACHE_TYPE}). + (not cachable, or due to @code{QUERY_CACHE_TYPE}). @item @code{Qcache_free_memory} @tab Amount of free memory for query cache. @item @code{Qcache_total_blocks} @@ -37330,7 +37630,7 @@ The following options to @code{mysqld} can be used to change the behaviour of @item @code{--delay-key-write-for-all-tables} @tab Don't flush key buffers between writes for any MyISAM table @item @code{-O myisam_max_extra_sort_file_size=#} @tab Used to help MySQL to decide when to use the slow but safe key cache index create method. @strong{Note} that this parameter is given in megabytes! @item @code{-O myisam_max_sort_file_size=#} @tab Don't use the fast sort index method to created index if the temporary file would get bigger than this. @strong{Note} that this paramter is given in megabytes! -@item @code{-O myisam_bulk_insert_tree_size=#} @tab Size of tree cache used in bulk insert optimisation. @strong{Note} that this is a limit @strong{per thread}! +@item @code{-O bulk_insert_buffer_size=#} @tab Size of tree cache used in bulk insert optimisation. @strong{Note} that this is a limit @strong{per thread}! @end multitable The automatic recovery is activated if you start @code{mysqld} with @@ -38369,11 +38669,11 @@ too high}. glibc will allow the process heap to grow over thread stacks, which will crash your server. It is a risk if the value of @example innodb_buffer_pool_size + key_buffer + -max_connections * (sort_buffer + record_buffer) + max_connections * 2 MB +max_connections * (sort_buffer + read_buffer_size) + max_connections * 2 MB @end example is close to 2 GB or exceeds 2 GB. Each thread will use a stack (often 2 MB, but in MySQL AB binaries only 256 kB) and in the worst case also -@code{sort_buffer + record_buffer} +@code{sort_buffer + read_buffer_size} additional memory. @strong{How to tune other @file{mysqld} server parameters?} @@ -38381,7 +38681,7 @@ Typical values which suit most users are: @example skip-locking set-variable = max_connections=200 -set-variable = record_buffer=1M +set-variable = read_buffer_size=1M set-variable = sort_buffer=1M # Set key_buffer to 5 - 50% # of your RAM depending on how @@ -46878,11 +47178,11 @@ You are using an older MySQL version (before 3.23.0) when an in-memory temporary table becomes larger than @code{tmp_table_size} bytes. To avoid this problem, you can use the @code{-O tmp_table_size=#} option to make @code{mysqld} increase the temporary table size or use the SQL -option @code{SQL_BIG_TABLES} before you issue the problematic query. -@xref{SET OPTION, ,@code{SET OPTION}}. +option @code{BIG_TABLES} before you issue the problematic query. +@xref{SET OPTION, ,@code{SET}}. 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. +This is exactly the same as using @code{BIG_TABLES} for all queries. In MySQL Version 3.23, in-memory temporary tables will automatically be converted to a disk-based @code{MyISAM} table after the table size gets @@ -49674,12 +49974,35 @@ Our TODO section contains what we plan to have in 4.0. @xref{TODO MySQL 4.0}. @cindex changes, version 4.0 @menu +* News-4.0.3:: Changes in release 4.0.3 * News-4.0.2:: Changes in release 4.0.2 (01 July 2002) * News-4.0.1:: Changes in release 4.0.1 (23 Dec 2001) * News-4.0.0:: Changes in release 4.0.0 (Oct 2001: Alpha) @end menu -@node News-4.0.2, News-4.0.1, News-4.0.x, News-4.0.x +@node News-4.0.3, News-4.0.2, News-4.0.x, News-4.0.x +@appendixsubsec Changes in release 4.0.3 +@itemize @bullet +The startup parameters @code{myisam_max_extra_sort_file_size} and +@code{myisam_max_extra_sort_file_size} are now given in bytes, not megabytes. +@item +Fixed a timing bug in @code{DROP DATABASE} +@item +New @code{SET [GLOBAL | SESSION]} syntax to change thread specific and global +server variables at runtime. +@item +Renamed variable @code{query_cache_startup_type} to @code{query_cache_type}, +@code{myisam_bulk_insert_tree_size} to @code{bulk_insert_buffer_size}, +@code{record_buffer} to @code{read_buffer_size} and +@code{record_rnd_buffer} to @code{record_rnd_buffer_size}. +@item +Renamed some sql variables, but old names will still work until 5.0. +@xref{Upgrading-from-3.23}. +@item +Removed not used variable @code{query_buffer_size}. +@end itemize + +@node News-4.0.2, News-4.0.1, News-4.0.3, News-4.0.x @appendixsubsec Changes in release 4.0.2 (01 July 2002) @itemize @bullet @@ -53273,7 +53596,7 @@ construct returned the rightmost 'pos' characters.) @item Fixed problem with @code{LOCK TABLES} combined with @code{DELETE FROM table} @item -Fixed problem that @code{INSERT ... SELECT} didn't use @code{SQL_BIG_TABLES}. +Fixed problem that @code{INSERT ... SELECT} didn't use @code{BIG_TABLES}. @item @code{SET SQL_LOW_PRIORITY_UPDATES=#} didn't work. @item @@ -53902,7 +54225,7 @@ assumes the value is given as: @code{[[[D ]HH:]MM:]SS} instead of and hours up to 32767. @item Added new option -@code{SET OPTION SQL_LOG_UPDATE=@{0|1@}} to allow users with +@code{SET SQL_LOG_UPDATE=@{0|1@}} to allow users with the @code{PROCESS} privilege to bypass the update log. (Modified patch from Sergey A Mukhin @email{violet@@rosnet.net}.) @item @@ -53921,7 +54244,7 @@ Added @code{--low-priority-updates} option to @code{mysqld}, to give table-modifying operations (@code{INSERT}, @code{REPLACE}, @code{UPDATE}, @code{DELETE}) lower priority than retrievals. You can now use @code{@{INSERT | REPLACE | UPDATE | DELETE@} LOW_PRIORITY ...} You can -also use @code{SET OPTION SQL_LOW_PRIORITY_UPDATES=@{0|1@}} to change +also use @code{SET SQL_LOW_PRIORITY_UPDATES=@{0|1@}} to change the priority for one thread. One side effect is that @code{LOW_PRIORITY} is now a reserved word. :( @item @@ -54969,7 +55292,7 @@ Fixed better @code{FOREIGN KEY} syntax skipping. New reserved words: @code{mysqld} now allows IP number and hostname for the @code{--bind-address} option. @item -Added @code{SET OPTION CHARACTER SET cp1251_koi8} to enable conversions of +Added @code{SET CHARACTER SET cp1251_koi8} to enable conversions of data to and from the @code{cp1251_koi8} character set. @item Lots of changes for Windows 95 port. In theory, this version should now be @@ -55229,7 +55552,7 @@ Added @code{-O join_cache_size=#} option to @code{mysqld}. @item Added @code{-O max_join_size=#} option to @code{mysqld}, to be able to set a limit how big queries (in this case big = slow) one should be able to handle -without specifying @code{SET OPTION SQL_BIG_SELECTS=1}. A # = is about 10 +without specifying @code{SET SQL_BIG_SELECTS=1}. A # = is about 10 examined records. The default is ``unlimited''. @item When comparing a @code{TIME}, @code{DATE}, @code{DATETIME} or @code{TIMESTAMP} @@ -55518,7 +55841,7 @@ Now @code{LIKE} is always case-insensitive. @item @file{mysql.cc}: Allow @code{'#'} anywhere on the line. @item -New command @code{SET OPTION SQL_SELECT_LIMIT=#}. See the FAQ for more details. +New command @code{SET SQL_SELECT_LIMIT=#}. See the FAQ for more details. @item New version of the @code{mysqlaccess} script. @item |