[ansi-sql compliant updates] - postgresql/mssql/mysql/sqlite - password_policy_update_8304

The upstream patch works for postgresql, however in this case what works for postgresql causes an exception on MariaDB/MySQL

#3313282-18: postgresql Update failed, password_policy_update_8304 →

A simple fix would be to change the original query from deleting duplicate rows, to deleting ALL the history:

see patch.

This 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 instead of complicated markup avoid using vendor only terms that are not ansi-sql compliant. Some might not like this but I'm guessing most will approve.

I removed the postgresql patch just for the moment.

What do we loose by just deleting the history table? Just records of changes?

ya just records of changes.

@sylus, this patch allows the update hook to work on ALL ansi-sql compliant dbs

we don't really care about the password policy history anyway.

https://www.drupal.org/files/issues/2023-08-14/delete_all_history-331328... →

would rather avoid the crash than save a bit of password policy history.

The maintainers of password_policy have acknowledged the issue but it's been ongoing for quite some time this back and forth so I decided to solve the problem without having to test on every single rdbms, I only have 3 different RDBMSs handy for testing. MySQL, MariaDB, Postgresql . With a little effort I could get SQLite up and running however until there's a better solution I'd rather go with the one that will 100% for sure remove duplicates and be compatible.

@sylus, the delete all rows from the password_policy_history table is the easiest way to clean up the duplicates on all platforms (pgsql/mariadb/mysql/persona/sqllite/mssql).
with that said, I have a patch that ONLY removes duplicates and works for postgresql however this patch breaks mariadb/mysql

It would be possible to detect the db driver in use and have a conditionally different query, example psql or mysql however I didn't have the db driver detection logic handy when I wrote the simpler patch that removes all rows from this table. The problem with the condition is that the mysql query also doesn't work on mssql either.

To remain compatible with all systems easily I recommend this patch → . Honestly I don't thing many will complain about losing this history information on an upgrade.

I'm not super happy about this but I've only got so much time and it's very annoying having the drush updb crash so I'd rather that it does not crash and still remove the duplicates.

@sylus,

The solution that works for all rdms types (psql, mysql/sqlite/mssql/etc wasn't actually included in the WxT build
:
https://www.drupal.org/files/issues/2023-08-14/3381106-3.patch →

Was this intentional?

I saw that this was merged upstream :)

Ya thankfully they fixed this, another big bug fix also in password_policy, expired account passwords were causing those logged in accounts to losing css/js, I believe they fixed that. Was rediculous, a mangled page for password update. They fixed the bug in the latest 4x release