Author: Gabriela D'Ávila Ferrara

From MySQL 8.0.0 to MySQL 8.0.1 – or any other dev milestone

Disclaimer: This post is aimed to you, the curious developer, sys-admin, technologist, whatever-title-you-use. DO NOT run the following lines on production. Not even in a stable environment, do this if you don’t care about the outcome of the current data.

If you want to keep up with the newest MySQL developer milestones I have news for you: there is no upgrade available for milestone versions. The way to go is to remove old version and install new one, according to their website:

Upgrades between milestone releases (or from a milestone release to a GA release) are not supported. For example, upgrading from 8.0.0 to 8.0.1 is not supported, as neither are GA status releases.

So if you, like me, had the 8.0.0 version and want to test the 8.0.1 (alhtough 8.0.3 milestone is already in development) you need to do something like the following (tutorial based on Debian/Ubuntu servers).

Stop your service:

$ sudo service mysql stop

Download Oracle’s repository and install it, as of now this is the current version, you can get the new package here:

$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.6-1_all.deb
$ sudo dpkg -i mysql-apt-config_0.8.6-1_all.deb

Clean your old install, you will lose all the data. Be careful, back up is on you!

$ sudo apt-get remove --purge mysql-server mysql-client mysql-common
$ sudo apt autoremove
$ sudo apt-get autoclean
$ sudo apt-get install mysql-server

This is the way to go to test the new features such as Descending Indexes and others. Remember, the new default encoding was changed from latin1 to utf8mb4.

Short feature list:

The complete list is available here.

Converting comma separated fields to MySQL JSON – a case study

This post is a case study of a job I had to do in a legacy application, it doesn’t mean it will apply to you, but it might.

This is a table of contents:

There are many ways to store a tree in a relational database, this is not by far the best option to do it, however it is still common to see it happen.

One way it is called materialized path, which consist with values separated by a delimiter, in my case a comma ,.

You would have a tree stored in a manner like this:

id parent_id user_id depth asc_path node
1 0 1 1
2 1 2 2 ,1, L
3 1 13 2 ,1, R
4 2 3 3 ,2,1, L
5 2 61 3 ,2,1, R
6 13 23 3 ,13,1, L
7 13 22 3 ,13,1, R
8 3 4 4 ,3,2,1, L
9 3 156 4 ,3,2,1, R
10 22 1568 4 ,22,13,1, L
11 22 26 4 ,22,13,1, R
12 23 1476 4 ,23,13,1, L
13 23 690716 4 ,23,13,1, R
14 61 1051 4 ,61,2,1, L
15 61 62 4 ,61,2,1, R

The column asc_path stands for ascending path of a tree in where which node has two other ones, not necessarily being a binary tree, being stored in the database.

This column has commas in the beginning and in the end because how queries are made to search if an element is present in the path or not by using LIKE "%,id,%". If someone did a search to know if the number 2 was a node in any of the paths, without the commas, it would also return 23, 62 and any other number containing 2.

Performance

The only way to make it a bit faster is having a FULLTEXT index created in asc_path. Because a BTREE index starts indexing in the beginning of a string, since the presence of the wildcard % in the string search it makes it in possible to use said index.

This is the graphical representation of the example above:

Tree

Searching

To search an specific element the query would be:

SELECT
parent_id,
user_id,
depth,
asc_path,
node
FROM tree
WHERE asc_path LIKE '%,13,%';

Result:

parent_id user_id depth asc_path node
13 23 3 ,13,1, L
13 22 3 ,13,1, R
22 1568 4 ,22,13,1, L
22 26 4 ,22,13,1, R
23 1476 4 ,23,13,1, L
23 690716 4 ,23,13,1, R

Converting to a JSON array

Some databases, like PostgresSQL (section 9.42) have more modifiers functions to convert strings to JSON, in my case I wanted to store the ascending tree path in a JSON field which would give me the possibility of using JSON_CONTAINS(json_doc, val) to know the records that have a given node in its path.

To do it, I had to transform the string in a JSON array.

