MySQL

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

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.

MySQL vs. MariaDB: Reality Check

Percona Database Performance Blog runs this post – MySQL vs. MariaDB: Reality Check – comparing the MySQL, MariaDB and the Percona Server for MySQL. I think that most people using MySQL or MariaDB today consider them to be pretty much identical (a fork for legal reasons or something along those lines). This blog post shows that there are quite a few significant differences.

Database Popularity Index

Have a look at Red9’s Database Popularity Index, which is updated now on a monthly basis. Last year I blogged about a similar study.

One thing that is still mind-boggling to me is the total number of different database engines – over 300! I know there is a constant need for better and more powerful databases, but 300? Sounds like too much to choose from.

One other thing that I find slightly surprising is the popularity of the Microsoft Access. Really? With so much to choose from, people still stay with Access? What am I not getting here?