Die wunderbare Welt von Isotopp

MySQL: Generated Columns and virtual indexes

Kristian Köhntopp - September 7, 2020
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.

MySQL: Basic usage of the JSON data type

Kristian Köhntopp - September 4, 2020
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>createtablet(idintegernotnullprimarykeyauto_increment,jjson);QueryOK,0rowsaffected(0.11sec)mysql>insertintot(j)values->('null'),->('true'),->('false'),->('1'),->('"keks"'),->('["eins", "zwei"]'),->('{"eins": "one", "zwei": "two"}');QueryOK,5rowsaffected(0.02sec)mysql>selectjson_type(j)astype,json_valid(j)asvalid,isnull(j)assqlnull,j,idfromt;+---------+-------+---------+--------------------------------+----+ |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|+---------+-------+---------+--------------------------------+----+ 8rowsinset(0.00sec)mysql>insertintot(j)values('["incomplete", "array", "closing bracket"');ERROR3140(22032):InvalidJSONtext:"Missing a comma or ']' after an array element.

MacOS: Deleting Relocated Items

Kristian Köhntopp - September 2, 2020
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.

On Touching Candles, And Error Budgets

Kristian Köhntopp - August 31, 2020
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.


Kristian Köhntopp - August 25, 2020
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]>createtablet(ainteger,binteger,unique(a,b));QueryOK,0rowsaffected(0.09sec)root@localhost[kris]>insertintotvalues(1,2);QueryOK,1rowaffected(0.01sec)root@localhost[kris]>insertintotvalues(1,2);ERROR1062(23000):Duplicateentry'1-2'forkey't.a'This does not work, as expected. But this does: root@localhost[kris]>truncatetablet;QueryOK,0rowsaffected(0.16sec)root@localhost[kris]>insertintotvalues(1,NULL);QueryOK,1rowaffected(0.02sec)root@localhost[kris]>insertintotvalues(1,NULL);QueryOK,1rowaffected(0.03sec)root@localhost[kris]>select*fromt;+------+------+ |a|b|+------+------+ |1|NULL||1|NULL|+------+------+ 2rowsinset(0.00sec)Why is that? This is usually where I point people at SQL for Smarties: Advanced SQL Programming .

Why do Ops and Sec people wear black

Kristian Köhntopp - August 19, 2020
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.

MySQL: Some Character Set Basics

Kristian Köhntopp - August 18, 2020
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.

Fertig gelesen: Building the Cycling City

Kristian Köhntopp - August 17, 2020
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.

Fertig gelesen: A Wizard's Guide To Defensive Baking

Kristian Köhntopp - August 16, 2020
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.

ELI5: Epic vs Apple and Google

Kristian Köhntopp - August 14, 2020
@jpmens asked on Twitter: What is Fortnite? #outingmyself Assuming XKCD 1053 . Fortnite Fortnite is a game made by Epic Games. They also make the Unreal Engine, which powers many modern computer games. Fortnite together with Roblox and Minecraft it is the set of games played most by people under 20. You want to follow Matthew Ball . Fortnite is a Battle Royal game, a 1st person shooter, in which a matchmaker puts you together with a set of other people of similar skill, drops you into an arena of ever shrinking size.

MySQL Foreign Key Constraints and Locking

Kristian Köhntopp - August 4, 2020
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. We will be using the tables and structures from our previous examples, a simple 1:n relationship between a and b: CREATETABLEa(a_idintNOTNULLAUTO_INCREMENT,PRIMARYKEY(a_id));INSERTINTOaVALUES(10),(20),(30),(40);CREATETABLEb(b_idintNOTNULLAUTO_INCREMENT,a_idintNOTNULL,PRIMARYKEY(b_id),KEY`a_id`(a_id),CONSTRAINTa_id_existsFOREIGNKEY(a_id)REFERENCESa(a_id)ONDELETERESTRICTONUPDATERESTRICT);INSERTINTObVALUES(10,10),(40,40);or the same definition for b without the constraint.

MySQL Foreign Keys and Foreign Key Constraints

Kristian Köhntopp - August 3, 2020
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. A very simple shop as an ER-model.

MySQL Deadlocks with INSERT

