Tag Archives: MySQL 5.6

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

MySQL, , ,

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.

 

 

See you at MySQL Innovation Day!

MySQL, ,

I’m excited to attend the MySQL Innovation Day at Oracle headquarters in Redwood Shores next Tuesday. There are some great sessions planned on MySQL advances, including:

  • InnoDB online operations
  • Global transaction IDs for replication and failover
  • NoSQL interfaces to InnoDB and NDB
  • PERFORMANCE_SCHEMA improvements
  • Optimizer enhancements

This is a great opportunity for MySQL users to get details straight from MySQL developers on upcoming features.  While the focus is entirely on what is being done in Engineering to drive MySQL forward, I’ll be around to chat about MySQL Support at the community reception in the afternoon.  If you’re a MySQL Support user, or just interested in learning more about the value MySQL Support can offer, stop by and say hi!

Registration for Innovation Day on-site remains open, so if you can make time this Tuesday, join us!  And if you can’t join us in person, there’s also the live webcast option!

Overlooked MySQL 5.6 New Features – TIMESTAMP and DATETIME improvements

MySQL,

Continuing my little tour of possibly overlooked new MySQL 5.6 features, I came across this oft-requested gem:  For eons, MySQL has supported TIMESTAMP columns that could be automatically populated at row insertion and/or row update time.  It’s a handy feature to have, but it has suffered from a pretty significant limitation – only one TIMESTAMP column per table could be automatically updated, forcing you to choose whether leverage this functionality for record creation or last updated time – or bypass the MySQL feature and write the logic to maintain a TIMESTAMP or other temporal column via your application code.  Well, all that has changed with 5.6.5, and you can now do this:

mysql> CREATE TABLE v (
->  a INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
->  b INT,
->  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
->  updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
->  );
Query OK, 0 rows affected (0.22 sec)

mysql> INSERT INTO v (a, b) VALUES (NULL, 1);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM v;
+---+------+---------------------+---------------------+
| a | b    | created             | updated             |
+---+------+---------------------+---------------------+
| 1 |    1 | 2012-05-29 11:25:20 | 0000-00-00 00:00:00 |
+---+------+---------------------+---------------------+
1 row in set (0.02 sec)

mysql> UPDATE v SET b = b+1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM v;
+---+------+---------------------+---------------------+
| a | b    | created             | updated             |
+---+------+---------------------+---------------------+
| 1 |    2 | 2012-05-29 11:25:20 | 2012-05-29 11:25:43 |
+---+------+---------------------+---------------------+
1 row in set (0.00 sec)

For the record, here’s the error message you get when you try to create the same table in 5.5:

mysql> CREATE TABLE v (
    ->  a INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->  b INT,
    ->  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ->  updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ->  );
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP
column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

In addition, 5.6.5 expands the automatic population to DATETIME columns, as well

mysql> DROP TABLE v;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE v (
->  a INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
->  b INT,
->  created DATETIME DEFAULT CURRENT_TIMESTAMP,
->  updated DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
->  );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> INSERT INTO v (a, b) VALUES (NULL, 1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM v;
+---+------+---------------------+---------+
| a | b    | created             | updated |
+---+------+---------------------+---------+
| 1 |    1 | 2012-05-29 11:39:41 | NULL    |
+---+------+---------------------+---------+
1 row in set (0.00 sec)

 

Many thanks to the MySQL Engineering team in general, and Martin in particular, for implementing this needed feature!

You can read more about this important change in Martin’s blog, the 5.6.5 change logs, and the 5.6 documentation.

Overlooked MySQL 5.6 New Features – WL#5217

MySQL, ,

There’s a lot of great new features in MySQL 5.6 DMRs – almost too many to keep track of.  And while a lot of (justified) attention is given to the headline-grabbing features of 5.6 (memcached APIs! global transaction ids! improved PERFORMANCE_SCHEMA!), I’m often curious about the new features that don’t make as big a splash.  I thought I would look at one such new feature – WorkLog #5217.  I’m not telling you what this WorkLog is yet; test your knowledge of 5.6 features by seeing if you can figure it out from the following scenario.  Imagine the following table data:

mysql> SELECT * FROM p;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|   11 |    1 |
|   21 |    1 |
+------+------+
3 rows in set (0.00 sec)

OK, try to write an UPDATE statement that increments b for the row where a = 11.  Easy, right?

UPDATE p SET b = b+1 WHERE a = 11;

OK, now do it without a WHERE clause.  Maybe that made you think for a minute, but perhaps you came up with something like this:

UPDATE p SET b = b+1 ORDER BY a = 11 DESC LIMIT 1;

Nice!  Now do it without ORDER BY or LIMIT.  Can you do it?

There’s a hint in the table structure:

mysql> SHOW CREATE TABLE p\G
*************************** 1. row ***************************
Table: p
Create Table: CREATE TABLE `p` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB) */
1 row in set (0.00 sec)

You’ve probably figured out that 5.6 adds the ability to explicitly restrict statements to specific partitions, even if you haven’t found or reviewed the excellent documentation in the manual.  In 5.6, the following works:

mysql> UPDATE p PARTITION(p1) SET b = b+1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM p;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|   11 |    2 |
|   21 |    1 |
+------+------+
3 rows in set (0.00 sec)

The documentation on this is very good, and provides some useful examples of how this might prove useful, including showing how this works not only with UPDATE statements, but INSERT, SELECT, DELETE, REPLACE and LOAD DATA/XML statements.  There’s also the WorkLog and associated bugs.mysql.com feature requests.  The documentation says the following about UPDATE statements:

UPDATE statements using explicit partition selection behave in the same way; only rows in the partitions referenced by the PARTITION option are considered when determining the rows to be updated, as can be seen by executing the following statements.

It then provides an example that is similar to the solution for the problem posed above.  I still had one question, though – what would happen if I did an UPDATE on a partition key column that moves a row from one partition to another?  I get an error:

mysql> UPDATE p PARTITION(p1) SET a = 22 WHERE a = 11;
ERROR 1745 (HY000): Found a row not matching the given partition set

But if I include both the source partition and the target partition, all’s well:

mysql> UPDATE p PARTITION(p1,p2) SET a = 22 WHERE a = 11;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

How do I know the data was actually moved from p1 to p2, and that mysqld isn’t just pulling my leg?  Easy:

mysql> SELECT * FROM p PARTITION(p2);
+------+------+
| a    | b    |
+------+------+
|   21 |    1 |
|   22 |    1 |
+------+------+

Awesome.  You might want to download the 5.6.5 Development Milestone Release yourself and try this and other new 5.6 features (you’ll need to click on the “Development Releases” tab to access it, something which took me longer to figure out than I care to admit).