Recent database error

What was your old DB system and what is the new one?

same DB system: 10.5.13-MariaDB to clarify: error started after periodic system update: in this case to wp 5.8.4 and buddyboss-platform 1.9.1.1. as well as some other plugins. I’ve been on ap 2.5.2 since august last year and I haven’t seen this error before.

OK, what was your previous WordPress version then?

5.7.5 I keep my infrastructure a minor release behind for wp and wc. and update my all my sites via wp-cli to specific versions

Can you please post the result of this SQL query:

SELECT column_name AS 'name', character_set_name AS 'charset', collation_name AS 'collate', column_type AS 'type', is_nullable AS 'nullable', column_default AS 'default' FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = DATABASE() AND table_name = 'evonet_avatar_privacy' AND column_name = 'log_message';

(If I haven’t made a typo somewhere.)

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0011 seconds.)

Sorry, my fault. Try this query please:

SELECT column_name AS 'name', character_set_name AS 'charset', collation_name AS 'collate', column_type AS 'type', is_nullable AS 'nullable', column_default AS 'default' FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'evonet_avatar_privacy' AND column_name = 'log_message';

name
charset
collate
type
nullable
default
log_message
utf8mb4
utf8mb4_unicode_520_ci
varchar(255)
YES
”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””’NULL”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””’

Huh, so it looks like the database schema got corrupted somewhere on the way. You will need to manually alter the log_message column in evonet_avatar_privacy to default to NULL (instead of that weird string).

ALTER TABLE 'evonet_avatar_privacy' MODIFY 'log_message' varchar(255) DEFAULT NULL;

should do it.

• This reply was modified 2 years, 11 months ago by pepe. Reason: Forgot to add table name

did you mean?

ALTER TABLE 'evonet_avatar_privacy' MODIFY 'log_message' varchar(255) DEFAULT NULL;

And are you saying table ‘evonet_avatar_privacy’ column ‘default’ should be NULL rather than all those single quotes surrounding NULL?

And let me know if there is anything to look into on my end. I’ll let you know if I track anything down.

I have a non-typical install setup and a bunch of my own code running in parallel with wp but I don’t write to the wp db at all. Also in case it’s relevant, the buddyboss folks just added some avatar stuff in 1.9 which seemed to mess up/conflict with ap. I haven’t had the time to look into what they did much. but maybe something there corrupted that column?

I mean. My sql is very rusty:

ALTER TABLE evonet_avatar_privacy MODIFY log_message varchar(255) DEFAULT NULL;

Fixed my comment (the '' are not really necessary for these idenitifiers and should be backticks really, but the syntax is valid). I doubt that BuddyBoss touched that table. More likely the schema has been weird for a long time, but the strange default value only now popped up because the WordPress collation changed and the table needed to be updated.

• This reply was modified 2 years, 11 months ago by pepe.

cool. thanks for fix and the info about probable cause. and again I appreciate your work.

It’s obviously not in the scope of this plugin but have you thought about combining more of the wp privacy pinging issues into one plugin? Or perhaps updating your plugin page to point folks to the remaining privacy holes?

It was a challenge making my wp sites 100% not-tracked-by-big-tech. Your plugin solved part of that. And I override google font (I found OMGF | Host Google Fonts Locally to be finicky) and external js pulls in my parallel code that serves those from my server. But I’d imagine adding a GUI option for:

define( ‘WP_HTTP_BLOCK_EXTERNAL’, true );
define( ‘WP_ACCESSIBLE_HOSTS’,’hostslist’);

would be pretty easy.

I know there is so much misc junk that gets inserted for tracking but I’d be curious to know your thoughts. Thanks!

It’s better to keep plugins focused. The whole avatar uploading functionality is big enough already (to properly integrate with Core and a variety of other plugins).

Some people are working on a solution for Google Fonts in Core, but I’m not sure what the status currently is. Unfortunately, it’s not a simple as flipping a switch (without losing functionality).

Did fixing the default value resolve the error message? Avatar Privacy should automatically have upgraded the collations afterwards.

Thanks for the reply and thoughts. And the reminder to see if it worked. Just checked the error log and still getting the same error:

[02-Apr-2022 17:24:08 UTC] WordPress database error Invalid default value for 'log_message' for query ALTER TABLEevonet_avatar_privacyCHARSET utf8mb4 COLLATE utf8mb4_unicode_520_ci, MODIFYemailvarchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL , MODIFYlog_messagevarchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT '\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'NULL\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'' made by require('wp-blog-header.php'), require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('plugins_loaded'), WP_Hook->do_action, WP_Hook->apply_filters, Avatar_Privacy\Components\Setup->update_check, Avatar_Privacy\Data_Storage\Database\Comment_Author_Table->setup, Avatar_Privacy\Data_Storage\Database\Table->setup, Avatar_Privacy\Data_Storage\Database\Table->maybe_create_table, Avatar_Privacy\Data_Storage\Database\Table->maybe_upgrade_charset_and_collation