database

There are times when you may notice your MySQL or MariaDB database server getting very slow. Usually, it's a very stressful time, as it means your site or application is also getting very slow since the underlying database is slow. And then when you dig in, you notice that logs are filling up—and in MySQL's case, the slow query log is often a canary in a coal mine which can indicate potential performance issues (or highlight active performance issues).

But—assuming you have the slow query log enabled—have you ever grabbed a copy of the log and dug into it? It can be extremely daunting. It's literally a list of query metrics (time, how long the query took, how long it locked the table), then the raw slow query itself. How do you know which query takes the longest time? And is there one sort-of slow query that is actually the worst, just because it's being run hundreds of times per minute?

Using the official MySQL Docker image from Docker Hub, I recently ran into the error:

Host '172.18.0.1' is not allowed to connect to this MySQL server

The only change I had made to my docker-compose.yml file was:

mysql:
  image: mysql:5.6
  ports:
    - '3306'
  volumes:
    # Use this option to persist the MySQL DBs in a shared volume.
    - ./mysqldata:/var/lib/mysql:rw,delegated
    # Use this option to persist the MySQL DBs in a data volume.
    # - db_data:/var/lib/mysql

I switched from using a data volume (db_data) to mounting a volume from my host (mysqldata in the current directory), and after the next time I did a docker-compose down and docker-compose up, I started seeing the error about my host not being allowed to connect to the MySQL server.

Recently I had to test importing some very large databases with lots of giant log tables (e.g. 5+ GB tables), and when I tried doing an import into a local docker MySQL container instance, I got ERROR 1114: The table is full. Here are the commands I used:

I found that—likely due to some Docker filesystem defaults—the MySQL import would fail every time when there was a database table containing more than 1GB of data. Now, this could be related to the way the database was exported, and I also found some issues where people were using memory tables that got exported and wouldn't import cleanly.

The PostgreSQL logo. Same family as PHP's mascot!

For the past few years, I've been intending to kick the tires of PostgreSQL, an open source RDBMS (Relational DataBase Management System) that's often used in place of MySQL, MariaDB, Oracle, MS SQL, or other SQL-compliant servers. Drupal 7 worked with PostgreSQL, but official support was a bit lacking. For Drupal 8, daily automated test builds are finally being run on MySQL, SQLite, and PostgreSQL, so many of the more annoying bugs that caused non-MySQL database engines to fail have finally been fixed!

Update: As of Drupal 7.50, Emoji/UTF-8 mb4 is now supported for MySQL (and other databases) in core! See the documentation page here for more information on how to configure it: Multi-byte UTF-8 support in Drupal 7. This blog post exists for historical purposes only—please see the Drupal.org documentation for the most up-to-date instructions!

On many Drupal 7 sites, I have encountered issues with Emoji (mostly) and other special characters (rarely) when importing content from social media feeds, during content migrations, and in other situations, so I finally decided to add a quick blog post about it.

Have you ever noticed an error in your logs complaining about incorrect string values, with an emoji or other special character, like the following:

(Note: Actual Emoji was removed from this summary post to prevent Drupal Planet's aggregator from barfing on the feed... due to this very issue!).

To fix this, you need to switch the affected MySQL table's encoding to utf8mb4, and also switch any table columns ('fields', in Drupal parlance) which will store Emojis or other exotic UTF-8 characters. This will allow these special characters to be stored in the database, and stop the PDOExceptions.

I typically use a MySQL GUI like Sequel Pro when I do database dumps and imports working from my Mac. GUI apps often give checkboxes that allow you to choose whether to include the structure/content/drop table command for each table in an export.

When using mysqldump on the command line, though, it's not as simple. You can either do a full dump and exclude a few tables entirely (using --ignore-table, or dump the structures of just one set of tables using the -d option. But you can't do both in one go with mysqldump.

However, you can use the power of redirection to do both commands at once to result in one dump file with all your tables, with structure only for the tables you specify:

[Update: And, as quickly as I finished writing this post, I thought to myself, "surely, this would be a good thing to have drush do out-of-the-box. And... it already does, making my work on this shell script null and void. I present to you: drush sql-drop! Oh, well.]

When I'm creating or updating an installation profile/distribution for Drupal, I need to reinstall Drupal over and over again. Doing this requires a few simple steps: drop/recreate the database (or drop all db tables), then drush site-install (shortcut: si) with the proper arguments to install the site again.

In the past, I've often had Sequel Pro running in the background on my Mac, and I'd select all the database tables, right-click, choose 'Delete Tables', then have to click again on a button to confirm the deletion. This took maybe 10-20 seconds, depending on whether I already had Sequel Pro running, and how good my mouse muscles were working.

Drupal 7 uses InnoDB tables. InnoDB provides many benefits, but can cause some unexpected headaches. One headache for me is that, by default, MySQL tells InnoDB to create one file on your system, called ibdata1, to hold ALL the data from EVERY InnoDB table you have on your MySQL server. This file never shrinks in size; it only expands to contain new data. If you delete something from MySQL or drop a table, the space that table was using is reallocated for other new data. This isn't a bad thing, especially for those who have a lot of drive space, and not many databases that are altered or dropped quite frequently.

I develop a lot of sites on my little MacBook Air (with a 128GB SSD), so I often download database snapshots from live and testing environments, empty out the tables on my local environment, then import the database dumps. Can you spot the problem here?

Using Daisy Disk I just noticed that my ibdata1 file had grown to more than 10 GB, and my Air's drive only had about 5 GB free space!

So, after reading through MySQL's InnoDB Engine documentation and this answer on Stack Overflow, I found that it's not too hard to change MySQL to keep data tables in their own files, and delete the files after the tables are deleted (thus saving me a ton of space). It just takes a little time and annoyance.

Here's how to do it, roughly:

I've had to do this a couple times, and every time, I look around on Google for some good solutions, but don't find much. Basically, I have two tables of data, and I want to see if there are any rows in the first table that aren't in the second (or, conversely, I only want values that are in the first table AND the second).

To select rows in the first table that don't have any corresponding values in the second, try:

Conversely, if you just want to select rows in the first table that are also in the second (but discard rows that don't have corresponding values in the second), try:

For my Drupal site, I needed to do something like: