Tuesday, December 5, 2017

JSON_TABLE Part 2

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)

mysql> 

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)

mysql> 

Monday, December 4, 2017

JSON_TABLE

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)


mysql>

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.

How To Use NOWAIT

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

On a second connection, start a transaction and issue a SELECT query with NOWAIT.
mysql>START TRANSACTION;
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

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.

How To Use SKIP LOCKED

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>START TRANSACTION;
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>START TRANSACTION;
mysql>SELECT FROM city WHERE id > 3990 and countryCode='USA'
FOR UPDATE SKIPPED LOCKED;
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.


#!/bin/bash
file="/home/dstokes/Downloads/zips.json"
while IFS= read line
do
 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.

Tuesday, July 25, 2017

PHP and MySQL Without the SQL

Embedding Structured Query Language (SQL) within PHP, or other programming languages, has been problematic for some. Mixing two programming languages together is just plainly not aesthetically pleasing. Especially when you have a declarative language (SQL) mixed with a procedural-object oriented language. But now, with the MySQL XDevAPI PECL extension, PHP developers can now stop mixing the two languages together together.

MySQL Document Store

The MySQL Document Store eliminates the heavy burden for SQL skills. It is designed to be a high speed, schema-less data store and is based on the MySQL JSON data type. This gives you roughly a gigabyte of store in a document format to do with as needed. So you do not need to architect you data before hand when you have no idea how it will evolve. No need to normalize your data. Now behind the scenes is a power MySQL database server but you are no longer writing SQL to use it!

But Is The Code Ugly?

If you looked at previous editions of this blog then you have seen examples of using the MySQL XDevAPI PECL extension. There is another example below of how to search for the information under various keys in a JSON document. The great news is that the code is all very modern looking PHP with no messy SQL statements thumb-tacked onto the code. This should ongoing support by those with little or no SQL skills.

Previous you would have had to stick SELECT JSON_EXTRACT(doc,'Name') AS 'Country', JSON_EXTRACT(doc,geography) as 'Geo', JSON_EXTACT(doc,'geography.Region) FROM world_x WHERE _id = "USA" as a string in the PHP code. If you prefer the -> operator to replace JSON_EXTRACT, the code can be trimmed down to SELECT doc->"$.Name" AS 'Country', doc->"$.geography" AS 'Geo', doc->"$.geography.Region" FROM world_x WHERE _id = "USA".

But the XDevAPI simplifies these queries into $result = $collection->find('_id = "USA"')->fields(['Name as Country','geography as Geo','geography.Region'])->execute();. This is much easier to understand than the previous two queries for most. And this example shows how to chain down the document path as it specifies all of the geography hey's values and also just the data under geography.Region. It also show how to alias columns from the document store to a label of the developers choice.


#!/usr/bin/php
<?PHP
// Connection parameters
  $user = 'root';
  $passwd = 'hidave';
  $host = 'localhost';
  $port = '33060';
  $connection_uri = 'mysqlx://'.$user.':'.$passwd.'@'.$host.':'.$port; 
  echo $connection_uri . "\n";

// Connect as a Node Session
  $nodeSession = mysql_xdevapi\getNodeSession($connection_uri);
// "USE world_x"
  $schema = $nodeSession->getSchema("world_x");
// Specify collection to use
  $collection = $schema->getCollection("countryinfo");

// Query the Document Store
  $result = $collection->find('_id = "USA"')->fields(['Name as Country','geography as Geo','geography.Region'])->execute();

// Fetch/Display data
  $data = $result->fetchAll();
  var_dump($data);
?>

And The Output


mysqlx://root:hidave@localhost:33060
array(1) {
  [0]=>
  array(3) {
    ["Geo"]=>
    array(3) {
      ["Region"]=>
      string(13) "North America"
      ["Continent"]=>
      string(13) "North America"
      ["SurfaceArea"]=>
      int(9363520)
    }
    ["Country"]=>
    string(13) "United States"
    ["geography.Region"]=>
    string(13) "North America"
  }
}

User Guide

The MySQL Shell User Guide is a great place to start learning how to interactively start using the Document Store.

Tuesday, July 18, 2017

Using find() with the MySQL Document Store

The Video

The find() function for the MySQL Document Store is a very powerful tool and I have just finished a handy introductory video. By the way -- please let me have feed back on the pace, the background music, the CGI special effects (kidding!), and the amount of the content.

The Script

For those who want to follow along with the videos, the core examples are below. The first step is to connect to a MySQL server to talk to the world_x schema (Instructions on loading that schema at the first link above).

\connect root@localhost/world_x

db is an object to points to the world_x schema. To find the records in the countryinfo collection, use db.countryinfo.find(). But that returns 237 JSON documents, too many! So lets cut it down to one record by qualifying that we only want the record where the _id is equal to USA, db.countryinfo.find(‘_id = “USA”’)

Deeper Level items in the Document

You can reach deeper level items by providing the path to the object such as db.countryinfo.find(‘geography.Continent = “North America”). Be sure to remember Case Sensitivity!!

Limits, Offsets, and Specifications

It is very simple to place restrictions on the amount of output by post pending .limit(5).skip(6). And you can specify which parameters meet you specification by using find(‘GNP > 8000000’)

Limiting Fields

But what if you do not want all the document but just a few certain fields. Then post pend .fields([“Name”, “GNP”]) to find().

And once again you can dig deeper into the document with specifying the path, such as.fields([“Name”, “GNP”, “geography.Continent”]).sort(“GNP”).

Sorting

Yes, you can easily sort the output from find() by adding .sort(“GNP”,”Name”) at the end.

More Complex Searches

Of course you can make the find() function perform more complex dives into the data such as db.countryinfo.find(‘GNP> 500000 and IndepYear > 1500”).

Parameter Passed Values

And find of parameter passed values will be happy to find they have not been forgotten. db.countryinfo.find(“Name = :country”).bind(“Country”,”Canada”)

Sunday, July 16, 2017

MySQL Document Store Video Series

I am starting a series of videos on the MySQL Document Store. The Document Store allows those who do not know Structured Query Language (SQL) to use a database without having to know the basics of relational databases, set theory, or data normalization. The goal is to have sort 2-3 minute episodes on the various facets of the Document Store including the basics, using various programming languages (Node.JS, PHP, Python), and materializing free form schemaless, NoSQL data into columns for use with SQL.

The first Episode, Introduction, can be found here.

Please provide feedback and let me know if there are subjects you would want covered in the near future.

Thursday, July 6, 2017

MySQL Document Store Concepts for PHP Developers

Docstore versus traditional MySQL

I have had quite a few questions from PHP Developers who are very interested in the new MySQL Document Store versus the traditional use of MySQL with PHP. You used to have to write queries in Structured Query Language (SQL) by outing them in strings withing your PHP code. In the past I have heard many folks complain about having to use a programming language within a programming language. And several of you have just skipped all than an started using an ORM. And roughly two percent of the developers at conferences tell me they have had any formal training in SQL, relational theory, or sets!

So what changes with Document Store?

The first and biggest changing is that you stop writing queries in SQL. SQL is surprisingly hard for many programmers. Part of this is that SQL is a declarative language. CSS is also a declarative language. These languages describe what the desired output looks like. Most other languages assemble the parts to get the desired output.

The second is that you your code stops looking like string handing routines. Compare $result = $mysqli->query("SELECT doc FROM countryinfo WHERE _id='USA'")) of the traditional embedded SQL code embedded in the PHP code to $result = $collection->find('_id = "USA"')->execute() of the Document store.

