BT

More Enhancements for MySQL 5.7 including Computed Columns

| by Jonathan Allen Follow 550 Followers on Mar 30, 2015. Estimated reading time: 1 minute |

Continuing our coverage from last week, we turn our attention to the new security features in MySQL 5.7. First up is the removal of the mysql_old_password authentication plugin. This plugin was effectively deprecated back in the 4.x era but wasn’t officially removed until build 5.7.5. As of 5.7.6, you will also need to explicitly indicate that you are using the mysql_native_password in the mysql.user table.

Password expiration polices are now supported in MySQL. Administrators can also simply lock out an account if they need to temporarily disable a user or application’s access.

Like many products, MySQL is moving towards a “secure by default” mindset. This means that when you install MySQL using “mysqld --initialize” it will automatically create a random password for the administrator account. This password is written to the error log and must be changed the first time the administrator logs in. This installation option will not create any anonymous accounts, nor will it create a test database.

Using the GRANT statement to automatically create user accounts is deprecated. As is using GRANT to modify account properties such as authentication and SSL settings.

Data Integrity

MySQL now defaults to strict mode for transactional storage engines. While this can still be overridden using INSERT IGNORE or UPDATE IGNORE, for normal access it should dramatically reduce occurrences of silent data corruption.

The IGNORE clause is no longer supported in ALTER TABLE statements.

The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes no longer exist as separate settings. Their functionality was combined with strict mode.

Performance

For all storage engines, indexes can now be renamed without a table-copy operation.

Statements containing a GROUP BY clause no longer implicitly sort the data according to that clause. This could cause problems for applications that relied on implicit sorting.

Features

MySQL now supports computed columns, which they refer to as “Generated Columns”. The syntax is a little weird in that it can’t infer the data type from the expression, but it supports the features you would expect such as the ability to store the computed value in the table or have it computed at read time. Stored computed columns may be indexed.

HANDLER, which allows direct access to a table or index, is now supported with partitioned tables.

Rate this Article

Adoption Stage
Style

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread
Community comments

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Discuss

Login to InfoQ to interact with what matters most to you.


Recover your password...

Follow

Follow your favorite topics and editors

Quick overview of most important highlights in the industry and on the site.

Like

More signal, less noise

Build your own feed by choosing topics you want to read about and editors you want to hear from.

Notifications

Stay up-to-date

Set up your notifications and don't miss out on content that matters to you

BT