Monday, July 13, 2020

JSON_VALUE() now in MySQL 8.0.21

MySQL 8.0.21 was released today and one of the many new features is the JSON_VALUE() function. The main motivation is to ease index creation values from JSON data but there is more to it.

JSON_VALUE() finds a specified scalar JSON value in JSON data and returns it as a SQL value.

Examples


I will use the mysql_x example database data for examples.   So let us start with getting the life expectancy data.

SELECT JSON_EXTRACT(doc, "$.demographics.LifeExpectancy") AS raw 
FROM countryinfo 
LIMIT 4;
+--------------------+
| raw                |
+--------------------+
| 78.4000015258789   |
| 45.900001525878906 |
| 38.29999923706055  |
| 76.0999984741211   |
+--------------------+

That is great information if not exactly human eye friendly.

We can use JSON_VALUE() to make it a little easier for humans

SELECT 
JSON_VALUE(doc, "$.demographics.LifeExpectancy" RETURNING DECIMAL(6,2)) AS trimmed FROM countryinfo 
LIMIT 4;
+---------+
| trimmed |
+---------+
|   78.40 |
|   45.90 |
|   38.30 |
|   76.10 |
+---------+

And it can be very useful in a WHERE clause. In this example there is no RETURNING clause.

SELECT doc->"$.Name" 
FROM countryinfo 
WHERE JSON_VALUE(doc, "$.demographics.LifeExpectancy" 
     RETURNING DECIMAL(6,3)) > 80.1;
+---------------+
| doc->"$.Name" |
+---------------+
| "Andorra"     |
| "Japan"       |
| "Macao"       |
| "San Marino"  |
+---------------+

The optional RETURN clause will cast your data as FLOAT, DOUBLE, DECIMAL, SIGNED,
UNSIGNED, DATE, TIME, DATETIME, CHAR, or JSON.

There are even on empty and on error clauses.

SELECT JSON_VALUE('{firstname:"John"}', '$.lastname'               
        DEFAULT 'No last name found' ON ERROR) AS "Last Name";
+--------------------+
| Last Name          |
+--------------------+
| No last name found |
+--------------------+


And of course it can be used to help define indexes.

CREATE TABLE xdemo (j JSON,
 x INTEGER,  
 key((json_value(j, '$.id'))) 
);









MySQL 8.0 Certifications

     Recently the MySQL 8.0 Developer Certification became available joining the MySQL 8.0 Developer Certifications.  I have recently seen posts on various social media sites asking what is on the exam, is there an exam guide, and what do you 'really need to know' to pass the exam.

   Both tests are very tough.  Neither is going to be passed by rote memorization as many of the questions require analysis of system output before trying to adjust for the desired result.  There are no true or false questions and no essays.  What you will get is a series of questions written by subject matter experts that range from testing basic conceptual knowledge to very tough assessments of how to fix problems.  BTW the exam software lets you mark questions for later review and it does help to bypass the tougher questions for later review at the end of the test.

  Are the question tricky?  No, not really.  They really require in depth knowledge of MySQL 8.0.  If you have ever interviewed someone for a DBA job you know there are certain points of knowledge (pain points) that a candidate MUST have to be trusted with your precious data.  Imagine going to your physician and having to identify which body part is a knee and which part is an elbow.  I personally would trust anyone with either the 8.0 DBA or 8.0 Developer certification to know their stuff.

  Certification guide? Nope, sorry. Back in the 5.0 days there was a team effort to produce a comprehensive exam guide.  But 5.0 was a long time ago and there was an effort back in the 5.6 days for a resurrected guide that did not come to fruition. And having written a technical book, I can assure you that any such work is like writing in sand on a busy holiday vacation spot beach as the content is constantly changes and the community is adding or dropping tools continually.

   I highly recommend Oracle's classes like MySQL For Developers and from regular reviews of the course materials on my part tell me that the curriculum team  does a fantastic job conveying the complex material way in an easy to consume package of literature and exercises.  I still recommend practicing the exercises for several weeks after the class before taking the exam.

  Exam crams?  Do not waste you money.  The 'dumps' I have examined in the past are either poor third-hands recitations of poor interpretations of what was on a test or bad copies from the manual. 

How to Have a Good Chance to Pass the Exam


  Review the exam topics below, using it as a check list.  Go to the MySQL Manual enter and read the sections for that area. Make sure you understand any examples and please do try them on your own scratch server.  Please write down notes in a notebook if you think having a consolidated review tool is handy for you. 

  Also check the MySQL Server Team blog and Planet.mysql.com for blogs from other Oracle MySQL Teams.

  And if you have questions please ask away on forums.mysql.com and mysqlcommunity.slack,com.  I monitor the Certification board on the forums if you have questions

