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.