1st step: remove the leading commas

Removing the leading commas, but before any update, lets test what we are doing:

SELECT
parent_id,
user_id,
depth,
asc_path,
TRIM(BOTH ',' FROM asc_path) AS trimmed_commas
FROM tree

Results:

parent_id user_id depth asc_path trimmed_commas
0 1 1
1 2 2 ,1, 1
1 13 2 ,1, 1
2 3 3 ,2,1, 2,1
2 61 3 ,2,1, 2,1
13 23 3 ,13,1, 13,1
13 22 3 ,13,1, 13,1
3 4 4 ,3,2,1, 3,2,1
3 156 4 ,3,2,1, 3,2,1
22 1568 4 ,22,13,1, 22,13,1

2nd step: add brackets to the string

A JSON array is formed around brackets [], and we need to have it in our string to be a valid JSON document:

SELECT
parent_id,
user_id,
depth,
asc_path,
TRIM(BOTH ',' FROM asc_path) AS trimmed_commas,
CONCAT("[", TRIM(BOTH ',' FROM asc_path), "]") AS added_brackets
FROM tree;

Results:

parent_id user_id depth asc_path trimmed_commas added_brackets
0 1 1
1 2 2 ,1, 1 [1]
1 13 2 ,1, 1 [1]
2 3 3 ,2,1, 2,1 [2,1]
2 61 3 ,2,1, 2,1 [2,1]
13 23 3 ,13,1, 13,1 [13,1]
13 22 3 ,13,1, 13,1 [13,1]
3 4 4 ,3,2,1, 3,2,1 [3,2,1]
3 156 4 ,3,2,1, 3,2,1 [3,2,1]
22 1568 4 ,22,13,1, 22,13,1 [22,13,1]

3rd step: validate if the changes works

Let’s use JSON_VALID() to see if it will accept our new string as a JSON, keep in mind that when the argument is NULL the return is also NULL:

SELECT
parent_id,
user_id,
depth,
asc_path,
TRIM(BOTH ',' FROM asc_path) AS trimmed_commas,
CONCAT("[", TRIM(BOTH ',' FROM asc_path), "]") AS added_brackets,
JSON_VALID(CONCAT("[", TRIM(BOTH ',' FROM asc_path), "]")) AS json_valid
FROM tree;

Results:

parent_id user_id depth asc_path trimmed_commas added_brackets json_valid
0 1 1
1 2 2 ,1, 1 [1] 1
1 13 2 ,1, 1 [1] 1
2 3 3 ,2,1, 2,1 [2,1] 1
2 61 3 ,2,1, 2,1 [2,1] 1
13 23 3 ,13,1, 13,1 [13,1] 1
13 22 3 ,13,1, 13,1 [13,1] 1
3 4 4 ,3,2,1, 3,2,1 [3,2,1] 1
3 156 4 ,3,2,1, 3,2,1 [3,2,1] 1
22 1568 4 ,22,13,1, 22,13,1 [22,13,1] 1
22 26 4 ,22,13,1, 22,13,1 [22,13,1] 1
23 1476 4 ,23,13,1, 23,13,1 [23,13,1] 1
23 690716 4 ,23,13,1, 23,13,1 [23,13,1] 1
61 1051 4 ,61,2,1, 61,2,1 [61,2,1] 1
61 62 4 ,61,2,1, 61,2,1 [61,2,1] 1

Replacing 1st step and 2nd step with a function

So that your query gets easier to use and not messy, you can create a function, I decided to create to_json_array(input_string, delimiter_char):

Running the query only with to_json_array on MySQL:

SELECT
parent_id,
user_id,
depth,
asc_path,
to_json_array(asc_path, ',') AS to_json_array,
JSON_VALID(to_json_array(asc_path, ',')) AS is_to_json_array_valid,
node
FROM tree;

Result:

parent_id user_id depth asc_path to_json_array is_to_json_array_valid node
0 1 1
1 2 2 ,1, [1] 1 L
1 13 2 ,1, [1] 1 R
2 3 3 ,2,1, [2, 1] 1 L
2 61 3 ,2,1, [2, 1] 1 R
13 23 3 ,13,1, [13, 1] 1 L
13 22 3 ,13,1, [13, 1] 1 R
3 4 4 ,3,2,1, [3, 2, 1] 1 L
3 156 4 ,3,2,1, [3, 2, 1] 1 R
22 1568 4 ,22,13,1, [22, 13, 1] 1 L

Disclaimer

This function is not native, and its use in production is not guaranteed.

Notice that the database returns the JSON as valid making it possible to convert that TEXT to a new column asc_path_json:

ALTER TABLE tree
ADD COLUMN asc_path_json JSON
AFTER asc_path;

UPDATE tree
SET asc_path_json = to_json_array(asc_path, ',');

Which gives us the ability to check more quickly if an item is in the path for that node:

SELECT *
FROM tree
WHERE json_contains(asc_path_json, "13");

Result:

id parent_id user_id depth asc_path asc_path_json node
6 13 23 3 ,13,1, [13, 1] L
7 13 22 3 ,13,1, [13, 1] R
10 22 1568 4 ,22,13,1, [22, 13, 1] L
11 22 26 4 ,22,13,1, [22, 13, 1] R
12 23 1476 4 ,23,13,1, [23, 13, 1] L
13 23 690716 4 ,23,13,1, [23, 13, 1] R

I don’t know Ops, and that may be OK

I am a Software Engineer at heart. I started as such and worked with PHP for about 7 years, always correlating my work with data somehow until I got an opportunity and decided to follow my instincts and be a Data Engineer.

I didn’t turn a Data Engineer from one night to another. It was a process. I was lucky to have a boss that noticed my skills with data and decided to give me room to play with it.

But the Ops part, was never my forte and this is why.

Data Integrity

This is my main concern. I am more worried about keeping consistency as much as possible and even in many times choosing it over performance.

Another trait I have is to be always looking for logic errors that may generate bad data into an application. I despise badly written models and have had a bit of problem when working with RDBMS and ActiveRecord. My take on it is: if you have a complex business model, what is easy may become painful. Also, there is no silver bullet solution, you don’t need to use only one technology.

But I won’t go into what is better, what is better it is what it works for you and make you application works and don’t let your users down while maintaining your data integrity.

Value

Your software is not valuable 99% of the times. Your software it is a means to an end. It is a path to interpret business logic and generate value to your company. And if your data sucks (duplicated records, lack of foreign key checks, extreme denormalization in the main DB if relational) you may have no real value at all.

Why don’t I know it?

When I needed doing ops, managing database servers, they were all single servers, at most a read replica on AWS, or only one slave.

One can say I’ve never needed actually to know it. I got lucky having good people working with me on the DevOps team, and we trusted each other’s work, in the end the managers would prefer to use my abilities in another area.

As I said before I do know it’s an area I need to improve, but it is ok to not know it, because even with me not being an expert on it my value lies in understanding the data, the data model and how application handles on data. As a DBA main job usually is to keep the database servers healthy, mine is to keep data itself as a valuable as possible to a company.

Not a DBA

What I do it is many times considered a DBA job, there are a couple areas where both can overlap but I try as a Data Engineer to support the Developers and the Business as a DevOps person do.

You may notice on my posts: I don’t write about replication, cluster, etc. One reason is: I have never in real life have to deal with this particular area deeply. I do know I should know more about that. But one thing is to set it up a couple servers on the cloud with no real data to analyze and performance issues to attend than doing it in real life. However, that doesn’t lessen my value.

I know how to prepare data, I do ETL’s, I do data modeling, I do deep research on which storage would be the best for a case scenario, I help to define policies around migrations and data access, for instance.

My Goal

My goal is to help developers. It’s to help them do the right thing regarding to data as they do regarding with test coverage and code quality.

Everybody reviews code, I rarely seem people reviewing data models. And I want to help to create a culture where people see data as their true value. Remember this: data leaks are more valuable than “code” leaks and potentially more devastating too. So please, let me help you.