I have made changes to the RSS Feed of this blog:
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.
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.
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.
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.
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”.
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?
For reasons that don’t need exploring at this juncture I tweeted
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.
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.
Since we now know how to look at the state of locking in a live database, let’s look at what happens when we run a normal insert or update and an insert or update with foreign key relationships defined, and compare.
Foreign Keys are what links tables together and turns a set of tables into a model. Foreign Key Constraints are conditions that must be true for the content of the tables to be an internally consistent model. Foreign Key Constraints can be defined and enforced in InnoDB, but this comes at a considerable price, and for some it may hurt more than it is worth.
Support Channel. “Hi, I am getting deadlocks in the database and they occur when I have to rollback the transactions but if we don’t have to roll back all transactions get executed.” Wait, what? After some back and forth it becomes clear that the Dev experiences deadlocks and has data:
In a previous article we wrote data to the database using atomic update statements, and then using transactions with SELECT ... FOR UPDATE. In this article we will look at what happens when we continue doing this, in a more complicated way. Source code for this article is also available on github.com.
Using the framework for testing we created in earlier articles, let’s try to
modify some data. We are writing a small program that increments a counter.
Our table looks like this, and contains 10 counters:
After having a look how MySQL handles transactions physically, let’s have a look at what is
going on from a logical point of view.
MySQL speaks its own proprietary protocol. It cannot be routed
by a HTTP proxy, and a MySQL connection is entire unlike a HTTP
connection. Specifically, a lot of state and configuration is
tied to a MySQL connection, and it cannot be recovered on
disconnect.
When writing data to disk, for small transactions the cost of writing the
commit out do disk dominates the execution time of the script. In order to
show that, I wrote a little bit of Python.
