Tuesday, December 5, 2017


The JSON_TABLE function has some very interesting uses. JSON data is great for schemaless jobs but what about when you need to pretend you have a schema and/or need to create tables based on values in a JSON document.

Existence and Defaults

Let start with some simple data:

mysql> SELECT * FROM t1;
| _id | doc                      |
|   1 | {"x": 0, "name": "Bill"} |
|   2 | {"x": 1, "name": "Mary"} |
|   3 | {"name": "Pete"}         |
3 rows in set (0.00 sec)
We have three documents and you will notice that the third record is missing a 'x' key/value pair. We can use JSON_TABLE to provide a value when that value is missing. For this example a missing value of 'x' is given a value of 999.

mysql> select * from t1, json_table(doc,"$" columns 
   (xHasValue int path "$.x" default '999' on empty, 
    hasname char(5) exists path "$.name", 
    mojo char(5) exists path "$.mojo")) as t2;
| _id | doc                      | xHasValue | hasname | mojo |
|   1 | {"x": 0, "name": "Bill"} |         0 | 1       | 0    |
|   2 | {"x": 1, "name": "Mary"} |         1 | 1       | 0    |
|   3 | {"name": "Pete"}         |       999 | 1       | 0    |
3 rows in set (0.00 sec)


Do we have that data?

We can also use the exists qualifier to test the existence of a key. The last two line in the query exists checks for a name which does exists and reports a '1'. And check for mojo which does not exist and reports a '0'. We can of course use these binary fields in our query.

mysql> select * from t1, json_table(doc,"$" columns 
   (xHasValue int path "$.x" default '999' on empty, 
    hasname char(5) exists path "$.name", 
    mojo char(5) exists path "$.mojo")) as t2 
    WHERE hasname=1 and xHasValue=1;
| _id | doc                      | xHasValue | hasname | mojo |
|   2 | {"x": 1, "name": "Mary"} |         1 | 1       | 0    |
1 row in set (0.01 sec)


Monday, December 4, 2017


JSON data is a wonderful way to store data without needing a schema but what about when you have to yank that data out of the database and apply some sort of formatting to that data?  Well, then you need JSON_TABLE.

JSON_TABLE takes free form JSON data and applies some formatting to it.  For this example we will use the world_x sample database's countryinfo table.  What is desired is the name of the country and the year of independence but only for the years after 1992.  Sound like a SQL query against JSON data, right? Well that is exactly what we are doing.

We tell the MySQL server that we are going to take the $.Name and $.IndepYear key's values from the JSON formatted doc column in  the table, format them into a string and a integer respectively, and alias the key value's name to a table column name that we can use for qualifiers in an SQL statement.

mysql> select country_name, IndyYear from countryinfo, json_table(doc,"$" columns (country_name char(20) path "$.Name", IndyYear int path "$.IndepYear")) as stuff where IndyYear > 1992;
| country_name   | IndyYear |
| Czech Republic |     1993 |
| Eritrea        |     1993 |
| Palau          |     1994 |
| Slovakia       |     1993 |
4 rows in set, 67 warnings (0.00 sec)


So what else can JSON_TABLE do? How about default values for missing values? Or checking that a key exists in a document. More on that next time. For now if you want to try MySQL 8.0.3 with JSON_TABLES, you need to head to Labs.MySQL.COM to test this experimental feature.

Tuesday, November 7, 2017

MySQL Queries No Waiting

Last time we looked at SKIP LOCKED where rows locked by another process were skipped. NOWAIT informs the server to return immediately IF the desired rows can not be locked.


Start a connection to your MySQL 8 server, start a transaction, and make a query to lock up part of the data.
mysql>SELECT * FROM city WHERE countryCode = 'USA' FOR UPDATE;

On a second connection, start a transaction and issue a SELECT query with NOWAIT.
mysql>SELECT * FROM city WHERE countryCode = 'USA' FOR UPDATE NOWAIT;

The second connection will get a message saying 'statement aborted because lock(s) could not be acquired immediately and NOWAIT is SET

So if you can come back later to lock the records or just can not wait around for your lock to be issued, NOWAIT is the keyword to add to your queries.

Monday, November 6, 2017


SKIP LOCKED is a new feature in MySQL 8 that many will find valuable.  If allows you to not wait about for locked records and work on what is available -- the unlocked records.


The MySQL world database has 274 records in the city table where the countryCode field equals 'USA' there are 274 records. From past interactions, we somehow know there are some records with the ID field greater than 4000.

