bk commit - mysqldoc tree (1.446)

Below is the list of changes that have just been committed into a local
mysqldoc repository of monty. When monty does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://www.mysql.com/doc/I/n/Installing_source_tree.html

ChangeSet
  1.446 03/04/27 21:59:23 monty@xxxxxxxxxxxxxxx +1 -0
  Updated upgrading to MySQL 4.1 section.
  Changelog
  Updated replication section with known problems with replication.
  Split replication problems section to have problems unique to 3.23 in a 
separate section.

  Docs/manual.texi
    1.420 03/04/27 21:59:22 monty@xxxxxxxxxxxxxxx +180 -69
    Updated upgrading to MySQL 4.1 section.
    Changelog
    Updated replication section with known problems with replication.
    Split replication problems section to have problems unique to 3.23 in a 
separate section.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: monty
# Host: mashka.mysql.fi
# Root: /home/my/mysqldoc

--- 1.419/Docs/manual.texi      Thu Apr 24 09:17:26 2003
+++ 1.420/Docs/manual.texi      Sun Apr 27 21:59:22 2003
@@ -3640,7 +3640,7 @@
 @node Open bugs,  , Errors in 3.23, Bugs
 @subsubsection Open Bugs / Design Deficiencies in MySQL
 
-The following problems are known and fixing them is a very high priority:
+The following problems are known and fixing them is a high priority:
 
 @itemize @bullet
 @item
@@ -3680,6 +3680,10 @@
 @itemize @bullet
 
 @item
+@code{LIKE} is not @code{multi-byte character} safe. Comparison is done
+character by character.
+@item
+
 When using @code{RPAD} function, or any other string function that ends
 up adding blanks to the right, in a query that has to use temporary
 table to be resolved, then all resulting strings will be RTRIM'ed. This
@@ -9026,7 +9030,6 @@
 
 @menu
 * Upgrading-from-4.0::          Upgrading From Version 4.0 to 4.1
-* Prepare-upgrade-4.0-4.1::     Preparing to Upgrade From Version 4.0 to 4.1
 * Upgrading-from-3.23::         Upgrading From Version 3.23 to 4.0
 * Upgrading-from-3.22::         Upgrading From Version 3.22 to 3.23
 * Upgrading-from-3.21::         Upgrading from Version 3.21 to 3.22
@@ -9036,9 +9039,73 @@
 @end menu
 
 
-@node Upgrading-from-4.0, Prepare-upgrade-4.0-4.1, Upgrade, Upgrade
+@node Upgrading-from-4.0, Upgrading-from-3.23, Upgrade, Upgrade
 @subsection Upgrading From Version 4.0 to 4.1
 
+@menu
+* Prepare-upgrade-4.0-4.1::     Preparing to Upgrade From Version 4.0 to 4.1
+* What-to-do-from-4.0::         What to do when upgrading from 4.0 to 4.1
+* What-changed-in-4.1::         What has changed between 4.0 and 4.1
+@end menu
+
+@node Prepare-upgrade-4.0-4.1, What-to-do-from-4.0, Upgrading-from-4.0, 
Upgrading-from-4.0
+@subsubsection Preparing to Upgrade From Version 4.0 to 4.1
+
+@cindex upgrading, 4.0 to 4.1
+
+Some visible things have changed between MySQL 4.0 and MySQL 4.1 to fix
+some critical bugs and make MySQL more compatible with the ANSI SQL
+standard.
+
+Instead of adding options (and a lot of code) to try to make 4.1 behave
+like 4.0 we have taken another approach:
+
+We have added to the later MySQL 4.0 releases the @code{--new} mysqld
+startup option,, which gives you the 4.1 behaviour for the most critical
+changes.  You can also set this for just one thread with the @code{SET
+@@@@new=1 command}.
+
+If you belive that some of the following changes will affect you when
+you upgrade to 4.1, we recommend you to instead download the latest
+MySQL 4.0 version and fix that your application works in the @code{--new
+mode}.  This way you will later have a smooth painless upgrade.
+
+
+In MySQL 4.1 we have done the following things that may affect some
+applications:
+
+@itemize
+@item
+@code{TIMESTAMP} is now returned as string with the format
+@code{'YYYY-MM-DD HH:MM:DD'}. If you want to have this as a number (like
+you Version 4.0 does) should add +0 to the timestamp column.  Different
+timestamp lengths are not supported.
+
+This change was necessary for SQL standards compliance. In a future
+version, a further change will be made (backward compatible with this
+change), allowing the timestamp length to indicate the desired number of
+digits of fractions of a second.
+
+From version 4.0.12, the @code{--new} option can be used to make the
+server behave as 4.1.
+@item
+Binary items (@code{0xFFDF}) are now assumed to be strings instead of
+numbers.  This fixes some problems with character sets where it's
+convenient to input the string as a binary item.
+
+After this change you have to convert the binary string to
+@code{INTEGER} with a @code{CAST} if you want to compare two binary
+items with eachother and know which one is bigger than the other.
+@code{SELECT CAST(0xfeff AS UNSIGNED INTEGER) < CAST(0xff AS UNSIGNED
+INTEGER)}.  Using binary items in a number context or comparing them with
+@code{=} should work as before.
+
+From version 4.0.13, the @code{--new} option can be used to make the server 
behave as 4.1 in this aspect.
+@end itemize
+
+@node What-to-do-from-4.0, What-changed-in-4.1, Prepare-upgrade-4.0-4.1, 
Upgrading-from-4.0
+@subsubsection What to do when upgrading from 4.0 to 4.1
+
 @cindex compatibility, between MySQL versions
 @cindex upgrading, 4.0 to 4.1
 
