Data Infrastructure Management Software Discussions

unable to query oci dwh database tables from wfa

Aravind

when i am trying to build a filter i am getting the error as :

 

Failed getting filter attributes - SELECT command denied to user 'restricted'@'localhost' for table 'volume'

 

tried to grant permissions  but unable to do so, as dwhuser doesnt have permissions.

 

please help on this.

 

Untitled.png

 

 

18 REPLIES 18

Re: unable to query oci dwh database tables from wfa

ostiguy

Hey Aravind,

 

I think you have something misconfigured in how you are passing your username to MySQL  - the error message you are seeing indicates that you are passing "restricted" as the username to mysql

Re: unable to query oci dwh database tables from wfa

karale

can you post the query?

Re: unable to query oci dwh database tables from wfa

Aravind
select * from dwh_inventory.volume;

Re: unable to query oci dwh database tables from wfa

Aravind

i am connecting to the dwh database through dwhuser ,not as restricted. but i dont no why it is unable to execute a select query.

Re: unable to query oci dwh database tables from wfa

hotz

It should work. I just tested with the dwhuser and works fine for me:

 

snapshot4.png

Re: unable to query oci dwh database tables from wfa

Aravind

from the command line and mysql client it is working fine.  but  its not working from wfa ,when  i  try   to create a filter.

 

Untitled.png

Re: unable to query oci dwh database tables from wfa

hotz

Can you try to access the db with a remote mysql client with dwhuser? I wonder if access is restricted to localhost...

Re: unable to query oci dwh database tables from wfa

hotz

No, that's not it. I tried from my linux box and it works fine. Could it be the firewall?

Re: unable to query oci dwh database tables from wfa

Aravind

i am quite confused why the username in the error is showing as  'restricted',  while  i am connecting as dwhuser.  and moreover dwhuser has  the select permissions.

 

and i am connecting to the db through remote sql client only. i am able to query from every possible way except from wfa.

 

perms.png

Re: unable to query oci dwh database tables from wfa

hotz
Sounds like a bug in WFA. Can you reach out to the wfa community?

Re: unable to query oci dwh database tables from wfa

sinhaa

This is not any Bug etc.. you need to understand what WFA filters are and how they work. At least bascis. Filter queries run locally on WFA DB. In your query, you are giving DB name which is in OCI DB. Giving the DB(dictionary ) name which is in WFA. It will work.

 

 

 

 

 

If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.

View solution in original post

Re: unable to query oci dwh database tables from wfa

Aravind

Hi sinhaa,

 

Thanks a lot for your  help.

i corrected it.now its working fine.

Re: unable to query oci dwh database tables from wfa

trentino123

Hello Arevind,

 

WFA has readonly databases that is pulls from OCUM/DFM, but it does not have OCI as a datasource.

 

Therefore when you try to pull information from WFA, it does not connect to the OCI datawarehouse.

 

In order to connect to the OCI datawarehouse and run a query, you need some powershell code. The following code can retrieve your query, just paste it on a new command, it will write the results on c:\temp\ocitest.csv

 

You will need to change the  $MySQLHost variable.

 

function Invoke-MySQL {
 Param(
   [Parameter(
   Mandatory = $true,
   ParameterSetName = '',
   ValueFromPipeline = $true)]
   [string]$Query
   )

 $MySQLAdminUserName = 'dwhuser'
 $MySQLAdminPassword = 'netapp123'
 $MySQLDatabase = 'dwh_inventory'
 $MySQLHost = 'OCIDWHSERVER.yourdomain.com'
 $ConnectionString = "server=" + $MySQLHost + "; port=3306; uid=" + $MySQLAdminUserName + "; pwd=" + $MySQLAdminPassword + "; database="+$MySQLDatabase

 Try {
   [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
   $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
   $Connection.ConnectionString = $ConnectionString
   $Connection.Open()

   $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
   $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
   $DataSet = New-Object System.Data.DataSet
   $RecordCount = $dataAdapter.Fill($dataSet, "data")
   $Command.Dispose()
   #$Table=$DataSet.Tables["data"] | FT  -auto
 $DataSet.Tables["data"] | Export-Csv "c:\temp\ocitest.csv"
                Get-WFALogger -Info -message $("OCI query result" + $DataSet.Tables["data"] )

     }

 Catch {
   throw "ERROR : Unable to run query : $query `n$Error[0]"
  }

 Finally {
   $Connection.Close()
   }
 } # end function Invoke-MYSQL

$result = Invoke-MySQL -Query " select * from dwh_inventory.volume "


 

Let me know if it helped.

Re: unable to query oci dwh database tables from wfa

Aravind

hi trentino,

 

actually i made a mistake by quering oci db  while creating a filter. which wil not be possible and which is not the way to do.

now i am able to  fetch the  oci db data into local wfa dictionary.

thanks for the response,now its working fine.

Re: unable to query oci dwh database tables from wfa

trentino123

Glad to know it worked Aravind , please post the filter. Attaching the command .dar for anyone that tries to export any MySQL query to any server into a csv.

Earn Rewards for Your Review!
GPI Review Banner
All Community Forums
Public