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 a ER-model. order_id is the primary key of the Orders table. customer_id in the Orders table is a foreign key: The primary key of another (“foreign”) table in this table. Foreign keys can model relationships between tables (“entities”) in an entity-relationship diagram.

Entities and their relationships

In Entity-Relationship modelling we are using tables in a database to model entities in the real world. Each entry in such a table is meant to represent a “thing” in the real world. For example, in our …

As a data architect I always ensure that for any database schema change there a fully recoverable execution path.
I have generally advised to create a patch/revert process for every change.  For example, if a change adds a new column or index to a table, a revert script would remove the respective column or index.
The goal is to always have a defensive position for any changes. The concept is that simple, it is not complex.

In its simplest form I use the following directory and file structure.

/schema
    schema.sql
    /patch
        YYYYMMDDXX.sql     where XX,ZZ are sequential 2 digit numbers, e.g. 01,02
        YYYYMMDDZZ.sql
   /revert
       YYYYMMDDXX.sql   This is the same file name in the revert sub-directory.
       YYYYMMDDZZ.sql

At any commit or tag in configuration management it is possible to create a current copy of the schema, i.e. use schema.sql.
It is also possible to take the first …

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:

mysql> pager less
mysql> show engine innodb status\G
...
MySQL thread id 142531, OS thread handle 139990258222848, query id 4799571
somehost.somedomain someuser update
INSERT into sometable (identifier_id, currency, balance ) VALUES ('d4e84cb1-4d56-4d67-9d16-1d548fd26b55', 'EUR', '0')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3523 page no 1106463 n bits 224 index PRIMARY of table `somedb`.`sometable` trx id 9843342279 lock mode S locks gap before rec

and 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 …

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. We are filling it with some records with gaps between the primary keys.

We then START TRANSACTION READ WRITE and SELECT ... FOR UPDATE a record:

Session1> create table kris ( id serial, …

A student question: Does JavaScript make context switching for web-based applications obsolete? Wow! I asked what that meant. He said, it means JavaScript replaces all other server-side programming languages, like PHP, C#, or Python. I asked the student why he believed that. His answer was that’s what two interviewing managers told him.

I thought it would be interesting to put the idea to a test. Below is a Node.js script that acts as a utility that queries the MySQL database with substitution variables in query. It also returns a standard out (stdout) stream of the MySQL query’s results. It also supports three flag and value pairs as arguments, and optionally writes the results of the MySQL query to a log file while still returning result as the stdout value. All errors are written to the standard error (stderr) stream.

The Node.js solution is completely portable between Windows …

Watch this on-demand webinar to learn the right way to deploy geo-distributed databases. We look at the pitfalls of deploying a single site and passive sites, and from there we show how to provide the best user experience by leveraging geo-distributed MySQL.

Tags:  MySQL Geo-Distributed mysql clustering single site passive site best practices Webinar …

This is the kick-off of our Tungsten MySQL Use Case Webinar Series presented by Continuent CEO Eero Teerikorpi. Our first three webinars offer a choice of topics ranging from Global MySQL Availability, MySQL Data Protection to MySQL High Availability.

Tags:  Webinar MySQL use case tungsten clustering mysql cluster global availability mysql data protection …

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 …

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:

CREATE TABLE `demo` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `counter` int NOT NULL DEFAULT '0',
  UNIQUE KEY `id` (`id`)
)
INSERT INTO `demo` VALUES (1,0);
INSERT INTO `demo` VALUES (2,0);
...
INSERT INTO `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 = …

As Group Replication (GR) matures and it is deployed in a myriad of different systems, we begin to witness specific network conditions that we must be able to cope with in order to make Group Replication more tolerant and robust to those failures.…

Tweet Share