@@ -9049,15 +9116,24 @@
 @item
 Run the script @code{mysql_fix_privilege_tables} to generate the new
 password field that is needed for secure handling of passwords.
+@item
+Check the changes section if there is some change that may affect your
+application. @xref{What-changed-in-4.1}.
+@item
+Read the new feature list for cool things that you can use in 4.1.
+@xref{News-4.1.x}.
 @end itemize
 
-The following is a more complete lists tell what you have to watch out
-for when upgrading to version 4.1;
+@node What-changed-in-4.1,  , What-to-do-from-4.0, Upgrading-from-4.0
+@subsubsection What has changed between 4.0 and 4.1
+
+The following is a lists of things that you have to watch out for when
+upgrading to version 4.1;
 
 @itemize
 @item
-Functions that return a DATE, DATETIME or TIME result is now fixed
-up when returned to the client.
+Functions that return a @code{DATE}, @code{DATETIME} or @code{TIME}
+result is now fixed up when returned to the client.
 
 @example
 mysql> SELECT cast("2001-1-1" as DATE)
@@ -9074,16 +9150,11 @@
 @code{TIMESTAMP} is now returned as string with the format
 @code{'YYYY-MM-DD HH:MM:DD'}. If you want to have this as a number you
 should add +0 to the timestamp column. Different timestamp lengths are
-not supported.
-
-@strong{Note}: From version 4.0.12, the @code{--new} option can be used
-to make the server behave as 4.1.
-This can be used to prepare for a 4.0 to 4.1 upgrade.
+not supported. @xref{Prepare-upgrade-4.0-4.1}.
 
-This change was necessary for SQL standards compliance. In a future
-version, a further change will be made (backward compatible with this
-change), allowing the timestamp length to indicate the desired number
-of digits of fractions of a second.
+@item
+Binary items (@code{0xFFDF}) are now assumed to be strings instead of
+numbers. @xref{Prepare-upgrade-4.0-4.1}.
 
 @item
 If you are running multiple servers on the same windows machine, you should
@@ -9098,31 +9169,7 @@
 If you are running MySQL Server on Windows, please also see
 @ref{Windows upgrading}.
 
-
-@node Prepare-upgrade-4.0-4.1, Upgrading-from-3.23, Upgrading-from-4.0, Upgrade
-@subsection Preparing to Upgrade From Version 4.0 to 4.1
-
-@cindex upgrading, 4.0 to 4.1
-
-@itemize
-@item
-In Version 4.1, @code{TIMESTAMP} is returned as string with the format
-@code{'YYYY-MM-DD HH:MM:DD'}. If you want to have this as a number
-(like you Version 4.0 does) should add +0 to the timestamp column.
-Different timestamp lengths are not supported.
-
-@strong{Note}: From version 4.0.12, the @code{--new} option can be used
-to make the server behave as 4.1.
-This can be used to prepare for a 4.0 to 4.1 upgrade.
-@end itemize
-
-This change was necessary for SQL standards compliance. In a future
-version, a further change will be made (backward compatible with this
-change), allowing the timestamp length to indicate the desired number
-of digits of fractions of a second.
-
-
-@node Upgrading-from-3.23, Upgrading-from-3.22, Prepare-upgrade-4.0-4.1, 
Upgrade
+@node Upgrading-from-3.23, Upgrading-from-3.22, Upgrading-from-4.0, Upgrade
 @subsection Upgrading From Version 3.23 to 4.0
 
 @cindex compatibility, between MySQL versions
@@ -21607,7 +21654,8 @@
 Don't start more than this number of threads to handle @code{INSERT DELAYED}
 statements.  If you try to insert data into a new table after all @code{INSERT
 DELAYED} threads are in use, the row will be inserted as if the
