Zero to DBA Hero Track at Southeast Linuxfest

This year the Zero to DBA Hero track at the Southeas  Llinuxfest expands to a second day.  The event is free to attend but it helps if you pre register.  Here is the agenda of that you will see in Charlotte June 8th, 9th, and 10th.

MySQL Talks at SELF – Zero to DBA Hero Track Linode Ballroom

Friday

9am 20 years of MySQL, 20 years of PHP, and 10 Years of SELF -- What the heck has been going on?

Dave Stokes

10:15 Introducing the MySQL Document Store
Charles Bell, PhD

11:30 Performance Analysis and Troubleshooting Methodologies for Databases

Peter Zaitsev

1:30 MySQL Without the SQL -- Oh My!

Dave Stokes

4:00 Introducing MySQL InnoDB Cluster Charles Bell, PhD

Saturday

9am MySQL/Percona Server/MariaDB Server Security Features Overview

Colin Charles

10:15 ZFS and MySQL on Linux, The Sweet Spots

Jervin Real

11:30 The MySQL Ecosystem - Understanding It, Not Running Away From It!!

Colin Charles

1:30 MySQL Troubleshooting and Performance Optimization with PMM

Peter Zaitsev

2:45 MySQL Windowing Functions

Dave Stokes

4:00 Understanding the Licensing of Your Database Stack

Colin Charles

Sunday

9am Copyright, Open Source, the Free Market, and the History of MySQL

Alexander Rubin

MySQL is again proud to be a platinum sponsor and please drop by the MySQL booth to talk about MySQL 8, Group Replication, and the MySQL Document Store,

MySQL and JSON - A Practical Programming Guide is now available!

My book MySQL and JSON - A Practical Programming Guide is now available! My promotional copies have arrived and I will be bringing them to PHP Tek, SyntaxCon, and Southeast Linuxfest in the next two weeks.

This is a book on discovering how to use JSON with MySQL with lots of examples, samples programs, and more.  Please order your copy today. Order From Amazon

When Your JSON Key is Numeric

There was an interesting question on Stackoverflow.com on extracting values from a JSON data type column in a MySQL database.  What caught my eye was the the keys for the key/value pairs were numeric. In particular the author of the question only wanted values for the key named 74.  The sample data was fairly simple.

{ "70" : "Apple", "71" : "Peach", "74" : "Kiwi" }

I thought SELECT JSON_EXTRACT(column, '$.74') FROM table; should work but it did not. There was a complaint about an invalid path expression.

It turns out that you need to make the second argument in the function '$."74"' or SELECT JSON_EXTRACT(column,'$."74"') FROM table; 

File this under something to remember for later. :-)

MySQL InnoDB Cluster Sandbox

InnoDB Cluster is major revolution for MySQL Replication but it is often hard to test out new technologies without a major investment in time, hardware, and frustration.  But what if there was a quick and easy way to set up a test InnoDB Cluster?

Sandbox

The details on how to set up a Sandbox InnoDB Cluster  is detailed at https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-sandbox-deployment.html but for those seeking a quick and dirty example please keep reading.

Step 1 - Fire up the MySQL Shell and Crate Sandbox Instances

Start the MySQL shell and create a Sandbox instance. In this example we set up a sandbox instance at port 3310.

An InnoDB Cluster needs three nodes for fault tolerance so we need to fire up to more sandbox instances on ports 3311 and 3312.

Step 2 -- Create Cluster

Connect to one of the sandbox instances, the server of 3310 in this example. Now we use the shell to create a cluster names mydemo.

Step 3 -- Add Instances to Cluster

Now we add the other two sandbox instances to the cluster.

Check Status

And with that we can use cluster.status() to see that the sandbox InnoDB cluster is ready to use. We have two read only nodes and one read write node.

Step 4 -- Start MySQL Router

Finally we start MySQL Router!

And there is how you set up a test instance of an InnoDB cluster in just a few minutes.

MySQL Document Store Document IDs.

