Author: paul
Date: 2010-10-01 17:48:40 +0200 (Fri, 01 Oct 2010)
New Revision: 22946
Log:
r63713@frost: paul | 2010-10-01 10:33:00 -0500
5.1: Whack dynamic open-bugs list
All but 4.1: Merge open bugs and known issues material into parent "bugs"
section
Modified:
trunk/refman-4.1/errors-problems.xml
trunk/refman-5.0/errors-problems.xml
trunk/refman-5.0/renamed-nodes.txt
trunk/refman-5.1/errors-problems-core.xml
trunk/refman-5.1/renamed-nodes.txt
trunk/refman-5.5/errors-problems-core.xml
trunk/refman-5.5/renamed-nodes.txt
trunk/refman-5.6/errors-problems-core.xml
trunk/refman-6.0/errors-problems.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- 07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/mysqldoc/trunk:35498
07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/trunk:42799
4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:43968
4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/trunk:44480
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:63692
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:39036
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/trunk:39546
+ 07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/mysqldoc/trunk:35498
07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/trunk:42799
4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:43968
4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/trunk:44480
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:63713
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:39036
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/trunk:39546
Modified: trunk/refman-4.1/errors-problems.xml
===================================================================
--- trunk/refman-4.1/errors-problems.xml 2010-10-01 13:10:36 UTC (rev 22945)
+++ trunk/refman-4.1/errors-problems.xml 2010-10-01 15:48:40 UTC (rev 22946)
Changed blocks: 2, Lines Added: 2, Lines Deleted: 4; 758 bytes
@@ -5578,8 +5578,7 @@
</indexterm>
<para>
- This section is a list of the known issues in recent versions of
- MySQL.
+ This section lists known issues in recent versions of MySQL.
</para>
<para>
@@ -5756,8 +5755,7 @@
<title>Open Issues in MySQL</title>
<para>
- The following problems are known and fixing them is a high
- priority:
+ The following problems are known:
</para>
<itemizedlist>
Modified: trunk/refman-5.0/errors-problems.xml
===================================================================
--- trunk/refman-5.0/errors-problems.xml 2010-10-01 13:10:36 UTC (rev 22945)
+++ trunk/refman-5.0/errors-problems.xml 2010-10-01 15:48:40 UTC (rev 22946)
Changed blocks: 3, Lines Added: 427, Lines Deleted: 444; 39108 bytes
@@ -5525,8 +5525,7 @@
</indexterm>
<para>
- This section is a list of the known issues in recent versions of
- MySQL.
+ This section lists known issues in recent versions of MySQL.
</para>
<para>
@@ -5537,477 +5536,466 @@
Internals: Porting</ulink>.
</para>
- <section id="open-bugs">
+ <para>
+ The following problems are known:
+ </para>
- <title>Open Issues in MySQL</title>
+ <itemizedlist>
- <para>
- The following problems are known and fixing them is a high
- priority:
- </para>
+ <listitem>
+ <para>
+ Subquery optimization for <literal>IN</literal> is not as
+ effective as for <literal>=</literal>.
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ Even if you use <literal>lower_case_table_names=2</literal>
+ (which enables MySQL to remember the case used for databases
+ and table names), MySQL does not remember the case used for
+ database names for the function
+ <literal role="func">DATABASE()</literal> or within the
+ various logs (on case-insensitive systems).
+ </para>
+ </listitem>
- <listitem>
- <para>
- Subquery optimization for <literal>IN</literal> is not as
- effective as for <literal>=</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Dropping a <literal>FOREIGN KEY</literal> constraint doesn't
+ work in replication because the constraint may have another
+ name on the slave.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Even if you use
- <literal>lower_case_table_names=2</literal> (which enables
- MySQL to remember the case used for databases and table
- names), MySQL does not remember the case used for database
- names for the function
- <literal role="func">DATABASE()</literal> or within the
- various logs (on case-insensitive systems).
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt">REPLACE</literal> (and
+ <literal role="stmt">LOAD DATA</literal> with the
+ <literal role="stmt">REPLACE</literal> option) does not
+ trigger <literal>ON DELETE CASCADE</literal>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Dropping a <literal>FOREIGN KEY</literal> constraint
- doesn't work in replication because the constraint may
- have another name on the slave.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal>DISTINCT</literal> with <literal>ORDER BY</literal>
+ doesn't work inside
+ <literal role="func">GROUP_CONCAT()</literal> if you don't
+ use all and only those columns that are in the
+ <literal>DISTINCT</literal> list.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="stmt">REPLACE</literal> (and
- <literal role="stmt">LOAD DATA</literal> with the
- <literal role="stmt">REPLACE</literal> option) does not
- trigger <literal>ON DELETE CASCADE</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If one user has a long-running transaction and another user
+ drops a table that is updated in the transaction, there is
+ small chance that the binary log may contain the
+ <literal role="stmt">DROP TABLE</literal> statement before
+ the table is used in the transaction itself. We plan to fix
+ this by having the <literal role="stmt">DROP TABLE</literal>
+ statement wait until the table is not being used in any
+ transaction.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>DISTINCT</literal> with <literal>ORDER
- BY</literal> doesn't work inside
- <literal role="func">GROUP_CONCAT()</literal> if you don't
- use all and only those columns that are in the
- <literal>DISTINCT</literal> list.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ When inserting a big integer value (between
+ 2<superscript>63</superscript> and
+ 2<superscript>64</superscript>−1) into a decimal or
+ string column, it is inserted as a negative value because
+ the number is evaluated in a signed integer context.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If one user has a long-running transaction and another
- user drops a table that is updated in the transaction,
- there is small chance that the binary log may contain the
- <literal role="stmt">DROP TABLE</literal> statement before
- the table is used in the transaction itself. We plan to
- fix this by having the <literal role="stmt">DROP
- TABLE</literal> statement wait until the table is not
- being used in any transaction.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt" condition="flush">FLUSH TABLES WITH
+ READ LOCK</literal> does not block
+ <literal role="stmt">COMMIT</literal> if the server is
+ running without binary logging, which may cause a problem
+ (of consistency between tables) when doing a full backup.
+ </para>
+ </listitem>
- <listitem>
- <para>
- When inserting a big integer value (between
- 2<superscript>63</superscript> and
- 2<superscript>64</superscript>−1) into a decimal or
- string column, it is inserted as a negative value because
- the number is evaluated in a signed integer context.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt">ANALYZE TABLE</literal> on a
+ <literal>BDB</literal> table may in some cases make the
+ table unusable until you restart <command>mysqld</command>.
+ If this happens, look for errors of the following form in
+ the MySQL error file:
+ </para>
- <listitem>
- <para>
- <literal role="stmt" condition="flush">FLUSH TABLES WITH
- READ LOCK</literal> does not block
- <literal role="stmt">COMMIT</literal> if the server is
- running without binary logging, which may cause a problem
- (of consistency between tables) when doing a full backup.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal role="stmt">ANALYZE TABLE</literal> on a
- <literal>BDB</literal> table may in some cases make the
- table unusable until you restart
- <command>mysqld</command>. If this happens, look for
- errors of the following form in the MySQL error file:
- </para>
-
<programlisting>
001207 22:07:56 bdb: log_flush: LSN past current end-of-log
</programlisting>
- </listitem>
+ </listitem>
- <listitem>
- <para>
- Don't execute <literal role="stmt">ALTER TABLE</literal>
- on a <literal>BDB</literal> table on which you are running
- multiple-statement transactions until all those
- transactions complete. (The transaction might be ignored.)
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Don't execute <literal role="stmt">ALTER TABLE</literal> on
+ a <literal>BDB</literal> table on which you are running
+ multiple-statement transactions until all those transactions
+ complete. (The transaction might be ignored.)
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="stmt">ANALYZE TABLE</literal>,
- <literal role="stmt">OPTIMIZE TABLE</literal>, and
- <literal role="stmt">REPAIR TABLE</literal> may cause
- problems on tables for which you are using
- <literal role="stmt">INSERT DELAYED</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt">ANALYZE TABLE</literal>,
+ <literal role="stmt">OPTIMIZE TABLE</literal>, and
+ <literal role="stmt">REPAIR TABLE</literal> may cause
+ problems on tables for which you are using
+ <literal role="stmt">INSERT DELAYED</literal>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Performing <literal>LOCK TABLE ...</literal> and
- <literal>FLUSH TABLES ...</literal> doesn't guarantee that
- there isn't a half-finished transaction in progress on the
- table.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Performing <literal>LOCK TABLE ...</literal> and
+ <literal>FLUSH TABLES ...</literal> doesn't guarantee that
+ there isn't a half-finished transaction in progress on the
+ table.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>BDB</literal> tables are relatively slow to open.
- If you have many <literal>BDB</literal> tables in a
- database, it takes a long time to use the
- <command>mysql</command> client on the database if you are
- not using the <literal>-A</literal> option or if you are
- using <literal>rehash</literal>. This is especially
- noticeable when you have a large table cache.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal>BDB</literal> tables are relatively slow to open.
+ If you have many <literal>BDB</literal> tables in a
+ database, it takes a long time to use the
+ <command>mysql</command> client on the database if you are
+ not using the <literal>-A</literal> option or if you are
+ using <literal>rehash</literal>. This is especially
+ noticeable when you have a large table cache.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Replication uses query-level logging: The master writes
- the executed queries to the binary log. This is a very
- fast, compact, and efficient logging method that works
- perfectly in most cases.
- </para>
+ <listitem>
+ <para>
+ Replication uses query-level logging: The master writes the
+ executed queries to the binary log. This is a very fast,
+ compact, and efficient logging method that works perfectly
+ in most cases.
+ </para>
- <para>
- It is possible for the data on the master and slave to
- become different if a query is designed in such a way that
- the data modification is nondeterministic (generally not a
- recommended practice, even outside of replication).
- </para>
+ <para>
+ It is possible for the data on the master and slave to
+ become different if a query is designed in such a way that
+ the data modification is nondeterministic (generally not a
+ recommended practice, even outside of replication).
+ </para>
- <para>
- For example:
- </para>
+ <para>
+ For example:
+ </para>
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- <literal role="stmt" condition="create-table-select">CREATE
- TABLE ... SELECT</literal> or
- <literal role="stmt" condition="insert-select">INSERT
- ... SELECT</literal> statements that insert zero or
- <literal>NULL</literal> values into an
- <literal>AUTO_INCREMENT</literal> column.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE ... SELECT</literal> or
+ <literal role="stmt" condition="insert-select">INSERT
+ ... SELECT</literal> statements that insert zero or
+ <literal>NULL</literal> values into an
+ <literal>AUTO_INCREMENT</literal> column.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="stmt">DELETE</literal> if you are
- deleting rows from a table that has foreign keys with
- <literal>ON DELETE CASCADE</literal> properties.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt">DELETE</literal> if you are
+ deleting rows from a table that has foreign keys with
+ <literal>ON DELETE CASCADE</literal> properties.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="stmt" condition="replace">REPLACE ...
- SELECT</literal>, <literal>INSERT IGNORE ...
- SELECT</literal> if you have duplicate key values in
- the inserted data.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt" condition="replace">REPLACE ...
+ SELECT</literal>, <literal>INSERT IGNORE ...
+ SELECT</literal> if you have duplicate key values in the
+ inserted data.
+ </para>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- <para>
- <emphasis role="bold">If and only if the preceding queries
- have no <literal>ORDER BY</literal> clause guaranteeing a
- deterministic order</emphasis>.
- </para>
+ <para>
+ <emphasis role="bold">If and only if the preceding queries
+ have no <literal>ORDER BY</literal> clause guaranteeing a
+ deterministic order</emphasis>.
+ </para>
- <para>
- For example, for
- <literal role="stmt" condition="insert-select">INSERT ...
- SELECT</literal> with no <literal>ORDER BY</literal>, the
- <literal role="stmt">SELECT</literal> may return rows in a
- different order (which results in a row having different
- ranks, hence getting a different number in the
- <literal>AUTO_INCREMENT</literal> column), depending on
- the choices made by the optimizers on the master and
- slave.
- </para>
+ <para>
+ For example, for
+ <literal role="stmt" condition="insert-select">INSERT ...
+ SELECT</literal> with no <literal>ORDER BY</literal>, the
+ <literal role="stmt">SELECT</literal> may return rows in a
+ different order (which results in a row having different
+ ranks, hence getting a different number in the
+ <literal>AUTO_INCREMENT</literal> column), depending on the
+ choices made by the optimizers on the master and slave.
+ </para>
- <para>
- A query is optimized differently on the master and slave
- only if:
- </para>
+ <para>
+ A query is optimized differently on the master and slave
+ only if:
+ </para>
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- The table is stored using a different storage engine
- on the master than on the slave. (It is possible to
- use different storage engines on the master and slave.
- For example, you can use <literal>InnoDB</literal> on
- the master, but <literal>MyISAM</literal> on the slave
- if the slave has less available disk space.)
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The table is stored using a different storage engine on
+ the master than on the slave. (It is possible to use
+ different storage engines on the master and slave. For
+ example, you can use <literal>InnoDB</literal> on the
+ master, but <literal>MyISAM</literal> on the slave if
+ the slave has less available disk space.)
+ </para>
+ </listitem>
- <listitem>
- <para>
- MySQL buffer sizes
- (<literal role="sysvar">key_buffer_size</literal>, and
- so on) are different on the master and slave.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ MySQL buffer sizes
+ (<literal role="sysvar">key_buffer_size</literal>, and
+ so on) are different on the master and slave.
+ </para>
+ </listitem>
- <listitem>
- <para>
- The master and slave run different MySQL versions, and
- the optimizer code differs between these versions.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The master and slave run different MySQL versions, and
+ the optimizer code differs between these versions.
+ </para>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- <para>
- This problem may also affect database restoration using
- <command>mysqlbinlog|mysql</command>.
- </para>
+ <para>
+ This problem may also affect database restoration using
+ <command>mysqlbinlog|mysql</command>.
+ </para>
- <para>
- The easiest way to avoid this problem is to add an
- <literal>ORDER BY</literal> clause to the aforementioned
- nondeterministic queries to ensure that the rows are
- always stored or modified in the same order.
- </para>
+ <para>
+ The easiest way to avoid this problem is to add an
+ <literal>ORDER BY</literal> clause to the aforementioned
+ nondeterministic queries to ensure that the rows are always
+ stored or modified in the same order.
+ </para>
- <para>
- In future MySQL versions, we will automatically add an
- <literal>ORDER BY</literal> clause when needed.
- </para>
- </listitem>
+ <para>
+ In future MySQL versions, we will automatically add an
+ <literal>ORDER BY</literal> clause when needed.
+ </para>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- <para>
- The following issues are known and will be fixed in due time:
- </para>
+ <para>
+ The following issues are known and will be fixed in due time:
+ </para>
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- Log file names are based on the server host name (if you
- don't specify a file name with the startup option). You
- have to use options such as
- <option role="mysqld">--log-bin=<replaceable>old_host_name</replaceable>-bin</option>
- if you change your host name to something else. Another
- option is to rename the old files to reflect your host
- name change (if these are binary logs, you need to edit
- the binary log index file and fix the binary log file
- names there as well). See
- <xref linkend="server-options"/>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Log file names are based on the server host name (if you
+ don't specify a file name with the startup option). You have
+ to use options such as
+ <option role="mysqld">--log-bin=<replaceable>old_host_name</replaceable>-bin</option>
+ if you change your host name to something else. Another
+ option is to rename the old files to reflect your host name
+ change (if these are binary logs, you need to edit the
+ binary log index file and fix the binary log file names
+ there as well). See <xref linkend="server-options"/>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <command>mysqlbinlog</command> does not delete temporary
- files left after a
- <literal role="stmt" condition="load-data">LOAD DATA
- INFILE</literal> statement. See
- <xref linkend="mysqlbinlog"/>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <command>mysqlbinlog</command> does not delete temporary
+ files left after a
+ <literal role="stmt" condition="load-data">LOAD DATA
+ INFILE</literal> statement. See
+ <xref linkend="mysqlbinlog"/>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>RENAME</literal> doesn't work with
- <literal>TEMPORARY</literal> tables or tables used in a
- <literal>MERGE</literal> table.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal>RENAME</literal> doesn't work with
+ <literal>TEMPORARY</literal> tables or tables used in a
+ <literal>MERGE</literal> table.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Due to the way table format (<filename>.frm</filename>)
- files are stored, you cannot use character 255
- (<literal>CHAR(255)</literal>) in table names, column
- names, or enumerations. This is scheduled to be fixed in
- version 5.1 when we implement new table definition format
- files.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Due to the way table format (<filename>.frm</filename>)
+ files are stored, you cannot use character 255
+ (<literal>CHAR(255)</literal>) in table names, column names,
+ or enumerations. This is scheduled to be fixed in version
+ 5.1 when we implement new table definition format files.
+ </para>
+ </listitem>
- <listitem>
- <para>
- When using <literal>SET CHARACTER SET</literal>, you can't
- use translated characters in database, table, and column
- names.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ When using <literal>SET CHARACTER SET</literal>, you can't
+ use translated characters in database, table, and column
+ names.
+ </para>
+ </listitem>
- <listitem>
- <para>
- You can't use <quote><literal>_</literal></quote> or
- <quote><literal>%</literal></quote> with
- <literal>ESCAPE</literal> in
- <literal role="op" condition="like">LIKE ...
- ESCAPE</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ You can't use <quote><literal>_</literal></quote> or
+ <quote><literal>%</literal></quote> with
+ <literal>ESCAPE</literal> in
+ <literal role="op" condition="like">LIKE ...
+ ESCAPE</literal>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- You cannot build the server in another directory when
- using MIT-pthreads. Because this requires changes to
- MIT-pthreads, we are not likely to fix this. See
- <xref linkend="mit-pthreads"/>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ You cannot build the server in another directory when using
+ MIT-pthreads. Because this requires changes to MIT-pthreads,
+ we are not likely to fix this. See
+ <xref linkend="mit-pthreads"/>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="type">BLOB</literal> and
- <literal role="type">TEXT</literal> values can't reliably
- be used in <literal>GROUP BY</literal>, <literal>ORDER
- BY</literal> or <literal>DISTINCT</literal>. Only the
- first <literal role="sysvar">max_sort_length</literal>
- bytes are used when comparing
- <literal role="type">BLOB</literal> values in these cases.
- The default value of
- <literal role="sysvar">max_sort_length</literal> is 1024
- and can be changed at server startup time or at runtime.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="type">BLOB</literal> and
+ <literal role="type">TEXT</literal> values can't reliably be
+ used in <literal>GROUP BY</literal>, <literal>ORDER
+ BY</literal> or <literal>DISTINCT</literal>. Only the first
+ <literal role="sysvar">max_sort_length</literal> bytes are
+ used when comparing <literal role="type">BLOB</literal>
+ values in these cases. The default value of
+ <literal role="sysvar">max_sort_length</literal> is 1024 and
+ can be changed at server startup time or at runtime.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Numeric calculations are done with
- <literal role="type">BIGINT</literal> or
- <literal role="type">DOUBLE</literal> (both are normally
- 64 bits long). Which precision you get depends on the
- function. The general rule is that bit functions are
- performed with <literal role="type">BIGINT</literal>
- precision, <literal role="func">IF()</literal> and
- <literal role="func">ELT()</literal> with
- <literal role="type">BIGINT</literal> or
- <literal role="type">DOUBLE</literal> precision, and the
- rest with <literal role="type">DOUBLE</literal> precision.
- You should try to avoid using unsigned long long values if
- they resolve to be larger than 63 bits
- (9223372036854775807) for anything other than bit fields.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Numeric calculations are done with
+ <literal role="type">BIGINT</literal> or
+ <literal role="type">DOUBLE</literal> (both are normally 64
+ bits long). Which precision you get depends on the function.
+ The general rule is that bit functions are performed with
+ <literal role="type">BIGINT</literal> precision,
+ <literal role="func">IF()</literal> and
+ <literal role="func">ELT()</literal> with
+ <literal role="type">BIGINT</literal> or
+ <literal role="type">DOUBLE</literal> precision, and the
+ rest with <literal role="type">DOUBLE</literal> precision.
+ You should try to avoid using unsigned long long values if
+ they resolve to be larger than 63 bits (9223372036854775807)
+ for anything other than bit fields.
+ </para>
+ </listitem>
- <listitem>
- <para>
- You can have up to 255 <literal role="type">ENUM</literal>
- and <literal role="type">SET</literal> columns in one
- table.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ You can have up to 255 <literal role="type">ENUM</literal>
+ and <literal role="type">SET</literal> columns in one table.
+ </para>
+ </listitem>
- <listitem>
- <para>
- In <literal role="func">MIN()</literal>,
- <literal role="func">MAX()</literal>, and other aggregate
- functions, MySQL currently compares
- <literal role="type">ENUM</literal> and
- <literal role="type">SET</literal> columns by their string
- value rather than by the string's relative position in the
- set.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ In <literal role="func">MIN()</literal>,
+ <literal role="func">MAX()</literal>, and other aggregate
+ functions, MySQL currently compares
+ <literal role="type">ENUM</literal> and
+ <literal role="type">SET</literal> columns by their string
+ value rather than by the string's relative position in the
+ set.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <command>mysqld_safe</command> redirects all messages from
- <command>mysqld</command> to the <command>mysqld</command>
- log. One problem with this is that if you execute
- <command>mysqladmin refresh</command> to close and reopen
- the log, <literal>stdout</literal> and
- <literal>stderr</literal> are still redirected to the old
- log. If you use the general query log extensively, you
- should edit <command>mysqld_safe</command> to log to
- <filename><replaceable>host_name</replaceable>.err</filename>
- instead of
- <filename><replaceable>host_name</replaceable>.log</filename>
- so that you can easily reclaim the space for the old log
- by deleting it and executing <command>mysqladmin
- refresh</command>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <command>mysqld_safe</command> redirects all messages from
+ <command>mysqld</command> to the <command>mysqld</command>
+ log. One problem with this is that if you execute
+ <command>mysqladmin refresh</command> to close and reopen
+ the log, <literal>stdout</literal> and
+ <literal>stderr</literal> are still redirected to the old
+ log. If you use the general query log extensively, you
+ should edit <command>mysqld_safe</command> to log to
+ <filename><replaceable>host_name</replaceable>.err</filename>
+ instead of
+ <filename><replaceable>host_name</replaceable>.log</filename>
+ so that you can easily reclaim the space for the old log by
+ deleting it and executing <command>mysqladmin
+ refresh</command>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- In an <literal role="stmt">UPDATE</literal> statement,
- columns are updated from left to right. If you refer to an
- updated column, you get the updated value instead of the
- original value. For example, the following statement
- increments <literal>KEY</literal> by <literal>2</literal>,
- <emphasis role="bold">not</emphasis> <literal>1</literal>:
- </para>
+ <listitem>
+ <para>
+ In an <literal role="stmt">UPDATE</literal> statement,
+ columns are updated from left to right. If you refer to an
+ updated column, you get the updated value instead of the
+ original value. For example, the following statement
+ increments <literal>KEY</literal> by <literal>2</literal>,
+ <emphasis role="bold">not</emphasis> <literal>1</literal>:
+ </para>
<programlisting>
mysql> <userinput>UPDATE <replaceable>tbl_name</replaceable> SET KEY=KEY+1,KEY=KEY+1;</userinput>
</programlisting>
- </listitem>
+ </listitem>
- <listitem>
- <para>
- You can refer to multiple temporary tables in the same
- query, but you cannot refer to any given temporary table
- more than once. For example, the following doesn't work:
- </para>
+ <listitem>
+ <para>
+ You can refer to multiple temporary tables in the same
+ query, but you cannot refer to any given temporary table
+ more than once. For example, the following doesn't work:
+ </para>
<programlisting>
mysql> <userinput>SELECT * FROM temp_table, temp_table AS t2;</userinput>
ERROR 1137: Can't reopen table: 'temp_table'
</programlisting>
- </listitem>
+ </listitem>
- <listitem>
- <para>
- The optimizer may handle <literal>DISTINCT</literal>
- differently when you are using <quote>hidden</quote>
- columns in a join than when you are not. In a join, hidden
- columns are counted as part of the result (even if they
- are not shown), whereas in normal queries, hidden columns
- don't participate in the <literal>DISTINCT</literal>
- comparison. We will probably change this in the future to
- never compare the hidden columns when executing
- <literal>DISTINCT</literal>.
- </para>
+ <listitem>
+ <para>
+ The optimizer may handle <literal>DISTINCT</literal>
+ differently when you are using <quote>hidden</quote> columns
+ in a join than when you are not. In a join, hidden columns
+ are counted as part of the result (even if they are not
+ shown), whereas in normal queries, hidden columns don't
+ participate in the <literal>DISTINCT</literal> comparison.
+ We will probably change this in the future to never compare
+ the hidden columns when executing
+ <literal>DISTINCT</literal>.
+ </para>
- <para>
- An example of this is:
- </para>
+ <para>
+ An example of this is:
+ </para>
<programlisting>
SELECT DISTINCT mp3id FROM band_downloads
WHERE userid = 9 ORDER BY id DESC;
</programlisting>
- <para>
- and
- </para>
+ <para>
+ and
+ </para>
<programlisting>
SELECT DISTINCT band_downloads.mp3id
@@ -6017,54 +6005,49 @@
ORDER BY band_downloads.id DESC;
</programlisting>
- <para>
- In the second case, using MySQL Server 3.23.x, you may get
- two identical rows in the result set (because the values
- in the hidden <literal>id</literal> column may differ).
- </para>
+ <para>
+ In the second case, using MySQL Server 3.23.x, you may get
+ two identical rows in the result set (because the values in
+ the hidden <literal>id</literal> column may differ).
+ </para>
- <para>
- Note that this happens only for queries where that do not
- have the <literal>ORDER BY</literal> columns in the
- result.
- </para>
- </listitem>
+ <para>
+ Note that this happens only for queries where that do not
+ have the <literal>ORDER BY</literal> columns in the result.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If you execute a <literal>PROCEDURE</literal> on a query
- that returns an empty set, in some cases the
- <literal>PROCEDURE</literal> does not transform the
- columns.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If you execute a <literal>PROCEDURE</literal> on a query
+ that returns an empty set, in some cases the
+ <literal>PROCEDURE</literal> does not transform the columns.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Creation of a table of type <literal>MERGE</literal>
- doesn't check whether the underlying tables are compatible
- types.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Creation of a table of type <literal>MERGE</literal> doesn't
+ check whether the underlying tables are compatible types.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If you use <literal role="stmt">ALTER TABLE</literal> to
- add a <literal>UNIQUE</literal> index to a table used in a
- <literal>MERGE</literal> table and then add a normal index
- on the <literal>MERGE</literal> table, the key order is
- different for the tables if there was an old,
- non-<literal>UNIQUE</literal> key in the table. This is
- because <literal role="stmt">ALTER TABLE</literal> puts
- <literal>UNIQUE</literal> indexes before normal indexes to
- be able to detect duplicate keys as early as possible.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If you use <literal role="stmt">ALTER TABLE</literal> to add
+ a <literal>UNIQUE</literal> index to a table used in a
+ <literal>MERGE</literal> table and then add a normal index
+ on the <literal>MERGE</literal> table, the key order is
+ different for the tables if there was an old,
+ non-<literal>UNIQUE</literal> key in the table. This is
+ because <literal role="stmt">ALTER TABLE</literal> puts
+ <literal>UNIQUE</literal> indexes before normal indexes to
+ be able to detect duplicate keys as early as possible.
+ </para>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- </section>
-
</section>
</section>
Modified: trunk/refman-5.0/renamed-nodes.txt
===================================================================
--- trunk/refman-5.0/renamed-nodes.txt 2010-10-01 13:10:36 UTC (rev 22945)
+++ trunk/refman-5.0/renamed-nodes.txt 2010-10-01 15:48:40 UTC (rev 22946)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 0; 613 bytes
@@ -119,6 +119,7 @@
mysql-proxy-cmdline mysql-proxy-configuration 2011-02-01
mysql-proxy-install-svn mysql-proxy-install-cvs 2010-07-01
nested-joins nested-join-optimization 2010-01-21
+open-bugs bugs 2011-10-01
other-functions miscellaneous-functions 2011-06-18
privilege-system-overview privilege-system 2010-02-06
privileges privilege-system 2010-01-13
Modified: trunk/refman-5.1/errors-problems-core.xml
===================================================================
--- trunk/refman-5.1/errors-problems-core.xml 2010-10-01 13:10:36 UTC (rev 22945)
+++ trunk/refman-5.1/errors-problems-core.xml 2010-10-01 15:48:40 UTC (rev 22946)
Changed blocks: 3, Lines Added: 391, Lines Deleted: 415; 35974 bytes
@@ -5425,8 +5425,7 @@
</indexterm>
<para>
- This section is a list of the known issues in recent versions of
- MySQL.
+ This section lists known issues in recent versions of MySQL.
</para>
<para>
@@ -5437,440 +5436,422 @@
Internals: Porting</ulink>.
</para>
- <section id="open-bugs">
+ <para>
+ The following problems are known:
+ </para>
- <title>Open Issues in MySQL</title>
+ <itemizedlist>
- <para condition="dynamic:openbugs:openbugslist" role="5.1:mysqld:5.1.38"/>
+ <listitem>
+ <para>
+ Subquery optimization for <literal>IN</literal> is not as
+ effective as for <literal>=</literal>.
+ </para>
+ </listitem>
- </section>
+ <listitem>
+ <para>
+ Even if you use <literal>lower_case_table_names=2</literal>
+ (which enables MySQL to remember the case used for databases
+ and table names), MySQL does not remember the case used for
+ database names for the function
+ <literal role="func">DATABASE()</literal> or within the
+ various logs (on case-insensitive systems).
+ </para>
+ </listitem>
- <section id="open-bugs-general">
+ <listitem>
+ <para>
+ Dropping a <literal>FOREIGN KEY</literal> constraint doesn't
+ work in replication because the constraint may have another
+ name on the slave.
+ </para>
+ </listitem>
- <title>Additional Known Issues</title>
+ <listitem>
+ <para>
+ <literal role="stmt">REPLACE</literal> (and
+ <literal role="stmt">LOAD DATA</literal> with the
+ <literal role="stmt">REPLACE</literal> option) does not
+ trigger <literal>ON DELETE CASCADE</literal>.
+ </para>
+ </listitem>
- <para>
- The following problems are known:
- </para>
+ <listitem>
+ <para>
+ <literal>DISTINCT</literal> with <literal>ORDER BY</literal>
+ doesn't work inside
+ <literal role="func">GROUP_CONCAT()</literal> if you don't
+ use all and only those columns that are in the
+ <literal>DISTINCT</literal> list.
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ If one user has a long-running transaction and another user
+ drops a table that is updated in the transaction, there is
+ small chance that the binary log may contain the
+ <literal role="stmt">DROP TABLE</literal> statement before
+ the table is used in the transaction itself. We plan to fix
+ this by having the <literal role="stmt">DROP TABLE</literal>
+ statement wait until the table is not being used in any
+ transaction.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Subquery optimization for <literal>IN</literal> is not as
- effective as for <literal>=</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ When inserting a big integer value (between
+ 2<superscript>63</superscript> and
+ 2<superscript>64</superscript>−1) into a decimal or
+ string column, it is inserted as a negative value because
+ the number is evaluated in a signed integer context.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Even if you use
- <literal>lower_case_table_names=2</literal> (which enables
- MySQL to remember the case used for databases and table
- names), MySQL does not remember the case used for database
- names for the function
- <literal role="func">DATABASE()</literal> or within the
- various logs (on case-insensitive systems).
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt" condition="flush">FLUSH TABLES WITH
+ READ LOCK</literal> does not block
+ <literal role="stmt">COMMIT</literal> if the server is
+ running without binary logging, which may cause a problem
+ (of consistency between tables) when doing a full backup.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Dropping a <literal>FOREIGN KEY</literal> constraint
- doesn't work in replication because the constraint may
- have another name on the slave.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt">ANALYZE TABLE</literal>,
+ <literal role="stmt">OPTIMIZE TABLE</literal>, and
+ <literal role="stmt">REPAIR TABLE</literal> may cause
+ problems on tables for which you are using
+ <literal role="stmt">INSERT DELAYED</literal>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="stmt">REPLACE</literal> (and
- <literal role="stmt">LOAD DATA</literal> with the
- <literal role="stmt">REPLACE</literal> option) does not
- trigger <literal>ON DELETE CASCADE</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Performing <literal>LOCK TABLE ...</literal> and
+ <literal>FLUSH TABLES ...</literal> doesn't guarantee that
+ there isn't a half-finished transaction in progress on the
+ table.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>DISTINCT</literal> with <literal>ORDER
- BY</literal> doesn't work inside
- <literal role="func">GROUP_CONCAT()</literal> if you don't
- use all and only those columns that are in the
- <literal>DISTINCT</literal> list.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Replication uses query-level logging: The master writes the
+ executed queries to the binary log. This is a very fast,
+ compact, and efficient logging method that works perfectly
+ in most cases.
+ </para>
- <listitem>
- <para>
- If one user has a long-running transaction and another
- user drops a table that is updated in the transaction,
- there is small chance that the binary log may contain the
- <literal role="stmt">DROP TABLE</literal> statement before
- the table is used in the transaction itself. We plan to
- fix this by having the <literal role="stmt">DROP
- TABLE</literal> statement wait until the table is not
- being used in any transaction.
- </para>
- </listitem>
+ <para>
+ It is possible for the data on the master and slave to
+ become different if a query is designed in such a way that
+ the data modification is nondeterministic (generally not a
+ recommended practice, even outside of replication).
+ </para>
- <listitem>
- <para>
- When inserting a big integer value (between
- 2<superscript>63</superscript> and
- 2<superscript>64</superscript>−1) into a decimal or
- string column, it is inserted as a negative value because
- the number is evaluated in a signed integer context.
- </para>
- </listitem>
+ <para>
+ For example:
+ </para>
- <listitem>
- <para>
- <literal role="stmt" condition="flush">FLUSH TABLES WITH
- READ LOCK</literal> does not block
- <literal role="stmt">COMMIT</literal> if the server is
- running without binary logging, which may cause a problem
- (of consistency between tables) when doing a full backup.
- </para>
- </listitem>
+ <itemizedlist>
- <listitem>
- <para>
- <literal role="stmt">ANALYZE TABLE</literal>,
- <literal role="stmt">OPTIMIZE TABLE</literal>, and
- <literal role="stmt">REPAIR TABLE</literal> may cause
- problems on tables for which you are using
- <literal role="stmt">INSERT DELAYED</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE ... SELECT</literal> or
+ <literal role="stmt" condition="insert-select">INSERT
+ ... SELECT</literal> statements that insert zero or
+ <literal>NULL</literal> values into an
+ <literal>AUTO_INCREMENT</literal> column.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Performing <literal>LOCK TABLE ...</literal> and
- <literal>FLUSH TABLES ...</literal> doesn't guarantee that
- there isn't a half-finished transaction in progress on the
- table.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt">DELETE</literal> if you are
+ deleting rows from a table that has foreign keys with
+ <literal>ON DELETE CASCADE</literal> properties.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Replication uses query-level logging: The master writes
- the executed queries to the binary log. This is a very
- fast, compact, and efficient logging method that works
- perfectly in most cases.
- </para>
+ <listitem>
+ <para>
+ <literal role="stmt" condition="replace">REPLACE ...
+ SELECT</literal>, <literal>INSERT IGNORE ...
+ SELECT</literal> if you have duplicate key values in the
+ inserted data.
+ </para>
+ </listitem>
- <para>
- It is possible for the data on the master and slave to
- become different if a query is designed in such a way that
- the data modification is nondeterministic (generally not a
- recommended practice, even outside of replication).
- </para>
+ </itemizedlist>
- <para>
- For example:
- </para>
+ <para>
+ <emphasis role="bold">If and only if the preceding queries
+ have no <literal>ORDER BY</literal> clause guaranteeing a
+ deterministic order</emphasis>.
+ </para>
- <itemizedlist>
+ <para>
+ For example, for
+ <literal role="stmt" condition="insert-select">INSERT ...
+ SELECT</literal> with no <literal>ORDER BY</literal>, the
+ <literal role="stmt">SELECT</literal> may return rows in a
+ different order (which results in a row having different
+ ranks, hence getting a different number in the
+ <literal>AUTO_INCREMENT</literal> column), depending on the
+ choices made by the optimizers on the master and slave.
+ </para>
- <listitem>
- <para>
- <literal role="stmt" condition="create-table-select">CREATE
- TABLE ... SELECT</literal> or
- <literal role="stmt" condition="insert-select">INSERT
- ... SELECT</literal> statements that insert zero or
- <literal>NULL</literal> values into an
- <literal>AUTO_INCREMENT</literal> column.
- </para>
- </listitem>
+ <para>
+ A query is optimized differently on the master and slave
+ only if:
+ </para>
- <listitem>
- <para>
- <literal role="stmt">DELETE</literal> if you are
- deleting rows from a table that has foreign keys with
- <literal>ON DELETE CASCADE</literal> properties.
- </para>
- </listitem>
+ <itemizedlist>
- <listitem>
- <para>
- <literal role="stmt" condition="replace">REPLACE ...
- SELECT</literal>, <literal>INSERT IGNORE ...
- SELECT</literal> if you have duplicate key values in
- the inserted data.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The table is stored using a different storage engine on
+ the master than on the slave. (It is possible to use
+ different storage engines on the master and slave. For
+ example, you can use <literal>InnoDB</literal> on the
+ master, but <literal>MyISAM</literal> on the slave if
+ the slave has less available disk space.)
+ </para>
+ </listitem>
- </itemizedlist>
+ <listitem>
+ <para>
+ MySQL buffer sizes
+ (<literal role="sysvar">key_buffer_size</literal>, and
+ so on) are different on the master and slave.
+ </para>
+ </listitem>
- <para>
- <emphasis role="bold">If and only if the preceding queries
- have no <literal>ORDER BY</literal> clause guaranteeing a
- deterministic order</emphasis>.
- </para>
+ <listitem>
+ <para>
+ The master and slave run different MySQL versions, and
+ the optimizer code differs between these versions.
+ </para>
+ </listitem>
- <para>
- For example, for
- <literal role="stmt" condition="insert-select">INSERT ...
- SELECT</literal> with no <literal>ORDER BY</literal>, the
- <literal role="stmt">SELECT</literal> may return rows in a
- different order (which results in a row having different
- ranks, hence getting a different number in the
- <literal>AUTO_INCREMENT</literal> column), depending on
- the choices made by the optimizers on the master and
- slave.
- </para>
+ </itemizedlist>
- <para>
- A query is optimized differently on the master and slave
- only if:
- </para>
+ <para>
+ This problem may also affect database restoration using
+ <command>mysqlbinlog|mysql</command>.
+ </para>
- <itemizedlist>
+ <para>
+ The easiest way to avoid this problem is to add an
+ <literal>ORDER BY</literal> clause to the aforementioned
+ nondeterministic queries to ensure that the rows are always
+ stored or modified in the same order.
+ </para>
- <listitem>
- <para>
- The table is stored using a different storage engine
- on the master than on the slave. (It is possible to
- use different storage engines on the master and slave.
- For example, you can use <literal>InnoDB</literal> on
- the master, but <literal>MyISAM</literal> on the slave
- if the slave has less available disk space.)
- </para>
- </listitem>
+ <para>
+ In future MySQL versions, we will automatically add an
+ <literal>ORDER BY</literal> clause when needed.
+ </para>
+ </listitem>
- <listitem>
- <para>
- MySQL buffer sizes
- (<literal role="sysvar">key_buffer_size</literal>, and
- so on) are different on the master and slave.
- </para>
- </listitem>
+ </itemizedlist>
- <listitem>
- <para>
- The master and slave run different MySQL versions, and
- the optimizer code differs between these versions.
- </para>
- </listitem>
+ <para>
+ The following issues are known and will be fixed in due time:
+ </para>
- </itemizedlist>
+ <itemizedlist>
- <para>
- This problem may also affect database restoration using
- <command>mysqlbinlog|mysql</command>.
- </para>
+ <listitem>
+ <para>
+ Log file names are based on the server host name (if you
+ don't specify a file name with the startup option). You have
+ to use options such as
+ <option role="mysqld">--log-bin=<replaceable>old_host_name</replaceable>-bin</option>
+ if you change your host name to something else. Another
+ option is to rename the old files to reflect your host name
+ change (if these are binary logs, you need to edit the
+ binary log index file and fix the binary log file names
+ there as well). See <xref linkend="server-options"/>.
+ </para>
+ </listitem>
- <para>
- The easiest way to avoid this problem is to add an
- <literal>ORDER BY</literal> clause to the aforementioned
- nondeterministic queries to ensure that the rows are
- always stored or modified in the same order.
- </para>
+ <listitem>
+ <para>
+ <command>mysqlbinlog</command> does not delete temporary
+ files left after a
+ <literal role="stmt" condition="load-data">LOAD DATA
+ INFILE</literal> statement. See
+ <xref linkend="mysqlbinlog"/>.
+ </para>
+ </listitem>
- <para>
- In future MySQL versions, we will automatically add an
- <literal>ORDER BY</literal> clause when needed.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal>RENAME</literal> doesn't work with
+ <literal>TEMPORARY</literal> tables or tables used in a
+ <literal>MERGE</literal> table.
+ </para>
+ </listitem>
- </itemizedlist>
+ <listitem>
+ <para>
+ Due to the way table format (<filename>.frm</filename>)
+ files are stored, you cannot use character 255
+ (<literal>CHAR(255)</literal>) in table names, column names,
+ or enumerations. This is scheduled to be fixed in version
+ 5.1 when we implement new table definition format files.
+ </para>
+ </listitem>
- <para>
- The following issues are known and will be fixed in due time:
- </para>
+ <listitem>
+ <para>
+ When using <literal>SET CHARACTER SET</literal>, you can't
+ use translated characters in database, table, and column
+ names.
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ You can't use <quote><literal>_</literal></quote> or
+ <quote><literal>%</literal></quote> with
+ <literal>ESCAPE</literal> in
+ <literal role="op" condition="like">LIKE ...
+ ESCAPE</literal>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Log file names are based on the server host name (if you
- don't specify a file name with the startup option). You
- have to use options such as
- <option role="mysqld">--log-bin=<replaceable>old_host_name</replaceable>-bin</option>
- if you change your host name to something else. Another
- option is to rename the old files to reflect your host
- name change (if these are binary logs, you need to edit
- the binary log index file and fix the binary log file
- names there as well). See
- <xref linkend="server-options"/>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="type">BLOB</literal> and
+ <literal role="type">TEXT</literal> values can't reliably be
+ used in <literal>GROUP BY</literal>, <literal>ORDER
+ BY</literal> or <literal>DISTINCT</literal>. Only the first
+ <literal role="sysvar">max_sort_length</literal> bytes are
+ used when comparing <literal role="type">BLOB</literal>
+ values in these cases. The default value of
+ <literal role="sysvar">max_sort_length</literal> is 1024 and
+ can be changed at server startup time or at runtime.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <command>mysqlbinlog</command> does not delete temporary
- files left after a
- <literal role="stmt" condition="load-data">LOAD DATA
- INFILE</literal> statement. See
- <xref linkend="mysqlbinlog"/>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Numeric calculations are done with
+ <literal role="type">BIGINT</literal> or
+ <literal role="type">DOUBLE</literal> (both are normally 64
+ bits long). Which precision you get depends on the function.
+ The general rule is that bit functions are performed with
+ <literal role="type">BIGINT</literal> precision,
+ <literal role="func">IF()</literal> and
+ <literal role="func">ELT()</literal> with
+ <literal role="type">BIGINT</literal> or
+ <literal role="type">DOUBLE</literal> precision, and the
+ rest with <literal role="type">DOUBLE</literal> precision.
+ You should try to avoid using unsigned long long values if
+ they resolve to be larger than 63 bits (9223372036854775807)
+ for anything other than bit fields.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>RENAME</literal> doesn't work with
- <literal>TEMPORARY</literal> tables or tables used in a
- <literal>MERGE</literal> table.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ You can have up to 255 <literal role="type">ENUM</literal>
+ and <literal role="type">SET</literal> columns in one table.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Due to the way table format (<filename>.frm</filename>)
- files are stored, you cannot use character 255
- (<literal>CHAR(255)</literal>) in table names, column
- names, or enumerations. This is scheduled to be fixed in
- version 5.1 when we implement new table definition format
- files.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ In <literal role="func">MIN()</literal>,
+ <literal role="func">MAX()</literal>, and other aggregate
+ functions, MySQL currently compares
+ <literal role="type">ENUM</literal> and
+ <literal role="type">SET</literal> columns by their string
+ value rather than by the string's relative position in the
+ set.
+ </para>
+ </listitem>
- <listitem>
- <para>
- When using <literal>SET CHARACTER SET</literal>, you can't
- use translated characters in database, table, and column
- names.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <command>mysqld_safe</command> redirects all messages from
+ <command>mysqld</command> to the <command>mysqld</command>
+ log. One problem with this is that if you execute
+ <command>mysqladmin refresh</command> to close and reopen
+ the log, <literal>stdout</literal> and
+ <literal>stderr</literal> are still redirected to the old
+ log. If you use the general query log extensively, you
+ should edit <command>mysqld_safe</command> to log to
+ <filename><replaceable>host_name</replaceable>.err</filename>
+ instead of
+ <filename><replaceable>host_name</replaceable>.log</filename>
+ so that you can easily reclaim the space for the old log by
+ deleting it and executing <command>mysqladmin
+ refresh</command>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- You can't use <quote><literal>_</literal></quote> or
- <quote><literal>%</literal></quote> with
- <literal>ESCAPE</literal> in
- <literal role="op" condition="like">LIKE ...
- ESCAPE</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ In an <literal role="stmt">UPDATE</literal> statement,
+ columns are updated from left to right. If you refer to an
+ updated column, you get the updated value instead of the
+ original value. For example, the following statement
+ increments <literal>KEY</literal> by <literal>2</literal>,
+ <emphasis role="bold">not</emphasis> <literal>1</literal>:
+ </para>
- <listitem>
- <para>
- <literal role="type">BLOB</literal> and
- <literal role="type">TEXT</literal> values can't reliably
- be used in <literal>GROUP BY</literal>, <literal>ORDER
- BY</literal> or <literal>DISTINCT</literal>. Only the
- first <literal role="sysvar">max_sort_length</literal>
- bytes are used when comparing
- <literal role="type">BLOB</literal> values in these cases.
- The default value of
- <literal role="sysvar">max_sort_length</literal> is 1024
- and can be changed at server startup time or at runtime.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Numeric calculations are done with
- <literal role="type">BIGINT</literal> or
- <literal role="type">DOUBLE</literal> (both are normally
- 64 bits long). Which precision you get depends on the
- function. The general rule is that bit functions are
- performed with <literal role="type">BIGINT</literal>
- precision, <literal role="func">IF()</literal> and
- <literal role="func">ELT()</literal> with
- <literal role="type">BIGINT</literal> or
- <literal role="type">DOUBLE</literal> precision, and the
- rest with <literal role="type">DOUBLE</literal> precision.
- You should try to avoid using unsigned long long values if
- they resolve to be larger than 63 bits
- (9223372036854775807) for anything other than bit fields.
- </para>
- </listitem>
-
- <listitem>
- <para>
- You can have up to 255 <literal role="type">ENUM</literal>
- and <literal role="type">SET</literal> columns in one
- table.
- </para>
- </listitem>
-
- <listitem>
- <para>
- In <literal role="func">MIN()</literal>,
- <literal role="func">MAX()</literal>, and other aggregate
- functions, MySQL currently compares
- <literal role="type">ENUM</literal> and
- <literal role="type">SET</literal> columns by their string
- value rather than by the string's relative position in the
- set.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <command>mysqld_safe</command> redirects all messages from
- <command>mysqld</command> to the <command>mysqld</command>
- log. One problem with this is that if you execute
- <command>mysqladmin refresh</command> to close and reopen
- the log, <literal>stdout</literal> and
- <literal>stderr</literal> are still redirected to the old
- log. If you use the general query log extensively, you
- should edit <command>mysqld_safe</command> to log to
- <filename><replaceable>host_name</replaceable>.err</filename>
- instead of
- <filename><replaceable>host_name</replaceable>.log</filename>
- so that you can easily reclaim the space for the old log
- by deleting it and executing <command>mysqladmin
- refresh</command>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- In an <literal role="stmt">UPDATE</literal> statement,
- columns are updated from left to right. If you refer to an
- updated column, you get the updated value instead of the
- original value. For example, the following statement
- increments <literal>KEY</literal> by <literal>2</literal>,
- <emphasis role="bold">not</emphasis> <literal>1</literal>:
- </para>
-
<programlisting>
mysql> <userinput>UPDATE <replaceable>tbl_name</replaceable> SET KEY=KEY+1,KEY=KEY+1;</userinput>
</programlisting>
- </listitem>
+ </listitem>
- <listitem>
- <para>
- You can refer to multiple temporary tables in the same
- query, but you cannot refer to any given temporary table
- more than once. For example, the following doesn't work:
- </para>
+ <listitem>
+ <para>
+ You can refer to multiple temporary tables in the same
+ query, but you cannot refer to any given temporary table
+ more than once. For example, the following doesn't work:
+ </para>
<programlisting>
mysql> <userinput>SELECT * FROM temp_table, temp_table AS t2;</userinput>
ERROR 1137: Can't reopen table: 'temp_table'
</programlisting>
- </listitem>
+ </listitem>
- <listitem>
- <para>
- The optimizer may handle <literal>DISTINCT</literal>
- differently when you are using <quote>hidden</quote>
- columns in a join than when you are not. In a join, hidden
- columns are counted as part of the result (even if they
- are not shown), whereas in normal queries, hidden columns
- don't participate in the <literal>DISTINCT</literal>
- comparison. We will probably change this in the future to
- never compare the hidden columns when executing
- <literal>DISTINCT</literal>.
- </para>
+ <listitem>
+ <para>
+ The optimizer may handle <literal>DISTINCT</literal>
+ differently when you are using <quote>hidden</quote> columns
+ in a join than when you are not. In a join, hidden columns
+ are counted as part of the result (even if they are not
+ shown), whereas in normal queries, hidden columns don't
+ participate in the <literal>DISTINCT</literal> comparison.
+ We will probably change this in the future to never compare
+ the hidden columns when executing
+ <literal>DISTINCT</literal>.
+ </para>
- <para>
- An example of this is:
- </para>
+ <para>
+ An example of this is:
+ </para>
<programlisting>
SELECT DISTINCT mp3id FROM band_downloads
WHERE userid = 9 ORDER BY id DESC;
</programlisting>
- <para>
- and
- </para>
+ <para>
+ and
+ </para>
<programlisting>
SELECT DISTINCT band_downloads.mp3id
@@ -5880,54 +5861,49 @@
ORDER BY band_downloads.id DESC;
</programlisting>
- <para>
- In the second case, using MySQL Server 3.23.x, you may get
- two identical rows in the result set (because the values
- in the hidden <literal>id</literal> column may differ).
- </para>
+ <para>
+ In the second case, using MySQL Server 3.23.x, you may get
+ two identical rows in the result set (because the values in
+ the hidden <literal>id</literal> column may differ).
+ </para>
- <para>
- Note that this happens only for queries where that do not
- have the <literal>ORDER BY</literal> columns in the
- result.
- </para>
- </listitem>
+ <para>
+ Note that this happens only for queries where that do not
+ have the <literal>ORDER BY</literal> columns in the result.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If you execute a <literal>PROCEDURE</literal> on a query
- that returns an empty set, in some cases the
- <literal>PROCEDURE</literal> does not transform the
- columns.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If you execute a <literal>PROCEDURE</literal> on a query
+ that returns an empty set, in some cases the
+ <literal>PROCEDURE</literal> does not transform the columns.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Creation of a table of type <literal>MERGE</literal>
- doesn't check whether the underlying tables are compatible
- types.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Creation of a table of type <literal>MERGE</literal> doesn't
+ check whether the underlying tables are compatible types.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If you use <literal role="stmt">ALTER TABLE</literal> to
- add a <literal>UNIQUE</literal> index to a table used in a
- <literal>MERGE</literal> table and then add a normal index
- on the <literal>MERGE</literal> table, the key order is
- different for the tables if there was an old,
- non-<literal>UNIQUE</literal> key in the table. This is
- because <literal role="stmt">ALTER TABLE</literal> puts
- <literal>UNIQUE</literal> indexes before normal indexes to
- be able to detect duplicate keys as early as possible.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If you use <literal role="stmt">ALTER TABLE</literal> to add
+ a <literal>UNIQUE</literal> index to a table used in a
+ <literal>MERGE</literal> table and then add a normal index
+ on the <literal>MERGE</literal> table, the key order is
+ different for the tables if there was an old,
+ non-<literal>UNIQUE</literal> key in the table. This is
+ because <literal role="stmt">ALTER TABLE</literal> puts
+ <literal>UNIQUE</literal> indexes before normal indexes to
+ be able to detect duplicate keys as early as possible.
+ </para>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- </section>
-
</section>
</section>
Modified: trunk/refman-5.1/renamed-nodes.txt
===================================================================
--- trunk/refman-5.1/renamed-nodes.txt 2010-10-01 13:10:36 UTC (rev 22945)
+++ trunk/refman-5.1/renamed-nodes.txt 2010-10-01 15:48:40 UTC (rev 22946)
Changed blocks: 1, Lines Added: 2, Lines Deleted: 0; 688 bytes
@@ -153,6 +153,8 @@
mysql-proxy-install-svn mysql-proxy-install-cvs 2010-07-01
nested-joins nested-join-optimization 2010-01-21
news-5-1-28-ndb-6-3-18 mysql-cluster-news-6-3 2009-10-05
+open-bugs bugs 2011-10-01
+open-bugs-general bugs 2011-10-01
other-functions miscellaneous-functions 2011-06-18
plugin-api-general plugin-general-data-structures 2011-03-30
plugin-api-type-specific plugin-type-specific-data-structures 2011-03-30
Modified: trunk/refman-5.5/errors-problems-core.xml
===================================================================
--- trunk/refman-5.5/errors-problems-core.xml 2010-10-01 13:10:36 UTC (rev 22945)
+++ trunk/refman-5.5/errors-problems-core.xml 2010-10-01 15:48:40 UTC (rev 22946)
Changed blocks: 3, Lines Added: 398, Lines Deleted: 413; 36185 bytes
@@ -5410,8 +5410,7 @@
</indexterm>
<para>
- This section is a list of the known issues in recent versions of
- MySQL.
+ This section lists known issues in recent versions of MySQL.
</para>
<para>
@@ -5422,439 +5421,430 @@
Internals: Porting</ulink>.
</para>
- <section id="open-bugs">
+ <para>
+ The following problems are known:
+ </para>
- <title>Open Issues in MySQL</title>
+ <itemizedlist>
- <para>
- The following problems are known:
- </para>
+ <listitem>
+ <para>
+ Subquery optimization for <literal>IN</literal> is not as
+ effective as for <literal>=</literal>.
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ Even if you use <literal>lower_case_table_names=2</literal>
+ (which enables MySQL to remember the case used for databases
+ and table names), MySQL does not remember the case used for
+ database names for the function
+ <literal role="func">DATABASE()</literal> or within the
+ various logs (on case-insensitive systems).
+ </para>
+ </listitem>
- <listitem>
- <para>
- Subquery optimization for <literal>IN</literal> is not as
- effective as for <literal>=</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Dropping a <literal>FOREIGN KEY</literal> constraint doesn't
+ work in replication because the constraint may have another
+ name on the slave.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Even if you use
- <literal>lower_case_table_names=2</literal> (which enables
- MySQL to remember the case used for databases and table
- names), MySQL does not remember the case used for database
- names for the function
- <literal role="func">DATABASE()</literal> or within the
- various logs (on case-insensitive systems).
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt">REPLACE</literal> (and
+ <literal role="stmt">LOAD DATA</literal> with the
+ <literal role="stmt">REPLACE</literal> option) does not
+ trigger <literal>ON DELETE CASCADE</literal>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Dropping a <literal>FOREIGN KEY</literal> constraint
- doesn't work in replication because the constraint may
- have another name on the slave.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal>DISTINCT</literal> with <literal>ORDER BY</literal>
+ doesn't work inside
+ <literal role="func">GROUP_CONCAT()</literal> if you don't
+ use all and only those columns that are in the
+ <literal>DISTINCT</literal> list.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="stmt">REPLACE</literal> (and
- <literal role="stmt">LOAD DATA</literal> with the
- <literal role="stmt">REPLACE</literal> option) does not
- trigger <literal>ON DELETE CASCADE</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If one user has a long-running transaction and another user
+ drops a table that is updated in the transaction, there is
+ small chance that the binary log may contain the
+ <literal role="stmt">DROP TABLE</literal> statement before
+ the table is used in the transaction itself. We plan to fix
+ this by having the <literal role="stmt">DROP TABLE</literal>
+ statement wait until the table is not being used in any
+ transaction.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>DISTINCT</literal> with <literal>ORDER
- BY</literal> doesn't work inside
- <literal role="func">GROUP_CONCAT()</literal> if you don't
- use all and only those columns that are in the
- <literal>DISTINCT</literal> list.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ When inserting a big integer value (between
+ 2<superscript>63</superscript> and
+ 2<superscript>64</superscript>−1) into a decimal or
+ string column, it is inserted as a negative value because
+ the number is evaluated in a signed integer context.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If one user has a long-running transaction and another
- user drops a table that is updated in the transaction,
- there is small chance that the binary log may contain the
- <literal role="stmt">DROP TABLE</literal> statement before
- the table is used in the transaction itself. We plan to
- fix this by having the <literal role="stmt">DROP
- TABLE</literal> statement wait until the table is not
- being used in any transaction.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt" condition="flush">FLUSH TABLES WITH
+ READ LOCK</literal> does not block
+ <literal role="stmt">COMMIT</literal> if the server is
+ running without binary logging, which may cause a problem
+ (of consistency between tables) when doing a full backup.
+ </para>
+ </listitem>
- <listitem>
- <para>
- When inserting a big integer value (between
- 2<superscript>63</superscript> and
- 2<superscript>64</superscript>−1) into a decimal or
- string column, it is inserted as a negative value because
- the number is evaluated in a signed integer context.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt">ANALYZE TABLE</literal>,
+ <literal role="stmt">OPTIMIZE TABLE</literal>, and
+ <literal role="stmt">REPAIR TABLE</literal> may cause
+ problems on tables for which you are using
+ <literal role="stmt">INSERT DELAYED</literal>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="stmt" condition="flush">FLUSH TABLES WITH
- READ LOCK</literal> does not block
- <literal role="stmt">COMMIT</literal> if the server is
- running without binary logging, which may cause a problem
- (of consistency between tables) when doing a full backup.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Performing <literal>LOCK TABLE ...</literal> and
+ <literal>FLUSH TABLES ...</literal> doesn't guarantee that
+ there isn't a half-finished transaction in progress on the
+ table.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="stmt">ANALYZE TABLE</literal>,
- <literal role="stmt">OPTIMIZE TABLE</literal>, and
- <literal role="stmt">REPAIR TABLE</literal> may cause
- problems on tables for which you are using
- <literal role="stmt">INSERT DELAYED</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Replication uses query-level logging: The master writes the
+ executed queries to the binary log. This is a very fast,
+ compact, and efficient logging method that works perfectly
+ in most cases.
+ </para>
- <listitem>
- <para>
- Performing <literal>LOCK TABLE ...</literal> and
- <literal>FLUSH TABLES ...</literal> doesn't guarantee that
- there isn't a half-finished transaction in progress on the
- table.
- </para>
- </listitem>
+ <para>
+ It is possible for the data on the master and slave to
+ become different if a query is designed in such a way that
+ the data modification is nondeterministic (generally not a
+ recommended practice, even outside of replication).
+ </para>
- <listitem>
- <para>
- Replication uses query-level logging: The master writes
- the executed queries to the binary log. This is a very
- fast, compact, and efficient logging method that works
- perfectly in most cases.
- </para>
+ <para>
+ For example:
+ </para>
- <para>
- It is possible for the data on the master and slave to
- become different if a query is designed in such a way that
- the data modification is nondeterministic (generally not a
- recommended practice, even outside of replication).
- </para>
+ <itemizedlist>
- <para>
- For example:
- </para>
+ <listitem>
+ <para>
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE ... SELECT</literal> or
+ <literal role="stmt" condition="insert-select">INSERT
+ ... SELECT</literal> statements that insert zero or
+ <literal>NULL</literal> values into an
+ <literal>AUTO_INCREMENT</literal> column.
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ <literal role="stmt">DELETE</literal> if you are
+ deleting rows from a table that has foreign keys with
+ <literal>ON DELETE CASCADE</literal> properties.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="stmt" condition="create-table-select">CREATE
- TABLE ... SELECT</literal> or
- <literal role="stmt" condition="insert-select">INSERT
- ... SELECT</literal> statements that insert zero or
- <literal>NULL</literal> values into an
- <literal>AUTO_INCREMENT</literal> column.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt" condition="replace">REPLACE ...
+ SELECT</literal>, <literal>INSERT IGNORE ...
+ SELECT</literal> if you have duplicate key values in the
+ inserted data.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="stmt">DELETE</literal> if you are
- deleting rows from a table that has foreign keys with
- <literal>ON DELETE CASCADE</literal> properties.
- </para>
- </listitem>
+ </itemizedlist>
- <listitem>
- <para>
- <literal role="stmt" condition="replace">REPLACE ...
- SELECT</literal>, <literal>INSERT IGNORE ...
- SELECT</literal> if you have duplicate key values in
- the inserted data.
- </para>
- </listitem>
+ <para>
+ <emphasis role="bold">If and only if the preceding queries
+ have no <literal>ORDER BY</literal> clause guaranteeing a
+ deterministic order</emphasis>.
+ </para>
- </itemizedlist>
+ <para>
+ For example, for
+ <literal role="stmt" condition="insert-select">INSERT ...
+ SELECT</literal> with no <literal>ORDER BY</literal>, the
+ <literal role="stmt">SELECT</literal> may return rows in a
+ different order (which results in a row having different
+ ranks, hence getting a different number in the
+ <literal>AUTO_INCREMENT</literal> column), depending on the
+ choices made by the optimizers on the master and slave.
+ </para>
- <para>
- <emphasis role="bold">If and only if the preceding queries
- have no <literal>ORDER BY</literal> clause guaranteeing a
- deterministic order</emphasis>.
- </para>
+ <para>
+ A query is optimized differently on the master and slave
+ only if:
+ </para>
- <para>
- For example, for
- <literal role="stmt" condition="insert-select">INSERT ...
- SELECT</literal> with no <literal>ORDER BY</literal>, the
- <literal role="stmt">SELECT</literal> may return rows in a
- different order (which results in a row having different
- ranks, hence getting a different number in the
- <literal>AUTO_INCREMENT</literal> column), depending on
- the choices made by the optimizers on the master and
- slave.
- </para>
+ <itemizedlist>
- <para>
- A query is optimized differently on the master and slave
- only if:
- </para>
+ <listitem>
+ <para>
+ The table is stored using a different storage engine on
+ the master than on the slave. (It is possible to use
+ different storage engines on the master and slave. For
+ example, you can use <literal>InnoDB</literal> on the
+ master, but <literal>MyISAM</literal> on the slave if
+ the slave has less available disk space.)
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ MySQL buffer sizes
+ (<literal role="sysvar">key_buffer_size</literal>, and
+ so on) are different on the master and slave.
+ </para>
+ </listitem>
- <listitem>
- <para>
- The table is stored using a different storage engine
- on the master than on the slave. (It is possible to
- use different storage engines on the master and slave.
- For example, you can use <literal>InnoDB</literal> on
- the master, but <literal>MyISAM</literal> on the slave
- if the slave has less available disk space.)
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The master and slave run different MySQL versions, and
+ the optimizer code differs between these versions.
+ </para>
+ </listitem>
- <listitem>
- <para>
- MySQL buffer sizes
- (<literal role="sysvar">key_buffer_size</literal>, and
- so on) are different on the master and slave.
- </para>
- </listitem>
+ </itemizedlist>
- <listitem>
- <para>
- The master and slave run different MySQL versions, and
- the optimizer code differs between these versions.
- </para>
- </listitem>
+ <para>
+ This problem may also affect database restoration using
+ <command>mysqlbinlog|mysql</command>.
+ </para>
- </itemizedlist>
+ <para>
+ The easiest way to avoid this problem is to add an
+ <literal>ORDER BY</literal> clause to the aforementioned
+ nondeterministic queries to ensure that the rows are always
+ stored or modified in the same order.
+ </para>
- <para>
- This problem may also affect database restoration using
- <command>mysqlbinlog|mysql</command>.
- </para>
+ <para>
+ In future MySQL versions, we will automatically add an
+ <literal>ORDER BY</literal> clause when needed.
+ </para>
+ </listitem>
- <para>
- The easiest way to avoid this problem is to add an
- <literal>ORDER BY</literal> clause to the aforementioned
- nondeterministic queries to ensure that the rows are
- always stored or modified in the same order.
- </para>
+ </itemizedlist>
- <para>
- In future MySQL versions, we will automatically add an
- <literal>ORDER BY</literal> clause when needed.
- </para>
- </listitem>
+ <para>
+ The following issues are known and will be fixed in due time:
+ </para>
- </itemizedlist>
+ <itemizedlist>
- <para>
- The following issues are known and will be fixed in due time:
- </para>
+ <listitem>
+ <para>
+ Log file names are based on the server host name (if you
+ don't specify a file name with the startup option). You have
+ to use options such as
+ <option role="mysqld">--log-bin=<replaceable>old_host_name</replaceable>-bin</option>
+ if you change your host name to something else. Another
+ option is to rename the old files to reflect your host name
+ change (if these are binary logs, you need to edit the
+ binary log index file and fix the binary log file names
+ there as well). See <xref linkend="server-options"/>.
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ <command>mysqlbinlog</command> does not delete temporary
+ files left after a
+ <literal role="stmt" condition="load-data">LOAD DATA
+ INFILE</literal> statement. See
+ <xref linkend="mysqlbinlog"/>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Log file names are based on the server host name (if you
- don't specify a file name with the startup option). You
- have to use options such as
- <option role="mysqld">--log-bin=<replaceable>old_host_name</replaceable>-bin</option>
- if you change your host name to something else. Another
- option is to rename the old files to reflect your host
- name change (if these are binary logs, you need to edit
- the binary log index file and fix the binary log file
- names there as well). See
- <xref linkend="server-options"/>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal>RENAME</literal> doesn't work with
+ <literal>TEMPORARY</literal> tables or tables used in a
+ <literal>MERGE</literal> table.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <command>mysqlbinlog</command> does not delete temporary
- files left after a
- <literal role="stmt" condition="load-data">LOAD DATA
- INFILE</literal> statement. See
- <xref linkend="mysqlbinlog"/>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Due to the way table format (<filename>.frm</filename>)
+ files are stored, you cannot use character 255
+ (<literal>CHAR(255)</literal>) in table names, column names,
+ or enumerations.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>RENAME</literal> doesn't work with
- <literal>TEMPORARY</literal> tables or tables used in a
- <literal>MERGE</literal> table.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ When using <literal>SET CHARACTER SET</literal>, you can't
+ use translated characters in database, table, and column
+ names.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Due to the way table format (<filename>.frm</filename>)
- files are stored, you cannot use character 255
- (<literal>CHAR(255)</literal>) in table names, column
- names, or enumerations.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ You can't use <quote><literal>_</literal></quote> or
+ <quote><literal>%</literal></quote> with
+ <literal>ESCAPE</literal> in
+ <literal role="op" condition="like">LIKE ...
+ ESCAPE</literal>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- When using <literal>SET CHARACTER SET</literal>, you can't
- use translated characters in database, table, and column
- names.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ You cannot build the server in another directory when using
+ MIT-pthreads. Because this requires changes to MIT-pthreads,
+ we are not likely to fix this. See
+ <xref linkend="mit-pthreads"/>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- You can't use <quote><literal>_</literal></quote> or
- <quote><literal>%</literal></quote> with
- <literal>ESCAPE</literal> in
- <literal role="op" condition="like">LIKE ...
- ESCAPE</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="type">BLOB</literal> and
+ <literal role="type">TEXT</literal> values can't reliably be
+ used in <literal>GROUP BY</literal>, <literal>ORDER
+ BY</literal> or <literal>DISTINCT</literal>. Only the first
+ <literal role="sysvar">max_sort_length</literal> bytes are
+ used when comparing <literal role="type">BLOB</literal>
+ values in these cases. The default value of
+ <literal role="sysvar">max_sort_length</literal> is 1024 and
+ can be changed at server startup time or at runtime.
+ </para>
+ </listitem>
- <listitem>
- <para>
- You cannot build the server in another directory when
- using MIT-pthreads. Because this requires changes to
- MIT-pthreads, we are not likely to fix this. See
- <xref linkend="mit-pthreads"/>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Numeric calculations are done with
+ <literal role="type">BIGINT</literal> or
+ <literal role="type">DOUBLE</literal> (both are normally 64
+ bits long). Which precision you get depends on the function.
+ The general rule is that bit functions are performed with
+ <literal role="type">BIGINT</literal> precision,
+ <literal role="func">IF()</literal> and
+ <literal role="func">ELT()</literal> with
+ <literal role="type">BIGINT</literal> or
+ <literal role="type">DOUBLE</literal> precision, and the
+ rest with <literal role="type">DOUBLE</literal> precision.
+ You should try to avoid using unsigned long long values if
+ they resolve to be larger than 63 bits (9223372036854775807)
+ for anything other than bit fields.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="type">BLOB</literal> and
- <literal role="type">TEXT</literal> values can't reliably
- be used in <literal>GROUP BY</literal>, <literal>ORDER
- BY</literal> or <literal>DISTINCT</literal>. Only the
- first <literal role="sysvar">max_sort_length</literal>
- bytes are used when comparing
- <literal role="type">BLOB</literal> values in these cases.
- The default value of
- <literal role="sysvar">max_sort_length</literal> is 1024
- and can be changed at server startup time or at runtime.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ You can have up to 255 <literal role="type">ENUM</literal>
+ and <literal role="type">SET</literal> columns in one table.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Numeric calculations are done with
- <literal role="type">BIGINT</literal> or
- <literal role="type">DOUBLE</literal> (both are normally
- 64 bits long). Which precision you get depends on the
- function. The general rule is that bit functions are
- performed with <literal role="type">BIGINT</literal>
- precision, <literal role="func">IF()</literal> and
- <literal role="func">ELT()</literal> with
- <literal role="type">BIGINT</literal> or
- <literal role="type">DOUBLE</literal> precision, and the
- rest with <literal role="type">DOUBLE</literal> precision.
- You should try to avoid using unsigned long long values if
- they resolve to be larger than 63 bits
- (9223372036854775807) for anything other than bit fields.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ In <literal role="func">MIN()</literal>,
+ <literal role="func">MAX()</literal>, and other aggregate
+ functions, MySQL currently compares
+ <literal role="type">ENUM</literal> and
+ <literal role="type">SET</literal> columns by their string
+ value rather than by the string's relative position in the
+ set.
+ </para>
+ </listitem>
- <listitem>
- <para>
- You can have up to 255 <literal role="type">ENUM</literal>
- and <literal role="type">SET</literal> columns in one
- table.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <command>mysqld_safe</command> redirects all messages from
+ <command>mysqld</command> to the <command>mysqld</command>
+ log. One problem with this is that if you execute
+ <command>mysqladmin refresh</command> to close and reopen
+ the log, <literal>stdout</literal> and
+ <literal>stderr</literal> are still redirected to the old
+ log. If you use the general query log extensively, you
+ should edit <command>mysqld_safe</command> to log to
+ <filename><replaceable>host_name</replaceable>.err</filename>
+ instead of
+ <filename><replaceable>host_name</replaceable>.log</filename>
+ so that you can easily reclaim the space for the old log by
+ deleting it and executing <command>mysqladmin
+ refresh</command>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- In <literal role="func">MIN()</literal>,
- <literal role="func">MAX()</literal>, and other aggregate
- functions, MySQL currently compares
- <literal role="type">ENUM</literal> and
- <literal role="type">SET</literal> columns by their string
- value rather than by the string's relative position in the
- set.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ In an <literal role="stmt">UPDATE</literal> statement,
+ columns are updated from left to right. If you refer to an
+ updated column, you get the updated value instead of the
+ original value. For example, the following statement
+ increments <literal>KEY</literal> by <literal>2</literal>,
+ <emphasis role="bold">not</emphasis> <literal>1</literal>:
+ </para>
- <listitem>
- <para>
- <command>mysqld_safe</command> redirects all messages from
- <command>mysqld</command> to the <command>mysqld</command>
- log. One problem with this is that if you execute
- <command>mysqladmin refresh</command> to close and reopen
- the log, <literal>stdout</literal> and
- <literal>stderr</literal> are still redirected to the old
- log. If you use the general query log extensively, you
- should edit <command>mysqld_safe</command> to log to
- <filename><replaceable>host_name</replaceable>.err</filename>
- instead of
- <filename><replaceable>host_name</replaceable>.log</filename>
- so that you can easily reclaim the space for the old log
- by deleting it and executing <command>mysqladmin
- refresh</command>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- In an <literal role="stmt">UPDATE</literal> statement,
- columns are updated from left to right. If you refer to an
- updated column, you get the updated value instead of the
- original value. For example, the following statement
- increments <literal>KEY</literal> by <literal>2</literal>,
- <emphasis role="bold">not</emphasis> <literal>1</literal>:
- </para>
-
<programlisting>
mysql> <userinput>UPDATE <replaceable>tbl_name</replaceable> SET KEY=KEY+1,KEY=KEY+1;</userinput>
</programlisting>
- </listitem>
+ </listitem>
- <listitem>
- <para>
- You can refer to multiple temporary tables in the same
- query, but you cannot refer to any given temporary table
- more than once. For example, the following doesn't work:
- </para>
+ <listitem>
+ <para>
+ You can refer to multiple temporary tables in the same
+ query, but you cannot refer to any given temporary table
+ more than once. For example, the following doesn't work:
+ </para>
<programlisting>
mysql> <userinput>SELECT * FROM temp_table, temp_table AS t2;</userinput>
ERROR 1137: Can't reopen table: 'temp_table'
</programlisting>
- </listitem>
+ </listitem>
- <listitem>
- <para>
- The optimizer may handle <literal>DISTINCT</literal>
- differently when you are using <quote>hidden</quote>
- columns in a join than when you are not. In a join, hidden
- columns are counted as part of the result (even if they
- are not shown), whereas in normal queries, hidden columns
- don't participate in the <literal>DISTINCT</literal>
- comparison. We will probably change this in the future to
- never compare the hidden columns when executing
- <literal>DISTINCT</literal>.
- </para>
+ <listitem>
+ <para>
+ The optimizer may handle <literal>DISTINCT</literal>
+ differently when you are using <quote>hidden</quote> columns
+ in a join than when you are not. In a join, hidden columns
+ are counted as part of the result (even if they are not
+ shown), whereas in normal queries, hidden columns don't
+ participate in the <literal>DISTINCT</literal> comparison.
+ We will probably change this in the future to never compare
+ the hidden columns when executing
+ <literal>DISTINCT</literal>.
+ </para>
- <para>
- An example of this is:
- </para>
+ <para>
+ An example of this is:
+ </para>
<programlisting>
SELECT DISTINCT mp3id FROM band_downloads
WHERE userid = 9 ORDER BY id DESC;
</programlisting>
- <para>
- and
- </para>
+ <para>
+ and
+ </para>
<programlisting>
SELECT DISTINCT band_downloads.mp3id
@@ -5864,54 +5854,49 @@
ORDER BY band_downloads.id DESC;
</programlisting>
- <para>
- In the second case, using MySQL Server 3.23.x, you may get
- two identical rows in the result set (because the values
- in the hidden <literal>id</literal> column may differ).
- </para>
+ <para>
+ In the second case, using MySQL Server 3.23.x, you may get
+ two identical rows in the result set (because the values in
+ the hidden <literal>id</literal> column may differ).
+ </para>
- <para>
- Note that this happens only for queries where that do not
- have the <literal>ORDER BY</literal> columns in the
- result.
- </para>
- </listitem>
+ <para>
+ Note that this happens only for queries where that do not
+ have the <literal>ORDER BY</literal> columns in the result.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If you execute a <literal>PROCEDURE</literal> on a query
- that returns an empty set, in some cases the
- <literal>PROCEDURE</literal> does not transform the
- columns.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If you execute a <literal>PROCEDURE</literal> on a query
+ that returns an empty set, in some cases the
+ <literal>PROCEDURE</literal> does not transform the columns.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Creation of a table of type <literal>MERGE</literal>
- doesn't check whether the underlying tables are compatible
- types.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Creation of a table of type <literal>MERGE</literal> doesn't
+ check whether the underlying tables are compatible types.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If you use <literal role="stmt">ALTER TABLE</literal> to
- add a <literal>UNIQUE</literal> index to a table used in a
- <literal>MERGE</literal> table and then add a normal index
- on the <literal>MERGE</literal> table, the key order is
- different for the tables if there was an old,
- non-<literal>UNIQUE</literal> key in the table. This is
- because <literal role="stmt">ALTER TABLE</literal> puts
- <literal>UNIQUE</literal> indexes before normal indexes to
- be able to detect duplicate keys as early as possible.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If you use <literal role="stmt">ALTER TABLE</literal> to add
+ a <literal>UNIQUE</literal> index to a table used in a
+ <literal>MERGE</literal> table and then add a normal index
+ on the <literal>MERGE</literal> table, the key order is
+ different for the tables if there was an old,
+ non-<literal>UNIQUE</literal> key in the table. This is
+ because <literal role="stmt">ALTER TABLE</literal> puts
+ <literal>UNIQUE</literal> indexes before normal indexes to
+ be able to detect duplicate keys as early as possible.
+ </para>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- </section>
-
</section>
</section>
Modified: trunk/refman-5.5/renamed-nodes.txt
===================================================================
--- trunk/refman-5.5/renamed-nodes.txt 2010-10-01 13:10:36 UTC (rev 22945)
+++ trunk/refman-5.5/renamed-nodes.txt 2010-10-01 15:48:40 UTC (rev 22946)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 0; 606 bytes
@@ -31,6 +31,7 @@
load-table-from-master introduction 2011-03-06
log-tables log-destinations 2011-09-21
mysql-copyright index 2011-04-09
+open-bugs bugs 2011-10-01
other-functions miscellaneous-functions 2011-06-18
plugin-api-general plugin-general-data-structures 2011-03-30
plugin-api-type-specific plugin-type-specific-data-structures 2011-03-30
Modified: trunk/refman-5.6/errors-problems-core.xml
===================================================================
--- trunk/refman-5.6/errors-problems-core.xml 2010-10-01 13:10:36 UTC (rev 22945)
+++ trunk/refman-5.6/errors-problems-core.xml 2010-10-01 15:48:40 UTC (rev 22946)
Changed blocks: 3, Lines Added: 398, Lines Deleted: 413; 36185 bytes
@@ -5416,8 +5416,7 @@
</indexterm>
<para>
- This section is a list of the known issues in recent versions of
- MySQL.
+ This section lists known issues in recent versions of MySQL.
</para>
<para>
@@ -5428,439 +5427,430 @@
Internals: Porting</ulink>.
</para>
- <section id="open-bugs">
+ <para>
+ The following problems are known:
+ </para>
- <title>Open Issues in MySQL</title>
+ <itemizedlist>
- <para>
- The following problems are known:
- </para>
+ <listitem>
+ <para>
+ Subquery optimization for <literal>IN</literal> is not as
+ effective as for <literal>=</literal>.
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ Even if you use <literal>lower_case_table_names=2</literal>
+ (which enables MySQL to remember the case used for databases
+ and table names), MySQL does not remember the case used for
+ database names for the function
+ <literal role="func">DATABASE()</literal> or within the
+ various logs (on case-insensitive systems).
+ </para>
+ </listitem>
- <listitem>
- <para>
- Subquery optimization for <literal>IN</literal> is not as
- effective as for <literal>=</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Dropping a <literal>FOREIGN KEY</literal> constraint doesn't
+ work in replication because the constraint may have another
+ name on the slave.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Even if you use
- <literal>lower_case_table_names=2</literal> (which enables
- MySQL to remember the case used for databases and table
- names), MySQL does not remember the case used for database
- names for the function
- <literal role="func">DATABASE()</literal> or within the
- various logs (on case-insensitive systems).
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt">REPLACE</literal> (and
+ <literal role="stmt">LOAD DATA</literal> with the
+ <literal role="stmt">REPLACE</literal> option) does not
+ trigger <literal>ON DELETE CASCADE</literal>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Dropping a <literal>FOREIGN KEY</literal> constraint
- doesn't work in replication because the constraint may
- have another name on the slave.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal>DISTINCT</literal> with <literal>ORDER BY</literal>
+ doesn't work inside
+ <literal role="func">GROUP_CONCAT()</literal> if you don't
+ use all and only those columns that are in the
+ <literal>DISTINCT</literal> list.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="stmt">REPLACE</literal> (and
- <literal role="stmt">LOAD DATA</literal> with the
- <literal role="stmt">REPLACE</literal> option) does not
- trigger <literal>ON DELETE CASCADE</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If one user has a long-running transaction and another user
+ drops a table that is updated in the transaction, there is
+ small chance that the binary log may contain the
+ <literal role="stmt">DROP TABLE</literal> statement before
+ the table is used in the transaction itself. We plan to fix
+ this by having the <literal role="stmt">DROP TABLE</literal>
+ statement wait until the table is not being used in any
+ transaction.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>DISTINCT</literal> with <literal>ORDER
- BY</literal> doesn't work inside
- <literal role="func">GROUP_CONCAT()</literal> if you don't
- use all and only those columns that are in the
- <literal>DISTINCT</literal> list.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ When inserting a big integer value (between
+ 2<superscript>63</superscript> and
+ 2<superscript>64</superscript>−1) into a decimal or
+ string column, it is inserted as a negative value because
+ the number is evaluated in a signed integer context.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If one user has a long-running transaction and another
- user drops a table that is updated in the transaction,
- there is small chance that the binary log may contain the
- <literal role="stmt">DROP TABLE</literal> statement before
- the table is used in the transaction itself. We plan to
- fix this by having the <literal role="stmt">DROP
- TABLE</literal> statement wait until the table is not
- being used in any transaction.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt" condition="flush">FLUSH TABLES WITH
+ READ LOCK</literal> does not block
+ <literal role="stmt">COMMIT</literal> if the server is
+ running without binary logging, which may cause a problem
+ (of consistency between tables) when doing a full backup.
+ </para>
+ </listitem>
- <listitem>
- <para>
- When inserting a big integer value (between
- 2<superscript>63</superscript> and
- 2<superscript>64</superscript>−1) into a decimal or
- string column, it is inserted as a negative value because
- the number is evaluated in a signed integer context.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt">ANALYZE TABLE</literal>,
+ <literal role="stmt">OPTIMIZE TABLE</literal>, and
+ <literal role="stmt">REPAIR TABLE</literal> may cause
+ problems on tables for which you are using
+ <literal role="stmt">INSERT DELAYED</literal>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="stmt" condition="flush">FLUSH TABLES WITH
- READ LOCK</literal> does not block
- <literal role="stmt">COMMIT</literal> if the server is
- running without binary logging, which may cause a problem
- (of consistency between tables) when doing a full backup.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Performing <literal>LOCK TABLE ...</literal> and
+ <literal>FLUSH TABLES ...</literal> doesn't guarantee that
+ there isn't a half-finished transaction in progress on the
+ table.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="stmt">ANALYZE TABLE</literal>,
- <literal role="stmt">OPTIMIZE TABLE</literal>, and
- <literal role="stmt">REPAIR TABLE</literal> may cause
- problems on tables for which you are using
- <literal role="stmt">INSERT DELAYED</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Replication uses query-level logging: The master writes the
+ executed queries to the binary log. This is a very fast,
+ compact, and efficient logging method that works perfectly
+ in most cases.
+ </para>
- <listitem>
- <para>
- Performing <literal>LOCK TABLE ...</literal> and
- <literal>FLUSH TABLES ...</literal> doesn't guarantee that
- there isn't a half-finished transaction in progress on the
- table.
- </para>
- </listitem>
+ <para>
+ It is possible for the data on the master and slave to
+ become different if a query is designed in such a way that
+ the data modification is nondeterministic (generally not a
+ recommended practice, even outside of replication).
+ </para>
- <listitem>
- <para>
- Replication uses query-level logging: The master writes
- the executed queries to the binary log. This is a very
- fast, compact, and efficient logging method that works
- perfectly in most cases.
- </para>
+ <para>
+ For example:
+ </para>
- <para>
- It is possible for the data on the master and slave to
- become different if a query is designed in such a way that
- the data modification is nondeterministic (generally not a
- recommended practice, even outside of replication).
- </para>
+ <itemizedlist>
- <para>
- For example:
- </para>
+ <listitem>
+ <para>
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE ... SELECT</literal> or
+ <literal role="stmt" condition="insert-select">INSERT
+ ... SELECT</literal> statements that insert zero or
+ <literal>NULL</literal> values into an
+ <literal>AUTO_INCREMENT</literal> column.
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ <literal role="stmt">DELETE</literal> if you are
+ deleting rows from a table that has foreign keys with
+ <literal>ON DELETE CASCADE</literal> properties.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="stmt" condition="create-table-select">CREATE
- TABLE ... SELECT</literal> or
- <literal role="stmt" condition="insert-select">INSERT
- ... SELECT</literal> statements that insert zero or
- <literal>NULL</literal> values into an
- <literal>AUTO_INCREMENT</literal> column.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt" condition="replace">REPLACE ...
+ SELECT</literal>, <literal>INSERT IGNORE ...
+ SELECT</literal> if you have duplicate key values in the
+ inserted data.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="stmt">DELETE</literal> if you are
- deleting rows from a table that has foreign keys with
- <literal>ON DELETE CASCADE</literal> properties.
- </para>
- </listitem>
+ </itemizedlist>
- <listitem>
- <para>
- <literal role="stmt" condition="replace">REPLACE ...
- SELECT</literal>, <literal>INSERT IGNORE ...
- SELECT</literal> if you have duplicate key values in
- the inserted data.
- </para>
- </listitem>
+ <para>
+ <emphasis role="bold">If and only if the preceding queries
+ have no <literal>ORDER BY</literal> clause guaranteeing a
+ deterministic order</emphasis>.
+ </para>
- </itemizedlist>
+ <para>
+ For example, for
+ <literal role="stmt" condition="insert-select">INSERT ...
+ SELECT</literal> with no <literal>ORDER BY</literal>, the
+ <literal role="stmt">SELECT</literal> may return rows in a
+ different order (which results in a row having different
+ ranks, hence getting a different number in the
+ <literal>AUTO_INCREMENT</literal> column), depending on the
+ choices made by the optimizers on the master and slave.
+ </para>
- <para>
- <emphasis role="bold">If and only if the preceding queries
- have no <literal>ORDER BY</literal> clause guaranteeing a
- deterministic order</emphasis>.
- </para>
+ <para>
+ A query is optimized differently on the master and slave
+ only if:
+ </para>
- <para>
- For example, for
- <literal role="stmt" condition="insert-select">INSERT ...
- SELECT</literal> with no <literal>ORDER BY</literal>, the
- <literal role="stmt">SELECT</literal> may return rows in a
- different order (which results in a row having different
- ranks, hence getting a different number in the
- <literal>AUTO_INCREMENT</literal> column), depending on
- the choices made by the optimizers on the master and
- slave.
- </para>
+ <itemizedlist>
- <para>
- A query is optimized differently on the master and slave
- only if:
- </para>
+ <listitem>
+ <para>
+ The table is stored using a different storage engine on
+ the master than on the slave. (It is possible to use
+ different storage engines on the master and slave. For
+ example, you can use <literal>InnoDB</literal> on the
+ master, but <literal>MyISAM</literal> on the slave if
+ the slave has less available disk space.)
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ MySQL buffer sizes
+ (<literal role="sysvar">key_buffer_size</literal>, and
+ so on) are different on the master and slave.
+ </para>
+ </listitem>
- <listitem>
- <para>
- The table is stored using a different storage engine
- on the master than on the slave. (It is possible to
- use different storage engines on the master and slave.
- For example, you can use <literal>InnoDB</literal> on
- the master, but <literal>MyISAM</literal> on the slave
- if the slave has less available disk space.)
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The master and slave run different MySQL versions, and
+ the optimizer code differs between these versions.
+ </para>
+ </listitem>
- <listitem>
- <para>
- MySQL buffer sizes
- (<literal role="sysvar">key_buffer_size</literal>, and
- so on) are different on the master and slave.
- </para>
- </listitem>
+ </itemizedlist>
- <listitem>
- <para>
- The master and slave run different MySQL versions, and
- the optimizer code differs between these versions.
- </para>
- </listitem>
+ <para>
+ This problem may also affect database restoration using
+ <command>mysqlbinlog|mysql</command>.
+ </para>
- </itemizedlist>
+ <para>
+ The easiest way to avoid this problem is to add an
+ <literal>ORDER BY</literal> clause to the aforementioned
+ nondeterministic queries to ensure that the rows are always
+ stored or modified in the same order.
+ </para>
- <para>
- This problem may also affect database restoration using
- <command>mysqlbinlog|mysql</command>.
- </para>
+ <para>
+ In future MySQL versions, we will automatically add an
+ <literal>ORDER BY</literal> clause when needed.
+ </para>
+ </listitem>
- <para>
- The easiest way to avoid this problem is to add an
- <literal>ORDER BY</literal> clause to the aforementioned
- nondeterministic queries to ensure that the rows are
- always stored or modified in the same order.
- </para>
+ </itemizedlist>
- <para>
- In future MySQL versions, we will automatically add an
- <literal>ORDER BY</literal> clause when needed.
- </para>
- </listitem>
+ <para>
+ The following issues are known and will be fixed in due time:
+ </para>
- </itemizedlist>
+ <itemizedlist>
- <para>
- The following issues are known and will be fixed in due time:
- </para>
+ <listitem>
+ <para>
+ Log file names are based on the server host name (if you
+ don't specify a file name with the startup option). You have
+ to use options such as
+ <option role="mysqld">--log-bin=<replaceable>old_host_name</replaceable>-bin</option>
+ if you change your host name to something else. Another
+ option is to rename the old files to reflect your host name
+ change (if these are binary logs, you need to edit the
+ binary log index file and fix the binary log file names
+ there as well). See <xref linkend="server-options"/>.
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ <command>mysqlbinlog</command> does not delete temporary
+ files left after a
+ <literal role="stmt" condition="load-data">LOAD DATA
+ INFILE</literal> statement. See
+ <xref linkend="mysqlbinlog"/>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Log file names are based on the server host name (if you
- don't specify a file name with the startup option). You
- have to use options such as
- <option role="mysqld">--log-bin=<replaceable>old_host_name</replaceable>-bin</option>
- if you change your host name to something else. Another
- option is to rename the old files to reflect your host
- name change (if these are binary logs, you need to edit
- the binary log index file and fix the binary log file
- names there as well). See
- <xref linkend="server-options"/>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal>RENAME</literal> doesn't work with
+ <literal>TEMPORARY</literal> tables or tables used in a
+ <literal>MERGE</literal> table.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <command>mysqlbinlog</command> does not delete temporary
- files left after a
- <literal role="stmt" condition="load-data">LOAD DATA
- INFILE</literal> statement. See
- <xref linkend="mysqlbinlog"/>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Due to the way table format (<filename>.frm</filename>)
+ files are stored, you cannot use character 255
+ (<literal>CHAR(255)</literal>) in table names, column names,
+ or enumerations.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>RENAME</literal> doesn't work with
- <literal>TEMPORARY</literal> tables or tables used in a
- <literal>MERGE</literal> table.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ When using <literal>SET CHARACTER SET</literal>, you can't
+ use translated characters in database, table, and column
+ names.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Due to the way table format (<filename>.frm</filename>)
- files are stored, you cannot use character 255
- (<literal>CHAR(255)</literal>) in table names, column
- names, or enumerations.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ You can't use <quote><literal>_</literal></quote> or
+ <quote><literal>%</literal></quote> with
+ <literal>ESCAPE</literal> in
+ <literal role="op" condition="like">LIKE ...
+ ESCAPE</literal>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- When using <literal>SET CHARACTER SET</literal>, you can't
- use translated characters in database, table, and column
- names.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ You cannot build the server in another directory when using
+ MIT-pthreads. Because this requires changes to MIT-pthreads,
+ we are not likely to fix this. See
+ <xref linkend="mit-pthreads"/>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- You can't use <quote><literal>_</literal></quote> or
- <quote><literal>%</literal></quote> with
- <literal>ESCAPE</literal> in
- <literal role="op" condition="like">LIKE ...
- ESCAPE</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="type">BLOB</literal> and
+ <literal role="type">TEXT</literal> values can't reliably be
+ used in <literal>GROUP BY</literal>, <literal>ORDER
+ BY</literal> or <literal>DISTINCT</literal>. Only the first
+ <literal role="sysvar">max_sort_length</literal> bytes are
+ used when comparing <literal role="type">BLOB</literal>
+ values in these cases. The default value of
+ <literal role="sysvar">max_sort_length</literal> is 1024 and
+ can be changed at server startup time or at runtime.
+ </para>
+ </listitem>
- <listitem>
- <para>
- You cannot build the server in another directory when
- using MIT-pthreads. Because this requires changes to
- MIT-pthreads, we are not likely to fix this. See
- <xref linkend="mit-pthreads"/>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Numeric calculations are done with
+ <literal role="type">BIGINT</literal> or
+ <literal role="type">DOUBLE</literal> (both are normally 64
+ bits long). Which precision you get depends on the function.
+ The general rule is that bit functions are performed with
+ <literal role="type">BIGINT</literal> precision,
+ <literal role="func">IF()</literal> and
+ <literal role="func">ELT()</literal> with
+ <literal role="type">BIGINT</literal> or
+ <literal role="type">DOUBLE</literal> precision, and the
+ rest with <literal role="type">DOUBLE</literal> precision.
+ You should try to avoid using unsigned long long values if
+ they resolve to be larger than 63 bits (9223372036854775807)
+ for anything other than bit fields.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="type">BLOB</literal> and
- <literal role="type">TEXT</literal> values can't reliably
- be used in <literal>GROUP BY</literal>, <literal>ORDER
- BY</literal> or <literal>DISTINCT</literal>. Only the
- first <literal role="sysvar">max_sort_length</literal>
- bytes are used when comparing
- <literal role="type">BLOB</literal> values in these cases.
- The default value of
- <literal role="sysvar">max_sort_length</literal> is 1024
- and can be changed at server startup time or at runtime.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ You can have up to 255 <literal role="type">ENUM</literal>
+ and <literal role="type">SET</literal> columns in one table.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Numeric calculations are done with
- <literal role="type">BIGINT</literal> or
- <literal role="type">DOUBLE</literal> (both are normally
- 64 bits long). Which precision you get depends on the
- function. The general rule is that bit functions are
- performed with <literal role="type">BIGINT</literal>
- precision, <literal role="func">IF()</literal> and
- <literal role="func">ELT()</literal> with
- <literal role="type">BIGINT</literal> or
- <literal role="type">DOUBLE</literal> precision, and the
- rest with <literal role="type">DOUBLE</literal> precision.
- You should try to avoid using unsigned long long values if
- they resolve to be larger than 63 bits
- (9223372036854775807) for anything other than bit fields.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ In <literal role="func">MIN()</literal>,
+ <literal role="func">MAX()</literal>, and other aggregate
+ functions, MySQL currently compares
+ <literal role="type">ENUM</literal> and
+ <literal role="type">SET</literal> columns by their string
+ value rather than by the string's relative position in the
+ set.
+ </para>
+ </listitem>
- <listitem>
- <para>
- You can have up to 255 <literal role="type">ENUM</literal>
- and <literal role="type">SET</literal> columns in one
- table.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <command>mysqld_safe</command> redirects all messages from
+ <command>mysqld</command> to the <command>mysqld</command>
+ log. One problem with this is that if you execute
+ <command>mysqladmin refresh</command> to close and reopen
+ the log, <literal>stdout</literal> and
+ <literal>stderr</literal> are still redirected to the old
+ log. If you use the general query log extensively, you
+ should edit <command>mysqld_safe</command> to log to
+ <filename><replaceable>host_name</replaceable>.err</filename>
+ instead of
+ <filename><replaceable>host_name</replaceable>.log</filename>
+ so that you can easily reclaim the space for the old log by
+ deleting it and executing <command>mysqladmin
+ refresh</command>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- In <literal role="func">MIN()</literal>,
- <literal role="func">MAX()</literal>, and other aggregate
- functions, MySQL currently compares
- <literal role="type">ENUM</literal> and
- <literal role="type">SET</literal> columns by their string
- value rather than by the string's relative position in the
- set.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ In an <literal role="stmt">UPDATE</literal> statement,
+ columns are updated from left to right. If you refer to an
+ updated column, you get the updated value instead of the
+ original value. For example, the following statement
+ increments <literal>KEY</literal> by <literal>2</literal>,
+ <emphasis role="bold">not</emphasis> <literal>1</literal>:
+ </para>
- <listitem>
- <para>
- <command>mysqld_safe</command> redirects all messages from
- <command>mysqld</command> to the <command>mysqld</command>
- log. One problem with this is that if you execute
- <command>mysqladmin refresh</command> to close and reopen
- the log, <literal>stdout</literal> and
- <literal>stderr</literal> are still redirected to the old
- log. If you use the general query log extensively, you
- should edit <command>mysqld_safe</command> to log to
- <filename><replaceable>host_name</replaceable>.err</filename>
- instead of
- <filename><replaceable>host_name</replaceable>.log</filename>
- so that you can easily reclaim the space for the old log
- by deleting it and executing <command>mysqladmin
- refresh</command>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- In an <literal role="stmt">UPDATE</literal> statement,
- columns are updated from left to right. If you refer to an
- updated column, you get the updated value instead of the
- original value. For example, the following statement
- increments <literal>KEY</literal> by <literal>2</literal>,
- <emphasis role="bold">not</emphasis> <literal>1</literal>:
- </para>
-
<programlisting>
mysql> <userinput>UPDATE <replaceable>tbl_name</replaceable> SET KEY=KEY+1,KEY=KEY+1;</userinput>
</programlisting>
- </listitem>
+ </listitem>
- <listitem>
- <para>
- You can refer to multiple temporary tables in the same
- query, but you cannot refer to any given temporary table
- more than once. For example, the following doesn't work:
- </para>
+ <listitem>
+ <para>
+ You can refer to multiple temporary tables in the same
+ query, but you cannot refer to any given temporary table
+ more than once. For example, the following doesn't work:
+ </para>
<programlisting>
mysql> <userinput>SELECT * FROM temp_table, temp_table AS t2;</userinput>
ERROR 1137: Can't reopen table: 'temp_table'
</programlisting>
- </listitem>
+ </listitem>
- <listitem>
- <para>
- The optimizer may handle <literal>DISTINCT</literal>
- differently when you are using <quote>hidden</quote>
- columns in a join than when you are not. In a join, hidden
- columns are counted as part of the result (even if they
- are not shown), whereas in normal queries, hidden columns
- don't participate in the <literal>DISTINCT</literal>
- comparison. We will probably change this in the future to
- never compare the hidden columns when executing
- <literal>DISTINCT</literal>.
- </para>
+ <listitem>
+ <para>
+ The optimizer may handle <literal>DISTINCT</literal>
+ differently when you are using <quote>hidden</quote> columns
+ in a join than when you are not. In a join, hidden columns
+ are counted as part of the result (even if they are not
+ shown), whereas in normal queries, hidden columns don't
+ participate in the <literal>DISTINCT</literal> comparison.
+ We will probably change this in the future to never compare
+ the hidden columns when executing
+ <literal>DISTINCT</literal>.
+ </para>
- <para>
- An example of this is:
- </para>
+ <para>
+ An example of this is:
+ </para>
<programlisting>
SELECT DISTINCT mp3id FROM band_downloads
WHERE userid = 9 ORDER BY id DESC;
</programlisting>
- <para>
- and
- </para>
+ <para>
+ and
+ </para>
<programlisting>
SELECT DISTINCT band_downloads.mp3id
@@ -5870,54 +5860,49 @@
ORDER BY band_downloads.id DESC;
</programlisting>
- <para>
- In the second case, using MySQL Server 3.23.x, you may get
- two identical rows in the result set (because the values
- in the hidden <literal>id</literal> column may differ).
- </para>
+ <para>
+ In the second case, using MySQL Server 3.23.x, you may get
+ two identical rows in the result set (because the values in
+ the hidden <literal>id</literal> column may differ).
+ </para>
- <para>
- Note that this happens only for queries where that do not
- have the <literal>ORDER BY</literal> columns in the
- result.
- </para>
- </listitem>
+ <para>
+ Note that this happens only for queries where that do not
+ have the <literal>ORDER BY</literal> columns in the result.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If you execute a <literal>PROCEDURE</literal> on a query
- that returns an empty set, in some cases the
- <literal>PROCEDURE</literal> does not transform the
- columns.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If you execute a <literal>PROCEDURE</literal> on a query
+ that returns an empty set, in some cases the
+ <literal>PROCEDURE</literal> does not transform the columns.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Creation of a table of type <literal>MERGE</literal>
- doesn't check whether the underlying tables are compatible
- types.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Creation of a table of type <literal>MERGE</literal> doesn't
+ check whether the underlying tables are compatible types.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If you use <literal role="stmt">ALTER TABLE</literal> to
- add a <literal>UNIQUE</literal> index to a table used in a
- <literal>MERGE</literal> table and then add a normal index
- on the <literal>MERGE</literal> table, the key order is
- different for the tables if there was an old,
- non-<literal>UNIQUE</literal> key in the table. This is
- because <literal role="stmt">ALTER TABLE</literal> puts
- <literal>UNIQUE</literal> indexes before normal indexes to
- be able to detect duplicate keys as early as possible.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If you use <literal role="stmt">ALTER TABLE</literal> to add
+ a <literal>UNIQUE</literal> index to a table used in a
+ <literal>MERGE</literal> table and then add a normal index
+ on the <literal>MERGE</literal> table, the key order is
+ different for the tables if there was an old,
+ non-<literal>UNIQUE</literal> key in the table. This is
+ because <literal role="stmt">ALTER TABLE</literal> puts
+ <literal>UNIQUE</literal> indexes before normal indexes to
+ be able to detect duplicate keys as early as possible.
+ </para>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- </section>
-
</section>
</section>
Modified: trunk/refman-6.0/errors-problems.xml
===================================================================
--- trunk/refman-6.0/errors-problems.xml 2010-10-01 13:10:36 UTC (rev 22945)
+++ trunk/refman-6.0/errors-problems.xml 2010-10-01 15:48:40 UTC (rev 22946)
Changed blocks: 3, Lines Added: 396, Lines Deleted: 419; 36395 bytes
@@ -5414,8 +5414,7 @@
</indexterm>
<para>
- This section is a list of the known issues in recent versions of
- MySQL.
+ This section lists known issues in recent versions of MySQL.
</para>
<para>
@@ -5426,447 +5425,430 @@
Internals: Porting</ulink>.
</para>
- <section id="open-bugs">
+ <para>
+ The following problems are known:
+ </para>
- <title>Open Issues in MySQL</title>
+ <itemizedlist>
- <warning>
+ <listitem>
<para>
- This list is based on the known issues in past releases.
- There will be no further updates to this list for MySQL 6.0.
+ Subquery optimization for <literal>IN</literal> is not as
+ effective as for <literal>=</literal>.
</para>
- </warning>
+ </listitem>
- <para>
- The following problems are known and fixing them is a high
- priority:
- </para>
+ <listitem>
+ <para>
+ Even if you use <literal>lower_case_table_names=2</literal>
+ (which enables MySQL to remember the case used for databases
+ and table names), MySQL does not remember the case used for
+ database names for the function
+ <literal role="func">DATABASE()</literal> or within the
+ various logs (on case-insensitive systems).
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ Dropping a <literal>FOREIGN KEY</literal> constraint doesn't
+ work in replication because the constraint may have another
+ name on the slave.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Subquery optimization for <literal>IN</literal> is not as
- effective as for <literal>=</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt">REPLACE</literal> (and
+ <literal role="stmt">LOAD DATA</literal> with the
+ <literal role="stmt">REPLACE</literal> option) does not
+ trigger <literal>ON DELETE CASCADE</literal>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Even if you use
- <literal>lower_case_table_names=2</literal> (which enables
- MySQL to remember the case used for databases and table
- names), MySQL does not remember the case used for database
- names for the function
- <literal role="func">DATABASE()</literal> or within the
- various logs (on case-insensitive systems).
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal>DISTINCT</literal> with <literal>ORDER BY</literal>
+ doesn't work inside
+ <literal role="func">GROUP_CONCAT()</literal> if you don't
+ use all and only those columns that are in the
+ <literal>DISTINCT</literal> list.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Dropping a <literal>FOREIGN KEY</literal> constraint
- doesn't work in replication because the constraint may
- have another name on the slave.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If one user has a long-running transaction and another user
+ drops a table that is updated in the transaction, there is
+ small chance that the binary log may contain the
+ <literal role="stmt">DROP TABLE</literal> statement before
+ the table is used in the transaction itself. We plan to fix
+ this by having the <literal role="stmt">DROP TABLE</literal>
+ statement wait until the table is not being used in any
+ transaction.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="stmt">REPLACE</literal> (and
- <literal role="stmt">LOAD DATA</literal> with the
- <literal role="stmt">REPLACE</literal> option) does not
- trigger <literal>ON DELETE CASCADE</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ When inserting a big integer value (between
+ 2<superscript>63</superscript> and
+ 2<superscript>64</superscript>−1) into a decimal or
+ string column, it is inserted as a negative value because
+ the number is evaluated in a signed integer context.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>DISTINCT</literal> with <literal>ORDER
- BY</literal> doesn't work inside
- <literal role="func">GROUP_CONCAT()</literal> if you don't
- use all and only those columns that are in the
- <literal>DISTINCT</literal> list.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt" condition="flush">FLUSH TABLES WITH
+ READ LOCK</literal> does not block
+ <literal role="stmt">COMMIT</literal> if the server is
+ running without binary logging, which may cause a problem
+ (of consistency between tables) when doing a full backup.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If one user has a long-running transaction and another
- user drops a table that is updated in the transaction,
- there is small chance that the binary log may contain the
- <literal role="stmt">DROP TABLE</literal> statement before
- the table is used in the transaction itself. We plan to
- fix this by having the <literal role="stmt">DROP
- TABLE</literal> statement wait until the table is not
- being used in any transaction.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="stmt">ANALYZE TABLE</literal>,
+ <literal role="stmt">OPTIMIZE TABLE</literal>, and
+ <literal role="stmt">REPAIR TABLE</literal> may cause
+ problems on tables for which you are using
+ <literal role="stmt">INSERT DELAYED</literal>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- When inserting a big integer value (between
- 2<superscript>63</superscript> and
- 2<superscript>64</superscript>−1) into a decimal or
- string column, it is inserted as a negative value because
- the number is evaluated in a signed integer context.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Performing <literal>LOCK TABLE ...</literal> and
+ <literal>FLUSH TABLES ...</literal> doesn't guarantee that
+ there isn't a half-finished transaction in progress on the
+ table.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="stmt" condition="flush">FLUSH TABLES WITH
- READ LOCK</literal> does not block
- <literal role="stmt">COMMIT</literal> if the server is
- running without binary logging, which may cause a problem
- (of consistency between tables) when doing a full backup.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Replication uses query-level logging: The master writes the
+ executed queries to the binary log. This is a very fast,
+ compact, and efficient logging method that works perfectly
+ in most cases.
+ </para>
- <listitem>
- <para>
- <literal role="stmt">ANALYZE TABLE</literal>,
- <literal role="stmt">OPTIMIZE TABLE</literal>, and
- <literal role="stmt">REPAIR TABLE</literal> may cause
- problems on tables for which you are using
- <literal role="stmt">INSERT DELAYED</literal>.
- </para>
- </listitem>
+ <para>
+ It is possible for the data on the master and slave to
+ become different if a query is designed in such a way that
+ the data modification is nondeterministic (generally not a
+ recommended practice, even outside of replication).
+ </para>
- <listitem>
- <para>
- Performing <literal>LOCK TABLE ...</literal> and
- <literal>FLUSH TABLES ...</literal> doesn't guarantee that
- there isn't a half-finished transaction in progress on the
- table.
- </para>
- </listitem>
+ <para>
+ For example:
+ </para>
- <listitem>
- <para>
- Replication uses query-level logging: The master writes
- the executed queries to the binary log. This is a very
- fast, compact, and efficient logging method that works
- perfectly in most cases.
- </para>
+ <itemizedlist>
- <para>
- It is possible for the data on the master and slave to
- become different if a query is designed in such a way that
- the data modification is nondeterministic (generally not a
- recommended practice, even outside of replication).
- </para>
+ <listitem>
+ <para>
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE ... SELECT</literal> or
+ <literal role="stmt" condition="insert-select">INSERT
+ ... SELECT</literal> statements that insert zero or
+ <literal>NULL</literal> values into an
+ <literal>AUTO_INCREMENT</literal> column.
+ </para>
+ </listitem>
- <para>
- For example:
- </para>
+ <listitem>
+ <para>
+ <literal role="stmt">DELETE</literal> if you are
+ deleting rows from a table that has foreign keys with
+ <literal>ON DELETE CASCADE</literal> properties.
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ <literal role="stmt" condition="replace">REPLACE ...
+ SELECT</literal>, <literal>INSERT IGNORE ...
+ SELECT</literal> if you have duplicate key values in the
+ inserted data.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="stmt" condition="create-table-select">CREATE
- TABLE ... SELECT</literal> or
- <literal role="stmt" condition="insert-select">INSERT
- ... SELECT</literal> statements that insert zero or
- <literal>NULL</literal> values into an
- <literal>AUTO_INCREMENT</literal> column.
- </para>
- </listitem>
+ </itemizedlist>
- <listitem>
- <para>
- <literal role="stmt">DELETE</literal> if you are
- deleting rows from a table that has foreign keys with
- <literal>ON DELETE CASCADE</literal> properties.
- </para>
- </listitem>
+ <para>
+ <emphasis role="bold">If and only if the preceding queries
+ have no <literal>ORDER BY</literal> clause guaranteeing a
+ deterministic order</emphasis>.
+ </para>
- <listitem>
- <para>
- <literal role="stmt" condition="replace">REPLACE ...
- SELECT</literal>, <literal>INSERT IGNORE ...
- SELECT</literal> if you have duplicate key values in
- the inserted data.
- </para>
- </listitem>
+ <para>
+ For example, for
+ <literal role="stmt" condition="insert-select">INSERT ...
+ SELECT</literal> with no <literal>ORDER BY</literal>, the
+ <literal role="stmt">SELECT</literal> may return rows in a
+ different order (which results in a row having different
+ ranks, hence getting a different number in the
+ <literal>AUTO_INCREMENT</literal> column), depending on the
+ choices made by the optimizers on the master and slave.
+ </para>
- </itemizedlist>
+ <para>
+ A query is optimized differently on the master and slave
+ only if:
+ </para>
- <para>
- <emphasis role="bold">If and only if the preceding queries
- have no <literal>ORDER BY</literal> clause guaranteeing a
- deterministic order</emphasis>.
- </para>
+ <itemizedlist>
- <para>
- For example, for
- <literal role="stmt" condition="insert-select">INSERT ...
- SELECT</literal> with no <literal>ORDER BY</literal>, the
- <literal role="stmt">SELECT</literal> may return rows in a
- different order (which results in a row having different
- ranks, hence getting a different number in the
- <literal>AUTO_INCREMENT</literal> column), depending on
- the choices made by the optimizers on the master and
- slave.
- </para>
+ <listitem>
+ <para>
+ The table is stored using a different storage engine on
+ the master than on the slave. (It is possible to use
+ different storage engines on the master and slave. For
+ example, you can use <literal>InnoDB</literal> on the
+ master, but <literal>MyISAM</literal> on the slave if
+ the slave has less available disk space.)
+ </para>
+ </listitem>
- <para>
- A query is optimized differently on the master and slave
- only if:
- </para>
+ <listitem>
+ <para>
+ MySQL buffer sizes
+ (<literal role="sysvar">key_buffer_size</literal>, and
+ so on) are different on the master and slave.
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ The master and slave run different MySQL versions, and
+ the optimizer code differs between these versions.
+ </para>
+ </listitem>
- <listitem>
- <para>
- The table is stored using a different storage engine
- on the master than on the slave. (It is possible to
- use different storage engines on the master and slave.
- For example, you can use <literal>InnoDB</literal> on
- the master, but <literal>MyISAM</literal> on the slave
- if the slave has less available disk space.)
- </para>
- </listitem>
+ </itemizedlist>
- <listitem>
- <para>
- MySQL buffer sizes
- (<literal role="sysvar">key_buffer_size</literal>, and
- so on) are different on the master and slave.
- </para>
- </listitem>
+ <para>
+ This problem may also affect database restoration using
+ <command>mysqlbinlog|mysql</command>.
+ </para>
- <listitem>
- <para>
- The master and slave run different MySQL versions, and
- the optimizer code differs between these versions.
- </para>
- </listitem>
+ <para>
+ The easiest way to avoid this problem is to add an
+ <literal>ORDER BY</literal> clause to the aforementioned
+ nondeterministic queries to ensure that the rows are always
+ stored or modified in the same order.
+ </para>
- </itemizedlist>
+ <para>
+ In future MySQL versions, we will automatically add an
+ <literal>ORDER BY</literal> clause when needed.
+ </para>
+ </listitem>
- <para>
- This problem may also affect database restoration using
- <command>mysqlbinlog|mysql</command>.
- </para>
+ </itemizedlist>
- <para>
- The easiest way to avoid this problem is to add an
- <literal>ORDER BY</literal> clause to the aforementioned
- nondeterministic queries to ensure that the rows are
- always stored or modified in the same order.
- </para>
+ <para>
+ The following issues are known and will be fixed in due time:
+ </para>
- <para>
- In future MySQL versions, we will automatically add an
- <literal>ORDER BY</literal> clause when needed.
- </para>
- </listitem>
+ <itemizedlist>
- </itemizedlist>
+ <listitem>
+ <para>
+ Log file names are based on the server host name (if you
+ don't specify a file name with the startup option). You have
+ to use options such as
+ <option role="mysqld">--log-bin=<replaceable>old_host_name</replaceable>-bin</option>
+ if you change your host name to something else. Another
+ option is to rename the old files to reflect your host name
+ change (if these are binary logs, you need to edit the
+ binary log index file and fix the binary log file names
+ there as well). See <xref linkend="server-options"/>.
+ </para>
+ </listitem>
- <para>
- The following issues are known and will be fixed in due time:
- </para>
+ <listitem>
+ <para>
+ <command>mysqlbinlog</command> does not delete temporary
+ files left after a
+ <literal role="stmt" condition="load-data">LOAD DATA
+ INFILE</literal> statement. See
+ <xref linkend="mysqlbinlog"/>.
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ <literal>RENAME</literal> doesn't work with
+ <literal>TEMPORARY</literal> tables or tables used in a
+ <literal>MERGE</literal> table.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Log file names are based on the server host name (if you
- don't specify a file name with the startup option). You
- have to use options such as
- <option role="mysqld">--log-bin=<replaceable>old_host_name</replaceable>-bin</option>
- if you change your host name to something else. Another
- option is to rename the old files to reflect your host
- name change (if these are binary logs, you need to edit
- the binary log index file and fix the binary log file
- names there as well). See
- <xref linkend="server-options"/>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Due to the way table format (<filename>.frm</filename>)
+ files are stored, you cannot use character 255
+ (<literal>CHAR(255)</literal>) in table names, column names,
+ or enumerations.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <command>mysqlbinlog</command> does not delete temporary
- files left after a
- <literal role="stmt" condition="load-data">LOAD DATA
- INFILE</literal> statement. See
- <xref linkend="mysqlbinlog"/>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ When using <literal>SET CHARACTER SET</literal>, you can't
+ use translated characters in database, table, and column
+ names.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>RENAME</literal> doesn't work with
- <literal>TEMPORARY</literal> tables or tables used in a
- <literal>MERGE</literal> table.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ You can't use <quote><literal>_</literal></quote> or
+ <quote><literal>%</literal></quote> with
+ <literal>ESCAPE</literal> in
+ <literal role="op" condition="like">LIKE ...
+ ESCAPE</literal>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Due to the way table format (<filename>.frm</filename>)
- files are stored, you cannot use character 255
- (<literal>CHAR(255)</literal>) in table names, column
- names, or enumerations.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ You cannot build the server in another directory when using
+ MIT-pthreads. Because this requires changes to MIT-pthreads,
+ we are not likely to fix this. See
+ <xref linkend="mit-pthreads"/>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- When using <literal>SET CHARACTER SET</literal>, you can't
- use translated characters in database, table, and column
- names.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal role="type">BLOB</literal> and
+ <literal role="type">TEXT</literal> values can't reliably be
+ used in <literal>GROUP BY</literal>, <literal>ORDER
+ BY</literal> or <literal>DISTINCT</literal>. Only the first
+ <literal role="sysvar">max_sort_length</literal> bytes are
+ used when comparing <literal role="type">BLOB</literal>
+ values in these cases. The default value of
+ <literal role="sysvar">max_sort_length</literal> is 1024 and
+ can be changed at server startup time or at runtime.
+ </para>
+ </listitem>
- <listitem>
- <para>
- You can't use <quote><literal>_</literal></quote> or
- <quote><literal>%</literal></quote> with
- <literal>ESCAPE</literal> in
- <literal role="op" condition="like">LIKE ...
- ESCAPE</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Numeric calculations are done with
+ <literal role="type">BIGINT</literal> or
+ <literal role="type">DOUBLE</literal> (both are normally 64
+ bits long). Which precision you get depends on the function.
+ The general rule is that bit functions are performed with
+ <literal role="type">BIGINT</literal> precision,
+ <literal role="func">IF()</literal> and
+ <literal role="func">ELT()</literal> with
+ <literal role="type">BIGINT</literal> or
+ <literal role="type">DOUBLE</literal> precision, and the
+ rest with <literal role="type">DOUBLE</literal> precision.
+ You should try to avoid using unsigned long long values if
+ they resolve to be larger than 63 bits (9223372036854775807)
+ for anything other than bit fields.
+ </para>
+ </listitem>
- <listitem>
- <para>
- You cannot build the server in another directory when
- using MIT-pthreads. Because this requires changes to
- MIT-pthreads, we are not likely to fix this. See
- <xref linkend="mit-pthreads"/>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ You can have up to 255 <literal role="type">ENUM</literal>
+ and <literal role="type">SET</literal> columns in one table.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal role="type">BLOB</literal> and
- <literal role="type">TEXT</literal> values can't reliably
- be used in <literal>GROUP BY</literal>, <literal>ORDER
- BY</literal> or <literal>DISTINCT</literal>. Only the
- first <literal role="sysvar">max_sort_length</literal>
- bytes are used when comparing
- <literal role="type">BLOB</literal> values in these cases.
- The default value of
- <literal role="sysvar">max_sort_length</literal> is 1024
- and can be changed at server startup time or at runtime.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ In <literal role="func">MIN()</literal>,
+ <literal role="func">MAX()</literal>, and other aggregate
+ functions, MySQL currently compares
+ <literal role="type">ENUM</literal> and
+ <literal role="type">SET</literal> columns by their string
+ value rather than by the string's relative position in the
+ set.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Numeric calculations are done with
- <literal role="type">BIGINT</literal> or
- <literal role="type">DOUBLE</literal> (both are normally
- 64 bits long). Which precision you get depends on the
- function. The general rule is that bit functions are
- performed with <literal role="type">BIGINT</literal>
- precision, <literal role="func">IF()</literal> and
- <literal role="func">ELT()</literal> with
- <literal role="type">BIGINT</literal> or
- <literal role="type">DOUBLE</literal> precision, and the
- rest with <literal role="type">DOUBLE</literal> precision.
- You should try to avoid using unsigned long long values if
- they resolve to be larger than 63 bits
- (9223372036854775807) for anything other than bit fields.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <command>mysqld_safe</command> redirects all messages from
+ <command>mysqld</command> to the <command>mysqld</command>
+ log. One problem with this is that if you execute
+ <command>mysqladmin refresh</command> to close and reopen
+ the log, <literal>stdout</literal> and
+ <literal>stderr</literal> are still redirected to the old
+ log. If you use the general query log extensively, you
+ should edit <command>mysqld_safe</command> to log to
+ <filename><replaceable>host_name</replaceable>.err</filename>
+ instead of
+ <filename><replaceable>host_name</replaceable>.log</filename>
+ so that you can easily reclaim the space for the old log by
+ deleting it and executing <command>mysqladmin
+ refresh</command>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- You can have up to 255 <literal role="type">ENUM</literal>
- and <literal role="type">SET</literal> columns in one
- table.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ In an <literal role="stmt">UPDATE</literal> statement,
+ columns are updated from left to right. If you refer to an
+ updated column, you get the updated value instead of the
+ original value. For example, the following statement
+ increments <literal>KEY</literal> by <literal>2</literal>,
+ <emphasis role="bold">not</emphasis> <literal>1</literal>:
+ </para>
- <listitem>
- <para>
- In <literal role="func">MIN()</literal>,
- <literal role="func">MAX()</literal>, and other aggregate
- functions, MySQL currently compares
- <literal role="type">ENUM</literal> and
- <literal role="type">SET</literal> columns by their string
- value rather than by the string's relative position in the
- set.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <command>mysqld_safe</command> redirects all messages from
- <command>mysqld</command> to the <command>mysqld</command>
- log. One problem with this is that if you execute
- <command>mysqladmin refresh</command> to close and reopen
- the log, <literal>stdout</literal> and
- <literal>stderr</literal> are still redirected to the old
- log. If you use the general query log extensively, you
- should edit <command>mysqld_safe</command> to log to
- <filename><replaceable>host_name</replaceable>.err</filename>
- instead of
- <filename><replaceable>host_name</replaceable>.log</filename>
- so that you can easily reclaim the space for the old log
- by deleting it and executing <command>mysqladmin
- refresh</command>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- In an <literal role="stmt">UPDATE</literal> statement,
- columns are updated from left to right. If you refer to an
- updated column, you get the updated value instead of the
- original value. For example, the following statement
- increments <literal>KEY</literal> by <literal>2</literal>,
- <emphasis role="bold">not</emphasis> <literal>1</literal>:
- </para>
-
<programlisting>
mysql> <userinput>UPDATE <replaceable>tbl_name</replaceable> SET KEY=KEY+1,KEY=KEY+1;</userinput>
</programlisting>
- </listitem>
+ </listitem>
- <listitem>
- <para>
- You can refer to multiple temporary tables in the same
- query, but you cannot refer to any given temporary table
- more than once. For example, the following doesn't work:
- </para>
+ <listitem>
+ <para>
+ You can refer to multiple temporary tables in the same
+ query, but you cannot refer to any given temporary table
+ more than once. For example, the following doesn't work:
+ </para>
<programlisting>
mysql> <userinput>SELECT * FROM temp_table, temp_table AS t2;</userinput>
ERROR 1137: Can't reopen table: 'temp_table'
</programlisting>
- </listitem>
+ </listitem>
- <listitem>
- <para>
- The optimizer may handle <literal>DISTINCT</literal>
- differently when you are using <quote>hidden</quote>
- columns in a join than when you are not. In a join, hidden
- columns are counted as part of the result (even if they
- are not shown), whereas in normal queries, hidden columns
- don't participate in the <literal>DISTINCT</literal>
- comparison. We will probably change this in the future to
- never compare the hidden columns when executing
- <literal>DISTINCT</literal>.
- </para>
+ <listitem>
+ <para>
+ The optimizer may handle <literal>DISTINCT</literal>
+ differently when you are using <quote>hidden</quote> columns
+ in a join than when you are not. In a join, hidden columns
+ are counted as part of the result (even if they are not
+ shown), whereas in normal queries, hidden columns don't
+ participate in the <literal>DISTINCT</literal> comparison.
+ We will probably change this in the future to never compare
+ the hidden columns when executing
+ <literal>DISTINCT</literal>.
+ </para>
- <para>
- An example of this is:
- </para>
+ <para>
+ An example of this is:
+ </para>
<programlisting>
SELECT DISTINCT mp3id FROM band_downloads
WHERE userid = 9 ORDER BY id DESC;
</programlisting>
- <para>
- and
- </para>
+ <para>
+ and
+ </para>
<programlisting>
SELECT DISTINCT band_downloads.mp3id
@@ -5876,54 +5858,49 @@
ORDER BY band_downloads.id DESC;
</programlisting>
- <para>
- In the second case, using MySQL Server 3.23.x, you may get
- two identical rows in the result set (because the values
- in the hidden <literal>id</literal> column may differ).
- </para>
+ <para>
+ In the second case, using MySQL Server 3.23.x, you may get
+ two identical rows in the result set (because the values in
+ the hidden <literal>id</literal> column may differ).
+ </para>
- <para>
- Note that this happens only for queries where that do not
- have the <literal>ORDER BY</literal> columns in the
- result.
- </para>
- </listitem>
+ <para>
+ Note that this happens only for queries where that do not
+ have the <literal>ORDER BY</literal> columns in the result.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If you execute a <literal>PROCEDURE</literal> on a query
- that returns an empty set, in some cases the
- <literal>PROCEDURE</literal> does not transform the
- columns.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If you execute a <literal>PROCEDURE</literal> on a query
+ that returns an empty set, in some cases the
+ <literal>PROCEDURE</literal> does not transform the columns.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Creation of a table of type <literal>MERGE</literal>
- doesn't check whether the underlying tables are compatible
- types.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Creation of a table of type <literal>MERGE</literal> doesn't
+ check whether the underlying tables are compatible types.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If you use <literal role="stmt">ALTER TABLE</literal> to
- add a <literal>UNIQUE</literal> index to a table used in a
- <literal>MERGE</literal> table and then add a normal index
- on the <literal>MERGE</literal> table, the key order is
- different for the tables if there was an old,
- non-<literal>UNIQUE</literal> key in the table. This is
- because <literal role="stmt">ALTER TABLE</literal> puts
- <literal>UNIQUE</literal> indexes before normal indexes to
- be able to detect duplicate keys as early as possible.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If you use <literal role="stmt">ALTER TABLE</literal> to add
+ a <literal>UNIQUE</literal> index to a table used in a
+ <literal>MERGE</literal> table and then add a normal index
+ on the <literal>MERGE</literal> table, the key order is
+ different for the tables if there was an old,
+ non-<literal>UNIQUE</literal> key in the table. This is
+ because <literal role="stmt">ALTER TABLE</literal> puts
+ <literal>UNIQUE</literal> indexes before normal indexes to
+ be able to detect duplicate keys as early as possible.
+ </para>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- </section>
-
</section>
</section>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r22946 - in trunk: . refman-4.1 refman-5.0 refman-5.1 refman-5.5 refman-5.6 refman-6.0 | paul.dubois | 1 Oct |
