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:

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 "*".
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 "" 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:

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!

Using MySQL on Openshift - Red Hat's public cloud

Developing a prototype, I found myself wanting a trustworthy cloud provider. Having heard of Openshift, I got my hands on it. It's Red Hat's Platform as a Service. With it, you can host and scale applications in a cloud environment. As a developer I found some wonderful features on the quite generous free tier. The ones I'd like to highlight are that:

  • It's based on a gear and cartridge concept that abstracts container provisioning.
  • Application deployment through Git, updating the application with each push to the remote repo. 

Cartridges are what provide the run-time for each of the supporting technologies. Remote access is through a Web console and a dedicated SSH client (RHC). For the purpose of this post, we'll go through the following scenario:
1. Creating a new application on Openshift
2. Deploying the MySQL gear
3. Deploy the phpMyAdmin gear
4. Accessing phpMyAdmin to manage the database instance
5. Port forwarding to attach a local machine MySQL administration software
For the first step, you'll first have to:
After successfully finishing the pre-requisites, you can create a new application, called "mysqllab" for our MySQL scenario, with the PHP programming language specified using the following command:
C:\mysqllab> rhc app create mysqllab php-5.4 
It will create the application with the PHP cartdrige. OpenShift provides a wide range of languages and services, deployed in applications through a cartridge. Cartridges can be web frameworks, databases, monitoring services, or connectors to external backends:
On the following step, we'll provision the MySQL gear, to provide a database server for our scenario. To add it to the "mysqllab" application, use the command:
C:\mysqllab> rhc cartridge add mysql-5.5 -a mysqllab
After the database gear creation, you're presented with the database credentials information. You can also access those credentials at any time using the Web console:
Having the database server up and running, we'll provision the phpMyAdmin cartridge:
C:\mysqllab> rhc cartridge add phpmyadmin -a mysqllab
Going over to the Web console, our gears are presented on the application. You can open phpMyAdmin from the popout link. The username and password are the same as the database credentials:
And there you have it, a MySQL instance working on Red Hat PaaS cloud:
You can save the phpMyAdmin cartridge if you forward the MySQL port and use your local admin software to attach and manage the database server. To perform port forwarding, enter on the the command line:
C:\mysqllab> rhc port-forward -a mysqllab
Afterwards, try connecting your usual DBA tool. While the command prompt window is open, the ports are forwarded to and from the local host:
If you need some help to get things going and go deeper into Red Hat's cloud platform, I recommend the book  Learning OpenShift.

Real time query monitoring on MySQL using the SYS schema

On an earlier post I wrote about real time query monitoring on MySQL with a third party tool and without one. The script is useful as it works with MySQL 5.5 and later.
However, if you're using a later version of MySQL, you should look at the SYS schema. It is a collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage. It's available to install on Github and works with versions 5.6 and higher. For MySQL 5.6 you should manually install though scripts or using MySQL Workbench 6.1 or later. Since MySQL 5.7.6 the sys schema is already included.
To start using the SYS schema to check what SQL statements are being executed on a MySQL instance, open a MySQL prompt and run the following query on the 'session' view:

To find more about this view and the SYS schema, I recommend you read the following:

Don't forget to check the documentation for a complete overview, as well as the examples on project's github page.