Fixing the “Illegal mix of collations” WordPress database error

When updating projects I also check the log files. In a project I maintained, I recently found the following error in the log files I have never seen before:

WordPress database error Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,COERCIBLE) for operation 'like' ...

As WordPress is not very strict with collations, your own WordPress website might also use different collations on different tables. In this project, there were six collations!

Fixing the collations

I order to eliminate this error, we have to convert all tables to use compatible collactions. It’s usually best to use only one collation for all of them. With WordPress 4.2.0 the collation utf8mb4 became the new standard. All database tables created after this update will be created with one of these collation variants. So for our single new collation, we use one of them.

Backup your database

STOP! Before we do anything with our database, we should create a backup. Changing the collation should not be an issue, but in any case it’s always best to create a backup. Use your favorite tool to create the backup. I usually use the WP-CLI for that:

wp db export

Now we can safely do the changes and roll back if things go wrong.

Changing the collation of one table

When changing anything for a database or table, we usually use the ALTER statement which we can also use here as followed:

ALTER DATABASE wp_project_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

This first statement will set the collation of the whole database, so when you create a new table using a database management tool, this collation will be used. But it will not change it for any of the tables. To do that, we have to alter the tables as well like this:

ALTER TABLE wp_project_name.wp_posts CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

This one statement would alter the wp_posts. Repeat the same for all other tables. Alternatively you can use the options provided by your database tool to change the collations.

Updating databases with lots of tables

The project I got this error was a multisite installation with 12 sites and 344 tables! That’s not fun updating all of these manually. Fortunately I found a nice little trick in a which will generate all the ALTER TABLE statements for all your tables:

SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;") 
FROM information_schema.TABLES
WHERE TABLE_SCHEMA="wp_project_name";

Just set your database name in the last line and run it. Then copy all the “generated” queries form the result and run them. Your might run into errors like this:

Invalid default value for 'comment_date'

Then just set the sql_mode temporarily by adding this statement before all the ALTER TABLE statements:

SET sql_mode = '';

Once all statements have finished successfully all tables should now have the same collation. In this example I’ve used utf8mb4_unicode_520_ci but on your server it might be a different variant.

Updating collations using a plugin

While writing this blog post I stumbled upon the Database Collation Fix plugin which can also do this task for you, in case you cannot use a database management tool or you don’t feel like running SQL statements. The only “downside” is, that the plugin will only convert tables to utf8mb4_unicode_ci (or three alternative collations) and not to the newer utf8mb4_unicode_520_ci collation. But this should generally not be an issue.

Conclusion

Even after working with WordPress for so many years there are still some errors I have never seen. In this case it was not an error specific for WordPress, but the way WordPress is handling collations can lead to this issue. Especially if you work with WordPress installations that have been around for a while (probably installed before version 4.2.0). So if you find the same error in your log files, now you should have an idea on how to solve it. And if you haven’t looked into the log file for a while, this blog post might be a reminder to check them.

Generate a dynamic username with the Gravity Forms User Registration add-on in a multisite

As mentioned in previous blog post, I use Gravity Forms quite often when creating forms. It also offers some great add-ons. One of this add-ons is the User Registation. With the help of this extension, you can create a WordPress user, after the form was submitted.

The “username” field

You basically set up a regular form. In this form you should ask for some manadtory fields you would need to create a user account. As a bare minimum, your should ask for an email address, so users can activate their account. Other fields might be first and last name. But what about a username? The add-on offers a special field for usernames. When you want to enable users to pick their own name, this is a perfect field to add.

Use the “email” field

As WordPress allows any user to login either with their username or with their email address, they might not even need to know their username. So when creating the “Feed” to create a user, you could just use the email field. Unless you are creating users for a multisite.

Create a dynamic username using other fields

In a multisite installation, a username can only contain lower case letters and numbers. This is because the username “can” be used for a subsite per user. But even if you don’t plan to have a site per user (which the add-on could also create for you), this constraint still applies. So how to deal with that without asking your users to pick their own username? Simply create a dynamic username using other fields.

