databases

TOP 10 MySQL 8.0 features for DBAs & OPS

Here’s a list of the TOP 10 MySQL 8.0 features for DBAs and OPS, with some detailed explanations of what they are and links to more information. The features covered are:

Temporary Tables Improvements
Persistent global variables
No more InnoDB System Tables
Reclaim UNDO space from large transactions
UTF8 performance
Removing Query Cache
Atomic DDLs
Faster & More Complete Performance Schema (Histograms, Indexes, …) and Information Schema
ROLES
REDO & UNDO logs encrypted if tablespace is encrypted

Encrypt MySQL data using AES technique

I came across this blog post from a while back, which demonstrates how to use AES encryption for the data in MySQL database.

INSERT into user (first_name, address) VALUES (AES_ENCRYPT('Obama', 'usa2010'),AES_ENCRYPT('Obama', 'usa2010'));
SELECT AES_DECRYPT(first_name, 'usa2010'), AES_DECRYPT(address, 'usa2010') from user;

This seems rather easy and straightforward (apart from a little calculation one needs to do for the VARBINARY field types). The only thing that I’m concerned about is whether the encryption keys will be visible in the MySQL process list (as in “SHOW FULL PROCESSLIST“).

SQL Keys in Depth

SQL Keys in Depth is an excellent read if you want to brush up on your knowledge of database keys and how they affect the performance of your application. For the laziest among you, here are the summary points, based on an extensive research of 60+ articles, StackOverflow questions and IRC discussions:

For each table:

Identify and declare all natural keys.

Create a <table_name>_id surrogate key of type uuid with default value uuid_generate_v1(). You can even mark it as a primary key if you like. Including the table name in this id makes joins simpler. It’s JOIN foo USING (bar_id) vs JOIN foo ON (foo.bar_id = bar.id). Do not expose this key to clients or anywhere outside the database.

For “join tables” declare all foreign key columns as a single composite primary key.

Add an artificial key if desired for use in a URL or anywhere else you want to share a reference to a row. Use a Feistel cipher or pg_hashids to conceal auto-incrementing integers.

Mark foreign keys to surrogate UUIDs as ON UPDATE RESTRICT and to external artificial keys as ON UPDATE CASCADE. Use your own judgement for natural keys.

MySQL optimize, repair, and analyze

Years ago I had the following script running as a cron job, but then I lost it somewhere. It took me a few minutes to find it again, but just in case I need it in the future, I’m saving it here.

#!/bin/bash
mysqlcheck --all-databases
mysqlcheck --all-databases -o
mysqlcheck --all-databases --auto-repair
mysqlcheck --all-databases --analyze

Found it here this time.

Change SQL mode for MariaDB in Fedora 27

After I upgraded my laptop to Fedora 27, I started experiencing some weird issues with most of the projects I am developing locally. Trying to save anything into the database that involves dates, started throwing the following errors:

Error: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2017-11-30T13:30:48+02:00' for column 'timestamp' at row 1

A quick look around showed that Fedora 27 ships MariaDB v10.2, while the previous distribution version shipped MariaDB v10.1. Digging through the changes between the two versions didn’t help much, even though there is slight mention of the related change.

SQL_MODE has been changed; in particular, NOT NULL fields with no default will no longer fall back to a dummy value for inserts which do not specify a value for that field.

StackOverflow is much more helpful, as always. These two threads – one and two – in particular, explained the changes and suggested the fix. I had to either fix the projects I was working on, or modify my local configuration to use the old SQL mode. This thread provided some more details, so the final solution was adding the following to the /etc/my.cnf and restarting the MariaDB service:

[mysqld]
sql-mode="NO_ZERO_IN_DATE,NO_ZERO_DATE"

And now we are back to normal.