On MySQL shell number 1, start a transaction and lock some records
mysql>SELECT * FROM city WHERE ID > 4000 and countryCode = 'USA';
There will be 66 records.

On MySQL shell number number 2, start a transaction and lets try to lock the records starting at IS 3990 and up.
mysql>SELECT FROM city WHERE id > 3990 and countryCode='USA'
There will be 10 records.  The records 4000 and up are locked by the transaction on the other shell.  

So no more hanging around for locked records.  You can process what is available at the current time.  So in situations where you are vending seats, hotel rooms, rental cares, or what have you -- you can find out what records are not locked by others and process them.

Wednesday, November 1, 2017

Scotland PHP, World PHP, and PyTexas -- Support your local conferences

November is the beginning of the end of the 2017 year conference cycle. But there is still time for you to get involved!! Scotland PHP is November 4th and features top presenters in Edinburgh. Last year this show was a happy surprise to find such a vibrant local PHP community and this year will be bigger.

November 15th-17th is PHP World in Washington, D.C. -- a show that had been extremely popular since it started with the movers and shakers of the PHP world.

And PyTexas has moved to Austin and is November 18th and 19th! This show has moved from the campus of Texas A&M into UT territory and will attract developers from the bubbling Texas developer community.
If there is a conference in your area it is a big bonus for you to support it. Not only can you get exposed to new ideas, network professionally, and visit vendors, you also show to those who are not from your area or lack local knowledge that there is a a community in your area. A community worth visiting, a community with a talent pool, and a community that needs paying attention to. So what if you do not know anyone there, or are not sure what exactly all the talks are about (I try to go to one session every conference on something I know NOTHING about to help broaden my perspective). You will run into others with like interests. And if you run into those with diametrically opposite interests, they can still teach you a thing or two. Two of the three events above (Scotland PHP and PyTexas(are smaller, regional events that tell vendors like Oracle/MySQL what is going on in that area by the attendees. All three are great events and, if local, worth spending a hard earned weekend day (or two) to attend and a reasonably price. PHP World may be your regional event but it has a larger, global audience that provides a lot of information for the price. The three sets of organizers of these shows do an amazing amount of work on your behalf. The presenters have fascinating things to show you. The vendors will have solutions to your problems. And you are part of a bigger community that needs you to help represent them to the world.
If you have not been to a conference this year then please try to make these shows.

And if you can not make it to show this year, you need to be planning for 2018. This is an investment in yourself that you need to make.

Wednesday, October 4, 2017

2017 MySQL Community Team Award

The 2017 MySQL Community Team Award was presented last night at the MySQL Community Reception. As part of this event the MySQL Community Team made a presentation to Alex Rubin of Percona for solving a bug that was report over a dozen years before. The initial bug report was files September 12, 2002 MySQL Does Not Make Toast but Alex demonstrated how to solve the problem in Fixing MySQL Bug#2: now MySQL makes toast! . For this contribution and many years of long, hard work in the MySQL Community, the MySQL Community Team is providing this award to recognize this achievement (and we spent a surprising large percentage of the team budget on this award).

Tuesday, October 3, 2017

MySQL Shell with Command Completion

MySQL Shell

CLI Interfaces are usually boring, ASCII-ish functional interfaces that are as about as exciting as paint drying or end user license agreements for your rice steamer. They get the job done but no excitement. The new MySQL Shell (mysqlsh instead of mysql at the command line is a great new tool but like its predecessor it is not exactly visually dynamic.

Until Now.

At labs.mysql.com there is a new version of the MySQL Shell that adds some new functionality and some visual enticements. I was in a session at Oracle OpenWorld and was impressed by not only the visually stunning upgrade but by the fact that we now get command auto-completion!

You can login as you did with the old shell but then you see that little bit of information in the command line. Pretty, for a CLI.

Security too

So connect to a database and then check your status. And pow! your connected with SSL. I am running the MySQL 8.0.3 RC and the labs release of the shell but I did not set up SSL/TLS, but there is it.

Command Completion

And there is even command completion. Few of us cam remember all the syntax for MySQL Syntax plus all the table or schema info.Take this example. I switch into SQL mode (note the notice about loading the auto-completion information). The first query I got to SELECT * FROM and then hit a tab. Auto-completion wisely asked if I wanted all 752 possibilities. Then I entered SELECT * FROM ci before entering a tab. i was given two options, one for CIPHER and the other for the table city.

Please Test

Please download this prospective new shell from Labs.MySQL.Com, kick the tires, and send feedback.