Use the first and last name fields

On many platforms, your username will automatically be a combination of your first and last name. So we can use a filter to create such a username. But as this username may still contain some invalid characters, we have to “sanitize” it. Fortunately WordPress offers a function for that. And as two new users may have the same first and last name, we need to find a way to solve that as well. But let’s start with the callback function to the Gravity Forms hook first:

function gf_ms_get_unique_username( $username, $feed, $form, $entry ) {
        // Get first and last name from the form submission.
        $first_name = rgar( $entry, rgars( $feed, 'meta/first_name' ) );
        $last_name  = rgar( $entry, rgars( $feed, 'meta/last_name' ) );

        // Generate a new username making it compatible with multisite constraints.
        $new_username = gf_ms_uf_get_unique_username( sanitize_user( $first_name . $last_name ) );

        if ( ! empty ( $new_username ) ) {
                $username = $new_username;
        }

        return $username;
}
add_filter( 'gform_username', 'gf_ms_uf_set_username', 10, 4 );

Here we use the hook gform_username which is used for the special username field. In the callback we get the values from the first and last name fields and pass them to our helper function to generate a unique username. If we get a new username, we return it.

Generating a unique username

Now how do we get this unique username? Well, we just need a method similar to the one that WordPress uses to get unique permalinks for posts/pages/terms with the same name. We use a “counter suffix” until the username is unique:

function gf_ms_get_unique_username( $username ) {
        $number            = 2;
        $original_username = $username;

        while ( username_exists( $username ) || gf_ms_signup_exists( $username ) ) {
                $username = $original_username . $number ++;
        }

        return $username;
}

function gf_ms_signup_exists( $username ) {
        global $wpdb;

        return $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM $wpdb->signups WHERE user_login = %s",
                $username
            )
        );
}

In the while loop we check not only if the username already exists, but also if the username has been used by a previous user who already signed up, but hasn’t confirmed his account using the activation link received by mail. To check this, we use another helper function with code similar to the one in Core.

Conclusion

WordPress does offer a native way to sign up new users – to single sites or multisite. But if you want to ask the new users for additional information, an add-on like the one from Gravity Forms can really improve the sign-up process. Signing up users for a multisite can be tricky, but with some good helper functions, this can also be solved. As always, you can find the code from this blog post in a GIST where you can also download it as a ZIP and install it as a plugin.

Get a custom primary navigation per page

In my last blog post I wrote about how to create a simple landing page template. In the text I’ve also mentioned, that you might want to create a custom navigation for the pages you create using this page template. In this blog post, I want to show you how to do that.

Create a menu for a landing page

First, we need to create a custom menu for the landing page. This can be done in either the customizer or the “Appearance -> Menues” section in the admin backend. For the name of the new menu, it’s easiest to use the same name as for the page slug. This will make sure that we can find the custom menu later.

Overwriting a menu by a custom menu

After we have created a new menu with the page slug, we have to dynamically use this new menu. Every menu item is saved in the database as a post type nav_menu_item. The menu itself is a nav_menu taxonomy term. When WordPress tries to load a menu for a menu location, it would query for this taxonomy. Here we can use a filter to get our custom menu with the following code:

function current_page_wp_get_nav_menu_object( $menu_obj, $menu ) {
	if ( $menu_obj instanceof WP_Term && 'primary' == $menu_obj->slug ) {
		$current_slug  = get_post_field( 'post_name', get_the_ID() );
		$page_menu_obj = get_term_by( 'slug', $current_slug, 'nav_menu' );

		if ( $page_menu_obj ) {
			$menu_obj = $page_menu_obj;
		}
	}

	return $menu_obj;
}
add_action( 'wp_get_nav_menu_object', 'current_page_wp_get_nav_menu_object', 11, 2 );

In line 2, we check for the menu location. If we forget to add this step, we would overwrite any menu on the page. So even footer menus, social menus, etc. In this case we will check for the location name primary, the main menu position of your theme may have a different name.

