Wednesday, January 15, 2020

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;


 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


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.

Friday, January 10, 2020

Indexing the MySQL Document Store

I am writing a tutorial on the MySQL Document Store for the sold out (sorry) pre-FOSDEM MySQL days.  For those who do not write such exercise they are often a difficult task as you have a limited time to convey information, need to provide vivid examples, and create exercises that give a good idea of what the topic is all about.  And my personal preference is to write once and use the tutorial at other events (please let me know if you have such an event).

Indexing records is a well know performance step when creating databases, SQL or NoSQL.  And back in June of 2017 I wrote a blog post on using createIndex() to index documents in the MySQL Document Store. And as part of creating the tutorial I referred to that blog post as a reference and was quite surprised that it was not working.

What happened? Well back in 8.0.11 the function was revised and it is no longer a series of chained calls but a function that receives 2 parameters, details can be found at:

So what follows is an update to the old blog post with the new version of the function.

Indexing and the MySQL Document Store

The MySQL Document Store allows developers who do not know Structured Query Language (SQL) to use MySQL as a high efficient NoSQL document store. It has several great features but databases, NoSQL and SQL, have a problem searching through data efficiently. To help searching, you can add an index on certain fields to go directly to certain records. Traditional databases, like MySQL, allow you to add indexes and NoSQL databases, for example MongoDB, lets you add indexes. The MySQL Document Store also allows indexing.

So lets take a quick look at some simple data and then create an index.

    "_id": "00005e163bc70000000000000001",
    "nbr": 1
    "_id": "00005e163bc70000000000000002",
    "nbr": 3
    "_id": "00005e163bc70000000000000003",
    "nbr": 5
    "_id": "00005e163bc70000000000000004",
    "nbr": 7
    "_id": "00005e163bc70000000000000005",
    "nbr": 99
    "_id": "00005e163bc70000000000000006",
    "nbr": 2
6 documents in set (0.0037 sec)

To index the nbr field with the 8.0.11 syntax we need to specify the name of the index and then the parameters for the index. In the example below we name the index nbr_idx and provide a JSON object of {fields:[{"field": "$.nbr", "type":"INT", required:true}]} with the desired settings, which is called the index definition.

db.b.createIndex("nbr_idx", {fields:[{"field": "$.nbr", "type":"INT", required:true}]});

The index name is up to you but please keep it useful and relevant.

What you Specify to Create and Index

Rule # 1 -- All the values in an key/value to be indexed MUST be of the same type! So no '1,2,3,Ralph,3.4' plese

The JSON document used for defining an index, such as {fields: [{field: '$.username', type: 'TEXT'}]}, can contain the following:

fields: an array of at least one IndexField object, each of which describes a JSON document field to be included in the index.

A single IndexField description consists of the following fields:

  • field: a string with the full document path to the document member or field to be indexed
  • type: a string with one of the supported column types to map the field to. For numeric types, the optional UNSIGNED keyword can follow. For the TEXT type you can define the length to consider for indexing (you do not need to index all that TEXT column, just enough to narrow down your search).
  • required: an optional boolean, set to true if the field is required to exist in the document. Defaults to false for all types except GEOJSON, which defaults to true.
  • options: an optional integer, used as special option flags to use when decoding GEOJSON data.
  • srid: an optional integer, srid value to use when decoding GEOJSON data.
  • array: (for MySQL 8.0.17 and later) an optional boolean, set to true if the field contains arrays. The default value is false. 
  • type: an optional string which defines the type of index. One of INDEX or SPATIAL. The default is INDEX and can be omitted.

Quick Review

Old syntax (MySQL 8.0.10 and earlier):"nbr_idx").field("nbr","INTEGER", false).execute()

New syntax (MySQL 8.0.11 and later):

db.b.createIndex("nbr_idx", {fields:[{"field": "$.nbr", "type":"INT", required:true}]});