Vertica Upgrade to 7.2.1.3 - Error & Workaround

Adrian_Oprea_1 · February 2016

I just upgraded to the last Vertica version available on customer ftp.

As usual the upgrade is quite easy and reliable. But this time i had to sweet a bit as all my users connecting from Reporting tools and ETL processes have lost access to the objects that normally had access to.

This is the error that i got in the error_messages table :

Permission denied for relation table_name

The funny part is that while going over the grants table i didn`t seem to find anything wrong, all roles appear to have the right grants and all the users were assigned all the roles.

The good thing is that the database is up and the data in all there(is a huge db ).

Now to fix my issues without having to go over grants and do it one by one i run the following script:

1- Fix table ownership :

-- run script and save the output
select 'ALTER TABLE '||table_schema||'.'||table_name||' owner to ' ||infa_user||';' from tables where owner_name='infa_user'


-- run script and execute the output
select 'ALTER TABLE '||table_schema||'.'||table_name||' owner to ' ||dbadmin||';' from tables where user_name=<user_name>

-- execute the output of the first script to restore the ownership back.

Note:

- you have to apply to each individual user that owns tables(move owner to dbadmin and then move it back).

- so once you run the script , edit the script to point to the initial owner.

2 - Match the grants to original users

Revoke grants:

select 'revoke '||privileges_description||' on '||object_schema||'.'||object_name||' from '||grantee from grants;

Grant back:

- make sure you just edit the output of the script:

Replace revoke with grant and from with to.

Example:

revoke SELECT on schema.table from role:
--will become 
grant SELECT on schema.table to role;

This fixe my issue

Also keep an eye on the error_messages table for any errors

select * from error_messages where error_level='ERROR'
 order by 1 desc

Also almost forgot , i got an error while trying to import my exported statistics

dbadmin=> select IMPORT_STATISTICS('/vertica_storage/backup/stats_before.xml');
WARNING 6530:  Invalid statistics file. Total number of bounds specified: '67' do not match the buckets value: '68' for column 'tbl-name'. Buckets value specified shtal number of bounds
WARNING 6530:  Invalid statistics file. Total number of bounds specified: '67' do not match the buckets value: '68' for column 'tbl-name'. Buckets value specified sotal number of bounds
                                              IMPORT_STATISTICS
--------------------------------------------------------------------------------------------------------------
 Error validating statistics file: At line 843770:11. Empty content not valid for content model: '(column,)'

Solution for this :

Run analize for all tables before opening the database for users.

Use this script :

SELECT
    'select analyze_statistics('''||table_schema||'.'||table_name||''');'
FROM
   tables;

- i hope this will be helpful to any on the forum.

Vertica Upgrade to 7.2.1.3 - Error &amp; Workaround