database unavailable during password change

[aaron...@gmail.com]

First many, many thanks for such a great open source product and for the time and effort that the PWM maintainers put into the project. The stability and flexibility that PWM provides has been of great benefit to our various organizational needs related to password management.

And now to the issue...we have a long running (several years) 1.8 PWM deployment (with a MySQL 5.5 back end). Suddenly we started observing an issue in our production environment in which any attempted password change including user initiated, helpdesk initiated or self-service reset would produce the following error:

Database Unavailable: If this error occurs repeatedly, please contact your help desk.

I knew that the issue was data related (we have multiple tomcat instances fronted by an LTM and I was able to readily reproduce the error against all of the instances) however it was a challenge to narrow down the cause. I wasn't able to reproduce the error by way of spinning up a new PWM instance that I seeded from an export of our prod database (I still don't understand why I wasn't able to reproduce with this test).

The following corresponding error was being logged by Tomcat when a password change was attempted:

2019-05-13T16:36:59Z, ERROR, rest.RestSetPasswordServer, error during set password REST operation: 5051 ERROR_DB_UNAVAILABLE (5051 ERROR_DB_UNAVAILABLE (put operation failed: Data truncation: Data too long for column 'value' at row 1))

Given the lack of detail in terms of what was being logged in catalina.out, by way of increasing MySQL's logging, I was able to determine that the insert into the USER_AUDIT table was the insert that was producing the error. Upon interrogating the USER_AUDIT table further, I noticed that the table had north of 24,000 rows (I can get the exact count if that level of detail is needed).

I was able to resolve the issue by dropping the USER_AUDIT table and allowing PWM to recreate the table.

I first wanted to post this to the community in case other's run into this issue as again it took a good deal of trial and error in order to understand what was causing the issue as well as some confusion on my part as to why seeding a new instance with the existing database instance didn't produce the error.

Back to the root cause...is there some limitation that we ran into regarding the USER_AUDIT table? 24,000 rows isn't itself a particularly large MySQL table. Is there regular maintenance that we should be performing against either PWM or the database in order to keep the environment proper?

Thanks much for your help.

Best,
Aaron

[Jason Rivard]

The settings in Settings -> Auditing -> Auditing Configuration control the max rows/age of records.  The error your getting is a specific issue where a specific record size is longer than the column width of the 'value' column in the audit table.  PWM should truncate long messages before writing but something went wrong...

[santosh...@gmail.com]

Hi Aaron,
Thank for your information, i am also having same issue and this is not will all users, i am getting with certain users, some of existing user says all sudden, they are getting user does not exist.
Can you please help me, how to drop table and instruct PWM to create again.

Thanks

[aaron...@gmail.com]

The syntax for dropping a database table is an operation specific to whatever database platform you are using in your setup.  Here is the general process I would advise:

1. backup your database and confirm that your backup is usable (i..e restore it in your pwm DR environment and confirm)

2. stop all app instances of pwm

3. drop the database table

4. restart a single pwm app instance -> pwm should re-create the table automatically but confirm that this took place

5. restart the rest of your pwm app instances 

Hopefully that will get you functional again.

Best,

Aaron

[santosh...@gmail.com]

Thank you so much, it worked, but how to fix this for all so that we dont need to truncate table.
Thanks

[Jason Rivard]

Hi Santosh, I posted on the other thread about extending the size of the PWM_RESPONSES table, or make sure your using newest PWM code and this issue is fixed.

[jason.e...@gmail.com]

Yes, change the type under Settings -> Database (Remote) -> Advanced and set 'Database Value Column Type' to LONGTEXT

Then go into your sql change the user_audit column 'value' to 'longtext'

ALTER TABLE `pwm`.`user_audit` 

CHANGE COLUMN `value` `value` LONGTEXT CHARACTER SET 'latin1' NULL DEFAULT NULL;

[Jason Rivard]

Please keep in mind this advice is database vendor specific.  Different database vendors support different value types.  PWM needs something that will support lengthy character values for the VALUE column.

[jason.e...@gmail.com]

si , i should have notated that, what I posted is for MySQL and it's derivatives such as Percona and MariaDB