Data too long for column 'field_last_password_reset_value': incorrect datetime format

Problem/Motivation

After updating to Drupal 9.3.6 and Password Policy 3.x-dev#966f272d364c35a9c535f768f6b80dddd04ea36b (latest commit as of March 11th), we have started seeing issues when trying to update user passwords. The website whitescreens and we get this error:

Drupal\Core\Entity\EntityStorageException: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'field_last_password_reset_value' at row 1: INSERT INTO "user__field_last_password_reset" ("entity_id", "revision_id", "bundle", "delta", "langcode", "field_last_password_reset_value") VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5); Array ( [:db_insert_placeholder_0] => 1040411 [:db_insert_placeholder_1] => 1040411 [:db_insert_placeholder_2] => user [:db_insert_placeholder_3] => 0 [:db_insert_placeholder_4] => en [:db_insert_placeholder_5] => 2022-03-11 10:26:34 Europe/London ) in Drupal\Core\Entity\Sql\SqlContentEntityStorage->save() (line 811 of /var/www/html/docroot/core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorage.php).

Doing some digging I spotted that the timestamp it's trying to save is 2022-03-11 10:26:34 Europe/London including the whole timezone - rather than something like 2022-03-11T10:26:34 which is how all the rest of the values appear in the database.

Caveat: we have overridden some of the Password Policy validation functions, but I have made sure these are all up-to-date with the latest versions in dev; the only modifications are to add extra user form IDs to if statements.

We have a number of password reset dates in the database already (approx. 93k of them!) and it's only since I updated to the latest version. We are using a specific dev version to fix another bug (we went from 3.0-beta1 to 3.1, and still needed the text updates from #2894929: Instructions unclear for constraint of Uppercase and Lowercase Characters).

Proposed resolution

It looks like this stems from the code setting the password reset value in _password_policy_user_profile_form_update_fields:

// Line 305
  if ($uid && ($current_pass || $form_state->get('user_pass_reset')) && $new_pass) {
    $form_state->setValue(
      'field_last_password_reset',
      [['value' => new DrupalDateTime()]]
    );
    $form_state->setValue('field_password_expiration', ['value' => '0']);
    $form_state->setValue('field_pending_expire_sent', ['value' => '0']);
  }

Checking the value of the `new DrupalDateTime` I can see:

$datetime->__toString(): string (33) "2022-03-11 10:41:04 Europe/London"

In other places the code now uses the Drupal date formatter. I've updated the code on mine to be consistent with that:

    $date = \Drupal::service('date.formatter')->format(\Drupal::time()->getRequestTime(), 'custom', DateTimeItemInterface::DATETIME_STORAGE_FORMAT, DateTimeItemInterface::STORAGE_TIMEZONE);
    $form_state->setValue(
      'field_last_password_reset',
      [['value' => $date]]
    );

Which in turn gives:

$date: string (19) "2022-03-11T10:41:04"

And this is stored correctly in the database, in the same format as the other entries.

I'll have a go at creating a fork and patching this shortly. Marking as major, because this is preventing us from using the module and parts of the site.

Testing Note

- Make sure to manually or apply the patch to remove the duplicate entries from the 'password_policy_history' table. Before testing this issue.