After this check, we get the current slug of the page, try to get the nav_menu term for this term and if we have found it, we will overwrite the original $menu_object to use our custom page menu.

Conclusion

Overwriting a template file is quite easy. Dynamically overwriting a menu being use on a specific position is not possible using template files, but becomes quits easy when using the right filter. Using this little code, you can now have a custom menu per landing page. If you just want to use the same custom menu, when using the page template from the blog post before, you can alternatively check in the filter, if the page template is used and then load another menu.

How to create a landind page without a plugin

A new client is using multiple WordPress plugins to create “landing pages”. Some of these plugins have been removed from the plugin directory. They will not receive any (security) update in the future. In a call it became clear, that we had very different ideas of what a landing page actually is.

What is a landing page

There is not a single best definition of what a landing page is. For me, it can be as simple as a single static page on your website, that presents all the information necessary to reach it’s primary goal. The page is reachable by a permalink and this link can either be found with the right keywords on a search engine or is used in social media posts.

Landing pages can be more complex depending on the goals they want to achieve (like selling things, getting new customers, etc.) and can use techniques to track their success in different ways.

A simple landing page

Many people would argue, that a landing page has no, or just a very limited navigation. Visitors would usually “navigate” from a landing page to another page (shop, checkout, registation, etc.) use “call to action button”. So on a complex website with a huge navigation, a sidebar and a large footer, those elements might want to be removed. But you might not have to use a plugin to do that, as WordPress offers an easier method for this.

Creating a “landing page” template

When you create a page, you can pick a “Template” in the “Page” (or “Post”) tab in the block editor. Those templates are offered by your theme (if it has some), but you can also create them in your child theme. You can find more information on how to create page templates in the Theme Handbook but often you simply copy the page.php, singular.php or index.php file, rename it (usually those files are prefixed with “page-“) and add a comment to the beginnig of the file with the name:

<?php
/**
* Template Name: Landing Page
*/

Just below that you usually find a call to get_header() which will load the header.php file from the theme. This is another place where we might want to customize our landing page template by using a different header. For this you pass a string to the function:

get_header( 'landing-page' );

If call the function like this, WordPress will try to find a file header-landing-page.php instead of the default file. Just copy the header.php as well, rename it accordingly. Now you can be creative in this new file. You can remove the navigation menu all together, use a different navigation position or even change things like logo, etc. in this template.

Creating content for the landing page

Now we are done with our new (and maybe reduces) header, we can continue with the content of the landing page. But this is quite simple the block editor offers all elements you need for a simple landing page, including easy to use buttons.

The future of landing pages

While this approach still needs some custom code, there is a new way in sight to create landing pages: Full Site Editing! Once this is available in WordPress (and themes), you no longer have to create page templates in a child theme. You simply customize the layout of the entire page. So adding/removing navigation menus will be become as easy as writing the content of the page. Then special landing page plugins really need to offer something important, that would justify using another plugin.

Checking your domain security with Mozilla Observatory

In the last two blog posts I have presented two tools I use a lot. This week I want to continue with a third tool, that I have already briefly mentioned in my blog post Better security for WordPress with secure server headers: the Mozilla Observatory tool.

HTTP Observatory

When you run the tool for a domain, you first get an overview about the HTTP security. This was the part I’ve talked about in my previous blog post. The summary of the result may look like this:

Read more →

Generating your own custom nginx configuration

In my last blog post I have presented you a tool I use to get “The perfect SSL configuration for your server”. This week I want to talk about another tool I use quite frequently to get a good nginx configuration. The tool could once be found at nginxconfig.io (which still redirects) and is now hosted as the DigitalOcean Community Tools – NGINXConfig.

The basics

The tool presents itself with some basic settings. It’s set to PHP and uses example.com for the domain. This is the first setting to change. With the “Add site” option you can also add mutiple domains which will result in multiple “Per-website” configurations.

The basic configuration options for the technology and hostname

In the Presets you can choose from various other widely used technologies. If you want to run a WordPress site behind nginx, choose the specific preset. It will generate the necessary rewrite rules and some security rules as well.

Presets