-@code{DELAYED} attribute wasn't specified.
+@code{DELAYED} attribute wasn't specified. If you set this to 0, MySQL
+will never create a max_delayed thread.
 
 @item @code{max_heap_table_size}
 Don't allow creation of heap tables bigger than this.
@@ -25802,28 +25850,22 @@
 Replication will be done correctly with @code{AUTO_INCREMENT},
 @code{LAST_INSERT_ID()}, and @code{TIMESTAMP} values.
 @item
-In 3.23 @code{RAND()} in updates does not replicate properly.
-Use @code{RAND(some_non_rand_expr)} if you are replicating updates with
-@code{RAND()}. You can, for example, use @code{UNIX_TIMESTAMP()} for the
-argument to @code{RAND()}. This is fixed in 4.0.
-@item
-Update queries that use @code{USER()} and @code{CONNECTION_ID()} are
-badly replicated.
+@code{USER()} and @code{CONNECTION_ID()} and @code{LOAD_FILE()} functions
+are replicated without changes and will thus not work reliable on the slave.
 @item
 @code{SQL_MODE}, @code{FOREIGN_KEY_CHECKS} and @code{TABLE_TYPE}
 variables are not replicated.
 @item
-@code{LOAD_FILE} is badly replicated.
-@item
 You have to use the same character set (@code{--default-character-set})
 on the master and the slave. If not, you may get duplicate key errors on
 the slave, because a key that is regarded as unique in the master character
 set may not be unique in the slave character set.
 @item
-In 3.23, @code{LOAD DATA INFILE} will be handled properly as long as the file
-still resides on the master server at the time of update
-propagation. @code{LOAD LOCAL DATA INFILE} will be skipped. All this
-is fixed in 4.0.
+If you are using transaction tables on the master and not transactional
+tables (for the same tables) on the slave, you will get problems if
+the slave is stopped in the middle of a @code{BEGIN/COMMIT} block, as
+the slave will later start at the beginning of the @code{BEGIN} block.
+This issue is on our TODO and will be fixed in the near future.
 @item
 Update queries that use user variables are badly replicated in
 3.23 and 4.0. This is fixed in 4.1.
@@ -25841,16 +25883,6 @@
 @code{MERGE} table, you will have to issue @code{FLUSH TABLES}
 manually on the slave.
 @item
-Temporary tables are well replicated in 4.0.
-In 3.23 too but with the
-exception of the case when you shut down slave server (not just slave thread),
-you have some temporary tables open, and they are used in subsequent updates.
-To deal with this problem shutting down the slave, do @code{SLAVE STOP},
-check @code{Slave_open_temp_tables} variable to see if it is 0, then issue
-@code{mysqladmin shutdown}. If the number is not 0, restart the slave thread
-with @code{SLAVE START} and see
-if you have better luck next time. 
-@item
 MySQL only supports one master and many slaves. Later we will
 add a voting algorithm to automatically change master if something goes
 wrong with the current master. We will also introduce 'agent' processes
@@ -25907,6 +25939,44 @@
 the query execution results in the same error code. If this error code
 validation behaviour is not desirable, some ( or all) errors could be masked
 out with @code{slave-skip-errors} option starting in Version 3.23.47.
+@item
+If you update transactional tables from non transaction tables inside a
+@code{BEGIN/COMMIT} segment updates to the binary log may be out of sync
+if some thread changes the non transactional table before the
+transaction commits.  This is because the transaction is written to the
+binary log only when it's commited.
+@item
+Any updates to a non transactional table is written to the binary log at
+once when the update is made while transactional updates are written on
+@code{COMMIT} or not written at all if you use @code{ROLLBACK}.  You
+have to take this into account when updating both transactional tables
+and non transactional tables in the same transaction and you are using
+binary logging for backups or replication.
+@end itemize
+
+The following table is about problems in 3.23 that are fixed in 4.0:
+
+@itemize @bullet
+@item
+@code{LOAD DATA INFILE} will be handled properly as long as the file
+still resides on the master server at the time of update
+propagation.
+@item
+@code{LOAD LOCAL DATA INFILE} will be skipped.
+@item
+In 3.23 @code{RAND()} in updates does not replicate properly.
+Use @code{RAND(some_non_rand_expr)} if you are replicating updates with
+@code{RAND()}. You can, for example, use @code{UNIX_TIMESTAMP()} for the
+argument to @code{RAND()}. This is fixed in 4.0.
+@item
+In 3.23 temporary tables are replicated with the exception of the case
+when you shut down slave server (not just slave thread) when you have some
+temporary tables open and they are used in subsequent updates.  To deal
+with this problem shutting down the slave, do @code{SLAVE STOP}, check
+@code{Slave_open_temp_tables} variable to see if it is 0, then issue
+@code{mysqladmin shutdown}. If the number is not 0, restart the slave
+thread with @code{SLAVE START} and see if you have better luck next
+time.
 @end itemize
 
 
