MySQL Support Blogs

While I prepared for my Hands-On Lab about the Performance Schema at MySQL Connect last year, one of the things that occurred to me was how difficult it was quickly getting an overview of which consumers, instruments, actors, etc. are actually enabled. For the consumers things are made more complicated as the effective setting also depends on parents in the hierarchy. So my thought was: “How difficult can it be to write a stored procedure that outputs a tree of the hierarchies.” Well, simple enough in principle, but trying to be general ended up making it into a lengthy

  [Read More]

Starting with MySQL 5.6, MySQL commercial-license builds use OpenSSL.  yaSSL – previously used as the default SSL library for all builds – remains the implementation for Community (GPL) builds, and users comfortable building from source can choose to build with OpenSSL instead.  Daniel van Eeden recently requested a global variable to indicate which SSL library was used to compile the server (bug#69226), and it’s a good request.  It’s something I’ve previously requested as well, having been fooled by the use of have_openssl as a synonym for

  [Read More]

The MySQL general query log can be a useful debugging tool, showing commands received from clients.  In versions through MySQL 5.5, you could count on the GQL to log every command it received – the logging happened before parsing.  That can be helpful – for example, the GQL entries might have records of somebody unsuccessfully attempting to exploit SQL injection vulnerabilities that result in syntax exceptions.

Here’s a sample, which I’ll run in both 5.5 and 5.6 and show the resulting GQL:

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

mysql> SELECT NOTHING();
ERROR 1305 (42000): FUNCTION NOTHING does not exist
mysql> SELECT 2;
+---+
| 2 |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

In 5.5, this produces the following in the general query log:

  [Read More]

MySQL Connector/J 5.1.25 has been released, and is available in Community and Enterprise flavors on dev.mysql.com and My Oracle Support, respectively.  I’ve already noted the addition of support for connection attributes for MySQL 5.6 – 5.1.25 adds this functionality.  This release also includes a fix for Bug#68733, which caused the special light-weight ping operation to

  [Read More]

From Freenode: how do you generate a five number summary in MySQL? There is no "median" aggregate function built in. You could do some clever things involving self joins or temporary tables, or build an aggregate UDF - see the comments section in the manual for those approaches.

Here's another way using a single query. Be sure to set group_concat_max_len high enough for your data, and since it relies on string manipulation, it's probably not a good choice if your data is millions of rows.

First, a helper function to get the Nth element of a comma-delimited string, just to make the query shorter:

CREATE FUNCTION LIST_ELEM(inString text, pos int)

  [Read More]

If you are a MySQL support customer, the recent release of My Oracle Support (MOS) 6.5 has some features which may interest you (if you’re not a customer, this post likely won’t interest you). MOS 6.5 was introduced on 06 April, and with it came the ability to opt in to receive service request (SR) update details via email. This was a feature some MySQL Support customers missed after the migration to MOS. Thanks to feedback from MySQL Support customers and others with similar needs, this feature has now been implemented. Because email is an inherently insecure delivery mechanism, not all customers will wan this, and the feature requires customers to explicitly opt in before SR update content is sent via email.  Coupled with the MOS Mobile interface, Support customers have a number of flexible ways to

  [Read More]

Even frequent visitors to bugs.mysql.com can sometimes miss the little note in the bottom right corner of each page:

Page generated in 0.017 sec. using MySQL 5.6.11-enterprise-commercial-advanced-log

That text changed this past weekend, going from MySQL Enterprise 5.6.10 to 5.6.11.  But more importantly, the collection of MyISAM tables which support the bugs system were also converted to InnoDB.  There’s a little story to tell here about eating this particular helping of dogfood which also amplifies changelog comments, so here it is:

We like to keep bugs.mysql.com on a current release of

  [Read More]

Stewart Smith recently questioned the current relevance of the MERGE storage engine, and it prompted me to finish a similar recent exercise I’ve been thinking about related to MySQL clients (UPDATE: and programs).  This originally came up when I listed the contents of the MySQL bin directory:

D:\mysql-advanced-5.6.11-win32>dir bin\*.exe
Volume in drive D is Data
Volume Serial Number is 4015-B2FF

Directory of D:\mysql-advanced-5.6.11-win32\bin

04/05/2013  06:52 AM           123,392 echo.exe
04/05/2013  06:53 AM         4,696,064 innochecksum.exe
04/05/2013  06:54 AM         5,084,672 myisamchk.exe
04/05/2013  06:54 AM         4,084,736

  [Read More]

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:

• 201 individual notes in the changelog
• 198 referenced bug reports (total, not de-duplicated)
• 61 bugs from community bugs

  [Read More]

Some time ago I wrote a blog post describing a way I use to verify MySQL Server bugs. But my job consists not only of bugs which can be verified just by passing SQL queries to the server.

One of such examples is UDF bugs.

MySQL User Reference Manual is good source of information for those who want to write UDF functions, as well as book "MySQL 5.1 Plugin Development" by  Sergei Golubchik and Andrew Hutchings. But while the book describes in details how to write UDFs it was created in time when current MySQL version was 5.1 and does not contain information about how

  [Read More]

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

  [Read More]

The Call for Papers for MySQL Connect 2013 will close this Friday.  This is the premier Oracle-sponsored MySQL conference, and a great opportunity for community users and customers alike to connect with Oracle staff from engineering, support, consulting and other organizations, as well as community partners and peers.  Maybe you’d like to talk about your experiences integrating one of the new MySQL 5.6 features.  Or maybe you’d like to share best practices you’ve developed for integrating with Hadoop.  Perhaps you’ve extended MySQL and want to tell the community about it.  Take advantage of this opportunity and submit your proposal this week!

Introduced in 5.6.6, connection attributes haven’t been mentioned in any of the many overviews of new 5.6 features that I’ve seen.  You might not have even known they exist – there’s not really a “feature page” in the documentation that tells you what this is all about.  The best documentation is found in the description of the PERFORMANCE_SCHEMA tables which expose these connection attributes:

As of MySQL 5.6.6, application programs can provide key/value connection attributes to be passed to the server at connect time, using the mysql_options() and

  [Read More]

In MySQL 5.5 and earlier, the steps to resize the InnoDB log files were a bit involved and for example included manually moving the log files out of the way as InnoDB would only create new files, if none existed.

In MySQL 5.6 a not so much talked about feature is the support to resize the log files in a way much more similar to changing other settings in MySQL. Now you simply update your MySQL configuration file and restart MySQL.

Let us look at an example. In MySQL 5.5 and earlier the total size of the InnoDB log files has to be less than 4G in total, so one way of staying within this limit is to have two files each 2047M large:

innodb $ ls -1s ib_logfile*
2096132 ib_logfile0
2096144 ib_logfile1

Now update the configuration file to take advantage of the fact that MySQL 5.6 allows much larger InnoDB log files; the actual limit is

  [Read More]

It came up twice in two days: if you do not specify the user name when connecting, what gets picked?

The manual says:
"On Unix, most MySQL clients by default try to log in using the current Unix user name as the MySQL user name, but that is for convenience only."
http://dev.mysql.com/doc/refman/5.6/en/user-names.html

"The default user name is ODBC on Windows or your Unix login name on Unix."
http://dev.mysql.com/doc/refman/5.6/en/connecting.html

Let's be a little more specific. The relevant section of code is in libmysql/libmysql.c

On Linux, we check the following in this order:
- if (geteuid() == 0),

  [Read More]

With MySQL 5.6 just gone GA, I thought it would be good to take a look at the changes in options and variables that comes with the new release.

First of all, several of the existing options have get new default values. As James Day already have written a good post about that in his blog, I will refer to that instead of going through the changes. For a general overview of the new features and improvements, the recent blogs by Rob

  [Read More]

Last year, when I was speaking about MySQL performance at Devconf in Moscow, I expected my audience will be very experienced as this always happen at all PHPClub conferences. So I had to choose: either make full-day seminar and explain people every basic of performance, or rely on their knowledge and make some one and half hours seminar. I prefer short speeches, so I considered latter.

But even with such a mature audience you don't always know if they knew some or another basic thing. Like somebody can be good analyzing EXPLAIN output and other is in reading InnoDB Monitor printout. Also, native language of the audience is not English and it would be always good to have short reference to simple things, described in their native

  [Read More]

I promised to write this blog post long time ago at one of conferences in Russia. Don't know why I delayed this, but finally I did.

We, members of MySQL bugs verification group, have to verify bugs in all currently supported versions. We use not only version reported, but test in development source tree for each of supported major versions and identify recent regressions.

You can imagine that even if I would do so for simple bug report about wrong results with perfect test case, which requires me simply run few queries I would have to start 4 or more MySQL servers: one for each of currently supported versions 5.0, 5.1, 5.5 plus one for current development. And unknown number of servers if I could not repeat or if I want to check if this is regression.

Even if I have all these basic 4 servers running I still should type all these queries at least 4

  [Read More]

MySQL 5.6 is in RC state now which means it is going to be GA sooner or later.

This release contains a lot of improvements. However, since I am a support engineer, I most amazed by those which make troubleshooting easier.

So here is the list of my favorite troubleshooting improvements.

1. EXPLAIN for UPDATE/INSERT/DELETE.

This is extremely useful feature.

Although prior version 5.6 we, theoretically, could have some kind of explain for them too, for example, if convert DML queries to their SELECT equivalents, optimizer can optimize them differently.

We still could execute DELETE or UPDATE, then query Hadler_% status variables, but who wants to execute update just for testing on live database? And anyway, querying Handler_% variables we could only know if some index was used or not, but can not identify which one.

  [Read More]

Comparison of Protocols

A while ago I wrote a small random function tester to fuzz test native functions such as linestring, polygon, astext, etc.  The queries it sends are generally small (100 bytes or less) and a totally CPU bound workload, since no data/tables are accessed.

As this was pretty much an open-ended test,  simply pumping random data into the functions, I had planned to let it run for a few days and see if any problems arose.

I benchmarked all the ways to connect on windows;  TCP/IP,




  [Read More]