MySQL Pre-FOSDEM Days - A Quick Review

A sold out, two-day event with over 30 sessions on various aspects of MySQL is a tough thing to organize and accomplish. But the MySQL pre-FOSDEM Days was an amazing success. If you missed this event then you really missed two full days of presentations by MySQL engineers and subject matter experts from the MySQL Community. 

It all started on Thursday January 30th with Geir Hoydalsvik talking about Simplifying MySQL which not a simple talk.  Big efforts are being made to clean up and modernize the MySQL Server Core code.  Work is being undertaken to change to a Volcano Model for extended flexibility. 

This was followed by Kenny Gryp showing how the new InnoDB Replica Set. Previously it was easier to setup InnoDB Cluster Replication than simple primary-secondary replication. 

From that point on there were two different tracks with many talks on indexing JSON Arrays, detailed information on date-time types, the new redo log, hash joins, the big changes in NDB Cluster, and more. 

The audience members I talked to where impressed with the new features, their access to MySQL engineers, and the quality of the talks.  And on Friday it was topped off by the fantastic community dinner. 

Next year you will hear about the 2021 event and I highly encourage you to participate.  This even has become one of the better 'raw' technical content events on the calendar and anyone running MySQL would greatly benefit from attending.

And a big round of applause for my colleagues from the MySQL Community Team - Lenka Kasparova and LeFred -- for all their hard work and on their accomplishments. Lenka routinely makes miracles happen with the various tasks related to funding while Fred brought the various other components together for a stupendous event. 

Check Constraints and Duplicate Names

i was working on some example code for using check constraints and was fairly happy with my first bit of of code.

CREATE TABLE c1 (x INT 
                   CONSTRAINT x_gt_0 CHECK (x > 0)
);

it worked well and did what I wanted which was to reject data where the value for x was not one or greater. MySQL has allowed you to have constraint checks for many years but they only came to life in 8.0.16.  Before that version the checks were simply ignored.

So I was writing some more demo code and smacked into what I thought was an odd error.

CREATE TABLE c2 (x INT 
                   CONSTRAINT x_gt_0 CHECK (x > 0), 
                   CONSTRAINT x_lt_10 CHECK ( x < 10)
);
ERROR: 3822: Duplicate check constraint name 'x_gt_0'.

I did have a constraint in another table named x_gt_0.  It seemed odd to me and it was odd enough that it sent me to the manual.  If you refer to 13.1.20.7 CHECK Constraints of the MySQL 8.0 server manual, there in black and white is

The SQL standard specifies that all types of constraints (primary key, unique index, foreign key, check) belong to the same namespace. In MySQL, each constraint type has its own namespace per schema (database). Consequently, CHECK constraint names must be unique per schema; no two tables in the same schema can share a CHECK constraint name. (Exception: A TEMPORARY table hides a non-TEMPORARY table of the same name, so it can have the same CHECK constraint names as well.)

Ah, it is because of the danged named spaces.  And if the SQL standard specifies then I have to live with it.

Work around? Yes, I quickly renamed the constraint in question getting what I wanted.  In this case it was not a big thing as the name of the constraint, while descriptive, was not named by some imperative.

>CREATE TABLE c2 (x INT 
                  CONSTRAINT x_gt_1 CHECK (x > 1), 
                  CONSTRAINT x_lt_10 CHECK ( x < 10)
);
Query OK, 0 rows affected (0.0801 sec)
 >insert into c2 (x) values (100);
ERROR: 3819: Check constraint 'x_lt_10' is violated.
>insert into c2 (x) values (0);
ERROR: 3819: Check constraint 'x_gt_1' is violated.
>insert into c2 (x) values (4);
Query OK, 1 row affected (0.0035 sec)

So there is always another way to skin a cat but how many skinless cats do you need hanging around?         

If you want to be proactive you can refer to the INFORMATION_SCHEMA.check_constraints table to see if you have already used that name.

>select * from INFORMATION_SCHEMA.check_constraints 
        where CONSTRAINT_NAME='x_gt_0'\G

*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: demo
   CONSTRAINT_NAME: x_gt_0
      CHECK_CLAUSE: (`x` > 0)
*************************** 2. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: xtest
   CONSTRAINT_NAME: x_gt_0
      CHECK_CLAUSE: (`x` > 0)
2 rows in set (0.0017 sec)

Remember you can reuse the name but it has to be in a different schema. That is why I can have x_gt_0 used twice but once in the demo schema and once in the xtest schema.







create table porridge (id int unsigned auto_increment,
temp integer
       constraint too_hot check (temp > 100),
       constraint too_cold check ( temp < 70),
primary key(id)
);

MySQL Document Store Tutorial

