"Updating failed. Could not update the meta value of footnotes in database." due to duplicate footnotes meta_key.

[gdogabriel]

Issue found in both trunk and 6.8.

wp-includes/meta.php > function update_metadata > Line: 314-317

Incorrect code:

<?php
        $result = $wpdb->update( $table, $data, $where );
        if ( ! $result ) {
                return false;
        }

Per $wpdb->update documentation, it says:

@return int|false The number of rows updated, or false on error

The code "! $result" is true when 0 or when false.

It means that when mysql returns "Query executed OK, 0 rows affected.", $wpdb->update returns 0, the function update_metadata returns false, and it means failure.
Even when the entry exists, in case the field already has the expected value, mysql returns 0 rows affected.

It’s causing issue in different scenarios, like this one:

Updating failed. Could not update the meta value of footnotes in database.

​https://wordpress.org/support/topic/updating-failed-could-not-update-the-meta-value-of-footnotes-in-database/
File: wp-includes/rest-api/fields/class-wp-rest-meta-fields.php:404

<?php
                if ( ! update_metadata( $meta_type, $object_id, wp_slash( $meta_key ), wp_slash( $value ) ) ) {
                        return new WP_Error(
                                'rest_meta_database_error',
                                /* translators: %s: Custom field key. */
                                sprintf( __( 'Could not update the meta value of %s in database.' ), $meta_key ),
                                array(
                                        'key'    => $name,
                                        'status' => WP_Http::INTERNAL_SERVER_ERROR,
                                )
                        );
                }

For fixing, I suggest using the identity operator to compare the $result to false:
wp-includes/meta.php > function update_metadata > Line: 314-317

<?php
        $result = $wpdb->update( $table, $data, $where );
        if ( false === $result ) {
                return false;
        }

[gdogabriel]

Perhaps the suggestion above could be problematic, since per function documentation it’s expected that failing or the value is the same to return false, and other areas may rely on this.

The new meta field ID if a field with the given key didn't exist and was therefore added, true on successful update, false on failure or if the value passed to the function is the same as the one that is already in the database.

So a second line of thought is to try to fix the update_meta_value function in wp-includes/rest-api/fields/class-wp-rest-meta-fields.php

We need to figure out why the code below is not working to detect when value is the same:

<?php
                if ( is_array( $old_value ) && 1 === count( $old_value )
                        && $this->is_meta_value_same_as_stored_value( $meta_key, $subtype, $old_value[0], $value )
                ) {
                        return true;
                }

[gdogabriel]

In my case, I found that I have duplicate meta_keys in my local installation. This is preventing to detect when the value is the same, since count( $old_value ) should be identical to 1.

post_id	meta_key	meta_value
123	footnotes	[{"id":"8a067ad3-...","content":""}]
123	footnotes	[{"id":"8a067ad3-...","content":""}]

<?php
                if ( is_array( $old_value ) && 1 === count( $old_value )
                        && $this->is_meta_value_same_as_stored_value( $meta_key, $subtype, $old_value[0], $value )
                ) {
                        return true;
                }

For some reason, I have several pages with duplicate footnotes and other metadata, including _edit_last, _wp_page_template, and metadata from other plugins.

Deduplicating metas solves it in my case.

So this may not be an issue to be resolved here. Perhaps this information will be useful to anyone having the same problem.