New features in MySQL 8.0.16 ecosystem

Last Thursday (2019-04-25), just before the Orthodox Easter holidays Oracle released version 8.0.16 of the products in the MySQL ecosystem (including server, router, shell, connectors and workbench GUI). The new versions of the server continued the tradition established with MySQL 8 of introducing new features even though they're still labeled "Maintenance Release". On the last Pre-FOSDEM MySQL Day Giuseppe Maxia suggested in a tweet that semantic versioning should be used and I stay behind this as I understand and support semantic versioning.

Start pushing for Semantic Versioning to be adopted at MySQL, and you will avoid this kind of miscommunications

— Giuseppe Maxia (@datacharmer) February 1-st 2019

This would be really helpful, because version 8.0.15 of the server was as a true maintenance release fixing only an important group replication issue. Following the new numbering scheme (see MySQL 8.0: It Goes to 11!) all other products from the ecosystem were bumped to 8.0.15 even without any changes whatsoever. I'm really not sure this is necessary, because as soon as you're using the same major (and minor) versions then there shouldn't be a problem with compatibility. And the same version numbers doesn't really ensure full compatibility between the products as my bugs on MySQL Workbench (e.g. 90620, 92900, 92908 and 94012) show.

MySQL Server

So, what's new in the new release of the server. Here are my picks.

CHECK constraints

Personally, I was waiting for this feature since MySQL 4 when they started adding major new features into the server and it started becoming more like other more advanced databases. Bug #3465 was opened in April 2004 (and was there promised to be fixed in MySQL 5.1), so this SQL feature is finally implemented after more than 15 years.

Until now (as you may have noticed) the CHECK keyword in CREATE TABLE was parsed, but silently ignored. We used workarounds in the past. For example one could ensure only nonnegative values in column with numeric type (integers and fixed/floating point types) by using the nonstandard attribute UNSIGNED (see Numeric Type Attributes). More complex constraints could be implemented through the use of triggers and raising conditions (see SIGNAL syntax) as I've shown in my presentation MySQL 8 for developers on BGOUG Spring 2018 Conference and these would still be required.

MySQL 8.0.16 now supports both table constraints and column constraints (see CHECK constraints) for all storage engines. Table constraints appear anywhere in CREATE TABLE outside column definitions and could refer to one or more columns even with forward references (i.e. for columns defined later in the statement). Column constraints appear within the definition of a column and could refer only to this column. Unfortunately, the expression that specifies the check condition could use only literals, deterministic built-in functions, and operators (i.e. stored routines, UDFs, variables and subqueries are not permitted). It is also not possible to use CHECK constraints on columns with foreign key referential actions (i.e. ON UPDATE, ON DELETE).

The thing to consider with CHECK constraints is that error is raised only if the condition evaluates to FALSE. If it evaluates to UNKNOWN due to NULL values error won't be raised. This is covered very well by Markus Winand in his article The Three-Valued Logic of SQL, so I highly recommended you to read the whole article.

CHECK constraints have a name of up to 64 characters and if not specified the server would generate a name like [table name]_chk_[ordinal number], because the names must be unique per schema. A useful feature is the possibility to create, but not enforce the constraint (i.e. with NOT ENFORCED clause), which is like enable/disable for triggers that is a feature I'd like to see implemented in future releases.

For basic usage and simple examples of CHECK constraints in MySQL, please check the manual and MySQL 8.0.16 Introducing CHECK constraint blog post. There's a more interesting example of CHECK constraint on JSON data in the article MySQL 8.0.16: how to validate JSON values in NoSQL with check constraint.

Here's my first example. I think it's quite common to have two dates in a table (e.g. start and end date) that should represent the beginning and end of something, so they should be in chronological order. Let's try to do this on the emp table from the example DEPT and EMP schema of Oracle, which I adapted for MySQL.

ALTER TABLE emp
  ADD COLUMN retdate DATE AFTER hiredate,
  ADD CONSTRAINT ret_after_hire CHECK (retdate > hiredate);

However, when I tried to update an employee row that already has value for hire date with the following query (the retirement date presumably coming from wrong user input and/or bad application):

UPDATE emp
   SET retdate = STR_TO_DATE('1019-04-25', '%Y-%m-%d')
 WHERE empno = 7369;

1 row(s) affected Rows matched: 1  Changed: 1  Warnings: 0

the query unexpectedly succeed and there was no "constraint violated" error. After discussing with Frédéric Deschamps on Slack I filed bug #95189. It seems to be related to replication, because on my master MySQL 8.0.16 server on windows the problem isn't reproducible and the query works as expected, but the test I've done was on my MySQL 8.0.16 server running on Slackware64 -current, which is replicating from my main MySQL 5.7.26 server.

Here's another example. A table constraint that ensures hire date is today or in future and salary is positive number.

ALTER TABLE emp
  ADD CONSTRAINT emp_chks CHECK (hiredate >= CURDATE() AND sal > 0);

Such a constraint may seem reasonable (if you consider just inserting data), but of course results in:

Error Code: 3814. An expression of a check constraint 'emp_chks' contains disallowed function: curdate.

because CURDATE is non-deterministic function. Such constraint is possible in PostgreSQL (which also supports usage of stored routines and UDFs), but not in Oracle (that supports usage of storage routines) and MariaDB (which pretty much has the same restrictions as MySQL although not clearly listed). The problems are how do you validate the constraint for existing rows and how do you update rows, because the value of CURDATE changes every day. The solution is to create an additional column preserving the current date when the row was created and then use it to validate the constraint.