Yesterday I was presenting on the MySQL Document Store and was asked if the _id fields created by the server as an InnoDB primary key is a UUID.  I knew that it was not a UUID but I had to hit the documentations (https://dev.mysql.com/doc/x-devapi-userguide/en/understanding-automatic-document-ids.html) to find out what the document ID really is -- a very interesting piece of information.

The Details

 If you are inserting a document lacking a _id key, the server generates a value. The _id is 32 bits of a unique prefix (4 bytes), a time stamp (8 bytes), and serial number (16 bytes). The prefix is assigned by the InnoDB Cluster to help ensure uniqueness across a cluster. The timestamp is the encoded startup time of the server.  The serial numbers uses the auto increment offset and auto increment increment server variables .  From the manual page:

This document ID format ensures that:

The primary key value monotonically increments for inserts originating from a single server instance, although the interval between values is not uniform within a table.

When using multi-primary Group Replication or InnoDB cluster, inserts to the same table from different instances do not have conflicting primary key values; assuming that the instances have the auto_increment_* system variables configured properly.

Properties

Once set, the _id can not be set to another value. Inserting your own value overrides the server assignment. And if you attempt to insert a document with the same _id you the server will generate a duplicate primary key error.

The _id values must be always increasing and sequential for optimal InnoDB performance. The server will keep track of theses numbers across restarts.

The generated _id values for each table/collection  are unique across instances to avoid primary key conflicts and minimize transaction certification in multi-primary Group Replication or InnoDB cluster environments.

Required?

So, you are adding a document to a collection and you get an ERROR: 5115!  That means in the following cast that the _id key/value pair is needed:

JS> db.foobar.add(
-> {
->  Data: "This is a test!"
-> }
-> )
->
ERROR: 5115: Document is missing a required field

JS> db.foobar.add( 

{  Data: "This is a test!" , 

-> _id: "first"

} 

)
Query OK, 1 item affected (0.0076 sec)

Storage Engine Vendor Meeting

Oracle will be hosting a MySQL Storage Engine Vendor Meeting next week. I have invited all the usual folks who participate but with the changes in MySQL 8, I may not know if you and or your company is contemplating a new storage engine or are planned to use MySQL 8. Last year we had about ten attendees discussing various topics and I would like to have you too there is you are interested. Please contact me by close of business this Friday if you are interested and will be in the Santa Clara area.

Array Ranges in MySQL JSON

Pretend you have a JSON array of data that looks roughly like the following.

mysql> insert into x(y) values('["a","b","c","d"]');
Query OK, 1 row affected (0.10 sec)

You could get all the values from that array using $[*]

mysql> select y->"$[*]" from x;
+----------------------+
| y->"$[*]"            |
+----------------------+
| ["a", "b", "c", "d"] |
+----------------------+
1 row in set (0.00 sec)

Or the individual members of the array with an index that starts with zero.

mysql> select y->"$[0]" from x;
+-----------+
| y->"$[0]" |
+-----------+
| "a"       |
+-----------+
1 row in set (0.00 sec)

But what about the times you want the last item in the array and really do not want to loop through all the items? How about using 'last'?

mysql> select y->"$[last]" as 'last' from x;
+------+
| last |
+------+
| "d"  |
+------+
1 row in set (0.00 sec)

Holey Moley! What is this? Well Roland Bouman, who was my predecessor on the MySQL Certification Team many years ago, still contributes to MySQL on a regular basis. He put in a (feature request for a JSON range operator.
So please thank Roland when you see him!!
Be sides 'last' there is 'to' too!.

mysql> select y->'$[last - 3 to last - 1]' as 'last three' from x;
+-----------------+
| last three      |
+-----------------+
| ["a", "b", "c"] |
+-----------------+
1 row in set (0.01 sec)

You can also use the ordinal number, here $[1], to strip off the first value of the array from the rest.

mysql> select y->'$[1 to last]' as 'strip first' from x;
+-----------------+
| strip first     |
+-----------------+
| ["b", "c", "d"] |
+-----------------+
1 row in set (0.00 sec)

This makes juggling array data in JSON columns much easier.