Planet MySQL

Tracking Foreign Keys

The other day, I was reading a blog by Magnus Hagander about tracking foreign keys throughout a schema in PostgreSQL. I thought it was a good idea, so I decided to look at how you can track foreign key in MySQL.

The way I decided to do it was to start out with a table, then find all tables referencing the table by a foreign key. From this basic (and simple as it will be shown) query, it is possible to create a chain of relations. The key table for the queries is information_schema.KEY_COLUMN_USAGE which has information about all the foreign key relationships.

However, before getting that far, it is necessary to have some tables to work with.

Sample Schema

A small schema, but with relatively complex foreign keys relations, can be created with the following statements:

DROP SCHEMA IF EXISTS db1; CREATE SCHEMA db1; CREATE TABLE db1.a ( a_id int, PRIMARY KEY (a_id) ); CREATE TABLE db1.b ( b_id1 int, b_id2 int, a_id int, PRIMARY KEY (b_id1, b_id2), FOREIGN KEY (a_id) REFERENCES a (a_id) ); CREATE TABLE db1.c ( c_id int, b_id1 int, b_id2 int, PRIMARY KEY (c_id), FOREIGN KEY (b_id1, b_id2) REFERENCES b (b_id1, b_id2) ); CREATE TABLE db1.d ( d_id int, a_id int, c_id int, PRIMARY KEY (d_id), FOREIGN KEY (a_id) REFERENCES a (a_id), FOREIGN KEY (c_id) REFERENCES c (c_id) ); CREATE TABLE db1.e ( e_id int, a_id int, PRIMARY KEY (e_id), FOREIGN KEY (a_id) REFERENCES a (a_id) ); CREATE TABLE db1.f ( f_id int, b_id1 int, b_id2 int, c_id int, PRIMARY KEY (f_id), FOREIGN KEY (b_id1, b_id2) REFERENCES b (b_id1, b_id2), FOREIGN KEY (c_id) REFERENCES c (c_id) );

The base table is a. There are three tables, b, d, and e, with a direct foreign key to table a.  Tables c and f in turn references table b through a two column foreign key, and tables d and f references table c. So there are quite a few paths to get to table a from another table. Before looking at how the relationship can be found from the information_schema.KEY_COLUMN_USAGE, it is worth creating a visual representation of the schema.

MySQL Workbench EER Diagrams

A long standing feature of MySQL Workbench is its ability to create enhanced entity–relationship (EER) diagrams. This shows a box with information about the columns and indexes of each table in a schema. Additionally there are lines connecting tables related by foreign keys. So, an EER diagram includes what we are looking for – the chain of foreign keys.

You can create an ERR diagram by clicking on Database and then choose Reverse Engineer … from the menu in MySQL Workbench:

Choose Reverse Engineer in the MySQL Workbench menu.

Alternatively use CTRL + R. You can do this from the homepage or from a database connection. Either way, you are taken to the connection options screen where you can choose an already defined connection or create a new one – this works the same as when you connect to a MySQL instance to execute queries:

Specify the connections options to create to the MySQL instance with the schema for the EER diagram.

When you continue, MySQL Workbench will connect to MySQL Server and get a list of the schemas available. Once you get to the Select Schemas page, you can choose the schema or schemas you want to create the EER diagram for. In this case choose the db1 schema (unless you created the tables in another schema):

Choose the schemas to import.

For this example, you can use the defaults for the rest of the screens. On the Select Objects screen, you can optionally choose to select a subset of tables for the diagram. On the same screen, you choose whether you want to place the imported objects into a diagram (enabled by default); you want to do that for this example.

Tip: If MySQL Workbench crashes when creating the diagram, try open Edit → Configuration… → Modelling in the menu and check the Force use of software based rendering for EER diagrams option.

At the end, you have the diagram. You can move the tables around to place them as you like. One example of the diagram is:

MySQL Workbench EER Diagram

This makes it easy to see the relations between the tables.

But what do you do, if you want to analyze the relationship in a program or for some other reason have the relationships in a text format? Let’s look at that.

Querying the Foreign Key Relationship

As mentioned, the base table for looking at foreign key relations is the information_schema.KEY_COLUMN_USAGE table. It has the following definition:

mysql> DESC information_schema.KEY_COLUMN_USAGE; +-------------------------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------------------+------------------+------+-----+---------+-------+ | CONSTRAINT_CATALOG | varchar(64) | YES | | NULL | | | CONSTRAINT_SCHEMA | varchar(64) | YES | | NULL | | | CONSTRAINT_NAME | varchar(64) | NO | | NULL | | | TABLE_CATALOG | varchar(64) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | YES | | NULL | | | TABLE_NAME | varchar(64) | YES | | NULL | | | COLUMN_NAME | varchar(64) | YES | | NULL | | | ORDINAL_POSITION | int(10) unsigned | NO | | NULL | | | POSITION_IN_UNIQUE_CONSTRAINT | binary(0) | YES | | NULL | | | REFERENCED_TABLE_SCHEMA | binary(0) | YES | | NULL | | | REFERENCED_TABLE_NAME | binary(0) | YES | | NULL | | | REFERENCED_COLUMN_NAME | binary(0) | YES | | NULL | | +-------------------------------+------------------+------+-----+---------+-------+ 12 rows in set (0.01 sec)

In MySQL 8.0 this is a view on the new data dictionary, so effectively a plain InnoDB query and it is fast to query. In MySQL 5.7 and earlier, querying it requires opening the tables which can be slow and all tables must be opened. If you have many tables and they are not cached in the table caches yet, querying KEY_COLUMN_USAGE can be slow and impact the general performance.

Basic Query – Single Column per Foreign Key

The three columns prefixed with REFERENCED_ contains the information about a foreign key. For example, for the tables used in this blog, if you want to know which tables have a direct foreign key to table a in the db1 schema, you can query KEY_COLUMN_USAGE with a WHERE clause on REFERENCED_TABLE_SCHEMA and REFERENCED_TABLE_NAME like:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'db1' AND REFERENCED_TABLE_NAME = 'a'; +--------------+------------+-------------+-------------------------+-----------------------+------------------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | +--------------+------------+-------------+-------------------------+-----------------------+------------------------+ | db1 | b | a_id | db1 | a | a_id | | db1 | d | a_id | db1 | a | a_id | | db1 | e | a_id | db1 | a | a_id | +--------------+------------+-------------+-------------------------+-----------------------+------------------------+ 3 rows in set (0.01 sec)

So, the tables b, d, and e has a foreign key to a_id in the db1.a table, and the column name for each of the three tables is also called a_id. This is just as expected.

The query works great for finding the immediate relations where the foreign key only includes a single column. However, for cases where there are multiple columns in the foreign key, there will be two rows for each referencing table. So what to do?

Basis Query – Multiple Columns per Foreign Key

To avoid having one row per column in a multi-column foreign key, you need to perform an aggregation. You can for example use the GROUP_CONCAT() to generate a single value combining the column names. In MySQL 8.0, you can also consider creating a JSON array by using the JSON_ARRAYAGG() function:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, JSON_ARRAYAGG(COLUMN_NAME) ColumnNames FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'db1' AND REFERENCED_TABLE_NAME = 'b' GROUP BY TABLE_SCHEMA, TABLE_NAME; +--------------+------------+--------------------+ | TABLE_SCHEMA | TABLE_NAME | ColumnNames | +--------------+------------+--------------------+ | db1 | c | ["b_id1", "b_id2"] | | db1 | f | ["b_id1", "b_id2"] | +--------------+------------+--------------------+ 2 rows in set (0.01 sec)

This queries the foreign keys to the b tables. The c and f tables have a foreign key using the b_id1 and b_id2 columns.

This query result also means that the c and f tables are related to the a table through the b table. Would it not be great, if there was a single query that could provide the foreign key chains? Well, in MySQL 8 you can get this using a common table expression (CTE).

Querying Foreign Key Chains – Step by Step Tip: If you are just interested in the final query, skip to the next subsection.

The query will use a recursive common table expression. This requires a seed query and a recursive query (that works on the rows generated in the previous iteration). A good seed query is similar to what we had for the basis query. However, to make it possible to aggregate all of the steps in the chain, the chain will be generated as a JSON array with each part of the chain being a JSON object. The seed query becomes:

mysql> SELECT 1 AS Level, TABLE_SCHEMA, TABLE_NAME, JSON_ARRAY( JSON_OBJECT('Level', 1, 'TableSchema', TABLE_SCHEMA, 'TableName', TABLE_NAME, 'ColumnNames', JSON_ARRAYAGG(COLUMN_NAME) ) ) AS ReferenceChain FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'db1' AND REFERENCED_TABLE_NAME = 'a' GROUP BY TABLE_SCHEMA, TABLE_NAME; +-------+--------------+------------+---------------------------------------------------------------------------------+ | Level | TABLE_SCHEMA | TABLE_NAME | ReferenceChain | +-------+--------------+------------+---------------------------------------------------------------------------------+ | 1 | db1 | b | [{"Level": 1, "TableName": "b", "ColumnNames": ["a_id"], "TableSchema": "db1"}] | | 1 | db1 | d | [{"Level": 1, "TableName": "d", "ColumnNames": ["a_id"], "TableSchema": "db1"}] | | 1 | db1 | e | [{"Level": 1, "TableName": "e", "ColumnNames": ["a_id"], "TableSchema": "db1"}] | +-------+--------------+------------+---------------------------------------------------------------------------------+ 3 rows in set (0.01 sec)

Now, you can take each of these relations and look for tables having a foreign key to them, and so forth. That is the recursive part of the query. There is one complication though: GROUP BY is not allowed in the recursive part. The workaround is to use a subquery:

mysql> WITH RECURSIVE `reference` (Level, TABLE_SCHEMA, TABLE_NAME, ReferenceChain) AS ( SELECT 1, TABLE_SCHEMA, TABLE_NAME, JSON_ARRAY( JSON_OBJECT('Level', 1, 'TableSchema', TABLE_SCHEMA, 'TableName', TABLE_NAME, 'ColumnNames', JSON_ARRAYAGG(COLUMN_NAME) ) ) FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'db1' AND REFERENCED_TABLE_NAME = 'a' GROUP BY TABLE_SCHEMA, TABLE_NAME UNION SELECT r.Level+1, ko.TABLE_SCHEMA, ko.TABLE_NAME, JSON_ARRAY_APPEND( ReferenceChain, '$', (SELECT JSON_OBJECT('Level', r.Level+1, 'TableSchema', TABLE_SCHEMA, 'TableName', TABLE_NAME, 'ColumnNames', JSON_ARRAYAGG(COLUMN_NAME) ) FROM information_schema.KEY_COLUMN_USAGE ki WHERE ki.TABLE_SCHEMA = ko.TABLE_SCHEMA AND TABLE_NAME = ko.TABLE_NAME AND ki.REFERENCED_TABLE_SCHEMA = ko.REFERENCED_TABLE_SCHEMA AND ki.REFERENCED_TABLE_NAME = ko.REFERENCED_TABLE_NAME) ) FROM `reference` r INNER JOIN information_schema.KEY_COLUMN_USAGE ko ON ko.REFERENCED_TABLE_SCHEMA = r.TABLE_SCHEMA AND ko.REFERENCED_TABLE_NAME = r.TABLE_NAME ) SELECT Level, TABLE_SCHEMA, TABLE_NAME, JSON_PRETTY(ReferenceChain) AS ReferenceChain FROM `reference`\G *************************** 1. row *************************** Level: 1 TABLE_SCHEMA: db1 TABLE_NAME: b ReferenceChain: [ { "Level": 1, "TableName": "b", "ColumnNames": [ "a_id" ], "TableSchema": "db1" } ] *************************** 2. row *************************** Level: 1 TABLE_SCHEMA: db1 TABLE_NAME: d ReferenceChain: [ { "Level": 1, "TableName": "d", "ColumnNames": [ "a_id" ], "TableSchema": "db1" } ] *************************** 3. row *************************** Level: 1 TABLE_SCHEMA: db1 TABLE_NAME: e ReferenceChain: [ { "Level": 1, "TableName": "e", "ColumnNames": [ "a_id" ], "TableSchema": "db1" } ] *************************** 4. row *************************** Level: 2 TABLE_SCHEMA: db1 TABLE_NAME: c ReferenceChain: [ { "Level": 1, "TableName": "b", "ColumnNames": [ "a_id" ], "TableSchema": "db1" }, { "Level": 2, "TableName": "c", "ColumnNames": [ "b_id1", "b_id2" ], "TableSchema": "db1" } ] *************************** 5. row *************************** Level: 2 TABLE_SCHEMA: db1 TABLE_NAME: f ReferenceChain: [ { "Level": 1, "TableName": "b", "ColumnNames": [ "a_id" ], "TableSchema": "db1" }, { "Level": 2, "TableName": "f", "ColumnNames": [ "b_id1", "b_id2" ], "TableSchema": "db1" } ] *************************** 6. row *************************** Level: 3 TABLE_SCHEMA: db1 TABLE_NAME: d ReferenceChain: [ { "Level": 1, "TableName": "b", "ColumnNames": [ "a_id" ], "TableSchema": "db1" }, { "Level": 2, "TableName": "c", "ColumnNames": [ "b_id1", "b_id2" ], "TableSchema": "db1" }, { "Level": 3, "TableName": "d", "ColumnNames": [ "c_id" ], "TableSchema": "db1" } ] *************************** 7. row *************************** Level: 3 TABLE_SCHEMA: db1 TABLE_NAME: f ReferenceChain: [ { "Level": 1, "TableName": "b", "ColumnNames": [ "a_id" ], "TableSchema": "db1" }, { "Level": 2, "TableName": "c", "ColumnNames": [ "b_id1", "b_id2" ], "TableSchema": "db1" }, { "Level": 3, "TableName": "f", "ColumnNames": [ "c_id" ], "TableSchema": "db1" } ] 7 rows in set (0.02 sec)

Here the ARRAY_APPEND() function is used to add the next part of the chain to ReferenceChain. The query relies on that the UNION is a UNION DISTINCT by default, so for the cases where there are two columns in the foreign key, the second (duplicate) row is automatically filtered out. For the main query, JSON_PRETTY() is used to make it easier to read the JSON document. If you are using the query in an application, this is not needed.

You can stop here. The result is correct. However, you may think there are more rows than you would expect. For example the chain a → b is there on its own (1st row) even though there are also tables with foreign keys to b. If you want to include subchains in the result, then you are all set. If you want to filter chains out that are part of another chain, a little more work is needed.

To filter out chains that are also included in subsequent rows, it is in one way or another necessary to keep track of whether a row has any child rows (i.e. that a subsequent row is generated based on the row). One way to do this is to have a serialized form of the chain, however the disadvantage is that you don’t know how long a string you need to store that (and the string length must be specified in the seed query). Another option is to generate an ID for each row – for example using the UUID() function. Then in rows generated from the row make a reference to the parent row. This is the option used here.

A disadvantage of this approach is that for tables with more then one column in the foreign key, the two rows generated are no longer identical. So, it is necessary to handle this in the main query. However, it is now easy to only include the end of the chains as these will not have another row with the parent ID set to the row’s ID. To find this, use a LEFT OUTER JOIN and look for rows where the optional row returns a NULL ID (that is, a row was not found).

Final Query

The final query thus becomes:

mysql> WITH RECURSIVE `reference` (ID, ParentID, Level, TABLE_SCHEMA, TABLE_NAME, ReferenceChain) AS ( SELECT UUID(), CAST(NULL AS CHAR(36)), 1, TABLE_SCHEMA, TABLE_NAME, JSON_ARRAY( JSON_OBJECT('Level', 1, 'TableSchema', TABLE_SCHEMA, 'TableName', TABLE_NAME, 'ColumnNames', JSON_ARRAYAGG(COLUMN_NAME) ) ) FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'db1' AND REFERENCED_TABLE_NAME = 'a' GROUP BY TABLE_SCHEMA, TABLE_NAME UNION SELECT UUID(), r.ID, r.Level+1, ko.TABLE_SCHEMA, ko.TABLE_NAME, JSON_ARRAY_APPEND( ReferenceChain, '$', (SELECT JSON_OBJECT('Level', r.Level+1, 'TableSchema', TABLE_SCHEMA, 'TableName', TABLE_NAME, 'ColumnNames', JSON_ARRAYAGG(COLUMN_NAME) ) FROM information_schema.KEY_COLUMN_USAGE ki WHERE ki.TABLE_SCHEMA = ko.TABLE_SCHEMA AND TABLE_NAME = ko.TABLE_NAME AND ki.REFERENCED_TABLE_SCHEMA = ko.REFERENCED_TABLE_SCHEMA AND ki.REFERENCED_TABLE_NAME = ko.REFERENCED_TABLE_NAME) ) FROM `reference` r INNER JOIN information_schema.KEY_COLUMN_USAGE ko ON ko.REFERENCED_TABLE_SCHEMA = r.TABLE_SCHEMA AND ko.REFERENCED_TABLE_NAME = r.TABLE_NAME ) SELECT DISTINCT r.Level AS NumLevels, JSON_PRETTY(r.ReferenceChain) AS ReferenceChain FROM `reference` r LEFT OUTER JOIN `reference` rc ON rc.ParentID = r.ID WHERE rc.ID IS NULL\G *************************** 1. row *************************** NumLevels: 1 ReferenceChain: [ { "Level": 1, "TableName": "d", "ColumnNames": [ "a_id" ], "TableSchema": "db1" } ] *************************** 2. row *************************** NumLevels: 1 ReferenceChain: [ { "Level": 1, "TableName": "e", "ColumnNames": [ "a_id" ], "TableSchema": "db1" } ] *************************** 3. row *************************** NumLevels: 2 ReferenceChain: [ { "Level": 1, "TableName": "b", "ColumnNames": [ "a_id" ], "TableSchema": "db1" }, { "Level": 2, "TableName": "f", "ColumnNames": [ "b_id1", "b_id2" ], "TableSchema": "db1" } ] *************************** 4. row *************************** NumLevels: 3 ReferenceChain: [ { "Level": 1, "TableName": "b", "ColumnNames": [ "a_id" ], "TableSchema": "db1" }, { "Level": 2, "TableName": "c", "ColumnNames": [ "b_id1", "b_id2" ], "TableSchema": "db1" }, { "Level": 3, "TableName": "d", "ColumnNames": [ "c_id" ], "TableSchema": "db1" } ] *************************** 5. row *************************** NumLevels: 3 ReferenceChain: [ { "Level": 1, "TableName": "b", "ColumnNames": [ "a_id" ], "TableSchema": "db1" }, { "Level": 2, "TableName": "c", "ColumnNames": [ "b_id1", "b_id2" ], "TableSchema": "db1" }, { "Level": 3, "TableName": "f", "ColumnNames": [ "c_id" ], "TableSchema": "db1" } ] 5 rows in set (0.03 sec)

The DISTINCT in the main part of the query ensures that duplicates due to multiple columns in the foreign key are filtered out.

Note: One thing this version of the query does not handle is circular key relations. For example if you add the column c_id to a with a foreign key to the c table, then an infinite number of chains will be created. So, there need to be a condition that detects when a loop is getting created. That is an exercise for the reader – or for a later blog.

Thus, this schema has five unique chains leading to the a tables. You can also verify this from the EER diagram – for reference, here it is again:

MySQL Workbench EER Diagram

C-Level MySQL

The old adage that time is money does not give any hints about buying more time. A MySQL Enterprise subscription does allow you to purchase time. Your technical staff has to do more with less time than ever before and operational delays add up extremely quickly.  Having the right tools when things go critical means staff can do what they need to do, when they need to do it.

 

So how does MySQL Enterprise Edition buy you more time?

 

When things inevitably go wrong, searching for free answers to problems on web sites with many dubious or flat out wrong solutions is a waste of precious time.  Informational websites like are fine sources of general information for researching technical issues but not when mission critical services are degraded or down.  An online recommendation to do what very large Enterprise Company does may not make sense for your company if you are not doing exactly what they are doing, at their scale. Getting the right help at crucial moments gets your business back on track faster.

A MySQL Enterprise Support Contract brings you the best technical support on the MySQL Database products by highly trained Oracle MySQL Engineers. You get the right answers to your questions.

 