TraditionalDocument Store

<?PHP
// Connection parameters
$host='127.0.0.1';
$user='root';
$pass='hidave';
$db  = 'world_x';

// connect to database server
$mysqli = mysqli_connect('localhost','root','hidave');

// Choose schema
$mysqli->select_db('world_x');


// send SQL query
if ($result = $mysqli->query("SELECT doc FROM countryinfo WHERE _id='USA'")) {
    $row = mysqli_fetch_row($result);
    var_dump($row);

    /* free result set */
    $result->close();
}


$mysqli->close();
?>

<?PHP
// Connection parameters
  $user = 'root';
  $passwd = 'hidave';
  $host = 'localhost';
  $port = '33060';

  $connection_uri = 'mysqlx://'.$user.':'.$passwd.'@'.$host.':'.$port; 
  

// Connect as a Node Session
  $nodeSession = mysql_xdevapi\getNodeSession($connection_uri);
// Choose schema
  $schema = $nodeSession->getSchema("world_x");
// Specify collection to use
  $collection = $schema->getCollection("countryinfo");
// Find desired record
  $result = $collection->find('_id = "USA"')->execute();
// Fetch/Display data
  $data = $result->fetchAll();
  var_dump($data);
?>

Choice

The good old mysqli extension is still a powerful way of getting data in and out of a MySQL server. But now you have another option that just may fit better with your programming paradigm.

Monday, July 3, 2017

How to Use PHP and MySQL Document Store

PHP Developers can now try the MySQL Document Store by using the MySQL X DevAPI for PHP PECL Extension. Developers in other languages have had access for a while but now PHP coders can get in on the action and use the MySQL Document Store.

What Does the Code Look Like?


#!/usr/bin/php
<?PHP
// Connection parameters
  $user = 'root';
  $passwd = 'S3cret#';
  $host = 'localhost';
  $port = '33060';
  $connection_uri = 'mysqlx://'.$user.':'.$passwd.'@'.$host.':'.$port;
  echo $connection_uri . "\n";

// Connect as a Node Session
  $nodeSession = mysql_xdevapi\getNodeSession($connection_uri);
// "USE world_x"
  $schema = $nodeSession->getSchema("world_x");
// Specify collection to use
  $collection = $schema->getCollection("countryinfo");
// SELECT * FROM world_x WHERE _id = "USA"
  $result = $collection->find('_id = "USA"')->execute();
// Fetch/Display data
  $data = $result->fetchAll();
  var_dump($data);
?>
Well, PHP code is PHP code. The big changes is that the developer no longer needs to use Structured Query Language to talk with the database. Now one connects to the schema of choice, sets the collection to use used, and then finds the record(s) of choice. Zero SQL involved.

PECL Extension

PECL is the PHP Community Library which houses all sorts of treasures. Now it also houses the MySQL XDevAPI extension. I have had the best luck with downloading the software and building it on my system following the directions in the README file. This is not an easy build but keep plugging and you will get it built.

Output


dstokes@davelaptop:~/phpxdev$ php 001.php
mysqlx://root:hidave@localhost:33060
array(1) {
  [0]=>
  array(7) {
    ["GNP"]=>
    int(8510700)
    ["_id"]=>
    string(3) "USA"
    ["Name"]=>
    string(13) "United States"
    ["IndepYear"]=>
    int(1776)
    ["geography"]=>
    array(3) {
      ["Region"]=>
      string(13) "North America"
      ["Continent"]=>
      string(13) "North America"
      ["SurfaceArea"]=>
      int(9363520)
    }
    ["government"]=>
    array(2) {
      ["HeadOfState"]=>
      string(14) "George W. Bush"
      ["GovernmentForm"]=>
      string(16) "Federal Republic"
    }
    ["demographics"]=>
    array(2) {
      ["Population"]=>
      int(278357000)
      ["LifeExpectancy"]=>
      float(77.099998474121)
    }
  }
}

Next Time

More PHP and MySQL Document Store code!

Monday, June 26, 2017

Indexing the MySQL Document Store

Indexing and the MySQL Document Store

The MySQL Document Store allows developers who do not know Structured Query Language (SQL) to use MySQL as a high efficient NoSQL document store. It has several great features but databases, NoSQL and SQL, have a problem searching through data efficiently. To help searching, you can add an index on certain fields to go directly to certain records. Traditional databases, like MySQL, allow you to add indexes and NoSQL databases, for example MongoDB, lets you add indexes. The MySQL Document Store also allows indexing.

