Unable to edit dashboards, or add new ones

[ganderson]

Okay, so I dug a bit deeper, and instead ran the following psql command:

Code: Select all

echo "select usermeta_id, user_id, keyname, autoload, keyvalue from xi_usermeta where keyname = 'dashboards';" | psql -U nagiosxi nagiosxi > /tmp/info.txt

This dumps out a lot of data for dashboards. I haven't attached it here because I am wearing my tin foil hat, but it does list the full names of the dashboards including the data before and after the apostrophe.

PHP file requested is attached however.

If there is an easy way to delete all dashboards, I am currently open to this if it will fix the problem...I just need to know how to do it!

[ganderson]

Had to deflate to 7z the file because PHP is not allowed

[tgriep]

I asked for the config.inc.php file to see if your server was using postgres but you beat me to it.

To be sure, run this command to display the Postgres xi_usermeta settings and post them here so I can see if the table sizes are correct.

Code: Select all

echo '\d xi_usermeta;' | psql nagiosxi nagiosxi

If you want to delete the dashboards and start over, here is the procedure.

To find the user_id for the user or users that are currently having the problem:

Code: Select all

echo "select user_id, username from xi_users;" | psql nagiosxi nagiosxi

Then delete the corrupted data that they have in the table for the dashboards:
- Change THEUSERID to the user_id from the above output.

Code: Select all

echo "delete from xi_usermeta where user_id = 'THEUSERID' and keyname = 'dashboards';" | psql nagiosxi nagiosxi

Then logout and login again and recreate the dashboards for that user.

[ganderson]

Thanks for the update.

When you mentioned deleting the dashboard for the user having the problem, you gave me something to test.

Sure enough, other users dashboards were working and able to be edited without issue.

So I deleted the row from the DB for the problematic user. Unfortunately this deletes every single dashboard (awwww). But I could confirm that I could then create and edit dashboards (consolation prize).

Luckily, I had taken a copy of the data before I did any deletion. So I found the new record in the database and updated it with the old data, but removed all occurrences of the apostrophe ( ' ). Now I have the dashboards back, and I can edit them again...(Angels sing)

Can we get some validation on the NagiosXI dashboards name field to make sure this is prevented for the future? We can close this thead now, thanks for the help

So a work around may be (at your own risk)

1. Take a backup
2. Identify the user_id of the affected user

Code: Select all

echo "select user_id, username from xi_users;" | psql nagiosxi nagiosxi

3. Identify the usermeta_id of the users dashboards that are failing

Code: Select all

echo "select usermeta_id from xi_usermeta where keyname = 'dashboards' and user_id = [user_id FROM STEP 2];" | psql -U nagiosxi nagiosxi

4. Grab the bad dashboard settings into a file

Code: Select all

echo "select keyvalue from xi_usermeta where keyname = 'dashboards' and usermeta_id = [usermeta_id FROM STEP 3];" | psql -U nagiosxi nagiosxi > /tmp/info.txt

5. Edit the /tmp/info.txt file to remove any apostrophe ( ' )
6. Add the following including the ( ' ) at the start of the file /tmp/info.txt (there should be no whitespace between the ( ' ) and the existing text):

Code: Select all

UPDATE xi_usermeta SET keyvalue = '

7. Add an apostrophe at the end of the file /tmp/info.txt, with no whitespace after the existing text
8. Push the updated dashboard metadata back into the database

Code: Select all

cat /tmp/dashrestore.txt | psql nagiosxi nagiosxi

[tgriep]

Good job on getting it to work. Without seeing the data, it is hard to figure out what the cause was but I think that either the data did not dump correctly from the old server or it did not get restored to the new server.

[ganderson]

Pretty sure it was just the ( ' ) in the name of the dashboard.

For example, if you create a dashboard called

"A Dashboard"
"Bob's Dashboard"
"Jeff's Dashboard"
"Another Dashboard"

Then export the backup and import into a new instance, you will run into issues I expect. All I did was dump the restored database value, and remove the ( ' ) from all dashboards, and reimport the value. They now look like:


"A Dashboard"
"Bobs Dashboard"
"Jeffs Dashboard"
"Another Dashboard"

And everything works normally. The rest of the data is the same, and the dashboards function as they always did, suggesting that the the original dump was okay, and it was restored into the table okay. But for some reason, it was not happy after restore. No idea why!

[tgriep]

I think I found it. Newer versions of Postgres had a change in how it stores the strings in to the database.

The fix is to do the following
Edit the /var/lib/pgsql/data/postgresql.conf file and change the following line from

Code: Select all

#standard_conforming_strings = on

to

Code: Select all

standard_conforming_strings = off

Code: Select all

Save the file and restart the postgress database by running
service postgresql restart

This is what I found in the Postgress change log that describes what that option is for.

standard_conforming_strings (boolean)

This controls whether ordinary string literals ('...') treat backslashes literally,
as specified in the SQL standard. Beginning in PostgreSQL 9.1, the default is on
(prior releases defaulted to off). Applications can check this parameter to determine how string
literals will be processed. The presence of this parameter can also be taken as an indication that
the escape string syntax (E'...') is supported. Escape string syntax (Section 4.1.2.2) should be
used if an application desires backslashes to be treated as escape characters.