Kristian Köhntopp - August 2, 2020
Support Channel. “Hi, I am getting deadlocks in the database, and they occur when I have to roll back 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: mysql>pagerlessmysql>showengineinnodbstatus\G...MySQLthreadid142531,OSthreadhandle139990258222848,queryid4799571somehost.somedomainsomeuserupdateINSERTintosometable(identifier_id,currency,balance)VALUES('d4e84cb1-4d56-4d67-9d16-1d548fd26b55','EUR','0')***(2)HOLDSTHELOCK(S):RECORDLOCKSspaceid3523pageno1106463nbits224indexPRIMARYoftable`somedb`.`sometable`trxid9843342279lockmodeSlocksgapbeforerecand that is weird because of the lock mode S locks gap in the last line. We get the exact same statement with the exact same value on the second thread, but with lock mode X locks gap.

MySQL: Locks and Deadlocks

Kristian Köhntopp - August 1, 2020
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 . A simple row lock But first let’s do things manually: We create a table kris with an integer primary key column and a secondary unindexed data column.

MySQL Transactions - writing data

Kristian Köhntopp - July 30, 2020
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: CREATETABLE`demo`(`id`bigintunsignedNOTNULLAUTO_INCREMENT,`counter`intNOTNULLDEFAULT'0',UNIQUEKEY`id`(`id`))INSERTINTO`demo`VALUES(1,0);INSERTINTO`demo`VALUES(2,0);...INSERTINTO`demo`VALUES(10,0);We are using some very simple programming to increment a counter: @sql.command() @click.option("--name", default="demo", help="Table name to count in") @click.option("--id", default=0, help="Counter to use") @click.option("--count", default=1000, help="Number of increments") def count(name, id, count): """ Increment counter --id by --count many steps in table --name """ for i in range(0, count): cmd = f"update {name}set counter=counter+1 where id = {id}" c = db.

MySQL Transactions - the logical side

Kristian Köhntopp - July 29, 2020
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. We are using a test table called demo with an id and a counter field, both integer. In it, we have 10 counters, all set to 0. CREATETABLE`demo`(`id`bigintunsignedNOTNULLAUTO_INCREMENT,`counter`intNOTNULLDEFAULT'0',UNIQUEKEY`id`(`id`))INSERTINTO`demo`VALUES(1,0);INSERTINTO`demo`VALUES(2,0);...INSERTINTO`demo`VALUES(10,0);In one session, we start a transaction and modify a counter value. We do not commit anything. Session1>starttransactionreadwrite;Session1>updatedemosetcounter=10whereid=3;Isolation In a second session, we check the data and notice a few things:

MySQL Connection Scoped State

Kristian Köhntopp - July 28, 2020
MySQL speaks its own proprietary protocol. It cannot be routed by an HTTP proxy, and a MySQL connection is entire unlike an HTTP connection. Specifically, a lot of state and configuration is tied to a MySQL connection, and it cannot be recovered on disconnect. What state is tied to a connection? Transactions A disconnect implies a ROLLBACK. So if you are in a transaction, all changes to the database that you attempted are lost, rolled back, as if they never happened.

MySQL Commit Size and Speed

Kristian Köhntopp - July 27, 2020
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 Python script. The script creates a test table in a database and writes 10.000 rows of test data into it, in commit sizes of 1, 2, 4, …, 1024 rows. $ ./mysql.py --help Usage: mysql.py [OPTIONS] COMMAND [ARGS].

MySQL Transactions - the physical side

Kristian Köhntopp - July 27, 2020
So you talk to a database, doing transactions. What happens actually, behind the scenes? Let’s have a look. There is a test table, and we write data into it inside a transaction: CREATETABLEt(idserial,datavarbinary(255))STARTTRANSACTIONREADWRITEINSERTINTOt(id,data)VALUES(NULL,RANDOM_BYTES(255))COMMITThe MySQL test instance we are talking to is running on a Linux machine, and otherwise idle to make observation easier. Also, we configured it with innodb_use_native_aio = false because observing actual physical asynchronous I/O and attributing it to the statement that caused it is really hard.

So I am a Windows User now

Kristian Köhntopp - July 7, 2020
So I am a Windows User now. I have an old MacBook pro, Late-2013 13" Retina, i7, 16 GB, 1 TB SSD, and the battery is done now, after 7 years. Also, the hardware is aging, and I want it refurbished and upgrade the son’s equipment (which is the previous 2010 MBP I had at that time). Modern Apple is not my thing. I have a company MBP, 2018 Retina 13" Four-Port, i7, 16 GB, and everything about this device is wrong.