Tag Archives: MySQL

Not-so-light reading: 5.6.11 changelog

MySQL,

Looking for some substantial reading material as the days grow longer?  You might consider dedicating a good chunk of time to review the MySQL 5.6.11 changelog.  The MySQL Engineering team at Oracle has been busy, and it shows in this maintenance release.  Stewart Smith recently noted the growth of the code base in 5.6 compared to 5.5.  That may or may not be the best measure of productivity, but the number of fixed bugs in a maintenance release like 5.6.11 is sure a good indicator.  A few general observations based on my quick study: Continue reading Not-so-light reading: 5.6.11 changelog →

MySQL Utilities meets the world’s ugliest table

MySQL, , ,

In case you missed it, MySQL Utilities 1.3.0 (alpha) was released last week.  MySQL Utilities is a component of MySQL Workbench, but it’s been broken out into a separate download allowing command-line users access unencumbered by a pretty GUI interface.  Plus, it has some new features – most importantly to me, a utility (mysqlfrm) which can read .FRM files and produce CREATE TABLE statements as a result.  This will be very useful for recovery processes, in conjunction with InnoDB transportable tablespaces in situations where the InnoDB system tablespace is corrupted, and you don’t know the exact table definition.

When we first started testing the FRM reader, it didn’t bootstrap mysqld to decipher .FRM file contents – Chuck Bell spent hours staring into the abyss that is FRM file formats and trying to shed light in the many dark corners that exist.  We started with simple tests, like:  can it read all the .FRM files in the data/mysql directory?  Then I hacked a quick program that created tables with many different data types, modifiers and variants.  But these tests were all dwarfed by the contribution of my colleague and bug-finder extraordinaire, Shane Bester, who gave us what may well be the world’s ugliest table.  Behold:

Continue reading MySQL Utilities meets the world’s ugliest table →

InnoDB now works with read-only media

MySQL, ,

With a handful of exceptions, few people deliberately choose to deploy MySQL on read-only media – but there are cases where being able to access InnoDB data that way comes in handy.  As it happened, I had exactly this need a few months back, and the excellent InnoDB development team at Oracle has recently implemented this feature in MySQL 5.6.

Continue reading InnoDB now works with read-only media →

Using MySQL for Excel

MySQL, , ,

I recently had a need to import a bunch of data into MySQL, and for reasons I won’t get into here, LOAD DATA INFILE wasn’t working for me.  I was in a hurry to get the data into MySQL so that I could filter it, and didn’t have time to work through my LOAD DATA INFILE issues.  Looking around for another way to quickly get my tab-delimited data into MySQL quickly, I decided to use the MySQL for Excel plugin.  If you haven’t seen this yet, and you have Excel, it might be worth your while. Continue reading Using MySQL for Excel →

System user authentication plugin

MySQL, ,

I’ve been working on revising my password policy scripts, and in the process, thought about the privileges required.  My first draft added tables to the mysql system database and leveraged the root@localhost account.  I’m looking to lock that down for the next iteration.  It’s easy to move the tables and procedures out of the mysql system database into a new password_policy database, but what to do about the use of the root account? Continue reading System user authentication plugin →

Implementing a password policy in MySQL

MySQL, ,

In a previous post, I noted that the new new password verification plugin in MySQL 5.6 provides a basis for implementing a more comprehensive password policy.  Most notably, password policies include requirements around password strength, duration, and reuse.  While the password validation plugin focuses on password strength policy components, there are ways to roll your own processes in support of password expiration and reuse policy components.  Unlike the password verification plugin, the tools I will describe below don’t hook directly into account maintenance commands.

You can download the full .SQL file (in a .ZIP package) here, and I’ll walk through the various tables, procedures and events used to implement a password policy which checks password expiration and reuse below. Continue reading Implementing a password policy in MySQL →

More GlassFish loadbalancing tips for Connector/J

Java, MySQL, , , ,

Almost two weeks ago, I encouraged GlassFish users who need load-balanced JDBC connections to MySQL Cluster (or master-master replicated MySQL Server) to set the loadBalanceValidateConnectionOnSwapServer property to true in order to help ensure the connection chosen at re-balance is still usable.  That advice triggered finding a bug (14563127) which will cause the following Exception message:

No operations allowed after connection closed. Connection closed after inability to pick valid new connection during fail-over.

If you implemented the loadBalanceValidateConnectionOnSwapServer property and are seeing the above error message, updating your driver to the newly-released 5.1.22 build will likely solve this problem.  Here’s a quick look at internals of Connector/J re-balance operations, some additional configuration suggestions, and details on the bug fixed in 5.1.22: Continue reading More GlassFish loadbalancing tips for Connector/J →

New 5.6 password verification plugin (and impacts to PASSWORD() function)

MySQL, ,

The recent MySQL 5.6.6 release includes a new password verification plugin.  This is a great tool for enforcing passwords that meet certain strength thresholds.  Quoting the options from the manual, there are three different criteria levels that can be applied:

  • WEAK policy tests password length only. Passwords must be at least 8 characters long.
  • MEDIUM policy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase and uppercase character, and 1 special (nonalphanumeric) character.
  • STRONG policy adds the condition that password substrings of length 4 or longer must not match words in the dictionary file, if one has been specified.

Deprecated in MySQL 5.6 – ignore_builtin_innodb

MySQL, , ,

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.

 

Learn From MySQL Support Staff at MySQL Connect!

Java, MySQL, , , , ,

Members of the MySQL Support Team wear a number of different hats here at Oracle.  Obviously, our top priority is to provide amazing technical support that makes customers rave.  We also have a team dedicated to processing bug reports from the MySQL Community.  Some of us are active bloggers or assist on mailing lists or forums, while others find other ways to contribute to the MySQL Community.  We help out with QA and product planning, write books, and the consultative support aspects of MySQL subscriptions allow us the opportunity to help train and advise customers on best practices and successful MySQL-backed application architecture.  In short, we’re deeply invested in making sure that MySQL deployments are successful.

This year, we’re privileged to send some of these great MySQL experts to the inaugural MySQL Connect Conference this September 29-30, and it will be a great opportunity for you to experience first-hand the value that MySQL Support can provide:

Be sure to stop by the MySQL Support booth in the demo grounds at MySQL Connect and meet these MySQL experts – they’ll be happy to help you solve any of your tricky MySQL problems.

If you’re an Oracle DBA just getting started on MySQL, it’s worth the $100 fee to add MySQL Connect to your OOW registration, but Ben Krug will also be leading a session at OOW with the goal of Demystifying MySQL for Oracle DBAs and Developers.

And if California is just too far to travel, perhaps you can catch Uma Bhat‘s presentation at PyCon India, Introduction to MySQL Connector/Python.

We hope to see you at one of these upcoming conferences!