Support Questions
Find answers, ask questions, and share your expertise

SQL query is failing in ambari database

SQL query is failing in ambari database

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

.

Re: SQL query is failing in ambari database

@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

@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.