Known Issues in MySQL
2976
001207 22:07:56 bdb: log_flush: LSN past current end-of-log
• Do not execute
ALTER TABLE
on a
BDB
table on which you are running multiple-statement
transactions until all those transactions complete. (The transaction might be ignored.)
•
ANALYZE TABLE
,
OPTIMIZE TABLE
, and
REPAIR TABLE
may cause problems on tables for which
you are using
INSERT DELAYED
.
• Performing
LOCK TABLE ...
and
FLUSH TABLES ...
does not guarantee that there isn't a half-
finished transaction in progress on the table.
•
BDB
tables are relatively slow to open. If you have many
BDB
tables in a database, it takes a long
time to use the
mysql
client on the database if you are not using the
-A
option or if you are using
rehash
. This is especially noticeable when you have a large table cache.
• 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.
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).
For example:
•
CREATE TABLE ... SELECT
or
INSERT ... SELECT
statements that insert zero or
NULL
values into an
AUTO_INCREMENT
column.
•
DELETE
if you are deleting rows from a table that has foreign keys with
ON DELETE CASCADE
properties.
•
REPLACE ... SELECT
,
INSERT IGNORE ... SELECT
if you have duplicate key values in the
inserted data.
If and only if the preceding queries have no
ORDER BY
clause guaranteeing a deterministic
order.
For example, for
INSERT ... SELECT
with no
ORDER BY
, the
SELECT
may return rows in a
different order (which results in a row having different ranks, hence getting a different number in the
AUTO_INCREMENT
column), depending on the choices made by the optimizers on the master and
slave.
A query is optimized differently on the master and slave only if:
• 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
InnoDB
on
the master, but
MyISAM
on the slave if the slave has less available disk space.)
• MySQL buffer sizes (
key_buffer_size
[458]
, and so on) are different on the master and slave.
• The master and slave run different MySQL versions, and the optimizer code differs between these
versions.
This problem may also affect database restoration using
mysqlbinlog|mysql
.
The easiest way to avoid this problem is to add an
ORDER BY
clause to the aforementioned
nondeterministic queries to ensure that the rows are always stored or modified in the same order.
• Log file names are based on the server host name if you do not specify a file name with the startup
option. To retain the same log file names if you change your host name to something else, you must
explicitly use options such as
--log-bin=old_host_name-bin
[1467]
. See
Section 5.1.3, “Server
Command Options”
. Alternatively, rename the old files to reflect your host name change. If these are
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