In this first section you set your hostname, the subdomain you want (or dont want) to use and the server path. You can also specifiy if you use Let’s Encrypt to get your certificates (or set a custom one), setup a PHP server/socket (and a backup) and some other more advanched options.

Global config

In the HTTPS settings group you will find the SSL profiles from the last blog post. It’s usually best to leave them at “Mozilla Intermediate”. When using the configuration tool for a WordPress website make sure to check the “Security | Content-Security-Policy” setting. As WordPress embeds inline CSS and JS it need to be allowed to run those scripts. If you set this policy to strict, your website might not work properly. When you have selected WordPress in the presets, the tool will give you a message on how to set this policy.

Another group of setting you might want to customize are found in the “Performance” tab. The expiration is only set to one week. As WordPress usually handles cache invalidation for assets quite well (be appending the plugin, theme or WordPress version) you can probably use a longer expiration period. But if you host multiple different system behind one nginx, you might want to be more careful here.

Saving your settings

Any time you change some settings, the URL in the browser will change. You can also find this URL in the “Tools” tab in the “Share configuration” field. I would highly recommend that you save or bookmark this URL, but it can also be found in the nginx.conf file of the resulting package.

Applying your configuration

Once you have finished customizing the configuration, follow the instructions in the “Setup” section. You will probably start by downloding the configuration archive and uploading it to your server and extracting it there. If you don’t have a running Let’s Encrypt installation, you can find some more commands on how to get this set up as well.

Testing your configuration

As I’ve also said in my previous blog post, you should never blindly copy/paste a configuration without checking it before you apply it to a running server. The wordpress.conf for example denies access to the xmlrpc.php file. This might be a resonable counter messure against brute force attacks to XML-RPC, but if you use the official WordPress apps, this file must not be blocked. So after applying the configuration, check some important pages on both frontend and backend, if they still work.

Help improving the tool

As this is a community tool, you can report issues or suggest improvements in the projects GitHub repository. I’ve done that myself two times. One issue I have found after updating the configurations was a deny pattern blocking the wp-comments-post.php file, which made commenting on the blog impossible. So if you find something, please help making the tool better for everyone.

Conclusion

Setting up a nginx server is really hard, if you don’t to that as your daily job. I only have to change my configuration so rarely, that I often have to look up basic things (I maintain Apache webservers more often in my day job). Tools like this really helped me a lot getting a first start and finding some good best practices. Another great ressource for such best practices are the configurations from the HTML5 Boilerplate. They don’t only offer example configurations for nginx but also for many other servers. But those are more for advanched users, as they do not offer a configuration tool.

The perfect SSL configuration for your server

It’s 2021 and we are all using SSL for all our website, right? Unfortunately there are still many sites out there not using SSL. But even those who fo use SSL often use insecure settings. Today, I want to give you some tips for tools you can use to optimize the SSL settings – not only for your web servers.

Analyzing the current status

Before we can optimize the settings, we should first get an idea of what we want/have to optimize. My go to tools is the SSL Server Test (Qualys SSL Labs) where you simply paste your host name and run the test. The test result might look like this:

SSL Labs test result

If your result looks something like this, you could stop reading now, as it’s almost the best result you can get. Getting also 100 with “Cipher Strength” is only possible, if the server is using the latest components and if you change various settings deep down in your OS, some of them invole manual re-compilation of components. And reaching 100 points in all four categories might look nice, but you may exclude some people from visitig your site, as you can only offer TLS 1.3, which some older (but not completely outdated) clients do not support.

Optimizing the SSL settings

Some of the checks will test your SSL certificate, but most of them will look on your web server configurations and here you can improve your settings the most.

Get a SSL certificate with a large key size

As for the SSL certificate, make sure that you get one if a key size of 4096. If you use Let’s Encrypt, you can get such a certificate by adding --rsa-key-size 4096 to your certbot call. Similar options are also available for many other Let’s Encrypt clients.

Generating the SSL settings for your server

