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.

MidWestPHP Conference -- Online -- this Friday

After six solid weeks on the road earlier this year, I was looking forward to taking most of the month of March at home.  Many shows on my schedule have canceled in the wake pf the Covid-19 virus.  One of those shows, Mid West PHP, has morphed into an online conference this Friday.  And did I mention the keynotes and the beginner tracks are FREE!

I will be speaking on new features in MySQL 8.0 on the Enterprise track.

Details at https://midwestphp.org/schedule

MySQL 5.6 End of Life is February 2021

You will be hearing a lot from me and others about the product end of life (EOL) of MySQL version 5.6 which will be February 2021.

Why?

While MySQL 8.0 is a vast leap forward over previous versions and MySQL 5.7 is fantastic, there are a lot of software still running and still dependent on MySQL 5.6.  And those sites running 5.6 need some encouragement to upgrade.

MySQL 5.6 came out in February of 2013 and will no longer be supported in 2021.  It has had a good run but if you are still running 5.6 then you need to upgrade.  And while I urge you to do so sooner than later, I know that some of you have more than full plates and need to plan for this upgrade.

So what you do you? First --  read this blog by my very clever colleague LeFred.

Second -- read the very fine MySQL Documentation.  Remember you can upgrade directly from MySQL 5.6 to 5.7 but those wanting to go to 8.0 still need to make the 5.7 as an intermediate step.  And I really recommend that 8.0 upgrade as it has so many new features and some really big performance gains too.  Heck - The new MySQL Shell (mysqlsh) has a utility to check your MySQL 5.7 instances for potential issues before you upgrade to 8.0!

Third -- do not forget the MySQL Forums and the MySQL Community Slack channel. They are there for you to ask questions and get answers.

Lastly - Do not neglect Planet.MySQL.com to keep track of the pulse of all things MySQL.

And yes, I will be reminding those out there to upgrade up until next February.