Tag Archives: deprecation

SF MySQL Meetup Presentation: Changes in MySQL 5.7

Last Wednesday, I spoke at the San Francisco MySQL Meetup on the topic of changes coming in MySQL 5.7 (and later).  We actually went through two different slide decks; the first on features being considered for deprecation in MySQL 5.7 (or later), and the second set providing a brief overview of the new features and benefits already introduced in MySQL 5.7 via the development milestone releases (DMRs) published to date.  A big thanks to the entire SF Meetup group, and in particular the organizers (Erin, Mike and Darren), for having me.  The event was streamed and recorded, and you can view the full presentation on YouTube.  The slide deck can be found here.

The discussion around proposed deprecation was good, and this blog serves to document my own notes about what was said – giving others an opportunity to provide additional feedback.  Feel free to comment either to reinforce or offer alternative perspectives on the feedback noted.  There’s also some post-presentation clarification mixed in:

Continue reading SF MySQL Meetup Presentation: Changes in MySQL 5.7

Spring Cleaning: Useless protocol commands

In an earlier post, I commented on clients and utility programs which seem to no longer be useful, and opened (or referenced existing) public bug reports to deprecate and remove, where appropriate.  That effort came actually was the product of a different initiative:  I was looking for clients which might leverage the full spectrum of MySQL protocol commands in an effort to understand whether certain protocol commands are in use.  I thought I would share my observations, in the hope that we might also get feedback from others regarding usage of these commands. And even though it’s no longer spring (I started this post in April), I finally found time to finish this post.
Continue reading Spring Cleaning: Useless protocol commands

Deprecated in MySQL 5.6 – ignore_builtin_innodb

Back when MySQL 5.1 was first released, Oracle (which didn’t at the time own Sun or MySQL) wanted to add new InnoDB functionality faster than MySQL could effectively incorporate it into the server, and the InnoDB plugin was introduced.  It provided 5.1 users the option of deploying a more advanced version of InnoDB, or using the more conservative built-in version baked into MySQL.  By the time the plugin reached GA status, though, Oracle had announced the acquisition of Sun (and MySQL), and things started to change.  The coordination between the InnoDB development team at Oracle and the MySQL development team increased once we were all “under one roof”.  The strategic directions of InnoDB and MySQL were aligned, and the results can be seen in the tremendous work done for MySQL 5.5 and 5.6.  In fact, the InnoDB plugin was removed with MySQL 5.5:

The built-in InnoDB storage engine within MySQL is the original form of distribution for the storage engine. Contrast with the InnoDB Plugin. Starting with MySQL 5.5, the InnoDB Plugin is merged back into the MySQL code base as the built-in InnoDB storage engine (known as InnoDB 1.1).

This distinction is important mainly in MySQL 5.1, where a feature or bug fix might apply to the InnoDB Plugin but not the built-in InnoDB, or vice versa.

One artifact of the “two different InnoDB engines” introduced with the plugin remains:  the ignore_builtin_innodb configuration option.  As of 5.6.5 and later, that option is now deprecated, and has no effect:

D:\mysql-5.6.5-m8-win32>bin\mysqld –no-defaults –console –port=3307 –ignore-builtin-innodb
120531 10:54:30 [Warning] ignore-builtin-innodb is ignored and will be removed in future releases.

Since MySQL 5.5, we’ve focused on and invested in InnoDB as a core element and default storage engine for MySQL.  As such, in 5.5, we removed the InnoDB plugin.  And now with 5.6, we are eliminating the option to disable InnoDB.

 

Deprecated in MySQL 5.6 – YEAR(2)

