MySQL - Versions

Table of content

Versions are introduced in any product to upgrade by adding extra features and removing unnecessary ones, fixing the bugs etc. The process of versioning is actually important to make the product more efficient with growing technology.

A product is generally released after performing phases of testing: alpha testing, beta testing, gamma testing, and then it is produced once all these tests are passed.

Whenever MySQL is installed, we must choose the version to install along with its distribution format. The latest version of MySQL is 8.0 with its minor version being 8.0.34. To install the MySQL server, there are two methods: Development release and General Availability.

The Development Release contains all the latest features but it is not recommended to be used in production. The General Availability Release is more of a production release which can be stably used in production.

MySQL Versions

MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. It is developed, marketed and supported by MySQL AB, which is a Swedish company. MySQL was first internally released on 23rd May, 1995, and until Oracle acquired Sun Microsystems, version 3.19 to version 5.1 were released.

Version 5.1

The version 5.1 of MySQL had its production release on 27th November, 2008 by adding extra features like event scheduler, partitioning, plugin API, row-based replication, server log tables.

But, version 5.1 contained 20 known bugs that gave out wrong results, along with the 35 bugs from version 5.0. However, almost all of them are fixed as of release 5.1.51. Also, MySQL 5.1 and 6.0 (in alpha test phase) showed poor performance in data warehousing, which could partially be due to its inability to utilize multiple CPU cores for processing a single query.

Version 5.5

MySQL Server 5.5 was a General Availability Release as of December 2010. The improved features in this version include −

  • The default storage engine is InnoDB with improved I/O subsystem, which supports transactions and referential integrity constraints.

  • Improved SMP support

  • Semi-synchronous replication.

  • SIGNAL and RESIGNAL statement was added in compliance with the SQL standard.

  • Support for supplementary Unicode character sets utf16, utf32, and utf8mb4.

  • New options for user-defined partitioning.

Version 5.6

The General Availability of MySQL 5.6 was released in February 2013. New features of this version included:

  • Query optimizer has more efficient performance.

  • Higher transactional throughput in InnoDB.

  • New NoSQL-style memcached APIs.

  • Improvements to partitioning for querying large tables.

  • Better management of very large tables.

  • TIMESTAMP column type that correctly stores milliseconds.

  • Improvements to replication.

  • Better performance monitoring by expanding the data available through the PERFORMANCE_SCHEMA.

  • The InnoDB storage engine also provides support for full-text search and improved group commit performance.

Version 5.7

MySQL 5.7 was made generally available in October 2015. For the minor versions of MySQL 5.7, MySQL 5.7.8 and later, a support for a native JSON data type defined by RFC 7159 by August 2015.

Version 8.0

MySQL Server 8.0 was announced in April 2018, with new improved features. Currently, the minor versions in MySQL 8.0 start from 8.0.0 to 8.0.34. Previous MySQL Server 8.0.0-dmr (as a Development Milestone Release) was announced on 12th September, 2016.

Features Added in MySQL 8.0

The latest version of MySQL is 8.0. The following features are some of the newly added features to it:

  • Data dictionary − In previous MySQL releases, dictionary data was stored in metadata files and non-transactional tables. MySQL now incorporates a transactional data dictionary that stores information about database objects.

  • Atomic Data Definition Language(Atomic DDL) statements − An Atomic DDL statement combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic transaction.

  • Upgrade procedure − Previously, after installation of a new version of MySQL, the MySQL server automatically upgrades the data dictionary tables at the next startup, after which the DBA is expected to invoke mysql_upgrade manually to upgrade the system tables in the mysql schema, as well as objects in other schemas such as the 'sys' schema and user schemas.

    As of MySQL 8.0.16, the server also performs the tasks previously handled by mysql_upgrade. In addition, the server updates the contents of the help tables as well. A new --upgrade server option provides control over how the server performs automatic data dictionary and server upgrade operations.

  • Session Reuse − MySQL Server now supports SSL session reuse by default with a timeout setting to control how long the server maintains a session cache that establishes the period during which a client is permitted to request session reuse for new connections. All MySQL client programs support session reuse. In addition, C applications now can use the C API capabilities to enable session reuse for encrypted connections.

  • Security and account management − The security is improved greatly and greater DBA flexibility in account management is also enabled.

  • Resource management − MySQL now supports creation and management of resource groups, and permits assigning threads running within the server to particular groups so that threads execute according to the resources available to the group.

  • Table encryption management − Table encryption can now be managed globally by defining and enforcing encryption defaults.

  • InnoDB enhancements − Several InnoDB enhancements were added, like, auto-increment counter value, index tree corruption, mem-cached plug-in, InnoDB_deadlock_detect, tablespace encryption feature, storage engine, InnoDB_dedicated_server, creating temporary tables in temporary tablespace, zlib library etc.

  • Character set support − The default character set has changed from latin1 to utf8mb4. The utf8mb4 character set has several new collations, including utf8mb4_ja_0900_as_cs, the first Japanese language-specific collation available for Unicode in MySQL.

  • JSON enhancements − Several enhancements and additions were made to MySQL's JSON functionality.

  • Data type support − MySQL now supports use of expressions as default values in data type specifications. This includes the use of expressions as default values for the BLOB, TEXT, GEOMETRY, and JSON data types, which previously could not be assigned default values at all.

  • Optimizer − Optimizer is enhanced in various ways as well.

  • Improved hash join performance − MySQL 8.0.23 reimplements the hash table used for hash joins, resulting in several improvements in hash join performance.

  • Common table expressions − MySQL now supports common table expressions, both non-recursive and recursive.

  • Window functions − MySQL now supports window functions that, for each row from a query, perform a calculation using rows related to that row.

  • Lateral derived tables − A derived table now may be preceded by the LATERAL keyword to specify that it is permitted to refer to (depend on) columns of preceding tables in the same FROM clause.

  • Aliases in single-table DELETE statements − In MySQL 8.0.16 and later, single-table DELETE statements support the use of table aliases.

  • Regular expression support − Previously, MySQL used the Henry Spencer regular expression library to support regular expression operators.

  • Internal temporary tables − The TempTable storage engine replaces the MEMORY storage engine as the default engine for in-memory internal temporary tables.

  • Logging − Logging process is also improved in the newer versions.

In addition to all these, there are many other features added to the new versions of MySQL as well.

Features Deprecated in MySQL 8.0

The following are some of the many features that are deprecated in MySQL 8.0 and may be removed in the future series. Several alternatives mentioned can be used by applications.

  • The utf8mb3 character set is deprecated. Please use utf8mb4 instead.

  • ucs2, macroman and macce, dec, hp8 are also deprecated. You should use utf8mb4 instead.

  • User-defined collations are deprecated.

  • sha256_password is deprecated.

  • The plugin form of validate_password is still available, but deprecated.

  • The ENGINE clause for the ALTER TABLESPACE and DROP TABLESPACE statements is deprecated.

  • The PAD_CHAR_TO_FULL_LENGTH SQL mode is deprecated.

  • AUTO_INCREMENT support is deprecated for columns of type FLOAT and DOUBLE (and any synonyms).

  • The UNSIGNED attribute is deprecated for columns of type FLOAT, DOUBLE, and DECIMAL (and any synonyms)

  • FLOAT(M,D) and DOUBLE(M,D) syntax is deprecated.

  • The ZEROFILL attribute for numeric data types and the display width attribute for integer data types are deprecated.

  • The BINARY attribute is deprecated. However, the use of BINARY to specify a data type or character set remains unchanged.

  • ASCII and UNICODE are deprecated (MySQL 8.0.28 and later). Use CHARACTER SET instead, in both cases.

Advertisements