MySQL + WePay MeetUp

Description

Join this virtual meetup featuring WePay and Oracle MySQL! They will review three great talks on MySQL Compatibility Check, Running MySQL on Kubernetes and Scalable Bookkeeping.

Talk 1: MySQL Compatibility Check

Talk 2: Running MySQL on Kubernetes

Talk 3: Scalable Bookkeeping

Date & Time 

Oct 1, 2020 10:00 AM in Pacific Time (US and Canada)

Register here

Better VIEWs with the WITH CHECK OPTION

     VIEWs have been a handy feature of MySQL for many years but do you know about the WITH CHECK OPTION clause?  The WITH CHECK OPTION clause is used for a updatable views to prohibit the changes to those views that would produce rows which are not included in the defining query.

    VIEW Definition

    Since a lot of SQL novices read my blog, I'd like to start with the definition of a view "Views are stored queries that when invoked produce a result set. A view acts as a virtual table" according the MySQL Manual.

SQL > CREATE VIEW x_city AS 

            SELECT     Name, 

                       CountryCode, 

                       District 

       FROM city;

Query OK, 0 rows affected (0.0358 sec)
SQL > SELECT * FROM x_city LIMIT 4;
+----------------+-------------+----------+
| Name           | CountryCode | District |
+----------------+-------------+----------+
| Kabul          | AFG         | Kabol    |
| Qandahar       | AFG         | Qandahar |
| Herat          | AFG         | Herat    |
| Mazar-e-Sharif | AFG         | Balkh    |
+----------------+-------------+----------+
4 rows in set (0.0200 sec)
 SQL > SELECT Name, District FROM x_city limit 4;
+----------------+----------+
| Name           | District |
+----------------+----------+
| Kabul          | Kabol    |
| Qandahar       | Qandahar |
| Herat          | Herat    |
| Mazar-e-Sharif | Balkh    |
+----------------+----------+

    Views are used to obfuscate column names, capture vital queries for later reuse, and many other reasons. For the most part we can treat it as a table. The above example joins data from two tables but we can treat the view as a separate, different table.  As can be seen above we can select all the columns from the view or a subset of the columns defined in the view. 

Updatable Views

    Some views can be used to specify tables to be updated in data change statements. In the example below we will use a simple one integer column table.

.

 SQL >  create view v_2 as select * from t2;

Query OK, 0 rows affected (0.0072 sec)

 SQL > select * from v_2;

+----+

| id |

+----+

|  1 |

|  3 |

|  5 |

|  7 |

|  9 |

| 11 |

+----+

6 rows in set (0.0009 sec)

 SQL > insert into v_2 (id) values (99);

Query OK, 1 row affected (0.0060 sec)

  SQL > select * from v_2;

+----+

| id |

+----+

|  1 |

|  3 |

|  5 |

|  7 |

|  9 |

| 11 |

| 99 |

+----+

    The v_2 view updated the underlying table t2.  

WITH CHECK OPTION

    The WITH CHECK OPTION  prevents inserts to rows for which the WHERE clause in the select_statement is not true. The view v_2a only retrieves data where the id value is greater than 4. If it is used in an attempt to update a value less than 5 it will raise and error.

SQL > CREATE VIEW v_2a AS 

        SELECT * FROM t2 WHERE id > 4 WITH CHECK OPTION;

Query OK, 0 rows affected (0.0102 sec)

 SQL > SELECT * FROM v_2a;

+----+

| id |

+----+

|  5 |

|  7 |

|  9 |

| 11 |

| 99 |

+----+

5 rows in set (0.0009 sec)

 SQL > insert into v_2a (id) values (2);

ERROR: 1369: CHECK OPTION failed 'demo.v_2a'

    And note the error provides the name of the view and the schema to helpt track it down.

    For more details please refer to the manual page on The View WITH CHECK OPTION Clause

Two MySQL 8.0 Talks this week - DevOps and Features, an upcoming Podcast, and an article on JSON Document Validation

 I have two talks this week on MySQL 8.0 that you can attend virtually. And another session that is one line also plus an article.  Busy week!

First is a follow on to a earlier session I did for the good folks at cPanel want you to register here for MySQL 8.0 changes for DBAs and DevOps on August 12th, 2020  at 2PM CDT. The first talk was mainly for developers and this time I am, heavily covering InnoDB Cluster and features that are of more interest to the folks who keep MySQL instances running.

