Google Cloud SQL: MySQL on the Google Cloud Platform

Brett Hesterberg (Google) delivers his talk, "Get to know Google Cloud SQL", on DAY 3 of the Percona Live Open Source Database Conference 2017, 4/27, at Santa Clara, CA.

MySQL 8.0.1 DMR now available for download

The MySQL Development team announced that the 8.0.1 development milestone release is available for download at dev.mysql.com. (8.0.1 adds features to 8.0.0). The source code is available on GitHub.
This release makes several important changes in Unicode character set support. In particular, the default character set has changed from latin1 to utf8mb4.
Check out all the changes by reading the release notes:

MySQL 8.0 - auto increment feature gets fixed

How InnoDB initializes AUTO_INCREMENT counters is actually not a bug, but a documented mechanism. There were some complaints and even people who lost data over this.
To initialize an auto-increment counter after a server restart, InnoDB executes the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column.
SELECT MAX(ai_col) FROM table_name FOR UPDATE;
InnoDB increments the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table.
With MySQL 8.0  this behavior is changed. The current maximum auto-increment counter value is written to the redo log each time it changes and is saved to an engine-private system table on each checkpoint. These changes make the current maximum auto-increment counter value persistent across server restarts. 
This change will make the use of automatic incremented primary keys and subsequent foreign keys a lot more dependable.

New tool: GitHub's online schema migration for MySQL

Shlomi Noach from the the Github engineering team posted the release of a new tool for triggerless online schema migration with MySQL. Although it's mostly targeted at databases using a replication architecture, if you don’t have replicas, or do not wish to use them, you are still able to operate directly on the master.
In spite of being a wonderful project and being used in production by Github's engineers, beware that it's still in an early stage and comes with documented limitations.
For instance, foreign keys not supported. On this matter you would better off using pt-online-schema-change from the Percona Toolkit.
The project is hosted at this repository and  is available in binary format for Linux and Mac OS/X. You can download the release here.

Learn to stop using shiny new things and love MySQL

It's almost a year old, but this post on the Pinterest engineering blog is really great advice:
https://engineering.pinterest.com/blog/learn-stop-using-shiny-new-things-and-love-mysql

My favorite advice from there: "Keep it simple. No matter what technology you’re using, it will fail."

Happy reading.

Developing a MySQL Workbench plugin

The MySQL Workbench tool is great for development and administration tasks. Also it's available on Windows, Linux and Mac OS X which, according to information from third party sources, is more than you can say for most of the other equivalent tools. And Workbench is free.
Having said that, most of the provided functionalities are intuitive and of daily use for developer and DBA staff alike. Moving beyond this rich out-of-the-box features set, Workbench empowers it's users to extend and develop their own custom features. For this purpose, we use both of the following:

  • GRT: Generic RunTime is the internal system used by Workbench to hold model document data. It is also the mechanism by which Workbench can interact with Modules and Plugins.
  • MForms: MForms is a small GUI toolkit library written for use in the MySQL Workbench project. 
MySQL Workbench is implemented with a C++ core back-end, and a native front-end for each supported platform. The custom plugins or extensions can be developed in python, using a general module structure, saved on a file named "*_grt.py".
I've developed a small proof of concept to learn how to code a custom plugin. The purpose is to provide a simple refactoring feature for the script on the SQL editor (basically it's a find and replace):
To briefly explain the code, first we import all of the workbench (wb) modules, grt and mforms:
# import the wb module
from wb import *
# import the grt module
import grt
# import the mforms module for GUI stuff
import mforms
Then we declare the module information, its name, author and version:
# define this Python module as a GRT module
ModuleInfo = DefineModule(name="Refactor", author="mjlmo", version="0.1")
Next we specify plugin internal name, tha caption to use on the workbench menu, what input we'll need to work on and which menu we want the feature to be available in:
@ModuleInfo.plugin("wb.text.refactor", caption = "Refactor Selection", input=[wbinputs.currentQueryBuffer()],  pluginMenu= "SQL/Utilities")
@ModuleInfo.export(grt.INT, grt.classes.db_query_QueryBuffer)
Finally, we insert the code to perform tha task in hand, in this case replace a selected text on the SQL editor with a given one on an input box. If there is no text selected, a message box will appear on screen stating that. Otherwise, we proceed with the refactoring operation.
To install this plugin, simply open MySQL Workbench, choose the "Scripting > Install Plugin/Module ..." option, browse to the "refactor_grt.py" file location and open it. Close and re-open the tool. There should be a new option on the "Tools > Utilities" menu called "Refactor Selection". Also, the plugin appear on the plugin manager:

You can read another full example regarding MySQL Workbench on how to define a module and define a plugin in Python here:
http://mysqlworkbench.org/workbench/doc/


Real time query monitoring on MySQL - with 3rd party tool and without

I've tried out Idera's MySQL Query Explorer, a free tool and I found it easy to use and simple to setup. The only improvement that can be suggested is to add, on the technical requirements page, that your MySQL instance (MySQL Server version 5.5 and newer) must be running with the performance_schema turned on. Otherwise the tool will just display an empty grid. After you've successfully set things up it will display live queries being executed on all databases (or if you prefer, the selected database from a dropdown list). It'll refresh every 30 seconds or manually by pushing a refresh button:

For each captured query you can select it from the grid and choose from three options:
  • Read the full SQL statement;
  • Analyze the optimizer's execution plan;
  • Check out the statistics on each of the execution steps.
This is a nice free tool, most welcome for developers and system administrators. However, database administrators should be able to retrieve the same set of information using MySQL's built-in mechanisms. For most MySQL users, the "Explain plan" option is well known. Another fair share of users knows about profiling a given query. What might not be so evident is:
  • How to capture live queries on the database?
  • The profiling statements are deprecated as of MySQL 5.6.7, what to use instead?
To capture live queries on the MySQL instance we'll resort to the information schema. With locking and blocking information and limiting to the top 10 longest running statements, we can use a similar query to MySQL Query Explorer:
After getting our longest running queries, we'll get the execution statistics from the performance schema. I won't get into details as there is already a great post about it on Percona's blog:
Profiling MySQL queries from Performance Schema

Happy tuning!