InnoDB Notes
This release includes InnoDB 1.0.6. This
version is considered of Release Candidate (RC) quality.
IPv6 Support
MySQL Server now can accept TCP/IP connections from clients connecting over IPv6. See IPv6 Support. For example, this command connects over IPv6 to the MySQL server on the local host:
shell> mysql -h ::1
To use this capability, two things must be true:
Your system must be configured to support IPv6.
The default MySQL server configuration permits only IPv4
connections, so the server must be configured for IPv6
connections. To permit IPv6 connections in addition to or
instead of IPv4 connections, start the server with an
appropriate --bind-address
option.
MySQL account names permit IPv6 addresses to enable DBAs to
specify privileges for clients that connect to the server over
IPv6. See Specifying Account Names. IPv6 addresses can be
specified in account names in statements such as
CREATE USER,
GRANT, and
REVOKE. For example:
mysql>CREATE USER 'bill'@'::1' IDENTIFIED BY 'secret';mysql>GRANT SELECT ON mydb.* TO 'bill'@'::1';
The default set of accounts created during MySQL installation
now include an account for 'root'@'::1'. See
Securing the Initial MySQL Accounts. This account can be used
to make connections as root if the server is
bound to ::1 and accepts only local IPv6
connections.
(Bug #8836)
Performance Schema Notes
MySQL Server now includes the Performance Schema, a feature for
monitoring server execution at a low level. The implementation
uses the PERFORMANCE_SCHEMA storage
engine and the performance_schema database.
The Performance Schema focuses primarily on performance data.
This differs from INFORMATION_SCHEMA, which
serves for inspection of metadata. For more information, see
MySQL Performance Schema.
Performance Schema support is included in binary MySQL
distributions but is disabled by default. To enable it, start
the server with the
--performance_schema option.
To create the performance_schema database if
you are upgrading from an earlier release, run
mysql_upgrade and restart the server. See
mysql_upgrade — Check and Upgrade MySQL Tables.
Functionality Added or Changed
Performance: The performance of internal functions that trim multiple spaces from strings when comparing them has been improved. (Bug #14637)
Incompatible Change: The following obsolete constructs have been removed. Where alternatives are shown, applications should be updated to use them.
The log_bin_trust_routine_creators system
variable (use
log_bin_trust_function_creators).
The myisam_max_extra_sort_file_size
system variable.
The record_buffer system variable (use
read_buffer_size).
The sql_log_update system variable.
The table_lock_wait_timeout system
variable.
The table_type system variable (use
storage_engine).
The FRAC_SECOND modifier for the
TIMESTAMPADD() function (use
MICROSECOND).
The TYPE table option to specify the
storage engine for CREATE
TABLE or ALTER
TABLE (use ENGINE).
The SHOW TABLE TYPES SQL statement (use
SHOW ENGINES).
The SHOW INNODB STATUS and SHOW
MUTEX STATUS SQL statements (use
SHOW ENGINE
INNODB STATUS
SHOW ENGINE
INNODB MUTEX).
The SHOW PLUGIN SQL statement (use
SHOW PLUGINS).
The LOAD TABLE ... FROM MASTER and
LOAD DATA FROM MASTER SQL statements (use
mysqldump or
mysqlhotcopy to dump tables and
mysql to reload dump files).
The BACKUP TABLE and RESTORE
TABLE SQL statements (use
mysqldump or
mysqlhotcopy to dump tables and
mysql to reload dump files).
TIMESTAMP(
data type: The ability to specify a display width of
N)N (use without
N).
The --default-character-set and
--default-collation server options (use
--character-set-server and
--collation-server).
The --delay-key-write-for-all-tables server
option (use
--delay-key-write=ALL).
The --enable-locking and
--skip-locking server options (use
--external-locking and
--skip-external-locking).
The --log-bin-trust-routine-creators server
option (use
--log-bin-trust-function-creators).
The --log-long-format server option.
The --log-update server option.
The --master-
server options to set replication parameters (use the
xxxCHANGE MASTER TO statement
instead): --master-host,
--master-user, --master-password
, --master-port,
--master-connect-retry,
--master-ssl,
--master-ssl-ca,
--master-ssl-capath,
--master-ssl-cert,
--master-ssl-cipher,
--master-ssl-key.
The --safe-show-database server option.
The --skip-symlink and
--use-symbolic-links server options (use
--skip-symbolic-links
and --symbolic-links).
The --sql-bin-update-same server option.
The --warnings server option (use
--log-warnings).
The --no-named-commands option for
mysql (use
--skip-named-commands).
The --no-pager option for
mysql (use
--skip-pager).
The --no-tee option for
mysql (use --skip-tee).
The --position option for
mysqlbinlog (use
--start-position).
The --all option for
mysqldump (use
--create-options).
The --first-slave option for
mysqldump (use
--lock-all-tables).
The --config-file option for
mysqld_multi (use
--defaults-extra-file).
The
--set-variable=
and var_name=value-O
general-purpose options for setting program variables (use
var_name=value--).
var_name=value
(Bug #48048)
References: See also Bug #47974, Bug #56408.
Incompatible Change:
CREATE VIEW and
DROP VIEW now are prohibited
while a LOCK TABLES statement is
in effect.
(Bug #56571)
Incompatible Change:
Several columns were added to the
INFORMATION_SCHEMA.ROUTINES table
to provide information about the RETURNS
clause data type for stored functions:
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH,
NUMERIC_PRECISION,
NUMERIC_SCALE,
CHARACTER_SET_NAME, and
COLLATION_NAME.
This change produces an incompatibility for applications that
depend on column order in the
ROUTINES table because the new
columns appear between the ROUTINE_TYPE and
DTD_IDENTIFIER columns. Such applications may
need to be adjusted to account for the new columns.
Incompatible Change:
Aliases for wildcards (as in SELECT t.* AS 'alias' FROM
t) are no longer accepted and result in an error.
Previously, such aliases were ignored silently.
(Bug #27249)
Incompatible Change: The Unicode implementation has been extended to provide support for supplementary characters that lie outside the Basic Multilingual Plane (BMP). Noteworthy features:
utf16 and utf32
character sets have been added. These correspond to the
UTF-16 and UTF-32 encodings of the Unicode character set,
and they both support supplementary characters.
The utf8mb4 character set has been added.
This is similar to utf8, but its encoding
allows up to four bytes per character to enable support for
supplementary characters.
The ucs2 character set is essentially
unchanged except for the inclusion of some newer BMP
characters.
In most respects, upgrading to MySQL 5.5 should present few problems with regard to Unicode usage, although there are some potential areas of incompatibility. These are the primary areas of concern:
For the variable-length character data types
(VARCHAR and the
TEXT types), the maximum
length in characters is less for utf8mb4
columns than for utf8 columns.
For all character data types
(CHAR,
VARCHAR, and the
TEXT types), the maximum
number of characters that can be indexed is less for
utf8mb4 columns than for
utf8 columns.
Consequently, if you want to upgrade tables from
utf8 to utf8mb4 to take
advantage of supplementary-character support, it may be
necessary to change some column or index definitions.
For additional details about the new Unicode character sets and potential incompatibilities, see Unicode Support, and Upgrading from Previous to Current Unicode Support.
Incompatible Change:
The server now includes dtoa, a library for
conversion between strings and numbers by David M. Gay. In
MySQL, this library provides the basis for improved conversion
between string or DECIMAL values
and approximate-value
(FLOAT/DOUBLE)
numbers:
Consistent conversion results across platforms, which eliminates, for example, Unix versus Windows conversion differences.
Accurate representation of values in cases where results previously did not provide sufficient precision, such as for values close to IEEE limits.
Conversion of numbers to string format with the best
possible precision. The precision of dtoa
is always the same or better than that of the standard C
library functions.
Because the conversions produced by this library differ in some cases from previous results, the potential exists for incompatibilities in applications that rely on previous results. For example, applications that depend on a specific exact result from previous conversions might need adjustment to accommodate additional precision.
For additional information about the properties of
dtoa conversions, see
Type Conversion in Expression Evaluation.
References: See also Bug #12860, Bug #21497, Bug #26788, Bug #24541, Bug #34015.
Incompatible Change: Several changes were made to processing of server system variables and command-line options to make their treatment more consistent.
General changes:
The help message text displayed by mysqld --verbose
--help now consistently uses dashes to show the
names of options and system variables that can be set at
server startup. Previously, the message used both dashes and
underscores (generally with dashes for options and
underscores for system variables). For example, the help
message now displays --log-output and
--general-log, whereas previously it
displayed --log-output and
--general_log.
This is a display-only change. The permissible syntax for setting options and variables remains unchanged:
At server startup, you can specify options and variables on the command line or in option files using either dashes or underscores.
For those system variables that can be set at runtime
(for example, using
SET),
you must specify them using underscores.
There are fewer session-only system variables. These
variables now have a global value:
autocommit,
foreign_key_checks,
profiling,
sql_auto_is_null,
sql_big_selects,
sql_buffer_result,
sql_log_bin,
sql_log_off,
sql_notes,
sql_quote_show_create,
sql_safe_updates,
sql_warnings,
unique_checks.
For those variables, you can now set the global value to change the value from which the session value is initialized for new sessions.
The following list shows the variables that remain
session-only. They apply only in the context of a specific
session so that a global value is of no use:
debug_sync,
error_count,
identity,
insert_id,
last_insert_id,
pseudo_thread_id,
rand_seed1,
rand_seed2,
timestamp,
warning_count.
All system variables are accessible at runtime using
@@ syntax
(@@GLOBAL.,
var_name@@SESSION.,
var_name@@).
Previously, this syntax produced an error for some
variables.
var_name
All system variables are included as appropriate in the
output from
SHOW
{GLOBAL, SESSION} VARIABLES and the
INFORMATION_SCHEMA.GLOBAL_VARIABLES
and
INFORMATION_SCHEMA.SESSION_VARIABLES
tables. Previously, some variables were not displayed.
“As appropriate” in the preceding item means
that SHOW
GLOBAL VARIABLES and
INFORMATION_SCHEMA.GLOBAL_VARIABLES
no longer include session-only system variables. Previously,
these included the global value of a variable if it had one,
and the session value if not.
(SHOW
SESSION VARIABLES still includes global-only
variables.)
The server now enforces type checking for assignments to system variables, so it is more consistent and strict about rejecting invalid values.
For attempts to assign a negative value to an unsigned system variable, the server truncates the value to the minimum permitted value. Previously, there was sometimes wraparound to a large positive value.
Some system variables (typically those that control memory
or disk allocation) are permitted to take only values that
are a multiple of a given block size, and assigning a value
not a block size multiple causes truncation to the nearest
multiple. (For example,
net_buffer_length must be a
multiple of 1024. Assigning 16384 results in a value of
16384, whereas assigning 16383 results in a value of 15360.)
A warning now occurs when adjustment of the specified value
takes place. Previously, adjustment was silent.
More system variables can be assigned the value
DEFAULT to set them to their default
value. Previously, this syntax produced an error in some
cases.
All variables that have a SET
data type value can be set to an integer value that is
treated like a bit mask. Previously, this did not work for
some SET-type variables.
The default value for several system variables no longer differs between 32-bit and 64-bit builds. Previously, the values differed by about 100 bytes for some variables.
There are no longer any write-only system variables. For
example, SELECT
@@rand_seed1 returns 0, not Variable
'rand_seed1' can only be set, not read.
Variable-specific changes:
The concurrent_insert
system variable now is handled as an enumeration with the
permissible values NEVER,
AUTO, and ALWAYS. The
corresponding integer values 0, 1, and 2 are still
recognized.
The completion_type system
variable now is handled as an enumeration with the
permissible values NO_CHAIN,
CHAIN, and RELEASE.
The corresponding integer values 0, 1, and 2 are still
recognized.
For concurrent_insert and
completion_type, the string
form of the value is displayed by SHOW
VARIABLES and
SELECT
@@.
var_name
The unused rpl_recovery_rank system
variable is deprecated.
The storage_engine system
variable is deprecated in favor of the new system variable
default_storage_engine.
This enables pairing of the
--default-storage-engine
command-line option with a system variable of a more closely
corresponding name.
The --myisam-recover option
is renamed to
--myisam-recover-options to
pair better with the name of the
myisam_recover_options
system variable. The old option name still works because it
is recognized as an unambiguous prefix of the new name.
(Option prefix recognition occurs as described in
Specifying Program Options.)
--myisam-recover-options has
a new permissible value OFF.
Attempts to drop the default key cache produce an error. Previously, it produced only a warning and status of success even though the attempt failed.
References: See also Bug #34437, Bug #34635, Bug #11747961, Bug #34829, Bug #34878, Bug #25430.
Incompatible Change:
Implicit conversion of a number or temporal value to string now
produces a value that has a character set and collation
determined by the
character_set_connection and
collation_connection system
variables. (These variables commonly are set with
SET
NAMES. For information about connection character
sets, see Connection Character Sets and Collations.)
This change means that such a conversion results in a character
(nonbinary) string (a CHAR,
VARCHAR, or
LONGTEXT value), except when the
connection character set is set to binary. In
that case, the conversion result is a binary string (a
BINARY,
VARBINARY, or
LONGBLOB value).
Previously, an implicit conversion always produced a binary
string, regardless of the connection character set. Such
implicit conversions to string typically occur for functions
that are passed numeric or temporal values when string values
are more usual, and thus could have effects beyond the type of
the converted value. Consider the expression
CONCAT(1, 'abc'). The numeric
argument 1 was converted to the binary string
'1' and the concatenation of that value with
the nonbinary string 'abc' produced the
binary string '1abc'.
This change in conversion behavior affects several functions that expect string arguments because a numeric or temporal argument converted to a string now results in a character rather than binary string argument:
String functions: CONCAT(),
CONCAT_WS(),
ELT(),
EXPORT_SET(),
INSERT(),
LCASE(),
LEFT(),
LOWER(),
LPAD(),
LTRIM(),
MID(),
QUOTE(),
REPEAT(),
REPLACE(),
REVERSE(),
RIGHT(),
RPAD(),
RTRIM(),
SOUNDEX(),
SUBSTRING(),
TRIM(),
UCASE(),
UPPER().
Date and time functions:
ADDDATE(),
ADDTIME(),
DATE_ADD(),
DATE_SUB(),
DAYNAME(),
GET_FORMAT(),
MONTHNAME(),
SUBDATE(),
SUBTIME(),
TIMESTAMPADD().
These functions remain unaffected:
CHAR() without a
USING clause still returns
VARBINARY.
Functions that previously returned utf8
strings still do so. Examples include
CHARSET() and
COLLATION().
Encryption and compression functions that expect string arguments and previously returned binary strings are affected depending on the content of the return value:
If the return value contains only ASCII characters, the
function now returns a character string with the connection
character set and collation:
MD5(),
OLD_PASSWORD(),
PASSWORD(),
SHA(),
SHA1(). The
ASTEXT() and
ASWKT()
spatial functions also fall into this category.
If the return value can contain non-ASCII characters, the
function still returns a binary string:
AES_ENCRYPT(),
COMPRESS(),
DES_ENCRYPT(),
ENCODE(),
ENCRYPT().
The INET_NTOA() return value
contains only ASCII characters, and this function now returns a
character string with the connection character set and collation
rather than a binary string.
Important Change; Replication:
RESET MASTER and
RESET SLAVE now reset the values
shown for Last_IO_Error,
Last_IO_Errno,
Last_SQL_Error, and
Last_SQL_Errno in the output of
SHOW SLAVE STATUS.
(Bug #34654)
References: See also Bug #44270.
Important Change:
The --skip-thread-priority option is now
deprecated such that the server will not change the thread
priorities by default. Giving threads different priorities might
yield marginal improvements in some platforms (where it actually
works), but it might instead cause significant degradation
depending on the thread count and number of processors. Meddling
with the thread priorities is a not a safe bet as it is very
dependent on the behavior of the CPU scheduler and system where
MySQL is being run.
(Bug #35164, Bug #37536)
Replication; Cluster Replication:
MySQL Replication now supports attribute promotion and demotion
for row-based replication between columns of different but
similar types on the master and the slave. For example, it is
possible to promote an INT column
on the master to a BIGINT column
on the slave, and to demote a
TEXT column to a
VARCHAR column.
The implementation of type demotion distinguishes between lossy
and non-lossy type conversions, and their use on the slave can
be controlled by setting the
slave_type_conversions global
server system variable.
For more information, see Row-based replication: attribute promotion and demotion. (Bug #47163, Bug #46584)
Replication: For replication based on row-based and mix-format binary logging, it is now safe to mix transactional and nontransactional statements within a transaction. The nontransactional statements are logged immediately rather than waiting until the transaction ends, ensuring that their results are logged and replicated correctly regardless of the result of the transaction.
For boolean options, the option-processing library now prints
additional information in the --help message:
If the option is enabled by default, the message says so and
indicates that the --skip form of the option
disables the option. This affects all compiled MySQL programs
that use the library.
(Bug #35224)
The use of the SQL_CACHE and
SQL_NO_CACHE options in
SELECT statements now is checked
more restrictively: 1) Previously, both options could be given
in the same statement. This is no longer true; only one can be
given. 2) Previously, these options could be given in
SELECT statements that were not
at the top-level. This is no longer true; the options are not
permitted in subqueries (including subqueries in the
FROM clause, and
SELECT statements in unions other
than the first SELECT.
(Bug #35020)
MySQL support for adding collations using LDML specifications
did not support the <i> identity rule
that indicates one character sorts identically to another. The
<i> rule now is supported. See
LDML Syntax Supported in MySQL.
(Bug #37129)
SHOW PROFILE
CPU has been ported to Windows. Thanks to Alex
Budovski for the patch.
(Bug #50057)
mysqltest has a new
--max-connections option to set a higher number
of maximum permitted server connections than the default 128.
This option can also be passed using
mysql-test-run.pl.
(Bug #51135)
mysql-test-run.pl has a new
--portbase option and a corresponding
MTR_PORT_BASE environment variable for
setting the port range, as an alternative to the existing
--build-thread option.
(Bug #50182)
mysql-test-run.pl now has a
--gprof option that runs the server through the
gprof profiler, much the same way the
currently supported --gcov option runs it
through gcov.
(Bug #49345)
mysqltest now has a
lowercase_result command that converts the
output of the next statement to lowercase. This is useful for
test cases where the lettercase may vary between platforms.
(Bug #48863)
mysqltest now has a
remove_files_wildcard command that removes
files matching a pattern from a directory.
(Bug #39774)
The maximum length of table comments was extended from 60 to 2048 characters. The maximum length of column comments was extended from 255 to 1024 characters. Index definitions now can include a comment of up to 1024 characters.
Added the PARAMETERS table to
INFORMATION_SCHEMA. The
PARAMETERS table provides
information about stored procedure and function parameters, and
about return values for stored functions.
The TABLESPACES table has been
added to INFORMATION_SCHEMA for tracking
tablespace details.
When the server detects MyISAM table
corruption, it now writes additional information to the error
log, such as the name and line number of the source file, and
the list of threads accessing the table. Example: Got
an error from thread_id=1, mi_dynrec.c:368. This is
useful information to include in bug reports.
Three options were added to mysqldump make it easier to generate a dump from a slave server:
--dump-slave is similar to
--master-data, but the
CHANGE MASTER TO statement
contains binary log coordinates for the slave's master host,
not the slave itself.
--apply-slave-statements
causes STOP SLAVE and
START SLAVE statements to be
added before the CHANGE MASTER
TO statement and at the end of the output,
respectively.
--include-master-host-port
causes the CHANGE MASTER TO
statement to include MASTER_PORT and
MASTER_HOST options for the slave's
master.
(Bug #8368)
The mysql client now has an
--auto-vertical-output option,
which causes result sets to be displayed vertically if they are
too wide for the current window, and uses normal tabular format
otherwise. (This applies to statements terminated by
; or \G.)
(Bug #26780)
Previously, prepared CALL
statements could be used through the C API only for stored
procedures that produce at most one result set, and applications
could not use placeholders for OUT or
INOUT parameters. For prepared
CALL statements used using
PREPARE and
EXECUTE, placeholders could not
be used for OUT or INOUT
parameters.
For the C API, prepared CALL
support now is expanded in the following ways:
A stored procedure can produce any number of result sets. The number of columns and the data types of the columns need not be the same for all result sets.
The final values of OUT and
INOUT parameters are available to the
calling application after the procedure returns. These
parameters are returned as an extra single-row result set
following any result sets produced by the procedure itself.
The row contains the values of the OUT
and INOUT parameters in the order in
which they are declared in the procedure parameter list.
A new C API function,
mysql_stmt_next_result(), is
available for processing stored procedure results. See
C API Support for Prepared CALL Statements.
The CLIENT_MULTI_RESULTS flag now is
enabled by default. It no longer needs to be enabled when
you call
mysql_real_connect(). (This
flag is necessary for executing stored procedures because
they can produce multiple result sets.)
For PREPARE and
EXECUTE, placeholder support for
OUT and INOUT parameters
is now available. See CALL Syntax.
(Bug #11638, Bug #17898)
mysqladmin now permits the password value to
be omitted following the password command. In
this case, mysqladmin prompts for the
password value, which enables you to avoid specifying the
password on the command line. Omitting the password value should
be done only if password is the final command
on the mysqladmin command line. Otherwise,
the next argument is taken as the password.
(Bug #5724)
TRUNCATE TABLE now is permitted
for a table for which a WRITE lock has been
acquired with LOCK TABLES.
(Bug #20667)
References: See also Bug #46452.
Some conversions between Japanese character sets are more efficient.
FLUSH LOGS now
takes an optional log_type value so
that FLUSH
can be used
to flush only a specified log type. These
log_type LOGSlog_type options are permitted:
BINARY closes and reopens the binary log
files.
ENGINE closes and reopens any flushable
logs for installed storage engines.
ERROR closes and reopens the error log
file.
GENERAL closes and reopens the general
query log file.
RELAY closes and reopens the relay log
files.
SLOW closes and reopens the slow query
log file.
Thanks to Eric Bergen for the patch to implement this feature. (Bug #14104)
The server now provides a pluggable audit interface that enables information about server operations to be reported to interested parties. Audit plugins may register with the audit interface to receive notification about server operations. When an auditable event occurs within the server, the server determines whether notification is needed. For each registered audit plugin, the server checks the event against those event classes in which the plugin is interested and passes the event to the plugin if there is a match. For more information, see Audit Plugins.
The optimizer_switch system
variable now has an engine_condition_pushdown
flag to control whether storage engine condition pushdown
optimization is used. As a consequence, the
engine_condition_pushdown
system variable now is deprecated.
Code that produces query IDs and updates the value of the
Threads_running status
variable no longer acquires a global lock that also protects the
list of all connections. Instead, it relies on atomic increment
and decrement instructions. This improves scalability and to a
certain extent alleviates the problem described in Bug
#11751904.
References: See also Bug #42930.
Bugs Fixed
Security Fix:
The server crashed if an account with the
CREATE ROUTINE privilege but not
the EXECUTE privilege attempted
to create a stored procedure.
(Bug #44798)
Security Enhancement:
When the DATA DIRECTORY or INDEX
DIRECTORY clause of a CREATE
TABLE statement referred to a subdirectory of the data
directory through a symlinked component of the data directory
path, it was accepted, when for security reasons it should be
rejected.
(Bug #39277)
Performance; Replication:
When writing events to the binary log, transactional events
(that is, events that operate on transactional tables) are
written to a thread-specific transaction cache, which is then
written to the binary log on commit. To handle nontransactional
events, there was a lock taken on the binary log (when entering
the function MYSQL_BIN_LOG::write()), even
when the event was written to the transaction cache instead of
the binary log, causing a major bottleneck in replication
performance.
(Bug #42757)
Incompatible Change; Replication:
The binlog_format system
variable can no longer be set inside a transaction. In other
words, the binary logging format can no longer be changed while
a transaction is in progress.
(Bug #47863)
Incompatible Change; Replication:
Concurrent statements using a stored function and
DROP FUNCTION for that function
could break statement-based replication.
DDL statements for stored procedures and functions are now
prohibited while a LOCK TABLES
statement is in effect.
(Bug #30977)
References: See also Bug #57663.
Incompatible Change:
The parser accepted illegal syntax in a FOREIGN
KEY clause:
Multiple MATCH clauses.
Multiple ON DELETE clauses.
Multiple ON UPDATE clauses.
MATCH clauses specified after ON
UPDATE or ON DELETE. In case of
multiple redundant clauses, this leads to confusion, and
implementation-dependent results.
These illegal syntaxes are now properly rejected. Existing applications that used them will require adjustment. (Bug #34455)
Incompatible Change:
The parser accepted an INTO clause in nested
SELECT statements, which is
invalid because such statements must return their results to the
outer context. This syntax is no longer permitted.
(Bug #33204)
Incompatible Change:
For application compatibility reasons, when
sql_auto_is_null is 1, MySQL
converts auto_inc_col IS
NULLauto_inc_col =
LAST_INSERT_ID()
In conjunction with this bug fix, the default value of the
sql_auto_is_null system
variable has been changed from 1 to 0, which may cause
incompatibilities with existing applications.
(Bug #41371)
Incompatible Change:
CREATE TABLE statements
(including CREATE
TABLE ... LIKE) are now prohibited whenever a
LOCK TABLES statement is in
effect.
One consequence of this change is that
CREATE TABLE ...
LIKE makes the same checks as
CREATE TABLE and does not just
copy the .frm file. This means that if the
current SQL mode is different from the mode in effect when the
original table was created, the table definition might be
considered invalid for the new mode and the statement will fail.
(Bug #42546, Bug #11751609)
Incompatible Change:
A deadlock occurred for this sequence of events: Session 1
locked a table using LOCK TABLES;
Session 2 dropped the database containing the table; Session 1
created any database.
As a consequence of this bug fix, CREATE
DATABASE is not permitted within a session that has an
active LOCK TABLES statement.
(Bug #49988)
Incompatible Change:
For debug builds, attempts to execute
RESET statements within a
transaction that had acquired metadata locks led to an assertion
failure.
As a result of this bug fix,
RESET statements now cause an
implicit commit.
(Bug #51336)
Incompatible Change:
Due to work done for Bug #989,
FLUSH TABLES is
not permitted when there is an active
LOCK TABLES ...
READ. This caused a problem with
mysqlhotcopy, which used that sequence of
statements. mysqlhotcopy now uses
FLUSH TABLES
to
flush and lock tables. If mysqlhotcopy is
used with a server older than MySQL 5.5.3 that does not support
this statement, it has a new option
tbl_list WITH READ LOCK--old_server that causes it
to use the previous statement sequence.
To provide a workaround for the restriction that
FLUSH TABLES is
no longer permitted when there is an active
LOCK TABLES ...
READ, FLUSH
TABLES has a new variant,
FLUSH TABLES
,
that enables tables to be flushed and locked in a single
operation. As a result of this change, applications that
previously used this statement sequence to lock and flush tables
will fail:
tbl_list WITH READ LOCK
LOCK TABLEStbl_listREAD; FLUSH TABLEStbl_list;
Such applications should now use this statement instead:
FLUSH TABLES tbl_list WITH READ LOCK;
(Bug #42465)
Incompatible Change:
Several changes were made to alias resolution in multiple-table
DELETE statements so that it is
no longer possible to have inconsistent or ambiguous table
aliases.
In MySQL 5.1.23, alias declarations outside the
table_references part of the
statement were disallowed for the USING
variant of multiple-table
DELETE syntax, to reduce the
possibility of ambiguous aliases that could lead to
ambiguous statements that have unexpected results such as
deleting rows from the wrong table.
Now alias declarations outside
table_references are disallowed
for all multiple-table DELETE
statements. Alias declarations are permitted only in the
table_references part.
Incorrect:
DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2;
Correct:
DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2;
Previously, for alias references in the list of tables from
which to delete rows in a multiple-table delete, the default
database is used unless one is specified explicitly. For
example, if the default database is db1,
the following statement does not work because the
unqualified alias reference a2 is
interpreted as having a database of db1:
DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id;
To correctly match an alias that refers to a table outside the default database, you must explicitly qualify the reference with the name of the proper database:
DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id;
Now alias resolution does not require qualification and alias references should not be qualified with the database name. Qualified names are interpreted as referring to tables, not aliases.
Statements containing alias constructs that are no longer permitted must be rewritten. (Bug #27525)
References: See also Bug #30234.
Incompatible Change:
DROP TABLE now is permitted only
if you have acquired a WRITE lock with
LOCK TABLES, or if you hold no
locks, or if the table is a TEMPORARY table.
Previously, if other tables were locked, you could drop a table with a read lock or no lock, which could lead to deadlocks between clients. The new stricter behavior means that some usage scenarios will fail when previously they did not. (Bug #25858)
Incompatible Change:
If a data definition language (DDL) statement occurred for a
table that was being used by another session in an active
transaction, statements could be written to the binary log in
the wrong order. For example, this could happen if DROP
TABLE occurred for a table being used in a
transaction. This is now prevented by deferring release of
metadata locks on tables used within a transaction until the
transaction ends.
This bug fix results in some incompatibilities with previous versions:
A table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.
FLUSH
TABLES is not permitted when there is an active
LOCK TABLES ...
READ. Use
FLUSH TABLES
instead. This causes a problem with
mysqlhotcopy, fixed in Bug #42465.
tbl_list WITH READ LOCK
(Bug #989, Bug #39675)
Incompatible Change:
The Locked thread state was equivalent to the
Table lock state and has been removed. It no
longer appears in SHOW
PROCESSLIST output.
(Bug #28870)
Important Change; Replication: For an engine that supported only row-based replication, replication stopped with an error when executing row events.
For information about changes in how the binary logging format is determined in relation to statement type and storage engine logging capabilities, see Mixed Binary Logging Format.
As part of the fix for this issue, the
EXAMPLE storage engine is now
changed so that it supports statement-based logging only.
Previously, it supported row-based logging only.
(Bug #39934, Bug #11749859)
Important Change:
The IPv6 loopback address ::1 was interpreted
as a hostname rather than a numeric IP address.
In addition, the IPv6-enabled server on Windows interpreted the
hostname localhost as ::1
only, which failed to match the default
'root'@'127.0.0.1' account in the
mysql.user privilege table.
As a result of this fix, a 'root'@'::1'
account is added to the mysql.user table as
one of the default accounts created during MySQL installation.
(Bug #43006)
References: See also Bug #38247, Bug #11753779, Bug #45584, Bug #45606.
InnoDB; Replication:
Column length information generated by
InnoDB did not match that generated
by MyISAM, which caused invalid
metadata to be written to the binary log when trying to
replicate BIT columns.
(Bug #49618)
InnoDB:
SHOW INNODB STATUS could display incorrect
information about deadlocks, when the deadlock detection routine
stops early (to avoid excessive CPU usage).
(Bug #49001)
InnoDB:
Concurrent execution of ALTER
TABLE for InnoDB table
and a transaction that tried to read and then update the table
could result in a deadlock between table-level locks and
InnoDB row locks, which was
detected only after the
innodb_lock_wait_timeout
timeout occurred.
(Bug #37346)
Partitioning:
When used on a partitioned table, ALTER
TABLE produced the wrong error message when the name
of a nonexistent storage engine was used in the
ENGINE clause.
(Bug #35765)
Partitioning:
The first time that a query against the
INFORMATION_SCHEMA.TABLES table for
partitioned tables using the
ARCHIVE engine was run, it returned
invalid data. If the server had been restarted since such a
table had been created, or if the table had never actually been
opened, its DATA_LENGTH was reported as 0
bytes. (The second and subsequent attempts to issue the same
query returned the expected result.)
(Bug #44622)
Partitioning:
ALTER
TABLE on a partitioned table caused unnecessary
deadlocks.
(Bug #43867)
References: See also Bug #46654. This bug is a regression of Bug #40181.
Partitioning: After attempting to create a duplicate index on a partitioned table (and having the attempt fail as expected), a subsequent attempt to create a new index on the table caused the server to hang. (Bug #40181)
Partitioning:
When using a debug build of MySQL, if a query against a
partitioned table having an index on one or more
DOUBLE columns used that index,
the server failed with an assertion.
(Bug #45816)
Partitioning:
When one user was in the midst of a transaction on a partitioned
table, a second user performing an ALTER
TABLE on this table caused the server to hang.
(Bug #34604)
Partitioning:
Attempting to drop a partitioned table from one connection while
waiting for the completion of an ALTER
TABLE that had been issued from a different
connection, and that changed the storage engine used by the
table, could cause the server to crash.
(Bug #42438)
Partitioning: Portions of the partitioning code were refactored in response to potential regression issues uncovered while working on the fix for Bug #31210. (Bug #32115)
References: See also Bug #40281.
Replication:
Statements that updated AUTO_INCREMENT
columns in multiple tables were logged using the row-based
format when --binlog_format was set to
MIXED, but did not cause an Unsafe
statement warning to be generated when
--binlog_format was set to
STATEMENT.
(Bug #45827)
References: See also Bug #39934, Bug #11749859.
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.
This was not an issue when using row-based replication.
(Bug #45516)
Replication:
When using row-based replication, the incomplete logging of a
group of events involving both transaction and nontransactional
tables could cause STOP SLAVE to
hang.
(Bug #45940)
References: See also Bug #319, Bug #38205.
Replication:
Large transactions and statements could corrupt the binary log
if the size of the cache (as set by
max_binlog_cache_size) was not
large enough to store the changes.
Now, for transactions that do not fit into the cache, the statement is not logged, and the statement generates an error instead.
For nontransactional changes that do not fit into the cache, the statement is also not logged—an incident event is logged after committing or rolling back any pending transaction, and the statement then raises an error.
If a failure occurs before the incident event is written the binary log, the slave does not stop, and the master does not report any errors.
(Bug #43929, Bug #11752675)
References: See also Bug #37148, Bug #11748696, Bug #46166, Bug #11754544.
Replication:
Executing the sequence of statements RESET
SLAVE, RESET MASTER,
and FLUSH LOGS,
when binary log or relay log files listed in the index file
could not be found, could cause the server to crash. This could
happen, for example, when these files had been moved or deleted
manually.
(Bug #41902)
Replication:
MySQL creates binary logs in a numbered sequence, with a maximum
possible 4294967295 concurrent log files, 4294967295 being the
maximum value for an unsigned long integer. However, binary log
file extensions were turned into negative numbers once the
variable used to hold the value reached the maximum value for a
signed long integer (2147483647). Consequently, when the
sequence value was incremented to the next (negative) number,
MySQL tried to create the file using a
.000000 extension, causing the server to
fail since this file already existed.
Negative file extensions are no longer permitted, and an error
is returned when the limit is reached. In addition,
FLUSH LOGS now
also reports warnings to the user, if the extension number has
reached the limit, and warnings are printed to the error log
when the limit is approaching.
(Bug #40611)
Replication:
Issuing concurrent STOP SLAVE,
START SLAVE, and
RESET SLAVE statements using
different connections caused the replication slave to crash.
(Bug #38716)
References: See also Bug #38715, Bug #44312.
Replication:
On Windows, RESET MASTER failed
in the event of a missing binlog file rather than issuing a
warning and completing the rest of the statement.
(Bug #42150, Bug #42218)
Replication:
mysqlbinlog sometimes failed when trying to
create temporary files; this was because it ignored the
specified temp file directory and tried to use the system
/tmp directory instead.
(Bug #35546)
References: See also Bug #35543.
Replication:
A slave compiled using --with-libevent and run
with
--thread-handling=pool-of-threads
could sometimes crash.
(Bug #36929)
Replication:
A CHANGE MASTER TO statement with
no MASTER_HEARTBEAT_PERIOD option failed to
reset the heartbeat period to its default value.
(Bug #34686)
Replication:
When using the row-based or mixed replication format with a
debug build of the MySQL server, inserts into columns using the
utf32 character set on the master caused the
slave to crash.
(Bug #51787)
References: See also Bug #51716.
Replication:
When using the row-based or mixed replication format, column
values using the utf16 character set on the
master were padded incorrectly on the slave.
(Bug #51716)
References: See also Bug #51787.
Replication: When using the semisynchronous replication plugin on Windows, the wait time calculated when the master was waiting for reply from the slave was incorrect. In addition, when the wait time was less than the current time, the master did not wait for a reply at all.
This issue was caused by the fact that a different internal function was used to get current time by the plugin on Windows as opposed to other platforms, and this function was not correctly implemented. Now the Windows version of the plugin uses the same function as other platforms for this purpose. (Bug #49557)
Replication:
If a CHANGE MASTER TO statement
set MASTER_HEARTBEAT_PERIOD to 30 or higher,
Slave_received_heartbeats did
not increase on the slave. This caused the slave to reconnect
before the time indicated by
slave_net_timeout had elapsed.
This issue affected big-endian 64-bit platforms such as Solaris/SPARC. (Bug #50296)
Replication: Adding an index to a table on the master caused the slave to stop logging slow queries to the slow query log. (Bug #50620)
Replication:
An issue internal to the code, first seen in Bug #49132 but not
completely resolved in the fix for that bug, was removed. This
should prevent similar issues to those in the previous bug with
binlog_format changes following
DDL statements.
For developers working with the MySQL Server
code: the public class variable
THD::current_stmt_binlog_row_based was
supposed to have been removed as part of the fix for Bug #39934,
but was still present in the code. If a developer later tried to
use this variable, it could cause the previous issues to
re-occur, and possibly new ones to arise. The variable has now
been removed; the previously added class functions
THD::is_current_stmt_binlog_format_row(),
THD::set_current_stmt_binlog_format_row(),
and
THD::clear_current_stmt_binlog_format_row()
should be used instead.
(Bug #51021)
References: See also Bug #11749859.
Replication:
The error message given when trying to replicate (using
statement-based mode) insertions into an
AUTO_INCREMENT column by a stored function or
a trigger was improved.
(Bug #50192)
Replication: Statement-based replication of user variables having numeric data types did not always work correctly. (Bug #49562, Bug #11757508)
Replication: There were two related issues concerning handling of unsafe statements and setting of the binary logging format when there were open temporary tables on the master, and the existing replication format was row-based or mixed:
When using
binlog_format=ROW, and an
unsafe statement was executed while there were open
temporary tables on the master, the statement
SET
@@session.binlog_format = MIXED failed with the
error Cannot switch out of the row-based binary
log format when the session has open temporary
tables.
When using
binlog_format=MIXED, and an
unsafe statement was executed while there were open
temporary tables on the master, the statement
SET
@@session.binlog_format = STATEMENT caused any
subsequent DML statements to be written to the binary log
using the row-based format instead of the statement-based
format.
(Bug #45855, Bug #45856)
Replication:
The server could deadlock when
FLUSH LOGS was
executed concurrently with DML statements. To fix this problem,
nontransactional changes are now always flushed before
transactional changes.
(Bug #50038)
Replication:
Metadata for GEOMETRY fields was not properly
stored by the slave in its definitions of tables.
(Bug #49836)
References: See also Bug #48776.
Replication: Due to a change in the format of the information used by the slave to connect to the master, which could cause to reject connection attempts to older masters by newer slaves. (Bug #49259)
References: This bug was introduced by Bug #13963.
Replication:
When using row-based logging, a failing
INSERT ...
SELECT statement on a nontransactional table was not
flagged correctly, such that, if a rollback was requested and no
other nontransactional table had been updated, nothing was
written to the binary log.
(Bug #47175)
References: See also Bug #40278.
Replication:
Even though INSERT DELAYED
statements are unsafe for statement-based replication, they
caused the statement only to be logged in row format when the
binary logging format was MIXED, but did not
cause a warning to be generated when the binary logging format
was STATEMENT.
(Bug #45825)
Replication:
When using MIXED binary logging format,
statements containing a LIMIT clause and
occurring in stored routines were not written to the log as row
events.
(Bug #45785)
Replication:
STOP SLAVE did not flush the
relay log or the master.info or
relay-log.info files, which could lead to
corruption if the server crashed.
(Bug #44188)
Replication:
Formerly, only slaves that had been started with the
--report-hosts option were visible in the
output of SHOW SLAVE HOSTS. Now,
all slaves that are registered with the master appear in
SHOW SLAVE HOSTS output.
As part of the fix for this issue, the
Rpl_recovery_rank column, which had appeared
in the output of SHOW SLAVE HOSTS
in some MySQL releases, was removed because the corresponding
server variable
rpl_recovery_rank (now
deprecated) was never actually used.
(Bug #13963)
References: See also Bug #21132, Bug #21869.
For an IPv6-enabled MySQL server, privileges specified using standard IPv4 addresses for hosts were not matched (only IPv4-mapped addresses were handled correctly).
As part of the fix for this bug, a new build option
--disable-ipv6 has been introduced. Compiling
MySQL with this option causes all IPv6-specific code in the
server to be ignored.
If the server has been compiled using
--disable-ipv6, it is not able to resolve
hostnames correctly when run in an IPv6 environment.
(Bug #11754062, Bug #45606)
References: See also Bug #38247, Bug #43006, Bug #45584.
The hostname cache failed to work correctly. (Bug #45584)
References: See also Bug #38247, Bug #43006, Bug #11753779, Bug #45606.
An IPv6-enabled MySQL server did not resolve the IP addresses of incoming connections correctly, with the result that a connection that attempted to match any privilege table entries using fully qualified domain names for hostnames or hostnames using wildcards were dropped. (Bug #38247)
References: See also Bug #43006, Bug #11753779, Bug #45584, Bug #45606.
A Windows Installation using the GUI installer would fail with:
MySQL Server 5.1 Setup Wizard ended prematurely The wizard was interrupted before MySQL Server 5.1. could be completely installed. Your system has not been modified. To complete installation at another time, please run setup again. Click Finish to exit the wizard
This was due to a step in the MSI installer that could fail to execute correctly on some environments. (Bug #45418)
MySQL Server permitted the creation of a merge table based on views but crashed when attempts were made to read from that table. The following example demonstrates this:
#Create a test table CREATE TABLE tmp (id int, c char(2)); #Create two VIEWs upon it CREATE VIEW v1 AS SELECT * FROM tmp; CREATE VIEW v2 AS SELECT * FROM tmp; #Finally create a MERGE table upon the VIEWs CREATE TABLE merge (id int, c char(2)) ENGINE=MERGE UNION(v1, v2); #Reading from the merge table lead to a crash SELECT * FROM merge;
The final statement generated the crash. (Bug #44040)
When archive tables were joined on their primary keys, a query returned no result if the optimizer chose to use this index. (Bug #40677)
The CSV storage engine did not parse
'\X' characters when they occurred in
unquoted fields.
(Bug #40814)
Dropping a locked Maria table leads to an
assertion failure.
(Bug #39395)
mysqlbinlog left temporary files on the disk after shutdown, leading to the pollution of the temporary directory, which eventually caused mysqlbinlog to fail. This caused problems in testing and other situations where mysqlbinlog might be invoked many times in a relatively short period of time. (Bug #35543)
Compiling MySQL on FreeBSD failed due to missing definitions for certain network constants. (Bug #34292)
The parser incorrectly permitted MySQL error code 0 to be specified for a condition handler. (This is incorrect because the condition must be a failure condition and 0 indicates success.) (Bug #36510)
mysql_stmt_prepare() did not
reset the list of messages (those messages available using
SHOW WARNINGS).
(Bug #36004)
Host name lookup failure could lead to a server crash. (Bug #39153)
Previously, statements inside a stored program did not clear the
warning list. For example, warnings or errors generated by
statements within a trigger or stored function would be
accumulated and added to the message list for the statement that
activated the trigger or invoked the function,
“polluting” the output of SHOW
WARNINGS or SHOW ERRORS
for the outer statement. Normally, messages for a statement that
can generate messages replace messages from the previous such
statement. The effect was that a statement could have a
different effect on the message list depending on whether it
executed inside or outside of a stored program.
Now within a stored program, successive statements that can generate messages update the message list and replace messages from the previous such statement. Only messages from the last of these statements is copied to the message list for the outer statement. (Bug #36649)
When parsing or formatting interval values of
DAY_MICROSECOND type, fractional seconds were
not handled correctly when more-significant fields were implied
or omitted.
(Bug #36466)
Threads were set to the Table lock state in
such a way that use of this state by other threads to check for
a lock wait was subject to a race condition.
(Bug #39897)
myisampack --join did not create the
destination table .frm file.
(Bug #36573)
mysql_install_db failed if run as
root and the root directory
(/) was not writable.
(Bug #36462)
Sign loss could occur in several contexts:
SEC_TO_TIME() could lose the
sign of negative arguments.
MAKETIME() could lose the
sign of negative arguments.
Comparison of TIME values
could lose the sign of operands.
(Bug #42661, Bug #42662, Bug #42664)
The state of a thread for the embedded server was always
displayed as Writing to net, which is
incorrect because there is no network connection for the
embedded server.
(Bug #41971)
mysqld_safe did not treat dashes and underscores as equivalent in option names. Thanks to Erik Ljungstrom for the patch to fix this bug. (Bug #40368)
Valgrind warnings that occurred for SHOW
TABLE STATUS with InnoDB tables
were silenced.
(Bug #38479)
A natural join of INFORMATION_SCHEMA tables
could cause an assertion failure.
(Bug #43834)
Execution of FLUSH
TABLES or FLUSH
TABLES WITH READ LOCK concurrently with
LOCK TABLES resulted in deadlock.
(Bug #45066)
Plugin shutdown could lead to an assertion failure caused by using an already destroyed mutex in the metadata locking subsystem. (Bug #39674)
Killing a delayed-insert thread could cause a server crash. (Bug #45067)
The patch for Bug #10374 broke named-pipe and shared-memory connections on Windows. (Bug #41860)
The mysql_real_connect() C API
function only attempted to connect to the first IP address
returned for a hostname. This could be a problem if a hostname
mapped to multiple IP address and the server was not bound to
the first one returned. Now
mysql_real_connect() attempts to
connect to all IPv4 or IPv6 addresses that a domain name maps
to.
(Bug #45017)
References: See also Bug #47757.
The server could crash if an attempt to open a
MERGE table child
MyISAM table failed.
(Bug #42862)
The server could crash attempting to flush privileges after
receipt of a SIGHUP signal.
(Bug #46495)
Improperly closing tables when INSERT
DELAYED needed to reopen tables could cause an
assertion failure.
(Bug #45949)
References: See also Bug #18484.
HANDLER statements are now not
permitted if a table lock has been acquired with
LOCK TABLES.
(Bug #43272)
An assertion failure could occur if
OPTIMIZE TABLE was started on an
InnoDB table and the table was altered to a
different storage engine during the optimization operation.
(Bug #42074)
Deadlock occurred if one session was running a multiple-statement transaction that involved a single partitioned table and another session attempted to alter the table. (Bug #46654)
If INSERT INTO
invoked a stored
function that modified tbl_nametbl_name, the
server crashed.
(Bug #46374)
CREATE VIEW raised an assertion
if a temporary table existed with the same name as the view.
(Bug #47635)
Selecting from the process list in the embedded server caused a crash. (Bug #47304)
References: See also Bug #43733.
If a temporary table was created with the same name as a view referenced in a stored routine, routine execution could raise an assertion. (Bug #47313)
Programs did not exit if the option file specified by
--defaults-file was not found.
(Bug #47216)
For queries that used GROUP_CONCAT(DISTINCT
...), the value of
max_heap_table_size was used
for memory allocation, which could be excessive. Now the minimum
of max_heap_table_size and
tmp_table_size is used.
(Bug #46018)
Creation of a temporary BLOB or
TEXT column could create a column
with the wrong maximum length.
(Bug #33969)
Valgrind warnings for several logging messages were corrected. (Bug #49130)
Constant expressions in WHERE,
HAVING, or ON clauses were
not cached, but were evaluated for each row. This caused a
slowdown of query execution, especially if constant user-defined
functions or stored functions were used.
(Bug #33546)
The mysql could default to the
ascii character set, which is not a valid
character set choice for MySQL. The latin1
character set will now be used when an ASCII environment has
been identified.
(Bug #51166)
SHOW CREATE VIEW returned invalid
SQL if the definition contained a
SELECT
' statement
where the string'string was longer than the
maximum length of a column name, due to the fact that this text
was also used as an alias (in the AS clause).
Because not all names retrieved from arbitrary
SELECT statements can be used as
view column names due to length and format restrictions, the
server now checks the conformity of automatically generated
column names and rewrites according to a predefined format any
names that are not acceptable as view column names before
storing the final view definition on disk.
In such cases, the name is now rewritten as
Name_exp_,
where pospos is the position of the
column. To avoid this conversion scheme, define explicit, valid
names for view columns using the
column_list clause of the
CREATE VIEW statement.
As part of this fix, aliases are now generated only for top-level statements. (Bug #40277)
Running SHOW CREATE TABLE on a
view v1 that contained a function which
accessed another view v2 could trigger a
infinite loop if the view referenced within the function
(v2) caused a warning to be raised while
being opened.
(Bug #48449)
If a prepared statement used both a MERGE
table and a stored function or trigger, execution sometimes
failed with a No such table error.
(Bug #47648)
An .ARZ file missing from the database
directory caused the server to crash.
(Bug #48757)
When building MySQL when using a different target directory (for
example using the VPATH environment
variable), the build of the embedded readline
component failed.
(Bug #35250)
On some Unix/Linux platforms, an error during build from source
could be produced, referring to a missing
LT_INIT program. This is due to versions of
libtool 2.1 and earlier.
(Bug #51009)
Corrected a potential problem of unintended file overwriting
when the MY_DONT_OVERWRITE_FILE flag was
used.
(Bug #47126)
If a stored function contained a
RETURN statement with an
ENUM value in the ucs2
character set, SHOW CREATE
FUNCTION and SELECT DTD_IDENTIFIER FROM
INFORMATION_SCHEMA.ROUTINES returned incorrect values.
(Bug #48766)
An aliasing violation in the C API could lead to a crash. (Bug #48284)
A dependent subquery containing
COUNT(DISTINCT
could be
evaluated incorrectly.
(Bug #48920)col_name))
mysqltest no longer permits you to execute an
SQL statement on a connection after doing a
send command, unless you do a
reap first. This was previously accepted but
could produce unpredictable results.
(Bug #49269)
InnoDB took a shared row lock when
executing SELECT statements
inside a stored function as a part of a transaction using
REPEATABLE READ. This
prevented other transactions from updating the row.
(Bug #44613)
The return values for calls to put information into the stored routine cache were not consistently checked, raising an assertion. (Bug #50412)
Purging the stored-routine cache could take a long time and render the server unresponsive. (Bug #41804)
When used in conjunction with LOCK
TABLES, FLUSH
TABLE waited for
all tables with old versions to clear from the table definition
list, rather than only the named tables.
(Bug #43685)tbl_list
HANDLER statements within a
transaction that already holds metadata locks could lead to
deadlocks.
Before this fix, all handlers for TEMPORARY
tables were reset whenever any base table was opened.
(Bug #46224)
There was no timeout for attempts to acquire metadata locks (for
example, a DROP TABLE attempt for
a table that was open in another transaction would not time
out).
To handle such situations, there is now a
lock_wait_timeout system
variable that specifies the timeout in seconds for attempts to
acquire metadata locks. The permitted values range from 1 to
31536000 (1 year). The default is 31536000.
This timeout applies to all statements that use metadata locks.
These include DML and DDL operations on tables, views, stored
procedures, and stored functions, as well as
LOCK TABLES,
FLUSH TABLES WITH READ
LOCK, and HANDLER
statements.
The timeout value applies separately for each metadata lock
attempt. A given statement can require more than one lock, so it
is possible for the statement to block for longer than the
lock_wait_timeout value before
reporting a timeout error. When lock timeout occurs,
ER_LOCK_WAIT_TIMEOUT is
reported.
lock_wait_timeout does not
apply to delayed inserts, which always execute with a timeout of
1 year. This is done to avoid unnecessary timeouts because a
session that issues a delayed insert receives no notification of
delayed insert timeouts.
In addition: The unused
table_lock_wait_timeout system
variable was removed. The LOW_PRIORITY
modifier for LOCK
TABLES ... WRITE locks now has no effect. The meaning
of LOW_PRIORITY remains as before in other
contexts, such as for INSERT or
DELETE statements.
innodb_table_locks=0 no longer
has an effect for tables locked explicitly with
LOCK TABLES ...
WRITE. It still has an effect for tables locked for
read or write by
LOCK TABLES ...
WRITE implicitly (for example, through triggers) or by
LOCK TABLES ...
READ.
(Bug #45225, Bug #56272)
mysqld_multi failed due to a syntax error in the script. (Bug #51468)
On POSIX systems, calls to select() with a
file descriptor set larger than FD_SETSIZE
resulted in unpredictable I/O errors; for example, when a large
number of tables required repair.
(Bug #48929)
For debug builds, SHOW BINARY
LOGS raised an assertion if binary logging was not
enabled.
(Bug #50780)
Full-text queries that used the truncation operator
(*) could enter an infinite loop.
(Bug #50351)
For debug builds on Windows, warnings about incorrect use of debugging directives were written to the error log. The directives were rewritten to eliminate these messages. (Bug #49025)
Slow CALL statements were not
always logged to the slow query log because execution time for
multiple-statement stored procedures was assessed incorrectly.
(Bug #47905)
The optimizer normally prefers use of
filesort plus the join cache to a full index
scan. But this combination was used even if the index is
clustered, in which case, the clustered index scan can be
faster.
(Bug #50843)
Incorrect handling of BIT columns
in temporary tables could lead to spurious duplicate-key errors.
(Bug #50591)
Referring to a subquery result in a HAVING
clause could produce incorrect results.
(Bug #50995)
mysql --show-warnings crashed if the server connection was lost. (Bug #49646)
When read_only was enabled, the
server incorrectly prevented data modifications to
TEMPORARY tables belonging to transactional
storage engines such as InnoDB.
(Bug #33669)
ALTER TABLE on a
MERGE table that has been locked
using LOCK TABLES
... WRITE incorrectly produced an
ER_TABLE_NOT_LOCKED_FOR_WRITE
error.
(Bug #51240)
Setting key_buffer_size to a
negative value could lead to very large allocations. Now an
error occurs.
(Bug #42103)
In the embedded server, stack overflow checks for recursive stored procedure calls did not work and stack overflow could occur. (Bug #43201)
Some plugins configured as mandatory could be disabled at server startup. (Bug #44691)
Attempts to print octal numbers with
my_vsnprintf() could cause a crash.
(Bug #47212)
Setting binlog_format to
DEFAULT assigned a value different from the
default.
(Bug #49540)
SHOW VARIABLES did not correctly
display string-valued system variables that contained
\0 characters.
(Bug #49644)
MySQL program option-processing code incorrectly displayed some options when printing ambiguous-option errors. (Bug #49640)
For string-valued system variables containing multi-byte characters, the byte length was used in contexts where the character length was more appropriate. (Bug #49645)
Failure to open a view with a nonexistent
DEFINER was improperly handled and the server
crashed later attempting to lock the view.
(Bug #47734)
flush_cache_records() did not correctly check
for errors that should cause statement execution to stop,
leading to a server crash.
(Bug #39022)
INSERT INTO ...
VALUES(DEFAULT) failed to insert the correct value for
ENUM columns. For
MyISAM tables, an empty value was
inserted. For CSV tables, the table
became corrupt.
(Bug #33717)
The sql_mode system variable
could be assigned the illegal value of '?'.
(Bug #34834)
String-valued system variables could be assigned literal values, but could not be assigned values using expressions. Now expressions are legal. (Bug #34883, Bug #46314)
Valgrind warnings about uninitialized variables in optimizer code were corrected. (Bug #45195)
Propagation of a large unsigned numeric constant in
WHERE expressions could lead to incorrect
results. This also affected EXPLAIN
EXTENDED, which printed incorrect numeric constants in
such transformed WHERE expressions.
(Bug #45360)
Grouping by a subquery in a query with a
DISTINCT aggregate function led to incorrect
and unordered grouping values.
(Bug #45640)
Valgrind warnings about memory allocation overruns for handling
CREATE FUNCTION statements for
UDFs were corrected.
(Bug #46570)
The server did not recognize that the stored procedure cache became invalid if a view was created or modified within a procedure, resulting in a crash. (Bug #50624)
The second or subsequent invocation of a stored procedure
containing DROP TRIGGER could
cause a server crash.
(Bug #50423)
For debug builds, an assertion was incorrectly raised in the
optimizer when matching ORDER BY expressions.
(Bug #50335)
Queries optimized with GROUP_MIN_MAX did not
clean up KEYREAD optimizations properly,
causing subsequent queries to return incomplete rows.
(Bug #49902)
For dynamic format MyISAM tables
containing LONGTEXT columns, a
bulk INSERT ... ON
DUPLICATE KEY UPDATE or bulk
REPLACE could cause corruption.
(Bug #49628)
For debug builds, with
sql_safe_updates enabled, a
multiple-table UPDATE with the
IGNORE modifier could raise an assertion.
(Bug #49534)
For debug builds, killing a
SELECT retrieving from a view
that was processing a function raised an assertion.
(Bug #47736)
For debug builds, creating a view containing a row constructor raised an assertion. (Bug #48294)
Invalid memory reads could occur following a query that
referenced a MyISAM table multiple
times with a write lock.
(Bug #48438)
EXPLAIN EXTENDED crashed trying
to print column names for a subquery in the
FROM clause when the table had gone out of
scope.
(Bug #49487)
Renaming a column of an InnoDB
table caused the server to go out of sync with the
InnoDB data dictionary. To avoid
this issue, renaming a column uses the older technique of
copying all the table data rather than updating the table
in-place.
(Bug #47621)
InnoDB logged an error repeatedly
trying to load a page into the buffer pool, filling the error
log and using excessive disk space. Now the number of attempts
is limited to 100, after which the operation aborts with a
message.
(Bug #38901)
Command-line options for enumeration-type plugin variables were not honored. (Bug #41010)
For plugins that did not have command-line options other than the ones to select the plugin itself, those options were not displayed in the mysqld help message. (Bug #44797)
System variables could be set to invalid values. (Bug #40988)
Plugins could find the unqualified form of their system
variables but not the qualified form. For example, a plugin
p with a system variable
sv could find sv but not
p_sv.
(Bug #32902)
A global read lock obtained with
FLUSH TABLES WITH READ
LOCK did not prevent sessions from creating tables.
(Bug #35935)
For InnoDB tables, the test for
using an index for ORDER BY sorting did not
distinguish between primary keys and secondary indexes and
expected primary key values to be concatenated to index values
the way they are to secondary key values.
(Bug #49324)
For CREATE TABLE
... LIKE with a MERGE
source table that included a UNION clause,
that clause was omitted from the definition of the destination
table.
(Bug #37371)
mysqld_safe did not pass the correct default
value of plugin_dir to
mysqld.
(Bug #51938)
Plugins in a binary release could not be installed into a debug version of the server. (Bug #49022)
Aggregate functions on TIMESTAMP
columns could yield incorrect or undefined results.
(Bug #50888)
If an operation had an InnoDB table, and two
triggers, AFTER UPDATE and AFTER
INSERT, competing for different resources (such as two
distinct MyISAM tables), the triggers were
unable to execute concurrently. In addition,
INSERT and
UPDATE statements for the
InnoDB table were unable to run concurrently.
(Bug #26141)
The character set was not being properly initialized for
CAST() with a type such as
CHAR(2) BINARY, which resulted in
incorrect results or a server crash.
(Bug #17903)
Labels in stored routines did not work if the character set was
not latin1.
(Bug #7088)
mysqld sometimes miscalculated the number of
digits required when storing a floating-point number in a
CHAR column. This caused the
value to be truncated, or (when using a debug build) caused the
server to crash.
(Bug #26788)
References: See also Bug #12860.
When inserting an extraordinarly large value into a
DOUBLE column, the value could be
truncated in such a way that the new value cannot be reloaded
manually or from the output of mysqldump.
(Bug #21497)
Zero-padding of exponent values was not the same across platforms. (Bug #12860)
Data truncated for column
warnings were
generated for some (constant) values that did not have too high
precision.
(Bug #24541)col_num at row
row_num
The grammar for GROUP BY, when used with
WITH CUBE or WITH ROLLUP,
caused a conflict with the grammar for view definitions that
included WITH CHECK OPTION.
(Bug #9801)
Stored procedure exception handlers were catching fatal errors (such as out of memory errors), which could cause execution not to stop to due a continue handler. Now fatal errors are not caught by exception handlers and a fatal error is returned to the client. (Bug #15192)
Delayed-insert threads were counted as connected but not as
created, incorrectly leading to a
Threads_connected value
greater than the
Threads_created value.
(Bug #17954)
If a connection was waiting for a
GET_LOCK() lock or a
SLEEP() call, and the connection
aborted, the server did not detect this and thus did not close
the connection. This caused a waste of system resources
allocated to dead connections. Now the server checks such a
connection every five seconds to see whether it has been
aborted. If so, the connection is killed (and any lock request
is aborted).
(Bug #10374)
Killing a statement that invoked a stored function could return an incorrect error message indicating table corruption rather than that the statement had been interrupted. (Bug #32140)
Occurrence of an error within a stored routine did not always cause immediate statement termination. (Bug #31881)
Statements to create, alter, or drop a view were not waiting for completion of statements that were using the view, which led to incorrect sequences of statements in the binary log when statement-based logging was enabled. (Bug #25144)
For DROP FUNCTION
(that is, when the function name is qualified with the database
name), the statement should apply only to a stored function
named db_name.func_namefunc_name in the given database.
However, if a UDF with the same name existed, the statement
dropped the UDF instead.
(Bug #31767)
perror did not work for errors described in
the sql/share/errmsg.txt file.
(Bug #10143)
For CREATE TABLE, the parser did
not enforce that parentheses were present in a CHECK
( clause; now it does.
The parser did not enforce that expr)CONSTRAINT
[ without a
following symbol]CHECK clause was illegal; now it
does.
(Bug #11714, Bug #35578, Bug #38696)
ALTER TABLE could not be used to
add columns to a table if the table had an index on a
utf8 column with a
TEXT data type.
(Bug #26180)
For the DIV operator, incorrect
results could occur for noninteger operands that exceed
BIGINT range. Now, if either
operand has a noninteger type, the operands are converted to
DECIMAL and divided using
DECIMAL arithmetic before
converting the result to BIGINT.
If the result exceeds BIGINT
range, an error occurs.
(Bug #8457, Bug #11745058)
References: See also Bug #59241.
Several data-modification statements were not being counted
toward the MAX_UPDATES_PER_HOUR user resource
limit.
(Bug #21793)
Previously, the server handled character data types for a stored
routine parameter, local routine variable created with
DECLARE, or stored function
return value as follows: If the CHARACTER SET
attribute was present, the COLLATE attribute
was not supported, so the character set's default collation was
used. (This includes use of BINARY, which in
this context specifies the binary collation of the character
set.) If there was no CHARACTER SET
attribute, the database character set and its default collation
were used.
Now for character data types, if there is a CHARACTER
SET attribute in the declaration, the specified
character set and its default collation is used. If the
COLLATE is also present, that collation is
used rather than the default collation. If there is no
CHARACTER SET attribute, the database
character set and collation in effect at routine creation time
are used. (The database character set and collation are given by
the value of the
character_set_database and
collation_database system
variables.)
(Bug #24690)
A statement that caused a circular wait among statements did not
return a deadlock error. Now the server detects deadlock and
returns ER_LOCK_DEADLOCK.
(Bug #22876)
For INSERT DELAYED statements
issued for a table while an ALTER
TABLE operation on the table was in progress, the
server could return a spurious Server shutdown in
progress error.
(Bug #18484)
References: See also Bug #45949.
The value of
sql_slave_skip_counter was
empty when displayed by SHOW
VARIABLES or
INFORMATION_SCHEMA.GLOBAL_VARIABLES.
(Bug #20413, Bug #37187)
Previously, for some Asian CJK character sets, the
UPPER() and
LOWER() functions worked only for
basic Latin letters (A-Z,
a-z). The affected character sets are
ujis, sjis,
gb2312, cp932,
eucjpms, big5,
euckr, and gbk.
Now UPPER() and
LOWER() perform case conversion
correctly for all characters in these character sets, with the
exception that if a character set contains a character in only
one lettercase, conversion to the other lettercase cannot be
done.
CREATE TABLE ...
LIKE did not always produce an error is the source
table column defaults were illegal for the current version of
MySQL. (This could occur if the table was created using an older
server that was less restrictive about legal default values.)
(Bug #22090)