Die wunderbare Welt von Isotopp

MySQL: NULL is NULL

- 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

- 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

- 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

- 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

- 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

- 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

- 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

- 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

- 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

- 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

- 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

- 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

- 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

- 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

- 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

- 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.

How I set up my Python

- June 29, 2020
Because Martin wanted some starting point, here is how I set up my Python. There are a lot of other things one can do, but this is supposed to be just a starting point. For a new project, make a project directory, usually not with a local git repository. kk:Python kris$ mkdir project kk:Python kris$ cd project kk:project kris$ git init Initialized empty Git repository in /Users/kris/Python/project/.git/ We need a virtual environment to keep our modules apart from the system python.

Schulen digitalisieren

- June 23, 2020
Da war also ein Artikel bei Golem: Schulen bemühen sich vergeblich um Geld aus dem Digitalpakt . Die Schulen wollten das Geld nutzen, die Mittel würden aber trotz vieler Einreichungen nicht freigeben, sagte ein Lehrer und IT-Verantwortlicher eines Berliner Gymnasiums Golem.de. Das liege daran, sagt der Artikel, daß für Mittel aus dem Digitalpakt eine Minimal-Ausstattung und Anbindung der Schulen gefordert ist, die so oft nicht realisierbar sei. Außerdem

MySQL Window Functions

- June 21, 2020
Two questions from Reddit’s /r/mysql related to Window Functions: How do I make row.numbers happen and Get the difference between two values in different recordings . One of the new things in MySQL is the implementation of Window Functions. They are related to aggregates, but do not actually lump values together. To better understand what goes on, let’s create some fake data to work with: #! /usr/bin/env python3 # -*- coding: utf-8 -*- import MySQLdb as mdb import MySQLdb.

Export the entire database to CSV

- June 20, 2020
A question from Reddit’s /r/mysql: Really new to MySQL and had a request to export an entire database to csv for review. I can manually export each table using workbench but there are 10+ tables and 10+ databases so I was looking to export the entire database to csv. It is likely that you have additional requirements on top of this, so it would be best to script this in a way that would allow for customization.