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. 

Videos on using the MySQL Document Store with Relational Tables and the _id Field

I have added two more videos to my MySQL Document Store Tutorials for your viewing pleasure. Early in May I will be presenting a webinar that will be an introduction to the Document Store which will cover much of this material.  If there is any specific that you want covered please let me know.

The first video is on how to use the Document Store to access relational tables.  Yes, from the NoSQL side of the server you can reach the structured data.

And the second video explains the _id field which is needed as a primary key for the storage engine, how you can supply you own values (no conflicts please), and how the server will generate values.

More MySQL Document Store Intro Videos

Last time I shared a video that is an introduction to Using MySQL without the SQL.   This time I am adding two more videos -- one on Document Collections and another on simple indexes.  The MySQL Document Store is a simple, easy to use way to for developers to store data without much of the traditional pre-requisite chores needed with a relational database.

You simple connect to the MySQL instance using the new MySQL Shell to the schema of your choice, create a document collection, and can start saving data right away. No more waiting for that DBA to setup tables, normalize data, and all the rest of that.  The API is designed to support CRUD operations and does not require developers to learn Structured Query Language to start saving data.  And the data you save with the MySQL Document Store is also available from the SQL side too.

More videos are on the way and please let me know if you have any requests.

MySQL without the SQL

MySQL can be used as a NoSQL JSON Document Store as well as a traditional relational database.  Before the MySQL Document Store you needed a DBA, or someone acting as one, to set up the schema to hold the tables and the tables according to how the data was arranged (and the indexes, constraints, and all the other stuff RDMS are known for). This was great for cases when you know exactly what the data looks likes, there are no changes to the structure of that data, and once things are set they go one that way forever.

But what about projects where change is constant, or where making changes to accommodate but keeping the old stuff as already formatted is needed? That is a perfect case for the MySQL Document Store.

MySQL w/o SQL

So you can connect to the server, create a schema, create a document collection, and store data without once using SQL. 

This video shows you a simple example of doing just that!  I connected to a MySQL server running 8.0.19 using the new MySQL Shell (mysqlsh) using an account I set up earlier and for security reasons you still need someone with a semblance of DBA skills for setting up an account with CREATE USER <user>@<host> IDENTIFIED BY <password> and GRANT ALL ON <shcma>,* to <user>@<host> or the MySQL Workbench equivalent. 

After the login, I typed session to see the detail on the session.  Next I created a schema with the session.createSchema() operator.  So the schema was created without Structured Query Language,

Next I needed to point the db object to that new schema and you can use either the \use command or session.setCurrentSchema()to do just that.

Now we need a document collection and to create one use db.createCollection() and from there it is easy to use add() to store data.

Commands Used

myssqlsh demo@localhost
session
session.getSchemas()
session.createSchema('demo')
\use demo  ( or session.setCurrentSchema('demo')  )
db
db.getCollections()
db.createCollection('dave')
db.dave.add( { name : "Dave", "demo" : "Working!" })
db.dave.find()

MySQL Failed Logins Locking Account

MySQL 8.0.19 introduced the ability to lock an account on a MySQL instance after too many failed logins.  This is not turned on by default, the locking period is measured in days (more below), and I would urge caution as not to 'denial of service' yourself out of your server.

Syntax

CREATE USER 'foobar'@'localhost'
IDENTIFIED by 'password'
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 2;

Data

And if you look at the mysql.user entry you will see

User_attributes: {"Password_locking": {"failed_login_attempts": 3, "password_lock_time_days": 2}}

Example

$mysql -u foobar -p
Enter password:
ERROR 1045 (28000): Access denied for user 'foobar'@'localhost' (using password: YES)
$ mysql -u foobar -p
Enter password:
ERROR 1045 (28000): Access denied for user 'foobar'@'localhost' (using password: YES)
$ mysql -u foobar -p
Enter password:
ERROR 3955 (HY000): Access denied for user 'foobar'@'localhost'. Account is blocked for 2 day(s) (2 day(s) remaining) due to 3 consecutive failed logins

Other Options

You can also use PASSWORD_LOCK_TIME UNBOUNDED; to keep the account locked.

The range for the lock time can range from 0 (which disables locking) to 32767 (32767 days is 89.7 years!).

ALTER USER can be used to modify existing accounts to enable locking. This locking feature supports DUAL passwords (see My MySQL Account Has TWO Passwords).

How to Reset

A reboot will reset the counter as will a FLUSH PRIVILEGE.

If you restart the instance with skip grant tables, the first FLUSH PRIVILEGE will enable this locking feature.

Or you can use ALTER TABLE to any value (including the current option value), or execution of an ALTER USER ... UNLOCK statement for the account.

Conclusion

This is an interesting security option that you may find useful. Once again I urge caution to avoid locking yourself out of an account -- so do not set it on root or other similar account and it would take a lot of debate to get me to enable this on a password used on an application's account. 

Last week I presented at the Midwest PHP Conference virtually and one of the participants was looking at the granularity of the locking period.  I would like to hear some opinions from those who want something shorter than a day.  And I would love to know if you need somethings longer then a day as 89.7 years seems extreme enough to me.