MySQL 6 Preview

In this article, we will take a closer look to the new, upcoming version of the MySQL software - MySQL 6 and present you with all the innovations it will bring. Scroll down to check it out!

MySQL Overview

In today's Internet world, each website strives to be as dynamic and content rich as possible, which is why almost every site is connected to a database. And when one mentions database, the word MySQL is the first thing which comes to mind. This lightweight, open source database software has, since its release back in 1995, quickly gained the love and support of almost everybody out there due to its simplicity of use and its easy integration with programming languages. The popularity of the language has lead to its eventual acquisition by Sun Microsystems, which can only speak for the quality of the product.

The MySQL Team is constantly working on improving the software in order to allow more possibilities for the user, to enhance the functionality and to fix any problems with the current version. And since MySQL 5 has been around for about 3 years now, we are nearing the release of the new version, MySQL 6, which will boost several important improvements.

Before starting this preview, I would like to specify that at this very moment the current stable version of MySQL is MySQL 5.1 and that this is the General Availability release. MySQL 6.0 releases are development releases and not final ones, so if you are planning to install MySQL on your machine, it's advised that you choose MySQL 5.1, which is also the MySQL version installed on our web hosting servers. With this behind us, here is a short list of what will be introduced in the 6th revision of the MySQL software.

The Falcon Storage Engine

One of the great innovations is the new Falcon storage engine. This engine is specially designed to work with multi-core CPUs and larger memory architectures. It's designed to work with 64-bit environments using the advantages of the newer architecture. This has allowed several innovations:

• Multi Version Concurrency Control (MVCC) - it allows records to be added without the extra amount of processing time used by the system software. It also eliminates the need for locking rows or entire tables while updating.
• The optimization for modern multi-threaded environments now unlocks the possibility for faster transactions and allows the MySQL software to handle them with ease.
• A new way of data compression - data is now compressed and decompressed on the go, thus decreasing the amount of disk space used.
• The data and the data indexes are now cached, providing faster access to the information stored. There is no longer a need for the index data to be loaded.

Backup improvements

In the 6.0 version of the MySQL software, another important function is the Database_Backup and Restore statements. Again, the aim is to minimize table locking during backups and restores. Another improvement is that the backups will be generated regardless of the storage engine used.

New Unicode Character Sets Added

Another much needed addition is the support for more Unicode character sets, such as utf16, utf32, and the 4-byte utf8. This adds more flexibility to the MySQL software, allowing more and more variable information to be stored in the databases.

There have been also several optimizations and enhancements to allow faster sub queries and joins.

LOCK_TABLES Statement Innovations

Another improvement is the updated syntax for the LOCK_TABLES statement. Support for transactional table locks, which do not commit the transactions automatically, has been added. Now, when using LOCK_TABLES ...IN SHARE MODE or ...IN EXCLUSIVE MODE, tables that are not specified in the LOCK_TABLES statement can be accessed. Additionally, when statements with transactional locks are now issued, additional tables can be added to the locked set without unlocking previously locked ones. When LOCK_TABLES is used ...IN SHARE MODE or ...IN EXCLUSIVE MODE, the locked tables will remain locked until the end of the transaction.

For READ or WRITE locks, the way LOCK_TABLES behaves is unchanged.

Here is a general idea of how the LOCK_TABLES statement works:

XML Functionality Improvements

The XML functionality has also been improved and in order to enhance it even further, a new statement - LOAD_XML, has been added, which is designed to read data from an XML fire directly into a table. LOAD_XML will act as a complement to the MySQL client, when run in XML output mode and when the client is started with “- -xml” from the command shell.

The statement will support 3 XML formats:

When the attributes are column names and the attribute values are column values:

<row column1="value1" column2="value2" .../>

When the tags are column names and the content of the tags is the column value:

<row>
<column1>value1</column1>
<column2>value2</column2>
</row>

The name attributes of the <field> tags are the column names and the content of these tags are the values:

<row>
<field name='column1'>value1</field>
<field name='column2'>value2</field>
</row>

The last format is also used by other MySQL tools, for example the "mysqldump".

All of the 3 formats can be used within the same XML file - the import procedure will automatically detect the format for each row and read it correctly. Tags will be matched with the tag or attribute name and the column name.

Extended Comments Support Added

Last, but not least, support for extended comments for tables, columns and indexes, has been added to the new version.

Deprecated Constructs

With the release of a new software version, there are always some parts, which are removed or replaced. The reason for this can vary from improved functionality to security issues. The 6th revision of the MySQL software makes no exception to this rule. Here is a list of constructs, which are now labeled deprecated and removed from the MySQL core:

• The table_type system variable has been removed. It's functions are now handled by storage_engine.
• The TYPE table option which was used to specify the storage engine for CREATE TABLE or ALTER TABLE is now replaced by the ENGINE option.
• The SHOW TABLE TYPES SQL statement is now replaced with SHOW ENGINES.
• The log_bin_trust_routine_creators variable is removed. The log_bin_trust_function_creators is to be used instead.
• TIMESTAMP(N): The ability to specify a display width of N. It can now be used without N.
• The SHOW INNODB STATUS and SHOW MUTEX STATUS SQL statements are now unified under SHOW ENGINE INNODB STATUS.
• The LOAD TABLE ... FROM MASTER and LOAD DATA FROM MASTER SQL statements have been removed.
• The SHOW PLUGIN SQL statement is removed and the SHOW PLUGINS statement will take it's place.
• The BACKUP TABLE and the RESTORE TABLE SQL statements have been removed.
• The --master-xxx server options to set replication parameters is now replaced with the CHANGE MASTER TO statement.

Resources:

• MySQL alter table