This section summarizes what has been added to and removed from MySQL 5.5.
Added Features
The following features have been added to MySQL 5.5:
Improved scalability on multi-core CPUs. The trend in hardware development now is toward more cores rather than continued increases in CPU clock speeds, which renders “wait until CPUs get faster” a nonviable means of improving database performance. Instead, it is necessary to make better use of multiple cores to maximally exploit the processing cycles they make available. MySQL 5.5 takes advantage of features of SMP systems and tries to eliminate bottlenecks in MySQL architecture that hinder full use of multiple cores. The focus has been on
InnoDB
, especially locking and memory management. See Section 1.5.1, “Scalability Improvements”.InnoDB
I/O subsystem changes enable more effective use of available I/O capacity. See Section 1.5.2, “InnoDB
I/O Subsystem Changes”.Several modifications improve operation of MySQL Server on Solaris. See Section 1.5.3, “Enhanced Solaris Support”.
There is better access to execution and performance information. Diagnostic improvements include DTrace probes, expanded
SHOW ENGINE INNODB STATUS
output, and a new status variable. See Section 1.5.4, “Diagnostic and Monitoring Capabilities”.The default storage engine for new tables is
InnoDB
rather thanMyISAM
. See Section 13.6.1, “InnoDB as the Default MySQL Storage Engine”.Support for an interface for semisynchronous replication: A commit performed on the master side blocks before returning to the session that performed the transaction until at least one slave acknowledges that it has received and logged the events for the transaction. Semisynchronous replication is implemented through an optional plugin component. See Section 17.3.8, “Semisynchronous Replication”
Support for the SQL standard
SIGNAL
andRESIGNAL
statements. See Section 12.7.8, “SIGNAL
andRESIGNAL
”.Support for Performance Schema, a feature for monitoring MySQL Server execution at a low level. See Chapter 21, MySQL Performance Schema.
Support for additional Unicode character sets:
utf16
,utf32
, andutf8mb4
. These character sets support supplementary Unicode characters; that is, characters outside the Basic Multilingual Plane (BMP). See Section 9.1.10, “Unicode Support”.Enhancements to XML functionality, including a new
LOAD XML
statement.Two new types of user-defined partitioning are supported:
RANGE COLUMNS
partitioning is an extension toRANGE
partitioning;LIST COLUMNS
partitioning is an extension toLIST
partitioning. Each of these extensions provides two enhancements to MySQL partitioning capabilities:It is possible to define partitioning ranges or lists based on
DATE
,DATETIME
, or string values (such asCHAR
orVARCHAR
).You can also define ranges or lists based on multiple column values when partitioning tables by
RANGE COLUMNS
orLIST COLUMNS
, respectively. Such a range or list may refer to up to 16 columns.For tables defined using these partitioning types, partition pruning can now optimize queries with
WHERE
conditions that use multiple comparisons between (different) column values and constants, such asa = 10 AND b > 5
ora < "2005-11-25" AND b = 10 AND c = 50
.
For more information, see Section 18.2.1, “
RANGE
Partitioning”, and Section 18.2.2, “LIST
Partitioning”.It is now possible to delete all rows from one or more partitions of a partitioned table using the
ALTER TABLE ... TRUNCATE PARTITION
statement. Executing the statement deletes rows without affecting the structure of the table. The partitions named in theTRUNCATE PARTITION
clause do not have to be contiguous.Key caches are now supported for indexes on partitioned
MyISAM
tables, using theCACHE INDEX
andLOAD INDEX INTO CACHE
statements. In addition, a key cache can be defined for and loaded with indexes from an entire partitioned table, or for one or more partitions. In the latter case, the partitions are not required to be contiguous.MySQL authentication supports two new capabilities, pluggable authentication and proxy users. With pluggable authentication, the server can use plugins to authenticate incoming client connections, and clients can load an authentication plugin that interacts properly with the corresponding server plugin. This capability enables clients to connect to the MySQL server with credentials that are appropriate for authentication methods other than the built-in MySQL authentication based on native MySQL passwords stored in the
mysql.user
table. For example, plugins can be created to use external authentication methods such as LDAP, Kerberos, PAM, or Windows login IDs. Proxy user capability enables a client who connects and authenticates as one user to be treated, for purposes of access control while connected, as having the privileges of a different user. In effect, one user impersonates another. Proxy capability depends on pluggable authentication because it is based on having an authentication plugin return to the server the user name that the connecting user impersonates. For information about these capabilities, see Section 5.5.6, “Pluggable Authentication”, and Section 5.5.7, “Proxy Users”.The
TO_SECONDS()
function is added. This function converts a date or datetime expression to a number of seconds since the year 0. You may use this function in partitioning expressions, and partition pruning is supported for tables defined using such expressions.MySQL releases are now built using CMake rather than the GNU autotools. Accordingly, the instructions for installing MySQL from source have been updated to discuss how to build MySQL using CMake. See Section 2.11, “Installing MySQL from Source”.
The build process is now similar enough on all platforms, including Windows, that there are no longer sections dedicated to notes for specific platforms.
Removed Features
The following constructs are obsolete and have been removed in MySQL 5.5. Where alternatives are shown, applications should be updated to use them.
The
log_bin_trust_routine_creators
system variable (uselog_bin_trust_function_creators
).The
myisam_max_extra_sort_file_size
system variable.The
record_buffer
system variable (useread_buffer_size
).The
sql_log_update
system variable.The
table_type
system variable (usestorage_engine
).The
FRAC_SECOND
modifier for theTIMESTAMPADD()
function.The
TYPE
table option to specify the storage engine forCREATE TABLE
orALTER TABLE
(useENGINE
).The
SHOW TABLE TYPES
SQL statement (useSHOW ENGINES
).The
SHOW INNODB STATUS
andSHOW MUTEX STATUS
SQL statements (useSHOW ENGINE INNODB STATUS
SHOW ENGINE INNODB MUTEX
).The
SHOW PLUGIN
SQL statement (useSHOW PLUGINS
).The
LOAD TABLE ... FROM MASTER
andLOAD DATA FROM MASTER
SQL statements (use mysqldump or mysqlhotcopy to dump tables and mysql to reload dump files).The
BACKUP TABLE
andRESTORE 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 ofN
)N
(use withoutN
).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 thexxx
CHANGE 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=
andvar_name
=value
-O
general-purpose options for setting program variables (usevar_name
=value
--
).var_name
=value
The
--with-pstack
option for configure and the--enable-pstack
option for mysqld.