So lets take a quick look at some simple data and then create an index.

mysql-js> db.foo.find()
[
    {
        "Name": "Carrie",
        "_id": "888881f14651e711940d0800276cdda9",
        "age": 21
    },
    {
        "Name": "Alex",
        "_id": "cc8a81f14651e711940d0800276cdda9",
        "age": 24
    },
    {
        "Last": "Stokes",
        "Name": "Dave",
        "_id": "davestokes"
    }
]
3 documents in set (0.01 sec)

mysql-js> db.foo.createIndex("ageidx").field("age","INTEGER", false).execute()
Query OK (0.01 sec)

The _id field was already indexed by default and I chose the age key for a new index. By the way you can crate UNIQUE and NON UNIQUE indexes. The arguments for the createIndex function are as follows. The first is the key in the JSON data to index. Second comes the index data type and age is an integer. And the third specifies if NOT NULL is supported and setting it to false means the column can contain NULL. BTW note that the last record has no age key which would be noted as a null; so if some of your records do not have the key to be indexed you should have this set to false.

So What Happened?

So lets take a look at what happened behind the scenes, using SQL.
mysql> DESC foo;
+------------------------------------------------+-------------+------+-----+---------+-------------------+
| Field                                          | Type        | Null | Key | Default | Extra             |
+------------------------------------------------+-------------+------+-----+---------+-------------------+
| doc                                            | json        | YES  |     | NULL    |                   |
| _id                                            | varchar(32) | NO   | PRI | NULL    | STORED GENERATED  |
| $ix_i_F177B50B40803DD7D3962E25071AC5CAA3D1139C | int(11)     | YES  | MUL | NULL    | VIRTUAL GENERATED |
+------------------------------------------------+-------------+------+-----+---------+-------------------+
3 rows in set (0.00 sec)

A VIRTUAL generated column was created. You may recall that virtual generated columns are not created until referenced, hence do not take up the space of a stored generated column. So what if $ix_i_F177B50B40803DD7D3962E25071AC5CAA3D1139C is not as human friendly as ageidx.

So lets try a search.

mysql-js> db.foo.find("age = 24")
[
    {
        "Name": "Alex",
        "_id": "cc8a81f14651e711940d0800276cdda9",
        "age": 24
    }
]
1 document in set (0.00 sec)

Sadly there is no corresponding function to the EXPLAIN SQL command. Which means there is no nice and easy way to see how much the index gains us in terms of performance.

Drop the index

But what if you want to remove that new index? Well, it is as simple as creating the index in the first place.

mysql-js> db.foo.dropIndex("ageidx").execute()
Query OK (0.01 sec)

Monday, June 19, 2017

Update on MySQL Document Store and Node.JS

Opps!

I admit my JavaScript skills are rusty and dusty. Plus I am new to Node.JS. So yesterdays blog drew some very helpful comments from two of MySQL's best. Johannes Schlüter and Rui Quelhas let me know there was a better way to code the example. Much thanks to them.

Better Example

// Simple example to grap one record and print it
const mysqlx = require('@mysql/xdevapi');
const options = {
  host: 'localhost',
  port: 33060, // should be a number
  dbUser: 'root',
  dbPassword: 'Hell0Dave!'
};

mysqlx
  .getSession(options)
  .then (session => {
  var schema = session.getSchema('world_x');

//equivilent of SELECT doc FROM countryinfo where _id = 'USA'
  var coll = schema.getCollection('countryinfo');
  var query = "$._id == 'USA'";

      // Print doc 
     return Promise.all([
       coll.find(query).execute(function (doc) {
       console.log(doc);
      }),
      session.close()
     ]);
    })
    .catch(err => {
        console.log(err.message);
        console.log(err.stack);
    });

Modern Code

This cleans up the error catching and the Promise.all helps put all the heavy lifting together.

And the PHP XDevAPI is in PECL

And you can access the XDevAPI from PHP using the PECL Extension!

Sunday, June 18, 2017

MySQL Document Store and Node.JS

JavaScript and MySQL

Last time I looked at using the new MySQL Shell in JavaScript mode to access documents. I promised that I would look at indexes this time but a few folks wanted to see how to use the MySQL Document Store with Javascript -- the language not the MySQL Shell mode-- specifically with the Node.JS connector. And they ask for a very simple example. So Indexes in the future, Node.JS now.

So this is a simple on using the MySQL Document Store from Node.JS.

Install

Download the Node.JS connector and then follow the very simple installation instructions. And use your OS's instictions for installing Node.JS.

The documentation on using the Node.JS Connector with the XDevAPI is not written for folks just starting with either the API or Node.JS.

Example


const mysqlx = require('@mysql/xdevapi');

mysqlx.getSession({             // Create session
  host: 'localhost',
  port: '33060',
  dbUser: 'root',
  dbPassword: 'Hell0Dave!'
}).then(function (session) {    // USE world_x
  var schema = session.getSchema('world_x');

//equivilent of SELECT doc FROM countryinfo where _id = 'USA'
  var coll = schema.getCollection('countryinfo');
  var query = "$._id == 'USA'";

      // Print doc 
  coll.find(query).execute(function (doc) {
    console.log(doc);
  })
  .catch(function (err) {
    console.log(err.message);
    console.log(err.stack);
  });
  session.close();
}).catch(function (err) {
  console.log(err.message);
  console.log(err.stack);
});

Output


node countryinfo.js
{ GNP: 8510700,
  _id: 'USA',
  Name: 'United States',
  IndepYear: 1776,
  geography: 
   { Region: 'North America',
     Continent: 'North America',
     SurfaceArea: 9363520 },
  government: 
   { HeadOfState: 'George W. Bush',
     GovernmentForm: 'Federal Republic' },
  demographics: { Population: 278357000, LifeExpectancy: 77.0999984741211 } }

