Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

SQL query is failing in ambari database

SQL query is failing in ambari database

New Contributor

Hi, I am trying to run below SQL query in ambari database to update one of the configuration, but query is failing with following error.

I am trying to run query in ambari database.

[root@c902f10x09 ~]# su - postgres
Last login: Tue Mar 12 22:10:31 EDT 2019 on pts/0 
JAVA at /usr/jdk64/jdk1.8.0_112
-bash-4.2$ /usr/pgsql-9.6/bin/psql
psql (9.6.12) Type "help" for help.

postgres=# \connect ambari
You are now connected to database "ambari" as user "postgres".
ambari=# update repo_version set stack_id = (select stack_id from stack where stack_version = '2.3') where display_name like 'BigInsights%';
ERROR:  relation "repo_version" does not exist
LINE 1: update repo_version set stack_id = (select stack_id from sta...
               ^
ambari=#

When I try to run "update repo_version set stack_id = (select stack_id from stack where stack_version = '2.3') where display_name like 'BigInsights%';"

I am getting error as

"ERROR: relation "repo_version" does not exist

LINE 1: update repo_version set stack_id = (select stack_id from sta..."

How to resolve this?.

@Aditya Sirna, any thoughts?.

4 REPLIES 4

Re: SQL query is failing in ambari database

Super Mentor

@prashanth shetty

Can you please chekc if your ambari server is actually using postgres database with name "ambari" ? Or is it something else?

Can you please share what is the database name do you see when you read the "ambari.properties" as following?

# grep 'database_name'  /etc/ambari-server/conf/ambari.properties
server.jdbc.database_name=ambari
# grep 'schema'  /etc/ambari-server/conf/ambari.properties
server.jdbc.postgres.schema=ambari

.

Also please check if you are connecting to the right Database instance on correct host?

# grep 'jdbc'  /etc/ambari-server/conf/ambari.properties

.

Highlighted

Re: SQL query is failing in ambari database

New Contributor

@Jay Kumar SenSharma

It tried adding "ambari." to query and it works.


ambari=# update ambari.repo_version set stack_id = (select stack_id from ambari.stack where stack_version = '2.3') where display_name like 'BigInsights%';

UPDATE 1

ambari=#

Re: SQL query is failing in ambari database

Super Mentor

@prashanth shetty
Great!! thanks for sharing your working solution.

Re: SQL query is failing in ambari database

New Contributor

@Jay Kumar SenSharma

[root@c902f10x09 ~]# grep 'database_name'  /etc/ambari-server/conf/ambari.properties
server.jdbc.database_name=ambari
[root@c902f10x09 ~]# grep 'schema'  /etc/ambari-server/conf/ambari.properties
server.jdbc.postgres.schema=ambari
[root@c902f10x09 ~]#  grep 'jdbc'  /etc/ambari-server/conf/ambari.properties
custom.mysql.jdbc.name=mysql-jdbc-driver.jar
previous.custom.mysql.jdbc.name=mysql-connector-java.jar
server.jdbc.connection-pool=internal
server.jdbc.database=postgres
server.jdbc.database_name=ambari
server.jdbc.driver=org.postgresql.Driver
server.jdbc.hostname=localhost
server.jdbc.port=5432
server.jdbc.postgres.schema=ambari
server.jdbc.rca.driver=org.postgresql.Driver
server.jdbc.rca.url=jdbc:postgresql://c902f10x09.gpfs.net:5432/ambari
server.jdbc.rca.user.name=ambari
server.jdbc.rca.user.passwd=/etc/ambari-server/conf/password.dat
server.jdbc.url=jdbc:postgresql://c902f10x09.gpfs.net:5432/ambari
server.jdbc.user.name=ambari
server.jdbc.user.passwd=/etc/ambari-server/conf/password.dat






For your reference.