If you try to directly create the column and enforce the constraint (i.e. with an ALTER TABLE) you'll of course get:

Error Code: 3819. Check constraint 'emp_chks' is violated.

because check constraints, like other constraints (e.g. primary and foreign keys, unique indexes, NOT NULL), are validated for all the rows when created and should always remain valid. So, let's try another way.

First, initialize the new column from existing data (e.g. based on hiredate column as expressions are possible as default values since 8.0.13):

ALTER TABLE emp
  ADD COLUMN created DATE DEFAULT (hiredate);

Then, modify the column and add the check constraint:

ALTER TABLE emp
  MODIFY COLUMN created DATE NOT NULL DEFAULT (CURDATE()),
  ADD CONSTRAINT emp_chks CHECK (hiredate >= created AND sal > 0);

Now, let's try some queries:

UPDATE emp SET hiredate = '1979-04-25' WHERE empno = 7369;

INSERT INTO emp
  (empno, ename, job, mgr, hiredate, sal)
VALUES
  (9999, 'MULDER', 'INVESTIG.', 7839, '2019-04-25', 4242);

As expected, the UPDATE query produces error:

Error Code: 3819. Check constraint 'emp_chks' is violated.

but the INSERT query succeeds, which is unexpected. Apparently, the value of created is not initialized with the default value when the CHECK constraint is validated. I tried the same (although slightly different syntax) on Oracle XE 18 and it worked as expected - both the UPDATE and INSERT queries violated the CHECK constraint.

There is a new table CHECK_CONSTRAINTS in INFORMATION_SCHEMA that provides information on defined check constraints in all sachems. Additional information about table name and whether a CHECK constraint is enforced or not could be retrieved from table TABLE_CONSTRAINTS by filtering the data by the new value CHECK of column CONSTRAINT_TYPE.

Spatial data

After ST_Distance since 8.0.14, now ST_Length function also supports optional second argument unit, so it's possible to calculate lengths in the different supported units as defined in INFORMATION_SCHEMA.ST_UNITS_OF_MEASURE table.

Server does all upgrades

The server is already capable of upgrading everything - the mysql schema, data dictionary and system tables as well as PERFORMANCE_SCHEMA, INFORMATION_SCHEMA, sys and user schemes if necessary (see What the MySQL Upgrade Process Upgrades), so mysql_upgrade command is going into retirement. It's an important administrative feature, because this would make upgrades easier and more convenient. In the past I regularly forgot to execute the command, which led to strange problems later.

In regard to upgrades I had this strange problem that the server wouldn't start with the error message [ERROR] [MY-013384] [Server] Could not create server upgrade info file at '/var/lib/mysql/data/' although the permissions were OK. I was able to find the gist mysql will not start after 8.0.15 to 8.0.16 update on Ubuntu 16.04 LTS with Google and after creating the file mysql_upgrade_info in /var/lib/mysql/data/ and changing its ownership to mysql:mysql the server was able to start successfully. I was upgrading from 8.0.14 in fact, but I think it may be a bug. Perhaps the server expects the file to have been created.

Another interesting new feature in the option --validate-config for validation of server configuration much like system administrators are used to do so with other servers (e.g. Apache). It's really important especially for production environments where unexpected downtime could be extremely unpleasant. Read more in Server Configuration Validation.

System users

MySQL accounts are now categorized and thus distinguished by system (who posses the SYSTEM_USER privilege) and regular users (who does not). This allows for better separation of roles as only system users could perform certain administrative operations on system accounts as previously any user having the appropriate privileges could for example delete any account or kill connections belonging to any user. It's also possible to revoke global privileges partially for certain schemas (i.e. like an exceptions) through the new system variable partial_revokes, which previously required granting privileges separately for each existing schemes and adding grants for each new schema.

Security

The new things are support for TLS 1.3, possibility to update SSL certificates without restarting the running server (see ALTER INSTANCE RELOAD TLS) and information about the certificates in PERFORMANCE_SCHEMA's keyring_keys table).

MySQL Router

The router now has an HTTP component that allows it to expose simple web-interface and REST APIs for the purpose to ensure better observability through integration with external tools for monitoring and management. Other notable changes include dynamic change between single and multi master modes and improved logging. There is now the WITH_ROUTER CMake option to build router together with the MySQL server, which defaults to ON and which I decided to switch OFF, because I plan to continue building router as a separate package.

MySQL Shell

The shell comes with the new Shell Reporting Framework (see Reporting with MySQL Shell), which allows registration, displaying and monitoring of custom reports. I'm eager to try it and I'll write a separate post on this later. See the article MySQL Shell 8.0.16: User Defined Reports by Jesper Wisborg Krogh. It's now also possible to execute SQL without changing the mode, as the \sql command now doesn't change the mode if provided with an SQL statement, which I think is pretty useful.

MySQL Workbench

MySQL Workbench now supports window functions in SELECT (see bug #90620), expressions in DEFAULT (see bug #92900) and as key parts (see bug #92908) as well as the LATERAL keyword (see bug #94012) - all of these implemented into previous 8.0.x versions of the server. Unfortunately, I do not see any support for CHECK constraints (see bug #95143 that was already verified), so again the same version numbers mean nothing.