The Exam Topics 


DBA Certification 

Architecture

  • Configure client connections to the server
  • Understand how MySQL stores data
  • Understand how InnoDB stores data and logs
  • Configure buffers and caches
  • Understand and use the Data Dictionary

Security

  • Create user accounts and roles
  • Use authentication plug-ins
  • Control user and role permissions
  • Recognize common security risks
  • Secure MySQL server connections
  • Provide password and login security
  • Secure the MySQL host environment
  • Prevent SQL injection attacks
  • Encrypt MySQL data
  • Configure MySQL Enterprise Firewall

Query Optimization

  • Examine how MySQL optimizes queries
  • Analyze queries with MySQL Enterprise Monitor
  • Create indexes to improve server performance
  • Monitor and understand index statistics

High Availability Techniques

  • Explain how replication provides high availability and scalability
  • Configure replication
  • Explain the role of the binary log in replication
  • Configure multisource replication
  • Explain the role of replication threads
  • Monitor and troubleshoot replication
  • Describe MySQL InnoDB cluster and Group Replication
  • Configure a MySQL InnoDB cluster
  • Perform an InnoDB cluster recovery


Connectors and APIs

  • Choose between connectors for a given application
  • Demonstrate connector use, management and configuration
  • Retrieve data from the database by using a connector
  • Handle special values
  • Secure credentials and connections

MySQL Schema Objects and Data

  • Design, create, and alter views
  • Store and process string data
  • Store and process numeric data
  • Store and process temporal data
  • Store and process spatial data

Query Optimization

  • Use indexes for optimization
  • Analyze queries for optimization
  • Rewrite queries for optimization

JSON and Document Store

  • Create and store JSON documents
  • Process data in JSON documents
  • Explain application development with NoSQL and XDevAPI
  • Create and access a document store
  • Use MySQL Shell to access document stores

Data-driven Applications

  • Use prepared statements
  • Set SQL Modes to change MySQL behavior
  • Handle and interpret errors and warnings
  • Display query results
  • Aggregate and summarize data
  • Advanced Report Generation

Transactions

  • Control transactions in SQL
  • Control transactions in applications
  • Resolve consistency problems with isolation levels
  • Understand locking mechanisms within MySQL

MySQL Stored Programs

  • Create and execute stored routines
  • Explain the programming constructs used in stored routines
  • Create and execute triggers
  • Schedule database operations

Server Installation and Configuration

  • Install and use the MySQL server and client programs
  • Identify the files and folders created during installation
  • Start and stop MySQL
  • Upgrade MySQL
  • Configure MySQL by using options and option files
  • Configure MySQL variables
  • Launch multiple MySQL servers on the same host

Monitoring and Maintenance

  • Configure and view MySQL log files
  • Monitor MySQL processes and status
  • Configure MySQL Enterprise Audit
  • Use MySQL Enterprise Monitor to view activity in MySQL
  • Monitor database growth and explain capacity planning
  • Troubleshoot problems with locked resources

Backups and Recovery

  • Distinguish between the different types of backup
  • Implement a backup strategy
  • Backup and restore data with MySQL Enterprise Backup
  • Use mysqldump and mysqlpump to perform logical backups
  • Explain when and how to use raw file backups
  • Back up the binary log

Wednesday, June 24, 2020

Writing mysqlsh Scripts

    The new MySQL Shell or mysqlsh has provisions for loading user plugins in Python or JavaScript that are loaded when the shell starts up. I am just taking my initial steps into this area and wanted to share with you how easy it is to create a plug-in to produce a report.

    The ability to write and run your own scripts for information that you want on a regular basis is very useful. The idea behind this was a discussion with a community member who had issues with a locked account that unknowingly locked and knowing when passwords where changed.  This is a typical bit of work for a DBA that would be better automated and saved as a script for future uses.

The Query


    The query collects several vital facets of information for dealing with passwords and accounts.

SELECT concat(User, '@',Host) as User, 
       password_expired as Expired, 
       password_last_changed as Changed, 
       password_lifetime as Lifetime, 
       account_locked as Locked, 
       User_attributes FROM mysql.user

    Nothing too exotic as far as a query goes.