Many of you might know Apache and nginx, but there are many more web servers (many of them open source) you can use. But also don’t forget you mail server, load balancer, database server, etc. when configuring SSL. And all of them are using different configuration files. Even setting the list of ciphers the server can use may have a different syntax. But forunately there is handy tool to help you, the moz://a SSL Configuration Generator. Here you simply select the “Server Software” you want to configure and the “Mozilla Configuration” which you want to use. For the later, you should probably fo with “Intermediate”:

Mozilla SSL Configuration Generator

You can optionally set version numbers of your server and OpenSSL which might be usefule if your are on a very old hosting that still runs Apache 2.2 or some other old software. In the “Miscellaneous” section you will also find a “HTTP Strict Transport Security” (HSTS) setting. When you set this incorrectly, you might make site using a subdomain unreachable, if they do not already use SSL (which they also really should).

So even though this tool give you some best practices, don’t blindly copy/paste all of the content into your server configurations. While you are changing the settings, you may want to update them in steps and checking the new results with the SSL Labs test again, until you find a configuration that works for you and has the best ratio between high security and device compatibility.

Conclusion

Setting a WordPress Installation to use SSL became really easy with version 5.7, but configuring a server so it’s only using a secure configuration can be hard. Using the right tools even that can be done rather easy.

Exclude first page from Gravity Forms progress bar

When we have to create more complex forms, we often use the Gravity Forms plugins. In one project, we wanted to use it to implement a form with multiple steps, each only having one radio group. Users should see the number of steps and the progress in the form. This is fairly simple, you can just add a “Page” and the other fields to these pages. When you insert the form into a post or page, you will get the following result:

The form with the “Step 1 of 3” progress bar

You can see the issue in this screenshot. The first pages does not have any form fields, but only a “HTML” field with an introduction text. But as it is a page of the form, the progress bar already starts counting.

Starting at page two

In order to fix this, we want to do two things. First we want to hide the progress bar from the first page. But then we would start with “Step 2 of 3” instead of “Step 1 of 2”, so we also have to update the text for the steps on all following pages.

Read more →

Placing the post or page title inside of the content

WordPress 5.8 is only a little more than two weeks away. Probably the biggest new feature is the introduction of Full Site Editing (FSE). This will enable you to edit the whole website with the block editor. But this will only work, if you use a FSE enabled theme. But there are some handy new block you might want to use even with your current theme. In a new project I worked on lately, I was using one of these new blocks (with the Gutenberg plugin installed).

The “Post Title” block

One of the new blocks will allow you to place the title of a blog post into the content. Even though it’s named “Post Title” it will also work with pages, or any other custom post type.

Let’s say you want to place the title in a colum next so some media element, just select this block at the desired position:

Read more →

Find cause for high LOAD with low CPU load at the same time

This week, one server had a LOAD of more than 2500% (around 150 with 6 CPUs), but the CPU load was only around 5%, so this was a sign, that the CPU load was not the cause of the high LOAD on the server:

top - 15:53:42 up 7 days, 10:01,  2 users,  load average: 159,47, 149,89, 160,80
Tasks: 540 total,   1 running, 468 sleeping,   0 stopped,   0 zombie
%Cpu(s):  2,0 us,  2,0 sy,  0,0 ni,  0,0 id, 95,6 wa,  0,0 hi,  0,4 si,  0,0 st
KiB Mem : 12296516 total,   607940 free,  9710388 used,  1978188 buff/cache
KiB Swap: 12578812 total,  7439140 free,  5139672 used.  1752884 avail Mem 

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 5564 user@xyz  20   0   99712  36384   5308 D   4,6  0,3   0:00.22 spamassassin
 1539 root      20   0 2394080  55984   7412 S   2,0  0,5   1365:51 fail2ban-server
 4561 root      20   0   33784   6168   3700 S   1,3  0,1   0:02.39 htop
    8 root      20   0       0      0      0 I   0,7  0,0  11:17.44 rcu_sched
...

Hard disc operations as a possible reason

Not only a high CPU load, but also a high number of IO operations, read and write access to the hard drive, can cause a high LOAD.

Read more →