Recently, at a presentation I was giving on the newer features of MySQL 8.0, I noticed one person in the audience getting very upset. The more I talked about one feature, the more agitation I could see this one person getting. We're talking upset at a level where I was wondering if I was going to worry about my physical safety. The person in question finally snapped, "If I had known about that, it would have saved me four months of my life!"
With the release of MySQL 8.0, in April 2018, the release cycle for new features was changed to four times a year. So, rather than waiting for two to three years for new features, the MySQL Engineering Teams can provide a steady stream of updates to our users. Part of this is customer demand for new facets to the most popular database on the web and part of an evolved software engineering process.
But sadly, most people, I am shocked to learn, are not sitting on the edge of their seats waiting for MySQL product announcements. So I would like to bring you up to speed on some of the later additions to the MySQL software worlds.
Dual passwords
Imagine corporate security reminds you to update the password on your 'application' (and by application, we mean eight hundred separate pieces of code that need to be edited to remove the old password and implement the new password). Even the most ardent Emacs fan would swallow hard and balk at trying this all at one time. To the rescue! MySQL 8.0 has support for dual passwords—one current password and a secondary password—to provide an easy way for you to transition to the new password.
ALTER USER 'appuser1' @ 'host1.example.com'
IDENTIFIED BY 'password_b'
RETAIN CURRENT PASSWORD;Now you can change the password at a less frenetic pace and, when you have all the code updates, you simply remove the older password.
ALTER USER 'appuser1' @ 'host1.example.com'
DISCARD OLD PASSWORD;You can also have the MySQL Server generate a random password for your account and set a threshold on the number of bad logins before automatically locking that account for a settable time limit.
JSON document validation
It is a lot harder to fix bad data stored in your database than it is to keep the bad data out in the first place. Traditional relational databases had some ways to ensure that you were only putting integers into integer columns, and there were ways to make sure that the data was in a specified range. But, with more reliance on MySQL native JSON data type, there was no way until recently to make sure that certain key/values were present, of the right data type, and in a proper range. Thanks to the folks at JSON-Schema.org, MySQL 8.0 can help keep your data clean by rejecting bad JSON data.
CREATE TABLE testx (
col JSON,
CONSTRAINT myage_inRange
CHECK (JSON_SCHEMA_VALID(’{“type”: “object”,
“properties”: {
“myage”: {
“type” : “number”,
“minimum”: 28,
“maximum”: 99
}
},“required”: [“myage”]
}’, col) = 1)
);An example of how bad data is kept out of your database:
mysql> insert into testx values(’{“myage”:27}’);
ERROR 3819 (HY000): Check constraint ‘myage_inRange’ is violated.
mysql> insert into testx values(’{“myage”:97}’);
Query OK, 1 row affected (0.02 sec)And yes, constraint checks now work with MySQL 8.0, where in the past they were ignored.
Better SQL support
MySQL 8.0 added Common Table Expression (CTES), Windowing Functions, Lateral Derived Tables, and support for the TABLE operator to provide a more powerful, robust Structured Query Language for our users.
Data dictionary
The biggest change is behind the scenes with the new data dictionary. If you have ever gone wandering under /var/lib/mysql with a previous version and been confused by the myriad of little files, then you have seen the metadata for your instance. That metadata is now stored in the database itself in the data dictionary. So, besides freeing up inodes, you now can have millions of tables in a schema. The downside is, well, now you can have millions of tables in a schema.
The new shell
The new MySQL shell, also known as Mysqlsh, is a new CLI but with a lot of horsepower. It has three modes—Structured Query Language, JavaScript, and Python. So if you have libraries in either of the two latter languages, you can use them from the shell. Plus, you can save scripts for later use.
There is a parallel bulk loader that can import CSV, TSV, and JSON data extremely quickly included in the built-in utilities. There is also an upgrade checker that can help you update from MySQL 5.7 to 8.0, which will point out conflicts with reserved words and other potential problems.
As an added bonus, there is a vastly expanded help command and command completion.
Just skimming the surface of the changes
That is only a few of the many changes in MySQL 8.0. For a complete list, please come to my talk at the Southern California Linux Expo (SCaLE) in Pasadena on March 6, 2020. There is a complete MySQL track on that day; please see my blog post for details.
Comments are closed.