MySQL 8.0 official version is released


The MySQL Server Team has announced the General Availability of MySQL 8.0. You can download it here.
Quoting their blog post, some key enhancements include:
  1. SQL Window functions, Common Table Expressions, NOWAIT and SKIP LOCKED, Descending Indexes, Grouping, Regular Expressions, Character Sets, Cost Model, and Histograms.
  2. JSON Extended syntax, new functions, improved sorting, and partial updates. With JSON table functions you can use the SQL machinery for JSON data.
  3. GIS Geography support. Spatial Reference Systems (SRS), as well as SRS aware spatial datatypes,  spatial indexes,  and spatial functions.
  4. Reliability DDL statements have become atomic and crash safe, meta-data is stored in a single, transactional data dictionary. Powered by InnoDB!
  5. Observability Significant enhancements to Performance Schema, Information Schema, Configuration Variables, and Error Logging.
  6. Manageability Remote management, Undo tablespace management, and new instant DDL.
  7. Security OpenSSL improvements, new default authentication, SQL Roles, breaking up the super privilege, password strength, and more.
  8. Performance InnoDB is significantly better at Read/Write workloads, IO bound workloads, and high contention “hot spot” workloads. Added Resource Group feature to give users an option optimize for specific workloads on specific hardware by mapping user threads to CPUs.
MySQL 8.0 is said to include significant performance, security and developer productivity improvements enabling the next generation of web, mobile, embedded and Cloud applications.
You can check out the white paper to find out more about this new release.

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/