One of the major challenges of query optimizers is to correctly estimate how many rows qualify from each table of a join. If the estimates are wrong, the optimizer may choose a non-optimal join order.

Before MySQL 5.7, the estimated number of rows from a table only took into account the conditions from the WHERE clause that were used to set up the access method (e.g., the size of an index range scan). This often led to row estimates that were far too high, resulting in very wrong cost estimates for join plans. To improve this issue, MySQL 5.7 introduced a cost model that considered the entire WHERE condition when estimating the number of qualifying rows from each table. This model estimates the filtering effect of the table’s conditions.

…

Yesterday I was invited to speak at the “Jeudis du Libre” in Mons.

The location was very special as it was in one auditorium of Polytech, the oldest university in the city of Mons.

I presented in French two very hot topics in the MySQL ecosystem:

• MySQL InnoDB Cluster
• MySQL as Document Store with JSON datatype & X plugin

Those are very new technologies illustrating MySQL’s innovation. And of course there is much more to come with MySQL 8 !

Here are the slides if you are interested:

Jeudis du Libre – MySQL InnoDB Cluster from Frederic Descamps

…

In this blog post, I will discuss the solutions for monitoring databases (which includes alerting) I have worked with and recommended in the past to my clients. This survey will mostly focus on MySQL solutions. 

One of the most common issues I come across when working with clients is monitoring and alerting. Many times, companies will fall into one of these categories:

• No monitoring or alerting. This means they have no idea what’s going on in their environment whatsoever.
• Inadequate monitoring. Maybe people in this camp are using a platform that just tells them the database is up or connections are happening, but there is no insight into what the database is doing.
• Too much monitoring and alerting. Companies in this camp have tons of dashboards filled with graphs, and their inbox is full of alerts that get promptly ignored. This type of monitoring is just as useful as the first option. Alert …

Since MySQL 5.5, MySQL has supported the utf8mb4 character set.  With the character-set defining the repertoire of characters that can be stored (utf8mb4 can present Unicode characters from U+0000 to U+10FFFF), a collation defines how sorting order and comparisons should behave.…

Somebody asked on Freenode. I don’t know why they wanted it. How would you drop all MySQL users who do not have “GRANT ALL ON *.* … WITH GRANT OPTION”? That is, drop any users who have ‘N’ in any of the privilege columns in `mysql`.`user`.

My solution shown below. Did you think of a different approach?

My solution ▼

I used SQL to build SQL which built more SQL. Get the list of ‘priv’ columns from information_schema.columns. Build a query that looks for ‘N’ in any of those columns (it feels funny to search for a constant in a list of fields instead of a field in a list of constants, but it’s perfectly legal). Use the results to build the DROP USER statement.

mysql -BNe "SELECT CONCAT('SELECT CONCAT(''DROP USER '', QUOTE(user), ''@'', QUOTE(Host), '';'') FROM mysql.user WHERE ''N'' IN (', GROUP_CONCAT(column_name), ')') FROM information_schema.columns WHERE table_schema = 'mysql' AND …

MySQL Connector/Net 7.0.7 is the fourth development release that expands cross-platform support to Linux and OS X when using Microsoft’s .NET Core framework. Now,
.NET developers can use the X DevAPI with .NET Core and Entity Framework Core
(EF Core) 1.0 to create server applications that run on Windows, Linux and OS X.
We are very excited about this change and really look forward to your feedback on it!

MySQL Connector/Net 7.0.7 is also the sixth development release of MySQL Connector/Net to add support for the new X DevAPI. The X DevAPI enables
application developers to write code that combines the strengths of the
relational and document models using a modern, NoSQL-like syntax that
does not assume previous experience writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see
…

The 2017 MySQL Community Awards event will take place, as usual, in Santa Clara, during the Percona Live Data Performance Conference, April 2017.

The MySQL Community Awards is a community based initiative. The idea is to publicly recognize contributors to the MySQL ecosystem. The entire process of discussing, voting and awarding is controlled by an independent group of community members, typically based of past winners or their representatives, as well as known contributors.

It is a self-appointed, self-declared, self-making-up-the-rules-as-it-goes committee. It is also very aware of the importance of the community; a no-nonsense, non-political, adhering to tradition, self criticizing committee.

The Call for Nominations is open. We are seeking the community’s assistance in nominating candidates in the following categories:

MySQL Community Awards: Community Contributor of the year 2017

Welcome to another post in the series of Percona Live featured session blogs! In these blogs, we’ll highlight some of the session speakers that will be at this year’s Percona Live conference. We’ll also discuss how these sessions can help you improve your database environment. Make sure to read to the end to get a special Percona Live 2017 registration bonus!

In this Percona Live featured session, we’ll meet Evan Elias, Director of Engineering, Tumblr. His session is Automatic MySQL Schema Management with Skeema. Skeema is a new open source CLI tool for managing MySQL schemas and …

One of the services our database engineers provide is adding users to MySQL. We have some nice Chef recipes, so all I have to do is update a few files, including adding in the MySQL password hash.

Now, when I added myself, I just logged into MySQL and generated a password hash. But when my SRE (systems reliability engineer) colleague needed to generate a password, he did not have a MySQL system he could login to.

The good news is it’s easy to generate a MySQL password hash. The MySQL password hash is simply a SHA1 hash of a SHA1 hash, with * at the beginning. Which means you do not need a MySQL database to create a MySQL password hash – all you need is a programming language that has a SHA1 function (well, and a concatenate function).

And I found it, of course, on this post at StackExchange (http://unix.stackexchange.com/a/234592/217471). So you don’t have to click through, here is what it says – and I have …

In previous blogs (part 1 and part 2), we discussed how to migrate your RDS data into an EC2 instance. In the process, we managed to move our data out of RDS, but we are still running on AWS. If you would like to move your data completely out of Amazon Web Services, there’s a bit more work to do. In today’s blog post, we will show you how it can be done.

Environment introduction

The environment we’ll be working with is pretty similar to what we ended up with on our last post in the series. The only difference is that no cutover happened, as we will use the EC2 …