MySQL 5.7 came out with support for JSON, improved geometry, and virtual columns. Here's an example showing them all playing together.

Download citylots.json.

It comes as one big object, so we'll break it up into separate lines:
grep "^{ .type" citylots.json > properties.json

Connect to a 5.7 instance of MySQL.

CREATE TABLE citylots (id serial, j json, p geometry as (ST_GeomFromGeoJSON(j, 2)));
LOAD DATA LOCAL INFILE 'properties.json' INTO TABLE citylots (j);

A few of the rows don't contain useful data:
DELETE FROM citylots WHERE j->'$.geometry.type' IS NULL;

In …

Upgrading MySQL

NOTE: This blog is an updated version of the previously published blog, Upgrading Directly From MySQL 5.0 to 5.6 With mysqldump, modified for upgrading to 5.7.

Upgrading MySQL is a task that is almost inevitable if you have been managing a MySQL installation for any length of time.…

Recently, I happened to have an onsite engagement and the goal of the engagement was to move a database service to RDS Aurora. Like probably most of you, I knew the service by name but I couldn’t say much about it, so, I Googled, I listened to talks and I read about it. Now that my onsite engagement is over, here’s my first impression of Aurora.

First, let’s describe the service itself. It is part of RDS and, at first glance, very similar to a regular RDS instance. In order to setup an Aurora instance, you go to the RDS console and you either launch a new instance choosing Aurora as type or you create a snapshot of a RDS 5.6 instance and migrate it to Aurora. While with a regular MySQL RDS instance you can create slaves, with Aurora you can add reader nodes to an existing cluster. An Aurora cluster minimally consists of a …

Many of you know I publish a newsletter monthly. One thing I love about it is that after almost a decade of writing it regularly, the list has grown considerably. And I’m always surprised at how many former colleagues are actually reading it. So that is a really gratifying thing. Thanks to those who are, … Continue reading Should we be muddying the relational waters? Use cases for MySQL & Mongodb → …

This Log Buffer Edition covers the weekly blog posts of Oracle, SQL Server and MySQL.

Oracle:

• An Index or Disaster, You Choose (It’s The End Of The World As We Know It).
• SQL Monitoring in Oracle Database 12c.
• RMAN Full Backup vs. Level 0 Incremental.
• Auto optimizer stats after CTAS or direct loads in #Oracle 12c.
• How to move …

When tackling a new enterprise project to support a given business, you face the challenge of choosing and committing to a database platform. The choice should be the one most adequate, given the needs and requirements of the new information system and data to be hosted and managed. Typically, a number of factors should be taken into consideration like security features, storage requirements,

Previous Episodes:

• MySQL-Docker operations. - Part 1: Getting started with MySQL in Docker

After seeing the basics of deploying a MySQL server in Docker, in this article we will lay the foundations to customising a node and eventually using more than one server, so that we can cover replication in the next one.
Enabling GTID: the dangerous approach.To enable GTID, you need to set five variables in the database server:

• master-info-repository=table
• relay-log-info-repository=table
• enforce-gtid-consistency
• gtid_mode=ON
• log-bin=mysql-bin

For MySQL 5.6, you also need to set log-slave-updates, but we won't deal with such ancient versions here.
Using the method …

Some time ago, Peter Boros at Percona wrote this post: Rotating MySQL slow logs safely. It contains good info, such as that one should use the rename method for rotation (rather than copytruncate), and then connect to mysqld and issue a FLUSH LOGS (rather than send a SIGHUP signal).

So far so good. What I do not agree with is the additional construct to prevent slow queries from being written during log rotation. The author’s rationale is that if too many items get written while the rotation is in process, this can block threads. I understand this, but let’s review what actually happens.

Indeed, if one were to do lots of writes to the slow query log in a short space of time, a write could block while waiting.

Is the risk of this occurring greater during a logrotate operation? I doubt it. A FLUSH LOGS has to …

MySQL 5.7 comes with built-in JSON support, comprising two major features:

• A native JSON data type
• A set of built-in functions to manipulate values of the JSON type

Despite being added rather recently (in MySQL 5.7.8 to be precise - one point release number before the 5.7.9 GA version), I feel the JSON support so far looks rather useful. Improvements are certainly possible, but compared to for example XML support (added in 5.1 and 5.5), the JSON feature set added to 5.7.8 is reasonably complete, coherent and standards-compliant.

(We can of course also phrase …

Hi!

Thanks everyone who attended the hands-on lab session on MySQL Cluster at Oracle OpenWorld today.

The following are the links for the slides, the HOL instructions, and the HOL extra instructions.

Will try to summarize the HOL session below.

Aim of the HOL was to help attendees to familiarize with MySQL Cluster. In particular, by:

1. Learning the basics of MySQL Cluster Architecture
2. Learning the basics of MySQL Cluster Configuration and Administration
3. Learning how to start a new Cluster for evaluation purposes and how …