The latest from the MySQL community

Welcome to this week’s Planets9s, covering all the latest resources and technologies we create around automation and management of open source database infrastructures.

*Updated with comments from MySQL at end of post. *

I’ve worked with a lot of the different flavors of SQL, including Oracle, SQL Server, Postgres, and SQLite, so I thought I knew all nuances of different SQL syntaxes. But I’ve recently been working with a project that’s using a LAMP stack, so I’ve gotten a lot more understanding of the MySQL’s caveats.

I’m working on some data manipulation that requires window functions in MySQL, and I learned the hard way that MySQL doesn’t support them.

Window functions are essential to most types of data manipulation because they allow for calculation across similar groups of rows without having to actually group the rows together and meld the data.

This kind of aggregation comes up all the time in business analysis and data science, for example, if …

Password management is an issue for many. It is not uncommon for an organization to require you to changing your password on a regular basis and many have rules on the length and content of passwords. The length and complexity (that require certain amounts of upper & lower case characters, special characters, and numerals) rules try to prevent users from using '12345', 'qwerty', or even the word 'password' itself as passwords. With MySQL 5.7 you can filter out those bad passwords, obscenities, slurs, or other words you do not want used as or part of a password.

If the STRONG password policy is set in the my.cnf file, a validate_password_dictionary_file can be specified and you can use your favorite text editor to add the words or phrases you wish to not be valid in that file. Note that words shorter than four characters are ignored. So create a file with a text editor ( mine was placed in /var/lib/mysql/dit) with …

In this blog post, we’ll look at pipelining versus parallel query execution when using X Plugin for MySQL 5.7.

In my previous blog post, I showed how to use X Plugin for MySQL 5.7 for parallel query execution. The tricks I used to make it work:

• Partitioning by hash
• Open N connections to MySQL, where N = number of CPU cores

I had to do it manually (as well as to sort the result at the end) as X Plugin only supports “pipelining” (which only saves the round trip time) and does not “multiplex” connections …

Percona announces the GA release of Percona Server 5.7.13-6 on July 6, 2016. Download the latest version from the Percona web site or from the Percona Software Repositories.

Based on MySQL 5.7.13, including all the bug fixes in it, Percona Server 5.7.13-6 is the current GA release in the Percona Server 5.7 series. Percona’s provides completely open-source and free software. All the details of the release can be found in the …

If you haven’t heard the news yet, MySQL 8.0 is apparently the next release of the world-famous database server.

Obviously abandoning plans to name the next release 5.8, Percona Server’s upstream provider relabelled all 5.8-related bugs to 8.0 as follows:

Reported version value updated to reflect release name change from 5.8 to 8.0

What will MySQL 8.0 bring to the world?

While lossless RBR has been suggested by Simon Mudd (for example), the actual feature list (except a Boost 1.60.0 upgrade!) remains a secret.

As far as bug and feature requests go, a smart google query revealed which bugs are likely …

In this post, I’ll discuss how MySQL 5.7 handles UTF8MB4 and the load data infile.

Many of my clients have told me that they do not like using the LOAD DATA INFILE statement and prefer to manually parse and load the data. The main reason they do it is issues with the character sets, specifically UTF8MB4 and the load data infile. This was surprising to me as nowadays everyone uses UTF8. MySQL 5.7 (as well as 5.6) has full support for UTF8MB4, which should fix any remaining issues (i.e., you can now load new emoji, like ).

Last week I was investigating an interesting case where we were loading data and got the following error:

mysql -e 'select version()'
+-----------+
| version() |
+-----------+
| 5.7.12    |
+-----------+
$ mysql -vvv testdb < load_data.sql
ERROR 1300 (HY000) at line 1: Invalid …

I am very happy that my talk, MySQL Parallel Replication: inventory, use-cases and limitations, is included in the Sneak Peek of Percona Live Amsterdam.  As a member of the Conference Committee, I knew this was being discussed, but I refrained from commenting on discussion about my talk and the submissions of my colleagues from Booking.com.

Mentioning the conference committee, as you can guess,

• Texas LinuxFest – July 8-9 2016 – Austin, Texas – I’ve never spoken at this event before but have heard great things about it. I’ve got a morning talk about what’s in MariaDB Server 10.1, and what’s coming in 10.2.
• db tech showcase – July 13-15 2016 – Tokyo, Japan – I’ve regularly spoken at this event and its a case of a 100% pure database conference, with a very captive audience. I’ll be talking about the lessons one can learn from other people’s database failures (this is the kind of talk that keeps on changing and getting better as the software improves).
• …

In this new webinar on July 12th, we’ll discuss the most important metrics MongoDB offers and will describe them in ordinary plain MySQL DBA language. We’ll have a look at the open source tools available for MongoDB monitoring and trending. And finally, we’ll show you how to leverage ClusterControl’s MongoDB metrics, dashboards, custom alerting and other features to track and optimize the performance of your system.

To operate MongoDB efficiently, you need to have insight into database performance. And with that in mind, we’ll dive into monitoring in this second webinar in the ‘Become a MongoDB DBA’ series.

Date, Time & Registration Europe/MEA/APAC

Tuesday, July 12th at 09:00 BST / 10:00 CEST (Germany, France, Sweden)
Register Now

North America/LatAm

Tuesday, …