So this has turned into a small series, explaining how to work with MYSQL from a developers perspective. This post is intended as a directory for the individual articles. It will be amended and re-dated as necessary.

The code for the series is also available in isotopp/mysql-dev-examples on GitHub.

The Tag #mysqldev will reference all articles from this series.

• MySQL Transactions - the physical side . Looking at how MySQL InnoDB handles transactions on the physical media, enabling rollback and commit. Introduces a number of important concepts: The Undo-Log, the Redo-Log, the Doublewrite Buffer, and the corrosponding in memory structures, the Log Buffer and the InnoDB Buffer Pool, as well as the concept of a page.

We have had a look at how MySQL 8 handles JSON recently, but with all those JSON functions and expressions it is clear that many JSON accesses cannot be fast. To grab data from a JSON column, you will use a lot of $->>field expressions and similar, and without indexes nothing of this will be fast.

JSON cannot be indexed.

But MySQL 8 offers another feature that comes in handy: Generated columns and indexes on those. Let’s look at the parts, step by step, and how to make them work, because they are useful even outside the context of JSON.

MySQL 8 provides solid support for the JSON data type. The manual has an overview of the data type , a JSON function reference , an an overview on generated column indexes , and explains multi-values indexes .

Creating JSON columns

Creating JSON columns is easy: Make the column of the JSON data type, fill in valid JSON data.

mysql> create table t ( id integer not null primary key auto_increment, j json);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t (j) values 
-> ('null'),
-> ('true'),
-> ('false'),
-> ('1'),
-> ('"keks"'),
-> ('["eins", "zwei"]'),
-> ('{"eins": "one", "zwei": "two"}');
Query OK, 5 rows affected (0.02 sec)

mysql> select json_type(j) as type, json_valid(j) as valid, isnull(j) as sqlnull, j, id from t;
+---------+-------+---------+--------------------------------+----+
| type    | valid | sqlnull | j                              | id |
+---------+-------+---------+--------------------------------+----+
| NULL    |  NULL |       1 | NULL                           |  1 |
| NULL    |     1 |       0 | null                           |  2 |
| BOOLEAN |     1 |       0 | true                           |  3 |
| BOOLEAN |     1 |       0 | false                          |  4 |
| INTEGER |     1 |       0 | 1                              |  5 |
| STRING  |     1 |       0 | "keks"                         |  6 |
| ARRAY   |     1 |       0 | ["eins", "zwei"]               |  7 |
| OBJECT  |     1 |       0 | {"eins": "one", "zwei": "two"} |  8 |
+---------+-------+---------+--------------------------------+----+
8 rows in set (0.00 sec)

mysql> insert into t (j) values ('["incomplete", "array", "closing bracket"');
ERROR 3140 (22032): Invalid JSON text: "Missing a comma or ']' after an array element." at position 41 in value for column 't.j'.

We learn several things from this experiment:

I had to upgrade my company issued MacBook pro to Catalina for fleet unity reasons. The upgrade left me with a folder Relocated Items in /Users/Shared/Desktop and a link to that prominently on my Desktop.

Deleting that folder moved it to trash. Trying to empty trash then failed. That is because in that folder was several levels deep a folder or link to X11R6.

Apple considers that file outdated, which is why it ended up in Relocated Items. It also considers this file protected as a system file, so you cannot delete it.

Ok, it’s “Dad Stories” Time (from Twitter ). When my son was somewhat older than a year, he was learning to speak. He could already say “Mama” and “Papa”.

It was around Christmas, and there was a candle on the table, glowing interestingly, so he wanted to touch it. “Nein, heiß” is what you would say in German.

Of course, a toddler does not understand the meaning of “heiß”. I mean, he’s trying to imitate the sound of it, but the meaning of “heiß” is something specific. We connect an experience with the word. Something that any child can only learn by touching the candle. There is literally no other way to learn it.

Question: Hey, I got a UNIQUE INDEX, but I can store multiple rows with the same value, NULL. That is surprising. Is that a bug?

This is a rewrite of the same in German from 9 years ago .

root@localhost [kris]> create table t ( a integer, b integer, unique (a,b));
Query OK, 0 rows affected (0.09 sec)

root@localhost [kris]> insert into t values (1, 2);
Query OK, 1 row affected (0.01 sec)

root@localhost [kris]> insert into t values (1, 2);
ERROR 1062 (23000): Duplicate entry '1-2' for key 't.a'

This does not work, as expected. But this does:

For reasons that don’t need exploring at this juncture I tweeted

Once upon a time there were shared boxes, on which the local Unix easily had 200-300 users, Junior Developers at a University.

A /tmp/ls found easily 3-4 people per day that had . (dot) in their path.

No particular reason. Why?

and followed up with

Hope is not a strategy.

Neither is curl stackoverflow | sudo bash.

/tmp/ls

I was asked to explain: “What is /tmp/ls?”.

This is the updated and english version of some older posts of mine in German. It is likely still incomplete, and will need information added to match current MySQL, but hopefully it is already useful.

Old source articles in German: 1 , 2 and 3 .

Some vocabulary

Symbol, Font, Encoding and Collation - what do they even mean?

A character set is a collection of symbols that belong together. That is a completely abstract thing, and also almost useless. The only thing you can do with a character set is decide if a specific symbol is legal within a context or not. And, if it is legal, what position the symbol in the character set has (the code point).

Melissa and Chris Bruntlett have been living in Vancouver close to the station, and decided to do as many daily runs as possible without a car, using public transport or bikes. In late 2015, early 2016 they did an extended visit to the Netherlands which resulted in a changed world view about what proper Urban Infrastructure can look like. Meanwhile they are living with two kids near Delft, in the Netherlands.

A Wizard’s Guide To Defensive Baking is a Yound Adult Coming-of-Age story about Mona. Mona works in her aunt’s bakery, and also is a hedge wizard. Her talent is mostly limited to influencing dough and baked goods, her familiar is a sourdough starter named Bob.

A Wizards Guide To Defensive Baking, T. Kingfisher, Amazon

Mona is a 14-year-old girl living in Riverbraid, a random city-state near a river in the floodplain of an otherwise unnamed country that vaguely feels like northern Italy during renaissance times. Working in her aunts bakery, her talent of being able to magically influence dough and baked goods comes in handy in production, and sometimes she lets a few Gingerbread men dance to amuse the customers.