Wrap-up

So that is a simple of an example that I could think of. I plan to explore more of using the XDevAPI (and fingers crossed the PHP version appears soon) and let me know if there are anything you want to see. And I will get to indexes while I am in Glasgow next week.

Wednesday, June 14, 2017

MySQL Document Store: Getting Started

MySQL's new NoSQL database is quite a big change for all used to using a SQL speaking relational database. The Document Store makes use of the new MySQL Shell, the new X Protocol, and the new X Devapi to provide a NoSQL document storage service. This provides a way for developers to have a schema-less, NoSQL data store to build services and applications is a quick fashion without having to design a schema, normalize data, develop queries in SQL, or any of the rest of the overhead needed to create a relational databases. Behind the scenes and invisible to the developer is that the schema-less data is stores in a JSON type column in a MySQL instance.

Please note that I am using JavaScript Mode (instead of SQL or Python) for these examples. No special reason.

First Step

The new MySQL Shell is a separate install from the server software. Once installed it will provide the ability to use MySQL as a document store.

The db object is how we can communicate to the server and the world_x data. Now we can create a simple collection.

Whoa! Something is wrong, isn't it?

The Document Store adds an _id column automatically. You can specify your own _id but be warned it is defined by default as an UNIQUE index, so no duplicates. The InnoDB storage engine desires a index and will pick a column to index if you do not specify one (usually not a great choice in a column BTW). But JSON columns can not be directly indexed. But do not worry about the details but do note that the _id column is there.

Also notice the order of the data returned. The MySQL JSON data type organizes the data in a special binary format for faster look ups and the side effect is the alphabetizing of the keys in the key/value pairs.

Filters

Filters can be added to find specific records and to return just the desired information (default is like a SELECT *).

You can bin values to variables to separate the data from the condition.

Modify Data

If you are used to UPDATE set x=y WHERE z=n then the next part may look a little odd.
You can add more than simple key/value pairs. Here is an example of adding an array.
And of course you need the compliment to set, unset.

We can also add to or remove items for arrays within the data. The $ is short hand for the 'current document'.

And of course db.foobar.remove() would delete all the records in the collection.

Next Time: Indexes and Documents

This time we skimmed the surface of the MySQL Document Store but next time we will look at getting some extra performance by using indexes.

Wednesday, May 24, 2017

Import a JSON Data Set into MySQL

JSON Data Into MySQL

JSON is now a very hot format for sharing data and MySQL's 5.7 Data Set is now a very hot way of storing that data. But I have had some requests on getting a raw JSON data set into MySQL. So lets start with Global Airfields data from the Awesome JSON Datasets collection.

airfields.json

The data is in a file named airfields.json and comes as one very long line. MySQl has now way of taking out the individual JSON documents from that string and putting them in a row. The jq tool will let use see the data broken down into objects. Try jq -C '.[]' airfields.json and you will see individual documents colorized.

{
  "ID": "LFOI",
  "ShortName": "ABBEV",
  "Name": "ABBEVILLE",
  "Region": "FR",
  "ICAO": "LFOI",
  "Flags": 72,
  "Catalog": 0,
  "Length": 1260,
  "Elevation": 67,
  "Runway": "0213",
  "Frequency": 0,
  "Latitude": "N500835",
  "Longitude": "E0014954"
}
{
  "ID": "LFBA",
  "ShortName": "AGENL",
  ....
  "Latitude": "N492658",
  "Longitude": "E0060730"
}

So now we can see the key/value pairs in each objects. So lets save that information into a text file but use the -M not -C option by typing jq -M '.[]' airfields.json > af.

The airport Table

We will need a simple table as in CREATE TABLE airport (doc JSON); to hold the data. Now the data set is fairly small and an text editor like vi can be used to turn the af into a SQL statement. The first line should read INSERT INTO airport (doc) VALUES ('{. The very last line needs to be }');. The second through last {s need to changed to ('{ and the }s (save the last one) need to be come }').

Login to MySQL and type SOURCE af to load the data. I used the s or search operator in vi but I could have used another editor like sed. Then it is time to feed the data into new table.

mysql> source afm
Query OK, 1218 rows affected (0.16 sec)
Records: 1218  Duplicates: 0  Warnings: 0

'
mysql> SELECT doc FROM airport LIMIT 1;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| doc                                                                                                                                                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"ID": "LFOI", "ICAO": "LFOI", "Name": "ABBEVILLE", "Flags": 72, "Length": 1260, "Region": "FR", "Runway": "0213", "Catalog": 0, "Latitude": "N500835", "Elevation": 67, "Frequency": 0, "Longitude": "E0014954", "ShortName": "ABBEV"} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Indexes?

We can easily build indexes on columns built on data in the documents. But what keys exist?

mysql> SELECT JSON_KEYS(doc) FROM airport LIMIT 1;
+------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_KEYS(doc)                                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------+
| ["ID", "ICAO", "Name", "Flags", "Length", "Region", "Runway", "Catalog", "Latitude", "Elevation", "Frequency", "Longitude", "ShortName"] |
+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

We could index all or a combination of any of these keys. Please note that all the records had the same format which can be rare for schemaless data. Let's take Region for an example. Region sounds important enough to need indexing. But is it a good candidate for an index? Here we have to peer at the data and unfortunately every record has 'FR' for a value. Indexing Region does not help pare down the search for specific record or records.

What about Name? Looking at the data shows that there are no duplicates in that field. As would Latitude and Longitude. Heck, longitude and latitude are often used together so we could put both into one index.

mysql> ALTER TABLE airport add column Name CHAR(30) GENERATED ALWAYS AS (doc->"$.Name");
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX nameidx ON  airport (Name);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

And we can test the index easily.

mysql> explain select Name from airport where Name="ABBEVILLE";
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | airport | NULL       | ref  | nameidx       | nameidx | 31      | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


And then the column for Longitude and Latitude.

