Multisite Sites Cannot Create Database tables in 4.2.1 fresh clean install

[reidbusi]

Looks like a fresh multisite was not tested with 4.2.1. On attempting to login to a freshly created site one gets "Error establishing a database connection".

Error logs indicate that the new utf8mb4 collation is the problem. Looks like multisite is trying to create utf8_general_ci tables and it is not working.

I will try to force a utf8 collation install and see if the issue disappears.

[jeremyfelt]

Hi @reidbusi, thanks for the report.

Can you provide a few more details to help reproduce the issue? I haven't had trouble with a new (or upgraded) 4.2.1 multisite installation.

• What MySQL version are you running?
• What are the DB_CHARSET and DB_COLLATE values set to in wp-config.php?
• What charset and collation are set as default in your MySQL configuration?
• Do have error logs on which queries failed? I'm guessing it's because of the collation, but an explicit MySQL error would be great.

[jeremyfelt]

Additional info. Can you check to see if mysqli is supported in your PHP configuration? This may be the same issue as #32127.

[reidbusi]

Can you check to see if mysqli is supported in your PHP configuration?:
MysqlI Support enabled
Client API library version 5.5.40-36.1

What MySQL version are you running?:
5.5.40-36.1 - Percona Server (GPL), Release 36.1, Revision 707

• (My host apparently changed to Percona without notice. Supposedly it is a drop-in replacement for MySQL, but I have never heard of it.)

What are the DB_CHARSET and DB_COLLATE values set to in wp-config.php?:

define('DB_CHARSET', 'utf8mb4');
define('DB_COLLATE', '');

• (as set by the installer, have not modified)

What charset and collation are set as default in your MySQL configuration?

As I am not root, I am not sure. But when I create a new database from the cPanel account the collation is utf8_unicode_ci.
This is what phpMyAdmin tells me:

character set client utf8
(Session value) utf8mb4
character set connection utf8
(Session value) utf8mb4
character set database utf8
character set filesystem binary
character set results utf8
(Session value) utf8mb4
character set server utf8
character set system utf8
character sets dir /usr/share/percona-server/charsets/
collation connection utf8_unicode_ci
(Session value) utf8mb4_unicode_ci
collation database utf8_unicode_ci
collation server utf8_unicode_ci

Do have error logs on which queries failed? I'm guessing it's because of the collation, but an explicit MySQL error would be great.
I currently do not, I did from my last attempt... oh wait I do have those saved... here is an example:

[30-Apr-2015 13:56:07 UTC] WordPress database error COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4' for query CREATE TABLE wpmu_5_terms (
 term_id bigint(20) unsigned NOT NULL auto_increment,
 name varchar(200) NOT NULL default '',
 slug varchar(200) NOT NULL default '',
 term_group bigint(10) NOT NULL default 0,
 PRIMARY KEY  (term_id),
 KEY slug (slug(191)),
 KEY name (name(191))
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8_general_ci made by wpmu_activate_signup, wpmu_create_blog, install_blog, make_db_current_silent, dbDelta

[reidbusi]

Confimred. As expected, manually creating wp-config.php during the installation and specifying:

define('DB_CHARSET', 'utf8');

... and everything works fine. (same server, same host)

So it looks like 4.2.1 multisite has not been adequately tested with utf8mb4 character set.

[jeremyfelt]

Thank you for all that information @reidbusi! Percona is a popular drop-in replacement for MySQL. I don't believe there are any known issues.

Some general thoughts for the ticket.

We set the utf8_general_ci charset in init_charset() before attempting to bump a utf8/utf8_general_ci connection to utf8mb4/utf8mb4_unicode_ci. It seems like this is the only place where this change can be short circuited.

if ( ( $this->use_mysqli && ! ( $this->dbh instanceof mysqli ) )
  || ( empty( $this->dbh ) || ! ( $this->dbh instanceof mysqli ) ) ) {
	return;
}

I've attempted to replicate with fresh installations of multisite in MySQL 5.5.40 and PHP 5.5.9 and have had no trouble creating new sites.

• Started with the database set to a default charset/collation of latin1/latin1_swedish_ci. No issues with the auto generated wp-config.php file and utf8mb4 charset.
• Modified my.cnf to use utf8/utf8_general_ci by default, no issues with auto generated wp-config.php and utf8mb4.
• Changed the character-set-client-handshake setting between true/false with no change. Not that I really understand what that does. :)

If I set something in DB_COLLATE that is not utf8_ based, like latin1_swedish_ci, then I do get similar errors. But that is not what is described in the ticket, this should be reproducible with DB_COLLATE as ''.

I'm going to attempt a swap to Percona in my VM to try and mirror configurations better, but I can't see that being the issue.

I'm guessing @pento will have good feedback here.

[jeremyfelt]

No change for me when swapping MySQL 5.5.40 to Percona 5.5.42-37.1.

[pento]

I wonder if this is being caused by #32127?

@reidbusi, could I get you to install the patch from #32127 and see if that helps?

[I-was-a-developer]

Replying to pento:

I wonder if this is being caused by #32127?

@reidbusi, could I get you to install the patch from #32127 and see if that helps?

I'm having the same issue in the same situation, i.e. WP 4.2.1 fresh installation + multisite, but with diff environment:
php: 5.4.38
db: ver. 5.6.19 - MySQL Community Server (GPL)
mysqli: mysqlnd 5.0.10 - 20111026

I used the the patch from #32127 and it fixed the issue. I can see that now the wp_N_whatever tables are being created for the N-th site. Sweet. Thanks.

[pento]

Duplicate of #32127.

[chriscct7]

#32308 was marked as a duplicate.

[Schuurmudgeon]

I opened a thread on de Forums here ​https://wordpress.org/support/topic/wp-multisite-fails-to-create-relevant-mysql-tables-on-jelastic and I'm wondering if this is the same issue?

[dd32]

As for why this is fixed via #32127 here's the deal:

• setup-config.php sets DB_CHARSET as utf8mb4 in the event of ( 'utf8mb4' === $wpdb->charset || ( ! $wpdb->charset && $wpdb->has_cap( 'utf8mb4' ) ) ) (Namely, the 2nd part comes into play here)
• WordPress Single-site installs fine, as it uses utf8mb4 charset, and has no collate defined, so doesn't include any in it's CREATE TABLE commands, just the DEFAULT CHARACTER SET utf8mb4.
• WordPress Multisite is a bit different however, Multisite always sets a collate defaulting to utf8_general_ci, If it breaks from wpdb::init_charset() before the collate is fixed to utf8mb4, then all CREATE TABLE's that Multisite create will include DEFAULT CHARACTER SET utf8mb4 COLLATE utf8_general_ci which isn't going to work.

[dd32]

#32308 was marked as a duplicate.