Database error after upgrade to WordPress 6.1

(@awestbha)

Same here. We’re on utf8mb4

This reply was modified 3 years, 7 months ago by awestbha.

Plugin Author

(@eskapism)

I have not seen that problem before. Could you provide some more info, like your version of MySQL or MariaDB?

I’m not aware of anything in WP 6.1 that would cause this, but I’ll check the changelogs for 6.1 and see if I find anything..

Plugin Author

(@eskapism)

Diff: https://github.com/WordPress/WordPress/commit/fab7d60c644916ca69a3ba7c92e15b778da26b3f

Ok I found two commit that looks related:

Database: Account for utf8 being renamed to utf8mb3 in newer MariaDB and MySQL versions.

Database: Correct MariaDB version check in wpdb::has_cap().

Diff:
https://github.com/WordPress/WordPress/commit/2ede19910a0c333db03a97f197ee7634753bc578

Please all that have this bug let me know as much as possible about your system setup, especially your database server software and version.

This reply was modified 3 years, 7 months ago by Pär Thernström.
This reply was modified 3 years, 7 months ago by Pär Thernström.

(@awestbha)

PHP7.4 / WP 6.1 / MariaDB 10.5.15

I’ve also seen it happening on PHP7.3 / WP 6.1 / MariaDB 10.1.41

On both we’re using utf8mb4 / utf8mb4_unicode_ci

Plugin Author

(@eskapism)

I did a quick test with WP 6.1 and MariaDB 10.5 but did not get this error.

This is my output from the Database-section of the Site Health page (/wp-admin/site-health.php?tab=debug):

Extension	mysqli
Server version	10.5.13-MariaDB-1:10.5.13+maria~focal
Client version	mysqlnd 7.4.32
Database username	root
Database host	127.0.0.1:3308
Database name	wordpress_stable_mariadb_105
Table prefix	wp_stable_mariadb105_
Database charset	utf8mb4
Database collation	utf8mb4_unicode_520_ci
Max allowed packet size	16777216
Max connections number	151

If possible, could @awestbha or @krstarica post your output so I have something to compare with?

(@awestbha)

Extension	mysqli
Server version	10.5.15-MariaDB-0+deb11u1
Client version	mysqlnd 7.4.33
Database username	[removed]
Database host	localhost
Database name	[removed]
Table prefix	wp_
Database charset	utf8mb4
Database collation	utf8mb4_unicode_520_ci
Maximum allowed packet size	16777216
Maximum connections number	250

admittedly I can’t see anything too different there!

I am pretty sure my collation is actually utf8mb4_unicode_ci, though – haven’t moved anything to 520

Thread Starter

net

(@krstarica)

Extension	mysqli
Server version	10.6.10-MariaDB-log
Client version	mysqlnd 7.4.33
Database username	wordpress
Database host	127.0.0.1:3306
Database name	wordpress
Table prefix	wp_
Database charset	utf8mb4
Database collation	utf8mb4_unicode_520_ci
Max allowed packet size	268435456
Max connections number	400

The problem seems to be in collation being shown as “utf8mb4_unicode_520_ci” which is incorrect. Older version of WordPress showed correct one “utf8mb4_unicode_ci”.

Thread Starter

net

(@krstarica)

I’ve raised the issue:
https://core.trac.ww.wp.xz.cn/ticket/57072

Plugin Author

(@eskapism)

Could you check the character set and collation of the tables that Simple History uses? The tables are called wp_simple_history and wp_simple_history_contexts. On my local test site the character set is utf8 and the collation is utf8_general_ci, so they may be different that the other tables in WordPress. I’m thinking that maybe one of the tables got changed to another collation but the other one was not, and now that gives errors…

Thread Starter

net

(@krstarica)


MariaDB [(none)]> SHOW VARIABLES LIKE '%_server' ;
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| character_set_server | utf8mb4            |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+


MariaDB [(none)]> USE wordpress;
Database changed

MariaDB [wordpress]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_unicode_ci   |
+--------------------------+----------------------+


MariaDB [wordpress]> SHOW CREATE TABLE wp_simple_history;

| wp_simple_history | CREATE TABLE 'wp_simple_history' (
  'id' bigint(20) NOT NULL AUTO_INCREMENT,
  'date' datetime NOT NULL,
  'logger' varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  'level' varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  'message' varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  'occasionsID' varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  'initiator' varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY ('id'),
  KEY 'date' ('date'),
  KEY 'loggerdate' ('logger','date')
) ENGINE=InnoDB AUTO_INCREMENT=77532 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |


MariaDB [wordpress]> SHOW CREATE TABLE wp_simple_history_contexts;

| wp_simple_history_contexts | CREATE TABLE 'wp_simple_history_contexts' (
  'context_id' bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  'history_id' bigint(20) unsigned NOT NULL,
  'key' varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  'value' longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY ('context_id'),
  KEY 'history_id' ('history_id'),
  KEY 'key' ('key')
) ENGINE=InnoDB AUTO_INCREMENT=973705 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

(@awestbha)

CREATE TABLE'wp_simple_history' (
  'id' bigint(20) NOT NULL AUTO_INCREMENT,
  'date' datetime NOT NULL,
  'action' varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  'object_type' varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  'object_subtype' varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  'user_id' int(10) DEFAULT NULL,
  'object_id' int(10) DEFAULT NULL,
  'object_name' varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  'action_description' longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  'logger' varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  'level' varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  'message' varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  'occasionsID' varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  'type' varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  'initiator' varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY ('id'),
  KEY 'date' ('date'),
  KEY 'loggerdate' ('logger','date')
) ENGINE=InnoDB AUTO_INCREMENT=2700767 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

'CREATE TABLE'wp_simple_history_contexts' (
  'context_id' bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  'history_id' bigint(20) unsigned NOT NULL,
  'key' varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  'value' longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY ('context_id'),
  KEY 'history_id' ('history_id'),
  KEY 'key' ('key')
) ENGINE=InnoDB AUTO_INCREMENT=16042582 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED'

This reply was modified 3 years, 6 months ago by awestbha. Reason: tidy up

Thread Starter

net

(@krstarica)

The fix is to replace in wp-content/plugins/simple-history/inc/SimpleHistoryLogQuery.php the following code:


$sql_set_var = "SET @a:='', @counter:=1, @groupby:=0";

with:


$sql_set_var = "SET @a:=NULL, @counter:=1, @groupby:=0";

Anyway this query is extremely slow and that too should be fixed:
https://ww.wp.xz.cn/support/topic/serious-performance-issue-5/

Plugin Author

(@eskapism)

Thanks for the update @krstarica. I installed MariaDB 10.6 and modified the character set to utf8mb4 and the collation to utf8mb4_unicode_520_ci and then I finally did get the same error message that you got. Also, the fix you provided worked fine on MariaDB 10.6, but I need to test the fix on more db versions before I release an update.

This reply was modified 3 years, 6 months ago by Pär Thernström.

Thread Starter

net

(@krstarica)

3 years, 4 months ago

@eskapism any update on this?

Plugin Author