Back in April, I issued a challenge to identify any compelling use case for the YEAR(2) data type.  Months later, I still don’t have such a use case, and the release of 5.6.6 brings deprecation of support for the YEAR(2) datatype.  Trying to create a table with a YEAR(2) column in 5.6.6 or greater will result in conversion to YEAR(4) and a warning:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.6-m9  |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE y (y YEAR(2));
Query OK, 0 rows affected, 1 warning (0.13 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 1818
Message: YEAR(2) column type is deprecated. Creating YEAR(4) column instead.
1 row in set (0.00 sec)

The change logs for 5.6.6 highlight this change, and reference the excellent documentation of YEAR(2) limitations and migration strategies.  If you’re not sure whether you have YEAR(2) columns or not, the following command will identify any such columns:

SELECT table_schema, table_name, column_name, column_type
FROM information_schema.columns
WHERE data_type = 'YEAR'
AND column_type != 'year(4)';

Note that some queries (including this one) against INFORMATION_SCHEMA tables can be slow, and may not be suitable for running in against a busy production database server.  In such instances, you may want to execute this against a slave, backup or development machine loaded with the current schema.

 

Why your pre-4.1 client won’t like MySQL 5.6

I have to think that the “Client does not support authentication protocol” error message may be the single most common error ever encountered for MySQL. While it’s not exactly coming back in 5.6, those users who have implemented workarounds in support of older client libraries will find they need to add an additional step if they upgrade to 5.6. This is because in 5.6.5, a change was made to default the secure_auth option to ON. Here’s what the manual has to say about this:

This option causes the server to block connections by clients that attempt to use accounts that have passwords stored in the old (pre-4.1) format. Use it to prevent all use of passwords employing the old format (and hence insecure communication over the network). Before MySQL 5.6.5, this option is disabled by default. As of MySQL 5.6.5, it is enabled by default; to disable it, use --skip-secure-auth.

The pre-4.1 password hashing has been deemed inadequate for years; that’s the reason password hashing was overhauled in 4.1, causing so much pain.  Hopefully, the lessons from 4.1 were applied, and people heard the message that reliance on clients supporting only the older password hashing is a bad thing, and have taken the steps needed to migrate to newer client libraries.  The workarounds for OLD_PASSWORD() and the like were meant as temporary stop-gaps, introduced in 2004 (or earlier) to prevent applications from being completely unable to access MySQL after an upgrade, when there were not yet updated client libraries.  They were not meant to become permanent deployment configurations , but it’s always tempting to not touch something once you’ve found a workaround.

It’s worth checking to see if that’s the case with your deployment.  While you’re at it, you might check for accounts that have no passwords, and just out of curiosity, list the accounts configured to use authentication plugins:

SELECT
COUNT(*),
IF(plugin = '', IF(LENGTH(password) = 16, 'OLD',
IF(password = '', 'NONE', 'NEW')), plugin) format
FROM mysql.user
GROUP BY format;

If you have any user accounts that have LENGTH(password) = 16 and a blank plugin column, that account will be unable to access MySQL 5.6.5 and higher without making some changes.  It’s strongly recommended that you migrate to a more robust password hash (and client library, where needed), but if needed, you can start MySQL with the –skip-secure-auth argument (“skip_secure_auth” for config files).
You will see warnings when you configure user accounts to use older passwords:

mysql> SET PASSWORD FOR ancient@localhost = OLD_PASSWORD('test');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 1287
Message: 'pre-4.1 password hash' is deprecated and will be
removed in a future release. Please use post-4.1 password hash instead
1 row in set (0.02 sec)

Like the change in 4.1, which trumpeted “Client does not support authentication protocol” when trying to connect without configuring the server to accept old passwords, you’ll get a distinct error message when you try to connect to a pre-4.1 account and have not  started the server with –skip-secure-auth:

D:\>mysql-5.6.5-m8-win32\bin\mysql -uancient -ptest -P3307
Warning: Using a password on the command line interface can be insecure.
ERROR 1275 (HY000): Server is running in --secure-auth mode, but 
'ancient'@'localhost' has a password in the old format; please change 
the password to the new format

In testing, I noticed a couple of other behaviors I hope we’ll tweak before 5.6 GA in order to provide better visibility to this change:

  • mysql_upgrade should report a warning when it finds a user account configured to use a pre-4.1 password (Bug#65461)
  • mysqld should warn when started with –skip-secure-auth (Bug#65462)
  • mysqld should warn (in error log) when a connection is attempted against an account configured for pre-4.1 password and –skip-secure-auth is not in effect (Bug#65463)

This is more good work from the MySQL Engineering team (yay Joro!), and pushes MySQL further from the pre-4.1 legacy of insecure passwords.  If you’re still dependent upon pre-4.1 passwords, the upgrade to 5.6 will be one more opportunity to convert your user accounts to more secure authorization options.