Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.Problem/Motivation
There was an issue where duplicate rows were added to the password_policy_history table that was fixed here:
#3095980: Password history policy inserts password twice in password_policy_history table
but it did not include an update hook to remove the existing duplicates. An update hook was added to do this here:
#3266140: Create hook_update to remove duplicated entries in password_policy_history
but it doesn't work for some databases and thus people are seeing errors like below.
An attempt was made to restructure the query to support all databases but this has proved difficult so the final approach is to just delete all the history. Not ideal, but will work for all databases. All new data in the table should not contain duplicates because the above fix.
Original description:
Drupal 9.4.8
Postgresql 12.x
password policy version 3.2
password_policy 3.1 works fine, but upgrading to password_policy 3.2 fails as follows:
> [error] SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "t1"
> LINE 1: DELETE t1 FROM "password_policy_history" t1 INNER JOIN "pass...
> ^: DELETE t1 FROM "password_policy_history" t1 INNER JOIN "password_policy_history" t2 WHERE t1.id < t2.id AND t1.pass_hash = t2.pass_hash AND t1.timestamp = t2.timestamp; Array
> (
> )Steps to reproduce
Postgresql v12, upgrading versions of dependencies
Proposed resolution
1. Update the update hook to delete all data from the table.
2. For those who've already run the update hook, they can manually remove the data from the table or run the new update hook once this code has been committed and they are using the updated module.
Remaining tasks
- Update code
- Review and test
- Commit
User interface changes
User will not see error when updating to new version with any database.
API changes
Data model changes
Old data will be removed but the data model is not changing.
Comments
Comment #2
joseph.olstaduninstalling the module and re-installing it again seems to be a workaround. losing configuration is a bit of an issue but that's all the time I have for this right now.
Comment #3
joseph.olstadComment #4
joseph.olstadComment #5
joseph.olstadUntil this is fixed, a workaround is to lock to password_policy version 3.1 and avoid the broken hook update for password_policy_update_8304
composer require drupal/password_policy:'3.1'this isn't fixed in 3.x-dev, tried both 3.2 and 3.x-dev, lock to 3.1 for now.
Comment #6
joseph.olstadComment #7
joseph.olstadComment #8
joseph.olstadComment #9
Michelle-Buckby CreditAttribution: Michelle-Buckby commentedComing across the issue with the update hook:
`password_policy_update_8304`
This is after updating from 3.0 to 4.0.0
At first with `drush updb` this update hook seems to hang , on a second attempt it fails and aborts:
```
> [error] Update failed: password_policy_update_8304
[error] Update aborted by: password_policy_update_8304
[error] Finished performing updates.
```
We cannot lock to 3.1 as suggested because we need 4.0.0 as trying to upgrade a project to D10
Comment #10
joseph.olstadIf my memory serves me correctly (it's been a while), a likely workaround to this issue is to uninstall the password_policy module, you may need to unwind some *.info.yml dependencies and rebuild caches to prevent other modules or custom modules from being uninstalled also. Once this module is uninstalled, install the 4.0.0+ password_policy.
You can test to see what happens by trying this drush command:
drush pmu password_policy;
If the only module that is proposed for uninstall is password_policy, this should make it easier.
Probably do this before upgrading the module.
Then in another step, upgrade the password_policy module and then install it/configure it.
Comment #11
Michelle-Buckby CreditAttribution: Michelle-Buckby commentedThank you for your reply, unfortunately trying to uninstall the module with drush seems to just hang.
This was after unwinding some of the *.info.yml to prevent dependencies from uninstalling first.
Not able to give it another go at the moment but will try again at a later date..and try to get more info.
Comment #12
joseph.olstad@Michelle-Buckby, you're in luck, I've written a patch for this.
Comment #13
joseph.olstadI haven't tested this with mysql yet, however it works with Postgresql.
Comment #14
amanp CreditAttribution: amanp at Catalyst IT commentedI confirm that the patch in #12 works with Postgresql. Thanks
Comment #15
Michelle-Buckby CreditAttribution: Michelle-Buckby commentedThank you @joseph.olstad , just getting back here to say I've successfully updated Password Policy on two projects now, no further issues related to this.
Comment #16
joseph.olstadRTBC
based on comment #14
Comment #17
joseph.olstadok this is RTBC for Postgresql, however if someone with MariaDB or MySQL could please test to make sure this works in MySQL, that'd be great, with that said, I could synthesize a test to see myself , I'll write some synthetic test code to try this on mariadb/mysql and see if this is compatible.
Comment #18
joseph.olstadThis patch works great with Postgresql, however it fails with MariaDB/MySQL as follows:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1 USING "password_policy_history" t2 WHERE t1.id < t2.id AND t1.pass_hash = ...' at line 1: DELETE FROM "password_policy_history" t1 USING "password_policy_history" t2 WHERE t1.id < t2.id AND t1.pass_hash = t2.pass_hash AND t1.timestamp = t2.timestamp; ArrayI think this query should be rewritten.
Comment #19
joseph.olstadThis is a patch that is compatible with all rdbms versions and drivers. sqlite, mariadb/mysql, psql/postgresql , mssql and others.
It is a brute force fix for all.
Delete ALL the history that avoids using vendor only query syntax that are not ansi-sql compliant. Some might not like this but I'm guessing most will approve.
Comment #20
joseph.olstadComment #21
joseph.olstadComment #22
joseph.olstadComment #23
joseph.olstadComment #26
Kristen PolAdding credit for work on duplicate issue:
#3314954: sql server password_policy_update_8304 failed with error
Comment #27
Kristen PolThis should be against 4.0.x as 8.x branches are no longer supported, so changing version.
Comment #28
Kristen PolAssigning to myself for review.
Comment #30
Kristen PolAdding credit from duplicate issue:
#3358771: Password policy update is failing for postgreSQL database while doing drush updb
Comment #31
Kristen PolUpdating the summary to provide more context.
Comment #32
Kristen PolMinor wording change to summary.
Comment #34
Kristen PolThanks everyone for your work on this issue. I've merged the fix and included updated/more comments for more clarification on what happened.
Comment #44
Kristen PolAdding credit from #3304188: Do not require user to re-run update 8302 to avoid error "user.field_pending_expire_sent field needs to be updated".
Comment #45
Kristen PolThis is part of the new 4.0.1 release.
Comment #46
joseph.olstadThanks @Kristen Pol, definately the right call here, every system is supported.
Comment #47
joseph.olstadThanks for the credit on this also! :)