When I tell people that they can use MySQL without SQL they tend to be skeptical.  But with the MySQL Document Store you can do just that with a new NoSQL API and in this case there is no structured query language.pre-FOSDEM MySQL Days (which is sold out and there is a waiting list) is my tutorial on using the Document Store.  Those in my session will be able to see how to use the MySQL Shell (mysqlsh) to connect to a MySQL server and save data without have to do the many things a DBA used to have to do in the past such as normalize data, setup relations, and several other tasks.  Plus the schema-less Document Store means you can alter your data needs without having to do an endless series of ALTER TABLES.

MySQL Document Store let you save and retrieve data without needed the use of structured query language (SQL)

Part of the tutorial is a workbook and slides that I should be able to publish if they are well received.  And maybe a video for those who will not be able to make it to Brussels.

Neat Stuff in MySQL 8.0.19

MySQL 8.0.19 came out this week and can he downloaded here.   One of the first things I do when a new release happens is look at the release notes.  The release note cover the changes from the previous versions and the latest edition edition details some interesting new stuff.

Password Locking

MySQL now enables administrators to configure user accounts such that too many consecutive login failures due to incorrect passwords cause temporary account locking. The required number of failures and the lock time are configurable per account, using the FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME options of the CREATE USER and ALTER USER statements.

YEAR(4) and Year

Support for the YEAR(2) data type was removed in MySQL 5.7.5, leaving only YEAR and YEAR(4) as valid specifications for year-valued data. Because YEAR and YEAR(4) are semantically identical, specifying a display width is unnecessary, so YEAR(4) is now deprecated and support for it will be removed in a future MySQL version. Statements that include data type definitions in their output no longer show the display width for YEAR. This change applies to tables, views, and stored routines, and affects the output from SHOW CREATE and DESCRIBE statements, and from INFORMATION_SCHEMA tables.

Syntax Change 

MySQL now supports explicit table clauses and table value constructors according to the SQL standard. These have now been implemented, respectively, as the TABLE statement and the VALUES statement. What does that mean? 

You can use TABLE like you use SELECT.  The two following statements are equivalent;

TABLE t ORDER BY c LIMIT 10 OFFSET 3; SELECT * FROM t ORDER BY c LIMIT 10 OFFSET 3;

 And ROW will help ease confusion for those adding multiple records in a statement.  Again the following two statements are equivalent.

INSERT INTO t1 VALUES ROW(1,2,3), ROW(4,5,6), ROW(7,8,9); INSERT INTO t1 VALUES (1,2,3), (4,5,6), (7,8,9);

 You can also select from a VALUES table value constructor just as you would a table, bearing in mind that you must supply a table alias when doing so. Using column aliases, you can also select individual columns

mysql> SELECT a,c FROM (VALUES ROW(1,2,3), ROW(4,5,6)) AS t(a,b,c); +---+---+ | a | c | +---+---+ | 1 | 3 | | 4 | 6 | +---+---+

Recursion Limits on CTEs

Before 8.0.19 it was not possible to use LIMIT in the recursive SELECT part of a recursive common table expression (CTE). LIMIT is now supported in such cases, along with an optional OFFSET clause

WITH RECURSIVE cte AS ( SELECT CAST("x" AS CHAR(100)) AS a FROM DUAL UNION ALL SELECT CONCAT("x",cte.a) FROM qn WHERE LENGTH(cte.a) < 10 LIMIT 3 OFFSET 2; ) SELECT * FROM cte;

Alias on DUPLICATE KEY Updates

MySQL now supports aliases in the VALUES and SET clauses of INSERT INTO ... ON DUPLICATE KEY UPDATE statement for the row to be inserted and its columns. Note the use of 'new' as an alias.

INSERT INTO t VALUES (9,5), (7,7), (11,-1) AS new ON DUPLICATE KEY UPDATE a = a + new.a - new.b;

Microsoft Windows System Command

 Previously, the system (\!) command for the mysql command-line client worked only for Unix systems. It now works on Windows as well. For example, system cls or \! cls may be used to clear the screen

X Protocol Compression

Compression is supported for messages sent over X Protocol connections. Connections can be compressed if the server and the client agree on a compression algorithm to use. By default, X Protocol announces support for the deflate, lz4, and zstd compression algorithms.

Row Based Replication for Security

A new setting REQUIRE_ROW_FORMAT is added for replication channels, which makes the channel accept only row-based replication events. You can specify REQUIRE_ROW_FORMAT using a CHANGE MASTER TO statement to enforce row-based binary logging for a replication channel that is secured with privilege checks, or to increase the security of a channel that is not secured in this way. By allowing only row-based replication events, REQUIRE_ROW_FORMAT prevents the replication applier from taking actions such as creating temporary tables and executing LOAD DATA INFILE requests, which protects the replication channel against some known attack vectors. Row-based binary logging (binlog_format=ROW) must be used on the replication master when REQUIRE_ROW_FORMAT is set.

But Wait -- There's More

These were only the big items that caught my eye and a recommend reading the release notes to see if there is something that did not catch my eye that may be of interest for you.