mysql> ALTER TABLE airport add column longlat CHAR(30) GENERATED always  AS (concat(json_unquote(doc->"$.Longitude"),"X",json_unquote(doc->"$.Latitude"))) stored;
Query OK, 1218 rows affected (0.39 sec)
Records: 1218  Duplicates: 0  Warnings: 0

The index for this new column's index is left up to you for an exercise.

But now we have a table built from raw JSON formatted data with indexes we can use for quick searches.

Tuesday, April 18, 2017

New MySQL JSON Functions (more)

MySQL 8 is going to have new batch of JSON functions and last time JSON_PRETTY() was covered in some details. The recent release of 8.0.1 provides an opportunity to try these new functions and a few that might have been missed with 8.0.0.

Unquoting

The -> shortcut for JSON_EXTRACT() was introduced with MySQL 5.7. And now there is the unquoting extraction operator or ->> to simplify things again! Think of it as JSON_UNQUOTE wrapped around JSON EXTRACT. The following there queries produce the same output.

Aggregation

The new JSON_ARRAYAGG() and JSON_OBJECTAGG() takes a column or column argument and crates an array or object.

Clear as mud?

Well, examine this example:

The two rows from table foo are combined to make a two element array.

The JSON_OBJECT() function takes pairs of columns, assumes they are a key/value pair, and combines them. Note that non-JSON columns and data from JSON columns can be combined, as well as literal strings.

Monday, April 17, 2017

Pretty JSON with JSON_PRETTY()

JSON is a great way to share data and is highly praised in being human readable. Well, compared to XML and some other standards it is easier to read. But sometimes the data looks mushed or wilted. And 'easy' is relative, right? Well, what if you had a way to make it easier to read, er, pretty?

MySQL 8.0.1 has a few new JSON functions but one of them is designed to help make your JSON data look pretty. Place JSON_PRETTY around the column desired and out comes better looking JSON data.

Wow! That is an improvement!

But wait! There's more

JSON_PRETTY can also help make non-JSON data pretty JSON. Using JSON_ARRAY() or JSON_OBJECT to create JSON-ized versions of data and then passing those to JSON_PRETTY produces the desired effect.

Cool!

Being a long time programming with languages that used a lot of braces and brackets, I have found it necessary when not using a help IDA that counted matches for brackets/braces for me to hunt for that missing or extra bracket/braces. Looking at JSON data from a simple select can quickly tire you eyes trying to guess how things are grouped in objects. But it is evident between the first and second examples how nicely JSON_PRETTY presents the data.

Wednesday, February 8, 2017

MySQL 8 Invisible Indexes

MySQL 8 features invisible indexes. But what good is an index if you can see it?

Is It Really Invisible

You probably know by now that indexes can really speed up data searches. And many of you have mastered the use of EXPLAIN to see if the optimizer is using an index for your query. Which means you should know that sometimes for some queries an index does not help.

mysql> CREATE TABLE t2 (
    ->   i INT NOT NULL,
    ->   j INT NOT NULL,
    ->   UNIQUE j_idx (j)
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values (1,2),(3,4),(5,6),(7,8);
Query OK, 4 rows affected (0.00 sec)

mysql> explain select j from t2 where j>2;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | j_idx         | j_idx | 4       | NULL |    3 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> 

So a simple select of j with values over 2 uses j_idx.

Hocus pocus - Your Index is now Invisible

But what if we are not sure if that index is really helping? Deleting an index for testing (and then rebuilding) can be a time consuming task. With MySQL 8 you simple make the index invisible.

mysql> alter table t2 alter index j_idx invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select j from t2 where j>2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> 

In this overly simple example it is easy to see that the query is not using a key, does not know of any possible keys, and had to read more rows to answer the query. So the j_idx query was helping for this case. You can also know if this is true by looking for errors occuring for queries that include index hints that refer to the invisible index, Performance Schema data shows an increase in workload for affected queries, or suddenly the query starts appearing in the slow query log.

Not for Primary keys

Any key can be made invisible except implicit or explicit primary keys. And it is storage engine neutral from MySQL 8.0.1 onward but 8.0.0 can only works with InnoDB. Please read the manual for more detail.

Presto-Change-o

But how do you reverse the invisibility?


mysql> alter table t2 alter index j_idx visible;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Monday, January 30, 2017

PHP and MySQL Basics IV -- SQL Injection and Prepared Statements

SQL Injection is a highly feared and often misunderstood problem. The basic phobia is that someone hijacks your SQL request and suddenly has full access to everything in your database. Well, it usually is not that easy and it is actually easy to avoid.

Rule 1: Never Trust User Supplied

The usual example is something like a query SELECT * FROM customer_data WHERE customer_id='$id' and the programmer was expecting an integer for the customer_id. But a dastardly use inserts some horrible SQL code to pirate the information so the query looks like SELECT * FROM customer_data WHERE customer_id=1 OR customer_id > 0 and suddenly all your customer data is out free in the universe waiting for who knows what.

The code could have checked to see if the value of customer_id was truly an integer or returning an error if not. The is_int function was designed to do just this.

if is_int($customer_id)  {
  //Do all the stuff we want to do if we have a integer
  //submitted for a customer_id
} else {
  echo "Hey! I want an INTEGER for a customer identification number!";
}
Even more dastardly and from the PHP Manual where the dastardly injector resets all the passwords:

<?php
But a malicious user subits the value ' or uid like'%admin% to $uid to change the admin's password, or simply 
sets $pwd to hehehe', trusted=100, admin='yes to gain more privileges. Then, the query will be twisted:
>?php

// $uid: ' or uid like '%admin%
$query = "UPDATE usertable SET pwd='...' WHERE uid='' or uid like '%admin%';"
// $pwd: hehehe', trusted=100, admin='yes
$query = "UPDATE usertable SET pwd='hehehe', trusted=100, admin='yes' WHERE
...;";?>

Paranoid yet? This is another case checking the values for reasonableness can save grief. Is the uid an integer, is that integer in a proper range for uids? If too low or too high, you need to suspect someone is doing something bad.

On the database side

One thing I have recommended and implemented for years is separate users for SELECTs and INSERT/UPDATE/DELETEs. It is too easy to use one connection string over and over. But if you are in a situation where you need to take 'loose' information from an user and use that information against you data base, you need to split the queries. Set up a cust_read account on the MySQL server that can read customer data ONLY. The a corresponding cust_mod for all the other queries. This way you are assured that they can not delete or update records with that account if things get compromised. Also do not user superuser accounts like root for production database interactions -- save these accounts for maintenance functions.

Bound variables

The MySQL server supports using anonymous, positional placeholder with ?. Again from the PHP Manual:

/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
Or as used in a SELECT

/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("SELECT * FROM test WHERE id = ?"))) {
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
/* Prepared statement, stage 2: bind and execute */
$id = 1;
if (!$stmt->bind_param("i", $id)) {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}

if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
} else {
    echo "Got it!!!\n";
}