The Code


    Most of the Python code deals with registering the registering the passwordData function that houses the query as report named userList. The query itself is in purple below and the registration 'boilerplate is in green.  The shell.register_report is what ties the new function to the extension called userList.

# Define a passwordData function that generates a MySQL Shell report

def passwordData(session):
  query = "SELECT concat(User, '@',Host) as User, password_expired as Expired, password_last_changed as Changed, password_lifetime as Lifetime, account_locked as Locked, User_attributes FROM mysql.user"

  result = session.sql(query).execute();
  report = []
  if (result.has_data()):
    report = [result.get_column_names()]
    for row in result.fetch_all():
        report.append(list(row))

  return {"report": report}

# Register the userList function as a MySQL Shell report

shell.register_report("userList", "list", passwordData, {"brief":"Lists the user password status on the target server."})


# Check if global object 'ext' has already been registered
if 'ext' in globals():
    global_obj = ext
else:
    # Otherwise register new global object named 'ext'
    global_obj = shell.create_extension_object()
    shell.register_global("ext", global_obj,
        {"brief":"MySQL Shell extension plugins."})

# Add the 'userList' extension object as a member of the 'ext' global object
try:
    plugin_obj = global_obj.process
except IndexError:


The Report


    The output is pretty much as expected and could use some careful tailoring.  It would be nice to add information on how many days until a password expires and parsing the User_attributes field for key/values in a pretty fashion.  But in a few minutes work I had a quick little utility that will save me time in the future.

 JS > \show userList
+----------------------------+---------+---------------------+----------+--------+----------------------------------------------------------------------------------+
| User                       | Expired | Changed             | Lifetime | Locked | User_attributes                                                                  |
+----------------------------+---------+---------------------+----------+--------+----------------------------------------------------------------------------------+
| demo@localhost             | N       | 2020-04-09 10:42:06 | NULL     | N      | NULL                                                                             |
| foobar@localhost           | N       | 2020-04-06 09:38:50 | NULL     | N      | {"Password_locking": {"failed_login_attempts": 3, "password_lock_time_days": 2}} |
| mysql.infoschema@localhost | N       | 2020-01-13 11:57:33 | NULL     | Y      | NULL                                                                             |
| mysql.session@localhost    | N       | 2020-01-13 11:57:33 | NULL     | Y      | NULL                                                                             |
| mysql.sys@localhost        | N       | 2020-06-12 10:20:33 | NULL     | Y      | NULL                                                                             |
| root@localhost             | N       | 2020-01-13 11:57:37 | NULL     | N      | NULL                                                                             |
+----------------------------+---------+---------------------+----------+--------+-----------------------------------------------------------------------------

You Try

    I recommended grabbing LeFred's Github repo and reading through his code.  You can create your own directory under your ~/.mysqlsh/plugins/ext/ directory and using the above code saved as init.py as an exercise.  

    I would love to see what similar scripts or reports others are creating so please share!


Wednesday, June 10, 2020

Virtual Southeast Linuxfest sessions on JSON Document Validation & MySQL 8.0 New Features

The Southeast Linuxfest will be virtual this year and I am happy to announce that I have two sessions.


Friday, June 12th 


(All times US EDT, GMT-4.  Time between talks is used for Q&A, discussion, wanton displays of old hardware and boxed Linux software hoarding, and breaks.)

6:00 - 7:00 PM:   Dave Stokes - MySQL JSON Document Validation

JSON is a popular data interchange format and MySQL has had a native JSON Data Type for a few years.  But until recently there was no way to make sure JSON data included required fields, data type checking, or range checking.  But thanks to the JSON-Schema.org's designs, there is now a way to ensure that the JSON data going into your MySQL Database is correct.  You will learn how to use constraint checks with the new JSON validation functions to catch bad data before it gets into your server.  

 Saturday, June 13th

 
3:30 - 4:30 PM:  David Stokes - What’s New With MySQL
MySQL has been releasing new features each quarter for the past few years but do you know what is included in those releases?  Well, this session will cover in reverse order the new features such as hash joins, dual password, multi-valued indexes, derived tables, and more.  There is some really cool stuff that can save you a lot of grief if you know about them.

Wednesday, June 3, 2020

Removing Constraint Checks

Constraint Checks that actually checked the constraints were introduced last year with MySQL 8.0.16 and they really do help you keep bad data out of your database.  I have found them very handy in the past year but today I spotted a question on a website about how to remove a Constraint Check.

What is a Constraint Check?

