41 Changes in MySQL 5.1.39 (2009-09-04)

Bugs Fixed

Performance:
For MyISAM tables with
bulk_insert_buffer_size values
larger than 256KB, the performance of bulk insert operations
such as multiple-row INSERT and
INSERT ...
SELECT operations has been improved greatly when up to
a hundred rows are inserted at the same time.
(Bug #44723)

Partitioning:
An INSERT ...
SELECT statement on an empty partition of a
partitioned table failed with ERROR 1030 (HY000): Got
error 124 from storage engine. This issue also
caused queries run against a partitioned table while a
LOAD DATA CONCURRENT
INFILE statement was in progress to fail with the same
error.
(Bug #46639)

References: See also Bug #35845, Bug #44657, Bug #45840.

Partitioning:
A partitioned table having a
TIMESTAMP column with a default
value of CURRENT_TIMESTAMP and this column
was not defined using an ON UPDATE option, an
ALTER TABLE ...
REORGANIZE PARTITION statement on the table caused the
TIMESTAMP column value to be set
to CURRENT_TIMESTAMP regardless.
(Bug #46478)

Partitioning:
Partition pruning did not always work correctly when the
table's partitioning key used the
TO_DAYS() function.
(Bug #46362)

Partitioning:
Attempting to access a partitioned table when partitioning
support was disabled in a MySQL server binary that had been
compiled with partitioning support caused the server to crash.
(Bug #39893)

Partitioning:
The use of TO_DAYS() in the
partitioning expression led to selection failures when the
column having the date value contained invalid dates. This
occurred because the function returns NULL in
such cases, and the partition containing NULL values was pruned
away. For example, this problem occurred if
'2001-02-00' was inserted into a
DATE column of such a table, and
a subsequent query on this table used WHERE
date_col <
'2001-02-00'—while
'2001-01-01' is less than
'2001-02-00',
TO_DAYS('2001-02-00') evaluates as
NULL, and so the row containing
'2001-01-01' was not returned. Now, for
tables using RANGE or LIST
partitioning and having TO_DAYS()
in the partitioning expression, the NULL
partition is also scanned instead of being ignored.

The fix for this issue also corrects misbehavior such that a
query of the form SELECT * FROM
table WHERE
date_col <
date_val on a table
partitioned by RANGE or
LIST was handled as though the server SQL
mode included
ALLOW_INVALID_DATES even if
this was not actually part of the server SQL mode at the time
the query was issued.
(Bug #20577)

References: See also Bug #18198, Bug #32021, Bug #46362.

Replication:
Performing a multi-row update of the
AUTO_INCREMENT column of a transactional
table could result in an inconsistency between master and slave
when there was a trigger on the transactional table that updated
a nontransactional table. When such an update failed on the
master, no rows were updated on the master, but some rows could
(erroneously) be updated on the slave.
(Bug #46864)

Replication:
When using the
--replicate-rewrite-db option and
the database referenced by this option on the master was the
current database when the connection to the slave was closed,
any temporary tables existing in this database were not properly
dropped.
(Bug #46861)

Replication:
When a statement that changed both transactional and
nontransactional tables failed, the transactional changes were
automatically rolled back on the master but the slave ignored
the error and did not roll them back, thus leading to
inconsistencies between master and slave.

This issue is fixed by automatically rolling back a statement
that fails on the slave; however, the transaction is not rolled
back unless a corresponding
ROLLBACK
statement is found in the relay log file.
(Bug #46130)

References: See also Bug #33864.

Replication:
When slave_transaction_retries
is set, a statement that replicates, but is then rolled back due
to a deadlock on the slave, should be retried. However, in
certain cases, replication was stopped with error 1213
(Deadlock found when trying to get lock; try
restarting transaction) instead, even when this
variable was set.
(Bug #45694)

Replication:
When using statement-based replication, database-level character
sets were not always honored by the replication SQL thread. This
could cause data inserted on the master using
LOAD DATA to be replicated using
the wrong character set.

Replication:
Creating a scheduled event whose DEFINER
clause was either set to
CURRENT_USER or not set
explicitly caused the master and the slave to become
inconsistent. This issue stems from the fact that, in both
cases, the DEFINER is set to the
CURRENT_USER of the current
thread. (On the master, the
CURRENT_USER is the
mysqld user; on the slave, the
CURRENT_USER is empty.)

If the definer is not set explicitly, a
DEFINER clause using the value of
CURRENT_USER is added to the
CREATE EVENT statement before
it is written to the binary log.

(Bug #44331)

References: See also Bug #42217.

Replication:
When using the statement-based logging format, the only possible
safe combination of transactional and nontransactional
statements within the same transaction is to perform any updates
on nontransactional tables (such as
MyISAM tables) first, before
updating any transactional tables (such as those using the
InnoDB storage engine). This is due
to the fact that, although a modification made to a
nontransactional table is immediately visible to other
connections, the update is not immediately written to the binary
log, which can lead to inconsistencies between master and slave.
(Other combinations may hide a causal dependency, thus making it
impossible to write statements updating nontransactional tables
to the binary log in the correct order.)

However, in some cases, this situation was not handled properly,
and the determination whether a given statement was safe or not
under these conditions was not always correct. In particular, a
multi-table update that affected both transactional and
nontransactional tables or a statement modifying data in a
nontransactional table having a trigger that operated on a
transactional table (or the reverse) was not determined to be
unsafe when it should have been.

With this fix, the following determinations regarding
replication safety are made when combining updates to
transactional and nontransactional tables within the same
transaction in statement-based logging mode:

Any statement modifying data in a nontransactional table
within a given transaction is considered safe if it is
issued prior to any data modification statement accessing a
transactional table within the same transaction.

A statement that updates transactional tables only is always
considered safe.

A statement affecting both transactional and
nontransactional tables within a transaction is always
considered unsafe. It is not necessary that both tables be
modified for this to be true; for example, a statement such
as INSERT INTO
innodb_table SELECT * FROM
myisam_table is also
considered unsafe.

Note

The current fix is valid only when using statement-based
logging mode; we plan to address similar issues occurring when
using the MIXED or ROW
format in a future MySQL release.

(Bug #28976)

Stack overflow checking did not account for the size of the
structure stored in the heap.
(Bug #46807)

The server could crash for queries with the following elements:
1. An “impossible where” in the outermost
SELECT; 2. An aggregate in the outermost
SELECT; 3. A correlated subquery with a
WHERE clause that includes an outer field
reference as a top-level WHERE sargable
predicate;
(Bug #46749)

CREATE TABLE ...
SELECT could cause assertion failure if a table
already existed with the same name and contained an
AUTO_INCREMENT column.
(Bug #46616)

In queries for which the loose index scan access method was
chosen, using a condition of the form
col_name rather than the equivalent
col_name <>
0 caused an assertion failure.
(Bug #46607)

TRUNCATE TABLE for a table that
was opened with HANDLER did not
close the handler and left it in an inconsistent state that
could lead to a server crash. Now TRUNCATE
TABLE for a table closes all open handlers for the
table.
(Bug #46456)

A query containing a subquery in the FROM
clause and PROCEDURE ANALYSE() caused a
server crash.
(Bug #46184)

References: See also Bug #48293.

Killing a query that was performing a sort could result in a
memory leak.
(Bug #45962)

Truncation of DECIMAL values
could lead to assertion failures; for example, when deducing the
type of a table column from a literal
DECIMAL value.
(Bug #45261)

References: See also Bug #48370.

A buffer overflow could occur during handling of IS
NULL ranges.
(Bug #37044)

mysqladmin --wait ping crashed on Windows
systems.
(Bug #35132)

Installation of MySQL on Windows failed to set the correct
location for the character set files, which could lead to
mysqld and mysql failing
to initialize properly.
(Bug #17270)