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.

Sunday, September 24, 2017

MySQL 8's Windowing Function Part 1

MySQL will have Windowing functions and CTEs which will mean it will be easier to do data analysis with MySQL. You can now make calculations on data from each row in a query plus rows related to that row. Windows will make it easier to group items when GROUP BY does not meet needs. This is a great breakthrough but the new documentation has a steep learning curve if you are starting from zero. Hopefully this and following blogs will make it easier to get started with Windowing Functions.

OVER & WINDOW as a keywords

Let's start with the world_x sample database. The sample below orders the city table by the CountryCode but notice the window w as (order by CountryCode) phrase. This sets up a group for analysis, or a window on the data. For this example we will get the row number, rank, and dense rank of the data in that group. So for CountryCode of ABW we get a row number of 1, rank of 1, and dense rank of 1. The dense rank, the last column, increases by one as the CountryCode increases. The rank column increases also but keeps the same number as the rest of the CountryCode group. A GROUP BY would collapse all this information to a single line for each CountryCode which is not as interesting.
mysql> select ID, Name, CountryCode, row_number() over w as 'row#', 
rank() over w as 'rank', dense_rank() over w as 'dr' from city 
window w as (order by CountryCode) limit 10;
| ID  | Name           | CountryCode | row# | rank | dr |
| 129 | Oranjestad     | ABW         |    1 |    1 |  1 |
|   1 | Kabul          | AFG         |    2 |    2 |  2 |
|   2 | Qandahar       | AFG         |    3 |    2 |  2 |
|   3 | Herat          | AFG         |    4 |    2 |  2 |
|   4 | Mazar-e-Sharif | AFG         |    5 |    2 |  2 |
|  56 | Luanda         | AGO         |    6 |    6 |  3 |
|  57 | Huambo         | AGO         |    7 |    6 |  3 |
|  58 | Lobito         | AGO         |    8 |    6 |  3 |
|  59 | Benguela       | AGO         |    9 |    6 |  3 |
|  60 | Namibe         | AGO         |   10 |    6 |  3 |
10 rows in set (0.01 sec)

Good Material

Do you know where good material on SQL Windowing functions lurk? Please send it on to me as I am having difficulty finding good novice to intermediate level training materials.

Thursday, August 10, 2017

Handy JSON to MySQL Loading Script

JSON in Flat File to MySQL Database

So how do you load that JSON data file into MySQL. Recently I had this question presented to me and I thought I would share a handy script I use to do such work. For this example I will use the US Zip (postal) codes from JSONAR. Download and unzip the file. The data file is named zips.json and it can not be bread directly into MySQL using the SOURCE command. It needs to have the information wrapped in a more palatable fashion.

head zips.json 
{ "city" : "AGAWAM", "loc" : [ -72.622739, 42.070206 ], "pop" : 15338, "state" : "MA", "_id" : "01001" }
{ "city" : "CUSHMAN", "loc" : [ -72.51564999999999, 42.377017 ], "pop" : 36963, "state" : "MA", "_id" : "01002" }
{ "city" : "BARRE", "loc" : [ -72.10835400000001, 42.409698 ], "pop" : 4546, "state" : "MA", "_id" : "01005" }
{ "city" : "BELCHERTOWN", "loc" : [ -72.41095300000001, 42.275103 ], "pop" : 10579, "state" : "MA", "_id" : "01007" }
{ "city" : "BLANDFORD", "loc" : [ -72.936114, 42.182949 ], "pop" : 1240, "state" : "MA", "_id" : "01008" }
{ "city" : "BRIMFIELD", "loc" : [ -72.188455, 42.116543 ], "pop" : 3706, "state" : "MA", "_id" : "01010" }
{ "city" : "CHESTER", "loc" : [ -72.988761, 42.279421 ], "pop" : 1688, "state" : "MA", "_id" : "01011" }
{ "city" : "CHESTERFIELD", "loc" : [ -72.833309, 42.38167 ], "pop" : 177, "state" : "MA", "_id" : "01012" }
{ "city" : "CHICOPEE", "loc" : [ -72.607962, 42.162046 ], "pop" : 23396, "state" : "MA", "_id" : "01013" }
{ "city" : "CHICOPEE", "loc" : [ -72.576142, 42.176443 ], "pop" : 31495, "state" : "MA", "_id" : "01020" }

Follow the Document Store Example

The MySQL Document Store is designed for storing JSON data and this example will follow its practices by having a two column table -- a JSON column, and another column for a primary key (remember InnoDB wants so badly to have a primary key on each table that it will create one for you but it is better practice to make it yourself; besides we want to search on the zipcode which is labeled as _id in the data. So we use a stored generated column that uses JSON_UNQUOTE(JSON_EXTRACT(doc,"$_id")) and saves that info in a column named zip.

So a simple table is created and it looks like this:

mysql> desc zipcode\g
| Field       | Type        | Null | Key | Default | Extra             |
| doc         | json        | YES  |     | NULL    |                   |
| zip         | char(5)     | NO   | PRI | NULL    | STORED GENERATED  |
2 rows in set (0.00 sec)

Handy Script

So now we have the data, we have the table, and now we need to convert the data into something MySQL can use to laod the data.

Bash is one of those shells with so many rich built-in tools that is hard to remember them all. But it does have a hand read line feature that can be used for the task.

while IFS= read line
 echo "INSERT INTO zipcode (doc) VALUES ('$line');"
done <"$file"
Run the script and output the data to a file named foo, ./loader.sh > foo. The output shows how the data is wrapped:
$head foo
INSERT INTO zipcode (doc) VALUES ('{ "city" : "AGAWAM", "loc" : [ -72.622739, 42.070206 ], "pop" : 15338, "state" : "MA", "_id" : "01001" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "CUSHMAN", "loc" : [ -72.51564999999999, 42.377017 ], "pop" : 36963, "state" : "MA", "_id" : "01002" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "BARRE", "loc" : [ -72.10835400000001, 42.409698 ], "pop" : 4546, "state" : "MA", "_id" : "01005" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "BELCHERTOWN", "loc" : [ -72.41095300000001, 42.275103 ], "pop" : 10579, "state" : "MA", "_id" : "01007" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "BLANDFORD", "loc" : [ -72.936114, 42.182949 ], "pop" : 1240, "state" : "MA", "_id" : "01008" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "BRIMFIELD", "loc" : [ -72.188455, 42.116543 ], "pop" : 3706, "state" : "MA", "_id" : "01010" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "CHESTER", "loc" : [ -72.988761, 42.279421 ], "pop" : 1688, "state" : "MA", "_id" : "01011" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "CHESTERFIELD", "loc" : [ -72.833309, 42.38167 ], "pop" : 177, "state" : "MA", "_id" : "01012" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "CHICOPEE", "loc" : [ -72.607962, 42.162046 ], "pop" : 23396, "state" : "MA", "_id" : "01013" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "CHICOPEE", "loc" : [ -72.576142, 42.176443 ], "pop" : 31495, "state" : "MA", "_id" : "01020" }');

So now the data can be loaded with mysql -u itisme test < foo.