Tools That Really Help

 

MySQL Enterprise Monitor is included in Enterprise Edition and is the best tool to monitor all your instances and report issues before they escalate.  MEM can make recommendations about your schemas and systems to achieve better performance and provide better understanding of what is really happening to your data. Fixing potential issues before they can blossom is much less expensive.  And you get proactive monitoring to keep on track of you databases instances that will inform your staff how your data is doing letting them concentrate on other issues.

 

Keeping your data safe is a major concern these days in a world with ever shrinking privacy.  MySQL Enterprise Backup ensures that your data is properly archived in case of accident by staff or hardware failure. Data losses are almost inevitable and you need to be able to quickly restore that data and MySQL Enterprise Backup is designed to do just that.

 

By default, MySQL 8.0 encrypts data between server and clients with TLS. But to keep your on-disk data safe from prying eyes, it helps to have at rest encryption paired with Oracle Key Vault to manage encryption key. The auditors doing your PCI or GDPR inspections will know Oracle Key Vault from thousands of other customers and will also recognize the Oracle Audit Vault for its deep reporting capabilities.  Enterprise Authentication can mask designated fields from those not authorized to view that data.  And Enterprise Firewall learns the patterns of your Structured Query Language traffic and can block malicious queries before they reach your database server. Outside software bolt-ons to manage your database security can be found but are not integrated to work together for your protection and managing all those desperate pieces is a drain of your staff’s time, often laborious to maintain and can draw unwanted attention from your auditors.

 

You will receive regular updates of the MySQL Enterprise Software directly from Oracle where the code is written.  It is easy to miss releases of software in the Open Source world and find yourself vulnerable to an exploit in security or missing features in later releases. The vulnerability is identified when the patch is released.

 

Conclusion

 

A MySQL Enterprise Subscription allows you and your staff to concentrate on the issues your business is facing.  It provides the tools you need for oversight, management, security, and safety of your critical data and “buys you more time” to work on delivering value to your enterprise.

 

A Tale of Two JSON Implementations - MySQL and MariaDB

JSON has proven to be a very import data format with immense popularity. A good part of my time for the last two or so years has been dedicated to this area and I even wrote a book on the subject.  This is a comparison of the implementations of handling JSON data in MySQL and MariaDB. I had requests from the community and customers for this evaluation.


JSON Data Types Are Not All Equal
MySQL added a JSON data type in version 5.7 and it has proven to be very popular.  MariaDB has  JSON support  version 10.0.16 but is actually an alias to a longtext data type so that statement based replication from MySQL to MariaDB is possible.

MySQL stores  JSON documents are converted to an internal format that permits quick read access to document elements.  The binary format is structured to enable the server to look up sub-objects or nested values directly by key or array index without reading all values before or after them in the document.  From a practical standpoint the big thing most people notice is that the data is alphabetized.

MariaDB does no such optimization and their documentation states the claim that the performance is at least equivalent.

JSON TablesThe first comparison is 'how hard is it to put non-JSON data into a JSON column?'  The standard (see below under 'extra'), by the way, states that the data must be in the UTF8MB4 character set. And what is going on 'underneath the cover'?

The first step is to create tables to hold JSON data.


MySQL:

create table t1 (j json);
Query OK, 0 rows affected (0.1826 sec)

 MySQL  localhost:33060+ ssl  json  SQL > show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `j` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.0149 sec)

 MySQL  localhost:33060+ ssl  json  SQL > desc t1;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| j     | json | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.0028 sec)


MariaDB:

MariaDB [json]> create table t2 (j json);
Query OK, 0 rows affected (0.046 sec)

MariaDB [json]> show create table t2;
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
 `j` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [json]> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| j     | longtext | YES  | | NULL |      |
+-------+----------+------+-----+---------+-------+
1 row in set (0.001 sec)

MariaDB [json]>




Conclusion: Both use UTF8MB4 and the underlying tables are roughly equivalent.

Checking Constraints Ensuring that only JSON gets into the JSON columns is essential. MySQL does this by default for the JSON data type but MariaDB does not.

MySQL:

insert into t1 values('junk');

ERROR: 3140: Invalid JSON text: "Invalid value." at position 0 in value for column 't1.j'.

MariaDB:



MariaDB [json]> insert into t2 values ('junk');
Opps! We now have a NON-JSON value in the table.

To be fair, MariaDB does let you establish a constraint check on the column to avoid this issue.


MariaDB [json]> drop table t2;
Query OK, 0 rows affected (0.046 sec)
MariaDB [json]> create table t2 (j json, check (json_valid(j))); Query OK, 0 rows affected (0.049 sec)

MariaDB [json]> insert into t2 values ('junk'); ERROR 4025 (23000): CONSTRAINT `CONSTRAINT_1` failed for `json`.`t2`
Conclusion: MySQL does as advertised by rejecting non-JSON data by default while MariaDB can do so with a little extra work.
Loading Data
The only major complaint about JSON data is that there is just so much of it. So having a way to bulk load is important.

MySQL:

MySQL's new shell (mysqlsh) has Python, JavaScipt, and SQL modes.  It is very easy to use either the Python or JavaScript modes to write a quick script to read bulk data sets line by line.  Giuseppe 'Datacharmer' Maxia has a great example of converting data from MongoDB to MySQL using the shell and I have used that example code extensively in the past. But now 8.0.13 has a bulk loader built into the shell.
The New MySQL Shell's utility to bulk load JSON
This utility functions allows JSON data to be stores in a JSON Document Collection (MySQL Document Store) or in a JSON column of s structured table. Very handy.


MariaDB:

Maria does not have an equivalent to the new MySQL Shell nor does it have a bulk loader utility for JSON data. 

I tired to use the Connect Storage Engine (not installed by default) and follow the examples on the CONNECT JSON Table Type page without luck. The Connect engine is supposed to have the capability to auto discover a table structure and define the table itself.  Mea Cupla for not getting this to work and I would encourage those who do use this feature to send me pointers PLEASE! But after several hours and tedious attempts to follow the examples exactly it was time to move on to something else.

Conclusion: MySQL does better loading data, especially in bulk.
JSON Functions
Both databases have functions and there are some differences.


  1. Functions That Create JSON Values
    1. JSON_ARRAY, JSON_OBJECT and JSON_QUOTE  are found in both and work the same.
  2. Functions That Search JSON Values
    1. JSON_CONTAINS, JSON_CONTAINS_PATH, JSON_EXTRACT, JSON_KEYS, and JSON_SEARCH are found in both and work the same.  However only MySQL has the -> and ->>  shortcuts for JSON_EXTRACT and JSON_UNQUOTE(JSON_EXTRACT)).. 
  3. Functions That Modify JSON Values
    1. JSON_ARRAY_APPEND, JSON_ARRAY_INSERT, JSON_INSERT, JSON_REMOVE, JSON_REPLACE, JSON_SET, and JSON_UNQUOTE are in both and work the same.  
    2. MariaDB has JSON_MERGE which has been deprecated in MYSQL 8.0.3 and replaced with JSON_MERGE_PRESERVE & JSON_MERGE_PATCH.   MySQL 8.0 supports the JSON Merge Patch format defined in RFC 7396 function.
  4. Functions That Return JSON Value Attributes
    1. JSON_DEPTH, JSON_LENGTH, JSON_TYPE, and JSON_VALID are found in both and work the same.
  5. Table Functions
    1. JSON_TABLE which allows you to use unstructured JSON data in a structured temporary table is in MySQL only.
  6. JSON Utility Functions
    1. JSON_PRETTY, JSON_STORAGE_FREE, and JSON_STORAGE_SIZE are only in MySQL.
  7. Other Functions
    1. JSON_ARRAYAGG and JSON_OBJECTAGG are only in MySQL and are very handy for turning non JSON data into JSON.
    2. JSON_VALUE and JSON_QUERY are MariaDB specific and return an object/array or a scalar respectively.

