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.

The Global Airport Database

Global Airport Database – Arash Partow

The Global Airport Database is a collection of data about all (???) airports in the world, big and small.  It covers a total of 9,300 airports worldwide.

That’s pretty interesting.  For example, how many airports do you think there are in Cyprus?  Obviously, there is the Larnaca International Airport and Paphos International Airport.  Then, there is one in Nicosia, which is not functioning since the island was divided.  And there is one in Acrotiri on the British military base.  Four, right?   Well, the Global Airport Database has a total of 7 (!!!) entries for the country of Cyprus:

LCCC:N/A:N/A:NICOSIA ACC/FIC:CYPRUS:000:000:000:U:000:000:000:U:00000:0.000:0.000
LCLK:LCA:LARNACA:LARNACA:CYPRUS:034:052:030:N:033:037:029:E:00003:34.875:33.625
LCNC:N/A:N/A:NICOSIA:CYPRUS:000:000:000:U:000:000:000:U:00000:0.000:0.000
LCPH:PFO:PAPHOS INTERNATIONAL:PAPHOS:CYPRUS:034:043:004:N:032:029:008:E:00013:34.718:32.486
LCRA:AKT:AKROTIRI:AKROTIRI:CYPRUS:034:035:025:N:032:059:016:E:00024:34.590:32.988
LCRO:N/A:N/A:EPISKOPI:CYPRUS:000:000:000:U:000:000:000:U:00000:0.000:0.000
LCRR:N/A:N/A:NICOSIA:CYPRUS:000:000:000:U:000:000:000:U:00000:0.000:0.000

Larnaca and Paphos are there. The Akrotiri one is there too. Then we have 3 records for Nicosia. And one more for Episkopi. Hmm …

Querying CSV with SQL

Excel is not the only tool available when it comes working with CSV files.  I have previously mentioned TextQL (here).  Yesterday, I tried another tool, called “q”.  But since searching for “q” is not very effective, it’s also known “q text as data“.

For those using Fedora, you can install it by simply running “dnf install q-text-as-data“.   Here’s an example of how it works:

$ q-text-as-data -H -d ',' "SELECT COUNT(DISTINCT(Project)) FROM deploy.csv"
95

In the above example, I’m querying the deploy.csv file, which is in the current folder. q supports both command and tab separated values, so I’m helping it out with the “-d ‘,’” parameter, saying that this particular one is a CSV. “-H” tells q that the first row in this file is used for headers. CSV files with headers are more convenient, as you can use headers as column names, instead of numerical indexes.

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?

Grakn and Graql – a database for AI

From the grakn.ai website:

Grakn is a distributed hyper-relational database for knowledge-oriented systems. Grakn enables machines to manage complex data that serves as a knowledge base for cognitive/AI systems.

Graql is Grakn’s reasoning (through OLTP) and analytics (through OLAP) query language. Graql is a much higher level abstraction over traditional query language – SQL, NoSQL, or Graphs.

Phinx joins CakePHP!

These are some really good news – Phinx joins CakePHP family!  If you are from a different technology stack and not familiar with these, Phinx is an excellent database migrations tool, which has been used by CakePHP framework for a while now.  The two worked great together.  Now that they are under the same roof, I’m expecting even more goodies!

We are very excited to announce that Phinx has joined the CakePHP team. The Github project has already been moved to the CakePHP organisation. The project itself will stay MIT-licensed but be gradually transformed into a Cake Software Foundation project. Other great news is that the current way to install and update Phinx remains unchanged.

As you are aware, CakePHP has been using Phinx since 3.0.0 for database migrations. The CakePHP Core team welcomes the opportunity to look after and maintain the project and will now start making changes to bring the code in line with the CakePHP (our) coding standards. As well as cleaning up issues and PR’s soon. We will be following up with our plans for the code and setting roadmaps in the coming weeks.

We welcome Phinx to the CakePHP family and hope to see Rob Morgan, Richard Quadling, Woody Gilk around!

Living Without Atomic Clocks

Living Without Atomic Clocks” is an interesting article that covers some design bits of distributed systems and CockroachDB (what a name!), especially those related to time precision.  This part in particular is the one I’m sure I’ll came back to at some point:

How does TrueTime provide linearizability?

OK, back to Spanner and TrueTime. It’s important to keep in mind that TrueTime does not guarantee perfectly synchronized clocks. Rather, TrueTime gives an upper bound for clock offsets between nodes in a cluster. Synchronization hardware helps minimize the upper bound. In Spanner’s case, Google mentions an upper bound of 7ms. That’s pretty tight; by contrast, using NTP for clock synchronization is likely to give somewhere between 100ms and 250ms.

So how does Spanner use TrueTime to provide linearizability given that there are still inaccuracies between clocks? It’s actually surprisingly simple. It waits. Before a node is allowed to report that a transaction has committed, it must wait 7ms. Because all clocks in the system are within 7ms of each other, waiting 7ms means that no subsequent transaction may commit at an earlier timestamp, even if the earlier transaction was committed on a node with a clock which was fast by the maximum 7ms. Pretty clever.

How to Synchronize WordPress Live and Development Databases

SitePoint runs through a few options that one can use to synchronize WordPress live and development databases.  I’ve linked to  some of these options before, but it’s nice to have them all conveniently together.  The solutions discussed include WordPress-specific tools:

as well as generic tools, such mysqldump, mysqlpump, rsync, and git.

Overall, it’s a pretty complete list of tools.  The one I’d like to add though is WP CLI, which allows a great deal of automation when it comes to WordPress, including things like database imports and exports, post and option management, and more.