It is an integrity check.  In the following example a constraint is set up to make sure the calues of the column 'is' are greater than one hundred.


CREATE TABLE c1 (id INT NOT NULL, 
   CONSTRAINT id_gt_100 CHECK (('id' > 100))
);

A simple test with a value in range runs perfectly fine.

INSERT INTO c1 (id) VALUES (10000);
Query OK, 1 row affected (0.0037 sec)

But you will receive an error if the value of 'is' is less than  100.

INSERT INTO c1 (id) VALUES (10);
ERROR: 3819: Check constraint 'id_gt_100' is violated.

Remembers it is cheaper and easier to keep bad data out of your databases than it is to correct it later.

Stopping the Check

If you like the check but have some reason to turn it off, say to bulk load some data in a situation where you can clean up the upload later, then use ALTER TABLE to turn off enforcement.

ALTER TABLE c1 ALTER CHECK id_gt_100 NOT ENFORCED;
Query OK, 0 rows affected (0.0150 sec)

Records: 0  Duplicates: 0  Warnings: 0
INSERT INTO c1 (id) VALUES (10);
Query OK, 1 row affected (0.0038 sec)

Or you can remove the constraint check.

ALTER TABLE c1 DROP CHECK id_gt_100;
Query OK, 0 rows affected (0.0091 sec)

Records: 0  Duplicates: 0  Warnings: 0
 SHOW CREATE TABLE c1\G
*************************** 1. row ***************************
       Table: c1
Create Table: CREATE TABLE `c1` (
  `id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0005 sec)

Further reading 

MySQL 8.0.16 Introducing CHECK constraint

MySQL Manual 13.1.20.6 CHECK Constraints


Thursday, May 7, 2020

Modify Multiple Documents in a MySQL Document Store Collection

The MySQL Document Store is an quick and easy to use NoSQL JSON Document Database that was designed to be easy to use.  But there is one thing that may not be obvious if you are working to modify multiple documents. 

So let us start with a very simple example collection of three documents.

>db.xyz.find()
{
    "a": 1,
    "_id": "00005eb2ba3c0000000000000004"
}
{
    "b": 2,
    "_id": "00005eb2ba3c0000000000000005"
}
{
    "c": 3,
    "_id": "00005eb2ba3c0000000000000006"
}
3 documents in set (0.0008 sec)

So we have three different documents and later determine we need to add the same key/value pair for all of them.

But the following will not work:

>db.xyz.modify().set("fizz","buzz")
Collection.modify: Invalid number of arguments, expected 1 but got 0 (ArgumentError)

The error message is trying to tell you that you need to a) specify one record (say where a = 1) to update just that one document or b) add 'true' to the modify() all of them.

>db.xyz.modify('true').set("fizz","buzz")
Query OK, 3 items affected (0.1389 sec)

>db.xyz.find()
{
    "a": 1,
    "_id": "00005eb2ba3c0000000000000004",
    "fizz": "buzz"
}
{
    "b": 2,
    "_id": "00005eb2ba3c0000000000000005",
    "fizz": "buzz"
}
{
    "c": 3,
    "_id": "00005eb2ba3c0000000000000006",
    "fizz": "buzz"
}
3 documents in set (0.0005 sec)

If you would prefer to see a video of this, please refer to my Github tutorials

Monday, April 27, 2020

MySQL 8.0.20 Is Released

MySQL 8.0.20 was released April 27th. So what  is new? The details are in the release notes and I would like to point out a few of the more interesting things, at least to me.

New SHOW_ROUTINE privilege 

Previously those using routines had to had the SELECT privilege which in many cases was overly broad. So for better granularity and control of resources.

Better JSON_TABLE

To bring JSON_TABLE into compliance with the specification, ON EMPTY must now come before ON ERROR where in previous versions you could the two clauses in any way you wanted.

Hash Joins

Hash joins are now available as Inner non-equi-joins, Semijoins, Antijoins, Left outer joins, and
Right outer joins.

Double Buffer Improvement

The InnoDB storage engine area for the doublewrite buffer was moved from the system tablespace to doublewrite files.This reduces write latency, increases throughput, and provides flexibility with respect to placement of doublewrite buffer pages.

Binary Log Compression

You can now use the zstd algorithm with replication! The log files are not decompressed by receiver threads and are written to the relay log still in their compressed state. This compression  saves storage space both on the originator of the transaction and on the recipient and saves network bandwidth when the transactions are sent between instances.

Bugs Gone

I counted 248 bugs fixed.