Conclusion:  I tested both database's functions and found they worked as advertised.  JSON_PRETTY is much missed by my eyes when dealing with documents with several levels of embedding with MariaDB.  The merging functions are richer for MySQL especially for those who need to follow the merge patch standard. And JSON_TABLE is a valuable tool when trying to treat unstructured data as structured data temporarily especially combined with CTEs and Windowing Functions that were introduced in MySQL 8.   
Updating Data
Updating data can be expensive and JSON data can be expansive.
MySQL:
MySQL 5.7 required a complete re-write of the document.  If this is something you do a lot then you need to consider upgrading to MySQL 8.
MySQL 8.0's  the optimizer can perform a partial, in-place update of a JSON column instead of removing the old document and writing the new document in its entirety to the column.  Replication. But there are conditions to this: 1) It has to be a JSON column, 2) The UPDATE statement uses any of the three functions JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() to update the column but a direct set like UPDATE mytable SET jcol = '{"a": 10, "b": 25'}) does not work, 3) The input column and the target column must be the same column, 4) All changes replace existing array or object values with new ones, and do not add any new elements to the parent object or array, and 5) The value being replaced must be at least as large as the replacement value. In other words, the new value cannot be any larger than the old one (An exception to this requirement occurs when a previous partial update has left sufficient space for the larger value. You can use the function JSON_STORAGE_FREE() see how much space has been freed by any partial update). If you can follow those rules the optimizer will do partial rewrites.
MariaDB:
I searched the MariaDB docs and Jira but found no mention of partial JSON column updates.  If anyone has links, please send them to me. So it appears that MariaDB does a full rewrite.
Conclusion:  MySQL is more efficient here.
Replication  Efficient replication is a must and goes double for JSON with the potential of very large document payloads having to cross network connections.
MySQL:
In MySQL 5.7 an update to a JSON column was written to the binary log as the complete document. In MySQL 8.0, it is possible to log partial updates to JSON documents.   In statement based replication JSON partial updates are always logged as partial updates. 
But in row based replication they are logged as complete documents. To enable the logging of partial updates, set binlog_row_value_options=PARTIAL_JSON. Please note that the replication master has this variable set, partial updates received from that master are handled and applied by a replication slave regardless of the slave's own setting for the variable.

MariaDB:

I searched the MariaDB docs and Jira but found no mention of partial JSON Replication updates.  If anyone has links, please send them to me. So expect the full document to be replicated.

Conclusion: MySQL can be more efficient.

X DevAPI / Document StoreOnly MySQL has the Document Store and the underlying Document Store and it is proving to be very popular with customers.  The ability to use a database as a NoSQL Document Store and a relational database is popular.  Not having embedded strings in their code and having the IDEs help is driving developers to this approach. 

MariaDB's CTO said at Zendcon that they will support if customers demand.

Conclusion:  MySQL is the sole choice here.


Overall ConclusionI have been using MySQL's JSON data type since the 5.7 DMRs and know them well.  MariaDB's implementation seems very familiar and works as expected.  MySQL is superior in the partial updates of data and replication, functions such as JSON_TABLE, the X DevAPI, and bulk loading of data. 

Extra
The IETF's standard is here and makes pretty quick reading.

MySQL Replication Data Recovery using 'mysqlbinlog' - Part II

MySQL Replication Data Recovery using 'mysqlbinlog' - Part II

The previous post (PART-I)
http://mysqlhk.blogspot.com/2018/10/mysql-replication-recovery-from-binlog.html

It describes the Replication Recovery from binlog by using those binlog files to be treated as Relay Log.  The Relay Log mechanism when the server is startup, the recovery is the SQL_THREAD applier to apply data to the database.    Check on the PART-I post for details.

Part II is about using the MySQL utility "mysqlbinlog" to dump the content from binlog files and apply the SQL to the Database.

Documentation
https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html
https://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery.html

The following sections describe the tutorial for Replication Data Recovery using 'mysqlbinlog'. 

The tutorial includes the followings topics (Note: Using GTID replication)
1. Initialize MySQL Instances (3316, 3326) installation & Configure Replication (Master and Slave)
2. Create Database and Table (test1.mytable1) with 8 rows
3. Stop the IO_THREAD on the Slave Server to simulate the situation where Data cannot transport to the RELAY Log on Slave Server.  Creating more data on Master and using "FLUSH LOGS;" to switching log files on Master server.
4. Checking the Replication Status on Slave Server for the Last retrieved position and file on Master.
5. Using 'mysqlbinlog' utility to extract SQL from the Master's binlog files starting from the position and file registered on Slave status.
6. Apply the SQLs to the Slave Server to recover the data and Check the Slave Status

7. Resume Replication "START SLAVE IO_THREAD FOR CHANNEL '<channel name>';"

Installation & Configurationo Assumptions
1. basedir = /usr/local/mysql   : The MySQL Binary Package
2. datadir = /home/mysql/data/server1 : The Server 1 Data Directory
3. datadir = /home/mysql/data/server2 : The Server 2 Data Directory
4. Using GTID and Channel for the replication setup
5. OS user for executing the commands - OS user : 'mysql'  (Do not use root
6. Configuration Files   (/home/mysql/data/my1.cnf & /home/mysql/data/my2.cnf)

/home/mysql/data/my1.cnf
[mysqld]
server-id=1
datadir=/home/mysql/data/server1
basedir=/usr/local/mysql

port=3316
socket=/home/mysql/data/server1/mysqld.sock

mysqlx-port=33160
mysqlx-socket=/home/mysql/data/server1/mysqlx.sock

log-error=/home/mysql/data/server1/mysqld.error
log-bin=mysqllog
relay-log=relay.bin

gtid-mode=on
enforce-gtid-consistency
master_info_repository=TABLE
relay_log_info_repository=TABLE


/home/mysql/data/my2.cnf
[mysqld]
server-id=2
datadir=/home/mysql/data/server2
basedir=/usr/local/mysql

port=3326
socket=/home/mysql/data/server2/mysqld.sock

mysqlx-port=33260
mysqlx-socket=/home/mysql/data/server2/mysqlx.sock

log-error=/home/mysql/data/server2/mysqld.error
log-bin=mysqllog
relay-log=relay.bin

gtid-mode=on
enforce-gtid-consistency
master_info_repository=TABLE
relay_log_info_repository=TABLE


STEP 1 : Initialize MySQL Instances and Configure Replication

Initialize Data Folder for 2 instances

# /usr/local/mysql/bin/mysqld --defaults-file=/home/mysql/data/my1.cnf --initialize-insecure
# /usr/local/mysql/bin/mysqld --defaults-file=/home/mysql/data/my2.cnf --initialize-insecure

Start up MySQL Instances
# /usr/local/mysql/bin/mysqld --defaults-file=/home/mysql/data/my1.cnf &
# /usr/local/mysql/bin/mysqld --defaults-file=/home/mysql/data/my2.cnf &

 Configure Replication User on Master and Slave
# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3316 -e "
drop user if exists repl@'localhost';
create user repl@'localhost' identified with mysql_native_password by 'repl';
grant replication slave on *.* to repl@'localhost';
"

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 -e "
drop user if exists repl@'localhost';
create user repl@'localhost' identified with mysql_native_password by 'repl';
grant replication slave on *.* to repl@'localhost';
"

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3316 << EOL3316
reset master;
reset slave;
EOL3316

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 << EOL3326
reset master;
reset slave;
EOL3326

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 << EOL1

change master to
master_host='127.0.0.1',
master_user='repl',
master_password='repl',
master_port=3316,
master_auto_position=1
for channel 'channel1';

start slave for channel 'channel1';

show slave status for channel 'channel1'\G

EOL1

 STEP 2 : Create Database and Table (test1.mytable1) with 8 rows of data

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3316

mysql> create database if not exists test1;
mysql> create table if not exists test1.mytable1 (f1 int not null auto_increment primary key, f2 varchar(20));

mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');



The data should now be replicated to Slave.  To show the data on Slave and Check the Replication Status, Execute :

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 -e "select count(*) from test1.mytable1;"

 /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 -e "show slave status for channel 'channel1'\G"

STEP 3 : Stop the IO_THREAD on Slave Server

This is to simulate the connection between master and slave being disconnected.  So there is no more NEW update to RELAY LOG on Slave Server.   To do this :

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 -e "stop slave IO_THREAD for channel 'channel1';"

Creating 16 more rows on Master Server, however they are not replicated to Slave Server.

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3316

mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');

At this point, Master Server has 32 rows.  However the Slave Server has ONLY 16 rows.

Executing "FLUSH LOGS" to flush the binlog and switch to new logs.  Inserting 16 more rows.
# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3316

mysql> FLUSH LOGS;
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');

mysql> FLUSH LOGS;
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');

After executing 2 times of "FLUSH LOGS;" commands, there are 3 binlog files created in the datadir "/home/mysql/data/server1" of the Master Server.   They are
mysqllog.000001
mysqllog.000002
mysqllog.000003
mysqllog.index

Number of ROWS on MASTER : 48
Number of ROWS on SLAVE : 16

***************************************************************************************************************
***  To recover the DATA from BINLOG of the Master Server to Slave Server   
***************************************************************************************************************

STEP 4 : Check the status on Slave  Server for what the last position of the BINLOG is and which file is the LAST file on Master Server being used.

Assuming we have the binlog files from MASTER Files - from datadir "/home/mysql/data/server1"
-- mysqllog.000001
-- mysqllog.000002
-- mysqllog.000003
-- mysqllog.index

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 -e "show slave status\G"
E.g. The output from command may look like this

 *************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 3316
                Connect_Retry: 60
              Master_Log_File: mysqllog.000001
          Read_Master_Log_Pos: 5422
 
              Relay_Log_File: relay-channel1.000002
                Relay_Log_Pos: 5634
        Relay_Master_Log_File: mysqllog.000001
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
...

...
       Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-18
            Executed_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-18
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: channel1
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0

 **************************************************
The Master Log File : mysqllog.000001
and Master Log Pos : 5422

This is the last position that the Slave IO_THREAD has read from Master Binlog file.

STEP 5 : Use 'mysqlbinlog' utility to extract SQL from MASTER Binlog files
Note : There are 3 binlog files from datadir "/home/mysql/data/server1"
-- mysqllog.000001
-- mysqllog.000002
-- mysqllog.000003
-- mysqllog.index

The command should include ALL binlog files starting from the one indicated by the Slave Status "Master Log Fie"

# cd /home/mysql/data/server1/
# /usr/local/mysql/bin/mysqlbinlog --start-position=5422 mysqllog.000001 mysqllog.000002 mysqllog.000003 > /tmp/my.sql

The output '/tmp/my.sql' contains ALL SQL statements with data after the transaction from position = 5422.   The FIRST mysqllog.000001 in the command is important which is the Master Log File from the Slave Status.

STEP 6 : Apply the SQL to the Slave Server and Check the Slave Status

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 < /tmp/my.sql

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 -e "show slave status\G"
E.g. The output from command may look like this

*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 3316
                Connect_Retry: 60
              Master_Log_File: mysqllog.000001
          Read_Master_Log_Pos: 5422
               Relay_Log_File: relay-channel1.000002
                Relay_Log_Pos: 5634
        Relay_Master_Log_File: mysqllog.000001
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
...
...
           Retrieved_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-18
            Executed_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-54
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: channel1
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0

************************************************************
Comparing the output from the previous Slave Status, the Master Log File and Post do not change.  But the Retrieved GTID_Set and Executed_Gtid_set indicated the execution has already applied the extra rows on the Master.

BEFORE data recovery from BINLOG
           Retrieved_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-18
            Executed_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-18

AFTER data recovery from BINLOG 
           Retrieved_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-18
            Executed_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-54

The outcome is from recovery is to get more transactions applied to slave.




STEP 7 :  Resume Replication

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 -e "START SLAVE IO_THREAD FOR CHANNEL 'channel1';"

Creating extra 16 rows on MASTER and check data on SLAVE
# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3316

mysql> FLUSH LOGS;
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');


Check the Slave Status
# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 -e "show slave status\G"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 3316
                Connect_Retry: 60
              Master_Log_File: mysqllog.000003
          Read_Master_Log_Pos: 10689
               Relay_Log_File: relay-channel1.000003
                Relay_Log_Pos: 5700
        Relay_Master_Log_File: mysqllog.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...
...
           Retrieved_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-18:55-72
            Executed_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-72
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: channel1
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
*********************************************************************
The Replication Channel has already been resumed.  The IO and SQL Threads are up and running :
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

The Master_log_file and Read_Master_log_Pos has already been started with new number and file name:
               Master_Log_File: mysqllog.000003
          Read_Master_Log_Pos: 10689

The Retrieved_Gtid_Set and Executed_Gtid_Set on Slave Server has already been updated with latest information :
           Retrieved_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-18:55-72
            Executed_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-72


The Master and Slave Servers are UP and RUNNING.


Oracle Recognizes Percona Fixes in MySQL 8.0

MySQL 8.0 Code Contributions (Shutterstock)

An Oracle engineer thanked two Percona engineers by name, along with engineers from Facebook and elsewhere, for their recent MySQL 8.0 source code contributions. Oracle incorporated their work into its latest MySQL production release (8.0.13).

Percona’s Zsolt Parragi authored a patch for a rare replication bug that left locked mutexes in production builds following debug crashes (bug #89421). Yura Sorokin authored a patch to fix wrong file I/O statistics in the MySQL Performance Schema (bug #90264).  Percona CTO Vadim Tkachenko cited both patches as examples of Percona’s continuing contributions to the open source community. This is one of Percona’s core ideals since the company’s founding in 2006.  

In past last three years alone, Percona has reported on over 600 bugs in the MySQL server.  Most of these bug reports Percona provided Oracle engineers with reproducible test cases. They also contained detailed stack traces and other information appropriate for analyzing and fixing the bug. During that same period, Oracle accepted at least 20 patches authored by Percona engineers into its MySQL code base.

Over its 12 year history, Percona engineers have created numerous open source projects that have won widespread community adoption.  These include Percona Server for MySQL, an enhanced version of the flagship MySQL database, Percona XtraDB Cluster, a high availability database solution, Percona Server for MongoDB®, an enhanced fork of the MongoDB® database, a  Percona XtraBackup, a database backup tool, Percona Tookit, a suite of utilities for database administrators, and the most recent, Percona Monitoring and Management (PMM), a GUI tool providing visibility into database performance.

Window Functions with Unusual Boundaries

Somebody on Freenode wanted this:

Source Result +----+------+ +----+------+ | id | x | | id | c | +----+------+ +----+------+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 2 | | 3 | NULL | | 3 | NULL | | 4 | NULL | -> | 4 | NULL | | 5 | 1 | | 5 | 1 | | 6 | NULL | | 6 | NULL | | 7 | 1 | | 7 | 3 | | 9 | 1 | | 9 | 3 | | 10 | 1 | | 10 | 3 | +----+------+ +----+------+

The result uses the NULL values in x as boundaries of windows, and counts the number of rows within each window. I don't know why anyone wants such a thing; it is not ours to reason why...

Anyway, the point is that you can use arbitrary expressions, even subqueries, to define your window partitions.

SELECT id, -- Count of rows in windows bound by NULL values in x IF( x IS NULL, NULL, COUNT(*) OVER (PARTITION BY ( -- Partition by the number of earlier NULLs SELECT COUNT(*) FROM t AS t1 WHERE t1.id

How does it work?

First, let's see what that subquery is all about:

SELECT id, x, (SELECT COUNT(*) FROM t AS t1 WHERE t1.id

By counting the number of "NULL rows" appearing earlier in the table, we get a value we can use to find the starting point of each window.

That alone goes one row too far, though - the "NULL row" which should end each window gets included in the window. However, you can use multiple expressions to partition windows.

SELECT id, x, (SELECT COUNT(*) FROM t AS t1 WHERE t1.id

The combination of (p1, p2) neatly partition the rows, so each "NULL row" is by itself, and non-NULL rows are together.

Data Masking in MySQL

If you’re here, that probably means you’re about to acquire some sensitive data to take care of. Or that you’ve already acquired it and are protecting it, and you want to see how MySQL Enterprise Data Masking and De-Identification features can help you do it in an easier, better and more efficient manner.…

My Slides about MySQL 8.0 Performance from #OOW18 and #PerconaLIVE 2018


As promised, here are slides about MySQL 8.0 Performance from my talks at Oracle Open World 2018 and Percona LIVE Europe 2018 -- all is combined into a single PDF file to give you an overall summary about what we already completed, where we're going in the next updates within our "continuous release", and what kind of performance issues we're digging right now.. ;-))
Also, I'd like to say that both Conferences were simply awesome, and it's great to see a constantly growing level of skills of all MySQL Users attending these Conferences ! -- hope you'll have even more fun with MySQL 8.0 now ;-))

Percona Live Europe 2018: What’s Up for Wednesday

Welcome to Wednesday at Percona Live Europe 2018! Today is the final day! Check out all of the excellent sessions to attend.

Please see the important updates below.

Download the conference App

If you haven’t already downloaded the app, go to the app store and download the official Percona Live App! You can view the schedule, be alerted for any important updates, create your own personalized schedule, rate the talks and interact with fellow attendees.

For Apple: Download here
For Android: Download here

Rate the talks!

We want to encourage all attendees to rate the talks which you have attended. Please take a few moments to rate the talks which you attended on the Percona Live App.

Registration and Badge Pick Up

Registration is open from 8 am.

AWS Cloud Track

Join the featured cloud track today where AWS will be presenting A Deep Dive on Amazon Aurora, Zero to Serverless in 60 Seconds, Top 10 Mistakes When Migrating From Oracle to PostgreSQL to name a few! These sessions will run in Wallstreet 2!

Keynotes

Keynotes begin promptly at 9:15 am. Please be seated and ready! Arrive early to secure your spot! Keynotes will take place in Dow Jones next to the expo area.

Expo Opening Hours

Have you visited the expo area yet? The expo will be open from 8:00 am to 4:30 pm today.

Conference Slides

Conference slides and presentations will be available to view after the conference and will be located on the Percona Live Europe website.

Breaks and Lunch

Coffee Breaks: The morning break is at 10:50 am – 11:20 am and the afternoon break from 4:10 pm- 4:30 pm (Conference Floor Foyer)
Lunch: 1:10 pm -2:10 pm Lunch will be served on the conference floor and in Showroom and Gaia restaurant on the lobby level.

With Thanks to Our Sponsors!

We hope you have enjoyed the conference! Save the Date!

Percona Live 2019 will happen in Austin, Texas. Save the dates in your diary for May 28-30, 2019!

The conference will take place just after Memorial Day at The Hyatt Regency, Austin on the shores of Lady Bird Lake. This is also an ideal central location for those who wish to extend their stay and explore what Austin has to offer! Call for papers, ticket sales and sponsorship opportunities will be announced soon, so stay tuned!

Common Table Expressions: A Shocking Difference Between MySQL and MariaDB

Common Table Expressions (CTEs) are a very useful tool and frankly a big improvement on sub-queries.  But there are differences in how they are implemented in MySQL and MariaDB.  That  is not too surprising since the code fork many years ago. Different engineers implementing the same idea will have different approaches (and sometimes results). But differences in implementation are often important and, in this case, shockingly different.

Jesper Wisborg Krogh at Oracle OpenWorld and CodeOne gave a series of presentations and hands on labs that were excellent. He is an amazing Support Engineer and a great presenter of material at conferences.  In the lab for Common Table Expressions he did point out to me an interesting problem in MariaDB's implementation of CTEs. 

The Problem In a Nutshell
On the PostgreSQL Wiki, there is a
an SQL query (requires PostgreSQL 8.4 or MySQL 8.0) that produces an ASCII-art image of the Mandelbrot set written entirely in SQL 2008 conforming SQL.

-- Based on: https://wiki.postgresql.org/wiki/Mandelbrot_set

WITH RECURSIVE x(i) AS (
    SELECT CAST(0 AS DECIMAL(13, 10))
     UNION ALL
    SELECT i + 1
      FROM x
     WHERE i < 101
),
Z(Ix, Iy, Cx, Cy, X, Y, I) AS (
    SELECT Ix, Iy, X, Y, X, Y, 0
      FROM (SELECT CAST(-2.2 + 0.031 * i AS DECIMAL(13, 10)) AS X,
                  i AS Ix FROM x) AS xgen
           CROSS JOIN (
               SELECT CAST(-1.5 + 0.031 * i AS DECIMAL(13, 10)) AS Y,
                      i AS iY FROM x
           ) AS ygen
    UNION ALL
    SELECT Ix, Iy, Cx, Cy,
           CAST(X * X - Y * Y + Cx AS DECIMAL(13, 10)) AS X,
           CAST(Y * X * 2 + Cy AS DECIMAL(13, 10)), I + 1
      FROM Z
     WHERE X * X + Y * Y < 16.0
           AND I < 27
),
Zt (Ix, Iy, I) AS (
    SELECT Ix, Iy, MAX(I) AS I
      FROM Z
     GROUP BY Iy, Ix
     ORDER BY Iy, Ix
)
SELECT GROUP_CONCAT(
           SUBSTRING(
               ' .,,,-----++++%%%%@@@@#### ',
               GREATEST(I, 1),
               1
           ) ORDER BY Ix SEPARATOR ''
       ) AS 'Mandelbrot Set'
  FROM Zt
 GROUP BY Iy
 ORDER BY Iy;


The code is best run on the new MySQL Shell or MySQL Workbench but works well on the old MySQL shell but with desegregated output.
An abbreviated image of  the Mandelbot SQL output (See above for listing) , truncated for size. Produced with the new MySQL Shell (mysqlsh) on MySQL 8.0.13 
But then Jesper mention he had tested the SQL the night before the lab and it runs quickly on MySQL - 0.7445 seconds on my Windows laptop. The Mandelbrot SQL code ran in 0.74445 seconds on MySQL 8.0.13


But not on MariaDB.  Jesper said he ran the same code on MariaDB 10.3 but killed it after fifteen minutes.  It was late and he had to get up early to get to San Francisco.
Double Check
With a fresh install of Fedora 29 and MariaDB 10.3.10, I ran the Mandelbrot SQL code.  And I waited for the result.  After an hour I went to lunch. But the query was still running when I returned.  I went on to other work an occasionally checking back and running SHOW PROCESSLIST  from time to time to make sure it had not died. 
But after two hours I hit control-C as I had other tasks for that system.  There are some interesting Recursive CTE problems listed on Jira,MariaDB.org but nothing obviously relevant.
But I was able to confirm that MySQL's implementation of Recursive CTEs works well but I can not say that about MariaDB's implementation. 

Temporary tables are now allowed in transactions when GTIDs are enabled

There has been a recurring request from our users to remove a limit when using global transaction identifiers (GTIDs) and temporary tables together. The limit is that temporary tables cannot be created and dropped inside a transaction when GTIDs are turned on.…

Welcome to Percona Live Europe 2018 Tuesday Keynotes and Sessions!

Hello, open source database enthusiasts at Percona Live Europe 2018! There is a lot to see and do today, and we’ve got some of the highlights listed below.

On Facebook? Go here for some pics that captured the action on Percona Live Europe 2018 Tutorials day (Monday, Nov. 5, 2018). 

 

Download the Conference App

We apologize for the confusion yesterday on the app but can assure you, the schedule and timings have been updated! If you haven’t already downloaded the app, go to the app store and download the official Percona Live App! You can view the schedule, be alerted for any important updates, create your own personalized schedule, rate the talks and interact with fellow attendees.

For Apple: Download here
For Android: Download here

Registration and Badge Pick Up

Registration is open from 8 am. The registration desk is located at the top of the stairs on the first floor of the Radisson Blu Hotel. 

Keynotes

Keynotes begin promptly at 9:15 am. Please be seated and ready! Arrive early to secure your spot! Keynotes will take place in Dow Jones next to the expo area. 

Community Networking Reception

Join the Open Source community on Tuesday evening at Chicago Meatpackers (Riverside), Frankfurt!

This is a great opportunity to socialize and network with Percona Live Attendees and Other Open Source Enthusiasts who’d like to come along too!

This is not a ticketed event or an official event of Percona Live Europe, simply an open invitation with a place to congregate for food and drinks! An A La Carte food menu and cash bar will be available.

Expo Opening Hours

The expo will be open from 8:00 am to 4:30 pm today. 

Breaks & Lunch

Coffee Breaks: Sponsored by Facebook!  AM Break will be at 10:50am – 11:20 am and the Afternoon break from  4:10 pm- 4:30 pm (Conference Floor Foyer)
Lunch: 1:10 pm -2:10 pm Lunch will be served on the conference floor and in Showroom and Gaia restaurant on the lobby level.

With thanks to our Sponsors!

Enjoy the conference!

How to Quickly Add a Node to an InnoDB Cluster or Group Replication

Quickly Add a Node to an InnoDB Cluster or Group Replication (Shutterstock)

In this blog, we’ll look at how to quickly add a node to an InnoDB Cluster or Group Replication using Percona XtraBackup.

Adding nodes to a Group Replication cluster can be easy (documented here), but it only works if the existing nodes have retained all the binary logs since the creation of the cluster. Obviously, this is possible if you create a new cluster from scratch. The nodes rotate old logs after some time, however. Technically, if the

gtid_purged set is non-empty, it means you will need another method to add a new node to a cluster. You also need a different method if data becomes inconsistent across cluster nodes for any reason. For example, you might hit something similar to this bug, or fall prey to human error. Hot Backup to the Rescue

The quick and simple method I’ll present here requires the Percona XtraBackup tool to be installed, as well as some additional small tools for convenience. I tested my example on Centos 7, but it works similarly on other Linux distributions. First of all, you will need the Percona repository installed:

# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm -y -q

Then, install Percona XtraBackup and the additional tools. You might need to enable the EPEL repo for the additional tools and the experimental Percona repo for XtraBackup 8.0 that works with MySQL 8.0. (Note: XtraBackup 8.0 is still not GA when writing this article, and we do NOT recommend or advise that you install XtraBackup 8.0 into a production environment until it is GA). For MySQL 5.7, Xtrabackup 2.4 from the regular repo is what you are looking for:

# grep -A3 percona-experimental-\$basearch /etc/yum.repos.d/percona-release.repo [percona-experimental-$basearch] name = Percona-Experimental YUM repository - $basearch baseurl = http://repo.percona.com/experimental/$releasever/RPMS/$basearch enabled = 1

# yum install pv pigz nmap-ncat percona-xtrabackup-80 -q ============================================================================================================================================== Package Arch Version Repository Size ============================================================================================================================================== Installing: nmap-ncat x86_64 2:6.40-13.el7 base 205 k percona-xtrabackup-80 x86_64 8.0.1-2.alpha2.el7 percona-experimental-x86_64 13 M pigz x86_64 2.3.4-1.el7 epel 81 k pv x86_64 1.4.6-1.el7 epel 47 k Installing for dependencies: perl-DBD-MySQL x86_64 4.023-6.el7 base 140 k Transaction Summary ============================================================================================================================================== Install 4 Packages (+1 Dependent package) Is this ok [y/d/N]: y #

You need to do it on both the source and destination nodes. Now, my existing cluster node (I will call it a donor) – gr01 looks like this:

gr01 > select * from performance_schema.replication_group_members\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: 76df8268-c95e-11e8-b55d-525400cae48b MEMBER_HOST: gr01 MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: PRIMARY MEMBER_VERSION: 8.0.13 1 row in set (0.00 sec) gr01 > show global variables like 'gtid%'; +----------------------------------+-----------------------------------------------+ | Variable_name | Value | +----------------------------------+-----------------------------------------------+ | gtid_executed | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-302662 | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-295538 | +----------------------------------+-----------------------------------------------+ 5 rows in set (0.01 sec)

The new node candidate (I will call it a joiner) – gr02, has no data but the same MySQL version installed. It also has the required settings in place, like the existing node address in group_replication_group_seeds, etc. The next step is to stop the MySQL service on the joiner (if already running), and wipe out it’s datadir:

[root@gr02 ~]# rm -fr /var/lib/mysql/*

and start the “listener” process, that waits to receive the data snapshot (remember to open the TCP port if you have a firewall):

[root@gr02 ~]# nc -l -p 4444 |pv| unpigz -c | xbstream -x -C /var/lib/mysql

Then, start the backup job on the donor:

[root@gr01 ~]# xtrabackup --user=root --password=*** --backup --parallel=4 --stream=xbstream --target-dir=./ 2> backup.log |pv|pigz -c --fast| nc -w 2 192.168.56.98 4444 240MiB 0:00:02 [81.4MiB/s] [ <=>

On the joiner side, we will see:

[root@gr02 ~]# nc -l -p 4444 |pv| unpigz -c | xbstream -x -C /var/lib/mysql 21.2MiB 0:03:30 [ 103kiB/s] [ <=> ] [root@gr02 ~]# du -hs /var/lib/mysql 241M /var/lib/mysql

BTW, if you noticed the difference in transfer rate between the two, please note that on the donor side I put

|pv| before the compressor while in the joiner before decompressor. This way, I can monitor the compression ratio at the same time!

The next step will be to prepare the backup on joiner:

[root@gr02 ~]# xtrabackup --use-memory=1G --prepare --target-dir=/var/lib/mysql 2>prepare.log [root@gr02 ~]# tail -1 prepare.log 181019 19:18:56 completed OK!

and fix the files ownership:

[root@gr02 ~]# chown -R mysql:mysql /var/lib/mysql

Now we should verify the GTID position information and restart the joiner (I have the

group_replication_start_on_boot=off in my.cnf):[root@gr02 ~]# cat /var/lib/mysql/xtrabackup_binlog_info binlog.000023 893 aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-302662 [root@gr02 ~]# systemctl restart mysqld

Now, let’s check if the position reported by the node is consistent with the above:

gr02 > select @@GLOBAL.gtid_executed; +-----------------------------------------------+ | @@GLOBAL.gtid_executed | +-----------------------------------------------+ | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-302660 | +-----------------------------------------------+ 1 row in set (0.00 sec)

No, it is not. We have to correct it:

gr02 > reset master; set global gtid_purged="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-302662"; Query OK, 0 rows affected (0.05 sec) Query OK, 0 rows affected (0.00 sec)

Finally, start the replication:

gr02 > START GROUP_REPLICATION; Query OK, 0 rows affected (3.91 sec)

Let’s check the cluster status again:

gr01 > select * from performance_schema.replication_group_members\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: 76df8268-c95e-11e8-b55d-525400cae48b MEMBER_HOST: gr01 MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: PRIMARY MEMBER_VERSION: 8.0.13 *************************** 2. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: a60a4124-d3d4-11e8-8ef2-525400cae48b MEMBER_HOST: gr02 MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: SECONDARY MEMBER_VERSION: 8.0.13 2 rows in set (0.00 sec) gr01 > select * from performance_schema.replication_group_member_stats\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier VIEW_ID: 15399708149765074:4 MEMBER_ID: 76df8268-c95e-11e8-b55d-525400cae48b COUNT_TRANSACTIONS_IN_QUEUE: 0 COUNT_TRANSACTIONS_CHECKED: 3 COUNT_CONFLICTS_DETECTED: 0 COUNT_TRANSACTIONS_ROWS_VALIDATING: 0 TRANSACTIONS_COMMITTED_ALL_MEMBERS: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-302666 LAST_CONFLICT_FREE_TRANSACTION: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:302665 COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0 COUNT_TRANSACTIONS_REMOTE_APPLIED: 2 COUNT_TRANSACTIONS_LOCAL_PROPOSED: 3 COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0 *************************** 2. row *************************** CHANNEL_NAME: group_replication_applier VIEW_ID: 15399708149765074:4 MEMBER_ID: a60a4124-d3d4-11e8-8ef2-525400cae48b COUNT_TRANSACTIONS_IN_QUEUE: 0 COUNT_TRANSACTIONS_CHECKED: 0 COUNT_CONFLICTS_DETECTED: 0 COUNT_TRANSACTIONS_ROWS_VALIDATING: 0 TRANSACTIONS_COMMITTED_ALL_MEMBERS: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-302666 LAST_CONFLICT_FREE_TRANSACTION: COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0 COUNT_TRANSACTIONS_REMOTE_APPLIED: 0 COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0 COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0 2 rows in set (0.00 sec)

OK, our cluster is consistent! The new node joined successfully as secondary. We can proceed to add more nodes!

Galera Cluster supports high volume traffic at Paytrail and THINQ

Paytrail

 

“Galera Cluster has supported our growth all the way from a small number of transactions to high volume  traffic. Since 2012 our payment service with Galera has processed over 4 billion euros worth of product and service sales. Today we help 10 000+ webshops and online services in several countries to provide a pleasant shopping experience to their customers”

 

THINQ

 

“We’re very happy how Galera Cluster facilitated automated failover eliminating downtime for THINQ services that programmatically route millions of phone calls. The comprehensive Codership documentation includes, for example, the AppArmor package settings necessary to make Galera Cluster nodes more secure. ”

North Carolina based, THINQ is a cloud-based software company that develops Business as a Service (BaaS) solutions for the telecommunications industry. Partners with top carriers like AT&T, Verizon, Comcast in providing wholesale rates for long distance or wireless calls.

Using Django with MySQL 8

A framework can be a great way to allow you to spend more time on the actual application or web site and less time on standard tasks. It can also greatly reduce the amount of custom code needed. Django is one of the best known web frameworks for Python, and the good news is that it works out of the box with MySQL Server 8 and MySQL Connector/Python 8. This blog will look at how to use Django with MySQL 8.

There actually is very little to get Django to work with MySQL 8. Just install it, configure Django to use MySQL Connector/Python as a backend, and that’s it. From the Django point of view, you just have to configure the database option in settings.py to use MySQL Connector/Python and your database settings, for example:

DATABASES = { 'default': { 'NAME': 'mydb', 'ENGINE': 'mysql.connector.django', 'USER': 'django', 'PASSWORD': '$@jkHhj34N!bd', 'OPTIONS': { 'autocommit': True, }, } }

The instructions in this blog should also work for older versions of MySQL.

Obviously this assumes, you have MySQL installed already. If you do not, the rest of the blog includes a more comprehensive list of steps. The first step is to install MySQL Server.

Django Administration Screen using MySQL as the backendInstalling MySQL Server

There are several ways to install MySQL Server and which one is the best depends on your circumstances and preferences. For the sake of this blog, I will show how MySQL Server can be installed on Oracle Linux/RHEL/CentOS 7 using RPMs and on Microsoft Windows using MySQL Installer. For more options, see the installation chapter in the reference manual. Let’s look at the Linux installation first.

RPM Install on Enterprise Linux

MySQL provides repositories for several Linux distributions including the Oracle Linux/RHEL/CentOS family. This makes it easy to install MySQL. The step to install the repository definition is:

$ sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpmLoaded plugins: langpacks, ulninfo Repository ol7_developer_EPEL is listed more than once in the configuration mysql80-community-release-el7-1.noarch.rpm | 25 kB 00:00:00 Examining /var/tmp/yum-root-Ts4OzC/mysql80-community-release-el7-1.noarch.rpm: mysql80-community-release-el7-1.noarch Marking /var/tmp/yum-root-Ts4OzC/mysql80-community-release-el7-1.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package mysql80-community-release.noarch 0:el7-1 will be installed --> Finished Dependency Resolution Dependencies Resolved ====================================================================================================== Package Arch Version Repository Size ====================================================================================================== Installing: mysql80-community-release noarch el7-1 /mysql80-community-release-el7-1.noarch 31 k Transaction Summary ====================================================================================================== Install 1 Package Total size: 31 k Installed size: 31 k Is this ok [y/d/N]: y Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : mysql80-community-release-el7-1.noarch 1/1 Verifying : mysql80-community-release-el7-1.noarch 1/1 Installed: mysql80-community-release.noarch 0:el7-1 Complete!

Now, you can install MySQL Server. There are several RPMs to choose from and which you need depends on which other features you need to use. A common set of RPMs can be installed as:

shell$ sudo yum install mysql-community-server mysql-community-libs \ mysql-community-libs-compat mysql-community-common mysql-community-client ...

Note: If you have another MySQL installation, it will be upgraded to the latest release (at the time of writing 8.0.13).

On the first start, the data directory will be initialized:

shell$ sudo systemctl start mysqld

To keep a fresh installation secure, a random password has been set for the root user. This can be found from the MySQL error log:

shell$ sudo grep password /var/log/mysqld.log 2018-11-05T08:05:09.985857Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: KWNfuA!1r:PF

Use this password to connect to MySQL and update the password (please use a strong password):

shell$ mysql --user=root --password Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> ALTER USER root@localhost IDENTIFIED BY 'Kij0@jDi~Faf4'; Query OK, 0 rows affected (0.13 sec)

MySQL is now ready for use. Before continuing, I will show an example of installing MySQL on Microsoft Windows.

Microsoft Windows

On Microsoft Windows an easy way to install MySQL is to use the MySQL Installer. The installer can be downloaded from the MySQL download site. The MySQL Installer can be used to install most MySQL products. If you start MySQL Installer for the first time, you will be taken directly to the screen to choose the products to install; if you already have installed products, you will need to choose to add new products.

On the Select Products and Features screen, choose to install MySQL Server 8.0 (MySQL Installer will list the latest release from the list of available products):

Installing MySQL Server 8.0.13 from MySQL Installer

Optionally, you can filter the list of products. Feel free to choose other products you want. MySQL Notifier can be useful for starting and stopping MySQL, if you do not plan to have MySQL running at all times. You can also install MySQL Connector/Python this way, however for this blog a different method will be used.

Follow the installation wizard. For this blog, the default choices will work, though during the configuration you may want to ensure Open Windows Firewall ports for network access is unchecked unless you need remote access.

Before you can connect to MySQL from your Django program, you need a user and a schema (database) to use from your web site.

Preparing MySQL Server

While MySQL is now ready to work with Django, you will likely want to do a few more preparation steps. Here creating the MySQL user and schema (database) used by Django and support for named time zones will be covered.

Creating the User and Schema

An example of creating the user django@localhost and give it all privileges to the mydb schema and to create the mydb schema is:

mysql> CREATE USER django@localhost IDENTIFIED BY '$@jkHhj34N!bd'; Query OK, 0 rows affected (0.11 sec) mysql> GRANT ALL ON mydb.* TO django@localhost; Query OK, 0 rows affected (0.11 sec) mysql> CREATE DATABASE mydb CHARACTER SET utf8mb4; Query OK, 1 row affected (0.07 sec)

This will allow the django user to connect from the same host as MySQL Server is installed by authenticating with the password $@jkHhj34N!bd.

In MySQL 8 it is not necessary to specify the database character set to utf8mb4 as it is the default. However, if you use an older version of MySQL Server, you should ensure you are using UTF-8. The utf8mb4 character set means that characters using up to four bytes are supported.

Named Time Zones

If you want to used named time zones (for example Australia/Sydney), you will need to install the data for these in MySQL. On Linux you can use the mysql_tzinfo_to_sql script that comes with the MySQL installation:

shell$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql --user=root --password mysql

On Microsoft Windows, you need to download the time zone information and load these into the database, for example:

mysql> use mysql Database changed mysql> SOURCE timezone_posix.sql

See also MySQL Server Time Zone Support in the reference manual.

Now, you can move on to MySQL Connector/Python and Django.

Installing MySQL Connector/Python and Django

Both MySQL Connector/Python and Django can be installed in a platform independent way using the pip command. Since Django 2.1 is only available for Python 3.4 and later, it is recommended to use Python 3.4 or later. This blog assumes Python 3.6. (MySQL Connector/Python 8.0.13 and later also supports Python 3.7.)

If you do not have Python 3.6 installed on Oracle Linux/RHEL/CentOS 7, you can easily install it for example from for EPEL repository. Assuming you have configured the EPEL repository, the following steps install Python 3.6, enable pip, and update pip to the latest version:

shell$ yum install python36 shell$ python3.6 -m ensurepip shell$ python3.6 -m pip install --upgrade pip

You can now use python3.6 to invoke Python 3.6. In the following, replace python with python3.6 if you have installed Python 3.6 in this way.

To install the latest MySQL Connector/Python release (currently 8.0.13):

PS:> python -m pip install mysql-connector-python Collecting mysql-connector-python Downloading https://files.pythonhosted.org/packages/4a/91/ffdd28ae0f8e01b09df67a4e48cef17a1a5374cd0ce0f2fe27e1d635423e/mysql_connector_python-8.0.13-cp36-cp36m-wi (3.2MB) 100% |████████████████████████████████| 3.2MB 3.0MB/s Requirement already satisfied: protobuf>=3.0.0 in c:\users\myuser\appdata\local\programs\python\python36\lib\site-packages (from mysql-connector-pytho st1) Requirement already satisfied: six>=1.9 in c:\users\myuser\appdata\local\programs\python\python36\lib\site-packages (from protobuf>=3.0.0->mysql-conne (1.11.0) Requirement already satisfied: setuptools in c:\users\myuser\appdata\local\programs\python\python36\lib\site-packages (from protobuf>=3.0.0->mysql-con n) (28.8.0) Installing collected packages: mysql-connector-python Successfully installed mysql-connector-python-8.0.13

Similar for installing Django:

PS:> python -m pip install Django Collecting Django Downloading https://files.pythonhosted.org/packages/d1/e5/2676be45ea49cfd09a663f289376b3888accd57ff06c953297bfdee1fb08/Django-2.1.3-py3-none-any.whl (7.3MB) 100% |████████████████████████████████| 7.3MB 1.8MB/s Collecting pytz (from Django) Downloading https://files.pythonhosted.org/packages/f8/0e/2365ddc010afb3d79147f1dd544e5ee24bf4ece58ab99b16fbb465ce6dc0/pytz-2018.7-py2.py3-none-any.whl (506kB) 100% |████████████████████████████████| 512kB 10.5MB/s Installing collected packages: pytz, Django Successfully installed Django-2.1.3 pytz-2018.7

That’s it. Now you are ready to use Django with MySQL Connector/Python 8 and MySQL Server 8.

Using Django

I will not go into details of how to use Django. If you are new to Django, you can consider going through the tutorial for Django 2.1 on the Django website. This sets up a web site using a database backend. The important thing with respect to MySQL is the configuration of the DATABASE property in settings.py (the first step in part 2):

DATABASES = { 'default': { 'NAME': 'mydb', 'ENGINE': 'mysql.connector.django', 'USER': 'django', 'PASSWORD': '$@jkHhj34N!bd', 'OPTIONS': { 'autocommit': True, }, } }

The key here is the engine. You need to set it to mysql.connector.django to use MySQL Connector/Python. The rest of the options are schema name, credentials, and MySQL Connector/Python specific options.

The Django documentation also has more information about using MySQL as the database. A couple of updates of the statements in the document:

  • As of MySQL 8, InnoDB does correctly restore the auto-increment ID after a restart.
  • The default character set in MySQL 8 is UTF-8 (called utf8mb4 in MySQL).
  • There is also support for a C Extension in MySQL Connector/Python. In MySQL 8 this is the default for the platforms/Python version where the C Extension is installed.
Note: In the second part of the tutorial, I need to swap the python manage.py sqlmigrate polls 0001 command with the next python manage.py migrate command. Otherwise the sqlmigrate command fails with an error.

The rest is all Python and Django. Have fun creating your next web site with Django, MySQL Connector/Python 8, and MySQL 8.

If you are interested in learning more about MySQL Connector/Python 8, then I am the author of MySQL Connector/Python Revealed (Apress) – also available as Amazon and other bookshops.

MySQL Meetup in Wien - "MySQL 8 - The next big thing!" (&. get together)

We are happy to announce that there will be a MySQL Day/MySQL Community Meetup hold in Oracle Buliding in Vienna on November 8th. Please find more details below:
  • Date: Thursday, November 8th, 2018 
  • Time: 7pm - 9pm
  • Address: Oracle Office DC Tower 1, Donau-City-Straße 7, 1220 Wien, Österreich
  • Meeting room: will be confirmed soon. 
  • Agenda: 
    • "Oracle MySQL 8 - The next big thing!" by Carsten Thalheimer the Master Principal Sales Consultant 
    • Discussion & pizza 
  • More information & Registration

Nov 5 -> Busy week for MySQL: where you can find us now?

as announced in the blog posted on Oct 18, 2018 we would like to just remind you about places and shows where you can find us this really busy week... We are ready for following shows:

  • PerconaLive in Frankfurt, Germany on November 5-7, 2018
    • as previously announced there are plenty of our MySQL experts, our friends from sales & Fred Descamps representing MySQL community at PerconaLive, Frankfurt which started today. Right now Fred Descamps & Kenny Grip are having a tutorial on "MySQL InnoDB Cluster in a Nutshell : The Saga Continues with 8.0" which ends before noon. Also there are plenty of MySQL talks (you can check them either on the previous blog post or directly at PerconaLive website).
    • From today's noon the expo is also open. MySQL is having a new designed booth there with our staff. Come to talk to us! We are looking forward to meeting you there!
  • PHP.RUHR, Dortmund, Germany on November 8, 2018
    • Next show where you can find us is PHP.RUHR. This is the third time we are supporting this one-day show and same as last year we are having a MySQL talk (in Mainstage: Developer on "MySQL 8.0 - The new Developer API") & Q&A session in the expo area where you can come to ask our MySQL speaker, Mario Beck MySQL related questions. 
    • Come to listen the news in MySQL 8.0 & talk to Mario at PHP.RUHR!
  • ​​Highload++, Moscow, Russia on November 8-9, 2018
    • We are really excited about being part of this big technology show in Russia. Last week we finally got a notification about that our MySQL talk was accepted so, you can find that talk in the conference schedule as follows:
      • "MySQL 8.0: SQL and NoSQL Scalability" given by Vittorio Cioe. Talk is scheduled for Nov 8th @14:00
    • Do not forget to stop by at our MySQL booth in the expo area. We are planning to have quiz for a MySQL prizes as well as InnoDB Cluster demo given by Vittorio on the first day and quiz followed by MEM demo given by Vittorio on the second day at our booth. Check the organizers' website & schedule for further updates & for sure come to our booth and MySQL session to get the latest MySQL updates and news. We are looking forward to talking to you at Highload++!!

 






     
  •  
  •  

Using Sass in Create React App v2

With the upgraded Create React App released recently, we got a lot of new tools to play with. Sass is one that I'm excited to have built in since we used to have to have .scss files compile and write to .css files right in our folder structure. Messy files when you have two of the same styles.scss and styles.css.

Create React App 2 makes it super easy to use Sass in 1 line.

You may be concerned about using Sass in React. Isn't a smarter way to write styles with CSS-in-JS libraries like styled-components or aphrodite? I believe that adding Sass support to Create React App will be a big help to beginners of React. How do I use Sass in React is one of the questions I always hear from people getting into React. With the React 16.6 additions like React.memo() and the React 16.7 functional additions like hooks, starting with React will be easier than ever!

Quick Start

The steps to use Sass in Create React App are:

  1. Install node-sass: npm install node-sass
  2. Change .css files to .scss
  3. Change any imports to use .scss
npm install node-sass -S

Once we've changed the file name from .css to .scss, we can import the Sass:

// replace import "./styles.css"; // with import "./styles.scss";

Done! Create React App will know to parse your .scss files and add the styles to your project.

Using and Sharing Sass Variables

How do we share variables across files? We are able to import our Sass files from other Sass files. Let's say you create a variables file:

variables.scss $primaryColor: #BADA55;

We can import this inside of another file like we normally would in Sass:

styles.scss // import starting from the src/ folder @import "variables.scss"; // can also be relative import // @import "./variables.scss"; // we can use the $primaryColor variable now h1, h2 { color: $primaryColor; }

Sass Files from 3rd Party Packages

If we want to use any 3rd party libraries like Bulma or Bootstrap (Bulma is my favorite right now), we don't need to import the entire CSS library anymore.

With Sass in React, we can import just the files we need. First, we have to install Bulma.

npm install bulma -S

If we look at Bulma's GitHub in the sass/ folder, we can see where they place their .sass files. Notice they are using .sass and we are using the .scss variant. No problems, node-sass can read and @import both!

Import files from node_modules using ~

The ~ let's webpack and Create React App know to look in the node_modules/ folder for the files we need. Let's add a few of the files we need to our app:

styles.scss // import using ~ @import "~bulma/sass/utilities/_all.sass"; @import "~bulma/sass/base/_all.sass"; @import "~bulma/sass/elements/button.sass"; @import "~bulma/sass/layout/section.sass";

Now, we can use Bulma's button and section.

App.js function App() { return ( <div className="App section"> <h1>Hello CodeSandbox</h1> <h2>Start editing to see some magic happen!</h2> <button className="button is-danger is-outlined"> Hello </button> </div> ); }

This approach let's us keep our CSS bundle sizes as small as possible as we only import what we need.

Conclusion and Demo

Using Sass in React is a quick way to get styling in your app. It is also recommended to look at CSS-in-JS solutions so that we can create even more modular CSS in our component based React apps.

Here's the CodeSandbox with the demo of Sass in Create React App 2:

https://codesandbox.io/s/j7y6wy9m09

On New Severity Levels for MySQL Bugs

Four weeks ago while working on a blog post about half baked XA transactions feature of MySQL server I've noted that there are new severity levels added by Oracle for MySQL bug reports. Previously we had 5 levels:

  • S1 (Critical) - mostly for all kinds of crashes, DoS attack vectors, data corruptions etc
  • S2 (Serious) - mostly for wrong results bugs, broken replication etc
  • S3 (Non-critical) - all kinds of minor but annoying bugs, from unexpected results in some corner cases to misleading or wrong error messages, inefficient or unclear code etc
  • S4 (Feature requests) - anything that should work or be implemented based on common sense, but is not documented in the manual and was not required by the original specification or implementation of some feature.
  • S5 (Performance) - everything works as expected and documented, but the resulting performance is bad or less than expected. Something does not scale well, doesn't return results fast enough in some cases, or could be made faster or some specific platform using some different code or library. This severity level was also probably added at Oracle times, at least it was not there in 2005 when I started to work on MySQL bugs.

Informal descriptions above are mine and may be incorrect or different from definitions Oracle engineers currently use. I tried to search for Oracle definitions that apply to MySQL, but was not able to find anything immediately useful (any help with public URL is appreciated). 

In general, severity is defined as the degree of impact a bug has on the operation or use of some software, so less severity assumes less impact on common MySQL operations. One may also expect that bugs with higher severity are fixed first (have higher internal priority). It may not be that simple (and was not during my days in MySQL, when many more inputs were taken into account while setting priority for the bug fix), but it's a valid assumption for any community member.

By default when searching for bugs you got all bugs of severity levels S1, S2, S3 and S5. You had to specifically care to get feature requests in search results while using bugs database search interface.

If you try to search bugs today, you'll see two more severity levels added, S6 (Debug Builds) and S7 (Test Cases):

Now we have 7 Severity levels for MySQL bug reports S6 severity level seems to be used for assertion failures and other bugs that affect only debug builds and can not be reproduced literally with non-debug binaries. S7 severity level is probably used for bug reports about failing MTR test cases, assuming that failure does NOT show a regression in MySQL software, but rather some non-determinism, platform dependencies, timing assumptions or other defects of the test case itself.

By default bug reports with these severity levels are NOT included in search (they are not considered "Production Bugs"). So, one has to care to see them. This, as well as normal common sense based assumption that lower severity eventually means to lower priority for the fix, caused some concerns. It would be great for somebody from Oracle to explain the intended use and reasons for introduction of these severity levels with some more text than a single tweet, to clarify possible FUD people may have. If applied formally, these new severity values may lead to low priority for quite important problems. Most debug assertions are in the code for really good reason, as many weird things (up to crashes and data corruption) may happen in non-debug binaries somewhere later in cases when debug-only assertion fails.

I was surprised to find out that at the moment we have 67 active S6 bug reports, and 32 active S7 bug reports. The latter list obviously includes reports that should not be S7, like Bug #92274 - "Question of mysql 8.0 sysbench oltp_point_select test" that is obviously about a performance regression noted in MySQL 8 (vs MySQL 5.7) by the bug reporter.

Any comments from Oracle colleagues on the reasons to introduce new severity levels, their formal definitions and impact on community bug reports processing are greatly appreciated.

Pages