The biggest concern with a slave is to ensure your data is consistent with the master! End of story!

3 of the biggest things I see when dealing with out-of-sync slaves:

1. Many users do not use the --read-only option on their slaves.
2. Some of those who do often have numerous users with SUPER who can still perform writes.
3. Many users simply use --slave-skip-errors=… to avoid common errors.

Of course, if you have a slave, definitely use the --read-only option.

However, SUPER users can still write on slaves with --read-only, so blindly granting SUPER to all users just to save a little time when creating users won’t help. I’d suggest to use SUPER as sparingly as possible (not to mention it’s good for security also).

And the use of --slave-skip-errors=… is generally just a quick fix to avoid errors on the slave, but a sure fire way to end up with inconsistent …

MySQL 5.7.4 was recently released (it is the latest MySQL 5.7, and is the “m14″ or “Milestone 14″ release), and is available for download here and here.

The 5.7.4 changelog begins with the following, so I felt it appropriate to include it here as well.

In Memoriam:

“This release is dedicated to the memory of two young engineers of the MySQL Engineering family, Astha and Akhila, whom we lost while they were in their early twenties. This is a small remembrance and a way to recognize your contribution to the 5.7 release. You will be missed.”

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-4.html

As for the fixes, there are …

MySQL 5.6.17 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here:

For this release, I counted 7 “Functionality Added” and/or “Incompatible Change” fixes:

1. Incompatible Change: The AES_ENCRYPT() and AES_DECRYPT() functions now permit control of the block encryption mode and take an optional initialization vector argument.
2. Incompatible Change: The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes now are deprecated and setting the sql_mode value to include any of them generates a warning. In MySQL 5.7, these modes do nothing. Instead, their effects are included in the effects of strict SQL mode (STRICT_ALL_TABLES or …

MySQL 5.5.37 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:

http://downloads.skysql.com/archive/index/p/mysql/v/5.5.37

This release, similar to the last 5.5 release, is mostly uneventful.

There was one new feature added (Solaris-specific + obscure), and only 21 bugs fixed.

The new feature is this:

• On Solaris, mysql_config –libs now includes -R/path/to/library so that libraries can be found at runtime.

Out of the 21 bugs, most were benign, but there was one definitely worth mentioning (because it is a regression bug with performance degradation):

• A regression introduced by Bug #14329288 would result in a performance degradation when a compressed table does not fit into memory. (Bug #18124788, …

In a previous post, I discussed debugging stored procedures with RESIGNAL, which is of great value when troubleshooting errors raised by your stored procedures, functions, triggers, and events as of MySQL/MariaDB 5.5.

However, as of MySQL 5.6 and MariaDB 10.0, there is GET DIAGNOSTICS, which can be used to get the exact error details as well.

RESIGNAL just outputs the error, as it comes from the server, for instance:

ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

You may not want the error just written to the console, or perhaps you want to at least control how it is written.

It’s common to see exit handler code in the following form:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT ...;
END;

Where the SELECT outputs something not very useful in many cases.

With GET DIAGNOSTICS, you can get all of the …

I was recently debugging a stored procedure and could not easily identify the underlying reason for why it was failing.

It had a standard exit handler catch-all for SQLEXCEPTION, which was:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT ...;
END;

When there was an error, it didn’t really output anything useful.

As of MySQL 5.5, there is RESIGNAL:

“RESIGNAL passes on the error condition information that is available during execution of a condition handler within a compound statement inside a stored procedure or function, trigger, or event.”

http://dev.mysql.com/doc/refman/5.5/en/resignal.html

There is also some good information about it here as well:

https://mariadb.com/kb/en/resignal/

It is very simple to use, …

I ran into an interesting issue today, while examining some EXPLAIN outputs, and wanted to share the findings, as some of this is undocumented.

Basically, you can start with a very simple InnoDB table – 2 INT columns, Primary Key (PK) on the 1st column, regular index on the 2nd:

CREATE TABLE `t1` (
  `id1` int(10) unsigned NOT NULL,
  `id2` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id1`),
  KEY `id2` (`id2`)
) ENGINE=InnoDB;

The query is:

SELECT id1 FROM t1;

This is a straight-forward query with no WHERE clause.

Given no WHERE clause, we know there will be a full table or index scan. Let’s look at EXPLAIN:

mysql> EXPLAIN SELECT id1 FROM t1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: id2
      key_len: 5
          ref: NULL
         rows: 1 …

MariaDB 10.0.9 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.0.9/

This is the second RC (“Release Candidate”) release of MariaDB 10.0, and 10th overall release of 10.0. All features planned for MariaDB 10.0 GA are included in this release.

There were 6 notable changes in MariaDB 10.0.9:

1. InnoDB upgraded to version 5.6.15
2. Extended-keys optimization is now enabled by default.
3. MariaDB can be compiled to use the system’s PCRE library.
4. Added MASTER_GTID_WAIT() and …

MariaDB 5.5.36 was recently released (it is the latest MariaDB 5.5), and is available for download here:

https://downloads.mariadb.org/mariadb/5.5.36/

This is a maintenance release, and so there are not too many big changes of note, just a number of normal bug fixes. However, there are a couple items worth mentioning:

1. Includes all bugfixes and updates from: MySQL 5.5.36
2. TokuDB is now included in RPM packages for CentOS 6 on x86-64

Note this release did not contain the bugfixes from XtraDB 5.5.36 yet. There is one bug fix in XtraDB 5.5.36 that is fairly serious, so if you are a *heavy* XtraDB+ user, I might wait for MariaDB 5.5.37 to be released, so you get this fix. I should note the bug was partially fixed in 5.5.35, and I did …

SkySQL just blogged about a tool to schedule long running MySQL jobs, prevent too many queries from running simultaneously, and stores the results in tables.  It even uses Gearman.  You will note that the article says that it uses PAQU, which uses Shard-Query.

I think PAQU was created for two reasons.  A) Shard-Query lacked support for fast aggregation of STDDEV and VARIANCE (this has been fixed), and B) their data set requires “cross-shard queries”.  From what I can see though, their type of cross-shard queries can be solved using subqueries in the FROM clause using Shard-Query, instead of using a customized (forked) version of Shard-Query.  It is unfortunate, because my recent improvements to Shard-Query have to be ported into PAQU by the PAQU authors.

I’d like to encourage you to look at Shard-Query if you need to run complex jobs in the background and get the results later.  As a bonus, you …