Prepared Statements - Help But Not A Cure

Prepared statements make it nearly impossible to cram lots of data into one variable. That was nearly impossible.

Take the extra time to make sure that integers are really integers and that they are in the correct range of numbers. If you are looking for a string of up to say 30 characters that you do something when the length is over run. MySQL used to get a lot of flack with people running servers in less that strict mode and having excess data truncated with only a warning generated. Now sometimes that data is valuable. But if your corporate standard is to store emails in 45 characters and the user is trying to store 50, you need to programmatically warn those with long emails that their data is too long (and the Ops folks that the email to the account if going to bounce (if you do accept that truncated email)).

RTFM

The PHP Manual's section on SQL Injection is a must read. Do use separate MySQL accounts for SELECTs and INSERT/UPDATE/DELETEs. Keep asking yourself how to keep from exposing more data than the absolute minimum needed. Do not use SELECT * FROM foo but instead explicitly name the columns in your select statement; If something goes wrong you are not exposing data column that may have private information. And be paranoid -- keep asking if there is something else you can do to protect the data.

Wednesday, January 18, 2017

Python and the MySQL Document Store

The MySQL Document Store and X Devapi have a lot of very interesting features but right now my programming language of choice, PHP, is not yet supported. My Python is rusty and learning Node.JS is progressing. But the ability to search data from a database without knowing Structured Query Language (SQL) is going to appeal to many.

Example One

import mysqlx
import string

session = mysqlx.get_session({
'host': 'localhost',
'port': 33060,
'user': 'dstokes',
'password': 'Hell0Dave!'})

schema = session.get_schema('world_x');
collection = schema.get_collection('countryinfo')

print "Find three records***\n"
result = collection.find().limit(3).execute()
docs = result.fetch_all()

for i, data in enumerate(docs):
    print "{iteration}: {data}".format(iteration = i, data=data)

print "Find USA***\n"
result = collection.find('_id = "USA"').execute()
row = result.fetch_all()

for i, data in enumerate(row):
    print "{iteration}: {data}".format(iteration = i, data=data)

session.close()
Is result = collection.find().limit(3).execute() simpler than SELECT * FROM countryinfo LIMIT 3;? Maybe not. But the second query result = collection.find('_id = "USA"').execute() is more likely a better example. Now SELECT * FROM countryinfo WHERE _ID = 'USA'; is where you can start to see the value of the document store.

Variables can be bound to queries:

country = "Mexico"
result = collection.find('Name = :param').bind('param', country).execute()

Or an expanded version:

for country in ("Mexico", "Canada", "Brazil"):
    result = collection.find('Name = :param').bind('param', country).execute()