@@ -30616,11 +30686,16 @@
          -> Paul
 @end example
 
+In MySQL 4.1 (and in MySQL 4.0 when using the @code{--new} option) the
+default type of of a hexadecimal value is a string.  If you want to be
+sure that the string is threated as a number, you can use
+@code{CAST( ... AS UNSIGNED)} on the hexadecimal value.
+
 The @code{x'hexstring'} syntax (new in 4.0) is based on standard SQL and the
 @code{0x} syntax is based on ODBC.  Hexadecimal strings are often used by
 ODBC to supply values for @code{BLOB} columns.
-You can convert a string or a number to hexadecimal with the @code{HEX()}
-function.
+You can convert a string or a number to string in hexadecimal format with
+the @code{HEX()} function.
 
 @node NULL values,  , Hexadecimal values, Literals
 @subsubsection @code{NULL} Values
@@ -34014,6 +34089,9 @@
 once by the parser and another time when the pattern match is done, leaving
 a single backslash to be matched).
 
+@strong{Note:} Currently @code{LIKE} is not @code{multi-byte character} safe.
+Comparison is done character by character.
+
 @findex NOT LIKE
 @item expr NOT LIKE pat [ESCAPE 'escape-char']
 Same as @code{NOT (expr LIKE pat [ESCAPE 'escape-char'])}.
@@ -43146,6 +43224,9 @@
 @item
 The maximum tablespace size is 4 billion database pages. This is also
 the maximum size for a table. The minimum tablespace size is 10 MB.
+@item
+When you restart the MySQL server, InnoDB may reuse an old value for
+an @code{AUTO_INCREMENT} column.
 @end itemize
 
 @node InnoDB change history, InnoDB contact information, InnoDB restrictions, 
InnoDB
@@ -57701,7 +57782,37 @@
 Functionality added or changed:
 @itemize
 @item
-Fixed that @code{SET SQL_BIG_SELECTS=1} works again.
+Fixed problem when comparing a key for a multi-byte-character set. (Bug #152)
+@item
+Option @code{--new} now changes binary items (@code{0xFFDF}) to be
+treated by default as binary strings instead of numbers. This fixes some
+problems with character sets where it's convenient to input the string
+as a binary item.  After this change you have to convert the binary
+string to @code{INTEGER} with a @code{CAST} if you want to compare two
+binary items with eachother and know which one is bigger than the other.
+@code{SELECT CAST(0xfeff AS UNSIGNED) < CAST(0xff AS UNSIGNED)}.
+This will be the default behaviour in MySQL 4.1. (Bug #152)
+@item
+Fixed bug with @code{NATURAL LEFT JOIN}, @code{NATURAL RIGHT JOIN} and
+@code{RIGHT JOIN} when using many joined tables.  The problem was that
+the @code{JOIN} method was not always associated with the tables
+surrounding the @code{JOIN} method.  If you have a query that uses many
+@code{RIGHT JOIN} or @code{NATURAL ... JOINS} you should check that they
+work as you expected after upgrading MySQL to this version. (Bug #291)
+@item
+Fix problem with @code{ORDER BY} being discarded for some
+@code{DISTINCT} queries. (Bug #275)
+@item
+Enabled @code{delayed_insert_timeout} on Linux (most modern glibc
+libraries has a fixed pthread_cond_timedwait). (Bug #211)
+@item
+Don't create more insert delayed threads than give by
+@code{max_insert_delayed_threads}. (Bug #211)
+@item
+Fixed bug in @code{LEFT}, @code{RIGHT} and @code{MID} when used with
+multi-byte character sets and some @code{GROUP BY} queries. (Bug #314)
+@item
+Fixed that @code{SET SQL_BIG_SELECTS=1} works as documented (New bug in 4.0)
 @item
 Changed @code{UPDATE ... LIMIT} to also count accepted, but not changed rows.
 @item
@@ -59590,7 +59701,7 @@
 @item
 Small fix in @code{safe_mysqld} for some shells.
 @item
-Fixed that @code{FLUSH STATUS} doesn't reset @code{Delayed_insert_threads}.
+Fixed that @code{FLUSH STATUS} doesn't reset @code{delayed_insert_threads}.
 @item
 Fixed core dump bug when using the @code{BINARY} cast on a @code{NULL} value.
 @item

-- 
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe:    
http://lists.mysql.com/internals?unsub=gcdmd-internals@xxxxxxxxxxx