MergePHP - New MySQL Features That You May Have Missed is on Thursday August 13th at 7:00 Eastern . This is an exciting combination of three MySQL user groups who have joined together for a virtual meeting.

Then on Friday the 14th I am panelist for the JavaScript and Friends conference's panel on keeping your skills relevant in a changing world.

The gold folks at PHP Architect have published and article I

wrote on JSON Document Validation with MySQL  and we are getting setup for me to speak on their podcast on just that subject. 

Array Ranges in MySQL JSON

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

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

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

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

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

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

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

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

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

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

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

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

This makes juggling array data in JSON columns much easier.

Common Table Expressions (CTEs) Part 1

Occasionally at conference or a Meetup, someone will approach me and ask me for help with a MySQL problem.  Eight out of ten times their difficulty includes a sub query. "I get an error message about a corrugated or conflabugated sub query or some such,"  they say, desperate for help.  Usually with a bit of fumbling we can get their problem solved.  The problem is not a lack of knowledge for either of us but that sub queries are often hard to write. 

MySQL 8 will be the first version of the most popular database on the web with Common Table Expressions or CTEs.  CTEs are a way to create temporary tables and then use that temporary table for queries. Think of them as easy to write sub queries!

WITH is The Magic Word

The new CTE magic is indicated with the WITH clause.

mysql> WITH myfirstCTE 
      AS (SELECT * FROM world.city WHERE CountryCode='USA')
SELECT Name, District
FROM myfirstCTE 
ORDER BY Name
LIMIT 5;
+-------------+------------+
| Name        | District   |
+-------------+------------+
| Abilene     | Texas      |
| Akron       | Ohio       |
| Albany      | New York   |
| Albuquerque | New Mexico |
| Alexandria  | Virginia   |
+-------------+------------+
5 rows in set (0.01 sec)

mysql> 

So in the above example we create at temporary table named 'myfirstCTE' and then query from that table. The CTE itself isa very simple query, SELECT * FROM world.city. And then two columns are plucked out of 'myfirstCTE'. The CTE can even have modifiers on the query for ORDER BY, LIMIT or any other SQL qualifier.

A Little More Complex Example 

Lets try a little more complex query.

mysql> WITH secondCTE AS 
     (SELECT city.Name AS A, country.Name as B, city.District AS C 
     FROM city 
     JOIN country on (city.CountryCode=country.Code)) 
     SELECT A, B, C FROM secondCTE LIMIT 5;
+----------------+-------------+----------+
| A              | B           | C        |
+----------------+-------------+----------+
| Oranjestad     | Aruba       | –        |
| Kabul          | Afghanistan | Kabol    |
| Qandahar       | Afghanistan | Qandahar |
| Herat          | Afghanistan | Herat    |
| Mazar-e-Sharif | Afghanistan | Balkh    |
+----------------+-------------+----------+
5 rows in set (0.04 sec)

mysql> 

Here we select columns A, B, C from temporary table 'secondCTE'. In this case it is easier to see that we are getting data from the CTE and not the two tables city or country.  At least directly.

The reason I use this examples is often times you have to join two or more tables and the columns with the name name have different sources, here name of country and name of city.  Yes, you could alias them in a sub query but the legibility of the query increases.  And as query complexity builds things like the old N+1 problems raise their ugly heads.

More Than One Way to Skin a Cat 

So lets play again with the information from the second example but this time we will write two separate CTEs and then join them together.  First we are going to get the Name column from the city table and the Name column from the country table. We do have to drag along the columns for a join in the CTEs.  I think for many with limited SQL experience that this version is a little easier to comprehend than the second example.

mysql> WITH threeA AS (SELECT Name AS N1, CountryCode AS x FROM city),
    -> threeB AS (SELECT Name AS N2, Code as y FROM country)
    -> SELECT N1, N2 FROM threeA JOIN threeB WHERE threeA.x = threeB.y LIMIT 5;
+----------------+-------------+
| N1             | N2          |
+----------------+-------------+
| Oranjestad     | Aruba       |
| Kabul          | Afghanistan |
| Qandahar       | Afghanistan |
| Herat          | Afghanistan |
| Mazar-e-Sharif | Afghanistan |
+----------------+-------------+
5 rows in set (0.01 sec)

mysql> 

Hopefully the use of CTEs will produce easier to read SQL statements. 

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.

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.

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.

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.