This is closer to what most programming languages teach today. SQL is a descriptive language. The differences between object-oriented/procedural languages and descriptive are subtle. But the subtlety escapes novices (and is often ignored by those at higher skill levels.

Of course it would be more efficient to make one dive into the database with a
SELECT * FROM countryinfo WHERE Name = 'Mexico' OR Name = 'Canada' OR Name = 'Brazil'
or
SELECT FROM countryinfo WHERE Name in ('Mexico', 'Canada', 'Brazil')

But those are not going to work.

Remember the data we are peeking at is in a JSON column.

So we would use
SELECT * FROM countryinfo where doc->"$.Name" = "Mexico" OR doc->"$.Name" = "Canada" OR doc->"$.Name" = "Brazil"
or
select * from countryinfo WHERE doc->"$.Name" IN ("Canada","Mexico","Brazil")
(note use of the -> shorthand in place of JSON_EXPLAIN)

That gets much more complicated. And complication can introduce errors. Novices should have few problems if they keep working in their chosen language without have to make a context switch mentally to write some SQL. And they days very few are being taught SQL.

Friday, January 13, 2017

PHP and MySQL Basics III -- Resulting Results

In the first two blogs entries on this series we set up a connection to MySQL and sent off a query. Now we need to get the data back from the database and into the application.

An Embarrassment of Riches

PHP has many options for what we want to do. But for the best place to start with was checking that rows were actually returned from a query. Below the results from a query are returned to a variable named $result. We can find out how many rows were returned from the server by examining $result->num_rows.
if (!$result = $mysqli->query($sql)) {
    
    // Again, do not do this on a public site, but we'll show you how
    // to get the error information
    echo "Error: Our query failed to execute and here is why: \n";
    echo "Query: " . $sql . "\n";
    echo "Errno: " . $mysqli->errno . "\n";
    echo "Error: " . $mysqli->error . "\n";
    exit;
}

// succeeded, but do we have a result?
if ($result->num_rows === 0) {
    // Oh, no rows! Sometimes that's expected and okay, sometimes
    // it is not. You decide.
    echo "No data returned.";
    exit;
}

This is a case where a programmer needs to know their data. In some cases you will not have a record or records returned because there is no data. Other times no data returned is a sign of big problems. So you have to have some education on what you expect back, and what you do not expect back.

Example

<?php
$mysqli = new mysqli("localhost", "root", "hidave", "world_x");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

/* Select queries return a resultset */
$query="SELECT Name, CountryCode, District FROM city LIMIT 10";

if ($result = $mysqli->query($query)) {

        if ($result->num_rows){
                printf("Select returned %d rows.\n", $result->num_rows);

                /* free result set */
                $result->close();
        } else {
                echo "No data returned";
        }
} else {   // if ($result)
   printf("Query failed: %s", $mysqli_error);
}

$mysqli->close();
?>

Sometime you just need the number of records, like number of outstanding customer orders. But in this case we are making sure we have some data to work with before proceedings.

So Now We Have Data

Now you have at least three choices -- rare, medium, or well done. Err, make that an associative array, an array or an object. Each have their uses and it is okay to have a favorite you use more.
$query="SELECT Name, CountryCode, District FROM city LIMIT 10";

if ($result = $mysqli->query($query)) {

        if ($result->num_rows){
                printf("Select returned %d rows.\n", $result->num_rows);
                $assoc = $result->fetch_assoc();
                $row = $result->fetch_row();
                $obj = $result->fetch_object();
        } else {
                echo "No data returned";
        }
} else {   // if ($result)
   printf("Query failed: %s", $mysqli_error);
}

So you make you choice of method and take the results. Here we use fetch_assoc(), fetch_row(), or fetch_object(). Depending on how you want to refer to the data, you use the one that fits the situation. Of course they are similar in use.

//associated array keys = column name, data = data from DB
printf("Sample assoc array %s -> %s\n", $assoc['Name'], $assoc['CountryCode']);

// simple row
printf("Sample row array %s -> %s\n", $row[0], $row[1]);

//object
printf("Sample object %s -> %s\n", $obj->Name, $obj->CountryCode);

Yes, you need to know all three as you will be looking at old code or someone else code that does not use your favorite. And sometimes you may need an object rather than a simple row.

Full Listing

<?php
$mysqli = new mysqli("localhost", "root", "hidave", "world_x");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

/* Select queries return a resultset */
$query="SELECT Name, CountryCode, District FROM city LIMIT 10";

if ($result = $mysqli->query($query)) {

        if ($result->num_rows){
                printf("Select returned %d rows.\n", $result->num_rows);
                $assoc = $result->fetch_assoc();
                $row = $result->fetch_row();
                $obj = $result->fetch_object();
        } else {
                echo "No data returned";
        }
} else {   // if ($result)
   printf("Query failed: %s", $mysqli_error);
}
//associated array keys = column name, data = data from DB
printf("Sample assoc array %s -> %s\n", $assoc['Name'], $assoc['CountryCode']);

// simple row
printf("Sample row array %s -> %s\n", $row[0], $row[1]);

//object
printf("Sample object %s -> %s\n", $obj->Name, $obj->CountryCode);

$result->close();
$mysqli->close();
?>

Wednesday, January 11, 2017

PHP and MySQL Basics II - Case Sense

Last time we set up a connection from a PHP program to a MySQL server. This time we will progress a little further in that direction.

Query

Data is asked for from the MySQL server by using a query written in a language named Structured Query Language (SQL). Now that we have a connection open to the server, we can pass out request to the server.

Manual Labor

The PHP Manual is wonderful 99% of time. If you take a peek at the page for mysqli::query there is a great example of a simple query. Many of learned to program by copying/pasting from books/manuals and this is a great us of the examples in the PHP manual. Except it may not work for you.

MySQL is usually case SeNsATiVe, so 'A' may not be the same thing as 'a'. But this is dependent to some extent on your operating system where 'A' = 'a'. I was using the example from the manual and ... it did not work.

What Happened

Here is an excerpt of the code, somewhat cut down:
<?php
$mysqli = new mysqli("localhost", "user", "secret", "world_x");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

$mysqli->close();
?>

Run the program and ... nothing.

So What Happened?

What happened is a subtle problem that novices will smack into very hard. Take a look at this section of the example.
/* Select queries return a resultset */
if ($result = $mysqli->query("SELECT Name FROM City LIMIT 10")) {
    printf("Select returned %d rows.\n", $result->num_rows);

    /* free result set */
    $result->close();
}

If you try the query SELECT Name FROM City LIMIT 10; with the MySQL command line client program you will get the answer. And the answer is:

mysql> SELECT Name FROM City LIMIT 10;
ERROR 1146 (42S02): Table 'world_x.City' doesn't exist
mysql>

I am using the new world_x example database where the city is NOT capitalized instead of the old world database where it is! This lesson can be summed as check you schema/table/column names for case sensitivity. Except that there is another problem here.

In the real world occasional the database/table/column that you carefully double checked was spelled correctly and with the proper case sensitivity will go away. It may have been renamed, deleted, munged, or what have you. What is needed is a way to check to see if there was an error if the query can not run.

Lets change the code slightly:

/* Select queries return a resultset */
if ($result = $mysqli->query("SELECT Name FROM City LIMIT 10")) {
    printf("Select returned %d rows.\n", $result->num_rows);

    /* free result set */
    $result->close();
} else {
    printf("Query failed: %s\n", $mysqli->error);
}

Always Check for Return Codes

By simply adding about 40 characters, the reliability of the program shoots up immensely AND we get an exact answer if what went wrong.
Query failed: Table 'world_x.City' doesn't exist
Same error as when we tried by query by hand. But now our code can handle this issue. We could even try to catch the error, send a note via a message queue to the operations staff about the nature of the problem, and possible limp along until things are resolved. Or we could just call exit()

When you are offered a return code be sure to check it. Yes, it may be over kill in simple examples. But the payoff comes when things go bad and you are scrambling to find out eleven months from now why your program is suddenly not working.

Tuesday, January 10, 2017

PHP and MySQL Basics

PHP and MySQL have had a long intertwined path together. I have been talking with a lot of newbies in the past several months who are trying to become PHP developers but are amazed at all the ancillary parts that go along with PHP such as unit testing, databases, JavaScript, continuous integration, and much more. Add in that there are two MySQL APIs -- PDO & MySQLi -- and an older deprecated mysql API still often found in the wild. This blog is the start of a series for new PHP developers to learn to program with a database.

Client Server Model

The PHP code when it seeks to talk to a MySQL (or most other databases) will make a connection to a port at an IP address. Usually MySQL is listening on port 3306. If you are developing an accessing a database on your local computer the IP address used will generally be at 127.0.0.1. The software that goes between the PHP application and the database is called a connector.

So your code on you local system an be talking to a database server on your local system or through a network connection. It does not matter which.

Can't connect to MySQL server on 'x.x.x.x' (111)

The Can't connect error can be especially frustrating. An experienced developer will know what to check from tears of experience. But this is a column on basics so we need to spell out the steps.
  1. Is the IP address correct? It is easy to fat finger IP address and ironically 127.0.01 on many Linux boxes will connect up to 127.0.0.1.
  2. Is there a instance of MySQL running at that IP address?
  3. Is that instance listening on the generic port 3306? Is may be running someplace else and you will have to chance down that port number.
  4. Can the MySQL command line shell or other tool connect to the instance? MySQL Workbench, PhPMyAdmin, the cli tools, and everything else authenticate through the same steps so if they work and your PHP program does not then most likely the fault is in the PHP code.

Setting up the client server connection

The PHP Manual is worth its weight in gold and you should refer to it often. Its examples are clear, or usually as clear as can be, and concise. Below is an excerpt example from the manual.

<?php

$mysqli = new mysqli("127.0.0.1", "user", "password", "database", 3306);
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

echo $mysqli->host_info . "\n";
?>

Note that the IP address, user name of "user", password of "password", and the port of 3306 will need to be changed to fit the installation. The mysqli call sets up the connection between the application and the MySQL database server.

Please note that you should protect usernames and password or any other information that could allow someone to compromise the server and data.

The if statement is invoked when there is an error code is returned from the $mysql->connect_errono call. Subsequently the error message from the server can be printed out using $mysqli->error. The error message itself can be terse but often points out what is wrong in the code.

Bad arguments

What follows below are three bad connection strings.
// Bad IP address
$mysqli = new mysqli("19.10.0.3", "root", "barfoo", "world_x", 3306);
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

// Bad account information
$mysqli = new mysqli("127.0.0.1", "root", "foobar", "world_x", 3306);
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

// Bad database specified
$mysqli = new mysqli("127.0.0.1", "root", "foobar", "world_xx\", 3306);
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

Part of mastering any computer programming language is learning to understand the error messages. The three examples above return similar but distinctly different messages.

The first of the trip provides the following error:


PHP Warning:  mysqli::__construct(): (HY000/2002): Network is unreachable in /home/dstokes/php/m02.php 
Failed to connect to MySQL: (2002) Network is unreachable

It would be nice to get more information than 'Network in unreachable' but it provides a starting point to diagnose the problem. Generally the more specific the problem, the more specific the error message.

The third of the trio attempts to connect to a database named 'world_xx' when we really wanted 'world_x'.

PHP Warning:  mysqli::__construct(): (HY000/1049): Unknown database 'world_xx' in /home/dstokes/php/m02.php
Failed to connect to MySQL: (1049) Unknown database 'world_xx'

Sadly for beginners it takes time and experience to get to the point where you can instantly look at an error and know what has gone wrong (or have a pretty good idea of what has gone wrong). But do not worry as many of us learn by correcting OUR mistakes and learning not to repeat them.

Connection Good

So after establishing a good connection to the MySQL server, we can now query it for data.
Next Time -- what happened to my query??

Friday, January 6, 2017

Using MySQL to Output JSON

MySQL has had a JSON data type since version 5.7 was released way back in '15. But did you know you could produce JSON output from non-JSON columns? It is very simple and saves a lot of time over trying to format it in your application.

World Database

We will be using the good old World database that MySQL has used for years in documentation, examples, and in the classroom. Starting with a simple query we will build up to something more complex.

SELECT Name, District, Population FROM City;

This will output the data from the table in a tabular format.

'Kabul', 'Kabol', '1780000'
'Qandahar', 'Qandahar', '237500'

Array or Object?

We have two options for composing JSON data: JSON_ARRAY and JSON_OBJECT.

Of the two, you will find JSON_ARRAY the least fussy. It will JSON-ize your data very easily. It takes a list of values or an empty list and returns a JSON array.

We add this function to our example query and it becomes SELECT JSON_ARRAY(Name, District, Population) FROM City;

And the output looks like:


'[\"Kabul\", \"Kabol\", 1780000]'
'[\"Qandahar\", \"Qandahar\", 237500]'
...

JSON_OBJECT wants key/value pairs and will complain if the key name is NULL or you have an odd number of objects. If we try SELECT JSON_OBJECT(Name, District, Population) FROM City; we will get Error Code: 1582. Incorrect parameter count in the call to native function 'JSON_OBJECT'. This fuctions sees the odd number of arguments as a 'key' and the evens as the 'value' in key/value pairs and therefore we should not have an odd number of arguments. We could stick in a dummy literal string into the select but odds are that we want the three fields specified but need to turn them into key/value pairs. So lets add 'keys' and let the database supply the values.

SELECT JSON_OBJECT('City', Name, 'Dist', District, 'Pop', Population) FROM City;,

And the output looks like:


'{\"Pop\": 1780000, \"City\": \"Kabul\", \"Dist\": \"Kabol\"}'
'{\"Pop\": 237500, \"City\": \"Qandahar\", \"Dist\": \"Qandahar\"}'
...

Conclusion

Developers need to work smarter and not harder. And I have been harping on letting the database do the heavy lifting for years. This is an example of letting the database format your information for you rather than feeding it into a function within your application. Sure you can do it but this saves you a step or two and reduces the complexity of your application.