I’ve been working with a lot of WordPress plugins recently and have come across a bug that had me stumped for a while, therefore, I thought it’d be worth sharing it here.
After installing and activating a plugin that created new database tables, I realised that the tables had not been installed. I contacted the plugin support who suggested re-installing WordPress. Reluctantly I did this but it didn’t help at all, I therefore decided I’d dig into the issue and see if I could track it down.
A bit of work and a few var_dump()‘s later I found that there as an error with the database creation SQL (below)
CREATE TABLE IF NOT EXISTS wp_domain_mapping (
id BIGINT NOT NULL AUTO_INCREMENT,
blog_id BIGINT NOT NULL,
domain VARCHAR(255) NOT NULL,
active TINYINT DEFAULT 1,
PRIMARY KEY (id),
KEY blog_id (blog_id, domain, active)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
I ran the query manually and realised it was returning the error Specified key was too long; max key length is 1000 bytes. I’d never seen this error before, so I dug into it a little more, this page informed me that InnoDB has a maximum key size of 1000 bytes, unless the innodb_large_prefix flag is set in MySQL in which case the maximum size is 3072 bytes.
That said, I found the following document where I could check the storage requirements of each field type. From this, I can tell that the BIGINT field, blog_id is 8 bytes, the TINYINT field, active is 1 byte and as UTF8 needs 3 bytes per character, the domain field is 255 * 3 bytes or 765 bytes. This gives us a total key length of 774 bytes.
It was then that I realised that I wasn’t using standard UTF8 but utf8mb4, which WordPress has set as it’s default character set as of 4.2. utf8mb4 requires 4 bytes per character and therefore the domain field calculation was wrong and was actually 255 * 4 bytes or 1,020 bytes in length. This is now an issue as our key is bigger than the size allowed by InnoDB hence the issue creating the tables.
Now as far as I can see, there is only one real way of getting around this and it’s the method WordPress has chosen, reducing the field length from 255 chars to something less, 247 in this case.
Now I know about this it shouldn’t be much of an issue, but I wonder how many plugin developers aren’t going to realise this has changed and are going to run into this. I foresee it being a major pain for a few months.
Anyway, onwards and upwards now!
Cheers for now!
